Database and SQL

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.

Group functions (aggregate functions like COUNT, SUM, AVG, MAX, MIN) cannot be used in WHERE clause. WHERE filters individual rows before grouping. Group functions must be used in HAVING clause or SELECT list, after grouping has occurred. This is a fundamental SQL rule.

An UPDATE statement in SQL can modify multiple rows simultaneously based on the condition specified in the WHERE clause. If no WHERE clause is provided, it updates every row in the table.

The DELETE statement with a WHERE clause specifically removes only those rows that satisfy the condition. This statement will delete all employee records where the grade column equals 'Y', leaving other rows untouched.

WITH CHECK OPTION is a clause used with updatable views that prevents INSERT or UPDATE operations on rows that would not be visible through the view. It ensures that DML operations only affect rows that satisfy the subquery defining the view.

The DEFAULT keyword can be explicitly used in both INSERT and UPDATE statements to assign a column's default value. In INSERT, you can specify VALUES (DEFAULT), and in UPDATE, you can use SET column = DEFAULT.

Standard SQL aggregate functions include COUNT, AVG, MAX, MIN, and SUM. TOTAL is not a valid SQL aggregate function. To sum values, use the SUM function instead.

COUNT(DISTINCT expr) returns the number of unique, non-null values for the specified expression. It eliminates both NULL values and duplicate values from the count, giving the count of distinct values present.