SQL Fundamentals: DML, Aggregates, and Group Operations

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.

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.

The MERGE statement (also known as UPSERT) combines INSERT and UPDATE operations into a single command, eliminating the need for separate statements. This improves performance, simplifies code, and is commonly used in data warehousing ETL processes.

Database transactions can end through explicit COMMIT or ROLLBACK commands, implicit commits triggered by DDL or DCL statements, or session termination (user exit or system crash). All three scenarios terminate the current transaction.

Oracle Database uses automatic, implicit locking at the row level by default. Unlike some databases requiring explicit lock management, Oracle handles locking transparently, making manual lock management unnecessary for most operations.

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.

AVG and SUM aggregate functions ignore NULL values and return NULL if all values in the group are NULL. They don't work on 'any' numeric data because they skip NULL values entirely in their calculations.

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.

Aggregate functions like SUM and AVG ignore NULL values by default. The NVL function converts NULL values to a specified non-null value (like 0), allowing those converted values to be included in aggregate calculations.

The HAVING clause filters grouped results after the GROUP BY operation, while WHERE filters individual rows before grouping. HAVING can use aggregate functions like COUNT, SUM, or AVG, which WHERE cannot.

Group functions like SUM, COUNT, AVG, MAX, MIN cannot be nested within each other in standard SQL. Nesting would require a subquery or CTE to compute the inner aggregate first.

The WHERE clause filters rows from a table before any grouping or aggregation occurs. It applies row-level filtering based on specified conditions.

In most SQL databases, character and string comparisons are case-sensitive by default. 'Scott' and 'scott' would be treated as different values unless specific collation settings are applied.

The double pipe (||) operator is used for string concatenation in SQL, particularly in Oracle and PostgreSQL databases. For example, 'First' || ' ' || 'Last' results in 'First Last'.