1Z1-047 by Oracle Actual Free Exam Q&As

Question 1

View the Exhibit and examine the structure of the ORDERS table. Which task would require subqueries?

A. displaying the number of orders whose order total is more than the average order total for all online orders B. displaying the number of orders that have order mode online and order date in 1999 C. displaying the order total for sales representative 161 in the year 1999 D. displaying the total order value for sales representatives 161 and 163

Discussion 0

Correct Answer: A Vote an answer

Question 2

View the Exhibit and examine the description of the PRODUCT_INFORMATION table.
Which SQL statement would retrieve from the table the number of products having LIST_PRICE
as NULL?

A. SELECT COUNT(NVL(list_price, 0))
FROM product_information
WHERE list_price IS NULL; B. SELECT COUNT(list_price)
FROM product_information
WHERE list_price IS NULL; C. SELECT COUNT(list_price)
FROM product_information
WHERE list_price = NULL; D. SELECT COUNT(DISTINCT list_price)
FROM product_information
WHERE list_price IS NULL;

Discussion 0

Correct Answer: A Vote an answer

Question 3

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)
HAVING 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
WHERE MONTHS_BETWEEN(order date,SYSDATE)<=6
GROUP BY ROLLUP (o.customer_id, oi.product_id); 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
GROUP BY ROLLUP (o.customer_id, oi.product_id)
WHERE MONTHS_BETWEEN(order_ date,SYSDATE)>=6;

Discussion 0

Correct Answer: C Vote an answer

Question 4

View the Exhibit and examine the structure of the EMPLOYEES table. You want to display all employees and their managers having 100 as the MANAGER_ID. You
want the output in two columns: the first column would have the LAST_NAME of the managers
and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?

A. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON e.employee_id = m.manager_id
WHERE m.manager_id=100; B. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
WHERE m.employee_id = e.manager_id AND e.managerjd=100; C. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE e.managerjd=100; D. SELECT m.last_name "Manager", e.last_name "Employee"
FROM employees m JOIN employees e
ON m.employee_id = e.manager_id
WHERE m.manager_id=100;

Discussion 0

Correct Answer: C Vote an answer

Question 5

Given below are the SQL statements executed in a user session:
CREATE TABLE product
(pcode NUMBER(2),
pnameVARCHAR2(10));
INSERT INTO product VALUES(1, 'pen');
INSERT INTO product VALUES (2,'pencil);
SAVEPOINT a;
UPDATE product SET pcode = 10 WHERE pcode = 1;
SAVEPOINT b;
DELETE FROM product WHERE pcode = 2;
COMMIT;
DELETE FROM product WHERE pcode=10;
ROLLBACK TO SAVEPOINT a;
Which statement describes the consequences?

A. No SQL statement would be rolled back. B. Both the DELETE statements and the UPDATE statement would be rolled back. C. Both the DELETE statements would be rolled back. D. Only the second DELETE statement would be rolled back.

Discussion 0

Correct Answer: A Vote an answer

Question 6

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 REGEXP_LIKE (category_description, '^H|hard+.s$'); B. WHERE REGEXP_LIKE (category_description, '^H|hard+.s'); C. WHERE REGEXP_LIKE (category_description, 'hard+.s'); D. WHERE REGEXP_LIKE (category_description, '[^Hlhard+.s]');

Discussion 0

Correct Answer: B,C Vote an answer

Question 7

Which three statements indicate the end of a transaction? (Choose three.)

A. after a ROLLBACK is issued B. after a COMMIT is issued C. after a CREATE statement is issued D. after a SAVEPOINT is issued E. after a SELECT statement is issued

Discussion 0

Correct Answer: A,B,C Vote an answer

Question 8

The following are the steps for a correlated subquery, listed in random order:
1) The WHERE clause of the outer query is evaluated.
2) The candidate row is fetched from the table specified in the outer query.
3) The procedure is repeated for the subsequent rows of the table, till all the rows are processed.
4) Rows are returned by the inner query, after being evaluated with the value from the candidate
row in the outer query. Identify the option that contains the steps in the correct sequence in which
the Oracle server evaluates a correlated subquery.

A. 2,4,1,3 B. 2,1,4,3 C. 4,1,2,3 D. 4,2,1,3

Discussion 0

Correct Answer: A Vote an answer

Question 9

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. INTERSECT B. UNION ALL C. UNION D. MINUS

Discussion 0

Correct Answer: A Vote an answer

Question 10

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 not execute because the main query block uses the query name before it is even created. B. The statement would not execute because the comma is missing between the main query block and the query name. C. The statement would execute and give the desired results. D. The statement would not execute because the = ANY comparison operator is used instead of=.

Discussion 0

Correct Answer: A Vote an answer

Question 11

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 ,'IO-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 ,'10-mar-2007', 'direct', &&customer_id, 1000); D. INSERT INTO(SELECT o.order_id, o.order_date.o.orde_mode.c.customer_id, o.order_total
FROM 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);

Discussion 0

Correct Answer: A Vote an answer

Oracle Database SQL Expert - 1Z1-047 Exam Practice Test