Q1. View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables. 

The query should display the employee IDs of all the employees who have held the job SA_MAN at any time during their tenure. 

Choose the correct SET operator to fill in the blank space and complete the following query. 

SELECT employee_id 

FROM employees 

WHERE job_id = 'SA_MAN' 

SELECT employee_id 

FROMjob_history 

WHERE job_id='SA_MAN' 

A. UNION 

B. MINUS 

C. INTERSECT 

D. UNIONALL 

View Answer

Q2. View the Exhibit and examine the details of the ORDER_ITEMS table. 

Evaluate the following SQL statements: 

Statement 1: 

SELECT MAX(unit_price*quantity) "Maximum Order" 

FROM order items; 

Statement 2: 

SELECT MAX(unit_price*quantity) "Maximum Order" 

FROM order_items GROUP BY order_id; 

Which statements are true regarding the output of these SQL statements? (Choose all that apply. 

A. Statement 1 would return only one row of output. 

B. Both the statements would give the same output. 

C. Statement 2 would return multiple rows of output. 

D. Statement 1 would not return any row because the GROUP BY clause is missing. 

E. Both statements would ignore NULL values for the UNIT_PRICE and QUANTITY columns. 

View Answer

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

The ORDERS table belongs to the user OE. HR is another user in the database. 

Evaluate the commands issued by users OE and HR in the following order: 

Statement 1 by user OE: GRANT SELECT, 

UPDATE(customer_id, order_total) 

ON orders 

TOhr; 

Statement 1 by user HR: SELECT * FROM oe.orders; 

Statement 2 by user HR: UPDATE oe.orders 

SET order_totah 10000; 

Which statement is true regarding the above commands? 

A. Statement 1 by user OE would not work because the statement has to be issued by the DBA. 

B. Statement 2 by user HR would not work because the grant is only for SELECT in a subquery of update. 

C. There are no errors in the statements issued by OE and HR; all the statements would execute successfully. 

D. Statement 1 by user HR would not work because SELECT and UPDATE privileges have been granted only on CUSTOMER_ID and ORDER_TOTAL columns. 

View Answer

Q4. Which statement correctly differentiates a system privilege from an object privilege? 

A. System privileges can be granted only by the DBA whereas object privileges can be granted by DBAs or the owner of the object. 

B. System privileges give the rights to only create user schemas whereas object privileges give rights to manipulate objects in a schema. 

C. Users require system privileges to gain access to the database whereas they require object privileges to create objects in the database. 

D. A system privilege is the right to perform specific activities in a database whereas an object privilege is a right to perform activities on a specific object in the database. 

View Answer

Q5. View the Exhibit and examine the data in the PRODUCT_INFORMATION table. 

There are some products listed in the PRODUCT_INFORMATION table that have no value in the 

LIST_PRICE column. You issued the following SQL statement to find out the PRODUCT_NAME 

for these products: 

SELECT product_name, list_price 

FROM product_information WHERE list_price = NULL; 

The query returns no rows. What changes would you make in the statement to get the desired result? 

A. Change the WHERE clause to WHERE list_price = 0 

B. Change the WHERE clause to WHERE list_price = ''. 

C. Change the WHERE clause to WHERE list_price IS NULL. 

D. In the WHERE clause, enclose NULL within single quotation marks. 

E. In the WHERE clause, enclose NULL within double quotation marks. 

View Answer

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

Q7. Evaluate the following CREATE SEQUENCE statement: 

CREATE SEQUENCE seql START WITH 100 INCREMENT BY 10 MAXVALUE 200 CYCLE NOCACHE; 

The sequence SEQ1 has generated numbers up to the maximum limit of 200. You issue the following SQL statement: 

SELECT seql.nextval FROM dual; 

What is displayed by the SELECT statement? 

A. 1 

B. 10 

C. 100 

D. an error 

View Answer

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

Which UPDATE statement is valid? 

A. UPDATE orders 

SETorder_date = 12-mar-2007\ order_total IS NULL WHERE order_id = 2455; 

B. UPDATE orders 

SET order_date = 12-mar-2007', order_total = NULL WHERE order_id = 2455; 

C. UPDATE orders 

SET order_date = '12-mar-2007' 

AND order_total = TO_NUMBER(NULL) 

WHERE order_id = 2455; 

D. UPDATE orders 

SET order_date = TO_DATE('12-mar-2007,l,dd-mon-yyyy')lSET order_total = 

TO_NUMBER(NULL) WHERE order id = 2455; 

View Answer

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

Q10. Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.) 

A. The HAVING clause conditions can have aggregate functions. 

B. The HAVING clause conditions can use aliases for the columns. 

C. WHERE and HAVING clauses cannot be used together in a SQL statement. 

D. The WHERE clause is used to exclude rows before the grouping of data. 

E. The HAVING clause is used to exclude one or more aggregated results after grouping data. 

View Answer