Database and SQL

Indexes are database structures created specifically to accelerate data retrieval operations. They exist solely to improve query performance by providing fast access paths to data, at the cost of additional storage and write overhead. Indexes are widely used and frequently significantly impact performance.

A synonym provides an alternative name for a database object like a table, allowing you to reference it without changing the actual table name. Views are virtual tables derived from queries, not simple aliases. Indexes improve query performance and sequences generate numeric values.

SQL allows defining constraints at both column level (like NOT NULL or CHECK on a single column) and table level (like PRIMARY KEY, FOREIGN KEY, or UNIQUE that may involve multiple columns). Column constraints apply to individual columns, while table constraints can span multiple columns and enforce relationships.

TRUNCATE is faster than DELETE because it simply deallocates data pages without logging individual row deletions. DELETE logs each row deletion for rollback capability, making it slower. TRUNCATE is a DDL operation that resets the table, while DELETE is a DML operation that removes rows one by one.

In Oracle SQL, a dropped table can be restored using the FLASHBACK TABLE statement, which retrieves it from the Recycle Bin. ROLLBACK is for uncommitted DML transactions and cannot undo DDL operations like DROP.

NVL2(expr1, expr2, expr3) returns expr2 if expr1 is NOT NULL, otherwise returns expr3. Since the first argument is NULL, NVL2 returns the third argument 'V2'. This differs from NVL which only handles NULL substitution.

COUNT ignores NULL values when counting a specific column. COUNT(*) returns all rows including NULLs, but COUNT(column_name) excludes rows where that column is NULL. This is important distinction when you need accurate counts of non-null values.

GRANT and REVOKE are Data Control Language (DCL) commands used to manage database access permissions. DDL (Data Definition Language) defines structures, DML (Data Manipulation Language) manipulates data, and DCL controls access and permissions.

add_months('15-Jan-2011', -2) subtracts 2 months from the given date. January minus 2 months is November of the previous year, so the result is 15-Nov-2010. The function handles year boundaries correctly when adding or subtracting months.

FLOOR(-4.6) returns -5 (rounds down to the next lower integer), CEIL(3.9) returns 4 (rounds up to the next higher integer). The sum is -5 + 4 = -1. Remember that FLOOR on negative numbers goes to a more negative value.

In Oracle, tables can have a large but finite number of indexes - up to the limit on the number of segments per table, which is determined by the database configuration. While the limit is high (unlimited in practical scenarios), technically it's not unlimited. The question uses 'unlimited' in a practical sense.

Oracle tables have a maximum limit of 1000 columns per table (or 1000 columns minus 1 for LONG columns). This is a hard constraint in the database schema. Therefore, tables cannot have unlimited columns.

Catch_#22 is valid because SQL identifiers can contain letters, numbers, underscores, and certain special characters like hash when properly quoted. Column is a reserved word in some databases, 1966_Invoices starts with a number (problematic in some SQL dialects), and #Invoices starts with a special character.

TRUNCATE is a DDL command that quickly removes all rows by resetting the table without logging individual row deletions to the rollback segment. DELETE is DML that logs each deletion, DROP removes the entire table structure, and CASCADE is a constraint option for related deletions.

ROWID is the physical disk address of a row, so accessing by ROWID is the fastest method - Oracle goes directly to the exact location. Primary key and unique index require traversing an index structure first, and full table scan reads every row in the table.