Oracle Updated 1z0-071 Exam Questions and Answers by andrei

Primary and foreign key constraints are foundational to relational database design:

Option A: Incorrect. Both primary and foreign key constraints can be defined at the column or table level.

Option B: Incorrect. Foreign key columns do not need to have the same names as the corresponding primary key columns in the parent table. They must be of the same data type and size.

Option C: Incorrect. If a foreign key constraint is enforced without ON DELETE CASCADE, deleting the parent row will either prevent the delete (due to constraint) or require a prior deletion or update of the child rows.

Option D: Correct. A table can indeed have only one primary key, which uniquely identifies each row, but it can have multiple foreign keys linking to primary keys of different tables.

Option E: Correct. Both types of keys can be defined at either level, providing flexibility in how constraints are applied and enforced.

Option F: Incorrect. A table can have multiple foreign keys as stated, each referencing a different parent table.

Option G: Correct. If the ON DELETE CASCADE option is set for a foreign key, then deleting a parent row will automatically delete the corresponding child rows, maintaining referential integrity.

When creating an external table, which allows you to access data in a flat file as though it is a table inside the database, certain clauses are required:

A. the ACCESS PARAMETERS clause: This clause specifies the parameters required by the access driver to read the data files.

D. the LOCATION clause: This clause specifies the location of the data files that make up the external table.

References:

Oracle Database SQL Language Reference 12c, particularly the sections detailing the creation and management of external tables.

A. True. The GRANT statement with the WITH ADMIN OPTION allows the grantee to not only modify the specified columns but also to grant that permission to other users.

B, C, D, E, and F are incorrect because:

B and C are incorrect due to the syntax; the correct syntax does not include the table name before each column when listing multiple columns in a GRANT statement.

D and E do not include the necessary WITH GRANT OPTION which is required to enable the grantee to grant the permission to others.

F is incorrect because the keyword WITH ADMIN OPTION should be used to allow the grantee to further grant the permission to others; WITH GRANT OPTION is correct in the context of object privileges.

To include employees with no manager in the query results, a LEFT OUTER JOIN should be used. This type of join returns all records from the left table (employees), and the matched records from the right table (managers). The result is NULL from the right side if there is no match.

Here's the modified query:

SELECT e.last_name AS emp, m.last_name AS mgr FROM employees e LEFT OUTER JOIN managers m ON e.manager_id = m.employee_id ORDER BY emp;

This ensures that even if an employee does not have a manager (i.e., e.manager_id is NULL or there is no corresponding m.employee_id), that employee will still be included in the results.