1z0-071 Sample Questions Answers

In Oracle SQL, the WITH CHECK OPTION clause in a CREATE VIEW statement ensures that all data manipulation statements performed through the view must conform to the view's defining query.

Option A: DELETE FROM emp80 WHERE department_id = 90;

This will violate the CHECK constraint because the WHERE clause condition does not meet the view's restriction (department_id = 80). This statement attempts to delete rows that the view is not supposed to have access to (since it should only include rows where department_id is 80).

Options B, C, and D do not violate the CHECK constraint because:

Option B: A SELECT statement does not modify data, so it does not violate the CHECK OPTION.

Option C: Selecting rows with department_id = 80 is consistent with the view’s definition.

Option D: There is a syntax error, but assuming it meant to set department_id to 80 where it is currently 90, it still would not violate the CHECK constraint because the CHECK OPTION on a view does not prevent updates that do not change the rows to fall outside the view's filter. However, since department_id is supposed to be 80 as per the view definition, this update operation doesn't make logical sense, as there should be no rows with department_id = 90 to update.

In Oracle Database 12c, it is important to understand the behavior and properties of constraints.

A. This statement is false. A FOREIGN KEY column in a child table does not need to have the same name as the referenced PRIMARY KEY column in the parent table. What is required is that the data type is the same and that the values in the FOREIGN KEY column correspond to values in the PRIMARY KEY column of the parent table.

B. This statement is false. A table cannot have multiple PRIMARY KEY constraints. By definition, a PRIMARY KEY is a unique identifier for a row in a table, and there can only be one such identifier.

C. This statement is false for the same reasons as B; a table can have only one PRIMARY KEY. However, it can have multiple FOREIGN KEY constraints that reference PRIMARY KEYS in other tables.

D. This is true. PRIMARY KEY and FOREIGN KEY constraints can indeed be specified at the column level with the column definition or at the table level with the ALTER TABLE statement.

E. This is true. A table can have only one PRIMARY KEY constraint because it defines a unique row identifier. However, it can have multiple FOREIGN KEY constraints referencing keys in other tables, allowing for complex relational structures.

F. This statement is false. NOT NULL constraints are always defined at the column level, as they apply to individual columns. They cannot be specified at the table level.

References:

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

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

A: 'CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 NOCACHE;' is correct for ensuring unique values without gaps as much as possible by not caching sequence numbers, which might otherwise be lost in a system crash.

B: CYCLE allows the sequence to restart when its max/min value is reached, which does not help with requirement 3 (duplicates are never permitted). Therefore, B is incorrect.

C: This lacks the necessary attributes like START WITH and INCREMENT BY which are crucial to defining a sequence. Thus, statement C is incorrect.

D: 'CREATE SEQUENCE emp_seq START WITH 1 CACHE;' might introduce gaps due to the caching of sequence numbers. This statement is somewhat contrary to requirement 5.

E: 'CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1 CACHE;' will provide continuous unique values, but may include gaps when the cache is lost due to a restart, yet it is more efficient and still generally aligns with the requirements. Hence, statement E is considered correct.

F: This lacks detail and is too ambiguous, lacking the necessary parameters. Therefore, F is incorrect.

The query uses the syntax GROUP | BY 1 which is not correct. The pipe symbol | is not a valid character in the context of the GROUP BY clause. Additionally, when using GROUP BY with a number, it refers to the position of the column in the SELECT list, which should be written without a pipe symbol and correctly as GROUP BY 1. Since the syntax is incorrect, the database engine will return an error.

The provided SQL statement is an update statement that involves a subquery which is correlated to the main query.

A. The subquery is executed before the UPDATE statement is executed. (Incorrect)

This statement is not accurate in the context of correlated subqueries. A correlated subquery is one where the subquery depends on values from the outer query. In this case, the subquery is executed once for each row that is potentially updated by the outer UPDATE statement because it references a column from the outer query (o.customer_id).

B. All existing rows in the ORDERS table are updated. (Incorrect)

Without a WHERE clause in the outer UPDATE statement, this would typically be true. However, the correctness of this statement depends on the actual data and presence of matching customer_id values in both tables. If there are rows in the ORDERS table with customer_id values that do not exist in the CUSTOMERS table, those rows will not be updated.

C. The subquery is executed for every updated row in the ORDERS table. (Correct)

Because the subquery is correlated (references o.customer_id from the outer query), it must be executed for each row to be updated in the ORDERS table to get the corresponding cust_last_name from the CUSTOMERS table.

The || operator is used in Oracle SQL to concatenate strings, and the q quote operator allows you to use alternative quoting mechanisms to avoid conflicts with single quotes within a string.

In options A, B, C, D, E, and F, the q-quote operator is used in different ways to define string literals.

The correct use of the q quote operator requires the format q'[...]', where [...] is the quote delimiter of your choice and can be any single character such as {, [, (, <, or others.

Option D is the correct answer because it follows the correct syntax for the q quote operator and concatenates the literal string correctly with the value of PROD_ID.

Here is the corrected syntax from option D:

SELECT PROD_ID || q'[('s not available)]' "CURRENT AVAILABILITY" FROM product_status WHERE status = 'OUT OF STOCK';

This query will execute successfully and is the only one that correctly uses the q quote operator.

In Oracle SQL, set operations like UNION, UNION ALL, INTERSECT, and MINUS can be used to combine results from different queries:

Option A:

Combining results using UNION ALL followed by ORDER BY will execute successfully because UNION ALL allows duplicate rows and ORDER BY can be used to sort the combined result set.

Option E:

Similar to option A, UNION ALL combines all rows from the two selects and allows ordering of the results.

Option G:

UNION combines the results from two queries and removes duplicates, and ORDER BY can be used to sort the final result set.

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

Option B: You cannot intersect different columns (ORDER_ID with INVOICE_ID).

Option C: Incorrect column names and syntax with ORDER BY.

Option D: ORDER BY cannot be used before a set operator like INTERSECT.

Option F: ORDER BY cannot be used directly after a MINUS operator without wrapping the MINUS operation in a subquery.

External tables using the ORACLE_DATAPUMP access driver have specific characteristics:

C. Creating an external table using the ORACLE_DATAPUMP access driver allows you to select data from other tables, including another external table or a regular table whose rows are stored in database blocks.

A, B, D, and E are incorrect. Specifically:

A is incorrect because creating an external table does not automatically create a directory object; the directory object must exist prior to or be created separately.

B is incorrect as it limits the creation to tables stored in database blocks, which is not a restriction.

D is incorrect because creating an external table does not create a dump file; it reads from an existing dump file created by Data Pump.

E is also incorrect because the dump file is not created as part of the external table creation process.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CREATE TABLE" for External Tables

D: True. This query selects the first name and calculates the due amount as 5% of the credit limit from the customers table where the income level is not null and the credit limit is also not null. The IS NOT NULL operator is used to check for non-null values correctly.

The IS NOT NULL operator is the correct way to check for non-null values in SQL. The <> NULL and != NULL comparisons are incorrect because NULL is not a value that can be compared using these operators; it represents the absence of a value.

There is no due_amount column in the customers table according to the structure provided, so any WHERE clause referencing due_amount is irrelevant and incorrect. The due_amount is a derived column in the SELECT clause.

The correct syntax to check for non-null values is IS NOT NULL, which is used in the WHERE clause of the correct answer.

References:

The use of IS NOT NULL in the WHERE clause to filter out null values is documented in Oracle's SQL reference.

NULL comparisons must be done with IS NULL or IS NOT NULL for correct logical evaluation in SQL.

Single-row functions can be used in the WHERE and HAVING clauses without requiring a subquery. However, aggregate functions, which operate on many rows to give one result per group, cannot be used in a WHERE clause unless a subquery is used because the WHERE clause is processed before the individual rows are aggregated into groups.

A. False. Single-row functions can be directly used in the WHERE clause.

B. False. Single-row functions can be directly used in the HAVING clause.

C. True. Aggregate functions cannot be used in the WHERE clause without a subquery because the WHERE clause filters individual rows before they are aggregated.

D. False. Aggregate functions are intended to be used in the HAVING clause, which is specifically for filtering groups of rows after they have been aggregated, and they do not require a subquery to be used there.

This question appears to be a duplicate of question no. 269, and the correct answers provided there apply here as well.

B. True. A DDL statement automatically commits any outstanding uncommitted transaction in the session. E. True. Exiting SQL*Plus without explicitly committing a transaction will cause the uncommitted transaction to be rolled back, not committed.

A, C, and D are incorrect for the same reasons provided in the explanation for question no. 269.

Assuming the statement involves creating a new table SALES1 from the existing SALES table:

B. SALES1 created with 55,000 rows: If the statement involved creating SALES1 by selecting all rows from SALES (such as with a CREATE TABLE AS SELECT operation), then all 55,000 rows would be copied if no WHERE clause limited the selection.

E. SALES1 has NOT NULL constraints on any selected columns which had those constraints in the SALES table: When creating a table using the CREATE TABLE AS SELECT syntax, any NOT NULL constraints on columns in the original table will also apply to the new table.

Incorrect options:

A: PRIMARY KEY and UNIQUE constraints are not automatically copied with a CREATE TABLE AS SELECT; they must be explicitly redefined.

C: The table is created with rows if the original SELECT statement (assumed here) selected rows.

D: The statement about creating a table with 1 row is incorrect based on the assumed full selection of the original table's rows.

A. The constraint commission < salary implies that COMMISSION must be less than SALARY, so it can have a negative value if the salary is greater than zero, making this statement true.

B. The constraint named CR_MGR is listed as a Referential (R) constraint on the MANAGER column, which implies it is a foreign key that would reference the EMPNO column, making this statement true.

C. The SALARY column is part of a Check (C) constraint CC_COMM that requires the SALARY to be greater than 100, which means it must have a value and cannot be NULL, making this statement true.

D. The presence of a foreign key constraint does not automatically create an index on the foreign key column. While Oracle may implicitly create indexes for primary keys, it does not do so for foreign keys.

E. The constraint deptno > 9 prevents DEPTNO from containing values less than or equal to 9, so it cannot contain the value 1, making this statement false.

F. There is no NOT NULL constraint listed on the DEPTNO column in the constraints image, so it can contain NULL values, making this statement false.

G. There is no indication of a unique constraint on the DNAME column in the constraints image; the constraint DNAME IS NOT NULL only implies that the column cannot contain NULL values, making this statement false.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Constraints"

Oracle Database Concepts, 12c Release 1 (12.1): "Indexes and Index-Organized Tables"

The behavior of data insertion into a table with a specific numeric format in Oracle Database 12c can be explained as follows:

A. INSERT INTO rate_list VALUES (-.9) inserts the value as -.9: This is correct. The value -.9 is within the precision defined by the table column (NUMBER(6,2)), where 6 is the total number of digits (including both sides of the decimal point), and 2 is the number of digits after the decimal point.

E. INSERT INTO rate_list VALUES (0.551) inserts the value as .55: Oracle rounds the number to the nearest value that fits the specified scale of 2. Thus, 0.551 rounds down to 0.55.

References:

Oracle Database SQL Language Reference 12c, particularly sections on data types and numeric precision.

The TRUNCATE TABLE command in Oracle SQL is used to quickly delete all rows from a table:

Option A:

It retains the indexes defined on the table. TRUNCATE does not affect the structure of the table, including its indexes.

Option D:

It retains the integrity constraints defined on the table. TRUNCATE does not remove or disable integrity constraints, except for unenforced foreign keys.

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

Option B: TRUNCATE does not drop triggers; it only removes all rows.

Option C: Flashback Table cannot be used after a TRUNCATE because TRUNCATE is a DDL operation that does not generate undo data for flashback.

Option E: A ROLLBACK cannot be used after a TRUNCATE because TRUNCATE is a DDL command that implicitly commits.

Option F: TRUNCATE may deallocate the space used by the table, depending on the database version and specific options used with the TRUNCATE command.

C. True, when performing a self join, aliases are used to differentiate the same table in different contexts within the same query. This helps in comparing different rows within the same table as if they were in two separate tables.D. True, the ON clause can be used in self joins to specify the conditions on which the join is based. This is a standard method for specifying join conditions in SQL.F. True, a self join can be an outer join, allowing for the inclusion of rows that do not have a matching row in the same table according to the join condition. This enhances the flexibility of data analysis and reporting.

References:

Oracle documentation on joins: Oracle Database SQL Language Reference

Details on self joins and outer joins: Oracle Joins Tutorial

To calculate the length of employment, you would compare the current date to the HIRE_DATE and calculate the difference in years.

A. This is the correct answer. The expression calculates the number of days between the current date (SYSDATE) and the HIRE_DATE, then divides by 365 to convert it to years, and checks if it is greater than 5.

B. SYSTIMESTAMP includes a time component including fractional seconds and time zone information, making this comparison incorrect.

C. There are typos in this option (CUARENT_DATE should be CURRENT_DATE and hire_data should be hire_date). Even with the correct function and column names, CURRENT_DATE returns the current date in the session time zone, not in years.

D. There are typos in this option (SYSNAYW should be SYSDATE). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.

E. This is a repetition of option D with the same issues.

F. There are typos in this option (CUNACV_DATE should be CURRENT_DATE and hire_data should be hire_date). Additionally, dividing by 12 incorrectly assumes that a month is equivalent to a year.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Date Functions"

In SQL, the correct usage of conversion functions is crucial when performing operations on dates. Oracle uses the TO_DATE function to convert a string to a date, and the TO_CHAR function to convert dates or numbers to strings.

Statement C is correct: WHERE order_date > TO_DATE('JUL 10 2018','MON DD YYYY'); is a proper use of the TO_DATE function. It converts the string 'JUL 10 2018' to a date type, with the format 'MON DD YYYY', which is then used to compare with the order_date.

Statements A, B, D, and E are incorrect or misuse conversion functions:

A is incorrect because TO_CHAR is used to convert dates or numbers to strings, not the other way around, and therefore should not be compared with order_date.

B is incorrect because order_date is of type DATE, and you should not compare a DATE with a string without converting it; the TO_CHAR here should be TO_DATE.

D is incorrect because it mixes TO_DATE and TO_CHAR in the same IN list, which should contain date types only.

E is incorrect because TO_DATE should take a string as an argument, not a date returned by ADD_MONTHS.

For the question about the INTERSECT operator in SQL:

A. It processes NULLS in the selected columns: The INTERSECT operator compares two SELECT statements and returns rows that exist in both queries. It considers NULLs during this process, and NULLs in corresponding columns must match for rows to be considered equal. This means if both selected columns in the intersecting queries have NULLs, those rows will be included in the output.

Incorrect options:

B: INTERSECT has higher precedence than UNION and UNION ALL, not lower.

C: It does not ignore NULLs; rather, it processes them, as explained.

D: Multiple INTERSECT operators are indeed possible in the same SQL statement, allowing for complex compound queries.

A: CURRENT_DATE returns the current date in the session time zone, not with a TIMESTAMP WITH TIME ZONE data type.

B: CURRENT_TIMESTAMP returns the current date and time in the session time zone with a TIMESTAMP WITH TIME ZONE data type. This is correct.

C: SYSDATE returns the current date and time from the operating system of the database server in the DATE data type.

D: LOCALTIMESTAMP returns the current date and time in the session time zone with a TIMESTAMP data type, without the TIME ZONE.

The TIMESTAMP WITH TIME ZONE data type and relevant functions are documented in the Oracle Database SQL Language Reference 12c.

A self-join is used to join a table to itself, and here's why the selected answers are correct:

Option B: It can be an outer join.A self-join can indeed be either an inner or an outer join, allowing for more flexibility in how records are matched and returned, especially useful in hierarchical or sequential data scenarios.

Option E: The query must use two different aliases for the table.When performing a self-join, aliases are necessary to distinguish between the different instances of the same table in the query.

Option F: The ON clause can be used.In SQL, the ON clause specifies the conditions that match rows in a self-join, offering a clear and structured way to define how the join works.

Other options are not universally true:

Option A: It must be an inner join. Incorrect because, as explained, outer joins are also possible.

Option C: The ON clause must be used. Incorrect because the WHERE clause might also be used to specify the join condition.

Option D: It must be an equijoin. Incorrect because non-equijoins (like non-equality comparisons) can also be used in self-joins.

The SQL query must find all customers with last names starting with A or B, regardless of case, and a credit limit below 1000:

B. WHERE (INITCAP(cust_last_name) LIKE ‘A%' OR INITCAP(cust_last_name) LIKE ‘B%') AND cust_credit_limit < 1000: The INITCAP function initializes the first letter to uppercase for comparison. However, it should be noted that using INITCAP is not necessary when using the LIKE operator with a wildcard % following a single character, because it will not correctly filter all last names that start with an upper or lower case A or B.

D. WHERE (UPPER(cust_last_name) LIKE 'A%’ OR UPPER(cust_last_name) LIKE ‘B%’) AND cust_credit_limit < 1000: This correctly filters last names beginning with A or B in any case and includes only those with a credit limit below 1000. The UPPER function is used to convert cust_last_name to uppercase before comparison.

References:

Oracle Database SQL Language Reference 12c, especially sections on string functions and conditions.

Regarding primary and foreign key constraints:

A. A table can have only one primary key but multiple foreign keys. This is true. A table is constrained to have only one primary key, which can consist of multiple columns, but can have several foreign keys referencing primary keys in other tables.

C. Primary key and foreign key constraints can be defined at both the column and table level. True. Constraints can be defined inline with the column definition or separately at the end of the table definition.

E. It is possible for child rows that have a foreign key to be deleted automatically from the child table at the time the parent row is deleted. This is also true if the foreign key is defined with the ON DELETE CASCADE option.

Options B, D, F, and G are incorrect:

B is incorrect because if a parent row is deleted, the child rows cannot remain without violating the integrity unless the foreign key is defined with ON DELETE SET NULL or similar behavior.

D is incorrect because both primary and foreign key constraints can be defined at both levels.

F is incorrect as the names of the foreign key columns do not need to match the primary key column names.

G is incorrect as a table can have multiple foreign keys.

C: True. You can add a new NOT NULL column to an existing table, provided you specify a DEFAULT value for the column. This ensures that existing rows in the table can be updated with a default value, thus maintaining the NOT NULL constraint integrity.

E: True. It is permissible to increase the width of a numeric column, provided the change does not truncate existing data. This change is generally safe as it allows for greater values or more precision without affecting existing data.

F: True. The default value of a column can be changed. This action affects subsequent insert operations but does not alter the values of existing rows. Changing the default is useful for adapting tables to evolving business rules without impacting existing data.

To determine which queries will execute successfully, we need to consider the compatibility of the column definitions and the structure of the SELECT statements:

Option A uses the MINUS set operator, which subtracts rows returned by the second SELECT statement from the rows returned by the first. For MINUS to work, the number and the order of columns and their data types must be the same in both queries. This query will not execute successfully because the second SELECT statement does not include all columns from the first SELECT statement, and the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).

Option B uses the UNION ALL set operator, which appends the results of the second SELECT statement to the results of the first. Unlike UNION, UNION ALL does not eliminate duplicate rows. This query will execute successfully because UNION ALL does not require the same data types or sizes, and the result will contain all columns from the first SELECT statement filled with NULL for non-matching columns from the second SELECT statement.

Option C uses the UNION set operator, which requires the same number of columns and compatible data types. This query will not execute successfully because PROD_NAME has different data types (CHAR(4) vs VARCHAR2(10)), and the result of a UNION must have the same number of columns with compatible data types in the two SELECT statements.

Option D uses the UNION set operator as well, but unlike Option C, it does not require a specific data type match because both SELECT statements include all columns and UNION is used (which will automatically handle type conversion where necessary). This query will execute successfully.

Option E uses the INTERSECT set operator, which requires the same number and order of columns and their data types to be identical or compatible. This query will not execute successfully because the data types and sizes of PROD_ID do not match (CHAR(2) vs CHAR(4)).

References:

Oracle Documentation on Set Operators: SQL Language Reference - Set Operators

Oracle Documentation on Data Type Precedence: SQL Language Reference - Data Type Precedence

In conclusion, only Option B and Option D will execute successfully because they adhere to the rules of the UNION ALL and UNION operators respectively, regarding column count and data type compatibility.

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.

E. True. Both & and && can prefix substitution variables in queries as well as in DML statements. & is used for defining a substitution variable that prompts the user every time the variable is encountered. && is used for a persistent substitution variable that, once defined in the session, will not prompt the user again for a value if used subsequently.

Given the statements, the failure to execute would be due to improper SQL syntax or logical structuring of the JOIN clause:

A. SELECT * FROM employees e JOIN department d WHERE e.department_id=d.department_id AND d.department_id=90: This statement will fail because it incorrectly uses the JOIN syntax without an ON clause, which is necessary to specify the condition on which the tables are joined. This statement mistakenly places the join condition in the WHERE clause without using ON.

Correct options (executes successfully):

B: Correctly formulated JOIN with ON and an additional condition in the WHERE clause.

C: Incorporates the filtering directly in the ON clause, which is a valid method to restrict the rows returned by the JOIN based on conditions related to both tables.

D: Although it appears awkward due to the order of conditions in the ON clause, it is syntactically correct.

These answers reflect typical use and constraints in SQL queries and are aligned with Oracle Database 12c standards for SQL syntax and operations.

In a SELECT statement with DISTINCT, the ORDER BY clause can only order by expressions that are part of the SELECT list.

A. quantity alone is not sufficient to replace <clause> as it is not included in the SELECT list after DISTINCT.

B. This option can successfully replace <clause> because both quantity and unit_price are used in the SELECT expression, and thus their individual values are valid for the ORDER BY clause.

C. total_paid is an alias for the expression quantity * unit_price, but it cannot be used in the ORDER BY clause because Oracle does not allow aliases of expressions in DISTINCT queries to be used in ORDER BY.

D. product_id is not included in the SELECT list after DISTINCT and thus cannot be used in ORDER BY.

E. The expression quantity * unit_price is exactly what is selected, so it can replace <clause> and the query will complete successfully.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "ORDER BY Clause"

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.

To display unique promotion costs in each promotion category, the correct queries that can be used are:

C. SELECT DISTINCT promo_category ||' has '|| promo_cost AS COSTS FROM promotions ORDER BY 1;This query concatenates the promo_category with the literal ' has ' and promo_cost, giving a unique string for each combination of promo_category and promo_cost, which is what we are interested in when we want to list unique costs per category.

D. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1;This query selects distinct combinations of promo_category and promo_cost, which is exactly what's required to display unique promotion costs in each category.

Options A, B, and E are incorrect:

A is incorrect because it does not use the DISTINCT keyword to ensure uniqueness.

B has incorrect syntax; the DISTINCT keyword should appear directly after SELECT and applies to all columns, not just one.

E is syntactically incorrect and would not execute because the DISTINCT keyword is not used correctly. It should not appear twice in the SELECT clause.

When crafting SQL queries, especially those involving aggregate functions and GROUP BY clauses, correct syntax and logical order of SQL clauses are crucial. Here's why A and B are the correct answers:

A: Correct. This query uses COUNT() as an aggregate function correctly with a GROUP BY clause. It counts the number of prod_id entries for each product where quantity_sold exceeds 55000, grouped by prod_id.

B: Correct. This query selects prod_id for groups having more than 10 rows where quantity_sold exceeds 55000. The GROUP BY and HAVING clauses are used correctly.

C: Incorrect. The WHERE clause should be placed before the GROUP BY clause. The correct order of clauses is essential for query execution.

D: Incorrect. This query attempts to use an aggregate function (COUNT(*)) in the WHERE clause and improperly in the GROUP BY clause, which is not allowed.

E: Incorrect. Similar to D, it incorrectly attempts to use COUNT(*) in the WHERE clause, which is syntactically incorrect.

D. True, after setting the DEPARTMENT_ID column to UNUSED, you can add a new column with the name DEPARTMENT_ID to the EMPLOYEES table. The UNUSED clause does not delete the column, it only marks it as no longer being used.E. True, once a column is marked as UNUSED, you cannot make updates to it. It becomes inaccessible for DML operations.

A, B, C, and F are not correct because: A. Once a column is set to UNUSED, it is not available for queries. B. The storage space is not immediately released after issuing a COMMIT; instead, the actual removal and space reclamation happen when you subsequently issue the DROP UNUSED COLUMNS operation. C. The DEPARTMENT_ID column is not set to null; instead, it's marked as UNUSED, which means it is no longer available for use. F. UNUSED columns are not placed into the recycle bin; they are just marked for deletion, and space can be reclaimed with the DROP UNUSED COLUMNS command.

References:

Oracle documentation on ALTER TABLE: Oracle Database SQL Language Reference

Understanding ALTER TABLE SET UNUSED: Oracle Database Administrator’s Guide

Roles and privileges in Oracle manage access and capabilities within the database:

Option A: False. Roles are not "owned" in the traditional sense by the user who created them. They exist independently within the Oracle database and are assigned to users.

Option B: False. System privileges can be very granular, affecting specific types of operations or database objects, not always the entire database.

Option C: False. Roles are not owned by the SYS schema but are managed by database security and can be created by any user with sufficient privileges.

Option D: True. A role can indeed contain a combination of several privileges, including other roles, allowing for flexible and layered security configurations.

Option E: True. By default, a user has all object privileges for objects they own (i.e., objects in their schema).

Option F: False. PUBLIC is a special designation that applies to all users; individual privileges granted to PUBLIC cannot be revoked from a single user without revoking them from all users.

Option G: True. PUBLIC is a role granted by default to every user in an Oracle database, providing basic privileges necessary for general usability of the database.

To calculate the date from a given base date with intervals, Oracle allows you to add or subtract intervals from dates. Since the NLS_DATE_FORMAT is set to DD-MM-YYYY, the output is expected to be in that format.

Option B seems to calculate a date that is 3 months and 7 days ahead of October 29, 2019, and then subtracts 360 seconds (which is 6 minutes), resulting in a time that is still within the same day.

Here's how the calculation in option B would work out:

Start date: 29-10-2019

Add 3 months: 29-01-2020

Add 7 days: 05-02-2020

Subtract 360 seconds: Since it's only a few minutes, the date remains 05-02-2020.

However, this does not match the provided result of 02-JAN-2020. We would need to consider the exact amount of time being subtracted or added to find the correct answer.

But upon reviewing the options, they all have various syntax errors such as a missing TO_CHAR function, incorrect quotes, and date formats not matching the session's NLS_DATE_FORMAT. Therefore, we would need to correct these issues to find the right answer.

In Oracle SQL, roles can be granted privileges and other roles, and these privileges can then be passed on to users.

Statement A is correct: GRANT CREATE SEQUENCE TO manager, emp; successfully grants the CREATE SEQUENCE privilege to both the manager and emp roles.

Statement E is correct: GRANT CREATE ANY SESSION, CREATE ANY TABLE TO manager; successfully grants two system privileges to the manager role.

Statements B, C, and D are incorrect for the following reasons:

B is incorrect because the correct syntax to grant object privileges includes separating privileges with commas, not spaces, and the object name should be in singular form (hr.employees).

C is incorrect because you cannot grant a role and a privilege in the same statement.

D is incorrect because the correct syntax for granting object privileges does not include the privilege CREATE TABLE before specifying the object (hr.employees).

C. True, the names of the columns in each SELECT statement of an INTERSECT query do not need to match, as long as the data types and order of the columns correspond.D. True, the INTERSECT operator returns only the rows that are common to both SELECT statements, effectively acting as a set intersection of the results from both queries.

References:

Oracle documentation on INTERSECT operator: Oracle Database SQL Language Reference

Detailed behavior of INTERSECT: Oracle Compound Queries

In Oracle SQL, adding hours to a date can be done using the INTERVAL keyword with appropriate clauses:

Option C: INTERVAL '12' HOUR

Adds exactly 12 hours to a date.

Option D: INTERVAL '720' MINUTE

Since 720 minutes equal 12 hours, this correctly adds 12 hours to a date.

Option F: INTERVAL '11:60' HOUR TO MINUTE

This interval represents 11 hours and 60 minutes, which effectively adds up to 12 hours.

Options A, B, and E contain incorrect syntax or do not match the required addition of 22 hours as specified in the question.

In Oracle Database 12c, regarding a full outer join:

A. It includes rows that are returned by an inner join. This is true. A full outer join includes all rows from both joined tables, matching wherever possible. When there's a match in both tables (as with an inner join), these rows are included.

D. It returns matched and unmatched rows from both tables being joined. This is correct and the essence of a full outer join. It combines the results of both left and right outer joins, showing all rows from both tables with matching rows from the opposite table where available.

Options B, C, and E are incorrect:

B is incorrect because the Oracle join operator (+) is used for syntax in older versions and cannot implement a full outer join by using (+) on both sides. Proper syntax uses the FULL OUTER JOIN keyword.

C is incorrect as a Cartesian product is the result of a cross join, not a full outer join.

E is incorrect because it only describes the scenario of a full anti-join, not a full outer join.

In Oracle 12c SQL, the GROUP BY clause is used to arrange identical data into groups with the GROUP BY expression followed by the SELECT statement. The SUM() function is then used to calculate the sum for each grouped record on a specific column, which in this case is the salary column.

Option A is valid because it correctly applies the GROUP BY clause. Both dept_id and join_date are included in the SELECT statement, which is a requirement when using these columns in conjunction with the GROUP BY clause. This means that the query will calculate the sum of salaries for each combination of dept_id and join_date. It adheres to the SQL rule that every item in the SELECT list must be either an aggregate function or appear in the GROUP BY clause.

Option B is invalid due to a typo in SELECT depe_id and also because it ends with a colon rather than a semicolon.

Option C is invalid because you cannot nest aggregate functions like MAX(AVG(salary)) without a subquery.

Option D is invalid for the same reason as option C, where it tries to nest aggregate functions AVG(MAX(salary)), which is not allowed directly in SQL without a subquery.

For further reference, you can consult the Oracle 12c documentation, which provides comprehensive guidelines on how to use the GROUP BY clause and aggregate functions like SUM():

Oracle Database SQL Language Reference, 12c Release 1 (12.1): GROUP BY Clause

Oracle Database SQL Language Reference, 12c Release 1 (12.1): Aggregate Functions

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.

The Oracle Data Pump access driver allows for specific actions with external tables:

B. Read data from an external table and load it into a table in the database. Data Pump can be used to efficiently transfer data between external tables and internal database tables.

C. Query data from an external table. The Data Pump access driver supports querying data directly from external tables.

F. Read data from a table in the database and insert it into an external table. The Data Pump can also export data from database tables to external table formats.

Options A, D, and E are incorrect:

A and D are incorrect as the creation of a directory object is not specific to the Data Pump access driver but is a general external table requirement.

E is incorrect because DML operations directly on external tables are not supported; they are read-only.

B. True. Oracle databases guarantee read consistency at the transaction level, meaning that each query can only see data that has been committed before the query began executing.

C. True. When an UPDATE statement is executed, Oracle locks each row as it is updated to prevent other transactions from modifying it until the transaction is committed or rolled back.

A is incorrect because updates made by one user cannot be rolled back by another user. D is incorrect because a query can access tables in other schemas if proper permissions are granted. E is incorrect because a user can own only one schema, which has the same name as the user in an Oracle database.

Set operators allow you to combine multiple queries into a single query. The key points for each option are:

A. An expression in the first SELECT list does not need to have a column alias; however, the column alias given to an expression in the first SELECT list is used as the column name of the output.

B. This statement is true. When CHAR columns of different lengths are combined with a set operator, Oracle will pad the shorter values with spaces to match the length of the longest value. This behavior is consistent with the SQL standard and Oracle's treatment of the CHAR datatype.

C. Oracle Database performs implicit conversion between compatible data types where necessary when using set operators. For example, a NUMBER can be implicitly converted to a VARCHAR2 and vice versa if the context requires it.

D. This is true. When multiple set operators are used in a single query, the INTERSECT operator takes precedence over the UNION and UNION ALL operators. This order of operation can be overridden by using parentheses.

E. Not all set operators work with all data types. For instance, LOB and LONG RAW data types cannot be used with set operators.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Set Operators"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Data Types"

D. True. The NVL function can replace a NULL value with a specified value, and it does not require the data types to match exactly, allowing implicit conversion where possible. Here, 'DATE' is a string literal, and 200 is a number, and since the selected column is NULL, NVL will return 200.

The other options are incorrect because the COALESCE function requires all arguments to be of the same data type or at least compatible types that Oracle can implicitly convert. In A and E, the use of COALESCE with a string literal 'DATE' and SYSDATE (which is a date type) is not compatible without explicit conversion. Option C has a typo (TRCH instead of FROM) and is mixing data types incorrectly.

A. A customer can exist in many countries. This is true as customers can have multiple addresses or operations in different countries, and a database design can reflect this by allowing multiple country entries for a single customer1.

C. The statement will fail because subquery may not be I contained in a values clause. In Oracle Database 12c, a subquery cannot be used within the VALUES clause of an INSERT statement. The correct approach would be to use the subquery in conjunction with the INSERT INTO … SELECT syntax if multiple rows are derived from a subquery2.

F. A product can have a different unit price at different times. It is common for products to have different unit prices at different times due to various factors such as promotions, discounts, or changes in cost price. This can be represented in a database by having a price history table or a similar mechanism to track the changes in price over time1.

Note: The other options are incorrect because:

B. The statement about the SALES table failing if a row already exists for product 23 is not necessarily true. Oracle allows for multiple rows with the same product ID if the table is designed to handle such cases, like having a composite primary key or no constraints preventing duplicates.

D. Without specific information about the SALES table’s design, we cannot verify the number of foreign keys it has.

E. The statement about the successful execution and insertion of a new row into the SALES table is too vague without the context of the actual SQL statement being referred to.

Based on the business rule, the true statements regarding the Entity Relationship (ER) model are:

A. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities. This is true because, in a many-to-many relationship, an associative (junction) table is used to maintain the associations between two entities.

D. The ER must have a many-to-many relationship between the STUDENTS and PROJECTS entities that must be resolved into 1-to-many relationships. This is correct because many-to-many relationships are resolved by introducing an associative table that breaks it down into two 1-to-many relationships.

Options B, C, and E are incorrect:

B is incorrect because PROJECT_ID should not be a foreign key in the STUDENTS entity in a many-to-many relationship.

C is incorrect as the rule indicates a many-to-many relationship, not 1-to-many.

E is incorrect because STUDENT_ID should not be a foreign key in the PROJECTS entity in a many-to-many relationship.

Global temporary tables in Oracle Database 12c have unique characteristics, primarily around their visibility and lifespan which is session-specific:

B. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted: This is accurate as TRUNCATE in the context of a global temporary table only affects the rows inserted during the session that issues the command. The effect is isolated to the session.

D. A GLOBAL TEMPORARY TABLE's definition is available to multiple sessions: The definition (i.e., the structure of the table such as column names, data types, etc.) of a global temporary table is persistent and visible across sessions. However, the data within is session-specific.

E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted: True, as the data in a global temporary table is designed to be temporary for the duration of a session. When the session ends, the data is automatically deleted.

References:

Oracle Database Concepts and SQL Language Reference 12c, especially sections on temporary tables.

D. True. The TRUNC function, when applied to the difference between SYSDATE and hire_date divided by 7, will return the number of complete weeks of employment. The ORDER BY tenure DESC will sort the result in descending order, starting with the longest-serving employee.

A is incorrect because it does not include the DESC keyword necessary to start with the longest-serving employee. B is incorrect because ROUND could result in rounding up to the next week, which does not give the number of complete weeks. C is incorrect because the division is done by 17 instead of 7, which is likely a typo, and ROUND is used instead of TRUNC.

Single-row functions operate on single rows and return one result per row. Let's look at each option in the context of Oracle 12c SQL:

A. CONCAT: This function can only combine two values at a time. If you need to concatenate more than two values, you have to nest CONCAT functions or use the || operator.

B. MOD: The MOD function returns the remainder of a division operation, not the quotient.

C. CEIL: This function returns the smallest integer that is greater than or equal to a specified number. It works with both positive and negative numbers.

D. FLOOR: It returns the largest integer that is less than or equal to the specified number, not greater than or equal to.

E. TRUNC: This function can indeed be used with both NUMBER and DATE values to truncate them to a specified number of decimal places or to a particular component of a date.

References:

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

Oracle Database SQL Language Reference 12c Release 1 (12.1), CONCAT Function

Oracle Database SQL Language Reference 12c Release 1 (12.1), MOD Function

Oracle Database SQL Language Reference 12c Release 1 (12.1), CEIL and FLOOR Functions

Oracle Database SQL Language Reference 12c Release 1 (12.1), TRUNC Function

Regarding the provided SQL INTERSECT query:

D. 1 ROW: The INTERSECT operation will compare the two SELECT statements and return rows that are identical between them. Both queries are designed to return the same values ('1' for the ID, 'John' for the name, and NULL for the commission), hence one row that is identical between the two datasets will be returned.

Incorrect options:

A: Only one identical row exists between the two datasets.

B: There is an identical row; thus, it is not zero.

C: There is no error in the syntax or execution of the query.

In Oracle 12c SQL, the DELETE statement is used to remove rows from a table based on a condition. Given the constraints and the information provided, let's evaluate the options:

A. DELETE FROM orders WHERE order_total<1000;: This statement is correctly formatted and will delete rows from the ORDERS table where ORDER_TOTAL is less than 1000. If there is a DELETE CASCADE constraint, corresponding rows in the ORDER_ITEMS table will also be deleted.

B. DELETE * FROM orders WHERE order_total<1000;: This syntax is incorrect. The asterisk (*) is not used in the DELETE statement.

C. DELETE orders WHERE order_total<1000;: This statement is also correctly formatted and is a shorthand version of the DELETE statement without the FROM clause.

D. DELETE FROM orders;: This statement will delete all rows from the ORDERS table, and with DELETE CASCADE, it will also delete all related rows in the ORDER_ITEMS table.

E. DELETE order_id FROM orders WHERE order_total<1000;: This syntax is incorrect because you cannot specify a column after the DELETE keyword.

References:

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

In Oracle Database 12c, the INTERSECT operator is used in compound queries to return only the rows that are common to both SELECT statements involved in the query. Here's a breakdown of why the correct answer is C:

A: Incorrect. The order of the tables in an INTERSECT operation does not affect the output because INTERSECT returns only the common elements between the two queries. The operation is symmetric.

B: Incorrect. While the column names themselves can differ between the two SELECT statements, the data types and the number of columns must match for INTERSECT to function correctly.

C: Correct. INTERSECT returns the intersection of the results of the two queries, which includes only rows that are found in both result sets.

D: Incorrect. The number of columns and their data types in each SELECT statement of the INTERSECT must be the same to avoid errors and to correctly perform the comparison.

E: Incorrect. INTERSECT does not ignore NULLs; rows with NULL values in corresponding columns must exactly match (both sides having NULLs in the same column) to be included in the output.

The correct statement to increase each employee's salary by the minimum salary for their department is:

D. This option correctly increases each employee's salary by the minimum salary in their department. The subquery calculates the minimum salary for the department of the current row being updated (e1) in the outer UPDATE statement. The subquery does not use GROUP BY since it returns a single value - the minimum salary for the matching department.

A, B, and C are incorrect. Specifically:

A uses a subquery with an aggregate function combined with GROUP BY, which is not needed and will result in an error.

B incorrectly references MIN(e1.salary) within the subquery, which is incorrect since e1 is the alias for the outer query's table and should not be used within the subquery.

C sets the salary to itself plus the minimum salary across all employees, not filtered by the department.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "UPDATE"

Regarding single row functions in Oracle Database 12c:

C. CEIL: can be used for positive and negative numbers. This is true. The CEIL function rounds a number up to the nearest integer, and it works for both positive and negative values.

D. TRUNC: can be used with NUMBER and DATE values. This is correct. The TRUNC function can truncate numbers or dates to a specified precision.

Options A, B, and E are incorrect:

A is incorrect because CONCAT function typically concatenates two strings; to concatenate more, you must nest CONCAT calls or use the || operator.

B is incorrect because FLOOR returns the largest integer less than or equal to the specified number, not greater.

E is incorrect because MOD returns the remainder of a division operation, not the quotient.

When you need to compare dates and calculate the difference in months between two dates, you use the MONTHS_BETWEEN function in Oracle SQL.

A. This statement is incorrect because MONTHS_BETWEEN takes the later date first, then the earlier date.

B. This statement is correct. The MONTHS_BETWEEN function returns the number of months between two dates. It takes the later date (in this case, SYSDATE) first and the earlier date (start_date) second. If the start_date is within the last 25 months, the result will be less than or equal to 25.

C. This statement is incorrect. The expression TO_NUMBER(start_date - SYSDATE) would not give the correct number of months between the dates.

D. This statement is incorrect. The ADD_MONTHS(start_date, 25) function adds 25 months to start_date, and comparing it to SYSDATE would show dates that are at least 25 months in the future.

The correct answer to perform the specified update and insert operations is to use the MERGE statement, which is designed to facilitate such "upsert" operations (update or insert).

A. The syntax is incorrect; the WHERE clause is not used with MERGE.

B. This is the correct syntax and use of the MERGE statement. It uses the ON clause to specify the join condition, and it provides actions for WHEN MATCHED and WHEN NOT MATCHED situations.

C. The keywords FOUND and NOT FOUND are not valid in the context of the MERGE statement in Oracle SQL.

D. Similar to option A, the syntax is incorrect because MERGE uses an ON clause, not a WHERE clause, and the keywords FOUND and NOT FOUND are incorrect.

The correct syntax of a MERGE statement includes defining a condition using the ON clause to determine how rows from the source and target tables are matched, then specifying the actions for when rows match (WHEN MATCHED) and when they do not match (WHEN NOT MATCHED).

References:

Oracle Documentation on MERGE: https://docs.oracle.com/database/121/SQLRF/statements_9016.htm#SQLRF01606

For filtering rows based on the START_DATE within the last 25 months:

C. WHERE MONTHS_BETWEEN(SYSDATE, start_date)<=25: This WHERE clause correctly uses the MONTHS_BETWEEN function to compute the number of months between the current date (SYSDATE) and the START_DATE column. It checks if the difference is 25 months or fewer, correctly applying the condition.

Incorrect options:

A: The TO_NUMBER usage is incorrect for date arithmetic in this context.

B: ADD_MONTHS used in this manner would incorrectly calculate dates in the future rather than comparing past dates.

D: The MONTHS_BETWEEN function should have SYSDATE as the first argument to calculate past months correctly.

The TO_CHAR function is used to convert a number to a string format in Oracle SQL. In this format mask '$9,999', the dollar sign is a literal, and the 9 placeholders represent a digit in the output. The comma is a digit group separator.

A. This statement is incorrect because the format model does not have enough digit placeholders to display the full number 1023.99; it would round it to $1,023 not $1,024. B. This statement is correct. Given the format '$9,999', the number 1023.99 will be formatted as $1,023 because the format rounds the number to no decimal places. C. This is incorrect because the format '$9,999' cannot display the number 10235.99; it exceeds the format's capacity. D. This is incorrect for the same reason as C, and the format would not change the thousands to hundreds. E. This statement is correct. If the number exceeds the maximum length of the format mask, which is 4 digits in this case, Oracle SQL displays a series of hash marks (#) instead of the number.

These formatting rules are described in the Oracle Database SQL Language Reference, which covers the TO_CHAR function and its number formatting capabilities.

The WITH GRANT OPTION clause in Oracle SQL allows the grantee to grant the privilege they have received to another user or role.

E. It cannot be used to pass on privileges to PUBLIC by the grantee: The WITH GRANT OPTION does not allow the grantee to pass on privileges to PUBLIC. Only the object's owner or a user with the GRANT ANY OBJECT PRIVILEGE system privilege can grant privileges to PUBLIC.

F. It can be used to pass on privileges to other users by the grantee: This is true. When a user receives privileges with the WITH GRANT OPTION, they can grant that privilege to another user or role.

References:

Oracle Database SQL Language Reference 12c, specifically sections on user privileges and the WITH GRANT OPTION.

To calculate the total annual compensation, you need to sum the annual salary with the annual commission. The annual commission is the monthly commission percentage times the monthly salary times 12 (months). The NVL function is used to replace NULL with 0 for commission percentage when calculating the commission.

A: This statement is incorrect because it does not handle NULL values for 'monthly_commission_pct', which will result in NULL for the entire expression if 'monthly_commission_pct' is NULL.

B: This statement is syntactically incorrect because it lacks commas and proper parentheses to separate the column name 'last_ name' from the calculation.

C: This is the correct statement as it uses NVL to replace NULL with 0 and calculates the total annual compensation correctly.

D: This statement is incorrect because, like in option A, it does not handle NULL values in 'monthly_commission_pct', resulting in NULL if 'monthly_commission_pct' is NULL.

To determine if customer details have been entered more than once using a different custno, the following methods can be used:

SUBQUERY: A subquery can be used to find duplicate names by grouping the names and having a count greater than one.

SELECT custname FROM customers GROUP BY custname HAVING COUNT(custname) > 1;

Self Join: A self join can compare rows within the same table to find duplicates in the custname column, excluding matches with the same custno.

SELECT c1.custname FROM customers c1 JOIN customers c2 ON c1.custname = c2.custname AND c1.custno != c2.custno;

These methods allow us to find duplicates in the custname column regardless of the custno. Options A, B, and E are not applicable methods for finding duplicates in this context.

References:

Oracle Documentation on Group By: Group By

Oracle Documentation on Joins: Joins

To retrieve the names of the three employees with the lowest salaries, the correct SQL syntax and logic are crucial:

Option B:

SELECT last_name, salary FROM employees ORDER BY salary FETCH FIRST 3 ROWS ONLY;

This query correctly sorts employees by their salary in ascending order and fetches the first three rows only. The FETCH FIRST n ROWS ONLY syntax is a standard way to limit the result set in SQL.

Options A, C, D, and E do not correctly implement the logic for fetching the lowest three salaries due to misuse of ROWNUM or incorrect placement of ORDER BY and FETCH clauses.

In SQL, when you want to count occurrences of null values using the COUNT function, you must remember that COUNT ignores null values. So, if you want to count rows with null list_price, you have to replace nulls with some value that can be counted. This is what the NVL function does. It replaces a null value with a specified value, in this case, 0.

A, C, and D options attempt to count list_price directly where it is null, but this will always result in a count of zero because COUNT does not count nulls. Option B correctly uses the NVL function to convert null list_price values to 0, which can then be counted. The WHERE list_price IS NULL clause ensures that only rows with null list_price are considered.

The SQL documentation confirms that COUNT does not include nulls in its count and NVL is used to substitute a value for nulls in an expression. So option B will give us the correct count of rows with a null list_price.

In Oracle Database, object privileges are rights to perform a particular action on a specific object in the database. Here's why the other options are incorrect:

A. UPDATE can be granted on tables, views, and materialized views, but not sequences. B. DELETE cannot be granted on sequences because sequences do not store data that can be deleted. D. INSERT cannot be granted on sequences; sequences are used to generate numbers, not to be inserted into directly. C. REFERENCES allows the grantee to create a foreign key that references the table or the columns of the table. It is applicable only to tables and views. E. SELECT can indeed only be granted on tables and views (including materialized views). F. ALTER is an object privilege that can be granted on tables and sequences but not views.

For more details, one may refer to the Oracle Database SQL Language Reference documentation, which specifies the types of object privileges and the objects they apply to.

Understanding the functionality of SQL joins and their application in a relational database:

Option A: Correct. A self join is used to relate or compare rows within the same table. It typically involves using table aliases to distinguish the different instances of the table in the query.

Option B: Incorrect. An inner join relates rows between two different tables based on a common condition, not within the same table.

Option C: Incorrect. SQL allows joining of rows from any tables, whether directly related by foreign keys or not, as long as there is a logical condition that can relate their columns.

Option D: Correct. Sometimes establishing a relationship between two specific tables might require joining through one or more intermediary tables, particularly in cases involving complex relationships or normalization.

Option E: Incorrect. While joins are often used to implement relationships, not every logical relationship necessitates a join condition, especially if data from only one table is required or if multiple relations are possible but not relevant to the specific query.

For creating tables and utilizing sequences in Oracle 12c SQL, certain rules apply:

Option A: This statement is valid. It creates a table with a primary key and dates, where the default value for end_date is SYSDATE. There are no constraints on sequences, and all syntax is correct.

Option B: This option is invalid because it attempts to use CURRVAL of a sequence as a default value for a primary key. CURRVAL cannot be used until the sequence's NEXTVAL has been called in the session.

Option C: Invalid syntax because 'start date' is not a recognized default expression in Oracle SQL. If the intention was to use 'start_date' as a reference, it still would not work correctly without proper syntax for conditional default values.

Option D: This syntax is invalid in Oracle SQL because the DEFAULT clause does not support relational conditions directly within the table definition.

Option E: Valid. It correctly uses NEXTVAL for a sequence in the DEFAULT clause for the primary key, ensuring that each new record has a unique primary key generated from the sequence. The start_date and end_date are assigned defaults of SYSDATE, which is syntactically correct.

In Oracle SQL, to include single quotes in a string literal, you need to use two single-quote characters.

A. This statement will not run successfully because the single quotes are not properly escaped. After the text The first_name is , there should be two single quotes to start the string literal for the first name.

B. This statement will run successfully. It properly escapes the single quotes by using two single quotes around first_name. This will result in the output like The first_name is 'John'.

C. This statement will run successfully as well. The two single quotes after The first_name is and before FROM employees are correctly used to delimit the first name.

D. This statement will not run successfully. There is a missing single quote before the double pipe (||) operator, and at the end of the statement, only one single quote is used instead of two for escaping.

E. This statement will not run successfully because the backslash is not used to escape single quotes in Oracle SQL. The proper method is to use two single quotes without a backslash.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Text Literals"

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "String Concatenation Operator"

Sequences and default values in Oracle play a crucial role in providing unique values for table columns.

Statement B is correct: When a row is inserted into ORD_ITEMS without an explicit value for ORD_NO, the ORD_NO column gets the next number from the ORD_SEQ sequence due to the DEFAULT ord_seq.nextval clause.

Statement D is correct: If the ORD_SEQ sequence is dropped, Oracle would not be able to get the next value for ORD_NO from ORD_SEQ, and unless another default is specified, the default for ORD_NO would be NULL.

Statements A, C, and E are incorrect for the following reasons:

A is incorrect because the use of the sequence in the default clause of the table definition automatically grants the necessary permissions to use the sequence when inserting into the table.

C is incorrect because the sequence is defined to cycle when it reaches its MAXVALUE and not after every 5000 numbers. It is set to cycle, but the cycling event is triggered by the MAXVALUE limit.

E is incorrect because while a sequence is designed to produce unique numbers, if it cycles, it can potentially generate the same number again after reaching the MAXVALUE. This statement would only be true if there was no cycling.

A. The USER SYNONYMS view can provide information about private synonyms: This view contains information about private synonyms that have been created by the user. It lists all synonyms that are accessible to the user in their schema.

E. DICTIONARY is a view that contains the names of all the data dictionary views that the user can access: This view lists the names of all data dictionary views available to the user, providing a directory of useful database metadata.

Incorrect options:

B: The SYSTEM user does not own all base tables of the data dictionary; some are owned by SYS.

C: Not all users have access to dynamic performance views prefixed with V$; access is restricted based on privileges.

D: The USER_OBJECTS view lists all objects owned by the user, not just tables and views but also other types of schema objects like procedures and functions.

When the NLS_DATE_FORMAT is set to DD Mon YYYY, the database expects the date string to be in the format of day, abbreviated month name, and full year.

B. The query SELECT DATE '2019-01-01' FROM DUAL; correctly returns the value 1 Jan 2019 because the ANSI date literal DATE 'YYYY-MM-DD' is independent of the NLS_DATE_FORMAT parameter.

Option A is incorrect because the TO_DATE function requires the format model to match the string literal, which it does not in this case.

Option C is incorrect because TO_CHAR is used to convert a date to a string, not a string to a date.

Option D is incorrect because without specifying that the string is a date, the result is just a string and not a date value.

Option E is incorrect because TO_DATE without a format model relies on the NLS_DATE_FORMAT to interpret the string, and YYYY-MM-DD does not match DD Mon YYYY.

The SQL UPDATE statement is used to modify existing records in a table. In the given statement, there is a subquery that selects the JOB_ID and SALARY of EMPLOYEE_ID 200 and sets these values to the corresponding columns of EMPLOYEE_ID 100.

B. Employee 100 will have SALARY set to the same value as the SALARY of employee 200: This is true because the subquery selects the SALARY of employee 200 and the outer update statement sets this SALARY to employee 100.

C. Employee 100 will have JOB_ID set to the same value as the JOB_ID of employee 200: Similarly, the JOB_ID for employee 100 is updated to match the JOB_ID of employee 200 because of the subquery.

References:

Oracle Database SQL Language Reference 12c, particularly the section on the UPDATE statement and subqueries.

A. True - The TO_CHAR function is used correctly here to convert the numeric value to a string, and NVL handles the case where cust_credit_limit might be NULL. The expression inside NVL computes 15% of the credit limit or displays 'Not Available' if the credit limit is NULL. The syntax is correct.

B. False - The NVL2 function requires three parameters: the expression to check for NULL, the value to return if it's not NULL, and the value to return if it is NULL. The given usage lacks the required parameters and syntax.

C. False - The NVL function expects both parameters to be of the same data type. Since the second parameter 'Not Available' is a string, it causes a data type conflict with the numerical result of the first parameter.

D. False - The keyword SELECT is misspelled as SLECT, making the syntax incorrect.

E. True - This query uses NVL2 correctly by checking if cust_credit_limit is not NULL, then applying TO_CHAR to compute 15% of it and converting it to string, or returning 'NOT Available' if it is NULL. The syntax and function usage are correct.

In reference to the attributes of the PRODUCT_DETAILS table:

A. This statement is false. If PRODUCT_PRICE has no value (i.e., it is NULL), it cannot be used directly in arithmetic expressions because any operation with NULL results in NULL.

B. This statement is true. PRODUCT_ID has the NOT NULL constraint and is of type NUMBER, making it eligible to be a PRIMARY KEY. Primary keys require all values to be unique and not null.

C. This statement is true. EXPIRY_DATE is of type DATE, and it cannot be used in arithmetic expressions directly without a date function that operates on dates.

D. This statement is false. By default, columns of type DATE do not have a default value unless explicitly assigned using the DEFAULT keyword in the column definition.

E. This statement is false. By default, numeric columns do not have a default value unless specified with the DEFAULT keyword.

F. This statement is false. The statement about PRODUCT_NAME not containing duplicate values would be true if it were a UNIQUE or PRIMARY KEY, but there is no such constraint indicated in the provided table description.

When writing SQL statements with multiple conditions in the WHERE clause, it's important to understand how logical operators like AND and OR work. These operators are used to filter records based on more than one condition:

The AND operator displays a record if all the conditions separated by AND are TRUE.

The OR operator displays a record if any of the conditions separated by OR is TRUE.

The precedence of these operators is also important: AND operations are always evaluated before OR operations unless parentheses are used to explicitly define the order of operations. Therefore, conditions enclosed in parentheses are evaluated first as per the standard SQL operator precedence.

Given this understanding, let's evaluate the options:

Option A uses parentheses to group the borrowed_date and transaction_type conditions together, ensuring these are evaluated first before the OR operator. This means the query will return records that meet both of these conditions or records with member_id 'A101' or 'A102', regardless of other conditions.

Option D does not use parentheses around the borrowed_date and transaction_type conditions but does use them to group the member_id conditions. Since the AND operator has higher precedence than the OR, this query will first evaluate the borrowed_date and transaction_type conditions, then evaluate the grouped member_id conditions. The outcome is records that have a borrowed_date of SYSDATE, a transaction_type of 'RM', and a member_id of either 'A101' or 'A102'.

The results of Options A and D are effectively the same, even though the use of parentheses is different. This is because in both cases, the evaluation of the borrowed_date and transaction_type conditions will be performed first due to their grouping by parentheses in Option A and the precedence of AND over OR in Option D. Therefore, they will both return all records where borrowed_date equals SYSDATE and transaction_type equals 'RM', plus any records where member_id is either 'A101' or 'A102'.

For further details on SQL operator precedence and logical operators, you can refer to Oracle Database SQL Language Reference 12c documentation, specifically the sections on conditional expressions.

Multiple INSERT statements allow data insertion into one or more tables based on different conditions or datasets:

Option A: False. Multiple INSERT operations can be performed using direct values, subqueries, or even default values, not exclusively through subqueries.

Option B: True. They can indeed be performed on relational tables, which is the standard use case in most relational databases.

Option C: True. INSERT operations can be performed on updatable views, assuming the view is not complex (involving joins, GROUP BY clauses, etc.).

Option D: True. Oracle allows INSERT operations on remote tables via database links, enabling distributed database interactions.

Option E: False. Direct INSERT statements cannot be performed on external tables. External tables are typically used for read operations, with data loading handled through utilities like SQL*Loader or external data processing tools.

Option F: False. Each INSERT statement inserts data into one table. While a single SQL command block can contain multiple INSERT statements, each one is directed at a single table.

When using the TRUNCATE TABLE command in Oracle, several aspects of the table's structure and associated database objects are impacted. Here's an explanation of each option:

A: Incorrect. TRUNCATE TABLE does not drop triggers associated with the table; they remain defined.

B: Correct. Indexes on the table are retained and not dropped when you truncate a table. However, if the index is a domain index, it may be dropped depending on its type.

C: Correct. Integrity constraints such as primary keys, foreign keys, etc., are retained unless they are on a disabled state where truncation can lead to constraint being dropped.

D: Incorrect. A TRUNCATE TABLE operation cannot be rolled back. It is a DDL (Data Definition Language) operation and commits automatically.

E: Incorrect. The TRUNCATE TABLE operation deallocates the space used by the data unless the REUSE STORAGE clause is specified.

F: Incorrect. TRUNCATE TABLE operation removes all the rows in a table and does not log individual row deletions, thus FLASHBACK TABLE cannot be used to retrieve the data.

The UNION ALL operator in SQL combines the results of two or more SELECT statements into a single result set:

Option A: True. Since UNION ALL does not eliminate duplicates, it does not ignore NULLs during any duplicate checking; however, it's worth noting that UNION ALL does not perform duplicate checking at all.

Option B: False. Unlike the UNION operator, UNION ALL does not eliminate duplicates; it includes all duplicates in the output.

Option C: False. The column names do not need to be identical in each SELECT statement; however, the result set will use the column names from the first SELECT statement.

Option D: True. For UNION ALL to work correctly, the number of columns and their data types in each SELECT statement must match, but the column names can differ.

Option E: False. UNION ALL does not inherently sort the output; it merely combines the results as they are retrieved. Any sorting would need to be explicitly specified using an ORDER BY clause.

Regarding the use of SAVEPOINT in transactions:

C. A SAVEPOINT does not issue a COMMIT: A SAVEPOINT in Oracle SQL is used to mark a point within a transaction. It does not commit the transaction; rather, it allows partial rollbacks to the point of the SAVEPOINT without affecting other uncommitted changes outside it.

E. Rolling back to a SAVEPOINT can undo a DELETE statement: SAVEPOINT allows you to rollback DML changes like INSERT, UPDATE, and DELETE that occur after the SAVEPOINT was established, but before a COMMIT.

Incorrect options:

A: CREATE INDEX is a DDL statement, and like most DDL statements, it implicitly commits the transaction, making it impossible to rollback using a SAVEPOINT.

B: Multiple SAVEPOINTS can be defined within a single transaction, each with unique or reused names.

D: TRUNCATE is a DDL statement that also implicitly commits the transaction; hence it cannot be rolled back to a SAVEPOINT.

In Oracle Database 12c, the SYSDATE function returns a date value in the current session's date format. The SUBSTR function is used to extract a substring from a string. However, SYSDATE is a DATE data type and not a string, so you cannot directly use SUBSTR on it. Therefore, SYSDATE must first be converted to a character data type using the TO_CHAR function before applying the SUBSTR function.

Therefore, the correct form of the query should be:

SELECT SUBSTR(TO_CHAR(SYSDATE), 1, 5) AS "Result" FROM DUAL;

This converts the current date and time to a string using the default date format and then extracts the first 5 characters from it.

A self join is a regular join, but the table is joined with itself. This kind of join can take the form of an inner join, a left outer join, or even a full outer join depending on the requirement.

A. The join key column must have an index. (Incorrect)

While indexes can improve the performance of joins by reducing the cost of the lookup operations, they are not a requirement for a self join. A self join can be performed with or without an index on the join key columns.

B. It can be a left outer join. (Correct)

A self join can indeed be a left outer join. This is useful when you want to include all records from the 'left' side of the join (the table itself), even if the join condition does not find any matching record on the 'right' side (the table itself again).

B: True. The REFERENCES privilege can be granted on specific columns within a table. This is necessary when a user needs to define foreign key constraints that reference those particular columns.

C: True. The UPDATE privilege can be granted on specific columns, allowing users to update only designated columns within a table. This is useful for restricting write access to sensitive or critical data within a table.

F: True. The INSERT privilege can also be granted on specific columns, meaning a user can be permitted to insert data into only certain columns of a table. This helps in maintaining data integrity and controlling access to data fields based on user roles.

The CHECK constraint is generally used to limit the range of values that can be placed in a column. If a CHECK constraint exists on the department_id to only allow values such as 80 (assuming from the context provided):

A. UPDATE emp80 SET department_id=90 WHERE department_id=80: This UPDATE statement attempts to set the department_id to 90 for rows where it is currently 80. If there is a CHECK constraint preventing department_id from being anything other than 80, this statement would violate that constraint.

Incorrect options:

B: DELETE operations do not affect CHECK constraints as they do not involve modifying or setting column values.

C and D: SELECT statements do not modify data and thus cannot violate data integrity constraints like CHECK constraints.

A: This is true. Using PURGE in the DROP TABLE command will permanently remove the table and its dependent objects so that it cannot be recovered, and the action cannot be rolled back.

B: This statement is true. Dropping the base table will result in the removal of all views that are based on the DEPARTMENTS table.

C: This statement is false. The PURGE option removes the table and does not just delete rows.

D: This statement is true. The DROP TABLE command will remove the DEPARTMENTS table from the database.

E: This statement is true. When a table is dropped, all synonyms for that table are also removed.

F: This statement is true. Dropping a table will automatically drop all indexes associated with the table.

The data dictionary contains metadata (data about data) about the database and its objects.

A. False. DBA_ views display metadata for objects accessible to the database administrator, not only for the SYS schema.

B. True. ALL_ views display information about all the objects that the current user has access to. This does not necessarily mean the user has privileges on these objects, only that the user can see them.

C. False. The data dictionary is not accessible when the database is closed because it requires the database to be open in order to access the system tables.

D. True. Not all types of metadata are available in all three prefixes (ALL_, DBA_, USER_). Some specific metadata might only be available in one or two of these view types.

E. False. The data dictionary stores metadata in tables. The various views (ALL_, DBA_, USER_, etc.) provide different perspectives on this data.

References:

Oracle Documentation on Data Dictionary and Dynamic Performance Views: https://docs.oracle.com/cd/B28359_01/server.111/b28310/datadict.htm

A. Schema owners indeed can grant privileges on objects in their schema to other users or roles, making this statement true.

B. Roles in Oracle can be granted both object and system privileges, making this statement true as well.

C. Not all types of schema objects have associated object privileges. For example, synonyms do not have object privileges because they are just aliases for other objects.

D. The DBA role is a powerful role, but creating roles can be done by any user granted the necessary privileges, not just users with the DBA role.

E. Object privileges on a table do not automatically apply to all synonyms for that table. Synonyms are separate objects that must have privileges granted explicitly.

F. The privilege to grant system privileges is controlled by the GRANT ANY PRIVILEGE system privilege, making this statement true.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "Privileges"

Oracle Database Security Guide, 12c Release 1 (12.1): "Administering User Privileges, Roles, and Profiles"

For conditional INSERT ALL in Oracle Database 12c:

D. A single WHEN condition can be used for multiple INTO clauses. This is true. A single WHEN condition in a multi-table insert can direct the insertion of a single row source into multiple target tables.

E. Each WHEN condition is tested for each row returned by the subquery. True, each row from the subquery is evaluated against each WHEN condition to determine into which table(s) the row should be inserted.

Options A, B, and C are incorrect:

A is incorrect because a row can indeed be directed into multiple tables based on the conditions.

B is incorrect as INSERT ALL can include an ELSE clause to handle rows that do not meet any of the specified conditions.

C is incorrect because not every row from the subquery necessarily results in a row insertion; it depends on the conditions being met.

Synonyms in Oracle are aliases for database objects that can simplify SQL statements for database users.

A. The term "GLOBAL" is not used in the creation of synonyms in Oracle.

B. The statement without the keyword PUBLIC will create a private synonym that is only accessible to the user creating the synonym, not all users.

C. The correct syntax does not include PUBLIC as a prefix to the synonym name itself, making this option incorrect.

D. You cannot specify the SYS schema for creating synonyms, as it is reserved for system objects.

E. This is the correct syntax to create a public synonym, which makes the underlying object accessible to all users.

References:

Oracle Database SQL Language Reference, 12c Release 1 (12.1): "CREATE SYNONYM"

A: True, the name of a column can be changed in Oracle using the ALTER TABLE ... RENAME COLUMN command.

B: False, you cannot change a column's data type from NUMBER to VARCHAR2 if the table contains data, unless the change does not result in data loss or inconsistency.

C: True, it is possible to change a DATE data type column to TIMESTAMP because TIMESTAMP is an extension of DATE that includes fractional seconds. This operation is allowed if there is no data loss.

D: False, any column that is not part of a primary key or does not have a non-deferrable constraint can generally be dropped unless it contains data that does not allow for such a change.

E: False, the DEFAULT clause can be added to a column provided there is no data that contradicts the default value or it doesn't violate any constraints.

These statements are verified against the Oracle Database 12c SQL documentation, specifically the sections on data types, the ALTER TABLE command, and the use of literals in SQL expressions.

The requirements specify that we need to select book titles based on a price condition and a date condition, and then sort the results by the date of purchase.

A. This query will not execute successfully because there is a syntax error in the price condition. It should be price < 500 OR price > 1000.

B. This query is incorrect. The requirement specifies that the price must be less than 500 or more than 1000, not in a list of values.

C. This query is correct. It selects books where the price is not between 500 and 1000 (thus, less than 500 or more than 1000) and the purchase date is before January 17, 2007, ordered by purchase date in descending order.

D. This query is incorrect because it selects books with prices between 500 and 1000, which is not what the requirement specifies.

To convert the string "Hello World" to "ello wozid":

Option B: SELECT LOWER(SUBSTR('Hello World', 2)) FROM DUAL;

This will remove the first character and convert the rest to lowercase, but it will not change the 'rld' to 'zid'.

Option D: SELECT SUBSTR('Hello world', 2) FROM DUAL;

This will remove the first character, but the rest of the string remains unchanged and not converted to lowercase.

Options A, C, and E are incorrect because:

Option A: SUBSTR is incorrect here because it is only getting one character instead of the entire string minus the first character.

Option C: While TRIM removes the 'H', LOWER converts all characters to lowercase, but it doesn't address the 'rld' to 'zid' change.

Option E: INITCAP and TRIM will not achieve the desired conversion of 'rld' to 'zid'.

The INTERSECT operator in SQL returns the results that are common to both of the SELECT statements. It functions similarly to a set intersection in mathematics. When comparing rows for the INTERSECT operation, Oracle Database uses all the expressions in the SELECT lists to derive the result set. NULL values are considered equal for the INTERSECT operator.

Evaluating the provided data from the ORDERS and INVOICES tables, let's see which rows have the same ORDER_ID and ORDER_DATE in both tables:

A: Order ID 3 has a NULL order date in the ORDERS table and does not match with any row in the INVOICES table, so it will not be returned.

B: Order ID 2 has a NULL order date in the ORDERS table but has a non-NULL order date in the INVOICES table, so it will not be returned.

C: Order ID 1 has a NULL order date in both tables, but INTERSECT considers NULLs as equal, so this will be returned.

D: Order ID 5 has a date of 01-MAR-2019 in the ORDERS table and 01-APR-2019 in the INVOICES table, so it will not be returned since the dates do not match.

E: Order ID 4 has a date of 01-FEB-2019 in both tables, so this row will be returned as it matches in both.

F: Order ID 3 has a NULL order date in the ORDERS table but has 01-JAN-2019 in the INVOICES table, so it will not be returned.

Based on this analysis, the query will return:

Order ID 1 with a NULL order date.

Order ID 4 with an order date of 01-FEB-2019.

So the correct answer is:

In SQL, the ORDER BY clause can refer to columns by their alias defined in the SELECT clause or by their positional number in the SELECT list. It's important to understand that after a UNION, the column names in the ORDER BY clause refer to the first SELECT statement's column names and aliases:

Option A: ORDER BY 2, 1This is correct because it refers to the second and first columns in the first SELECT clause, which correspond to the aliases "Last Name" and cust_id, respectively.

Option E: ORDER BY "Last Name"This is correct because "Last Name" is a valid alias defined in the first SELECT clause.

The other options fail for the following reasons:

Option B: "CUST_NO" is not recognized in the ORDER BY clause because it's not an alias used in the first SELECT statement.

Option C: Incorrect because 2, cust_id mixes positional reference with a column name that doesn't apply to both SELECT statements consistently.

Option D: ORDER BY CUST_NO fails because CUST_NO is not an alias in the first SELECT clause.