Oracle 1Z0-007 Value Pack (Frequently Bought Together)

Which two statements about subqueries are true? (Choose two.)

• A. A single row subquery can retrieve data from more than one table.
• B. A SQL query statement cannot display data from table B that is referred to in its subquery, unless table B is included in the main query's FROM clause.
• C. A single row subquery cannot be used in a condition where the LIKE operator is used for comparison.
• D. A single row subquery can retrieve data from only one table.
• E. A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause.
• F. A multiple-row subquery cannot be used in a condition where the LIKE operator is used for comparison.

Which two statements accurately describe a role? (Choose two.)

• A. A role can have a maximum of 100 privileges contained in it.
• B. A role can be given to a maximum of 1000 users.
• C. A role is a named group of related privileges that can be granted to the user.
• D. A user can have access to a maximum of 10 roles.
• E. Privileges are given to a role by using the CREATE ROLE statement.
• F. A user can have access to several roles, and several users can be assigned the same role.

Evaluate the SQL statement:
TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)

• A. An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.
• B. It does not release the storage space used by the table.
• C. You can roll back the deletion of rows after the statement executes.
• D. It releases the storage space used by the table.
• E. You can NOT roll back the deletion of rows after the statement executes.
• F. You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT table

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:
EMPLOYEES EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE
NEW_EMPLOYEES EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(60) Which MERGE statement is valid?

• A. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• B. MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• C. MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES (e.employee_id, e.first_name ||', '||e.last_name);
• D. MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET c.name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT VALUES(e.employee_id, e.first_name ||', '||e.last_name);

Evaluate these two SQL statements:
SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC; What is true about them?

• A. The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.
• B. There is no need to specify DESC because the results are sorted in descending order by default.
• C. The two statements produce identical results.
• D. The second statement returns a syntax error.