Q1. - (Topic 1) 

The following data exists in the PRODUCTS table: PROD_ID PROD_LIST_PRICE 

123456 152525.99 

You issue the following query: 

SQL> SELECT RPAD(( ROUND(prod_list_price)), 10,'*') 

FROM products 

WHERE prod_id = 123456; 

What would be the outcome? 

A. 152526**** 

B. **152525.99 

C. 152525** 

D. an error message 

View Answer

Q2. - (Topic 1) 

You are currently located in Singapore and have connected to a remote database in Chicago. You issue the following command: 

Exhibit: 

PROMOTIONS is the public synonym for the public database link for the PROMOTIONS table. 

What is the outcome? 

A. Number of days since the promo started based on the current Chicago data and time 

B. Number of days since the promo started based on the current Singapore data and time. 

C. An error because the WHERE condition specified is invalid 

D. An error because the ROUND function specified is invalid 

View Answer

Q3. - (Topic 2) 

View the Exhibit and examine the structure of the PROMOTIONS table. 

Evaluate the following SQL statement: 

SQL>SELECT promo_name,CASE 

WHEN promo_cost >=(SELECT AVG(promo_cost) 

FROM promotions 

WHERE promo_category='TV') 

then 'HIGH' 

else 'LOW' 

END COST_REMARK 

FROM promotions; 

Which statement is true regarding the outcome of the above query? 

A. It shows COST_REMARK for all the promos in the table. 

B. It produces an error because the subquery gives an error. 

C. It shows COST_REMARK for all the promos in the promo category 'TV'. 

D. It produces an error because subqueries cannot be used with the CASE expression. 

View Answer

Q4. - (Topic 2) 

View the Exhibit and examine the structure of the PRODUCTS, SALES, and SALE_SUMMARY tables. 

SALE_VW is a view created using the following command: 

SQL>CREATE VIEW sale_vw AS 

SELECT prod_id, SUM(quantity_sold) QTY_SOLD 

FROM sales GROUP BY prod_id; 

You issue the following command to add a row to the SALE_SUMMARY table: 

SQL>INSERT INTO sale_summary 

SELECT prod_id, prod_name, qty_sold FROM sale_vw JOIN products 

USING (prod_id) WHERE prod_id = 16; 

What is the outcome? 

A. It executes successfully. 

B. It gives an error because a complex view cannot be used to add data into the SALE_SUMMARY table. 

C. It gives an error because the column names in the subquery and the SALE_SUMMARY table do not match. 

D. It gives an error because the number of columns to be inserted does not match with the number of columns in the SALE_SUMMARY table. 

View Answer

Q5. - (Topic 1) 

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

You want to create a SALE_PROD view by executing the following SQL statements: 

Which statement is true regarding the execution of the above statement? 

A. The view will be created and you can perform DLM operations on the view 

B. The view will not be created because the join statements are not allowed for creating a view 

C. The view will not be created because the GROUP BY clause is not allowed for creating a view 

D. The view will be created but no DML operations will be allowed on the view 

View Answer

Q6. - (Topic 1) 

View the Exhibits and examine the structures of the PRODUCTS SALES and CUSTOMERS tables. 

You need to generate a report that gives details of the customer's last name, name of the product, and the quantity sold for all customers in Tokyo'. Which two queries give the required result? (Choose two.) 

A. 

SELECT c.cust_last_name,p.prod_name, s.quantity_sold FROM sales s JOIN products p 

USING(prod_id) 

JOIN customers c 

USING(cust_id) 

WHERE c.cust_city='Tokyo' 

B. 

SELECT c.cust_last_name, p.prod_name, s.quantity_sold 

FROM products p JOIN sales s JOIN customers c 

ON(p.prod_id=s.prod_id) 

ON(s.cust_id=c.cust_id) 

WHERE c.cust_city='Tokyo' 

C. 

SELECT c.cust_last_name, p.prod_name, s.quantity_sold 

FROM products p JOIN sales s 

ON(p.prod_id=s.prod_id) 

JOIN customers c 

ON(s.cust_id=c.cust_id) 

AND c.cust_city='Tokyo' 

D. 

SELECT c.cust_id,c.cust_last_name,p.prod_id, p.prod_name, s.quantity_sold FROM 

products p JOIN sales s 

USING(prod_id) 

JOIN customers c 

USING(cust_id) 

WHERE c.cust_city='Tokyo' 

View Answer

Q7. - (Topic 2) 

The DBA issues this SQL command: 

CREATE USER Scott 

IDENTIFIED by tiger; 

What privileges does the user Scott have at this point? 

A. No privileges. 

B. Only the SELECT privilege. 

C. Only the CONNECT privilege. 

D. All the privileges of a default user. 

View Answer

Q8. - (Topic 2) 

You need to write a SQL statement that returns employee name, salary, department ID, and maximum salary earned in the department of the employee for all employees who earn less than the maximum salary in their department. 

Which statement accomplishes this task? 

A. SELECT a.emp_name, a.sal, b.dept_id, MAX(sal) FROM employees a, departments b WHERE a.dept_id = b.dept_id AND a.sal < MAX(sal) GROUP BY b.dept_id; 

B. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) b WHERE a.dept_id = b.dept_id AND a.sal < b.maxsal; 

C. SELECT a.emp_name, a.sal, a.dept_id, b.maxsal FROM employees a WHERE a.sal < (SELECT MAX(sal) maxsal FROM employees b GROUP BY dept_id); 

D. SELECT emp_name, sal, dept_id, maxsal FROM employees, (SELECT dept_id, MAX(sal) maxsal FROM employees GROUP BY dept_id) WHERE a.sal < maxsal; 

View Answer

Q9. - (Topic 1) 

View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. There is only one customer with the cus_last_name column having value Roberts. Which INSERT statement should be used to add a row into the ORDERS table for the customer whose CUST_LAST_NAME is Roberts and CREDIT_LIMIT is 600? 

A. INSERT INTO orders VALUES (l.'10-mar-2007\ 'direct'. (SELECT customerid FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000); 

B. INSERT INTO orders (order_id.order_date.order_mode. (SELECT customer id FROM customers WHERE cust_last_iiame='Roberts' AND redit_limit=600).order_total) VALUES(L'10-mar-2007'. 'direct', &&customer_id, 1000): 

C. INSERT INTO(SELECT o.order_id. o.order_date.o.order_modex.customer_id. 

o.ordertotal FROM orders o. customers c WHERE o.customer_id = c.customerid AND c.cust_la$t_name-RoberTs' ANDc.credit_liinit=600) VALUES (L'10-mar-2007\ 'direct'.( SELECT customer_id FROM customers WHERE cust_last_iiame='Roberts' AND credit_limit=600). 1000); 

D. INSERT INTO orders (order_id.order_date.order_mode. 

(SELECT customer_id 

FROM customers 

WHERE cust_last_iiame='Roberts' AND 

credit_limit=600).order_total) 

VALUES(l.'10-mar-2007\ 'direct'. &customer_id. 1000): 

View Answer

Q10. - (Topic 1) 

Which is a valid CREATE TABLE statement? 

A. CREATE TABLE EMP9$# AS (empid number(2)); 

B. CREATE TABLE EMP*123 AS (empid number(2)); 

C. CREATE TABLE PACKAGE AS (packid number(2)); 

D. CREATE TABLE 1EMP_TEST AS (empid number(2)); 

View Answer