Try Oracle : 1Z1-047 valid & accurate questions and answers

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 views created on the EMP table that include the MGR_ID column would have to be dropped and re-created.
• C. Any constraints defined on the MGR_ID column would be removed by the above command.
• D. Any synonym existing on the EMP table would have to be re-created.

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 execute successfully to produce the required output.
• D. The statement would not execute because the GROUP BY clause cannot be used in the inline view.

Which three statements are true regarding the WHERE and HAVING clauses in a SQL statement? (Choose three.)

• A. The HAVING clause is used to exclude one or more aggregated results after grouping data.
• B. The HAVING clause conditions can use aliases for the columns.
• C. The HAVING clause conditions can have aggregate functions.
• D. WHERE and HAVING clauses cannot be used together in a SQL statement.
• E. The WHERE clause is used to exclude rows before the grouping of data.

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 order_id FROM orders WHERE order_total < 1000;
• C. DELETE FROM orders WHERE (SELECT order_id FROM order_items);
• D. DELETE orders WHERE order_total < 1000;

Given below is a list of datetime data types and examples of values stored in them in a random
order:
Datatype Example
1) INTERVAL YEAR TO MONTH a) 2003-04-15 8:00:00 -8:00'
2) TIMESTAMP WITH LOCAL TIME ZONE b) '-K)6 03:30:16.000000'
3) TIMESTAMP WITH TIME ZONE c) '17-JUN-03 12.00.00.000000 AM'4)INTERVAL DAY TO
SECOND d) '402-00'
Identify the option that correctly matches the data types with the values.

• A. 1-b.2-a.3-c.4-d
• B. 1-d.2-c.3-b.4-a
• C. 1-b.2-a, 3-d, 4-c
• D. 1-d.2-c.3-a.4-b