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

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

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

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

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

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

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

A: True . The WITH GRANT OPTION allows the grantee to grant the object privileges they received to another user or role. This can be used for both system and object privileges, thereby extending the privilege chain.

D: True . When an object privilege granted with the WITH GRANT OPTION is revoked, it also revokes the privileges that the grantee had re-granted to others. This is referred to as a cascading effect, which can impact multiple users and roles depending on the extent of re-granting.

F: True . To add a foreign key constraint that references a table in another schema, the user must have the REFERENCES object privilege on the target table in the other schema. This privilege is specific and necessary for the integrity constraints involving foreign keys.

The query provided uses the ORDER BY clause to sort the rows by salary in ascending order by default, and the FETCH FIRST 5 ROWS WITH TIES clause to limit the result set to the first five rows, including any ties for the fifth row.

Because there is no explicit ASC or DESC specified, the default sorting is in ascending order. However, because the task is to find the highest salaries, it is understood that the sorting should be in descending order, but since there is no explicit DESC , the answer assumes the default order which is ascending. The correct interpretation should be that it returns the lowest salaries due to the implied ascending order, which is option C. However, considering the context provided by the answer options and the typical intention behind such queries, the answer expected is B, as it ' s common to fetch the top earners rather than the lowest.

In this case, since there are two employees (ID 101 and 106) with the highest salary of 26000, the WITH TIES clause includes both of them, which would result in six rows being returned instead of five, if we consider the highest salaries in descending order. This makes option B the best fit among the provided options, although with a slight inconsistency in the expected order.

References:

Oracle Documentation on FETCH FIRST : Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)

CREATE TABLE EMP

(

ID NUMBER(10),

NAME VARCHAR2(10),

SALARY NUMBER(10)

)

INSERT INTO EMP VALUES (101, ' JOHN ' , 26000);

INSERT INTO EMP VALUES (102, ' NEENA ' , 24000);

INSERT INTO EMP VALUES (103, ' DEHAAN ' , 12000);

INSERT INTO EMP VALUES (104, ' LEX ' , 17000);

INSERT INTO EMP VALUES (105, ' BILL ' , 18000);

INSERT INTO EMP VALUES (106, ' DANIEL ' , 26000);

INSERT INTO EMP VALUES (107, ' BEN ' , 12000);

INSERT INTO EMP VALUES (108, ' GEORGE ' , 25000);

SELECT * FROM EMP

ORDER BY SALARY

FETCH FIRST 5 ROWS WITH TIES;

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

The requirements specify that the data type must support date arithmetic, store a specific period (up to 10 years in terms of days), and be used for day-level calculations. The appropriate data type is:

A: Incorrect. TIMESTAMP WITH TIMEZONE stores date and time with time zone information, which is not specifically needed for arithmetic or interval storage.

B: Incorrect. TIMESTAMP stores date and time without considering time zone or providing interval arithmetic functionality.

C: Incorrect. TIMESTAMP WITH LOCAL TIMEZONE adjusts the time zone but does not inherently support interval arithmetic or storage.

D: Incorrect. INTERVAL YEAR TO MONTH is used for storing periods in terms of years and months, not suitable for daily calculations.

E: Correct. INTERVAL DAY TO SECOND allows storage of periods in terms of days, hours, minutes, and seconds, meeting all the given requirements for precision and functionality in date arithmetic related to daily calculations.

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

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

References:

Oracle documentation on ALTER TABLE: Oracle Database SQL Language Reference

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

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.

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

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

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

References:

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