Question : : Evaluate the following query: SQL> SELECT TRUNC(ROUND(156.00, -1), -1) FROM DUAL; What would be the outcome? 1. 16 2. 100 3. 160 4. 200 5. 150
Correct Answer : 3
Explanation: Function Purpose ROUND(column|expression, n) Rounds the column, expression, or value to n decimal places or, if n is omitted, no decimal places (If n is negative, numbers to the left of decimal point are rounded.) TRUNC(column|expression, n) Truncates the column, expression, or value to n decimal places or, if n is omitted, n defaults to zero
Question : You want to display percent of the rows from the sales table for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows. Which query will provide the required result? 1. A 2. B 3. C 4. D
Correct Answer : 4 Explanation: Top-N Queries The syntax for the row limiting clause looks a little complicated at first glance. [ OFFSET offset { ROW | ROWS } ] [ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ] { ROW | ROWS } { ONLY | WITH TIES } ] Actually, for the classic Top-N query it is very simple. The example below returns the 5 largest values from an ordered set. Using the ONLY clause limits the number of rows returned to the exact number requested. SELECT val FROM rownum_order_test ORDER BY val DESC FETCH FIRST 2 ROWS ONLY; VAL ---------- 10 2 rows selected. Using the WITH TIES clause may result in more rows being returned if multiple rows match the value of the Nth row. In this case the 5th row has the value "8", but there are two rows that tie for 5th place, so both are returned. SELECT val FROM rownum_order_test ORDER BY val DESC FETCH FIRST 5 ROWS WITH TIES;
In addition to limiting by row count, the row limiting clause also allows us to limit by percentage of rows. The following query returns the bottom 20% of rows. SELECT val FROM rownum_order_test ORDER BY val FETCH FIRST 20 PERCENT ROWS ONLY; VAL ---------- 1 2 4 rows selected.
Question : You need to list the employees in DEPARTMENT_ID 30 in a single row, ordered by HIRE_DATE. Examine the sample output: Which query will provide the required output? 1. A 2. B 3. C 4. D
Correct Answer : 2 Explanation: For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.
As a single-set aggregate function, LISTAGG operates on all rows and returns a single output row. As a group-set aggregate, the function operates on and returns an output row for each group defined by the GROUP BY clause. As an analytic function, LISTAGG partitions the query result set into groups based on one or more expression in the query_partition_clause. The arguments to the function are subject to the following rules: The measure_expr can be any expression. Null values in the measure column are ignored. The delimiter_expr designates the string that is to separate the measure values. This clause is optional and defaults to NULL. The order_by_clause determines the order in which the concatenated values are returned. The function is deterministic only if the ORDER BY column list achieved unique ordering. The return data type is RAW if the measure column is RAW; otherwise the return value is VARCHAR2. Aggregate Examples The following single-set aggregate example lists all of the employees in Department 30 in the hr.employees table, ordered by hire date and last name: SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list", MIN(hire_date) "Earliest" FROM employees WHERE department_id = 30; Emp_list Earliest ------------------------------------------------------------ --------- Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares 07-DEC-02
1. DELETE FROM TableA WHERE NOT EXIST ( SELECT * FROM TableB WHERE TableA.field1 = TableB.fieldx AND TableA.field2 = TableB.fieldz ); 2. DELETE FROM TableA WHERE IN ( SELECT * FROM TableB WHERE TableA.field1 = TableB.fieldx AND TableA.field2 = TableB.fieldz ); 3. Access Mostly Uused Products by 50000+ Subscribers WHERE NOT IN ( SELECT * FROM TableB WHERE TableA.field1 = TableB.fieldx AND TableA.field2 = TableB.fieldz ); 4. DELETE FROM TableA WHERE NOT EXISTS ( SELECT * FROM TableB WHERE TableA.field1 = TableB.fieldx AND TableA.field2 = TableB.fieldz );
Question : Select the statement which is not correct ? 1. SELECT department, SUM(sales) AS "Total sales" FROM order_details GROUP BY department HAVING SUM(sales) > 25000; 2. SELECT department, COUNT(*) AS "Number of employees" FROM employees WHERE salary & lt; 49500 GROUP BY department HAVING COUNT(*) > 10; 3. Access Mostly Uused Products by 50000+ Subscribers FROM employees GROUP BY department HAVING MIN(salary) & lt; 42000; 4. 1 and 3