Oracle 1z0-071 Exam Made Easy: Step-by-Step Preparation Guide

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.

Examining the execution of SELECT statements in Oracle SQL requires understanding correct syntax, especially regarding aliases and string literals:

Option A: Incorrect. Although the syntax might seem plausible, using DATE as an alias without quotes can lead to ambiguity because DATE is a reserved keyword in Oracle SQL. This might cause an error or unexpected behavior unless it is enclosed in double quotes.

Option B: Incorrect. Single quotes are used for string literals in SQL, not for aliasing column names. Using single quotes for aliasing, as in 'CUSTOMER-ID', 'DUES', is syntactically incorrect and will result in an error.

Option C: Correct. This statement uses valid syntax with no reserved keywords as aliases and no misuse of string literal notation. Aliases are provided without quotes, which is acceptable as long as they are not reserved words or contain special characters.

Option D: Correct. It correctly uses double quotes for aliasing, which is necessary when using reserved words like "DATE" or special characters. This is a proper use of SQL syntax for aliasing in Oracle.

Option E: Incorrect. This statement attempts to use aliases with hyphens without enclosing them in double quotes, which leads to syntax errors. Also, customer id seems to be missing an underscore or another connector to properly reference a column name, indicating a typo or error.

For these queries, ensuring correct alias syntax and avoiding reserved keywords without appropriate quoting are key elements for successful execution.

A. Only the second statement will display departments with no employees. This is true because the second statement uses a LEFT JOIN to include all departments from the departments table, even those without matching entries in the employees table. When there are no employees in a department, the AVG(salary) will be NULL, and the department will still be displayed1.

C. Both statements will execute successfully if you add e.avg_sal to the select list. This is correct. Both statements calculate e.avg_sal as an average salary, either through a subquery or a join operation. Adding e.avg_sal to the select list will display the average salary alongside the departments. However, it’s important to note that the first statement will not display departments with no employees because it does not use a join that would allow for NULL values from the employees table2.

References:

Understanding SQL JOINs - Stack Overflow1.

Oracle Documentation on JOIN Operations2.

Note: The other options are incorrect because:

B. The first statement will not display departments with no employees since it does not use a join that includes departments without matching employee records.

D. As explained, the first statement will not display departments with no employees.

E. There is a typo in the option; it should be E.AVG_SAL. Even if corrected, the first statement alone would not execute successfully because it does not include a join to bring in the avg_sal value.

F. The second statement will display departments with no employees, but the first statement will not, so this option is incorrect.

The provided DELETE statement uses a correlated subquery to determine which rows should be deleted from the EMPLOYEES table.

A. The subquery is indeed executed for every row in the EMPLOYEES table. This is because it references e.employee_id, which is a column from the outer query, making it a correlated subquery.

B. The subquery is a correlated subquery, as explained above.

C. The subquery is executed for each row, not before the DELETE statement.

D. Not all existing rows in the EMPLOYEES table are deleted, only those that have a corresponding employee_id in the EMP_HISTORY table.

E. The DELETE statement executes successfully even if the subquery selects multiple rows because the EXISTS condition only checks for the presence of rows, not their count.

References:

Oracle Database SQL Language Reference 12c Release 1 (12.1), DELETE Statement

Oracle Database SQL Language Reference 12c Release 1 (12.1), Subquery Factoring

Oracle Database SQL Language Reference 12c Release 1 (12.1), Correlated Subqueries

A. False. Constraints are enforced during INSERT and UPDATE operations, and by the nature of their definition, they impact DELETE operations as well (in the case of referential constraints).

B. False. A column with a foreign key constraint can contain a NULL value unless it is also constrained to be NOT NULL.

C. False. Not all constraints can be defined at the column level. For example, some constraints such as FOREIGN KEY constraints are more commonly defined at the table level.

D. True. A constraint can be disabled regardless of whether the constrained column contains data. However, re-enabling the constraint requires that all data satisfy the constraint rules.

E. True. A column with a UNIQUE constraint can indeed contain a NULL value, as NULL is considered not equal to any value, including itself. This means that multiple rows with NULL values do not violate the UNIQUE constraint.

References:

Oracle Documentation on Constraints: https://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52271

Oracle Documentation on Enabling and Disabling Constraints: https://docs.oracle.com/database/121/ADMIN/clustrct.htm#ADMIN13508

A: True. The DESCRIBE command can indeed be used from SQL Developer as well as other Oracle database tools such as SQL*Plus. This command is used to display the structure of a table, view, or other object, showing information such as column names, data types, and whether a column is nullable.

B: True. The DESCRIBE command can be used to display the structure of an existing view, showing similar information as it would for a table. This includes the columns, their data types, and other pertinent details.

D: True. When DESCRIBE is used, it does display the NOT NULL constraints for columns that have this constraint. This is part of the basic information about the structure of a table or view that can help developers understand the requirements of the data stored therein.

The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column. Here are the evaluations of the options:

A: The pattern ' % a % ’ will match any customer names that contain the letter 'a' anywhere in the name, not necessarily those starting with 'Ma'.

B: This is correct as 'Ma%' will match any customer names that start with 'Ma'.

C: In SQL, the wildcard character is '%' not '*', therefore, the pattern 'Ma' is incorrect.

D: This is the correct syntax. 'UPPER (customer_ name ) LIKE 'MA%'' will match customer names starting with 'Ma' in a case-insensitive manner.

E: Again, '*' is not a valid wildcard character in SQL.

F: The character '&' is not a wildcard in SQL.

G: The operator 'KIKE' is a typo, and '.Ma' is not valid SQL pattern syntax.