Run a mock round

Aggregate functions perform a computation on a set of values across multiple rows and return a single summarizing value:

### Core Standard Aggregate Functions:

1. COUNT(): Returns the number of rows or non-null values.
2. SUM(): Computes the mathematical sum of numeric values.
3. AVG(): Computes the arithmetic mean of numeric values (automatically ignores NULLs).
4. MIN(): Returns the minimum value (numeric, string, or date).
5. MAX(): Returns the maximum value.

### Statistical & String Aggregates:

• GROUP_CONCAT() (MySQL) / STRING_AGG() (PostgreSQL/SQL Server): Concatenates strings from a group.
• STDDEV(), VARIANCE(): Statistical dispersion calculations.

The keyword used in an UPDATE statement to specify new values for columns is SET:

UPDATE employees 
SET salary = salary * 1.10, 
    title = 'Senior Software Engineer' 
WHERE employee_id = 101;

The SET keyword precedes a comma-separated list of column_name = new_value assignments.

1. Simplify complex queries — hide JOINs and aggregations
2. Security — expose only specific columns/rows
3. Logical independence — shield apps from schema changes
4. Consistency — same calculation logic everywhere

Statements such as COMMIT, ROLLBACK, CREATE, ALTER, and DROP are NOT DML statements:

• COMMIT and ROLLBACK are TCL (Transaction Control Language).
• CREATE, ALTER, DROP, and TRUNCATE are DDL (Data Definition Language).
• GRANT and REVOKE are DCL (Data Control Language).

*DML statements are strictly:* SELECT, INSERT, UPDATE, and DELETE.

SQL's version of IF-THEN-ELSE. Two forms: simple and searched.

-- Searched CASE (more flexible)
SELECT name,
    CASE 
        WHEN salary >= 100000 THEN 'Executive'
        WHEN salary >= 60000  THEN 'Senior'
        WHEN salary >= 40000  THEN 'Mid'
        ELSE 'Junior'
    END AS level
FROM employees;

-- Simple CASE
SELECT name,
    CASE dept
        WHEN 'Engineering' THEN 'Tech'
        WHEN 'Sales' THEN 'Revenue'
        ELSE 'Other'
    END AS dept_category
FROM employees;

---

CREATE TEMPORARY TABLE temp_top_customers
SELECT * FROM customers WHERE total_spent > 10000;

It exists only for the current session/connection and is automatically dropped when the session ends.

| PRIMARY KEY | UNIQUE |
|-------------|--------|
| One per table | Multiple per table |
| Cannot be NULL | Usually allows one NULL |
| Implicitly indexed | Explicitly indexed |
| Identifies the row | Alternate identifier |

The keyword and aggregate function used to retrieve the maximum value of a column is MAX():

SELECT MAX(salary) AS highest_salary 
FROM employees;

### With Grouping:

SELECT department_id, MAX(salary) AS top_dept_salary 
FROM employees 
GROUP BY department_id;

If an index exists on the target column, the database engine can find MAX() in $O(1)$ time by reading the last entry of the B-tree index.

The NOT NULL constraint enforces that a column must always contain a valid data value and cannot accept NULL:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    hired_date DATE NOT NULL
);

Attempting to insert an empty NULL into a NOT NULL column causes the database to reject the transaction with an integrity violation error.