1z0-047 | A Review Of Accurate 1z0-047 exam
Q61. 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
Q62. View the Exhibit and examine the description of the ORDERS table.
Your manager asked you to get the SALES_REP_ID and the total numbers of orders placed by each of the sales representatives. Which statement would provide the desired result?
A. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
GROUP BY sales_rep_id;
B. SELECT sales_rep_id, COUNT(order_id)total_orders FROM orders GROUP BY sales_rep_id, total_orders;
C. SELECT sales_rep_id, COUNT(order_id)total_orders FROM orders;
D. SELECT sales_rep_id, COUNT(order_id)total_orders FROM orders WHERE sales_rep_id IS NOT NULL;
Answer: A
Q63. 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
Q64. Which statement is true regarding Flashback Version Query?
A. It returns versions of rows only within a transaction.
B. It can be used in subqueries contained only in a SELECT statement.
C. It will return an error if the undo retention time is less than the lower bound time or SCN specified.
D. It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows.
Answer: D
Q65. 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;
Answer: D
Q66. You need to load information about new customers from the NEW_CUST table into the tables CUST and CUST_SPECIAL If a new customer has a credit limit greater than 10,000, then the details have to be inserted into CUST_SPECIAL All new customer details have to be inserted into the CUST table. Which technique should be used to load the data most efficiently?
A. external table
B. the MERGE command
C. the multi table INSERT command
D. INSERT using WITHCHECK OPTION
Answer: C
Q67. View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
There are some products listed in the PRODUCT_INFORMATION table that have no value in the
LIST_PRICE column. You issued the following SQL statement to find out the PRODUCT_NAME
for these products:
SELECT product_name, list_price
FROM product_information WHERE list_price = NULL;
The query returns no rows. What changes would you make in the statement to get the desired result?
A. Change the WHERE clause to WHERE list_price = 0
B. Change the WHERE clause to WHERE list_price = ''.
C. Change the WHERE clause to WHERE list_price IS NULL.
D. In the WHERE clause, enclose NULL within single quotation marks.
E. In the WHERE clause, enclose NULL within double quotation marks.
Answer: C
Q68. Which two statements are true regarding subqueries? (Choose three.)
A. The ORDER BY clause can be used in the subquery.
B. A subquery can be used in the FROM clause of a SELECT statement.
C. If the subquery returns NULL, the main query may still return result rows.
D. A subquery can be placed in a WHERE clause, GROUP BY clause, or a HAVING clause.
E. Logical operators, such as AND, OR and NOT, cannot be used in the WHERE clause of a subquery.
Answer: ABC
Q69. View the Exhibit and examine the structure of the ORDERS table. The columns ORDER_MODE and ORDER_TOTAL have the default values 'direct' and 0 respectively.
Which two INSERT statements are valid? (Choose two.)
A. INSERT INTO orders
VALUES (1, O9-mar-2007', 'online',",1000);
B. INSERT INTO orders
(order_id ,order_date ,order_mode,
customer_id ,order_total)
VALUES(1 ,TO_DATE(NULL), 'online', 101, NULL);
C. INSERT INTO
(SELECT order_id ,order_date .customer_id
FROM orders)
VALUES (1,O9-mar-2007', 101);
D. INSERT INTO orders
VALUES (1,09-mar-2007', DEFAULT, 101, DEFAULT);
E. INSERT INTO orders
(order_id,order_date ,order_mode .order_total)
VALUES (1 ,'10-mar-2007','online',1000);
Answer: CD
Q70. View the Exhibit and examine the description of EMPLOYEES and DEPARTMENTS tables.
You want to display the EMPLOYEE_ID, LAST_NAME, and SALARY for the employees who get
the maximum salary in their respective departments. The following SQL statement was written:
WITH
SELECT employee_id, last_name, salary
FROM employees
WHERE (department_id, salary) = ANY (SELECT*
FROM dept_max)
dept_max as (SELECT d.department_id, max(salary)
FROM departments d JOIN employees j
ON (d. department_id = j. department_id)
GROUP BY d. department_id);
Which statement is true regarding the execution and the output of this statement?
A. The statement would execute and give the desired results.
B. The statement would not execute because the = ANY comparison operator is used instead of=.
C. The statement would not execute because the main query block uses the query name before it is even created.
D. The statement would not execute because the comma is missing between the main query block and the query name.
Answer: C