Oracle Advanced SQL and PL/SQL Developer Certification Questions and Answers (Dumps and Practice Questions)

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

Related Questions

---

Question : How many rows of data are returned after executing the following statement?
SELECT USER_ID, SUM(NVL(CLICKS,100) FROM USERS GROUP BY USER_ID HAVING SUM(CLICKS) >400
Assume the USERS table has ten rows and each contains a CLICKS value of 100, except for one, which has a null value in the CLICKS field.
The first and second five rows have USER_ID values of 10 and 20, respectively.

1. 2
2. 1
3. Access Mostly Uused Products by 50000+ Subscribers
4. None



Question : Select the correct update statement syntex

1. UPDATE table
SET column1 = expression1,
column2 = expression2,
...
WHERE conditions;
2. UPDATE table1
SET column1 = (SELECT expression1
FROM table2
WHERE conditions)
WHERE conditions;
3. Access Mostly Uused Products by 50000+ Subscribers
SET state = 'California',
customer_rep = 32
WHERE customer_id > 100;
4. Only 1 and 3
5. All 1,2 and 3



Question : What values are returned after executing the following statement?

SELECT USER_ID,MAX_CLICKS FROM USERS GROUP BY MAX_CLICKS;

Assume that the USERS table has ten records with the same USER_ID value of DBA and the same MAX_CLICKS value of 100.


1. One row of output with the values DBA, 100
2. Ten rows of output with the values DBA, 100
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above



Question : What value is returned after executing the following statement?

SELECT SUM((AVG(LENGTH(NVL(CLICKS,0))))) FROM USERS GROUP BY CLICKS;

Assume there are ten user records and each contains a CLICKS value of 100, except for one, which has a null value in the CLICKS field.




1. An error is returned
2. 3
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of the above



Question : How would I write an Oracle DELETE statement to delete all records in TableA whose data in field and field
DO NOT match the data in fieldx and fieldz of TableB?




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

5. None of the above