Q1. View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables. 

Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them? 

SELECT location_id, city 

FROM locations 

SELECT location_id, city 

FROM locations JOIN departments 

USING(location_id); 

A. UNION 

B. MINUS 

C. INTERSECT 

D. UNIONALL 

View Answer

Q2. View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables. 

Evaluate the following SQL statement: 

SELECT oi.order_id, product_id, 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 statementwouldnot execute because table aliasesarenot allowed in the JOIN clause. 

B. Thestatement wouldnotexecute because thetablealias prefix is not usedin theUSING clause. 

C. The statementwouldnot execute because allthecolumns 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 haveaqualifier in the SELECT list. 

View Answer

Q3. Given below are the SQL statements executed in a user session: 

CREATE TABLE product 

(pcode NUMBER(2), 

pnameVARCHAR2(10)); 

INSERT INTO product VALUES(1, ‘pen’); 

INSERT INTO product VALUES (2,'penci’); 

SAVEPOINT a; 

UPDATE product SET pcode = 10 WHERE pcode = 1; 

SAVEPOINT b; 

DELETE FROM product WHERE pcode = 2; 

COMMIT; 

DELETE FROM product WHERE pcode=10; 

ROLLBACK TO SAVEPOINT a; 

Which statement describes the consequences? 

A. No SQL statementwouldberolledback. 

B. Both the DELETE statements would be rolled back. 

C. Only the second DELETE statementwould be rolledback. 

D. Both theDELETE statementsand the UPDATE statement would berolledback. 

View Answer

Q4. View the Exhibit and examine the structure of the MARKS_DETAILS and MARKStables. 

Which is the best method to load data from the MARKS DETAILStable to the MARKStable? 

A. Pivoting INSERT 

B. Unconditional INSERT 

C. Conditional ALL INSERT 

D. Conditional FIRST INSERT 

View Answer

Q5. View the Exhibit and examine the data in EMP and DEPT tables. 

In the DEPT table, DEPTNO is the PRIMARY KEY. 

In the EMP table, EMPNO is the PRIMARY KEY and DEPTNO is the FOREIGN KEY referencing 

the DEPTNO column in the DEPT table. 

What would be the outcome of the following statements executed in the given sequence? 

DROP TABLE emp; 

FLASHBACK TABLE emp TO BEFORE DROP; 

INSERT INTO emp VALUES (2.COTT 10); 

INSERT INTO emp VALUES (3,ING 55); 

A. Both the INSERT statements would fail because all constraints are automatically retrieved when the table is flashed back. 

B. Both the INSERT statements would succeed because none of the constraints on the table are automatically retrieved when the table is flashed back. 

C. Only the first INSERT statement would succeed because all the constraints except the primary key constraint are automatically retrieved after a table is flashed back. 

D. Only the second INSERT statement would succeed because all the constraints except referential integrity constraints that reference other tables are retrieved automatically after the table is flashed back. 

View Answer

Q6. Which three statements are true regarding group functions? (Choose three.) 

A. They can be used on columns or expressions. 

B. They can be passed as an argument to another group function. 

C. They can be used only with a SQL statement that has the GROUP BY clause. 

D. They can be used on only one column in the SELECT clause of a SQL statement. 

E. They can be used along with the single-row function in the SELECT clause of a SQL statement. 

View Answer

Q7. 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. 

Which SQL statement would you execute to get the desired output? 

A. 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; 

B. 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) 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

Q8. View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables. 

You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table. 

Which DELETE statements are valid? (Choose all that apply.) 

A. DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status in (0,1)); 

B. DELETE * FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status IN (0,1)); 

C. DELETE FROM order_items i WHERE order_id = (SELECT order_id FROM orders o WHERE i. order_id = o. order_id AND order_status IN (0,1)); 

D. DELETE FROM (SELECT* FROM order_items i.orders o WHERE i.order_id = o.order_id AND order_status IN (0,1)); 

View Answer

Q9. 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_jd, TO_YMINTERVAL(warranty_period) FROM product_information; 

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

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

View Answer

Q10. The first DROP operation is performed on PRODUCTS table using the following command: 

DROP TABLE products PURGE; 

Then you performed the FLASHBACK operation by using the following command: 

FLASHBACK TABLE products TO BEFORE DROP; 

Which statement describes the outcome of the FLASHBACK command? 

A. It recovers only thetablestructure. 

B. It recovers thetablestructure,data,andtheindexes. 

C. It recovers thetablestructure anddatabutnotthe related indexes. 

D. It is not possible to recover the table structure, data, or the related indexes. 

View Answer