SQL From Scratch to Advanced: A Complete Guide

SQL (Structured Query Language) is the standard language for talking to relational databases like MySQL, PostgreSQL, SQL Server, and SQLite. Whether you’re a total beginner or brushing up before an interview, this guide walks through everything from your first SELECT to advanced window functions.

We’ll use a simple example database throughout:

-- employees table
-- id | name    | department | salary | manager_id | hire_date
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    manager_id INT,
    hire_date DATE
);

-- departments table
-- id | name       | location
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    location VARCHAR(50)
);

1. The Basics

1.1 SELECT — Retrieving Data

SELECT * FROM employees;

SELECT name, salary FROM employees;

1.2 WHERE — Filtering Rows

SELECT name, salary
FROM employees
WHERE department = 'Engineering';

SELECT name
FROM employees
WHERE salary > 60000 AND hire_date > '2022-01-01';

Common operators: =, != / <>, >, <, >=, <=, AND, OR, NOT, BETWEEN, IN, LIKE, IS NULL.

SELECT name FROM employees
WHERE department IN ('Engineering', 'Sales');

SELECT name FROM employees
WHERE name LIKE 'A%';   -- starts with A

SELECT name FROM employees
WHERE salary BETWEEN 50000 AND 90000;

SELECT name FROM employees
WHERE manager_id IS NULL;   -- top-level employees

1.3 ORDER BY — Sorting

SELECT name, salary
FROM employees
ORDER BY salary DESC;

SELECT name, department
FROM employees
ORDER BY department ASC, salary DESC;

1.4 LIMIT — Restricting Row Count

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

(In SQL Server, use SELECT TOP 5 ... instead.)

2. Working with Multiple Columns and Aliases

SELECT
    name AS employee_name,
    salary * 12 AS annual_salary
FROM employees;

DISTINCT — Unique Values

SELECT DISTINCT department FROM employees;

3. Aggregate Functions and Grouping

Aggregate functions summarize data: COUNT(), SUM(), AVG(), MIN(), MAX().

SELECT COUNT(*) AS total_employees FROM employees;

SELECT AVG(salary) AS avg_salary FROM employees;

GROUP BY

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

HAVING — Filtering Groups

WHERE filters rows before grouping; HAVING filters groups after aggregation.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000;

4. Joins — Combining Tables

Joins are where SQL starts to feel powerful. Assume employees.department actually stores a department_id matching departments.id.

INNER JOIN — Only Matching Rows

SELECT e.name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department = d.id;

LEFT JOIN — All Left Rows, Matched or Not

SELECT e.name, d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department = d.id;

RIGHT JOIN and FULL OUTER JOIN

-- All departments, even if no employees
SELECT e.name, d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department = d.id;

-- Everything from both sides
SELECT e.name, d.name AS department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department = d.id;

Self Join — Employees and Their Managers

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

5. Subqueries

A subquery is a query nested inside another query.

Subquery in WHERE

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Subquery in FROM (Derived Table)

SELECT department, avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) AS dept_avg
WHERE avg_salary > 65000;

Correlated Subquery

References the outer query, runs once per outer row.

SELECT e.name, e.salary, e.department
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.department = e.department
);

This finds employees who earn more than their own department’s average.

6. Common Table Expressions (CTEs)

CTEs, written with WITH, make complex queries readable by breaking them into named steps.

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary;

Recursive CTE — Organizational Hierarchy

WITH RECURSIVE org_chart AS (
    -- Anchor: top-level employees
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: find direct reports
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart
ORDER BY level;

7. Window Functions

Window functions perform calculations across a set of rows related to the current row, without collapsing rows the way GROUP BY does. This is one of the most powerful — and most interview-favorite — SQL topics.

ROW_NUMBER, RANK, DENSE_RANK

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;
  • ROW_NUMBER() gives every row a unique number.
  • RANK() gives ties the same rank but skips subsequent numbers.
  • DENSE_RANK() gives ties the same rank without skipping.

Top N Per Group

WITH ranked AS (
    SELECT
        name, department, salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3;   -- top 3 earners per department

Running Totals with SUM() OVER

SELECT
    name,
    hire_date,
    salary,
    SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employees;

LAG and LEAD — Comparing to Other Rows

SELECT
    name,
    department,
    salary,
    LAG(salary)  OVER (PARTITION BY department ORDER BY hire_date) AS prev_hire_salary,
    LEAD(salary) OVER (PARTITION BY department ORDER BY hire_date) AS next_hire_salary
FROM employees;

8. CASE Expressions

CASE lets you add conditional logic directly into a query.

SELECT
    name,
    salary,
    CASE
        WHEN salary >= 90000 THEN 'High'
        WHEN salary >= 60000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_band
FROM employees;

9. Set Operations

Combine results from multiple SELECT statements.

-- UNION removes duplicates, UNION ALL keeps them
SELECT name FROM employees WHERE department = 'Sales'
UNION
SELECT name FROM employees WHERE salary > 80000;

SELECT name FROM employees
INTERSECT
SELECT name FROM employees WHERE hire_date > '2023-01-01';

SELECT name FROM employees
EXCEPT
SELECT name FROM employees WHERE department = 'Sales';

10. Data Modification (DML)

-- Insert
INSERT INTO employees (id, name, department, salary, manager_id, hire_date)
VALUES (101, 'Priya Sharma', 'Engineering', 75000, 5, '2024-03-01');

-- Update
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'Engineering';

-- Delete
DELETE FROM employees
WHERE id = 101;

11. Indexes and Performance (A Quick Primer)

As queries grow more advanced, performance starts to matter.

CREATE INDEX idx_employees_department ON employees(department);
  • Indexes speed up lookups and joins on the indexed column(s), but slow down inserts/updates slightly.
  • Use EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to see how the database plans to execute a query:
EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

12. Putting It All Together — An Advanced Example

Find, for each department, the top-paid employee, their rank among all departments by salary, and how their salary compares to the company-wide average — all in one query.

WITH dept_ranked AS (
    SELECT
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
    FROM employees
),
company_avg AS (
    SELECT AVG(salary) AS avg_salary FROM employees
)
SELECT
    dr.department,
    dr.name AS top_earner,
    dr.salary,
    ROUND(dr.salary - ca.avg_salary, 2) AS diff_from_company_avg
FROM dept_ranked dr
CROSS JOIN company_avg ca
WHERE dr.dept_rank = 1
ORDER BY dr.salary DESC;

Where to Go From Here

Once you’re comfortable with everything above, good next steps are:

  • Query optimization: execution plans, indexing strategies, query rewriting
  • Transactions: BEGIN, COMMIT, ROLLBACK, isolation levels
  • Stored procedures and triggers
  • Database-specific features: JSON columns, full-text search, partitioning

SQL rewards practice more than memorization — the best way to get comfortable is to take a dataset you care about and start asking it questions.