Q1. - (Topic 1) 

View the Exhibit and examine the description for the PRODUCTS and SALES table. 

PROD_ID is a primary key in the PRODUCTS table and foreign key in the SALES table. You want to remove all the rows from the PRODUCTS table for which no sale was done for the last three years. Which is the valid DELETE statement? 

A. 

DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE time_id - 3*365 = SYSDATE ); 

B. 

DELETE FROM products WHERE prod_id = (SELECT prod_id FROM sales WHERE SYSDATE >= time_id - 3*365 ); 

C. 

DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE SYSDATE - 3*365 >= time_id); 

D. 

DELETE FROM products WHERE prod_id IN (SELECT prod_id FROM sales WHERE time_id >= SYSDATE - 3*365 ); 

View Answer

Q2. - (Topic 1) 

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

Q3. - (Topic 1) 

View the Exhibit and examine the data in the PROMOTIONS table. 

You need to display all promo categories that do not have 'discount' in their subcategory. 

Which two SQL statements give the required result? (Choose two.) 

A. 

SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount' 

B. 

SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory = 'discount' 

C. 

SELECT promo_category FROM promotions MINUS SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount' 

D. 

SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM promotions WHERE promo_subcategory <> 'discount' 

View Answer

Q4. - (Topic 1) 

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

The ORD_NO column is PRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO 

columns are composite PRIMARY KEY in the ORD_ITEMS table. 

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

A. CREATE INDEX ord_idx1 

ON ord(ord_no); 

B. CREATE INDEX ord_idx2 

ON ord_items(ord_no); 

C. CREATE INDEX ord_idx3 

ON ord_items(item_no); 

D. CREATE INDEX ord_idx4 

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

View Answer

Q5. - (Topic 1) 

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: 

Which DELETE statement is valid? 

A. DELETE FROM employeesWHERE employee_id = (SELECT employee_id FROM employees); 

B. DELETE * FROM employeesWHERE employee_id=(SELECT employee_id FROM new_employees); 

C. DELETE FROM employeesWHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ‘Carrey’); 

D. DELETE * FROM employeesWHERE employee_id IN (SELECT employee_id FROM new_employees WHERE name = ‘Carrey’); 

View Answer

Q6. - (Topic 2) 

Examine the data in the PROMO_BEGIN_DATE column of the PROMOTIONS table: 

PROMO_BEGIN _DATE 

04-jan-00 

10-jan-00 

15-dec-99 

18-oct-98 

22-aug-99 

You want to display the number of promotions started in 1999 and 2000. 

Which query gives the correct output? 

A. 

SELECT SUM(DECODE(SUBSTR(promo_begin_date,8),'00',1,0)) "2000", 

SUM(DECODE(SUBSTR 

(promo_begin_date,8),'99',1,0)) "1999" 

FROM promotions; 

B. 

SELECT SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 

END) "1999",SUM(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 

0 END) "2000" 

FROM promotions; 

C. 

SELECT COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '99' THEN 1 ELSE 0 END) "1999", COUNT(CASE TO_CHAR(promo_begin_date,'yyyy') WHEN '00' THEN 1 ELSE 0 END) "2000" FROM promotions; 

D. 

SELECT COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8), '1999', 1, 

0)) "1999", COUNT(DECODE(SUBSTR(TO_CHAR(promo_begin_date,'yyyy'), 8),'2000', 1, 

0)) "2000" 

FROM promotions; 

View Answer

Q7. - (Topic 2) 

Evaluate the SQL statement: 

SELECT LPAD (salary,10,’*’) 

FROM EMP 

WHERE EMP_ID = 1001; 

If the employee with the EMP_ID 1001 has a salary of 17000, what is displayed? 

A. 17000.00 

B. 17000***** 

C. ****170.00 

D. **17000.00 

E. an error statement 

View Answer

Q8. - (Topic 2) 

View the Exhibits and examine the structures of the CUSTOMERS, SALES, and COUNTRIES tables. 

You need to generate a report that shows all country names, with corresponding customers (if any) and sales details (if any), for all customers. 

Which FROM clause gives the required result? 

A. FROM sales JOIN customers USING (cust_id) FULL OUTER JOIN countries USING (country_id); 

B. FROM sales JOIN customers USING (cust_id) RIGHT OUTER JOIN countries USING (country_id); 

C. FROM customers LEFT OUTER JOIN sales USING (cust_id) RIGHT OUTER JOIN countries USING (country_id); 

D. FROM customers LEFT OUTER JOIN sales USING (cust_id) LEFT OUTER JOIN countries USING (country_id); 

View Answer

Q9. - (Topic 2) 

You need to create a table named ORDERS that contain four columns: 

1. 

an ORDER_ID column of number data type 

2. 

a CUSTOMER_ID column of number data type 

3. 

an ORDER_STATUS column that contains a character data type 

4. 

a DATE_ORDERED column to contain the date the order was placed. 

When a row is inserted into the table, if no value is provided when the order was placed, today’s date should be used instead. 

Which statement accomplishes this? 

A. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE); 

B. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE); 

C. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE DEFAULT SYSDATE); 

D. CREATE OR REPLACE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status VARCHAR2 (10),date_ordered DATE = SYSDATE); 

E. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE = SYSDATE); 

F. CREATE TABLE orders (order_id NUMBER (10),customer_id NUMBER (8),order_status NUMBER (10),date_ordered DATE DEFAULT SYSDATE); 

View Answer

Q10. - (Topic 1) 

You work as a database administrator at ABC.com. You study the exhibit carefully. 

Exhibit: 

Which two SQL statements would execute successfully? (Choose two.) 

A. 

UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE TO_CHAR(promo_end_date, 'yyyy') > '2000' 

B. 

SELECT promo_begin_date FROM promotions WHERE TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98' 

C. 

UPDATE promotions SET promo_cost = promo_cost+ 100 WHERE promo_end_date > TO_DATE(SUBSTR('01-JAN-2000',8)); 

D. 

SELECT TO_CHAR(promo_begin_date,'dd/month') FROM promotions 

WHERE promo_begin_date IN (TO_DATE('JUN 01 98'), TO_DATE('JUL 01 98')); 

View Answer