Complete SQL Cheatsheet with All Commands and Examples

This SQL cheatsheet covers DDL, DML, DCL, TCL, queries, joins, subqueries, indexes, views, stored procedures, triggers, transactions, and advanced SQL concepts with practical examples for MySQL, PostgreSQL, SQL Server, and Oracle.

Database

1. SQL Basics and Introduction 2. DDL - Creating Databases and Tables 3. DDL - Altering and Managing Tables 4. DML - Inserting Data 5. DML - Selecting Data (Basic)6. DML - Advanced SELECT Queries 7. DML - Updating Data 8. DML - Deleting Data 9. Aggregate Functions 10. GROUP BY and HAVING 11. JOINS - Inner and Outer Joins 12. Subqueries and CTEs 13. Indexes and Performance 14. Transactions and ACID 15. Views, Functions, and Procedures

SQL Basics and Introduction

Understanding SQL fundamentals and database concepts

basics

SQL (Structured Query Language): A standard language for managing and manipulating relational databases. SQL allows you to create, read, update, and delete data (CRUD operations), as well as define database structures, control access, and manage transactions. SQL is used by all major relational database management systems (RDBMS) including MySQL, PostgreSQL, SQL Server, Oracle, and SQLite. Understanding SQL is essential for any developer working with data-driven applications.

sql

-- SQL Command Categories

-- 1. DDL (Data Definition Language)
-- CREATE, ALTER, DROP, TRUNCATE, RENAME
-- Used to define database structure

-- 2. DML (Data Manipulation Language)
-- SELECT, INSERT, UPDATE, DELETE
-- Used to manipulate data

-- 3. DCL (Data Control Language)
-- GRANT, REVOKE
-- Used to control access permissions

-- 4. TCL (Transaction Control Language)
-- COMMIT, ROLLBACK, SAVEPOINT
-- Used to manage transactions

-- 5. DQL (Data Query Language)
-- SELECT (sometimes classified separately)
-- Used to query and retrieve data

sql

-- Basic SQL Syntax Rules

-- 1. SQL is not case-sensitive (keywords)
SELECT * FROM users;  -- same as
select * from users;

-- 2. Semicolon ends a statement
SELECT * FROM users;

-- 3. Comments
-- Single line comment
/* Multi-line
   comment */

-- 4. String literals use single quotes
SELECT * FROM users WHERE name = 'John';

-- 5. Identifiers (table/column names) can use backticks
SELECT `user-name` FROM `user-table`;

DDL - Creating Databases and Tables

Data Definition Language commands for creating structures

ddl

CREATE Commands: DDL commands are used to define and modify database structures. The CREATE statement allows you to create databases, tables, indexes, views, and other database objects. When creating tables, you define columns with specific data types and constraints to enforce data integrity. Understanding proper table design with primary keys, foreign keys, and constraints is fundamental to building robust database applications.

sql

-- Create Database
CREATE DATABASE company_db;
CREATE DATABASE IF NOT EXISTS company_db;

-- Use Database
USE company_db;

-- Drop Database
DROP DATABASE company_db;
DROP DATABASE IF EXISTS company_db;

-- Show Databases
SHOW DATABASES;

-- Show Current Database
SELECT DATABASE();

sql

-- Create Table with Basic Data Types
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    hire_date DATE NOT NULL,
    salary DECIMAL(10, 2),
    department_id INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create Table with Multiple Constraints
CREATE TABLE departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL UNIQUE,
    manager_id INT,
    location VARCHAR(100),
    budget DECIMAL(15, 2) CHECK (budget > 0),
    CONSTRAINT fk_manager FOREIGN KEY (manager_id) 
        REFERENCES employees(id)
);

sql

-- Common Data Types

-- Numeric Types
INT, SMALLINT, BIGINT          -- Integers
DECIMAL(10,2), NUMERIC(10,2)   -- Fixed-point
FLOAT, DOUBLE, REAL            -- Floating-point

-- String Types
CHAR(10)                       -- Fixed length
VARCHAR(255)                   -- Variable length
TEXT, MEDIUMTEXT, LONGTEXT     -- Long text

-- Date and Time Types
DATE                           -- YYYY-MM-DD
TIME                           -- HH:MM:SS
DATETIME                       -- YYYY-MM-DD HH:MM:SS
TIMESTAMP                      -- With timezone
YEAR                           -- YYYY

-- Binary Types
BLOB, MEDIUMBLOB, LONGBLOB     -- Binary data
BINARY, VARBINARY              -- Binary strings

-- Other Types
BOOLEAN, BOOL                  -- True/False (TINYINT(1))
ENUM('S','M','L')             -- Enumeration
JSON                           -- JSON data (MySQL 5.7+)

DDL - Altering and Managing Tables

Modifying existing table structures

ddl

ALTER Commands: The ALTER TABLE statement allows you to modify existing table structures without losing data. You can add new columns, modify existing columns, drop columns, add or remove constraints, and rename tables. These operations are crucial for database evolution as application requirements change over time. Always be cautious when altering production tables, especially when dropping columns or changing data types.

sql

-- Add Column
ALTER TABLE employees
ADD COLUMN middle_name VARCHAR(50) AFTER first_name;

ALTER TABLE employees
ADD COLUMN age INT,
ADD COLUMN gender CHAR(1);

-- Modify Column
ALTER TABLE employees
MODIFY COLUMN salary DECIMAL(12, 2);

ALTER TABLE employees
CHANGE COLUMN phone phone_number VARCHAR(15);

-- Drop Column
ALTER TABLE employees
DROP COLUMN middle_name;

-- Rename Table
ALTER TABLE employees
RENAME TO staff;

RENAME TABLE staff TO employees;

sql

-- Add Constraints
ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

ALTER TABLE employees
ADD CONSTRAINT fk_dept FOREIGN KEY (department_id)
    REFERENCES departments(dept_id)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

ALTER TABLE employees
ADD CONSTRAINT chk_salary CHECK (salary > 0);

-- Drop Constraints
ALTER TABLE employees
DROP CONSTRAINT unique_email;

ALTER TABLE employees
DROP FOREIGN KEY fk_dept;

ALTER TABLE employees
DROP INDEX unique_email;

sql

-- Other Table Operations

-- Truncate Table (delete all rows, keep structure)
TRUNCATE TABLE employees;

-- Drop Table
DROP TABLE employees;
DROP TABLE IF EXISTS employees;

-- Show Table Structure
DESCRIBE employees;
DESC employees;
SHOW COLUMNS FROM employees;

-- Show Create Statement
SHOW CREATE TABLE employees;

-- Show All Tables
SHOW TABLES;

-- Copy Table Structure
CREATE TABLE employees_backup LIKE employees;

-- Copy Table with Data
CREATE TABLE employees_backup AS
SELECT * FROM employees;

DML - Inserting Data

Adding data to tables with INSERT statements

dml

INSERT Statement: The INSERT command adds new rows to a table. You can insert single or multiple rows in one statement, specify values for all or selected columns, and use default values or expressions. Understanding different INSERT variations helps you write efficient data insertion code. For bulk inserts, using multi-row INSERT statements is much faster than individual inserts. Always ensure data integrity by respecting constraints and data types.

sql

-- Insert Single Row (all columns)
INSERT INTO employees 
VALUES (1, 'John', 'Doe', 'john@email.com', '1234567890', 
        '2024-01-15', 75000.00, 1, TRUE, NOW());

-- Insert Single Row (specific columns)
INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES ('Jane', 'Smith', 'jane@email.com', '2024-02-01', 80000.00);

-- Insert Multiple Rows
INSERT INTO employees (first_name, last_name, email, hire_date, salary)
VALUES 
    ('Alice', 'Johnson', 'alice@email.com', '2024-03-01', 70000.00),
    ('Bob', 'Williams', 'bob@email.com', '2024-03-15', 72000.00),
    ('Carol', 'Brown', 'carol@email.com', '2024-04-01', 75000.00);

sql

-- Insert with SELECT (copy data from another table)
INSERT INTO employees_backup (first_name, last_name, email, salary)
SELECT first_name, last_name, email, salary
FROM employees
WHERE department_id = 1;

-- Insert with Default Values
INSERT INTO employees (first_name, last_name, email)
VALUES ('David', 'Miller', 'david@email.com');
-- Other columns use DEFAULT values

-- Insert Ignore (skip if duplicate key)
INSERT IGNORE INTO employees (id, first_name, last_name, email)
VALUES (1, 'John', 'Doe', 'john@email.com');

-- Insert On Duplicate Key Update (MySQL)
INSERT INTO employees (id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 80000.00)
ON DUPLICATE KEY UPDATE 
    salary = VALUES(salary),
    last_name = VALUES(last_name);

sql

-- Insert with RETURNING (PostgreSQL)
INSERT INTO employees (first_name, last_name, email)
VALUES ('Emma', 'Davis', 'emma@email.com')
RETURNING id, first_name, created_at;

-- Insert with OUTPUT (SQL Server)
INSERT INTO employees (first_name, last_name, email)
OUTPUT INSERTED.id, INSERTED.first_name
VALUES ('Frank', 'Wilson', 'frank@email.com');

-- Upsert (PostgreSQL)
INSERT INTO employees (id, first_name, last_name, salary)
VALUES (1, 'John', 'Doe', 85000.00)
ON CONFLICT (id) 
DO UPDATE SET 
    salary = EXCLUDED.salary,
    last_name = EXCLUDED.last_name;

DML - Selecting Data (Basic)

Retrieving data with SELECT queries

dml

SELECT Statement: The SELECT command is the most frequently used SQL statement, allowing you to retrieve data from one or more tables. You can select all columns or specific columns, filter results with WHERE clauses, sort data with ORDER BY, limit results, and perform calculations. Mastering SELECT queries is essential for data analysis and reporting. Understanding how to write efficient queries with proper indexing will significantly improve application performance.

sql

-- Select All Columns
SELECT * FROM employees;

-- Select Specific Columns
SELECT first_name, last_name, salary FROM employees;

-- Select with Column Aliases
SELECT 
    first_name AS 'First Name',
    last_name AS 'Last Name',
    salary AS 'Annual Salary'
FROM employees;

-- Select with Expressions
SELECT 
    first_name,
    last_name,
    salary,
    salary * 12 AS annual_salary,
    salary * 0.1 AS tax
FROM employees;

-- Select Distinct Values
SELECT DISTINCT department_id FROM employees;

SELECT DISTINCT first_name, last_name FROM employees;

sql

-- WHERE Clause (Filtering)
SELECT * FROM employees WHERE salary > 70000;

SELECT * FROM employees WHERE department_id = 1;

SELECT * FROM employees 
WHERE first_name = 'John' AND last_name = 'Doe';

SELECT * FROM employees 
WHERE salary >= 60000 AND salary <= 80000;

SELECT * FROM employees 
WHERE department_id IN (1, 2, 3);

SELECT * FROM employees 
WHERE first_name LIKE 'J%';  -- Starts with J

SELECT * FROM employees 
WHERE email LIKE '%@gmail.com';  -- Ends with @gmail.com

SELECT * FROM employees 
WHERE first_name LIKE '%oh%';  -- Contains 'oh'

SELECT * FROM employees 
WHERE phone IS NULL;

SELECT * FROM employees 
WHERE phone IS NOT NULL;

sql

-- ORDER BY (Sorting)
SELECT * FROM employees 
ORDER BY salary DESC;

SELECT * FROM employees 
ORDER BY last_name ASC, first_name ASC;

SELECT * FROM employees 
ORDER BY hire_date DESC;

-- LIMIT (Restricting Results)
SELECT * FROM employees 
LIMIT 10;

SELECT * FROM employees 
ORDER BY salary DESC 
LIMIT 5;  -- Top 5 highest salaries

-- OFFSET (Pagination)
SELECT * FROM employees 
LIMIT 10 OFFSET 20;  -- Skip first 20, get next 10

-- MySQL alternative
SELECT * FROM employees 
LIMIT 20, 10;  -- Skip 20, get 10

DML - Advanced SELECT Queries

Complex filtering and pattern matching

dml

Advanced Filtering: SQL provides powerful operators for complex filtering scenarios. The BETWEEN operator simplifies range queries, IN allows checking against multiple values, and LIKE enables pattern matching with wildcards. Understanding logical operators (AND, OR, NOT) and operator precedence is crucial for writing correct query conditions. CASE expressions add conditional logic to SELECT statements, enabling dynamic column values based on conditions.

sql

-- BETWEEN Operator
SELECT * FROM employees 
WHERE salary BETWEEN 60000 AND 80000;

SELECT * FROM employees 
WHERE hire_date BETWEEN '2024-01-01' AND '2024-12-31';

-- IN Operator
SELECT * FROM employees 
WHERE department_id IN (1, 2, 3);

SELECT * FROM employees 
WHERE first_name IN ('John', 'Jane', 'Alice');

-- NOT IN
SELECT * FROM employees 
WHERE department_id NOT IN (1, 2);

-- EXISTS
SELECT * FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.dept_id = e.department_id
);

sql

-- LIKE Patterns
SELECT * FROM employees 
WHERE first_name LIKE 'J%';     -- Starts with J

SELECT * FROM employees 
WHERE first_name LIKE '%n';     -- Ends with n

SELECT * FROM employees 
WHERE first_name LIKE '%oh%';   -- Contains oh

SELECT * FROM employees 
WHERE first_name LIKE 'J___';   -- J followed by 3 chars

SELECT * FROM employees 
WHERE first_name LIKE '[JM]%';  -- Starts with J or M (SQL Server)

SELECT * FROM employees 
WHERE first_name LIKE '[A-M]%'; -- Starts with A to M

SELECT * FROM employees 
WHERE email NOT LIKE '%@gmail.com';

-- REGEXP (MySQL, PostgreSQL)
SELECT * FROM employees 
WHERE first_name REGEXP '^[A-M]';  -- Starts with A-M

SELECT * FROM employees 
WHERE email REGEXP '[0-9]';  -- Contains digit

sql

-- CASE Expression
SELECT 
    first_name,
    last_name,
    salary,
    CASE 
        WHEN salary < 50000 THEN 'Low'
        WHEN salary BETWEEN 50000 AND 80000 THEN 'Medium'
        WHEN salary > 80000 THEN 'High'
        ELSE 'Unknown'
    END AS salary_category
FROM employees;

-- Simple CASE
SELECT 
    first_name,
    department_id,
    CASE department_id
        WHEN 1 THEN 'IT'
        WHEN 2 THEN 'HR'
        WHEN 3 THEN 'Sales'
        ELSE 'Other'
    END AS department_name
FROM employees;

-- CASE in ORDER BY
SELECT * FROM employees
ORDER BY 
    CASE 
        WHEN department_id = 1 THEN 1
        WHEN department_id = 2 THEN 2
        ELSE 3
    END;

DML - Updating Data

Modifying existing records with UPDATE

dml

UPDATE Statement: The UPDATE command modifies existing rows in a table. You can update one or more columns, use expressions for calculated values, and apply conditions with WHERE clauses to target specific rows. Always use WHERE clauses carefully to avoid accidentally updating all rows. For safety, many database configurations require explicit WHERE clauses in UPDATE statements. You can also update based on data from other tables using subqueries or joins.

sql

-- Update Single Column
UPDATE employees
SET salary = 80000
WHERE id = 1;

-- Update Multiple Columns
UPDATE employees
SET 
    salary = 85000,
    department_id = 2,
    is_active = TRUE
WHERE id = 1;

-- Update with Expression
UPDATE employees
SET salary = salary * 1.1  -- 10% raise
WHERE department_id = 1;

-- Update All Rows (DANGEROUS!)
UPDATE employees
SET is_active = TRUE;

-- Update with CASE
UPDATE employees
SET salary = CASE
    WHEN department_id = 1 THEN salary * 1.15
    WHEN department_id = 2 THEN salary * 1.10
    WHEN department_id = 3 THEN salary * 1.08
    ELSE salary
END;

sql

-- Update with Subquery
UPDATE employees
SET department_id = (
    SELECT dept_id FROM departments 
    WHERE dept_name = 'IT'
)
WHERE first_name = 'John';

-- Update with JOIN (MySQL)
UPDATE employees e
INNER JOIN departments d ON e.department_id = d.dept_id
SET e.salary = e.salary * 1.1
WHERE d.dept_name = 'Sales';

-- Update Multiple Tables (MySQL)
UPDATE employees e, departments d
SET 
    e.salary = e.salary * 1.1,
    d.budget = d.budget - 10000
WHERE e.department_id = d.dept_id 
    AND d.dept_name = 'IT';

-- Update with RETURNING (PostgreSQL)
UPDATE employees
SET salary = 90000
WHERE id = 1
RETURNING id, first_name, salary;

sql

-- Safe Update Practices

-- 1. Always use WHERE clause
UPDATE employees
SET salary = 80000
WHERE id = 1;  -- NOT: without WHERE

-- 2. Test with SELECT first
SELECT * FROM employees WHERE id = 1;
-- Then UPDATE

-- 3. Use transactions for safety
START TRANSACTION;
UPDATE employees SET salary = 90000 WHERE id = 1;
-- Check results
SELECT * FROM employees WHERE id = 1;
-- If correct: COMMIT, else: ROLLBACK

-- 4. Backup before mass updates
CREATE TABLE employees_backup AS SELECT * FROM employees;

-- 5. Use LIMIT for testing (MySQL)
UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 1
LIMIT 5;

DML - Deleting Data

Removing records from tables

dml

DELETE Statement: The DELETE command removes rows from a table based on specified conditions. Like UPDATE, you should always use WHERE clauses to target specific rows unless you intentionally want to delete all data. DELETE removes rows but keeps the table structure intact, unlike DROP TABLE which removes the entire table. TRUNCATE is faster than DELETE for removing all rows but cannot be rolled back in most databases and does not fire triggers.

sql

-- Delete Specific Rows
DELETE FROM employees 
WHERE id = 1;

DELETE FROM employees 
WHERE salary < 40000;

DELETE FROM employees 
WHERE hire_date < '2020-01-01';

-- Delete with Multiple Conditions
DELETE FROM employees 
WHERE department_id = 1 AND is_active = FALSE;

DELETE FROM employees 
WHERE last_name IN ('Doe', 'Smith');

-- Delete All Rows (DANGEROUS!)
DELETE FROM employees;

-- Delete with LIMIT (MySQL)
DELETE FROM employees 
WHERE salary < 50000 
LIMIT 10;

sql

-- Delete with Subquery
DELETE FROM employees
WHERE department_id IN (
    SELECT dept_id FROM departments 
    WHERE budget < 50000
);

DELETE FROM employees
WHERE id NOT IN (
    SELECT DISTINCT employee_id FROM projects
);

-- Delete with JOIN (MySQL)
DELETE e FROM employees e
INNER JOIN departments d ON e.department_id = d.dept_id
WHERE d.dept_name = 'Closed';

-- Delete with RETURNING (PostgreSQL)
DELETE FROM employees
WHERE id = 1
RETURNING id, first_name, last_name;

sql

-- TRUNCATE vs DELETE

-- DELETE: Removes rows one by one
DELETE FROM employees;
-- - Slower
-- - Can be rolled back
-- - Fires triggers
-- - Can use WHERE clause
-- - Logs each row deletion

-- TRUNCATE: Removes all rows at once
TRUNCATE TABLE employees;
-- - Faster
-- - Cannot be rolled back (in most DBs)
-- - Does not fire triggers
-- - No WHERE clause
-- - Minimal logging
-- - Resets auto-increment counter

-- DROP: Removes entire table
DROP TABLE employees;
-- - Removes structure and data
-- - Cannot recover without backup

-- Safe Delete Practices
-- 1. Use SELECT first
SELECT * FROM employees WHERE id = 1;
-- 2. Then DELETE
DELETE FROM employees WHERE id = 1;

Aggregate Functions

Performing calculations on data sets

functions

Aggregate Functions: These functions perform calculations on multiple rows and return a single result. Common aggregate functions include COUNT, SUM, AVG, MAX, and MIN. They are essential for statistical analysis and reporting. Aggregate functions ignore NULL values (except COUNT(*)), and you can combine them with GROUP BY to calculate statistics for different groups of data. Understanding how to use these functions effectively is crucial for data analysis tasks.

sql

-- COUNT: Count rows
SELECT COUNT(*) FROM employees;  -- All rows
SELECT COUNT(phone) FROM employees;  -- Non-NULL values
SELECT COUNT(DISTINCT department_id) FROM employees;

-- SUM: Total of numeric values
SELECT SUM(salary) FROM employees;
SELECT SUM(salary) AS total_payroll FROM employees;

-- AVG: Average value
SELECT AVG(salary) FROM employees;
SELECT AVG(salary) AS average_salary 
FROM employees 
WHERE department_id = 1;

-- MAX: Maximum value
SELECT MAX(salary) FROM employees;
SELECT MAX(hire_date) AS latest_hire FROM employees;

-- MIN: Minimum value
SELECT MIN(salary) FROM employees;
SELECT MIN(hire_date) AS earliest_hire FROM employees;

sql

-- Multiple Aggregates
SELECT 
    COUNT(*) AS total_employees,
    SUM(salary) AS total_payroll,
    AVG(salary) AS avg_salary,
    MAX(salary) AS highest_salary,
    MIN(salary) AS lowest_salary
FROM employees;

-- Aggregates with WHERE
SELECT 
    COUNT(*) AS it_employees,
    AVG(salary) AS avg_it_salary
FROM employees
WHERE department_id = 1;

-- ROUND for precision
SELECT 
    ROUND(AVG(salary), 2) AS avg_salary,
    ROUND(SUM(salary), 2) AS total_payroll
FROM employees;

-- Aggregates with DISTINCT
SELECT 
    COUNT(DISTINCT department_id) AS unique_departments,
    COUNT(DISTINCT last_name) AS unique_surnames
FROM employees;

sql

-- Statistical Functions

-- STDDEV: Standard deviation
SELECT STDDEV(salary) AS salary_std_dev FROM employees;

-- VARIANCE: Variance
SELECT VARIANCE(salary) AS salary_variance FROM employees;

-- String Aggregation (MySQL)
SELECT GROUP_CONCAT(first_name) AS all_names 
FROM employees;

SELECT GROUP_CONCAT(first_name SEPARATOR ', ') AS names
FROM employees;

-- String Aggregation (PostgreSQL)
SELECT STRING_AGG(first_name, ', ') AS names 
FROM employees;

-- Conditional Aggregation
SELECT 
    COUNT(*) AS total,
    COUNT(CASE WHEN salary > 70000 THEN 1 END) AS high_earners,
    COUNT(CASE WHEN salary <= 70000 THEN 1 END) AS others
FROM employees;

GROUP BY and HAVING

Grouping data and filtering groups

grouping

GROUP BY Clause: Groups rows that have the same values in specified columns into summary rows. It is typically used with aggregate functions to perform calculations on each group separately. The HAVING clause filters groups based on aggregate conditions, unlike WHERE which filters individual rows before grouping. Understanding the order of execution (WHERE → GROUP BY → HAVING → SELECT → ORDER BY) is crucial for writing correct queries with grouping.

sql

-- Basic GROUP BY
SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- Multiple Columns in GROUP BY
SELECT 
    department_id, 
    is_active,
    COUNT(*) AS count
FROM employees
GROUP BY department_id, is_active;

-- GROUP BY with Multiple Aggregates
SELECT 
    department_id,
    COUNT(*) AS emp_count,
    SUM(salary) AS total_salary,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees
GROUP BY department_id;

sql

-- HAVING Clause (Filter Groups)
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 70000;

SELECT department_id, COUNT(*) AS emp_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 5;

-- WHERE vs HAVING
-- WHERE: Filters rows before grouping
-- HAVING: Filters groups after aggregation

SELECT department_id, COUNT(*) AS emp_count
FROM employees
WHERE salary > 50000  -- Filter rows first
GROUP BY department_id
HAVING COUNT(*) > 3;  -- Then filter groups

-- Multiple HAVING Conditions
SELECT 
    department_id,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5 AND AVG(salary) > 60000;

sql

-- GROUP BY with ORDER BY
SELECT 
    department_id,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY emp_count DESC;

-- GROUP BY with Expressions
SELECT 
    YEAR(hire_date) AS hire_year,
    COUNT(*) AS hires
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY hire_year;

-- ROLLUP (Subtotals)
SELECT 
    department_id,
    COUNT(*) AS emp_count,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department_id WITH ROLLUP;

-- GROUPING SETS (Advanced)
SELECT 
    department_id,
    is_active,
    COUNT(*) AS count
FROM employees
GROUP BY GROUPING SETS (
    (department_id),
    (is_active),
    (department_id, is_active)
);

JOINS - Inner and Outer Joins

Combining rows from multiple tables

joins

Joins: Joins let you combine rows from two or more tables based on related columns. INNER JOIN returns matching rows, LEFT/RIGHT OUTER JOIN keep unmatched rows from one side, and FULL OUTER JOIN keeps all rows from both sides. CROSS JOIN creates a cartesian product. SELF JOIN lets a table join to itself for hierarchical data.

sql

-- Inner Join (only matches)
SELECT e.first_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.dept_id;

-- Left Join (all left, matching right)
SELECT e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id;

-- Right Join (all right, matching left)
SELECT e.first_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.dept_id;

sql

-- Full Outer Join (all rows both sides)
-- PostgreSQL / SQL Server
SELECT e.first_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.dept_id;

-- MySQL alternative using UNION
SELECT e.first_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.dept_id
UNION
SELECT e.first_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.dept_id;

sql

-- Cross Join (cartesian product)
SELECT e.first_name, p.project_name
FROM employees e
CROSS JOIN projects p;

-- Self Join (table joins itself)
SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Subqueries and CTEs

Using subqueries and common table expressions

subqueries

Subqueries & CTEs: Subqueries run inside another query for filtering or derived tables. Common Table Expressions (CTEs) improve readability and can be recursive for hierarchical data. Use CTEs to break complex logic into steps.

sql

-- Subquery in WHERE
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery as derived table
SELECT dept_name, avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) t
JOIN departments d ON t.department_id = d.dept_id;

sql

-- CTE (WITH)
WITH dept_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.dept_name, ds.avg_salary
FROM dept_salary ds
JOIN departments d ON ds.department_id = d.dept_id;

-- Recursive CTE (hierarchy)
WITH RECURSIVE employee_path AS (
    SELECT id, first_name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.first_name, e.manager_id, ep.level + 1
    FROM employees e
    JOIN employee_path ep ON e.manager_id = ep.id
)
SELECT * FROM employee_path
ORDER BY level, first_name;

Indexes and Performance

Improving query speed with indexes and EXPLAIN

performance

Indexes: Indexes speed up reads by keeping sorted references to data. Create indexes on frequently filtered or joined columns. Avoid over-indexing because each index slows inserts/updates. Use EXPLAIN/EXPLAIN ANALYZE to inspect query plans.

sql

-- Create / drop indexes
CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE UNIQUE INDEX idx_emp_email ON employees(email);
DROP INDEX idx_emp_dept ON employees;   -- MySQL
DROP INDEX idx_emp_email;               -- PostgreSQL

-- Composite index (order matters)
CREATE INDEX idx_emp_dept_salary ON employees(department_id, salary);

-- Covering index example (MySQL/InnoDB)
CREATE INDEX idx_emp_email_phone ON employees(email, phone);

sql

-- Analyze query plan
EXPLAIN SELECT * FROM employees WHERE email = 'john@email.com';

-- PostgreSQL with runtime stats
EXPLAIN ANALYZE
SELECT * FROM employees WHERE department_id = 1;

-- Update statistics (PostgreSQL)
VACUUM ANALYZE employees;

-- MySQL optimizer hints
SELECT /*+ INDEX(employees idx_emp_dept) */ *
FROM employees
WHERE department_id = 1;

Transactions and ACID

Ensuring consistency with transactions and isolation levels

transactions

Transactions: Group statements so they succeed or fail together (atomicity). Use proper isolation levels to balance consistency and concurrency. Always COMMIT on success and ROLLBACK on error.

sql

-- Basic transaction
START TRANSACTION;  -- or BEGIN
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- or ROLLBACK on failure

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Savepoints
START TRANSACTION;
SAVEPOINT before_bonus;
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 1;
ROLLBACK TO SAVEPOINT before_bonus;
COMMIT;

Views, Functions, and Procedures

Encapsulating logic on the database side

admin

Views & Routines: Views expose saved queries, while stored procedures and functions package reusable logic. Use them to enforce consistency and reduce duplication. Grant minimal privileges to execute these objects.

sql

-- Create / drop view
CREATE VIEW active_employees AS
SELECT id, first_name, last_name, department_id
FROM employees
WHERE is_active = TRUE;

SELECT * FROM active_employees;
DROP VIEW active_employees;

sql

-- Stored procedure (MySQL)
DELIMITER //
CREATE PROCEDURE give_raise(IN dept INT, IN pct DECIMAL(5,2))
BEGIN
  UPDATE employees
  SET salary = salary * (1 + pct / 100)
  WHERE department_id = dept;
END //
DELIMITER ;

CALL give_raise(1, 5.0);

-- Scalar function (PostgreSQL)
CREATE OR REPLACE FUNCTION full_name(first TEXT, last TEXT)
RETURNS TEXT AS $$
BEGIN
  RETURN first || ' ' || last;
END;
$$ LANGUAGE plpgsql;