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

Question : Which comparison operator cannot be used with multiple-row subqueries?
A. ALL
B. ANY
C. IN
D. NOT IN



1. A,B
2. B,C
3. Access Mostly Uused Products by 50000+ Subscribers
4. A,B,C
5. A,B,C,D




Correct Answer : Get Lastest Questions and Answer :

Explanation: ALL, ANY, IN, and NOT IN are the multiple-row comparison operators.









Question : Which of these set operators will not sort the rows?

1. INTERSECT
2. MINUS
3. Access Mostly Uused Products by 50000+ Subscribers
4. UNION ALL



Correct Answer : Get Lastest Questions and Answer :

Explanation: UNION ALL returns rows in the order that they are delivered by the two queries from which the compound query is made up.




Question : Which of these operators will remove duplicate rows from the final result?



1. INTERSECT
2. MINUS
3. Access Mostly Uused Products by 50000+ Subscribers
4. UNION ALL
4. 1,2 and 3


Correct Answer : Get Lastest Questions and Answer :

Explanation: INTERSECT, MINUS, and UNION all remove duplicate rows.
4 is incorrect. UNION ALL returns all rows, duplicates included.

Related Questions

---

Question : Select the correct statement which applies for below query.
SELECT supplier_id
FROM suppliers
INTERSECT
SELECT supplier_id
FROM orders;

1. if a supplier_id appeared in the suppliers it would appear in your result set.
2. if a supplier_id appeared in orders table, it would appear in your result set.
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of above


Question : Select the correct statement which applies for below query.
SELECT *
FROM customers
WHERE EXISTS (SELECT *
FROM order_details
WHERE customers.customer_id = order_details.customer_id);

1. will return only first record from the customers table where there is at least one record in the order_details table with the matching customer_id.
2. will return all records from the customers table where there is at least one record in the order_details.
3. Access Mostly Uused Products by 50000+ Subscribers
4. None of above


Question : A query can have a subquery embedded within it. Under what circumstances could there be more than one subquery?


1. The outer query can include an inner query. It is not possible to have another query within the inner query.
2. It is possible to embed a single-row subquery inside a multiple-row subquery, but not the other way around.
3. Access Mostly Uused Products by 50000+ Subscribers
4. Subqueries can be embedded within each other with no practical limitations on depth.




Question :

SELECT * FROM TABLE1 WHERE CLICKS > (SELECT AVG(CLICKS) FROM USERS);

When will the subquery be executed?



1. It will be executed before the outer query.
2. It will be executed after the outer query.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It will be executed once for every row in the EMPLOYEES table.



Question : SELECT A.USER_ID, A.LAST_NAME FROM TABLE A WHERE A.CLICKS> (SELECT avg(B.CLICKS) FROM TABLE B WHERE B.click_id=A.click_id);
When will the subquery be executed?




1. It will be executed before the outer query.
2. It will be executed after the outer query.
3. Access Mostly Uused Products by 50000+ Subscribers
4. It will be executed once for every row in the EMPLOYEES table.





Question : What are the distinguishing characteristics of a scalar subquery?


1. A scalar subquery returns one row.
2. A scalar subquery cannot be used in the SELECT LIST of the parent query.
3. Access Mostly Uused Products by 50000+ Subscribers
4. 1 and 2
5. All 1,2 and 3