Latest Oracle 1Z1-047 Actual Free Exam Questions & Community Discussion, Page 2

Question #12

Evaluate the following ALTER TABLE statement:
ALTER TABLE orders SET UNUSED order_date;
Which statement is true?

A. The ORDER_DATE column should be empty for the ALTER TABLE command to execute successfully.

B. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.

C. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.

D. The DESCRIBE command would still display the ORDER_DATE column.

Discussion 0

Correct Answer: B Vote an answer

Question #13

View the Exhibit and examine the structure for the ORDERS and ORDER_ITEMS tables.
You want to display ORDER_ID, PRODUCT_ID, and TOTAL (UNIT_PRICE multiplied by QUANTITY) for all the orders placed in the last seven days.
Which query would you execute?

A. SELECT orde_id, product_id, unit_price*quantity "TOTAL"
FROM order_items oi JOIN orders o
ON (o.order_id=oi. order_id)
WHERE o.order_date>=SYSDATE-7;

B. SELECT o.order_id,oi.product_id, oi.unit_price*oi.quantity "TOTAL"
FROM order_items oi JOIN orders o
USING (order_id)
WHERE o.order_date>=SYSDATE-7;

C. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL"
FROM orde_items oi JOIN orders o
ON (o.order_id=oi. order_id)
WHERE o. order date>=SYSDATE-7;

D. SELECT o.order_id, oi.product_id, oi.unit_price*oi.quantity "TOTAL"
FROM order_items oi JOIN orders o
WHERE o.order_date>=SYSDATE-7
ON (o.order_id=oi. order_id);

Discussion 0

Correct Answer: C Vote an answer

Question #14

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,09-mar-2007', DEFAULT, 101, DEFAULT);

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 orders
VALUES (1, O9-mar-2007', 'online',",1000);

D. INSERT INTO orders
(order_id ,order_date ,order_mode .order_total)
VALUES (1 ,'10-mar-2007','online',1000);

E. INSERT INTO
(SELECT order_id ,order_date .customer_id
FROM orders)
VALUES (1,O9-mar-2007', 101);

Discussion 0

Correct Answer: A,E Vote an answer

Question #15

View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt FROM (SELECT product_id, COUNT (*) item_cnt FROM order_items GROUP BY product_id) i RIGHT OUTER JOIN products p ON i.product_id = p.product_id; What would happen when the above statement is executed?

A. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

B. The statement would not execute because inline views and outer joins cannot be used together.

C. The statement would not execute because the GROUP BY clause cannot be used in the inline view.

D. The statement would execute successfully to produce the required output.

Discussion 0

Correct Answer: D Vote an answer

Question #16

View the Exhibit and examine the structure of the EMPLOYEES table.
You want to retrieve hierarchical data of the employees using the top-down hierarchy. Which SQL clause would let you choose the direction to walk through the hierarchy tree?

A. WHERE

B. START WITH

C. HAVING

D. GROUP BY

E. CONNECT BY PRIOR

Discussion 0

Correct Answer: E Vote an answer

Question #17

View the Exhibit and examine the data in the DEPARTMENTS tables.
Evaluate the following SQL statement:
SELECT department_id "DEPT_ID", department_name , 'b' FROM departments WHERE department_id=90 UNION SELECT department_id, department_name DEPT_NAME, 'a' FROM departments WHERE department_id=10 Which two ORDER BY clauses can be used to sort the output of the above statement? (Choose two.)

A. ORDER BY 3;

B. ORDER BY 'b';

C. ORDER BY DEPT NAME;

D. ORDER BY DEPT_ID;

Discussion 0

Correct Answer: A,D Vote an answer

Question #18

View the Exhibit and examine DEPARTMENTS and the LOCATIONS tables.
Evaluate the following SOL statement:
SELECT location_id, city FROM locations I WHERE NOT EXISTS (SELECT location_id FROM departments WHERE location_id <> I. location_id);
This statement was written to display LOCATION_ID and CITY where there are no departments located. Which statement is true regarding the execution and output of the command?

A. The statement would execute but it will return zero rows because the WHERE clause in the inner query should have the = operator instead of <>.

B. The statement would not execute because the WHERE clause in the outer query is missing the column name for comparison with the inner query result.

C. The statement would execute and would return the desired results.

D. The statement would not execute because the = comparison operator is missing in the WHERE clause of the outer query.

Discussion 0

Correct Answer: A Vote an answer

Question #19

View the Exhibit and examine the details of the EMPLOYEES table.
Evaluate the following SQL statements:
Statement 1:
SELECT employee_id, last_name, job_id, manager_id FROM employees START WITH
employee_id = 101
CONNECT BY PRIOR employee_id = manager_id AND manager_id != 108;
Statement 2:
SELECT employee_id, last_name, job_id, manager_id
FROM employees
WHERE manager_id != 108
START WITH employee_id = 101
CONNECT BY PRIOR employee_id = manager_id;
Which two statements are true regarding the above SQL statements? (Choose two.)

A. The output for statement 2 would not display the employee with MANAGERJD 108 but it would display all the employees below him or her in the hierarchy.

B. The output for statement 1 would display the employee with MANAGERJD 108 and all the employees below him or her in the hierarchy.

C. Statement 2 would not execute because the WHERE clause condition is not allowed in a statement that has the START WITH clause.

D. The output of statement 1 would neither display the employee with MANAGERJD 108 nor any employee below him or her in the hierarchy.

Discussion 0

Correct Answer: A,D Vote an answer

Question #20

Which two statements are true regarding roles? (Choose two.)

A. A role can be granted to PUBLIC.

B. The REVOKE command can be used to remove privileges but not roles from other users.

C. A role can be granted to itself.

D. Roles are named groups of related privileges that can be granted to users or other roles.

E. A user can be granted only one role at any point of time.

Discussion 0

Correct Answer: A,D Vote an answer

Question #21

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 (SELECT* FROM order_items i.orders o
WHERE i.order_id = o.order_id AND order_status IN (0,1));

C. DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders WHERE order_status in (0,1));

D. 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));

Discussion 0

Correct Answer: B,C,D Vote an answer

Question #22

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.
ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the
ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.
Which DELETE statement would execute successfully?

A. DELETE orders o, order_items i
WHERE o.order id = i.order id;

B. DELETE FROM orders WHERE (SELECT order_id FROM order_items);

C. DELETE orders WHERE order_total < 1000;

D. DELETE order_id FROM orders WHERE order_total < 1000;

Discussion 0

Correct Answer: C Vote an answer

Oracle 1Z1-047 Actual Free Exam Questions & Community Discussion

Download Free Oracle 1Z1-047 Demo