Q1. Which two statements are true about the GROUPING function? (Choose two.) 

A. Itis used to find the groups forming the subtotal in a row. 

B. It is used to identify the NULL value in the aggregate functions. 

C. It is used to form the group sets involved in generating the totals and subtotals. 

D. It can only be used with ROLLUP and CUBE operators specified in the GROUP BY clause. 

View Answer

Q2. View the Exhibit and examine the description of the PRODUCT_INFORMATION table. 

You want to display the expiration date of the warranty for a product. Which SQL statement would you execute? 

A. SELECT product_id, SYSDATE + warranty_period FROM product_information; 

B. SELECT product_id, TO_YMINTERVAL(warranty_period) FROM product_information; 

C. SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information; 

D. SELECT product_id, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information; 

View Answer

Q3. View the Exhibit and examine the description of the EMPLOYEES table. 

You want to display the EMPLOYEE_ID, FIRST_NAME, and DEPARTMENT_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYEE_ID 104. To accomplish the task, you execute the following SQL statement: 

SELECT employee_id, first_name, department_id 

FROM employees 

WHERE (manager_id, department_id) =(SELECT department_id, manager_id FROM employees WHERE employee_id = 104) 

AND employee_id <> 104; 

When you execute the statement it does not produce the desired output. What is the reason for this? 

A. The WHERE clause condition in the main query is using the = comparison operator, instead of EXISTS. 

B. The WHERE clause condition in the main query is using the = comparison operator, instead of the IN operator. 

C. The WHERE clause condition in the main query is using the = comparison operator, instead of the = ANY operator. 

D. The columns in the WHERE clause condition of the main query and the columns selected in the subquery should be in the same order. 

View Answer

Q4. View the Exhibit and examine the structure of the EMPLOYEES table. 

You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees. 

Which SQL statement would you execute? 

A. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

ON m.employee_id = e.manager_id 

WHERE m.manager_id=100; 

B. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

ON m.employee_id = e.manager_id 

WHERE e.manager_id=100; 

C. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

ON e.employee_id = m.manager_id WHERE m.manager_id=100; 

D. SELECT m.last_name "Manager", e.last_name "Employee" 

FROM employees m JOIN employees e 

WHERE m.employee_id = e.manager_id AND e.manager_id=100; 

View Answer

Q5. Which statement is true regarding the CUBE operator in the GROUP BY clause of a SQL statement? 

A. It produces only aggregates for the groups specified in the GROUP BY clause. 

B. It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause. 

C. It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause. 

D. It produces n+1 possible superaggregatecombinations, if the n columns and expressions are specified in the GROUP BY clause. 

View Answer

Q6. View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables. 

You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get 

the maximum salary in their respective departments. The following SQL statement was written: 

WITH 

SELECT employee_id, last_name, salary 

FROM employees 

WHERE (department_id, salary) = ANY (SELECT* 

FROM dept_max) 

dept_max as (SELECT d.department_id, max(salary) 

FROM departments d JOIN employees j 

ON (d. department_id = j. department_id) 

GROUP BY d. department_id); 

Which statement is true regarding the execution and the output of this statement? 

A. The statement would execute and give the desired results. 

B. The statement would not execute because the = ANY comparison operator is used instead of=. 

C. The statement would not execute because the main query block uses the query name before it is even created. 

D. The statement would not execute because the comma is missing between the main query block and the query name. 

View Answer

Q7. Evaluate the following SQL statement: 

CREATE INDEX upper_name_idx 

ON product_information(UPPER(product_name)); 

Which query would use the UPPER_NAME_IDX index? 

A. SELECT UPPER(product_name) 

FROM product_information 

WHERE product_jd = 2254; 

B. SELECT UPPER(product_name) 

FROM product_jnformation; 

C. SELECT product_id 

FROM product_jnformation 

WHERE UPPER(product_name) IN ('LASERPRO', 'Cable); 

D. SELECT product_jd, UPPER(product_name) 

FROM product_information 

WHERE UPPER(product_name)='LASERPRO' OR list_price > 1000; 

View Answer

Q8. View the Exhibit and examine the structure of the EMPLOYEES table. 

You want to know the FIRST_NAME and SALARY for all employees who have the same manager as that of the employee with the first name 'Neena' and have salary equal to or greater than that of'Neena'. 

Which SQL statement would give you the desired result? 

A. SELECTfirst_name, salary FROM employees WHERE (manager_id,salary) >= ALL (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena') AND first_name <> 'Neena' 

B. SELECTfirst_name, salary FROM employees WHERE (manager_id, salary) >= (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena') AND first_name <> 'Neena' 

C. SELECT first_name, salary FROM employees WHERE (manager_id,salary) >= ANY (SELECT manager_id, salary FROM employees WHERE first_name = 'Neena' AND first_name <> 'Neena' 

D. SELECT first_name, salary FROM employees WHERE (manager_id = (SELECT manager_id FROM employees WHERE first_name = 'Neena') AND salary >= (SELECT salary FROM employees WHERE first_name = 'Neena')) AND first name <> 'Neena' 

View Answer

Q9. View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. 

Which SET operator would you use in the blank space in the following SQL statement to list the departments where all the employees have managers? 

SELECT department_id FROM departments 

SELECT department_id FROM employees WHERE manager_id IS NULL; 

A. UNION 

B. MINUS 

C. INTERSECT 

D. UNION ALL 

View Answer

Q10. View the Exhibit and examine the structure of the ORDERS and ORDERJTEMS tables. 

Evaluate the following SQL statement: 

SELECT oi.order_id, product_jd, order_date 

FROM order_items oi JOIN orders o 

USING(order_id); 

Which statement is true regarding the execution of this SQL statement? 

A. The statement would not execute because table aliases are not allowed in the JOIN clause. 

B. The statement would not execute because the table alias prefix is not used in the USING clause. 

C. The statement would not execute because all the columns in the SELECT clause are not prefixed with table aliases. 

D. The statement would not execute because the column part of the USING clause cannot have a qualifier in the SELECT list. 

View Answer