Examine the structure of the CUSTOMERS table: (Choose two.)

CUSTNO is the PRIMARY KEY.
You must determine if any customers' details have been entered more than once using a different CUSTNO, by listing all duplicate names.
Which two methods can you use to get the required result?

1. A. Subquery
2. B. Self-join
3. C. Full outer-join with self-join
4. D. Left outer-join with self-join
5. E. Right outer-join with self-join

Correct Answer: AB

View the Exhibit and examine the details of the PRODUCT_INFORMATION table.

You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:
SELECT product_name, list_price FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; Which statement is true regarding the execution of the query?

1. A. It would not execute because the entire WHERE clause is not enclosed within parentheses.
2. B. It would execute but would return no rows.
3. C. It would not execute because the same column has been used twice with the AND logical operator.
4. D. It would execute and return the desired.

Correct Answer: B

Which statement is true about SQL query processing in an Oracle database instance? (Choose the best answer.)

1. A. During parsing, a SQL statement containing literals in the WHERE clause that has been executed by any session and which is cached in memory, is always reused for the current execution.
2. B. During executing, the oracle server may read data from storage if the required data is not already in memory.
3. C. During row source generation, rows that satisfy the query are retrieved from the database and stored in memory.
4. D. During optimization, execution plans are formulated based on the statistics gathered by the database instance, and the lowest cost plan is selected for execution.

Correct Answer: B

Examine the structure of the EMPLOYEES table. NameNull?Type
---------------------- ------------ EMPLOYEE_IDNOT NULLNUMBER(6) FIRST_NAMEVARCHAR2(20) LAST_NAMENOT NULLVARCHAR2(25) EMAILNOT NULLVARCHAR2(25) PHONE NUMBERVARCHAR2(20) HIRE_DATENOT NULLDATE JOB_IDNOT NULLVARCHAR2(10) SALARYNUMBER(8,2) COMMISSION_PCTNUMBER(2,2) MANAGER_IDNUMBER(6) DEPARTMENT_IDNUMBER(4)
There is a parent/child relationship between EMPLOYEE_ID and MANAGER_ID.
You want to display the last names and manager IDs of employees who work for the same manager as the employee whose EMPLOYEE_ID is 123.
Which query provides the correct output?

1. A. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.manager_id = m.employee_id)AND e.employee_id = 123;
2. B. SELECT e.last_name, m.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.manager_id)WHERE e.employee_id = 123;
3. C. SELECT e.last_name, e.manager_idFROM employees e RIGHT OUTER JOIN employees mon (e.employee_id = m.employee_id)WHERE e.employee_id = 123;
4. D. SELECT m.last_name, e.manager_idFROM employees e LEFT OUTER JOIN employees mon (e.manager_id = m.manager_id)WHERE e.employee_id = 123;

Correct Answer: B

Examine the structure of the EMPLOYEES table. (Choose the best answer.)

You must display the details of employees who have manager with MANAGER_ID 100, who were hired in the past 6 months and who have salaries greater than 10000.

1. A. SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALL SELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100)INETRSECTSELECT last_name, hire_date, salaryFROM employees WHERE hire_date > SYSDATE- 180;
2. B. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = 100)UNION ALL(SELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000);
3. C. SELECT last_name, hire_date, salaryFROM employeesWHERE manager_id = (SELECT employee_id FROM employees WHERE employee_id = '100')UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180INTERSECTSELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000;
4. D. (SELECT last_name, hire_date, salaryFROM employeesWHERE salary > 10000UNION ALLSELECT last_name, hire_date, salaryFROM employeesWHERE manager_ID = (SELECT employee_id FROM employees WHERE employee_id = 100))UNIONSELECT last_name, hire_date, salaryFROM employeesWHERE hire_date > SYSDATE -180;

Correct Answer: C