1z0-047 oracle database sql expert pdf : Apr 2021 Edition
Exam Code: 1Z0-047 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Oracle Database SQL Expert
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1Z0-047 Exam.
2021 Apr 1Z0-047 Study Guide Questions:
Q113. View the Exhibit and examine the structure of the ORDERS table:
The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint.
Evaluate the following statement:
INSERT INTO (SELECT order_id.order_date.customer_id
FROM ORDERS
WHERE order_total = 1000
WITH CHECK OPTION)
VALUES (13, SYSDATE, 101);
What would be the outcome of the above INSERT statement?
A. It would execute successfully and the new row would be inserted into a new temporary table created by the subquery.
B. It would execute successfully and the ORDER_TOTAL column would have the value 1000 inserted automatically in the new row.
C. It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.
D. It would not execute successfully because all the columns from the ORDERS table should have been included in the SELECT list and values should have been provided for all the columns.
Answer: C
Q114. Evaluate the following statement:
INSERT ALL
WHEN order_total < 10000 THEN
INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN
INTO medium_orders
WHEN order_total > 2000000 THEN
INTO large_orders
SELECT order_id, order_total, customer_id
FROM orders;
Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?
A. They areevaluatedby allthe three WHENclauses regardlessofthe resultsof the evaluation ofany other WHEN clause.
B. They are evaluated by thefirst WHENclause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.
C. They are evaluated by the first WHEN clause. If the condition isfalse,thenthe row wouldbeevaluated by the subsequentWHENclauses.
D. TheINSERT statement would give an error becausetheELSE clause is notpresent forsupport in case none of theWHENclauses are true.
Answer: A
Q115. 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.
Answer: BC
Q116. Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)
A. You can use column alias in the GROUP BY clause.
B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.
D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.
E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in the SELECT clause should be included in the GROUP BY clause.
Answer: DE
Q117. View the Exhibit and examine the structure of the PRODUCT_INFORMATION and INVENTORIES tables.
You have a requirement from the supplies department to give a list containing PRODUCT_ID, SUPPLIER_ID, and QUANTITY_ON_HAND for all the products wherein QUANTITY_ON_HAND is less than five.
Which two SQL statements can accomplish the task? (Choose two.)
A. SELECT product_id, quantity_on_hand , supplier_id FROM product_information NATURAL JOIN inventories AND quantity_on_hand<5;
B. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventoriesi USING (product_id) AND quantity_on_hand < 5;
C. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventories i ON (pi. product_id=i. product_id) WHERE quantity_on_hand < 5;
D. SELECT i.product_id, i.quantity_on_hand , pi.supplier_id FROM product_information pi JOIN inventories i ON (pi. product_id=i. product_id) AND quantity_on_hand < 5;
Answer: CD
Avant-garde oracle 1z0-047 latest dumps:
Q118. 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
Answer: C
Q119. Which statement is true regarding the CUBE operator in the GROUP BY clause of a SQL statement?
A. It produces only aggregates for the groups specified in the GROUP BY clause.
B. It finds all the NULL values in the superaggregates for the groups specified in the GROUP BY clause.
C. It produces 2 n possible superaggregate combinations, if the n columns and expressions are specified in the GROUP BY clause.
D. It produces n+1 possible superaggregatecombinations, if the n columns and expressions are specified in the GROUP BY clause.
Answer: C
Q120. View the Exhibit and examine the structure of ORDER_ITEMS and ORDERS tables.
You need to remove from the ORDER_ITEMS table those rows that have an order status of 0 or 1 in the ORDERS table.
Which DELETE statements are valid? (Choose all that apply.)
A. DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status in (0,1));
B. DELETE * FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status IN (0,1));
C. DELETE FROM order_items i WHERE order_id = (SELECT order_id FROM orders o WHERE i. order_id = o. order_id AND order_status IN (0,1));
D. DELETE FROM (SELECT* FROM order_items i.orders o WHERE i.order_id = o.order_id AND order_status IN (0,1));
Answer: ACD
Q121. View the Exhibit and examine the description of the EMPLOYEES table.
Evaluate the following SQL statement: SELECT employee_id, last_name, jobjd, manager_id, LEVEL FROM employees START WITH employee_id = 101 CONNECT BY PRIOR employee_id=manager_id;
Which two statements are true regarding the output of this command? (Choose two.)
A. Theoutputwouldbeintop-downhierarchy starting with EMPLOYEE_ID having value 101.
B. Theoutput wouldbein bottom-up hierarchystartingwith EMPLOYEE_ID havingvalue101.
C. TheLEVEL columndisplaysthenumber of employees in the hierarchy under the employee having theEMPLOYEE_ID 101.
D. The LEVEL column displays the level in the hierarchy at which the employee is placed undertheemployee having the EMPLOYEE_ID 101
Answer: AD
Q122. Which two statements are true regarding the types of table joins available in Oracle Database 10g? (Choose two.)
A. You can use the JOIN clause to join only two tables.
B. You can explicitly provide the join condition with a NATURAL JOIN.
C. You can use the USING clause to join tables on more than one column.
D. You can use the ON clause to specify multiple conditions while joining tables.
Answer: CD
Exact oca oracle database sql expert exam guide exam 1z0-047 pdf:
Q123. View the Exhibit and examine the structure of the ORDERS table.
NEW_IDRDERS is a new table with the columns ORD_ID, ORD_DATE, CUST_ID, and ORD_TOTAL that have the same data types and size as the corresponding columns in the ORDERS table.
Evaluate the following INSERT statement:
INSERT INTO new_orders (ord_id, ord_date, cust_id, ord_total) VALUES
(SELECT order_id.order_date.customer_id.order_total FROM orders WHERE order_date > ‘31-dec-1999’);
Why would the INSERT statement fail?
A. because column names in NEWORDERS and ORDERS tables do not match
B. because the VALUES clause cannot be used in an INSERT with a subquery
C. because the WHERE clause cannot be used in a subquery embedded in an INSERT statement
D. because the total number of columns in the NEW ORDERS table does not match the total number of columns in the ORDERS table
Answer: B
Q124. Which two statements are true about the GROUPING function? (Choose two.)
A. Itis used to find the groups forming the subtotal in a row.
B. It is used to identify the NULL value in the aggregate functions.
C. It is used to form the group sets involved in generating the totals and subtotals.
D. It can only be used with ROLLUP and CUBE operators specified in the GROUP BY clause.
Answer: A,D
Q125. The first DROP operation is performed on PRODUCTS table using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?
A. It recovers only thetablestructure.
B. It recovers thetablestructure,data,andtheindexes.
C. It recovers thetablestructure anddatabutnotthe related indexes.
D. It is not possible to recover the table structure, data, or the related indexes.
Answer: D
Q126. View the Exhibit and examine the description of the PRODUCT_INFORMATION table.
SELECT product_name, list_price, min_price, list_price - min_price Difference FROM product_information
Which options when used with the above SQL statement can produce the sorted output in ascending order of the price difference between LIST_PRICE and MIN_PRICE? (Choose all that apply.)
A. ORDERBY4
B. ORDER BY MIN_PRICE
C. ORDER BY DIFFERENCE
D. ORDER BY LIST_PRICE
E. ORDERBYLIST_PRICE–MIN_PRICE
Answer: ACE
Q127. 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;
Answer: A
Q128. View the Exhibit and examine the structure of the ORDERS table:
The ORDER_ID column has the PRIMARY KEY constraint and CUSTOMER_ID has the NOT NULL constraint.
Evaluate the following statement:
INSERT INTO (SELECT order_id.order_date.customer_id FROM ORDERS WHERE order_total = 1000 WITH CHECK OPTION) VALUES (13, SYSDATE, 101);
What would be the outcome of the above INSERT statement?
A. It would execute successfully and the new row would be inserted into a new temporary table created by the subquery.
B. It would execute successfully and the ORDER_TOTAL column would have the value 1000 inserted automatically in the new row.
C. It would not execute successfully because the ORDER_TOTAL column is not specified in the SELECT list and no value is provided for it.
D. It would not execute successfully because all the columns from the ORDERS table should have been included in the SELECT list and values should have been provided for all the columns.
Answer: C