It can also be used in an INSERT statement or a CREATE VIEW statement. Show
An ORDER BY clause allows you to specify the order in which rows appear in the result set. In subqueries, the ORDER BY clause is meaningless unless it is accompanied by one or both of the result offset and fetch first clauses or in conjunction with the ROW_NUMBER function, since there is no guarantee that the order is retained in the outer result set. It is permissible to combine ORDER BY on the outer query with ORDER BY in subqueries.
SyntaxORDER BY { column-Name | ColumnPosition | Expression } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [ , column-Name | ColumnPosition | Expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ] *column-NameRefers to the names visible from the SelectItems in the underlying query of the SELECT statement. The column-Name that you specify in the ORDER BY clause does not need to be the SELECT list.ColumnPositionAn integer that identifies the number of the column in the SelectItems in the underlying query of the SELECT statement. ColumnPosition must be greater than 0 and not greater than the number of columns in the result table. In other words, if you want to order by a column, that column must be specified in the SELECT list.ExpressionA sort key expression, such as numeric, string, and datetime expressions. Expression can also be a row value expression such as a scalar subquery or case expression. ASCSpecifies that the results should be returned in ascending order. If the order is not specified, ASC is the default.DESCSpecifies that the results should be returned in descending order.NULLS FIRSTSpecifies that NULL values should be returned before non-NULL values.NULLS LASTSpecifies that NULL values should be returned after non-NULL values. Notes
Example using a correlation nameYou can sort the result set by a correlation name, if the correlation name is specified in the select list. For example, to return from the CITIES database all of the entries in the CITY_NAME and COUNTRY columns, where the COUNTRY column has the correlation name NATION, you specify this SELECT statement: SELECT CITY_NAME, COUNTRY AS NATION FROM CITIES ORDER BY NATION Example using a numeric expressionYou can sort the result set by a numeric expression, for example: SELECT name, salary, bonus FROM employee ORDER BY salary+bonus In this example, the salary and bonus columns are DECIMAL data types. Example using a functionYou can sort the result set by invoking a function, for example: SELECT i, len FROM measures ORDER BY sin(i) Example specifying null orderingYou can specify the position of NULL values using the null ordering specification: SELECT * FROM t1 ORDER BY c1 DESC NULLS LAST 1. Which of the following clause is used to limit the number of rows retrieved from a SELECT query?
Answer: B. The WHERE clause is used to restrict the number of rows returned from a SELECT query. 2. Choose the database elements whose values can be compared in a WHERE clause of a SELECT query.
Answer: A, D. The WHERE clause can be used to compare the values from columns, literals, arithmetic functions and functions. 3. What are the elements NOT contained in the WHERE clause predicate of the SELECT query?
Answer: D. Table Name is not required in the WHERE clause predicate. 4. Which of the following values can NOT be returned after evaluation of WHERE clause condition?
Answer: A. If the result of the condition in WHERE clause is not known, NULL is returned. In all other scenarios, either TRUE or FALSE is returned. 5. What is the minimum number of WHERE clauses that must be present in a SELECT query?
Answer: C. The WHERE clause is an optional clause in the SELECT query which is only used to restrict the number of rows. 6. What is the maximum number of WHERE clauses that can be included in a SELECT query?
Answer: A. The WHERE clause is an optional clause in the SELECT query which can be used only once to restrict the number of rows. 7. Which of the following statements are correct about the WHERE clause?
Answer: C. The WHERE clause must have comparison operator to evaluate the condition. It can use function as one of the operand. Only one WHERE clause is allowed in a SELECT query. 8. Write a SELECT query to list down unique departments from EMP table?
Answer: B & C. The keyword DISTINCT is used to filter out the duplicate rows from the SELECT query. 9. Which of the following operations are permitted for date and timestamp columns?
Answer: B, C, and D. Addition, subtraction and Concatenation are the operations permitted for date and timestamp columns. 10. From the below operators, which one of them holds the highest precedence level?
Answer: C. Expressions within the brackets hold the highest precedence level. 11. Interpret the output returned by the below SELECT query SELECT ename, (sysdate - hiredate) FROM emp;
Answer: C. The expression (sysdate-hiredate) returns the number of employment days of an employee with the company. 12. Which of the below statements correctly describle the DUAL table in Oracle?
Answer: B, C, D. The DUAL table in Oracle is owned by SYS and contains one column DUMMY of type VARCHAR2(1). 13. Determine the type of output returned by the below query SELECT sysdate - hiredate FROM emp WHERE empno=7369;
Answer: B. Subtraction between two dates results in numeric difference of days between the two dates 14. Which expressions do NOT return NULL values?
Answer: B, D. Any arithmetic operation with NULL results in NULL. 15. Determine the output of the below query SELECT 'Tutorial''s Point compiles technical tutorials' FROM DUAL;
Answer: B. 16. Examine the TRAINING table as given below: Name Null? Type ----------------------------------------- -------- ------------- TRAINING_ID NOT NULL NUMBER(5) TRAINING_LOCATION NUMBER(7,2) START_DATE DATE END_DATE DATE Which two SQL would execute successfully? (Choose two)
Answer: A, D. Use NVL function to provide an alternate value to a column when NULL. 17. What does the selection of columns in a SELECT statement known as?
Answer: C. Projection is the ability to select only the required columns in SELECT statement. 18. What does the restriction of rows returned by a SELECT statement known as
Answer: C. Restricting is the ability to limit the number of rows by putting certain conditions. 19. Which of the following is true about the query given below? SELECT col1, col2 FROM tab1 ORDER BY col1;
Answer: B. By default, the ORDER BY clause sorts the values in ascending order. 20. Which of the following is true about the SQL query given below? SELECT col1,col2 FROM tab1 WHERE col1 = 'A' ORDER BY col2 DESC, col1;
Answer: C. Since the COL1 is already filtered and fixed in the query as a scalar value, no sorting will happen on the basis of COL1. 21. What is true regarding the query given below? SELECT col1, col2 FROM tab1 ORDER BY col1,col2 WHERE col2 = 'B';
Answer: D. The ORDER BY clause must appear after the WHERE clause in the SELECT statement 22. Which two clauses of the SELECT statement are necessary for Selection and Projection?
Answer: C. 23. Which of the following WHERE clauses will NOT fit in the below SELECT query? SELECT ename, deptno, sal FROM emp;
Answer: C. Character literals must be enclosed within single quotes 24. Choose the WHERE clause that extracts the DNAME values containing the character literal “er” from the DEPT table.
Answer: B. The LIKE operator is used to perform wild card search in SQL queries. 25. Which two of the following conditions are equivalent to each other?
Answer: A, D. The NOT operator can be used to negate the effect of its operand. Therefore (COMM IS NULL) is equivalent to (NOT (COMM IS NOT NULL)). 26. Which of the following clauses are mandatory in an SQL query?
Answer: A. SELECT and FROM are the mandatory clauses in a SELECT query. 27. Which three of the following WHERE clause conditions are equivalent to each other?
Answer: A, C, D. The conditions can be made equivalent with the use of IN, BETWEEN and relational operators 28. Which of the following is true with respect to the below query? SELECT empno, ename, job FROM emp WHERE ename like '_ith%';
Answer: D. 29. Which of the following is used to end a SQL query?
Answer: B, D. A semicolon (;) or backslash (/) is used to terminate a query in SQL* Plus and SQL Developer. 30. The employees JAMES and MILLER want to know their department id by querying the database. Which of the following queries will give the required result?
Answer: D. Multiple conditions can be joined using OR clause. Query execution is successful if either of the two is true. 31. Which of the following is false regarding the WHERE clause?
Answer: C, D. 32. What is the default date format in Oracle?
Answer: D. DD-MON-RR is the default date format in Oracle. 33. Predict the output of the below SQL query. SELECT ename, deptno, sal, comm FROM emp WHERE job = 'SALES' AND hiredate = ”01-JAN-97”;
Answer: B. Date literals must be enclosed within single quotes. 34. You need to display the names of all the employees having the first name as "GARRY" from the EMPLOYEES table. Which of the following queries will fulfill the requirement?
Answer: C. Wild Cards can be used if certain characters of the search string are unknown. 35. You need to display the employee ID of all the employees who contain a letter 's' in their last name at second position and department ID as 100. Which of the following queries will fetch the required results?
Answer: D. The wildcard character underscore (_) is used to substitute a single character. 36. What will be the outcome of the below query? SELECT first_name, last_name, dept_id FROM employees WHERE hire_date LIKE '%98';
Answer: D. The LIKE operator is used to perform wild card search on character and date literals. 37. Which of the following is used to get rows based on a range of values?
Answer: C. The BETWEEN operator is used to retrieve rows based on range of values. 38. You need to display the employee IDs of the employees who have their salaries between 20000 (inclusive) and 50000(inclusive). Which of the following queries will fetch the required results?
Answer: A, D. For larger ranges of values, BETWEEN and relational operators are best suited in the queries. IN operator is not recommended for large range of values. 39. What is true with respect to the below query? SELECT first_name, last_name FROM employees WHERE last_name BETWEEN 'B%' AND 'E%';
Answer: A. The BETWEEN operator works with the range of character values also. 40. What will be the outcome of the query mentioned below? SELECT employee_id, last_name, first_name, salary, manager_id FROM employees WHERE manager_id IN (200,100,300); ORDER BY manager_id ASC;
Answer: B. The IN operator can be used to provide small and limited number of range. 41. Which of the following clause defines a Membership condition?
Answer: D. The IN operator defines a Membership condition which may use a range of values or a subquery. 42. Which of the following data types can be used within IN operator?
Answer: D. The IN operator works with all types of values. 43. You need to display the list of all the employees whose first name starts with “Bryan” or “Jason”. Which of the following queries will fulfill the requirement?
Answer: C, D. The IN operator checks for ANY values defined as membership condition. 44. You need to extract details of those departments whose name contains the string '_DXX'. Which of the below WHERE clauses could be used in the SELECT statement to get the required output?
Answer: B. 45. Which statement is true regarding the default behavior of the ORDER BY clause?
Answer: A. The ORDER BY clause does a case sensitive sorting with character values. 46. You need to generate a report of all employees from the EMPLOYEES table based on the following conditions: 1. The Employee first name should not begin with 'T' or 'N'. 2. The Employee's salary should be more than 20000. 3. The Employee should have been hired after 1st January 2010. Which WHERE clause would give the required result?
Answer: C. 47. Using the EMPLOYEES table, you need to display the names of all employees hired after January 1, 2013, starting with the freshers. Which query would give the required result? (Choose all that apply.)
Answer: A, D. 48. Using the EMPLOYEES table, you need to find out the names and salaries of all the employees hired in departments 100 and 101 in the time interval 15th March '12 to 15th October '13. Which two queries would give the required result? (Choose two.)
Answer: A, D. 49. Using the EMPLOYEES table, you issue the following query to generate the names, current salary and the salary increased after an appraisal by 25%. The increased salary for all the employees should be above 30000. SELECT first_name, salary, salary + (salary *0.25) "INCREASED_SALARY" FROM employees WHERE increased_salary >30000; The query throws an error ORA-00904. What is the reason for the error?
Answer: C. A column alias cannot be used in WHERE clause conditions but can be used in SELECT statement and ORDER BY clause. 50. You need to display employee names from the EMPLOYEES table that belong to the Department id 100 with minimum salary as either 2000 or 4000 and no job_id. You issue the following query. SELECT first_name, dept_id, salary FROM employees WHERE dept_id = 100 AND (salary = 2000 OR salary = 4000) AND job_id <> ''; Which statement is true regarding the above query?
Answer: A. The condition (salary = 2000 OR salary = 4000) results in FALSE because an employee cannot held multiple salaries at a time. 51. Which three tasks can be performed using SQL functions built into Oracle Database? (Choose three.)
Answer: A, B, C. Use formatting functions (TO_CHAR, TO_DATE), and character functions (LENGTH, REPLACE) to achieve the objectives. 52. You need to generate a report that displays the IDs of all employees in the EMPLOYEES table whose salary is at least 25% more than the value 20000. The details should be displayed in the descending order of the salary. You issue the following query. SELECT emp_id FROM employees WHERE salary>=20000*0.25 ORDER BY salary*0.25 DESC; Which statement is true regarding the above query?
Answer: A. The ORDER BY clause can contain column expressions. 53. Examine the structure and data of the TRAININGS table: Name Null? Type ----------------------------------------- -------- ------------- TRAINING_ID NOT NULL NUMBER(5) TRAINING_LOCATION NUMBER(7,2) START_DATE DATE END_DATE DATE TRAINING_ID START_DATE TRAINING_COST ------ ---------------- ------------------------------------------------- 11 01-JAN-10 1000 22 01-FEB-10 2000 33 01-MAR-10 3000 Dates are stored in the default date format dd-mon-rr in the TRAININGS table. Which three SQL statements would execute successfully? (Choose three.)
Answer: A, C, D. 54. Which of the following statements is/are true with respect to the below query? SELECT emp_id, first_name FROM employees ORDER BY dept_id;
Answer: C. The ORDER BY clause can use a column to sort the data which is not selected in the column list but is contained in the table used in FROM clause. 55. Which feature of ORDER BY clause is demonstrated in the below query? SELECT emp_id, first_name “EmpName” FROM employees ORDER BY "EmpName";
Answer: C. The ORDER BY clauses works fine with the column aliases used in SELECT statement. 56. What is true about the query given below? SELECT last_name, job_id, department_id, hire_date FROM employees ORDER BY 2;
Answer: A. Numeric position of the column can be used in the ORDER BY clause. 57. You need to list the employees details for different jobs but only one at a time. SELECT emp_id, first_name, last_name FROM employees WHERE job_id....; Which of the following is an easier way to achieve the same in SQL* Plus?
Answer: C. The &X notation haults the query execution and prompts for user input every time the query is executed. 58. Which of the following statements is true regarding substitution variables in SQL?
Answer: A. 59. Which of the following data type is assigned to Substitution variables?
Answer: C. Substitution variables do not have the data type of their own but comply with the column's data type with whom they are used. 60. Which among the following is true about substitution variables?
Answer: D. 61. Which of the following is a correct syntax for Substitution variables in SQL* Plus?
Answer: C, D. 62. Which of the following Substitution variables will take the entered value once and then keeps it for the rest of the session?
Answer: A. A substitution variable with double ampersand repeatedly uses the value once provided by the user. 63. Which of the following is true about substitution variables?
Answer: C. 64. What is true about the query given below? SELECT first_name, last_name, employee_id, salary FROM employees WHERE employee_id = &eid;
Answer: B, D. 65. Choose the statements which hold true about the query given below. SELECT first_name, last_name, &&prompt_col FROM employees ORDER BY &&promp_col;
Answer: D. A substitution variable can be used in all the clauses of SQL query. 66. Which of the following commands is used to create and assign a value to a substitution variable in SQL* Plus?
Answer: D. Use DEFINE command in SQL* Plus to declare a substitution variable in a session. 67. What will be the outcome of the below activity in SQL* Plus? DEFINE eid = 117 SELECT first_name, last_name, employee_id, salary FROM employees WHERE employee_id = &eid;
Answer: C. 68. What is the command to remove the value of the substitution variable set by the command DEFINE?
Answer: A. Use UNDEFINE command to delete a substitution variable from the session 69. Which of the following commands is used to check the substitution variables values before and after execution of an SQL query?
Answer: D.Use VERIFY command in SQL*Plus and SQL Developer to check the substitution of values using substitution variables. 70. Which of the following are valid operators for the WHERE clause?
Answer: A, B, C. 71. Evaluate the following query: SELECT ename || q'{'s salary is }' || sal AS "Salary" FROM emp; What happens when the above query is executed?
Answer: C. 72. Which of the below WHERE clause predicates will correctly list the employees from department 20?
Answer: C, D. The equality operator (=) is used to compare the operands in the condition for equality. 73. Write a SELECT query to list the employees whose salary is greater than 1000.
Answer: B. The greater than operator (>) is used to compare the operands in the condition. 74. What would happen when the below query is executed in SQL* Plus? SELECT ename, sal, deptno FROM emp WHERE sal/10 > deptno*10;
Answer: A. The WHERE clause can contain expressions. 75. Determine the error in the below SELECT statement SELECT ename, deptno, sal FROM emp WHERE job=CLERK;
Answer: B. Character literals must be enclosed within single quotes 76. Interpret the output of the below SQL query SELECT ename, deptno, sal FROM emp WHERE sysdate-hiredate > 100;
Answer: A, B. Dates expressions can be used in WHERE clause 77. Which of the following query will display the employees which are hired after 31st Decemeber, 1982?
Answer: A, B. Date literals must be enclosed within single quotes. 78. Which of the following WHERE conditions will list employees who were hired on current date?
Answer: C, D. The condition SYSDATE=HIREDATE will not work because SYSDATE contains dynamic timestamp component while hiredate is a static value in the database. 79. What of the following are the valid formats of date literals which can be used in WHERE clause?
Answer: A, C. Default format for date literals is DD-MON-RR. Which clause would you include in a SELECT statement to sort the rows returned by the lastname column?The ORDER BY clause can be used in the SELECT query to sort the result in ascending or descending order of one or more columns.
Which order of clauses in a SELECT statement is correct?Detailed Solution. The correct answer is Select, where, group by, having.
Which clause is required in a SELECT statement?The FROM clause is the only required clause in the SELECT statement. The FROM clause specifies the specific database tables to retrieve data from.
Which clause would you include in a SELECT statement to restrict the data returned to only the employees in department 10?You can restrict the rows returned from the query by using the WHERE clause. A WHERE clause contains a condition that must be met, and it directly follows the FROM clause. In the example, the SELECT statement retrieves the name, job title, and department number of all employees whose job title is CLERK.
|