Oracle Database SQL Expert - 1Z1-047 FREE EXAM DUMPS QUESTIONS & ANSWERS]

Question 1

View the Exhibit and examine the description of the ORDER_ITEMS table. The following SQL statement was written to retrieve the rows for the PRODUCT_ID that has a
UNIT_PRICE of more than 1,000 and has been ordered more than five times: SELECT product_id, COUNT(order_id) total, unit_price
FROM order_items
WHERE unit_price>1000 AND COUNT(order_id)>5
GROUP BY product_id, unit_price;
Which statement is true regarding this SQL statement?

A. The statement would not execute because the WHERE clause should have the OR logical operator instead of AND. B. The statement would execute and give you the desired result. C. The statement would not execute because in the SELECT clause, the UNIT_PRICE column is placed after the column having the aggregate function. D. The statement would not execute because the aggregate function is used in the WHERE clause.

Discussion 0

Correct Answer: D Vote an answer

Question 2

View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite PRIMARY KEY is (ORD_NO, ITEM_NO).
Which two CREATE INDEX statements are valid? (Choose two.)

A. CREATE INDEX ord_idx
ON ord_items(item_no); B. CREATE INDEX ord_idx
ON ord,ord_items(ord_no, ord_date,qty); C. CREATE INDEX ord_idx
ON ord(ord_no); D. CREATE INDEX ord_idx
ON ord_items(ord_no);

Discussion 0

Correct Answer: A,D Vote an answer

Question 3

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 WHERE clause cannot be used in a subquery embedded in an INSERT statement C. because the VALUES clause cannot be used in an INSERT with a subquery D. because the total number of columns in the NEW ORDERS table does not match the total number of columns in the ORDERS table

Discussion 0

Correct Answer: C Vote an answer

Question 4

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 index created on the MGR_ID column would continue to exist until the DROP UNUSED COLUMNS command is executed. 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 synonym existing on the EMP table would have to be re-created.

Discussion 0

Correct Answer: B Vote an answer

Question 5

View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables. You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by
QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as
well as for a PRODUCT ID for the last six months.
Which SQL statement would you execute to get the desired output?

A. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id, oi.product_id)
WHERE MONTHS_BETWEEN(order_ date,SYSDATE)<=6; B. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id, oi.product_id)
WHERE MONTHS_BETWEEN(order_ date,SYSDATE)>=6; C. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id, oi.product_id)
HAVING MONTHS_BETWEEN(order_ date,SYSDATE)<=6; D. SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Total
FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
WHERE MONTHS_BETWEEN(order date,SYSDATE)<=6
GROUP BY ROLLUP (o.customer_id, oi.product_id);

Discussion 0

Correct Answer: D Vote an answer

Question 6

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 are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses. B. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause. C. The INSERT statement would give an error because the ELSE clause is not present for support in case none of the WHEN clauses are true. D. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.

Discussion 0

Correct Answer: B Vote an answer

Question 7

Which statement is true regarding Flashback Version Query?

A. It retrieves all versions including the deleted as well as subsequently reinserted versions of the rows. B. It will return an error if the undo retention time is less than the lower bound time or SCN specified. C. It returns versions of rows only within a transaction. D. It can be used in subqueries contained only in a SELECT statement.

Discussion 0

Correct Answer: A Vote an answer

Question 8

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, TO_YMINTERVAL(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, SYSDATE + warranty_period FROM product_information;

Discussion 0

Correct Answer: D Vote an answer

Question 9

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 statement would not execute because the table alias prefix is not used in the USING clause. B. The statement would not execute because all the columns in the SELECT clause are not prefixed with table aliases. C. The statement would not execute because table aliases are not allowed in the JOIN clause. D. The statement would not execute because the column part of the USING clause cannot have a qualifier in the SELECT list.

Discussion 0

Correct Answer: D Vote an answer

Question 10

View the Exhibit and examine the structure of ORD and ORD_ITEMS tables.
In the ORD table, the PRIMARY KEY is ORD_NO and in the ORD_ITEMS tables the composite
PRIMARY KEY is (ORD_NO, ITEM_NO).
Which two CREATE INDEX statements are valid? (Choose two.)

A. CREATE INDEX ord_idx
ON ord_items(item_no); B. CREATE INDEX ord_idx
ON ord,ord_items(ord_no, ord_date,qty); C. CREATE INDEX ord_idx
ON ord(ord_no); D. CREATE INDEX ord_idx
ON ord_items(ord_no);

Discussion 0

Correct Answer: A,D Vote an answer

Question 11

View the Exhibit and examine the details for the CATEGORIES_TAB table. Evaluate the following incomplete SQL statement:
SELECT category_name ,category_description FROM categories_tab You want to display only the rows that have 'harddisks' as part of the string in the CATEGORY_DESCRIPTION column.
Which two WHERE clause options can give you the desired result? (Choose two.)

A. WHERE REGEXPJJKE (category_description, '[^Hlhard+.s]'); B. WHERE REGEXPJJKE (category_description, '^H|hard+.s'); C. WHERE REGEXPJJKE (category_description, 'hard+.s'); D. WHERE REGEXPJJKE (category_description, '^H|hard+.s$');

Discussion 0

Correct Answer: B,C Vote an answer

Question 12

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, total_orders; B. SELECT sales_rep_id, COUNT(order_id) total_orders
FROM orders; C. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
GROUP BY sales_rep_id; D. SELECT sales_rep_id, COUNT(order_id) total_orders FROM orders
WHERE sales_rep_id IS NOT NULL;

Discussion 0

Correct Answer: C Vote an answer

Question 13

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

A. You cannot specify conditions when you retrieve data by using a hierarchical query. B. It is 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. It is possible to retrieve data only in top-down hierarchy.

Discussion 0

Correct Answer: B,C Vote an answer