Oracle 1z0-047 Free Practice Questions

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

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

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 (1,'10-mar-2007', 'direct', (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600), 1000); 

B. INSERT INTO orders (order_id,order_date,order_mode, (SELECT customer_id FROM customers WHERE cust_last_name='Roberts' AND credit_limit=600) .order_total) 

VALUES(1 ,'10-mar-2007', 'direct', &&customer_id, 1000); 

C. INSERT INTO orders (order_id.order_date.order_mode, 

(SELECT customer_id 

FROM customers 

WHERE cust_last_name='Roberts' AND 

credit _limit=600) .order_total) 

VALUES(1 ,'IO-mar-2007', 'direct', &customer_id, 1000); 

D. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id, 

o.order_totalFROM orders o, customers c 

WHERE o.customer_id = c.customer_id 

AND c.cust_last_name='Roberts'ANDc. Credit_limit=600) 

VALUES (1,'10-mar-2007', 'direct',(SELECT customer_id 

FROM customers 

WHERE cust_last_name='Roberts' AND 

Credit_limit=600), 1000); 

View Answer

Q3. Which statements are true regarding the hierarchical query in Oracle Database 10g? (Choose all that apply.) 

A. It is possible to retrieve data only in top-down hierarchy. 

B. Itis possible to retrieve data in top-down or bottom-up hierarchy. 

C. It is possible to remove an entire branch from the output of the hierarchical query. 

D. You cannot specify conditions when you retrieve data by using a hierarchical query. 

View Answer

Q4. The details of the order ID, order date, order total, and customer ID are obtained from the ORDERS table. If the order value is more than 30000, the details have to be added to the LARGE_DRDERS table. The order ID, order date, and order total should be added to the ORDER_HISTORY table, and order ID and customer ID should be added to the CUST_HISTORY table. Which multitable INSERT statement would you use? 

A. Pivoting INSERT 

B. Unconditional INSERT 

C. ConditionalALLINSERT 

D. Conditional FIRST INSERT 

View Answer

Q5. View the Exhibit button and examine the structures of ORDERS and ORDER_ITEMS tables. 

In the ORDERS table, ORDER_ID is the PRIMARY KEY and in the ORDER_ITEMS table, ORDER_ID and LINE_ITEM_ID form the composite primary key. 

Which view can have all the DML operations performed on it? 

A. CREATE VIEWV1 

ASSELECTorder_id, product_id 

FROMorder_items; 

B. CREATE VIEW V4(or_no, or_date, cust_id) 

ASSELECTorder_id, order_date, customer_id 

FROM orders 

WHEREorder_date < 30-mar-2007' 

WITH CHECK OPTION; 

C. CREATEVIEW V3 

ASSELECT o.order_id,o.customer_id, i.product_id 

FROM orders o, order_items i 

WHERE o.order_id=i.order_id; 

D. CREATE VIEWV2 

AS SELECT order_id, line_item_id, unit_price*quantity total 

FROM order items; 

View Answer

Q6. View the Exhibit and examine the structure of the LOCATIONS and DEPARTMENTS tables. 

Which SET operator should be used in the blank space in the following SQL statement to display the cities that have departments located in them? 

SELECT location_id, city 

FROM locations 

SELECT location_id, city 

FROM locations JOIN departments 

USING(location_id); 

A. UNION 

B. MINUS 

C. INTERSECT 

D. UNIONALL 

View Answer

Q7. Evaluate the SQL statements: 

CREATE TABLE new_order 

(orderno NUMBER(4), 

booking_date TIMESTAMP WITH LOCAL TIME ZONE); 

The database is located in San Francisco where the time zone is -8:00. 

The user is located in New York where the time zone is -5:00. 

A New York user inserts the following record: 

INSERT INTO new_order 

VALUES(1, TIMESTAMP ?007-05-10 6:00:00 -5:00?); 

Which statement is true? 

A. When theNewYorkuserselects the row, booking_date is displayed as 007-05-10 3.00.00.000000' 

B. When the New York user selectstherow, booking_date is displayed as 2007-05-10 6.00.00.000000 -5:00'. 

C. WhentheSan Francisco user selectstherow, booking_date is displayed as 007-05-103.00.00.000000' 

D. When the San Francisco user selectstherow, booking_dateis displayed 

as 007-05-103.00.00.000000-8:00' 

View Answer

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

Q9. View the Exhibit and examine the data in the CUST_DET table. 

You executed the following multitable INSERT statement: 

INSERT FIRST 

WHEN credit_limit >= 5000 THEN 

INTO cust_1 VALUES(cust_id, credit_limit, grade, gender) 

WHEN grade = THE 

INTO cust_2 VALUES(cust_id, credit_limit, grade, gender) 

WHEN gender = THE 

INTO cust_3 VALUES(cust_id, credit_limit, grade, gender) 

INTO cust_4 VALUES(cust_id, credit_limit, grade, gender) 

ELSE 

INTO cust_5 VALUES(cust_id, credit_limit, grade, gender) 

SELECT * FROM cust_det; 

The row will be inserted in________. 

A. CUST_1 table only because CREDIT_LIMIT condition is satisfied 

B. CUST_1and CUST_2 tables because CREDIT_LIMIT and GRADE conditions are satisfied 

C. CUST_1,CUST_2 and CUST_5 tables because CREDIT_LIMIT and GRADE conditions are satisfied but GENDER condition is not satisfied 

D. CUST 1, CUST 2 and CUST 4 tables because CREDIT LIMIT and GRADE conditions are satisfied for CUST 1 and CUST 2, and CUST 4 has no condition on it 

View Answer

Q10. View the Exhibit and examine the data in the DEPARTMENTS tables. 

Evaluate the following SQL statement: 

SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department_id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10 

Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.) 

A. ORDERBY 3; 

B. ORDER BY 'b' 

C. ORDER BY DEPT_ID; 

D. ORDER BY DEPT NAME; 

View Answer

Q11. View the Exhibit and examine the details of the PRODUCT_INFORMATION table. 

You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement: 

SELECT product_name, list_price 

FROM product_information 

WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088; 

Which statement is true regarding the execution of the query? 

A. Itwould executebut theoutput would return no rows. 

B. It would execute and the outputwould displaythedesired result. 

C. It wouldnotexecute because the entireWHEREclause conditionisnot enclosedwithinthe parentheses. 

D. Itwould not execute becausethesame column has been used in both sidesoftheANDlogical operatortoform the condition. 

View Answer

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

Q13. View the Exhibit and examine the description of the PRODUCT_INFORMATION table. 

You want to display the expiration date of the warranty for a product. Which SQL statement would you execute? 

A. SELECT product_id, SYSDATE + warranty_period FROM product_information; 

B. SELECT product_id, TO_YMINTERVAL(warranty_period) FROM product_information; 

C. SELECT product_id, TO_YMINTERVAL(SYSDATE) + warranty_period FROM product_information; 

D. SELECT product_id, TO_YMINTERVAL(SYSDATE + warranty_period) FROM product_information; 

View Answer

Q14. View the Exhibit and examine the structure of the EMPLOYEES and DEPARTMENTS tables. 

Which SET operator would you use in the blank space in the following SQL statement to list the departments where all the employees have managers? 

SELECT department_id FROM departments 

SELECT department_id FROM employees WHERE manager_id IS NULL; 

A. UNION 

B. MINUS 

C. INTERSECT 

D. UNION ALL 

View Answer

Q15. View the Exhibit and examine the data in the LOCATIONS table. 

Evaluate the following SOL statement: SELECT street_address FROM locations WHERE REGEXP_INSTR(street_address,'[^[: alpha:]]’) = 1; 

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

A. It would display all the street addresses that do not have a substring 'alpha1. 

B. It would display all the street addresses where the first character is a special character. 

C. It would display all the street addresses where the first character is a letter of the alphabet. 

D. It would display all the street addresses where the first character is not a letter of the alphabet. 

View Answer