Q1. View the Exhibit and examine the data in the PRODUCT INFORMATION table. 

Which two tasks would require subqueries? (Choose two.) 

A. displaying the minimum list price for each product status 

B. displaying all supplier IDs whose average list price is more than 500 

C. displaying the number of products whose list prices are more than the average list price 

D. displaying all the products whose minimum list prices are more than the average list price of products having the product status orderable 

E. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE 

View Answer

Q2. You need to create a table for a banking application with the following considerations: 

1) You want a column in the table to store the duration of the credit period. 

2) The data in the column should be stored in a format such that it can be easily added and subtracted with 3) date type data without using the conversion functions. 

4) The maximum period of the credit provision in the application is 30 days. 

5) The interest has to be calculated for the number of days an individual has taken a credit for. 

Which data type would you use for such a column in the table? 

A. INTERVAL YEAR TOMONTH 

B. INTERVALDAYTO SECOND 

C. TIMESTAMP WITHTIMEZONE 

D. TIMESTAMP WITH LOCAL TIME ZONE 

View Answer

Q3. Which SQL statement would display the view names and definitions of all the views owned by you? 

A. SELECTviewjiame, text FROM user_view; 

B. SELECTviewjiame, text FROM user_object; 

C. SELECTviewjiame, text FROM user_objects; 

D. SELECTview_name, text FROM user views; 

View Answer

Q4. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause? 

A. to find the groups forming the subtotal in a row 

B. to create group-wise grand totals for the groups specified within a GROUP BY clause 

C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals 

D. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals 

View Answer

Q5. View the Exhibit and examine the description of the ORDERS table. 

Which two WHERE clause conditions demonstrate the correct usage of conversion functions? (Choose two.) 

A. WHERE order_date> TO_DATE('JUL 10 2006','MON DD YYYY) 

B. WHERE TO_CHAR(order_date,'MONDDYYYY) = 'JAN 20 2003' 

C. WHEREorder_date > TO_CHAR(ADD_MONTHS(SYSDATE,6),'MONDDYYYY") 

D. WHERE order_dateIN(TO_DATE('Oct 21 2003','Mon DD YYYY"),TO_CHAR('NOV21 2003','Mon DD YYYY")) 

View Answer

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

You have to display ORDER_ID, ORDER_DATE, and CUSTOMER_ID for all those orders that were placed after the last order placed by the customer whose CUSTOMER_ID is 101 

Which query would give you the desired output? 

A. SELECT order_id, order_date FROM orders 

WHERE order_date > ALL (SELECT MAX(order_date) 

FROM orders) AND 

Customer_id = 101; 

B. SELECT order_id, order_date FROM orders 

WHERE order_date > ANY (SELECT order_date 

FROM orders 

WHERE customer_id = 101); 

C. SELECT order_id, order_date FROM orders 

WHERE order_date > ALL (SELECT order_date 

FROM orders 

WHERE customer_id = 101); 

D. SELECT order_id, order_date FROM orders 

WHERE order_date IN (SELECT order_date 

FROM orders 

WHERE customer id = 101); 

View Answer

Q7. Evaluate the following SQL statement: 

ALTER TABLE hr.emp SET UNUSED (mgr_id); 

Which statement is true regarding the effect of the above SQL statement? 

A. Any synonym existing on the EMP table would have to be re-created. 

B. Any constraints defined on the MGR_ID column would be removed by the above command. 

C. Any views created on the EMP table that include the MGR_ID column would have to be dropped and re-created. 

D. Any index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed. 

View Answer

Q8. Evaluate the following statement: 

CREATE TABLE bonuses(employee_id NUMBER, bonus NUMBER DEFAULT 100); 

The details of all employees who have made sales need to be inserted into the BONUSES table. You can obtain the list of employees who have made sales based on the SALES_REP_ID column of the ORDERS table. The human resources manager now decides that employees with a salary of $8,000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who have made sales get a bonus of 1 % of their salary and also a salary increase of 1 %. The salary of each employee can be obtained from the EMPLOYEES table. 

Which option should be used to perform this task most efficiently? 

A. MERGE 

B. Unconditional INSERT 

C. ConditionalALLINSERT 

D. Conditional FIRST INSERT 

View Answer

Q9. View the Exhibit and examine the structure of the PRODUCT_INFORMATION table. 

You want to see the product names and the date of expiration of warranty for all the products, if the product is purchased today. The products that have no warranty should be displayed at the top and the products with maximum warranty period should be displayed at the bottom. 

Which SQL statement would you execute to fulfill this requirement? 

A. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE-warranty_period; 

B. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE+warranty_period; 

C. SELECT product_name, category_id, SYSDATE+warranty_period AS "Warranty expire date" FROM product_information ORDER BY SYSDATE; 

D. SELECT product_name, category_id, SYSDATE+warranty_period "Warranty expire date" FROM product_information WHERE warranty_period >SYSDATE; 

View Answer

Q10. View the Exhibit and examine the description of the ORDERS table. 

The orders in the ORDERS table are placed through sales representatives only. You are given the task to get the SALES_REP_ID from the ORDERS table of those sales representatives who have successfully referred more than 10 customers. Which statement would achieve this purpose? 

A. SELECT sales_rep_id, COUNT(customer_id)"Total" 

FROMorders" 

HAVING COUNT(customer_id)>10; 

B. SELECT sales_rep_id, COUNT(customer_id) "Total" 

FROM orders " 

WHERE COUNT(customer_id)>10 

GROUP BY sales_rep_id; 

C. SELECT sales_rep_id, COUNT(customer_id) "Total" 

FROMorders" 

GROUP BY sales_rep_id 

HAVING total > 10; 

D. SELECT sales_rep_id, COUNT(customer_id) "Total" 

FROM orders " 

GROUP BY sales_rep_id 

HAVING COUNT(customer_id) > 10; 

View Answer