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

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

Q3. Evaluate the following CREATE TABLE command: 

CREATE TABLE order_item 

(order_id NUMBER(3), 

item_id NUMBER(2), 

qty NUMBER(4), 

CONSTRAINT ord_itm_id_pk 

PRIMARY KEY (order_id item_id) 

USING INDEX 

(CREATE INDEX ord_itm_idx 

ON order_item(order_id,item_id))); 

Which statement is true regarding the above SOL statement? 

A. Itwould execute successfullyandonly ORD_ITM_IDX index would be created. 

B. It would give an error because the USING INDEX clause cannot be used onacomposite primarykey. 

C. It wouldexecutesuccessfully and two indexesORD_ITM_IDXand ORD_ITM_ID_PKwould becreated. 

D. Itwould give an error becausetheUSING INDEX clause isnotpermitted in the CREATETABLEcommand. 

View Answer

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

Evaluate the following SQL statement: SELECT employee_id, last_name, jobjd, manager_id, LEVEL FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id=manager_id; 

Which two statements are true regarding the output of this command? (Choose two.) 

A. Theoutputwouldbeintop-downhierarchy starting with EMPLOYEE_ID having value 101. 

B. Theoutput wouldbein bottom-up hierarchystartingwith EMPLOYEE_ID havingvalue101. 

C. TheLEVEL columndisplaysthenumber of employees in the hierarchy under the employee having theEMPLOYEE_ID 101. 

D. The LEVEL column displays the level in the hierarchy at which the employee is placed undertheemployee having the EMPLOYEE_ID 101 

View Answer

Q5. Evaluate the following SQL statements in the given order: 

DROP TABLE dept; 

CREATE TABLE dept 

(deptno NUMBER(3) PRIMARY KEY, 

deptname VARCHAR2(10)); 

DROP TABLE dept; 

FLASHBACK TABLE dept TO BEFORE DROP; 

Which statement is true regarding the above FLASHBACK operation? 

A. It recovers only the firstDEPTtable. 

B. Itrecovers onlythesecondDEPTtable. 

C. It does not recoveranyof the tables becauseFLASHBACKis not possible in this case. 

D. Itrecovers both the tables but the names would be changed to the ones assigned intheRECYCLEBIN. 

View Answer

Q6. Which CREATE TABLE statement is valid? 

A. CREATE TABLE ord_details 

(ord_no NUMBER(2) PRIMARY KEY, 

item_no NUMBER(3)PRIMARY KEY, 

ord_date date NOT NULL); 

B. CREATE TABLE ord_details 

(ord_no NUMBER(2) UNIQUE, NOT NULL, 

item_no NUMBER(3), 

ord_date date DEFAULT SYSDATE NOT NULL); 

C. CREATE TABLE ord_details 

(ord_no NUMBER(2) , 

item_no NUMBER(3), 

ord_date date DEFAULT NOT NULL, 

CONSTRAINT ord_uq UNIQUE (ord_no), 

CONSTRAINT ord_pk PRIMARY KEY (ord_no)); 

D. CREATE TABLE ord_details 

(ord_no NUMBER(2), 

item_no NUMBER(3), 

ord_date date DEFAULT SYSDATE NOT NULL, 

CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no)); 

View Answer

Q7. View the Exhibit and examine the structure for the ORDERS and ORDER_ITEMS tables. 

You want to display ORDER_ID, PRODUCT_ID, and TOTAL (UNIT_PRICE multiplied by QUANTITY) for all the orders placed in the last seven days. 

Which query would you execute? 

A. SELECT orde_id, product_id, unit_price*quantity "TOTAL" 

FROM order_items oi JOIN orders o 

ON (o.order_id=oi. order_id) 

WHERE o.order_date>=SYSDATE-7; 

B. SELECT o.order_id,oi.product_id, oi.unit_price*oi.quantity "TOTAL" 

FROM order_items oi JOIN orders o 

USING (order_id) 

WHERE o.order_date>=SYSDATE-7; 

C. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL" 

FROM order_items oi JOIN orders o 

WHERE o.order_date>=SYSDATE-7 ON (o.order_id=oi. order_id); 

D. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL" 

FROM orde_items oi JOIN orders o 

ON (o.order_id=oi. order_id) 

WHERE o. order date>=SYSDATE-7; 

View Answer

Q8. Evaluate the following command: 

CREATE TABLE employees 

(employee_id NUMBER(2) PRIMARY KEY, 

last_name VARCHAR2(25) NOT NULL, 

department_id NUMBER(2), job_id VARCHAR2(8), 

salary NUMBER(10,2)); 

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization: 

CREATE OR REPLACE VIEW sales_staff_vu AS 

SELECT employee_id, last_name job_id 

FROM employees 

WHERE job_id LIKE 'SA_%' WITH CHECK OPTION; 

Which statements are true regarding the above view? (Choose all that apply.) 

A. It allows you to insert details of allnewstaff into the EMPLOYEES table. 

B. Itallowsyou todeletethedetails of the existing sales staff fromtheEMPLOYEES table. 

C. It allows you to updatethejob ids oftheexisting sales staff to any other job id in the EMPLOYEES table. 

D. It allows you to insert the IDs, last names and job ids of the sales staff from theviewif it is used in multitable INSERT statements. 

View Answer

Q9. View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the 

PRIMARY KEY in the ORDERS table. 

Evaluate the following CREATE TABLE command: 

CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id) 

AS 

SELECT order_id.order_date,customer_id 

FROM orders; 

Which statement is true regarding the above command? 

A. The NEW_IDRDERS table would not get created because the DEFAULT value can not be specified in the column definition. 

B. The NEW_IDRDERS table would get created and only the NOTNULL constraint defined on the specified columns would be passed to the new table. 

C. The NEW_IDRDERS table would not get created because the column names in the CREATE TABLE commandand the SELECT clause do not match. 

D. The NEW_IDRDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table. 

View Answer

Q10. Which statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose all that apply.) 

A. It can be used only with the SELECT clause. 

B. The WITH clause can hold more than one query. 

C. If the query block name and the table name were the same, then the table name would take precedence. 

D. The query name in the WITH clause is visible to other query blocks in the WITH clause as well as to the main query block. 

View Answer