Oracle Database Fundamentals and SQL

A tablespace can only be dropped when it has no active segments (objects) occupying space. The online/offline status of the tablespace doesn't prevent dropping. The administrator must have the DROP TABLESPACE system privilege. Option D has a double negative making it confusing.

TRUNCATE is a DDL (Data Definition Language) operation that can fire triggers in database systems. Sysdate is a function, avg is an aggregate function, and to_date is a conversion function - none of these are events that fire triggers. Only DDL/DML operations like TRUNCATE can trigger database triggers.

In SQL, 'SELECT' is a reserved keyword. While it can be used as an object name if enclosed in double quotes (e.g., "SELECT"), using it unquoted as an identifier is invalid and will cause a syntax error.

To search for the literal pattern 'A_B' (where underscore is a literal character, not a wildcard), use LIKE '%A_B%' ESCAPE ''. The ESCAPE clause specifies that backslash is the escape character, so _ treats underscore as a literal. Option A uses double backslash incorrectly. Options B and C have wrong ESCAPE syntax - missing escape character or using % incorrectly.

A right outer join returns all rows from the right table (B) and matching rows from the left table (A). This is the opposite of a left outer join, which returns all rows from A. Cross joins produce Cartesian products and are not outer joins.

TO_CHAR converts a date to a string. 'FM' (Fill Mode) removes leading zeros or trailing blanks. 'Day' provides the full name of the day, 'DD' the day of the month, and 'Month' the full month name. Option 512993 matches the requested format.

In Oracle, DDL statements like ALTER TABLE and DCL statements like GRANT issue an implicit COMMIT, thereby completing the current transaction. ROLLBACK TO SAVEPOINT does not end a transaction; it only reverts to a specific point within it.

Views support DML object privileges: DELETE, INSERT, SELECT, and UPDATE. These allow users to manipulate data through the view (subject to view constraints). ALTER is a DDL privilege used for modifying object structure, not an object privilege granted on views.

TRUNCATE is a DDL command that quickly removes all rows from a table by deallocating data pages, without logging individual row deletions to the rollback segment. DROP removes the entire table structure. DELETE logs each row deletion to rollback.

Access by ROWID is fastest because ROWID is the physical address of the row in the data file. Oracle can go directly to that location without traversing index structures or scanning. Primary key and unique index access require index traversal first.

The SUBSTR function is used in Oracle and many other SQL dialects to extract a specific substring from a string. INSTR finds the position of a substring, while SUBSTRING is the standard name in SQL Server/MySQL but not Oracle.

In Oracle, if a user creates a segment (like a table) without specifying a tablespace, the database automatically assigns it to the default tablespace defined in that specific user's profile configuration.

In SQL*Plus, the hyphen (-) at the end of a line indicates that the statement continues on the next line. This is the continuation character when entering multi-line SQL statements.

In Oracle SQL, NEXT_DAY returns the date of the first weekday named that is later than the given date. If today is Friday, NEXT_DAY(sysdate, 'FRIDAY') will return the following Friday, which is September 4th, 2009.

To join N tables, you generally need a minimum of N-1 join conditions to avoid a Cartesian product. For four tables, 4 - 1 = 3 conditions are required to link them all together in a single chain or tree.

In SQL, identifiers starting with numbers or containing special characters like '#' are generally invalid unless quoted. However, 'Catch_#22' is often permitted in specific SQL dialects, while 'Column' is a reserved word and '#Invoices' is often used for temporary tables.