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.

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

Sequences are database objects used to generate unique numeric identifiers. Here ' s the correct understanding of sequences in Oracle:

A: Correct. Cached sequence values that are not yet used are lost if the database instance shuts down, as the cache is held in memory.

B: Incorrect. The same sequence can be used to generate keys for more than one table. There is no such limitation.

C: Correct. If a transaction using a sequence number is rolled back, the sequence number that was used or allocated is not reused.

D: Incorrect. By their nature and configuration, sequences are designed to avoid issuing duplicate values unless explicitly designed to cycle.

E: Correct. Sequences may have gaps, which can occur due to caching, sequence increment settings, or if a transaction using a sequence number is rolled back.

F: Incorrect. A sequence can be dropped by any user with adequate privileges, not just a DBA.

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.

Regarding the precedence of operators and conditions in Oracle Database 12c:

Option B : NOT has a higher order of precedence than AND and OR in a condition.

In logical operations, NOT is evaluated first, followed by AND and then OR .

Option E : || has a higher order of precedence than +(addition).

The string concatenation operator || has a higher precedence than the arithmetic + operator.

Options A, C, and D are incorrect because:

Option A : Multiplication ( * ) has a higher precedence than addition ( + ).

Option C : AND has a higher precedence than OR .

Option D : Conditions (which may contain operators) are evaluated according to the rules of operator precedence.

Global temporary tables in Oracle Database are designed to hold temporary data for the duration of a session or a transaction.

Given that the global temporary table is created and then populated twice from the emp table without any mention of data deletion, one would initially think the count would be 28, since 14 rows are inserted twice. However, if the temporary table was created with the default ON COMMIT DELETE ROWS option, the rows are deleted at the end of the transaction, which is signified by the COMMIT operation.

Thus, after the first COMMIT, the temporary table would be empty. After the second INSERT, another 14 rows would be added. So, the last query would retrieve 14 rows unless a DELETE operation was committed.

For the SELECT statement from two tables without a JOIN clause:

Option B : You can add a WHERE clause with filtering criteria.

A WHERE clause can be added to a query to filter the results based on specified conditions.

Option E : It returns the same rows as SELECT * FROM bricks CROSS JOIN colors .

A comma-separated list of tables in a FROM clause is equivalent to a CROSS JOIN , resulting in a Cartesian product of the tables.

Options A, C, and D are incorrect because:

Option A : You cannot add an ON clause without changing the comma-separated FROM clause to a proper JOIN .

Option C : It returns the Cartesian product, not the sum, of the rows from BRICKS and COLORS .

Option D : A USING clause is not applicable without changing the syntax to a proper JOIN .

When joining tables in Oracle Database 12c, the USING clause has specific behaviors:

Option C : It is used to specify an equijoin of columns that have the same name in both tables.

The USING clause is indeed used to specify an equijoin between two tables based on columns with identical names in the tables being joined. It simplifies the syntax of the JOIN operation by eliminating the need to qualify the joined columns with table names.

Option D : It can never be used with a full outer join.

The USING clause cannot be used with a full outer join because the full outer join requires a specification of how to treat each side of the join, including rows that don ' t match the join condition, which is not compatible with the semantics of the USING clause.

Options A, B, and E are incorrect:

Option A is incorrect because when using the USING clause, you do not need to qualify the columns with table names or aliases in the select list; Oracle assumes that they are the same in both tables.

Option B is incorrect because the USING clause can be used with natural joins.

Option E is incorrect as the USING clause is not meant to specify explicit join conditions with operators; it ' s specifically for equijoins on columns of the same name.

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.