Q1. View the Exhibit and examine the structure of the CUST table. 

Evaluate the following SQL statements executed in the given order: 

ALTER TABLE cust 

ADD CONSTRAINT cust_id_pk PRIMARY KEY(cust_id) DEFERRABLE INITIALLY DEFERRED; 

INSERT INTO cust VALUES (1 /RAJ1); --row 1 

INSERT INTO cust VALUES (1 ,'SAM); --row 2 

COMMIT; 

SET CONSTRAINT cust_id_pk IMMEDIATE; 

INSERT INTO cust VALUES (1 /LATA1); --row 3 

INSERT INTO cust VALUES (2 .KING1); --row 4 

COMMIT; 

Which rows would be made permanent in the CUST table? 

A. row4only 

B. rows 2 and 4 

C. rows 3 and 4 

D. rows1and 4 

View Answer

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

You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission. 

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

A. SELECTfirst_name, salary, salary*12+salary*commission_pct "Total" 

FROM EMPLOYEES; 

B. SELECTfirst_name,salary, salary*12+NVL((salary*commission_pct), 0) "Total" 

FROMEMPLOYEES; 

C. SELECTfirst_name, salary, salary*12 + NVL(salary, O)*commission_pct "Total" 

FROM EMPLOYEES; 

D. SELECTfirst_name, salary, salary*12+(salary*NVL2(commission_pct, 

salary,salary+commission_pct))"Total" 

FROM EMPLOYEES; 

View Answer

Q3. 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 LOCATIONJD and CITY where there are no departments located. Which statement is true regarding the execution and output of the command? 

A. The statement would execute and would return the desired results. 

B. The statement would not execute because the = comparison operator is missing in the WHERE clause of the outer query. 

C. The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of <>. 

D. The statement would not execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result. 

View Answer

Q4. View the Exhibit and examine the description for EMPLOYEES and DEPARTMENTS tables. 

Evaluate the following SQL statement: 

SELECT e.department_id, e.job_id, d.location_id, sum(e. salary) total FROM employees e JOIN departments d ON e.department_id = d.department_id GROUP BY CUBE (e.department_id, e.job_id, d.location_id); 

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

A. Theoutputwould display thetotal salaryforallthedepartments. 

B. Theoutputwoulddisplay the total salaryforall theJOB_IDsinadepartment. 

C. The output would display only the grand total ofthesalary for allJOB_IDsin a LOCATION_ID. 

D. Theoutput would displaythegrandtotalof thesalaryfor only the groups specified in the GROUP BY clause. 

View Answer

Q5. 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. CREATEINDEX ord_idx 

ON ord,ord_items(ord_no, ord_date,qty); 

View Answer

Q6. View the Exhibit and examine the details of the EMPLOYEES table. 

You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100. 

Which SQL clauses would you require to accomplish the task? (Choose all that apply.) 

A. WHERE 

B. HAVING 

C. GROUP BY 

D. START WITH 

E. CONNECT BY 

View Answer

Q7. Evaluate the following query: SELECT INTERVAL 300' MONTH, 

INTERVAL 54-2' YEAR TO MONTH, 

INTERVAL '11:12:10.1234567' HOUR TO SECOND 

FROM dual; 

What is the correct output of the above query? 

A. +25-00, +54-02,+00 11:12:10.123457 

B. +00-300, +54-02,+00 11:12:10.123457 

C. +25-00,+00-650,+00 11:12:10.123457 

D. +00-300,+00-650,+0011:12:10.123457 

View Answer

Q8. View the Exhibit and examine the structure of the ORDERS table. 

NEW_IDRDERS is a new table with the columns ORD_ID, ORD_DATE, CUST_ID, and ORD_TOTAL that have the same data types and size as the corresponding columns in the ORDERS table. 

Evaluate the following INSERT statement: 

INSERT INTO new_orders (ord_id, ord_date, cust_id, ord_total) VALUES

(SELECT order_id.order_date.customer_id.order_total FROM orders WHERE order_date > ‘31-dec-1999’); 

Why would the INSERT statement fail? 

A. because column names in NEWORDERS and ORDERS tables do not match 

B. because the VALUES clause cannot be used in an INSERT with a subquery 

C. because the WHERE clause cannot be used in a subquery embedded in an INSERT statement 

D. because the total number of columns in the NEW ORDERS table does not match the total number of columns in the ORDERS table 

View Answer

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

You want to know the EMPLOYEE_ID and FIRST_NAME of all the records in the EMPLOYEES table wherein the JOB_ID column has ST_CLERK or ST_MAN values, the DEPARTMENT_ID column has value 30, and the SALARY column has a value greater than 3,000. 

Which SOL statement would get you the desired result? 

A. SELECT employee_id, first_name 

FROM employees 

WHERE job_id like‘MAN%' OR job_id like 'CLERK%' 

AND department_id = 30 AND salary > 3000; 

B. SELECT employee_ d, first_name 

FROM employees 

WHERE job_id like‘%MAN' OR job_id like '%CLERK' 

AND (department_id = 30 OR salary > 3000); 

C. SELECT employee_id, first_name 

FROM employees 

WHERE (job_id like‘%MAN' AND job_id like '%CLERK’) 

AND department_id = 30 OR salary > 3000; 

D. SELECT employee_id, first_name 

FROM employees 

WHERE (job_id like '%MAN' OR job_id like '%CLERK') 

AND department_id= 30 AND salary > 3000; 

View Answer

Q10. Which statement is true regarding Flashback Version Query? 

A. It returns versions of rows only within a transaction. 

B. It can be used in subqueries contained only in a SELECT statement. 

C. It will return an error if the undo retention time is less than the lower bound time or SCN specified. 

D. It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows. 

View Answer