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 . The MAX and MIN functions can be applied to columns with character data types. These functions will return the highest or lowest values in a set of rows, based on alphabetical order for character data. This allows these aggregate functions to be versatile and useful across different data types, including numerical, date, and character types.

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

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

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

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

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

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

References:

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

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

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

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

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

In the case of SQL commands TRUNCATE and DELETE :

A. the result of a delete can be undone by issuing a rollback: DELETE is a DML operation that affects rows individually and can be rolled back if it is performed within a transaction.

B. delete can use a where clause to determine which row(s) should be removed: DELETE operation allows the use of a WHERE clause to specify which rows should be deleted based on certain conditions.

Incorrect options are:

C: TRUNCATE does not support the use of a WHERE clause. It is designed to remove all rows from a table swiftly and cannot be conditional.

D: TRUNCATE does not leave indexes in an unusable state; it simply removes all rows.

E: TRUNCATE is a DDL command and its operation typically cannot be rolled back in many SQL database systems, including Oracle.

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.

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

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

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

Global temporary tables (GTTs) in Oracle have unique properties related to session-based visibility and data persistence:

B. A TRUNCATE command issued in a session causes all rows in a GLOBAL TEMPORARY TABLE for the issuing session to be deleted: True, TRUNCATE is session-specific for GTTs and will remove all rows inserted by the session that issues the TRUNCATE command.

D. A GLOBAL TEMPORARY TABLE ' s definition is available to multiple sessions: The structure of a GTT is persistent and shared across sessions, but the data within is session-specific.

E. Any GLOBAL TEMPORARY TABLE rows existing at session termination will be deleted: The data in GTTs is private for the session, and when the session ends, the data is deleted automatically.

References:

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

Scalar subquery expressions in Oracle SQL have specific rules:

Option C : You must enclose them in parentheses.

Scalar subqueries must be enclosed in parentheses. This is a requirement for syntax clarity and to distinguish the subquery from the rest of the SQL statement.

Option D : They can return at most one row.

Scalar subqueries are designed to return exactly one row containing one column. If a scalar subquery returns more than one row, Oracle will throw an error, ensuring that the subquery either returns a single value or no value (NULL).

Options A, B, and E are incorrect based on Oracle SQL functionalities:

Option A is incorrect because scalar subqueries can indeed be correlated with the parent query.

Option B is true but not in the context of default constraints for table columns in the CREATE TABLE statement.

Option E is incorrect because scalar subqueries can only return a single column by definition.

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

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

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

To find customer names where the last name starts with Mc or MC, the correct queries are:

A. WHERE UPPER(SUBSTR(cust_name, INSTR(cust_name, ' ' ) + 1)) LIKE ' MC% ' This query converts the substring of cust_name that comes after the first space (which should correspond to the last name) to uppercase and checks if it starts with ' MC ' , which will match both ' Mc ' and ' MC ' .

E. WHERE SUBSTR(cust_name, INSTR(cust_name, ' ' ) + 1) LIKE ' Mc% ' This query checks if the substring of cust_name after the first space starts with ' Mc ' . However, this query will only match last names that start with ' Mc ' and not ' MC ' unless the database is using a case-insensitive collation.

Options B, C, and D are incorrect:

B is incorrect because the syntax is not valid in Oracle SQL; the LIKE clause cannot be used to match multiple patterns in that way.

C is incorrect because INITCAP would not only capitalize the first letter of ' mc ' or ' Mc ' but would lowercase all other letters, which is not the intended action.

D is incorrect because, like option C, INITCAP is not the appropriate function for this use case and it will not correctly identify names that start with ' MC ' .