Q1. - (Topic 1) 

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

Using the PROMOTIONS table, you need to display the names of all promos done after 

January 1, 2001 starting with the latest promo. 

Which query would give the required result? (Choose all that apply.) 

A. SELECT promo_name,promo_begin_date 

FROM promotions 

WHERE promo_begin_date > '01-JAN-01' 

ORDER BY 1 DESC; 

B. SELECT promo_name,promo_begin_date "START DATE" 

FROM promotions 

WHERE promo_begin_date > '01-JAN-01' 

ORDER BY "START DATE" DESC; 

C. SELECT promo_name,promo_begin_date 

FROM promotions 

WHERE promo_begin_date > '01-JAN-01' 

ORDER BY 2 DESC; 

D. SELECT promo_name,promo_begin_date 

FROM promotions 

WHERE promo_begin_date > '01-JAN-01' 

ORDER BY promo_name DESC; 

View Answer

Q2. - (Topic 1) 

Here is the structure and data of the CUST_TRANS table: Exhibit: 

Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table. 

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

A. SELECT transdate + '10' FROM cust_trans; 

B. SELECT * FROM cust_trans WHERE transdate = '01-01-07' 

C. SELECT transamt FROM cust_trans WHERE custno > '11' 

D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07' 

E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000; 

View Answer

Q3. - (Topic 1) 

See the Exhibit and Examine the structure of SALES and PROMOTIONS tables: Exhibit: 

You want to delete rows from the SALES table, where the PROMO_NAME column in the PROMOTIONS table has either blowout sale or everyday low price as values. 

Which DELETE statements are valid? (Choose all that apply.) 

A. 

DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale') AND promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'everyday low price'); 

B. 

DELETE FROM sales WHERE promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale') OR promo_id = (SELECT promo_id FROM promotions WHERE promo_name = 'everyday low price'); 

C. 

DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name = 'blowout sale' OR promo_name = 'everyday low price'); 

D. 

D DELETE FROM sales WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_name IN ('blowout sale','everyday low price')); 

View Answer

Q4. - (Topic 1) 

You need to calculate the number of days from 1st Jan 2007 till date: 

Dates are stored in the default format of dd-mm-rr. 

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

A. SELECT SYSDATE - TO_DATE('01/JANUARY/2007') FROM DUAL; 

B. SELECT TO_DATE(SYSDATE,'DD/MONTH/YYYY')-'01/JANUARY/2007' FROM DUAL; 

C. SELECT SYSDATE - TO_DATE('01-JANUARY-2007') FROM DUAL 

D. SELECT SYSDATE - '01-JAN-2007' FROM DUAL 

E. SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY')-'01-JAN-2007' FROM DUAL; 

View Answer

Q5. - (Topic 1) 

When does a transaction complete? (Choose all that apply.) 

A. When a PL/SQL anonymous block is executed B. When a DELETE statement is executed 

C. When a data definition language statement is executed 

D. When a TRUNCATE statement is executed after the pending transaction 

E. When a ROLLBACK command is executed 

View Answer

Q6. - (Topic 2) 

Examine the structure of the CUSTOMERS table: 

CUSTNO is the PRIMARY KEY in the table. You want to find out if any customers' details have been entered more than once using different CUSTNO, by listing all the duplicate names. 

Which two methods can you use to get the required result? (Choose two.) 

A. self-join 

B. subquery 

C. full outer-join with self-join 

D. left outer-join with self-join 

E. right outer-join with self-join 

View Answer

Q7. - (Topic 2) 

You are granted the CREATE VIEW privilege. What does this allow you to do? 

A. Create a table view. 

B. Create a view in any schema. 

C. Create a view in your schema. 

D. Create a sequence view in any schema. 

E. Create a view that is accessible by everyone. 

F. Create a view only of it is based on tables that you created. 

View Answer

Q8. - (Topic 2) 

View the Exhibit and examine the structure of the PRODUCTS tables. 

You want to generate a report that displays the average list price of product categories where the average list price is less than half the maximum in each category. 

Which query would give the correct output? 

A. 

SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category); 

B. 

SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category); 

C. 

SELECT prod_category,avg(prod_list_price) FROM products HAVING avg(prod_list_price) < ALL (SELECT max(prod_list_price)/2 FROM products GROUP BY prod_category); 

D. 

SELECT prod_category,avg(prod_list_price) FROM products GROUP BY prod_category HAVING avg(prod_list_price) > ANY (SELECT max(prod_list_price)/2 FROM products); 

View Answer

Q9. - (Topic 2) 

Evaluate the following SQL statement: 

SQL> SELECT cust_id, cust_last_name FROM customers WHERE cust_credit_limit IN (select cust_credit_limit FROM customers WHERE cust_city ='Singapore'); 

Which statement is true regarding the above query if one of the values generated by the subquery is NULL? 

A. It produces an error. 

B. It executes but returns no rows. 

C. It generates output for NULL as well as the other values produced by the subquery. 

D. It ignores the NULL value and generates output for the other values produced by the subquery. 

View Answer

Q10. - (Topic 2) 

Examine the description of the CUSTOMERS table: 

The CUSTOMER_ID column is the primary key for the table. 

Which statement returns the city address and the number of customers in the cities Los Angeles or San Francisco? 

A. SELECT city_address, COUNT(*) FROM customers 

WHERE city_address IN ( ‘Los Angeles’, ‘San Fransisco’); 

B. SELECT city_address, COUNT (*) 

FROMcustomers 

WHERE city address IN ( ‘Los Angeles’, ‘San Fransisco’) 

GROUP BY city_address; 

C. SELECT city_address, COUNT(customer_id) 

FROMcustomers 

WHERE city_address IN ( ‘Los Angeles’, ‘San Fransisco’) 

GROUP BYcity_address, customer_id; 

D. SELECT city_address, COUNT (customer_id) 

FROM customers 

GROUP BY city_address IN ( ‘Los Angeles’, ‘San Fransisco’); 

View Answer