Q1. Evaluate the following CREATE TABLE commands: 

CREATE TABLE orders 

(ord_no NUMBER(2) CONSTRAINT ord_pk PRIMARY KEY, 

ord_date DATE, 

cust_id NUMBER(4)); 

CREATE TABLE ord_items 

(ord_no NUMBER(2), 

item_no NUMBER(3), 

qty NUMBER(3) CHECK (qty BETWEEN 100 AND 200), 

expiry_date date CHECK (expiry_date > SYSDATE), 

CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no), 

CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no)); 

Why would the ORD_ITEMS table not get created? 

A. SYSDATE cannot be used with the CHECK constraint. 

B. TheCHECK constraint cannot be usedtwiceforthesame table. 

C. TheBETWEENclause cannot be used for the CHECK constraint. 

D. ORD_NO and ITEM_NO cannotbeused asacomposite primarykeybecause ORD_NO is also the FOREIGN KEY. 

View Answer

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

You want to display the LAST_NAME for the employees, LAST_NAME for the manager of the employees, and the DEPARTMENT_NAME for the employees having 100 as MANAGER_ID. The following SQL statement was written: 

SELECT m.last_name "Manager", e.last_name "Employee", department_name "Department" 

FROM employees m JOIN employees e 

ON (m.employee_id = e.manager_id) 

WHERE e.manager_id=100 

JOIN departments d 

ON (e.department_id = d.department_id); 

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

A. The statement would provide the desired results. 

B. The statement would not execute because the ON clause is written twice. 

C. The statement would not execute because the WHERE clause is wrongly placed. 

D. The statement would not execute because the self join uses the ON clause instead of the USING clause. 

View Answer

Q3. View the Exhibit and examine the structure of ORD and ORD_ITEMS tables. 

In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO, ITEM_NO). 

Which two CREATE INDEX statements are valid? (Choose two.) 

A. CREATE INDEX ord_idx ON ord(ord_no); 

B. CREATE INDEX ord_idx ON ord_items(ord_no); 

C. CREATE INDEX ord_idx ON ord_items(item_no); 

D. CREATE INDEX ord_idx ON ord,ord_items(ord_no, ord_date,qty); 

View Answer

Q4. Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.) 

A. You can use column alias in the GROUP BY clause. 

B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups. 

C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause. 

D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups. 

E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause. 

View Answer

Q5. Which two statements are true regarding subqueries? (Choose three.) 

A. The ORDER BY clause can be used in the subquery. 

B. A subquery can be used in the FROM clause of a SELECT statement. 

C. If the subquery returns NULL, the main query may still return result rows. 

D. A subquery can be placed in a WHERE clause, GROUP BY clause, or a HAVING clause. 

E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery. 

View Answer

Q6. View the Exhibit and examine the data in the EMPLOYEES tables. 

Evaluate the following SQL statement: SELECT employee_id, department_id FROM employees WHERE department_id= 50 ORDER BY department_id UNION SELECT employee_id, department_id FROM employees WHERE department_id= 90 UNION SELECT employee_id, department_id FROM employees WHERE department_id= 10; What would be the outcome of the above SQL statement? 

A. The statementwouldexecute successfullyanddisplay all the rows intheascending order of DEPARTMENT_ID. 

B. Thestatement would execute successfullybutitwillignoretheORDERBYclause and display the rows in random order. 

C. The statementwouldnot execute because the positional notationinstead of thecolumn name shouldbeusedwiththe ORDER BY clause. 

D. The statement would not execute because the ORDER BY clause should appear only at the end of the SQL statement, that is, in the last SELECT statement. 

View Answer

Q7. Evaluate the CREATE TABLE statement: 

CREATE TABLE products 

(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY, 

product_name VARCHAR2(15)); 

Which statement is true regarding the PROD_ID_PK constraint? 

A. Itwould becreated only if a unique index is manually created first. 

B. Itwould becreated andwould use an automatically created unique index. 

C. It would be createdandwould use an automaticallycreatednonunique index. 

D. Itwouldbecreated and remainsinadisabledstatebecauseno indexis specified in the command. 

View Answer

Q8. Which statement is true regarding external tables? 

A. The default REJECT LIMIT for external tables is UNLIMITED. 

B. The data and metadata for an external table are stored outside the database. 

C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table. 

D. The CREATE TABLE AS SELECT statement can be used to unload data into regular table in the database from an external table. 

View Answer

Q9. View the Exhibit button and examine the structures of ORDERS and ORDER_ITEMS tables. 

In the ORDERS table, ORDER_ID is the PRIMARY KEY and in the ORDER_ITEMS table, ORDER_ID and LINE_ITEM_ID form the composite primary key. 

Which view can have all the DML operations performed on it? 

A. CREATE VIEWV1 

ASSELECTorder_id, product_id 

FROMorder_items; 

B. CREATE VIEW V4(or_no, or_date, cust_id) 

ASSELECTorder_id, order_date, customer_id 

FROM orders 

WHEREorder_date < 30-mar-2007' 

WITH CHECK OPTION; 

C. CREATEVIEW V3 

ASSELECT o.order_id,o.customer_id, i.product_id 

FROM orders o, order_items i 

WHERE o.order_id=i.order_id; 

D. CREATE VIEWV2 

AS SELECT order_id, line_item_id, unit_price*quantity total 

FROM order items; 

View Answer

Q10. Which statement is true regarding the SESSION_PRIVS dictionary view? 

A. It contains the current object privileges available in the user session. 

B. It contains the current system privileges available in the user session. 

C. It contains the object privileges granted to other users by the current user session. 

D. It contains the system privileges granted to other users by the current user session. 

View Answer