Q1. View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables. 

You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered: 

SELECT p.product_name, i.item_cnt 

FROM (SELECT product_id, COUNT (*) item_cnt 

FROM order_items 

GROUP BY product_id) i RIGHT OUTER JOIN products p 

ON i.product_id = p.product_id; 

What would happen when the above statement is executed? 

A. Thestatement would execute successfullytoproducetherequired output. 

B. Thestatement wouldnotexecute because inlineviewsandouterjoins cannot be usedtogether. 

C. The statementwouldnot execute because the ITEM_CNT alias cannotbedisplayedintheouter query. 

D. The statement wouldnot execute because the GROUP BYclausecannot be used intheinline view. 

View Answer

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

Evaluate the following SQL statement: 

SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id=manager_id; 

Which statement is true regarding the output for this command? 

A. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by his or her peers. 

B. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by the employee to whom he or she reports. 

C. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is 101, followed by employees below him or her in the hierarchy. 

D. It would return a hierarchical output starting with the employee whose EMPLOYEE_ID is101, followed by employees up to one level below him or her in the hierarchy. 

View Answer

Q3. View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables. 

You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of 

items in each order. 

Which CREATE VIEW statement would create the view successfully? 

A. CREATE OR REPLACE VIEW ord_vu (order_id,order_date) 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

"NO OF ITEMS" 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 

B. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

"NO OF ITEMS" 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 

C. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 

D. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)| NO OF ITEMS' 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date 

WITH CHECK OPTION; 

View Answer

Q4. Which view would you use to display the column names and DEFAULT values for a table? 

A. DBA_TABLES 

B. DBA_COLUMNS 

C. USER_COLUMNS 

D. USER TAB COLUMNS 

View Answer

Q5. A subquery is called a single-row subquery when 

A. the inner query returns a single value to the main query 

B. the inner query uses an aggregate function and returns one or more values 

C. there is only one inner query in the main query and the inner query returns one or more values 

D. the inner query returns one or more values and the main query returns a single value as output 

View Answer

Q6. 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.managerjd=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.managerjd=100; 

View Answer

Q7. Evaluate the following SQL statement: 

ALTER TABLE hr.emp SET UNUSED (mgr_id); 

Which statement is true regarding the effect of the above SQL statement? 

A. Any synonym existing on the EMP table would have to be re-created. 

B. Any constraints defined on the MGR_ID column would be removed by the above command. 

C. Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created. 

D. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed. 

View Answer

Q8. Which statement is true regarding the ROLLUP operator specified in the GROUP BY clause of a SQL statement? 

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

B. It produces only the grand totals for the groups specified in the GROUP BY clause. 

C. It produces higher-level subtotals, moving from right to left through the list of grouping columns specified in the GROUP BY clause. 

D. It produces higher-level subtotals, moving in all the directions through the list of grouping columns specified in the GROUP BY clause. 

View Answer

Q9. A non-correlated subquery can be defined as________ . 

A. a set of sequential queries, all of which must always return a single value 

B. a set of sequential queries, all of which must return values from the same table 

C. a SELECT statement that can be embedded in a clause of another SELECT statement only 

D. a set of one or more sequential queries in which generally the result of the inner query is used as the search value in the outer query 

View Answer

Q10. View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables. 

You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as well as for a PRODUCT ID for the last six months. 

A. SELECT o.customer_Id, oi.productj_id, SUM(oi.unit_price*oi. quantity) "Total" 

FROM order_items oi JOIN orders o 

ON oi.order_id=o.order_id 

GROUP BY ROLLUP (o.customer_id.oi.product_id) 

WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6; 

B. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total" 

FROM orderjtems oi JOIN orders o 

ON oi.order_id=o.order_id 

GROUP BY ROLLUP (o.customer_id.oi.product_id) 

HAVING MONTHS_BETWEEN(order_date, SYSDATE) <= 6; 

C. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" 

FROM order_items oi JOIN orders o 

ON oi.order_id=o.order_id 

GROUP BY ROLLUP (o.customer_id, oi.product_id) 

WHERE MONTHS_BETWEEN(order_date, SYSDATE) >= 6; 

D. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) "Total" 

FROM order_items oi JOIN orders o 

ON oi.order_id=o.order_id 

WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6 

GROUP BY ROLLUP (o.customer_id, oi.product_id); 

View Answer