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

Q2. View the Exhibit and examine the structure of the ORDERS and ORDER_ITEMS tables. 

Evaluate the following SQL statement: 

SELECT oi.order_id, product_id, order_date FROM order_items oi JOIN orders o 

USING(order_id); 

Which statement is true regarding the execution of this SQL statement? 

A. The statementwouldnot execute because table aliasesarenot allowed in the JOIN clause. 

B. Thestatement wouldnotexecute because thetablealias prefix is not usedin theUSING clause. 

C. The statementwouldnot execute because allthecolumns in the SELECT clause are not prefixed with table aliases. 

D. The statement would not execute because the column part of the USING clause cannot haveaqualifier in the SELECT list. 

View Answer

Q3. Which three tasks can be performed using regular expression support in Oracle Database 10g? (Choose three.) 

A. it can be used to concatenate two strings. 

B. it can be used to find out the total length of the string. 

C. it can be used for string manipulation and searching operations. 

D. it can be used to format the output for a column or expression having string data. 

E. it can be used to find and replace operations for a column or expression having string data. 

View Answer

Q4. View the Exhibit and examine the structure of ORDERS and CUSTOMERS tables. 

Evaluate the following UPDATE statement: 

UPDATE 

(SELECT order_date, order_total, customer_id 

FROM orders) 

SET order_date = 22-mar-2007' 

WHERE customer_id = (SELECT customer_id FROM customers WHERE cust_last_name = 'Roberts'AND Credit_limit = 600); 

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

A. Itwouldnot execute becausetwotables cannot be usedin a single UPDATEstatement. 

B. It wouldexecuteandrestrict modifications toonly thecolumns specifiedin theSELECT statement. 

C. It wouldnotexecute becauseasubquery cannot be used in the WHERE clause ofanUPDATE statement. 

D. Itwould not execute becausetheSELECTstatementcannot be used in place of the table name. 

View Answer

Q5. View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables. 

You need to create a view that displays the ORDER ID, ORDER_DATE, and the total number of items in each order. 

Which CREATE VIEW statement would create the view successfully? 

A. CREATE OR REPLACE VIEW ord_vu (order_id,order_date) 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

"NO OF ITEMS" 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 

B. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

"NO OF ITEMS" 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 

C. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id) 

FROM orders o JOIN order_items i ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date; 

D. CREATE OR REPLACE VIEW ord_vu 

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)ll’ NO OF ITEMS' 

FROM orders o JOIN order_items i 

ON (o.order_id = i.order_id) 

GROUP BY o.order_id,o.order_date 

WITH CHECK OPTION; 

View Answer

Q6. EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table? 

A. UPDATE empdet SET ename ='Amit' WHERE empno = 1234; 

B. DELETE FROM empdet WHERE ename LIKE 'J%' 

C. CREATE VIEWempvu AS SELECT* FROMempdept; 

D. CREATEINDEX empdet_dx ON empdet(empno); 

View Answer

Q7. View the Exhibit and examine the structure of the ORDER_ITEMS table. 

You need to display the ORDER_ID of the order that has the highest total value among all the orders in the ORDER_ITEMS table. 

Which query would produce the desired output? 

A. SELECT order_id FROM order_items 

WHERE(unit_price*quantity) = MAX(unit_price*quantity) GROUP BY order_id; 

B. SELECT order_id FROM order_items 

WHERE(unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items) GROUP 

BY order_id; 

C. SELECT order_id FROM order_items 

WHERE (unit_price*quantity) = (SELECT MAX(unit_price*quantity) FROM order_items GROUP 

BY order_id); 

D. SELECT order_id FROM order_items GROUP BY order_id 

HAVING SUM(unit_price*quantity) =(SELECT MAX(SUM(unit_price*quantity)jFROM order_items 

GROUP BY order_id); 

View Answer

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

You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree? 

A. WHERE 

B. HAVING 

C. GROUP BY 

D. STARTWITH 

E. CONNECT BY PRIOR 

View Answer

Q9. Evaluate the following statements: 

CREATE TABLE digits 

(id NUMBER(2), 

description VARCHAR2(15)); 

INSERT INTO digits VALUES (1,'ONE’); 

UPDATE digits SET description =TWO'WHERE id=1; 

INSERT INTO digits VALUES (2.’TWO’); 

COMMIT; 

DELETE FROM digits; 

SELECT description FROM digits 

VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE; 

What would be the outcome of the above query? 

A. Itwouldnot display any values. 

B. It would displaythevalue TWO once. 

C. Itwould display the valueTWOtwice. 

D. Itwould display the values ONE, TWO, andTWO. 

View Answer

Q10. View the Exhibit and examine the description of the ORDER_ITEMS and 

PRODUCT_INFORMATION tables. 

The ORDER_ITEM table has records pertaining to details for each product in an order. The 

PRODUCT_INFORMATION table has records for all the products available for ordering. 

Evaluate the following SOL statement: 

SELECT oi.order_id, pi.product_id 

FROM orderjtems oi RIGHT OUTER JOIN product_information pi 

ON (oi. product_id=pi. Product_id); 

Which statement is true regarding the output of this SOL statement? 

A. The query would return the ORDER_ID and PRODUCT_ID for only those products that are ordered. 

B. The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered as well as for the products that have never been ordered. 

C. The query would return the ORDER_ID and PRODUCT_ID for the products that are ordered but not listed in the PRODUCT_INFORMATION table. 

D. The query would return the ORDER_ID and PRODUCT_ID for those products that are ordered as well as for the products that have never been ordered, and for the products that are not listed in the PRODUCT INFORMATION table. 

View Answer