Oracle Database Fundamentals Quiz

Statement 4 is correct because accessing the same table that fired the trigger within the trigger causes a mutating table error in Oracle. This is a well-known restriction. Statements 1 and 3 are incorrect: INSTEAD OF triggers can only be used on views (not tables), and compound triggers don't allow multiple timing events on the same object in the manner described. Statement 2 is incorrect as INSTEAD OF triggers only fire at the statement level, not row level.

Creating a PRIMARY KEY or UNIQUE constraint automatically generates a corresponding index to enforce uniqueness. CHECK and NOT NULL constraints do not automatically create indexes.

The UNION operator combines query results and eliminates duplicate rows. Conversely, the UNION ALL operator combines all rows from the queries, retaining duplicate rows in the final result set.

TRUNCATE is a DDL command, so it cannot be run directly in PL/SQL without dynamic SQL, and it automatically commits. DELETE is a DML command that removes data without resetting the high-water mark.

The OPTIMIZER_MODE initialization parameter in Oracle defaults to CHOOSE. This setting allows the optimizer to choose between rule-based and cost-based optimization depending on whether statistics are available for the tables in the query.

A table can have only one primary key constraint, though that primary key can consist of multiple columns (a composite key). The statement asks about multiple primary keys, which is not allowed in standard SQL - hence the answer is False.

Aggregate functions perform calculations on multiple values and return a single result. SUM and AVG are standard SQL aggregate functions. Median is also an aggregate function (though not available in all SQL dialects). Trim is a string manipulation function that operates on individual values, not sets.

Unique constraints in SQL allow multiple NULL values because NULL represents unknown or missing data, not an actual value. The uniqueness check only applies to non-NULL values - each NULL is considered distinct from other NULLs. Therefore, you can insert as many NULL values as needed into a column with a unique constraint.

SQL allows multiple CHECK constraints on a single column because each constraint is evaluated independently. For example, you could have one CHECK ensuring age >= 18 and another ensuring age <= 65 on the same column. All CHECK constraints must be satisfied for any INSERT or UPDATE operation on that column.

Oracle data dictionary views provide metadata about database objects. USER_CONSTRAINTS displays constraint definitions for the current user's tables. USER_CONS_COLUMNS shows which columns participate in which constraints. USER_TABLES and ALL_TAB_COLS contain table and column information but not constraint details.

In PL/SQL and SQL, CASE statements support complex logical conditions and non-equality operators (like < or >), whereas DECODE is limited to equality checks. CASE also handles multiple distinct conditions. Distractors incorrectly equate them.

ROUND(45.953, -1) rounds to the tens place (negative precision means left of decimal): 45.953 becomes 50 because it's closer to 50 than 40. TRUNC(45.936, 2) truncates to 2 decimal places: 45.936 becomes 45.93 (truncates, doesn't round). Result: 50 and 45.93.

DML (Data Manipulation Language) commands manipulate data within tables: MERGE combines insert and update operations, UPDATE modifies existing rows, and DELETE removes rows. In contrast, CREATE and DROP are DDL (Data Definition Language) commands that define database structures, and COMMIT is a transaction control command.

The TO_CHAR(USER) function converts a username string to character format, creating a type mismatch when inserting into a DATE column (last_stock_date). DATE columns require proper date values like SYSDATE or TO_DATE expressions, not string conversions. The TO_CHAR function returns a VARCHAR2, which cannot be directly inserted into a DATE column without implicit conversion.

To understand the given set of SQL statements, the user must have knowledge of SQL commands and their functions.

• CREATE TABLE statement creates a table with the specified columns and data types.

• ROLLBACK statement rolls back the current transaction and undoes all the changes, freeing the storage space occupied by the table.

• DESCRIBE statement displays the structure of the specified table.

Now, let's evaluate each option:

A. The DESCRIBE DEPT statement displays the structure of the DEPT table.

This option is correct. The DESCRIBE statement is used to display the structure of the specified table, and in this case, the specified table is DEPT. So, the DESCRIBE DEPT statement will display the structure of the DEPT table.

B. The ROLLBACK statement frees the storage space occupied by the DEPT table.

This option is incorrect. The ROLLBACK statement rolls back the current transaction and undoes all the changes, but it does not free the storage space occupied by the DEPT table. The table will still exist even after the ROLLBACK statement is executed.

C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.

This option is incorrect. The DEPT table was created using the CREATE TABLE statement, so it exists in the database. The DESCRIBE DEPT statement will display the structure of the DEPT table.

D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

This option is incorrect. The DESCRIBE statement displays the structure of the specified table regardless of the presence of a COMMIT statement. The ROLLBACK statement undoes all the changes made in the current transaction, but it does not affect the execution of the DESCRIBE statement.

Therefore, the correct answer is:

The Answer is: A. A

SYSDATE, USER, and USERENV are all built‑in Oracle system variables that provide session and environment information. Consequently the option stating “ALL THE ABOVE” correctly aggregates them, and the stored answer is accurate.

DECODE compares the expression ('B') against each search value in sequence. Since 'B' doesn't match any of the search values ('A', 'E', 'I', 'O', 'U'), the function returns the default value 'N/A' specified as the last argument. DECODE is similar to a CASE statement or if-else ladder.

To calculate the annual compensation as monthly salary plus a monthly bonus of $100, multiplied by 12, we need to modify the expression "12*sal+100" in the SELECT statement.

Option A: No change is required to achieve the desired results. This option is incorrect because the original expression "12*sal+100" calculates the total compensation as monthly salary multiplied by 12, plus a fixed monthly bonus of $100. It does not add the bonus to the monthly salary before multiplying by 12.

Option B: SELECT ename, sal, 12*(sal+100) FROM emp; This option is correct. By adding the monthly bonus of $100 to the monthly salary before multiplying by 12, we get the correct annual compensation. The expression "12*(sal+100)" calculates the annual compensation as monthly salary plus bonus, multiplied by 12.

Option C: SELECT ename, sal, (12sal)+100 FROM emp; This option is incorrect because the parentheses around "12sal" are unnecessary. The multiplication operator has higher precedence than the addition operator, so "12*sal" is evaluated before adding 100.

Option D: SELECT ename, sal+100,12 FROM emp; This option is incorrect because the syntax is incorrect. The asterisk () is not a valid operator in this context. We need to use the multiplication operator (*) to calculate the annual compensation.

Therefore, the correct answer is: B. SELECT ename, sal, 12*(sal+100) FROM emp;

The RENAME command (Oracle syntax) requires the TO keyword between the old and new names. The correct syntax is RENAME old_name TO new_name, without the TABLE keyword and with the TO separator. Options A and C are missing the TO keyword, while D incorrectly adds the TABLE keyword.