Online Access Free 1Z0-047 Practice Test

Which statement correctly grants a system privilege?

• A.GRANT EXECUTE ON prod TO PUBLIC;
• B.GRANT CREATE TABLE
  TO used,user2;
• C.GRANT CREATE SESSION
  TO ALL;
• D.GRANT CREATE VIEW ON tablel TO User1;

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 of statement 1 would neither display the employee with MANAGERJD 108 nor any employee 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.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.
• D.Statement 2 would not execute because the WHERE clause condition is not allowed in a statement that has the START WITH clause.

Evaluate the CREATE TABLE statement:
CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY,
product_name VARCHAR2(15));
Which statement is true regarding the PROD_ID_PK constraint?

• A.It would be created only if a unique index is manually created first.
• B.It would be created and would use an automatically created unique index.
• C.It would be created and remains in a disabled state because no index is specified in the command.
• D.It would be created and would use an automatically created nonunique index.

View the Exhibit and examine the structure of the ORDERS table. The ORDER_ID column is the PRIMARY KEY in the ORDERS table. Evaluate the following
CREATE TABLE command:
CREATE TABLE new_orders(ord_id, ord_date DEFAULT SYSDATE, cus_id)
AS
SELECT order_id.order_date.customer_id
FROM orders;
Which statement is true regarding the above command?

• A.The NEW_ORDERS table would get created and only the NOT NULL constraint defined on the specified columns would be passed to the new table.
• B.The NEW_ORDERS table would get created and all the constraints defined on the specified columns in the ORDERS table would be passed to the new table.
• C.The NEW_ORDERS table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
• D.The NEW_ORDERS table would not get created because the DEFAULT value cannot be specified in the column definition.

Which two statements best describe the benefits of using the WITH clause? (Choose two.)

• A.It enables users to reuse the same query block in a SELECT statement, if it occurs more than once in a complex query.
• B.It enables users to store the results of a query permanently.
• C.It can improve the performance of a large query by storing the result of a query block having the WITH clause in the user's temporary tablespace.
• D.It enables users to store the query block permanently in the memory and use it to create complex queries.