Q1. View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
Which DELETE statement would execute successfully?
A. DELETEorder_idFROMorders WHEREorder_total< 1000;
B. DELETEordersWHERE order_total < 1000;
C. DELETE FROM orders WHERE (SELECTorder_id FROM order_items);
D. DELETE orders o, order_itemsi WHEREo.order id = i.order id;
Answer: B
Q2. Which two statements are true regarding roles? (Choose two.)
A. A role can be granted to itself.
B. A role can be granted to PUBLIC.
C. A user can be granted only one role at any point of time.
D. The REVOKE command can be used to remove privileges but not roles from other users.
E. Roles are named groups of related privileges that can be granted to users or other roles.
Answer: B,E
Q3. Evaluate the following statements:
CREATE TABLE digits
(id NUMBER(2),
description VARCHAR2(15));
INSERT INTO digits VALUES (1,'ONE’);
UPDATE digits SET description =TWO'WHERE id=1;
INSERT INTO digits VALUES (2.’TWO’);
COMMIT;
DELETE FROM digits;
SELECT description FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;
What would be the outcome of the above query?
A. Itwouldnot display any values.
B. It would displaythevalue TWO once.
C. Itwould display the valueTWOtwice.
D. Itwould display the values ONE, TWO, andTWO.
Answer: C
Q4. View the Exhibit and examine the structure of the EMP table which is not partitioned and not an index-organized table.
Evaluate the following SQL statement:
ALTER TABLE emp DROP COLUMN first_name;
Which two statements are true regarding the above command? (Choose two.)
A. The FIRST_NAME column would be dropped provided it does not contain any data.
B. The FIRST_NAME column would be dropped provided at least one or more columns remain in the table.
C. The FIRST_NAME column can be rolled back provided the SET UNUSED option is added to the above SQL statement.
D. The FIRST_NAME column can be dropped even if it is part of a composite PRIMARY KEY provided the CASCADE option is used.
Answer: BD
Q5. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause?
A. to find the groups forming the subtotal in a row
B. to create group-wise grand totals for the groups specified within a GROUP BY clause
C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals
D. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals
Answer: C
Q6. View the Exhibit and examine the description of the EMPLOYEES table.
You want to display the EMPLOYE_ID, FIRST_NAME, and DEPARTMEN_ID for all the employees who work in the same department and have the same manager as that of the employee having EMPLOYE_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. TheWHERE clause condition in themainquery is usingthe= comparison operator,insteadof theINoperator.
C. The WHERE clause condition in themainquery is using the=comparison operator,insteadof the =ANYoperator.
D. The columns in the WHERE clause condition of the main query andthecolumns selected inthesubquery should be in the same order.
Answer: D
Q7. Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED order_date;
Which statement is true?
A. The DESCRIBE command would still display the ORDER_DATE column.
B. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
C. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.
D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.
Answer: D
Q8. 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 MARKS table?
A. PivotingINSERT
B. Unconditional INSERT
C. ConditionalALLINSERT
D. Conditional FIRST INSERT
Answer: A
Q9. View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables.
Evaluate the following SOL statement:
SELECT location_id, city FROM locations I WHERE NOT EXISTS (SELECT location_id FROM departments WHERE location_id <> I. location_id);
This statement was written to display LOCATION_ID and CITY where there are no departments located. Which statement is true regarding the execution and output of the command?
A. The statementwouldexecute andwouldreturn the desired results.
B. Thestatement wouldnotexecute becausethe= comparison operatorismissing in the WHERE clause of theouterquery.
C. Thestatement wouldexecutebutitwillreturnzerorows because the WHERE clause intheinner query shouldhavethe= operatorinstead of <>.
D. The statement wouldnot execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result.
Answer: C
Q10. View the Exhibit and examine the description of the ORDERS table.
Your manager asked you to get the SALES_REP_ID and the total numbers of orders placed by each of the sales representatives. Which statement would provide the desired result?
A. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
GROUP BY sales_rep_id;
B. SELECT sales_rep_id, COUNT(order_id)total_orders FROM orders GROUP BY sales_rep_id, total_orders;
C. SELECT sales_rep_id, COUNT(order_id)total_orders FROM orders;
D. SELECT sales_rep_id, COUNT(order_id)total_orders FROM orders WHERE sales_rep_id IS NOT NULL;
Answer: A