Database and SQL

The DELETE statement with a WHERE clause removes only those rows that satisfy the condition. In this case, only employees whose grade column contains 'Y' will be deleted. All other rows remain untouched. The DELETE without WHERE would remove all rows.

WITH CHECK OPTION is used with updatable views to prevent modifications that would make rows invisible to the view. It ensures that INSERT or UPDATE operations result in rows that still satisfy the view's defining query, maintaining data integrity and view consistency.

Explicit defaults can be specified in both INSERT and UPDATE statements. In INSERT, you can explicitly set a column to its default value. In UPDATE, you can reset a column back to its default value using the DEFAULT keyword. This provides flexibility in managing default values during data manipulation.

In join conditions, specifying table names is NOT mandatory when column names are unambiguous. If a column name exists in only one of the joined tables, you can reference it directly without the table prefix. Table prefixes (or aliases) are only required when column names are ambiguous (exist in multiple tables). The claimed answer 'False' is correct.

To join N tables together, you need at minimum N-1 join conditions. This is because each join reduces the number of unconnected tables by one. Starting with 10 tables, you need 9 joins to connect them all into a single result set. For example: (T1 JOIN T2) JOIN T3 ... JOIN T10 requires 9 joins.

A non-equi-join is defined as a join condition that uses comparison operators other than equality (=). These include operators like <, >, <=, >=, <>, BETWEEN, LIKE, etc. For example, finding employees whose salary is greater than the department average uses a non-equi-join. The statement is accurate, so 'True' is correct.

You CAN sort SQL results by column alias - this is a standard SQL feature. The ORDER BY clause can reference column aliases defined in the SELECT list, even though the alias doesn't exist in the original table. This is because ORDER BY is logically processed after SELECT, so the aliases are available. The statement claims sorting by alias is not possible, which is false.

SQL functions are categorized into two main types: single-row functions (which operate on each row independently) and multiple-row functions (also called aggregate or group functions) which operate on groups of rows. Options A and B invent non-existent categories. This classification is fundamental to understanding SQL function behavior.

Single-row functions return exactly ONE result per input row, not multiple results. This is why they're called 'single-row' functions - they maintain a one-to-one relationship between input rows and output rows. They may transform the data (change data type, format, etc.), but they never produce multiple output rows from a single input row. Multiple-row functions (aggregates) can reduce many rows to one result, but single-row functions do not.

While an inner join only returns rows with matching values in both tables, an outer join also retrieves rows from one or both tables that do not have a corresponding match in the other, filling missing values with NULL. This makes it ideal for finding unmatched records.

In Oracle SQL, the (+) operator is used for outer joins in the WHERE clause syntax. For example, WHERE table1.column = table2.column(+) indicates a right outer join (rows from table2 are preserved). The (-) and * and + alone are not outer join operators.

A self-join joins a table to itself, typically to compare rows within the same table. It requires table aliases to distinguish the two instances of the table. Outer join returns all rows from one table plus matching rows from another. Inner join returns only matching rows. Equi-join is a join using = operator.