Oracle 1z0-071 Oracle Database 12c SQL Exam Practice Test

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.

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.

Regarding creating tables in an Oracle database:

B. The same table name can be used for tables in different schemas: In Oracle, a schema is essentially a namespace within the database; thus, the same table name can exist in different schemas without conflict, as each schema is distinct.

C. A system privilege is required: To create tables, a user must have the necessary system privileges, typically granted explicitly or through roles such as CREATE TABLE or administrative privileges depending on the environment setup.

Incorrect options for all three repeated questions:

A: Oracle SQL does not allow specifying the maximum number of rows directly in a CREATE TABLE statement; this is controlled by storage allocation and database design rather than table creation syntax.

D: Creating an external table does not create the physical file. It merely creates a table structure that allows access to data stored in an external file specified in the directory; the file itself must already exist or be managed outside of Oracle.

E: A primary key constraint is not mandatory for creating tables. While it is a common practice to define a primary key to enforce entity integrity, it is not required by the Oracle SQL syntax for table creation.

These answers and explanations are aligned with Oracle Database 12c SQL documentation and standard practices.

Regarding INTERVAL data types in Oracle Database 12c:

A. INTERVAL YEAR TO MONTH columns only support monthly intervals within a range of years. This is true. The INTERVAL YEAR TO MONTH data type stores a period of time using years and months.

E. INTERVAL DAY TO SECOND columns support fractions of seconds. This is true. The INTERVAL DAY TO SECOND data type can store days, hours, minutes, seconds, and fractional seconds.

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

B is incorrect because data types between INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH are not compatible.

C is incorrect as it incorrectly limits INTERVAL YEAR TO MONTH to a single year.

D is incorrect; the YEAR field can be negative to represent a past interval.

F is incorrect as INTERVAL YEAR TO MONTH supports intervals that can span multiple years, not just annual increments.

A. Incorrect. DUAL consists of a single row and column. B. Incorrect. DUAL is a special one-row, one-column table present by default in all Oracle database installations and can be accessed by any user with SELECT privilege. C. Correct. Any user with the SELECT privilege can select from the DUAL table. It is a dummy table used primarily for selecting a constant, pseudocolumn, or expression not involving any tables. D. Incorrect. DUAL has only one row and one column. E. Correct. The DUAL table has a single VARCHAR2 column called DUMMY, which has a value of 'X'. F. Incorrect. DUAL can be used for a variety of purposes, such as selecting system constants, user environment variables, expressions, and sequences.

This information is detailed in the Oracle Database SQL Language Reference, which describes the properties and uses of the DUAL table.

A: No implicit conversion is done here because TO_CHAR is an explicit conversion from a numeric to a string data type.

B: Implicit conversion happens here because the '0001' string will be automatically converted to a numeric data type to match the CUSTOMER_ID field.

C: No implicit conversion is needed because 0001 is already a numeric literal.

D: Implicit conversion occurs here because the string '01-JAN-19' will be converted to a date type to compare with INSERT_DATE.

E: No implicit conversion; DATE is explicitly specified using the DATE keyword.

F: Incorrect syntax, it has typographical errors and also TO_DATE is an explicit function, not an implicit conversion.

References for SQL functions, conversions, and the CROSS JOIN behavior can be found in the Oracle Database SQL Language Reference 12c documentation.

When the TRUNCATE TABLE command is used:

C. It retains the indexes defined on the table. This is true. The TRUNCATE TABLE operation does not drop the indexes on the table; it only removes all rows from the table.

D. It retains the integrity constraints defined on the table. This is correct. The TRUNCATE TABLE operation does not affect the integrity constraints; it retains them.

Options A, B, E, and F are incorrect:

A is incorrect because the TRUNCATE TABLE operation cannot be rolled back as it is a DDL operation and not a DML operation like DELETE.

B is incorrect because TRUNCATE TABLE does not drop any triggers; they remain in place.

E is incorrect as TRUNCATE TABLE may or may not release the space depending on whether the REUSE STORAGE clause is used.

F is incorrect because FLASHBACK TABLE cannot be used to retrieve data after a TRUNCATE TABLE operation since it does not generate redo logs that FLASHBACK relies upon.

The query uses two functions: ROUND and TRUNC. The ROUND function will round the number 156.00 to the nearest hundred because of the -2 which specifies the number of decimal places to round to. This will result in 200. Then the TRUNC function truncates this number to the nearest 10, due to the -1 argument, which will give us 200 as the result since truncation does not change the rounded value in this case.

A. 16 (Incorrect)

B. 160 (Incorrect)

C. 150 (Incorrect)

D. 200 (Incorrect)

E. 100 (Incorrect)

B. True. This statement will execute successfully because it has a single column in the SELECT statements combined with UNION, and the ORDER BY clause is referencing a valid column in the result set.

A is incorrect because it uses an ORDER BY clause with two columns, which is not allowed when the SELECT statements have only one column each. C is incorrect for the same reason as A; it references columns that do not exist in the result set. D is incorrect because it attempts to ORDER BY a second column, which does not exist in the result of the union.

E. True. Indexes can be created on global temporary tables just like they can on permanent tables. The indexes on global temporary tables are also temporary and only exist for the duration of the session or transaction, based on how the table was defined.

F. True. If the ON COMMIT clause is specified as DELETE ROWS, then all rows in the global temporary table are deleted after each COMMIT or ROLLBACK within the transaction. This clause defines the scope of the data's persistence within the global temporary table.

Regarding Oracle join and ANSI join syntax:

B. The Oracle join syntax supports the creation of a Cartesian product of two tables. This is true. In Oracle, if you list tables in the FROM clause without a join condition, it creates a Cartesian product.

C. The SQL:1999 compliant ANSI join syntax supports natural joins. This is true. ANSI syntax supports natural joins, which join tables based on columns with the same names in the joined tables.

F. The SQL:1999 compliant ANSI join syntax supports the creation of a Cartesian product of two tables. This is true. The ANSI standard allows for Cartesian products when tables are listed in the FROM clause without a join condition.

Options A, D, E, and G are incorrect:

A is incorrect because the Oracle join syntax supports all types of joins, including right outer joins.

D is incorrect because Oracle's proprietary join syntax does not use the term "natural join."

E is incorrect because there is no inherent performance difference between Oracle join syntax and ANSI join syntax; performance depends on how the query is written and how the database optimizer handles it.

G is incorrect for the same reason as E.

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

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

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

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

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

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

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

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

A. A primary key column referenced by another column as a foreign key can be dropped if using the CASCADE option. (Incorrect)

Primary key columns that are referenced by foreign keys cannot be dropped as it would violate referential integrity. The CASCADE option does not apply in this context.

B. A DROP COLUMN command can be rolled back. (Correct)

Dropping a column from a table is a transactional operation. If it's not committed, it can be rolled back.

Option D is the correct SQL statement that satisfies all the requirements mentioned. The CASE statement correctly compares the hire_date to date intervals subtracted from the current date (SYSDATE) to determine the number of years of service. This CASE statement is also appropriately ordered to ensure that the first condition matched is the one returned, preventing overlapping of the conditions.

Here is how Option D works according to the requirements:

It selects the last_name and hire_date from the employees table.

The CASE statement is used to calculate the number of years of service and to display the appropriate message according to the intervals defined.

The ORDER BY hire_date clause ensures the results are sorted by the date of hire.

In Option D, the intervals are defined in the correct order, ensuring that the first true condition is the one that is used:

The first WHEN checks if the hire date is 15 or more years ago.

The second WHEN checks if it is 10 or more years ago.

The third WHEN checks if it is 5 or more years ago.

The ELSE clause covers any hire dates less than 5 years ago.

The correct syntax for the intervals is SYSDATE - hire_date >= TO_YMINTERVAL('15-0') and similarly for the 10 and 5 years intervals.

Options A, B, and C are incorrect because they have various issues, such as incorrect ordering of the CASE statement's conditions, which could lead to incorrect results due to overlapping intervals, or the use of the TO_YMINTERVAL function that may not properly cover the intended date ranges.

References:

Oracle Documentation on CASE Expressions: SQL Language Reference - CASE Expression

Oracle Documentation on TO_YMINTERVAL Function: SQL Language Reference - TO_YMINTERVAL

Oracle Documentation on ORDER BY Clause: SQL Language Reference - ORDER BY Clause

Therefore, Option D is the statement that fulfills all the requirements for displaying the number of years of service based on the employee's hire date and ordered by the hire date.

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.

For question 138 regarding Oracle views, the correct answers are B, E, and G:

B. The WITH CHECK OPTION clause prevents certain rows from being updated or inserted: This clause ensures that all data modifications performed through the view result in data that conforms to the view's defining query.

E. Deleting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error: Views that involve aggregate functions, GROUP BY clauses, or DISTINCT cannot be directly modified with DML operations such as DELETE, because the underlying data manipulation would be ambiguous.

G. Inserting one or more rows using a view whose defining query contains a GROUP BY clause will cause an error: Similarly to DELETE, INSERT operations into a view that uses a GROUP BY clause are generally not allowed because it's unclear how the new rows should be aggregated into the existing groups.

Other options are incorrect because:

A: The WITH CHECK OPTION clause does not prevent rows from being displayed; it restricts DML operations.

C: Views can be created with non-existent underlying tables or views using the FORCE option in Oracle.

D: Not all views are updatable, especially those involving joins, group functions, or distinct aggregation.

F: Updating a view does not impact the granted privileges unless the definition of the view changes.

In Oracle Database 12c, when creating an external table using the CREATE TABLE ... ORGANIZATION EXTERNAL statement, there are certain clauses that are mandatory for the command to execute successfully.

Statement C, the LOCATION clause, is required. The LOCATION clause specifies one or more external data source locations, typically a file or a directory that the external table will read from. Without this, Oracle would not know where to find the external data for the table.

Statement E, the access driver TYPE clause, is also required. The access driver tells Oracle how to interpret the format of the data files. The most common access driver is ORACLE_LOADER, which allows the reading of data files in a format compatible with the SQL*Loader utility. Another option could be ORACLE_DATAPUMP, which reads data in a Data Pump format.

Statements A, B, and D are not strictly required for the command to execute successfully, although they are often used in practice:

A, the DEFAULT DIRECTORY clause, is not mandatory if you have specified the full path in the LOCATION clause, but it is a best practice to use it to avoid hard-coding directory paths in the LOCATION clause.

B, the REJECT LIMIT clause, is optional and specifies the maximum number of errors to allow during the loading of data. If not specified, the default is 0, meaning the load will fail upon the first error encountered.

D, the ACCESS PARAMETERS clause, is where one would specify parameters for the access driver, such as field delimiters and record formatting details. While it is common to include this clause to define the format of the external data, it is not absolutely required for the command to execute; defaults would be used if this clause is omitted.

For reference, you can find more details in the Oracle Database SQL Language Reference for version 12c, under the CREATE TABLE statement for external tables.

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"

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 WHERE clause in SQL filters the rows returned by the SELECT statement. The result of logical operators and conditions can change significantly depending on the use of parentheses.

Options A and E both use parentheses to ensure that borrowed_date = SYSDATE is evaluated with transaction_type ='RM' as one group and the member_id conditions as another group. The parentheses ensure that both conditions within each set of parentheses must be true for the rows to be included.

Option B is incorrect because it does not use parentheses to enforce the grouping of conditions, leading to potentially different results due to the way logical OR works.

Option C is incorrect because it has a syntax error; it is missing a parenthesis.

Option D is incorrect because it will return rows where either borrowed_date = SYSDATE AND transaction_type ='RM' is true or member_id IN ('A101','A102') is true, which is a broader condition than what's specified in options A and E.

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

B: True. The second ROLLBACK command would not do anything because the first ROLLBACK TO SAVEPOINT a; already undid the delete operation, and there was no other DML operation between the first and the second ROLLBACK.

E: True. The second ROLLBACK command undoes the insert because after the first ROLLBACK TO SAVEPOINT a; there are no savepoints defined, so a subsequent ROLLBACK would undo all transactions to the beginning of the current session or last COMMIT.

The ROLLBACK command is used to undo transactions that have not yet been committed. Rolling back to a savepoint only undoes transactions up to that savepoint, and a subsequent ROLLBACK without a savepoint name will undo all uncommitted changes since the last COMMIT.

References:Oracle SQL documentation on the ROLLBACK statement provides details on how it interacts with savepoints and the effects it has on the transactions within a session.

The COUNT function is one of the most commonly used aggregate functions in SQL for determining the number of items in a group. Here's why the correct answers are B and C:

A: Incorrect. COUNT can be used with any data type, not just NUMBER. It counts rows without regard to data type.

B: Correct. COUNT(DISTINCT inv_amt) counts the number of unique non-null values in the column inv_amt. It excludes duplicates and ignores NULL values.

C: Correct. COUNT(*) counts all rows in a table, including those with duplicates and those with NULLs in any column. It is a total row count regardless of content.

D: Incorrect. You can use a WHERE clause with COUNT. The WHERE clause filters rows before counting, which is standard in SQL.

E: Incorrect. COUNT(inv_amt) counts the rows where inv_amt is not NULL. Rows with NULL in the inv_amt column are not counted.

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 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.

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.

Substitution variables in Oracle are used to replace a value dynamically during the execution of SQL statements. The behavior of these variables is well-documented:

C. A substitution variable prefixed with & always prompts only once for a value in a session: This is true. In a session, when you use a single ampersand (&), SQL*Plus or SQL Developer will prompt for the value the first time the variable is encountered. The value for this variable will then be reused for the remainder of the session unless it is redefined.

D. A substitution variable can be used with any clause in a SELECT statement: Substitution variables can be placed in any part of a SQL statement, including the SELECT, WHERE, GROUP BY, ORDER BY, etc. They are not limited to any specific clause.

References:

Oracle SQL*Plus User's Guide and Reference, which discusses substitution variables.

The CONCAT function and FLOOR function in Oracle SQL have specific behaviors:

A. CONCAT function joins two or more character strings into one string, making this statement true.

B. FLOOR function returns the largest integer that is less than or equal to the specified number, making this statement true.

C. While CONCAT can join two strings together, this statement is incomplete as it can join more than two.

D. INSTR can find the offset of a substring within a string, not just a single character.

E. INSTR starts searching the string from position 1 in Oracle SQL, not position 0.

F. FLOOR does return the largest integer less than or equal to the specified number, but it can be any integer, not just positive ones.

References:

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

In SQL, the WHERE and HAVING clauses are used to filter records; the WHERE clause is applied before grouping the records, while the HAVING clause is used after grouping the records, particularly when using aggregation functions.

Statement D is true because the HAVING clause is used to filter groups based on the result of aggregate functions. Therefore, any column or aggregate function appearing in the HAVING clause must also appear in the SELECT list of the query, unless it is used as part of an aggregate function.

Statement E is true because the WHERE clause is designed to filter rows before they are grouped into aggregate groups in a GROUP BY clause. This is a fundamental aspect of SQL that optimizes query performance by reducing the number of rows to be processed in the aggregate phase.

Statements A, B, and C are incorrect based on the following:

A is incorrect because the WHERE clause does not operate on groups but on individual rows before grouping.

B is misleading; while WHERE and HAVING can be used in the same statement, their usage is not restricted to different columns. They perform different functions (row-level filtering vs. group-level filtering).

C is incorrect because subqueries using aggregate functions typically do not use HAVING clauses; rather, HAVING is used in the outer query to filter the results of aggregates.

A. This statement is incorrect because TO_NUMBER expects a character string as an argument, not a date. Directly converting a date to a number without an intermediate conversion to a character string would result in an error. B. This statement is incorrect. Multiplying a date by a number does not make sense in SQL, and attempting to convert such an expression to a date will also result in an error. C. This statement is correct. Subtracting two dates in Oracle SQL results in the number of days between those dates, hence the result is a number. D. This statement is correct. Subtracting a number from a date in Oracle SQL will subtract that number of days from the date, returning another date. E. This statement is incorrect. As stated in C, subtracting a date from SYSDATE correctly returns the number of days between those two dates, not an error.

These concepts are explained in the Oracle Database SQL Language Reference, which details date arithmetic in SQL.

B. CREATE INDEX price_idx on products (price); E. ALTER TABLE products DROP UNUSED COLUMNS; F. DROP TABLE products.

Comprehensive and Detailed Explanation WITH all References:

When a table is in read-only mode, most types of modifications are prohibited. However, certain operations can still be performed.

A. Incorrect. You cannot drop a column from a table that is in read-only mode, as it is a modifying operation. B. Correct. You can create an index on a read-only table. Creating an index does not modify the actual rows within the table; it builds a separate structure used for faster access. C. Incorrect. The SET UNUSED statement marks one or more columns as unused so they can be dropped when the database is not busy. This operation is considered a modifying operation and therefore is not allowed on a read-only table. D. Incorrect. Truncate is a DDL operation that would delete all rows from a table. This operation is not allowed on a read-only table. E. Correct. The ALTER TABLE ... DROP UNUSED COLUMNS statement is used to drop columns that have been previously marked as unused using the SET UNUSED statement. This operation is allowed because it only affects previously marked unused columns, not actively used data. F. Correct. Dropping a table is allowed even if it's in read-only mode because it is a DDL operation that does not operate on the rows of the table but rather on the object itself.

The behavior of read-only tables and the operations that can be performed on them are detailed in the Oracle Database SQL Language Reference and Oracle Database Administrator's Guide.

A: NEXTVAL is used to increment the sequence and return the next value; it does not give the last number generated.

B: CURRVAL returns the current value of the sequence, which is the last value generated in the user's current session. However, CURRVAL cannot be queried unless NEXTVAL has been called at least once in that session.

C: CURRVAL is used correctly, but the syntax 'sequence.CURRVAL' is not correct in Oracle SQL.

D: NEXTVAL is used to generate the next sequence number, not to retrieve the last one generated.

D. All remaining employee names will appear in descending order F. The names of employees with maximum salary will appear first in descending order

Comprehensive and Detailed Explanation WITH all References:

Analyzing the given SQL query:

ORDER BY CASE WHEN salary = (SELECT max(salary) FROM employees) THEN 'A' ELSE last_name END, last_name DESC;

Here's what happens in the query:

The CASE statement checks if the employee's salary is equal to the maximum salary in the table.

If an employee has the maximum salary, they are assigned a constant value 'A', else their last_name is used for sorting.

Since 'A' comes before any possible last name value in ascending order, all employees with the maximum salary will be listed first.

After the CASE expression, the query specifies last_name DESC. This means that within each group sorted by the CASE expression, the names will be sorted in descending order.

Therefore, the employees with the maximum salary will appear first, and within that group, if there is more than one employee with the same maximum salary, they will be sorted in descending order by last name (F). Then, all remaining employee names will appear in descending order (D).

References:

Oracle Documentation on CASE Expressions: CASE Expressions

Oracle Documentation on Sorting Query Results: Sorting Query Results

The actions possible using the ALTER TABLE command:

E. Rename a table: The ALTER TABLE command can be used to rename a table, although the syntax typically involves RENAME TO as part of table management.

F. Enable or disable constraints on a table: This is a standard use of the ALTER TABLE command, allowing for constraints to be enabled or disabled, which is critical for managing table integrity during data loads or maintenance.

Incorrect options:

A: You cannot drop pseudo columns from a table as they are not explicitly defined or managed like regular columns.

B: The ALTER TABLE command does not directly restrict DML statements; this would typically be managed through security roles or other database mechanisms.

C: Dropping all columns from a table simultaneously is not possible; a table must retain at least one column.

D: Locking a set of rows is performed with SQL commands like SELECT FOR UPDATE, not with ALTER TABLE.

A: True. A full outer join does indeed return both matched and unmatched rows from both tables involved in the join. It combines the results of both left and right outer joins.

E: True. An inner join, by definition, returns rows that have matching values in both tables. Rows from both tables that do not match are not returned in an inner join result set.

Inner joins match rows from the joined tables based on the join condition, while outer joins include all rows from one or both tables regardless of whether a matching row exists in the other table.

References:The Oracle SQL documentation explains different types of joins, including inner joins, left and right outer joins, and full outer joins, clarifying how they differ in the result sets they produce.

A. True, arithmetic operators of equal precedence are evaluated from left to right within an expression, according to the standard SQL operator precedence. B. True, multiple parentheses can be used in an expression to change the order of operations and override the default precedence of operators.

C, D, and E are not correct because: C. The + binary operator does not have the highest precedence; multiplication and division have higher precedence in SQL. D. NULLS do not influence the precedence of operators in an expression; they may affect the result of an operation but not the order in which operators are evaluated. E. The concatenation operator (||) has lower precedence than arithmetic operators in SQL expressions.

References:

Oracle documentation on operator precedence: Oracle Database SQL Language Reference

The correct way to compute the total annual compensation, which includes the monthly salary and the monthly commission (if any), is:

Option C: SELECT last_name, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) AS annual_comp FROM employees;

This statement takes the monthly salary and multiplies it by 12 to get the annual salary, and then adds the annual commission which is the monthly salary multiplied by the commission percentage (if any, else 0) and then by 12.

Options A, B, and D are incorrect because:

Option A: Does not handle the case where the commission percentage is NULL which would result in NULL for the entire expression when added to the monthly salary.

Option B: Does not consider that the commission percentage might be NULL which could lead to incorrect calculations (or NULL values if commission is NULL).

Option D: Incorrectly adds the monthly commission percentage directly to the annual salary without considering that the percentage needs to be applied to the salary.

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.

Both ROUND and TRUNC functions can be applied to numbers, and MOD is a function that returns the remainder of a division. The ROUND function rounds a number to a specified number of decimal places, which can be positive, zero, or negative. The TRUNC function truncates a number to a specified number of decimal places.

ROUND(MOD(25,3),-1) rounds the result of MOD(25,3), which is 1, to tens place, which results in 0. TRUNC(MOD(25,3),-1) truncates the result of MOD(25,3), which is 1, to tens place, which also results in 0.

Both are valid, but in this specific case, they give the same result because the remainder (1) when rounded or truncated to tens place (-1) will be 0.

Object privileges applicable to tables, views, and sequences in Oracle are:

B. REFERENCES can be granted only on tables. It allows the grantee to create a foreign key that refers to the table.

D. SELECT can be granted on tables and views. This allows the grantee to perform a SELECT on the table or view.

A, C, and E are incorrect. Specifically:

A is incorrect because DELETE cannot be granted on sequences; it is a privilege applicable to tables and views.

C is incorrect because INSERT cannot be granted on sequences; INSERT is applicable only to tables and views.

E is incorrect because ALTER is a privilege applicable to tables and indexes, not sequences.

References:

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

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, 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.

To return all rows for employees whose salary is greater than the average salary in their department, you would use either a subquery or an analytic function:

Option B:

SELECT ... FROM employees WHERE salary > AVG(salary) OVER (PARTITION BY department_id);

This uses the window function AVG with PARTITION BY to calculate the average salary per department, and it compares each employee’s salary to this average.

Option C:

SELECT ... FROM employees e1 WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

This correlated subquery compares each employee's salary to the average salary in their department using a subquery to calculate the average salary for that department.

Options A, D, and E are incorrect because:

Option A: The use of ANY with the subquery does not ensure comparison with the average salary of their respective department.

Option D: This is syntactically incorrect; the subquery alias avg_sal is not accessible outside the subquery.

Option E: The subquery does not correlate with the outer query to ensure that each employee's salary is compared to the average salary of their respective department.

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"

The LIKE operator is used in SQL to search for a specified pattern in a column. To find all customers residing in cities starting with 'D' followed by at least two characters, you need to use the LIKE operator with the appropriate wildcard pattern:

A. SELECT * FROM customers WHERE city LIKE 'D_%'; The underscore () wildcard character in SQL represents a single character. The percent (%) wildcard character represents zero or more characters. So 'D%' will match any city starting with 'D' followed by at least one character (as there is one underscore).

References:

Oracle Database SQL Language Reference 12c, specifically the section on pattern matching with the LIKE condition.

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.

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.

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"

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.

A. PRODUCT_ID can be assigned the PRIMARY KEY constraint.

In Oracle Database 12c, a PRIMARY KEY constraint is a combination of a NOT NULL constraint and a unique constraint. It ensures that the data contained in a column, or a group of columns, is unique among all the rows in the table and not null. Given the PRODUCT_ID is marked as NOT NULL, it is a candidate for being a primary key because we can assume that it is intended to uniquely identify each product in the table.

In data modeling and database design, the direction of a relationship typically includes:

Option B: a cardinality

Cardinality refers to the numerical relationships between two entities, indicating the number of instances of one entity that can or must be associated with each instance of another.

Option C: label

A label is often used to describe the role or purpose of the relationship in a way that clarifies the nature of the interaction between entities.

Option D: an optionality

Optionality indicates whether or not a relationship is mandatory or optional; in other words, it shows if an instance of an entity must be related to an instance of another entity or if it can exist without such a relationship.

Options A, E, and F are not part of the direction of a relationship:

Option A: An attribute is a property or characteristic of an entity, not the direction of a relationship.

Option E: A unique identifier uniquely identifies each instance of an entity, not a relationship's direction.

Option F: An entity is a thing with distinct and independent existence in a database, usually mapped to a table, not a part of the direction of a relationship.

In Oracle Database, when a transaction is not committed, the changes it makes are not visible to other sessions. This is due to Oracle's read consistency model, which means that Andrew will not be able to see the changes you have made until they are committed.

Option A is correct because, in Oracle, another session can modify rows that have not been locked by an uncommitted transaction.

Option C is incorrect because, as per Oracle's read consistency, the changes made by an uncommitted transaction are not visible to other users.

Option D is incorrect because Andrew can perform INSERT, UPDATE, or DELETE operations on the rows that you have not modified.

Option E is incorrect because, while Andrew will be able to SELECT from the table, he will still be able to modify rows that are not locked by your uncommitted update.

References:

Oracle Documentation on Transactions: Transactions

Oracle Documentation on Read Consistency: Read Consistency

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.

B: True. Multiple table insert statements, specifically the conditional INSERT ALL, can transform a single row from the source table into multiple rows in one or more target tables depending on the conditions specified in the WHEN clauses.

E: True. The unconditional INSERT ALL statement will insert rows into multiple target tables without any conditions. However, it does not require the same number of columns in both the source and target tables. The INSERT ALL syntax allows you to specify different columns for each target table into which rows will be inserted.

Multiple table insert operations allow for complex insert scenarios, where based on the data, one can insert into different tables or multiple times into the same table based on different conditions.

References:Oracle SQL reference details the use of INSERT ALL and INSERT FIRST clauses for inserting into multiple tables based on conditions specified.

A. This statement is false. In NVL2, the first expression is returned if it is not null.

B. This statement is false. NVL2 takes exactly three arguments, not any number of expressions.

C. This statement is false. COALESCE stops evaluating its list of expressions as soon as it finds the first non-null value.

D. This is true. COALESCE returns the first non-null expression in its list.

E. This statement is true. NVL requires the first and second expressions to be of the same or compatible data types because it returns the first expression if it is not null, otherwise it returns the second.

F. This statement is false. NVL takes exactly two arguments, not any number.

References:

Oracle Documentation on COALESCE: https://docs.oracle.com/database/121/SQLRF/functions040.htm#SQLRF00625

Oracle Documentation on NVL: https://docs.oracle.com/database/121/SQLRF/functions130.htm#SQLRF00683

Oracle Documentation on NVL2: https://docs.oracle.com/database/121/SQLRF/functions123.htm#SQLRF00682

The function ADD_MONTHS(hire_date, 6) adds 6 months to the hire_date. The function NEXT_DAY(date, 'day_name') finds the date of the first specified day_name after the date given. In this case, 'MONDAY' is used to find the date of the first Monday after the hire_date plus 6 months.

Option A is correct as it accurately composes both ADD_MONTHS and NEXT_DAY functions to fulfill the requirement.

Options B, C, and D do not provide a valid use of the NEXT_DAY function, either because of incorrect syntax or incorrect logic in calculating the required date.

The Oracle Database SQL Language Reference for 12c specifies how these date functions should be used.

In Oracle SQL, the correct way to check for non-null values is to use the IS NOT NULL condition. Using != NULL or <> NULL is incorrect because NULL represents the absence of any value, and you cannot use equality or inequality operators to check for NULL.

C. SELECT cust_first_name, cust_credit_limit * .05 AS DUE_AMOUNT FROM customers WHERE cust_income_level IS NOT NULL AND cust_credit_limit IS NOT NULL; This query will correctly filter out any customers with a NULL income level or credit limit and then calculate the due amount as 5% of their credit limit for the remaining customers.

References:

Oracle Database SQL Language Reference 12c, especially sections on conditions and expressions that deal with NULL values.

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

The scenario requires prompting for column names and WHERE condition each time the query is executed, but only prompting for the table name once. This behavior is achievable through the use of substitution variables in SQL*Plus or SQL Developer:

A.

SELECT &col1, &col2 FROM &&table WHERE &condition;

This query uses & for columns and condition, which will prompt every time the query is run. &&table will prompt for the table name the first time and reuse the same value in the same session without re-prompting.

Options B, C, D, and E use incorrect syntax or variable types, leading to various errors or undesired behaviors, such as not maintaining the value of the table name across executions or incorrect usage of single quotes and comparison in SQL.

The statement that will fail is B. In Oracle SQL, the WHERE clause cannot contain aggregate functions directly. The HAVING clause is used instead to apply conditions that involve aggregates, but it is applied after the GROUP BY clause.

A. While the HAVING clause is used before the GROUP BY clause which is not standard SQL syntax, Oracle SQL may still execute it successfully due to its flexibility in syntax.

B. This statement will fail because it uses an aggregate function, COUNT(*), in the WHERE clause, which is not allowed. The correct approach is to use the HAVING clause to filter the results of aggregate functions after the GROUP BY clause.

C. This statement is correct; it places the HAVING clause after the GROUP BY clause, applying the filter on the aggregated count.

D. This is a correctly constructed statement with the WHERE clause filtering individual records before grouping, and the HAVING clause filtering groups based on the aggregate function.

References:

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

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

Based on the given PRODUCTS table and the SQL queries provided:

Query 1: Excludes rows where prod_list is 10 or 20 and category_id is 1.

Query 2: Includes rows where prod_list is neither 10 nor 20 and category_id is 1.

Query 3: Excludes rows where prod_list is both 10 and 20 (which is not possible for a single value) and category_id is 1.

The correct answer is A, queries 1 and 3 will produce the same result. Both queries exclude rows where prod_list is 10 or 20 and include only rows from category_id 1. The NOT IN operator excludes the values within the list, and <> ALL operator ensures that prod_list is not equal to any of the values in the list, which effectively excludes the same set of rows.

Query 2, using the <> ANY operator, is incorrect because this operator will return true if prod_list is different from any of the values in the list, which is not the logic represented by the other two queries.

A. True, this statement will execute successfully. In Oracle SQL, column aliases can be given in double quotes which allows the use of special characters like a hyphen. Additionally, you can perform arithmetic operations such as amount+100 directly in the SELECT clause to return increased values of amount.

C. True, this statement is also valid. It does not use any special characters or reserved keywords as aliases that would require double quotes. The arithmetic operation is the same as in option A, which is permissible.

For B, D, and E, the reasons for failure are: B. In Oracle SQL, single quotes are used for string literals, not for column aliases. Therefore, using single quotes around 'CUSTOMER-ID' and 'DUES' will result in an error. D. The word "DATE" is a reserved keyword in Oracle SQL. When used as a column alias without double quotes, it will lead to an error. In this statement, although "DATE" is in double quotes, it's a best practice to avoid using reserved keywords as aliases. E. There are syntax errors in the statement. "customer id" should be "customer_id", and the alias "CUSTOMER-ID" should be enclosed in double quotes if special characters are used. Also, "DUES AMOUNT" as an alias should be in double quotes to be valid because it contains a space.

References:

Oracle documentation on column aliases: Oracle Database SQL Language Reference

Oracle reserved words: Oracle Database SQL Language Reserved Words

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

Analyzing the given SQL statements on the ORDER_ITEMS table:

D. Statement 2 may return multiple rows of output: Statement 2 groups the results by ORDER_ID, which means it calculates the maximum UNIT_PRICE * QUANTITY for each ORDER_ID, potentially returning multiple rows depending on the number of unique ORDER_IDs in the table.

E. Statement 1 returns only one row of output: Statement 1 computes the maximum product of UNIT_PRICE and QUANTITY across all entries in the ORDER_ITEMS table, returning a single row with the maximum value.

Incorrect options:

A: Since Statement 2 groups by ORDER_ID, it does not necessarily return just one row; it returns one row per ORDER_ID.

B: These statements do not yield the same output; Statement 1 returns a single maximum value, while Statement 2 returns the maximum value per ORDER_ID.

C: If either UNIT_PRICE or QUANTITY is NULL, the product for that row will be NULL, but the MAX function ignores NULL values in its calculation unless all rows are NULL, in which case it returns NULL.

For the provided DELETE statement with an EXISTS clause:

Option B: The subquery is executed before the DELETE statement is executed.

Subqueries with EXISTS are typically executed before the outer DELETE statement to determine which rows of the outer query satisfy the condition.

Option D: The DELETE statement executes successfully even if the subquery selects multiple rows.

The EXISTS condition is used to check for the existence of rows returned by the subquery, regardless of how many rows there are. It returns TRUE if the subquery returns at least one row.

Options A, C, and E are incorrect because:

Option A: This statement is incorrect; the subquery is indeed a correlated subquery because it references the employee_id from the outer query (employees).

Option C: This is incorrect because not all existing rows in the EMPLOYEES table will be deleted, only those for which an associated record exists in the emp_history table.

Option E: While technically the subquery may be evaluated multiple times, it is only executed for those rows in EMPLOYEES that satisfy the condition of the EXISTS clause.

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.

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 statement is correct. It orders the employees by salary and fetches the first 3 rows.

B: This statement has a typo with "FETCE" and "RONS" which should be "FETCH" and "ROWS". Hence, it will not execute successfully.

C: This statement will not execute successfully due to syntactical errors and incorrect use of the ORDER BY clause.

D: This statement is correct. It uses a subquery to order employees by salary and then limits the results to the first 3 using the ROWNUM pseudo-column.

E: This statement will not execute successfully because ROWNUM is evaluated before the ORDER BY clause, so it would return the first 3 rows based on the table's natural order, not the lowest salaries.

The behavior of ROWNUM and the FETCH FIRST syntax are explained in the Oracle Database SQL Language Reference 12c.

For the given SQL commands:

Option A: ROLLBACK;

A ROLLBACK will undo all changes made in the transaction up to the last COMMIT.

Option C: ROLLBACK TO SAVEPOINT post_insert;

ROLLBACK TO SAVEPOINT will undo changes back to the named savepoint, which in this case is after the first insert.

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.

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

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

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

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

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

In Oracle SQL, the ALTER TABLE statement is used to modify the definition of a table after it has been created. Specific changes are allowed depending on the current data type of the column and the data it contains.

A. You cannot change a VARCHAR2 column to NUMBER if the table contains rows with non-numeric values, which is the case with '123' as a VARCHAR2; it is a string, not a number.

B. You can reduce the size of a NUMBER column if the data will still fit within the new precision, but you cannot change a VARCHAR2 column to a smaller size if it contains values that exceed the new size limit.

C. You cannot change a NUMBER column to VARCHAR2 if the table contains rows because the existing data type of NUMBER does not match the new data type of VARCHAR2.

D. Similar to A, you cannot change the data type from VARCHAR2 to NUMBER if non-numeric data is present.

E. You can change a VARCHAR2(10) column to VARCHAR2(5) if all existing rows have values that fit within the new size, and you can change a NUMBER column to have a different scale (NUMBER(12,2)).

References:

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

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.

C. True. A transaction that is interrupted by a system failure is automatically rolled back by Oracle to ensure data consistency. This is part of the ACID (Atomicity, Consistency, Isolation, Durability) properties that Oracle adheres to.

D. True. A ROLLBACK without specifying a savepoint rolls back the entire transaction and removes all savepoints within that transaction. This restores the data to the state it was in at the beginning of the transaction.

In Oracle Database 12c SQL, regarding date/time functions and considering a session where NLS_DATE_FORMAT is set to DD-MON-YYYY SH24:MI:SS:

C. CURRENT_DATE returns the current date and time as per the session time zone. This is correct as CURRENT_DATE returns the current date and time in the time zone of the current SQL session, as set by the ALTER SESSION command.

D. SYSDATE and CURRENT_DATE return the current date and time set for the operating system of the database server. This is partially correct. SYSDATE returns the current date and time from the operating system of the database server. However, CURRENT_DATE returns the date and time set for the client's operating system environment, adjusted to the session time zone.

Options A, B, E, and F are incorrect based on Oracle's documentation:

A is incorrect because SYSDATE is independent of the NLS_DATE_FORMAT setting.

B is incorrect because CURRENT_TIMESTAMP includes time zone information, which can differ from CURRENT_DATE.

E is incorrect because CURRENT_TIMESTAMP differs from SYSDATE by including fractional seconds and time zone.

F is incorrect as SYSDATE can be queried in any SELECT statement, not just from DUAL.

D. only &mgr_id: Since the hiredate variable is already defined before the SELECT statement, you will not be prompted for &hiredate again. However, &mgr_id has not been defined, so you will be prompted for this substitution variable.

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.

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.

To find employees whose manager works in a different department, you can use a subquery or a join that compares the DEPARTMENT_ID of the employee with the DEPARTMENT_ID of their manager.

A. This query is incorrect because the NOT IN subquery incorrectly attempts to compare EMPLOYEE_ID with MANAGER_ID, and the correlation condition inside the subquery is incorrect.

B. This query is correct. The NOT EXISTS clause correctly identifies employees whose MANAGER_ID matches the EMPLOYEE_ID of another employee (mgr) and where the DEPARTMENT_ID differs from that manager's DEPARTMENT_ID.

C. This query is incorrect because the LEFT JOIN will return all employees, and there is no WHERE clause to filter out those employees whose managers are in the same department.

D. This query is incorrect. The RIGHT JOIN does not ensure that the resulting rows are for employees whose manager works in a different department. It also returns all managers, which is not the requirement.

E. This query is correct. The JOIN ensures that the returned rows are for employees (emp) whose MANAGER_ID matches the EMPLOYEE_ID of managers (mgr), and it correctly filters to include only those employees whose DEPARTMENT_ID is different from their manager's DEPARTMENT_ID.

In Oracle Database 12c, when dealing with NULL values in arithmetic expressions, the functions NVL, NVL2, and equivalent techniques using NULLIF or arithmetic operations with NULL can be crucial.

Option A: SELECT * FROM employees WHERE salary + NULLIF(commission, 0) >= 20000;

NULLIF(value1, value2) returns NULL if value1 is equal to value2, otherwise it returns value1. In this case, if commission is NULL, it treats it as 0.

This query correctly adds salary and commission (assuming 0 when commission is NULL) and checks if the total is at least 20000.

Option B: SELECT * FROM employees WHERE salary + NVL2(commission, commission, 0) >= 20000;

NVL2(expr1, expr2, expr3) returns expr2 if expr1 is not NULL; otherwise, it returns expr3. Here, if commission is not NULL, it uses commission, otherwise 0.

This condition correctly calculates salary + commission (assuming commission as 0 if it is NULL) and checks if the total is at least 20000.

Option D: SELECT * FROM employees WHERE salary + NVL(commission, 0) >= 20000;

NVL(expr1, expr2) returns expr2 if expr1 is NULL; otherwise, it returns expr1. This query treats commission as 0 if it is NULL.

It correctly sums salary and commission and compares the result against 20000.

Options C, E, and F contain either syntax errors or logical errors in handling NULLs and comparisons.

A: This statement will not execute successfully because you cannot subtract a DAY interval from a DATE directly. SYSDATE needs to be cast to a TIMESTAMP first.

B: This statement is correct. It adds an interval of '1' DAY to the current TIMESTAMP.

C: This statement is correct. It subtracts an interval of '1' MINUTE from '1' DAY.

D: This statement will not execute successfully. In Oracle, you cannot add intervals of different date fields (DAY and MONTH) directly.

E: This statement has a syntax error with the quotes around INTERVAL and a misspelling, it should be 'INTERVAL'.

Oracle Database 12c SQL supports interval arithmetic as described in the SQL Language Reference documentation.

B. True. DDL (Data Definition Language) statements in Oracle Database are auto-commit statements. This means that if a DDL statement is issued, any uncommitted transactions in the same session will automatically be committed.

D. False. This is a common misconception. DML (Data Manipulation Language) statements do not automatically start new transactions. In Oracle, a transaction begins when the first DML statement (INSERT, UPDATE, DELETE, MERGE, SELECT FOR UPDATE) is encountered after a previous transaction has been committed or rolled back.

A is incorrect because DDL statements automatically commit the entire transaction, not just the data dictionary updates. C is incorrect because an uncommitted transaction is not automatically committed when a user exits SQL*Plus; instead, it is rolled back. E is incorrect because a session cannot see uncommitted updates made by other sessions.

The correct statement for computing the total annual compensation for each employee is option C. This is because the monthly commission is a percentage of the monthly salary (indicated by the column name MONTHLY_COMMISSION_PCT). To calculate the annual compensation, we need to calculate the annual salary (which is monthly_salary * 12) and add the total annual commission to it.

Here's the breakdown of the correct statement, option C:

(monthly_salary * 12) computes the total salary for the year.

NVL(monthly_commission_pct, 0) replaces NULL values in the monthly_commission_pct column with 0, ensuring that the commission is only added if it exists.

(monthly_salary * 12 * NVL(monthly_commission_pct, 0)) computes the annual commission by first determining the monthly commission (which is a percentage of the monthly salary), and then multiplying it by 12 to get the annual commission.

Finally, (monthly_salary * 12) + (monthly_salary * 12 * NVL(monthly_commission_pct, 0)) adds the annual salary and the annual commission to get the total annual compensation.

The other options are incorrect:

Option A is incorrect because it adds the monthly_commission_pct directly to the monthly_salary, which does not consider that monthly_commission_pct is a percentage.

Option B is incorrect because it adds the commission percentage directly without first applying it to the monthly salary.

Option D is incorrect because it does not handle the NULL values in the commission column, which would result in a NULL total annual compensation whenever the monthly_comission_pct is NULL.

References:

Oracle Documentation on NVL function: NVL

Oracle Documentation on Numeric Literals: Numeric Literals

For displaying unique promotion costs in each promotion category from the PROMOTIONS table:

A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1: This query correctly retrieves unique combinations of promo_category and promo_cost, sorting the results based on the promo_category.

D. SELECT DISTINCT promo_category || ' has ' || promo_cost AS COSTS FROM promotions ORDER BY 1: This query correctly combines the category and cost into a single string for each unique combination, using string concatenation and the DISTINCT keyword to ensure uniqueness, sorting by the concatenated string.

Incorrect options:

B: This does not ensure that the combinations of promo_cost and promo_category are unique because DISTINCT was not specified for both columns together.

C: The syntax is incorrect; DISTINCT cannot be applied to a single column partway through a SELECT clause.

E: This is syntactically incorrect as DISTINCT cannot be used twice in the way shown; it must apply to all columns if used at the beginning of a SELECT statement.

Regarding creating tables in an Oracle database:

B. The same table name can be used for tables in different schemas: In Oracle, a schema is essentially a namespace within the database; thus, the same table name can exist in different schemas without conflict, as each schema is distinct.

C. A system privilege is required: To create tables, a user must have the necessary system privileges, typically granted explicitly or through roles such as CREATE TABLE or administrative privileges depending on the environment setup.

Incorrect options for all three repeated questions:

A: Oracle SQL does not allow specifying the maximum number of rows directly in a CREATE TABLE statement; this is controlled by storage allocation and database design rather than table creation syntax.

D: Creating an external table does not create the physical file. It merely creates a table structure that allows access to data stored in an external file specified in the directory; the file itself must already exist or be managed outside of Oracle.

E: A primary key constraint is not mandatory for creating tables. While it is a common practice to define a primary key to enforce entity integrity, it is not required by the Oracle SQL syntax for table creation.

These answers and explanations are aligned with Oracle Database 12c SQL documentation and standard practices.

A: Incorrect because the CROSS JOIN of the BRICKS table with itself will produce more than one row.

B: Incorrect because CROSS JOINing a table with four rows with itself produces 16 rows, but the WHERE condition filters these down.

C: Incorrect because while a CROSS JOIN of a table with four rows with itself produces 16 rows, this answer does not take into account the WHERE condition.

D: Correct. The CROSS JOIN will result in 16 combinations (4x4), but the WHERE condition b1.weight < b2.weight will only be true for combinations where the second weight is greater than the first. Since there are 4 distinct weights, there are (42)=6(24​)=6 combinations where one weight is less than the other.

E: Incorrect because this does not account for the conditions specified in the WHERE clause.

F: Incorrect because the query will return some rows due to the condition specified.

Indexes are structures that can improve the retrieval time of data from a database table and have certain characteristics:

A. A SELECT statement can access one or more indices without accessing any tables: If the index contains all the columns needed for the query, Oracle can retrieve the data from the index alone without accessing the table. This is known as an index-only scan.

D. A UNIQUE index can be altered to be non-unique: It is possible to alter a unique index to become non-unique by using the ALTER INDEX command.

F. An update to a table can result in updates to any or all of the table's indexes: If the updated column is part of one or more indexes, those indexes will need to be updated to reflect the change. If the updated column is not part of an index, then no index update is required.

References:

Oracle Database SQL Language Reference 12c, especially sections on index management and the behavior of DML operations with respect to indexes.

A. True. This statement will work if customer_id is a character data type in the database. Oracle will implicitly convert the numeric literal 0001 to a string to compare with customer_id.

D. True. If the insert_date is of type DATE and the NLS_DATE_FORMAT matches 'DD-MON-YY', Oracle will implicitly convert the string literal '01-JAN-19' to a date type to compare with insert_date.

B is incorrect because if customer_id is a numeric data type, there is no need for implicit conversion. C is incorrect because using the DATE literal DATE '2019-01-01' is an explicit conversion. E is incorrect because TO_CHAR(customer_id) is an explicit conversion from a numeric to a string data type.

In Oracle SQL, when performing a JOIN operation, the ON clause is used to specify the condition that relates the two tables being joined. The WHERE clause can be used to further filter the result set.

A) This is a valid join condition using the WHERE clause to filter the rows after the join has been made.

B) This statement will fail because the ON clause should only contain conditions that relate the two tables. The condition for filtering the departments table should be in the WHERE clause, not in the ON clause. This is a common mistake when writing JOIN statements.

C) This is a correct statement. The ON clause specifies how the tables are related and the WHERE clause specifies an additional filtering condition for the query.

D) This statement is also correct. It's similar to the first statement (A) and properly places the department_id filter in the ON clause, which is acceptable though not typically best practice as it can be less readable than using a WHERE clause for non-join conditions.

When the JOIN operation is executed, the database first pairs rows from the joined tables that meet the join condition specified by the ON clause. Then, it filters the result of the JOIN operation based on the condition specified in the WHERE clause.

References:

Oracle Documentation on Joins: https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52359

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. True, when using the ORDER BY clause with set operators like UNION, you can refer to the results by column positions. This allows for consistent sorting behavior across potentially heterogeneous SELECT statements.D. True, only the column names or positions from the first SELECT statement are recognized in the ORDER BY clause when used with set operators like UNION, as the result set is treated as if it originated from the first SELECT structure.

References:

Oracle documentation on ORDER BY with set operators: Oracle Database SQL Language Reference

Explanation of ORDER BY usage: Oracle SQL Tips

Top of Form

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.

The NULLIF and COALESCE functions in Oracle SQL serve specific purposes:

Option A: Executes successfully. NULLIF returns NULL if the two arguments are equal; otherwise, it returns the first argument.

Option B: Also correct. COALESCE returns the first non-null value among its arguments.

Option C: This will not execute successfully because NULLIF requires both arguments to be of comparable data types. Comparing an integer to a character ('100' to 'A') is invalid.

Option D: Executes successfully. NULLIF returns NULL because it compares NULL to a number, which is valid (though always yields NULL).

Option E: Executes successfully. COALESCE accepts any data type as it returns the first non-null value, irrespective of type consistency among the arguments.

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.

A view is a virtual table based on a SQL query.

A. This is incorrect because a SELECT statement querying a view can contain a WHERE clause, regardless of the view’s defining query. C. This is incorrect because views can join tables from different schemas, not just the same schema. B. Correct. The rows inserted into a base table via a view remain in the table even if the view is dropped because the view is just a window to the data in the base tables. D. Correct. Views do not require storage space other than for the definition of the view in the data dictionary, hence they have no segment. E. Incorrect. Views do not have object numbers because they are not database objects that occupy physical space. F. Correct. You can create a view that references non-existent tables; such a view would be considered invalid until the base table is created.

The Oracle Database Concepts guide provides information about views and their characteristics.