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

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

Correct Answer : Get Lastest Questions and Answer :

Explanation: The dataset is segmented based on the CLCIKS column. This creates two groups: one with CLCIKS values of 100 and the other with a null CLCIKS value. The average length of CLCIKS value 100 is 3 for the rows in the first group. The NULL CLCIKS value is first converted into the number 0 by the NVL function, and the average length of CLCIKS is 1. The SUM function operates across the two groups adding the values 3 and 1, returning 4.

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 );

Correct Answer : Get Lastest Questions and Answer :

Explanation: ou can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the Oracle FROM clause when you are performing a delete, you can use the Oracle EXISTS clause.

For example:

DELETE FROM suppliers WHERE EXISTS ( SELECT customers.customer_name FROM customers WHERE customers.customer_id = suppliers.supplier_id AND customer_id > 25 );
This Oracle DELETE example would delete all records in the suppliers table where there is a record in the customers table whose customer_id is greater than 25, and the customer_id matches the supplier_id. If you wish to determine the number of rows that will be deleted, you can run the following Oracle SELECT statement before performing the delete.
SELECT COUNT(*) FROM suppliers WHERE EXISTS ( SELECT customers.customer_name FROM customers WHERE customers.customer_id = suppliers.supplier_id AND customer_id > 25 );

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

Correct Answer : Get Lastest Questions and Answer :

Explanation: The syntax for the Oracle HAVING Clause is:

SELECT expression1, expression2, ... expression_n,
aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
HAVING condition;
Parameters or Arguments

aggregate_function can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

expression1, expression2, ... expression_n are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause.

condition is the condition that is used to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.

Related Questions

Question : What value is returned after executing the following statement?
select CAST( '22-Aug-2003' AS varchar2(30) ) from dual;

1. This would convert the date (ie: 22-Aug-2003) into a varchar2(30) value.
2. This would convert the date (ie: 22-Aug-2003) into a Number(30) value.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error

Question : What value is returned after executing the following statement?
CEIL(-32.65)

1. -33
2. -32
3. Access Mostly Uused Products by 50000+ Subscribers
4. -33.0

Question : Select the statement which is equivalent to below block
IF address1 is not null THEN
result := address1;

ELSIF address2 is not null THEN
result := address2;

ELSIF address3 is not null THEN
result := address3;

ELSE
result := null;

END IF;

1. SELECT COMPOSE( address1, address2, address3 ) result
FROM suppliers;
2. SELECT COALESCE( address1, address2, address3 ) result
FROM suppliers;
3. Access Mostly Uused Products by 50000+ Subscribers
FROM suppliers;
4. SELECT NVL( address1, address2, address3 ) result
FROM suppliers;

Question : What value is returned after executing the following statement?
LENGTH2('HadoopExam.com')

1. 12
2. 14
3. Access Mostly Uused Products by 50000+ Subscribers
4. Null

Question : What value is returned after executing the following statement?
SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id >= 500
UNION
SELECT company_id, company_name
FROM companies
WHERE company_name = 'Apple'
ORDER BY 2;

1. First Half data would be sorted based on supplier_name
2. Data would be sorted based on supplier_name but could be random because of different column names
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error

Question : What value is returned after executing the following statement?
SELECT supplier_id, supplier_name
FROM suppliers
WHERE state = 'California'
UNION ALL
SELECT company_id, company_name
FROM companies
WHERE company_id > 1000
ORDER BY 2;

1. First Half data would be sorted based on supplier_name
2. Data would be sorted based on supplier_name but could be random because of different column names
3. Access Mostly Uused Products by 50000+ Subscribers
4. Error