READ Free Dumps For Oracle- 1z0-001
| Question ID 8162 | Which select statement displays the order id product id and quantity of items in the item table that matches both the product id and quantity of an item order(605). Do not display the details of the order 605?
|
| Option A | SELECT ordeid,prodid,qty FROM item WHERE (prodid,qty) IN (SELECT prodid,qty FROM item WHERE ordid=605);
|
| Option B | SELECT ordeid,prodid,qty FROM item WHERE (prodid,qty) = (SELECT prodid,qty FROM item WHERE ordid=605) AND ordid<>605;
|
| Option C | SELECT ordeid,prodid,qty FROM item WHERE (prodid,qty) IN (SELECT ordid,prodid,qty FROM item WHERE ordid=605 AND ordid<>605;
|
| Option D | SELECT ordeid,prodid,qty FROM item WHERE (prodid,qty) IN (SELECT prodid,qty FROM item WHERE ordid=605); AND ordid<>605;
|
| Correct Answer | D |
Explanation Explanation/Reference: Explanation: Answer D is correct because it shows correct result using operation IN and eliminating ordid = 605 in the main query. Incorrect Answers: A: This query works fine except one thing : it does not eliminate ordid = 605. B: Sub query can return more than one row, so usage of "=" can cause an error, operation IN is need to be used instead. C: This query would work correctly if there was no error : the WHERE clause contains 2 columns, but sub query will return 3 result columns, it will cause an error. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 100-105 Chapter 2: Advanced Selection in Oracle
| Question ID 8163 | Which select statement displays all the employees who do not have any subordinate?
|
| Option A | SELECT
|
| Option B | FROM emp e WHERE e.mgr IS NOT NULL;
|
| Option C | SELECT e.ename FROM emp e WHERE e.empno IN (select m.mgr FROM emp m);
|
| Option D | SELECT e.ename FROM emp e WHERE e.empno NOT IN (select m.mgr FROM emp m);
|
| Option E | SELECT e.ename FROM emp e WHERE e.empno NOT IN (select m.mgr FROM emp m WHERE m.mgr IS NOT NULL);
|
| Correct Answer | D |
Explanation Explanation/Reference: Explanation: Answer D is correct because sub query will return all employees who are manager for somebody and main query will show all employees who are not in this list. Incorrect Answers: A: This query will just show all employees who have manager. B: This query will just show all employees who are manager himself or herself. C: It will show almost the same result as query in answer D, but sub query in answer C does not analyze is MGR column NULL or not, which will cause wrong result of query. Oracle 8, DBA Certification Exam Guide, Jason S. Couchman, p. 10-11 Chapter 1: Selecting Data from Oracle