SQL Execution Order

SQL has a logical execution order different from written order, which explains why aliases defined in SELECT cannot be used in WHERE.

Why WHERE annual_salary > 1000000 fails if alias defined in SELECT — WHERE runs before SELECT.

SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 1000000; -- fails
-- Use subquery/CTE instead

CASE Statement

CASE provides conditional logic inside a query, similar to if-else, to categorize or transform values on the fly.

SELECT first_name, salary,
       CASE WHEN salary >= 90000 THEN 'High'
            WHEN salary >= 70000 THEN 'Medium'
            ELSE 'Low' END AS salary_category
FROM employees;

Views

A View is a stored query definition that acts like a virtual table and always reflects current underlying data.

CREATE VIEW high_salary_employees AS
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > 80000;

SELECT * FROM high_salary_employees;

Materialized Views

A Materialized View stores the query result physically for fast reads, but it can become stale and needs refreshing.

Stored Procedures

Stored Procedures are reusable SQL logic stored in the database that can accept parameters and encapsulate business operations.

CREATE PROCEDURE UpdateEmployeeSalary
    @employee_id INT, @new_salary DECIMAL(10,2)
AS
BEGIN
    UPDATE employees SET salary = @new_salary WHERE employee_id = @employee_id;
END;

DELETE vs TRUNCATE vs DROP

DELETE removes specific rows, TRUNCATE removes all rows quickly without logging each row, and DROP removes the entire table structure.

DELETE FROM employees WHERE employee_id = 101;
TRUNCATE TABLE employees;
DROP TABLE employees;