Oracle Database SQL Expert - 1Z1-047 Exam Practice Test
Evaluate the following DELETE statement:
DELETE FROM orders;
There are no other uncommitted transactions on the ORDERS table.
Which statement is true about the DELETE statement?
DELETE FROM orders;
There are no other uncommitted transactions on the ORDERS table.
Which statement is true about the DELETE statement?
Correct Answer: D
Vote an answer
EMPDET is an external table containing the columns EMPNO and ENAME. Which command would work in relation to the EMPDET table?
Correct Answer: C
Vote an answer
Which mandatory clause has to be added to the following statement to successfully create an
external table called EMPDET?
CREATE TABLE empdet
(empno CHAR(2), ename CHAR(5), deptno NUMBER(4))
ORGANIZATION EXTERNAL
(LOCATION ('emp.daf));
external table called EMPDET?
CREATE TABLE empdet
(empno CHAR(2), ename CHAR(5), deptno NUMBER(4))
ORGANIZATION EXTERNAL
(LOCATION ('emp.daf));
Correct Answer: D
Vote an answer
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?
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?
Correct Answer: A
Vote an answer
View the Exhibit and examine the structure of the ORDER_ITEMS table. Examine the following SQL statement: SELECT order_id, product_id, unit_price
FROM order_jtems
WHERE unit_price = (SELECT MAX(unit_price)
FROM order_items
GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per
ORDER_ID.
What correction should be made in the above SQL statement to achieve this?
FROM order_jtems
WHERE unit_price = (SELECT MAX(unit_price)
FROM order_items
GROUP BY order_id);
You want to display the PRODUCT_ID of the product that has the highest UNIT_PRICE per
ORDER_ID.
What correction should be made in the above SQL statement to achieve this?
Correct Answer: B
Vote an answer
View the Exhibit and examine the details of the EMPLOYEES table.
You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100.
Which SQL clauses would you require to accomplish the task? (Choose all that apply.)
You want to generate a hierarchical report for all the employees who report to the employee whose EMPLOYEE_ID is 100.
Which SQL clauses would you require to accomplish the task? (Choose all that apply.)
Correct Answer: C,D,E
Vote an answer
Which three statements are true regarding group functions? (Choose three.)
Correct Answer: A,B,D
Vote an answer
Which statements are true regarding the usage of the WITH clause in complex correlated subqueries? (Choose all that apply.)
Correct Answer: A,B,C
Vote an answer
View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables.
Evaluate the following SQL statement:
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount"
FROM orde_items oi JOIN orders o
ON oi.order_id = o.order_id
GROUP BY CUBE (o.customer_id, oi.product_id);
Which three statements are true regarding the output of this SQL statement? (Choose three.)
Evaluate the following SQL statement:
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount"
FROM orde_items oi JOIN orders o
ON oi.order_id = o.order_id
GROUP BY CUBE (o.customer_id, oi.product_id);
Which three statements are true regarding the output of this SQL statement? (Choose three.)
Correct Answer: C,D,E
Vote an answer