SQL

Complete Guide
From Basics to Advanced

● Database Design ● Queries ● Joins ● Subqueries ● Indexes ● Transactions

A concise, example-driven reference built around a single e-commerce database. Every concept is
demonstrated on real tables so you see exactly how SQL works in practice.
SQL Complete Guide From Basics to Advanced

Table of Contents
The Sample Database
1.
Our Schema

DDL – Creating & Managing Tables

2.
CREATE, ALTER, DROP

DML – Inserting, Updating, Deleting Data

3.
INSERT, UPDATE, DELETE

Basic SELECT Queries

4.
SELECT, WHERE, ORDER BY, LIMIT

Filtering & Operators

5.
AND/OR, IN, BETWEEN, LIKE, IS NULL

Aggregate Functions & GROUP BY

6.
COUNT, SUM, AVG, MIN, MAX

JOINs
7.
INNER, LEFT, RIGHT, FULL, SELF

Subqueries
8.
Scalar, IN, EXISTS, Correlated

String, Date & Math Functions

9.
Built-in Functions

Views
10.
CREATE VIEW, DROP VIEW

Indexes
11.
CREATE INDEX, Performance

Transactions
12.
BEGIN, COMMIT, ROLLBACK, ACID

Window Functions
13.
ROW_NUMBER, RANK, LAG/LEAD

Quick Reference
14.
Cheat Sheet

© SQL Complete Guide — All examples use the e-commerce sample database Page 2
SQL Complete Guide From Basics to Advanced

Chapter 1 – The Sample Database

All examples in this guide use a small e-commerce database with four tables: customers, products,
orders, and order_items. This mirrors a real-world scenario so every query makes intuitive sense.

Table Columns Description

customers customer_id, name, email, city, join_date People who shop

products product_id, name, category, price, stock Items for sale

orders order_id, customer_id, order_date, status, total Purchase headers

order_items item_id, order_id, product_id, qty, unit_price Line items per order

■ Tip: customer_id in orders is a Foreign Key linking to customers. order_id in order_items links to orders.
This is a classic one-to-many relationship.

© SQL Complete Guide — All examples use the e-commerce sample database Page 3
SQL Complete Guide From Basics to Advanced

Chapter 2 – DDL: Creating & Managing Tables

DDL (Data Definition Language) defines the structure of your database — tables, columns, constraints.

2.1 CREATE TABLE

Use CREATE TABLE to define a new table with column names, data types, and constraints.

-- Create the customers table

CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL,
city VARCHAR(80),
join_date DATE DEFAULT (CURRENT_DATE)
);

-- Create products table

CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(120) NOT NULL,
category VARCHAR(60),
price DECIMAL(8,2) NOT NULL CHECK (price >= 0),
stock INT DEFAULT 0
);

CREATE TABLE orders (

order_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATE DEFAULT (CURRENT_DATE),
status VARCHAR(20) DEFAULT 'pending',
total DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE order_items (

item_id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
qty INT NOT NULL CHECK (qty > 0),
unit_price DECIMAL(8,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);

2.2 Common Data Types

Type Example Use for

INT 42 Whole numbers, IDs

DECIMAL(8,2) 1999.99 Money, exact decimals

VARCHAR(n) 'Alice' Variable-length text

TEXT 'Long text…' Long descriptions

DATE '2024-03-15' Dates (no time)

DATETIME '2024-03-15 10:30:00' Date + time

BOOLEAN TRUE / FALSE Flags / toggles

2.3 ALTER TABLE

Modify an existing table — add/drop columns, rename, change type.

© SQL Complete Guide — All examples use the e-commerce sample database Page 4
SQL Complete Guide From Basics to Advanced

-- Add a phone column

ALTER TABLE customers ADD COLUMN phone VARCHAR(20);
-- Rename a column (MySQL 8+ / PostgreSQL)
ALTER TABLE customers RENAME COLUMN phone TO mobile;
-- Drop a column
ALTER TABLE customers DROP COLUMN mobile;

2.4 DROP & TRUNCATE

DROP TABLE IF EXISTS old_table; -- removes table entirely
TRUNCATE TABLE order_items; -- deletes all rows, keeps structure

■ Note: TRUNCATE cannot be rolled back in MySQL. Use DELETE inside a transaction if you need safety.

© SQL Complete Guide — All examples use the e-commerce sample database Page 5
SQL Complete Guide From Basics to Advanced

Chapter 3 – DML: Inserting, Updating & Deleting Data

DML (Data Manipulation Language) works with the rows inside tables.

3.1 INSERT
-- Insert one customer
INSERT INTO customers (name, email, city, join_date)
VALUES ('Alice Chen', 'alice@[Link]', 'Mumbai', '2023-01-15');
-- Insert multiple rows at once
INSERT INTO customers (name, email, city, join_date) VALUES
('Bob Singh', 'bob@[Link]', 'Delhi', '2023-03-20'),
('Cara Mehta', 'cara@[Link]', 'Pune', '2023-06-01'),
('Dev Patel', 'dev@[Link]', 'Chennai', '2024-01-10'),
('Eva Rao', 'eva@[Link]', 'Mumbai', '2024-02-28');

INSERT INTO products (name, category, price, stock) VALUES

('Laptop Pro 15', 'Electronics', 75000.00, 50),
('Wireless Mouse', 'Electronics', 1200.00, 200),
('SQL Mastery Book','Books', 899.00, 150),
('Desk Lamp LED', 'Furniture', 2500.00, 80),
('Coffee Mug', 'Kitchen', 350.00, 500);

INSERT INTO orders (customer_id, order_date, status, total) VALUES

(1, '2024-03-01', 'delivered', 76200.00),
(2, '2024-03-05', 'shipped', 2099.00),
(1, '2024-03-10', 'pending', 899.00),
(3, '2024-03-12', 'delivered', 3700.00),
(4, '2024-03-15', 'cancelled', 1200.00);
INSERT INTO order_items (order_id, product_id, qty, unit_price) VALUES
(1, 1, 1, 75000.00), (1, 2, 1, 1200.00),
(2, 2, 1, 1200.00), (2, 5, 3, 350.00),
(3, 3, 1, 899.00),
(4, 4, 1, 2500.00), (4, 5, 4, 350.00),
(5, 2, 1, 1200.00);

3.2 UPDATE
-- Update a single row
UPDATE products SET price = 1100.00 WHERE product_id = 2;
-- Update multiple columns
UPDATE orders
SET status = 'delivered', total = total * 0.9 -- 10% discount
WHERE order_id = 2;

■ Note: Always include WHERE in UPDATE/DELETE — without it every row is affected!

3.3 DELETE
-- Delete one order (cancelled)
DELETE FROM orders WHERE order_id = 5;
-- Delete all cancelled orders
DELETE FROM orders WHERE status = 'cancelled';

© SQL Complete Guide — All examples use the e-commerce sample database Page 6
SQL Complete Guide From Basics to Advanced

Chapter 4 – Basic SELECT Queries

SELECT retrieves data. It is the most-used SQL command by far.

4.1 SELECT all / specific columns

SELECT * FROM customers; -- all columns
SELECT name, city FROM customers; -- specific columns
SELECT name, price * 1.18 AS price_with_gst FROM products; -- expression + alias

Result of the last query:

name price_with_gst

Laptop Pro 15 88500.00

Wireless Mouse 1416.00

SQL Mastery Book 1060.82

Desk Lamp LED 2950.00

Coffee Mug 413.00

4.2 WHERE clause

SELECT name, price FROM products WHERE price > 1000;

name price

Laptop Pro 15 75000.00

Wireless Mouse 1100.00

Desk Lamp LED 2500.00

4.3 ORDER BY
SELECT name, price FROM products ORDER BY price DESC; -- highest first
SELECT name, city FROM customers ORDER BY city ASC, name ASC; -- multi-sort

4.4 LIMIT & OFFSET

SELECT name, price FROM products ORDER BY price DESC LIMIT 3; -- top 3
SELECT name, price FROM products ORDER BY price DESC LIMIT 3 OFFSET 3; -- page 2

4.5 DISTINCT
SELECT DISTINCT city FROM customers; -- unique cities only

city

Mumbai

Delhi

Pune

Chennai

© SQL Complete Guide — All examples use the e-commerce sample database Page 7
SQL Complete Guide From Basics to Advanced

Chapter 5 – Filtering & Operators

5.1 AND / OR / NOT
SELECT * FROM products WHERE category = 'Electronics' AND price < 5000;
SELECT * FROM orders WHERE status = 'delivered' OR status = 'shipped';
SELECT * FROM products WHERE NOT category = 'Books';

5.2 IN
IN is a cleaner alternative to multiple OR conditions.

SELECT name, city FROM customers WHERE city IN ('Mumbai', 'Delhi', 'Pune');

5.3 BETWEEN
SELECT name, price FROM products WHERE price BETWEEN 500 AND 3000;
SELECT * FROM orders WHERE order_date BETWEEN '2024-03-01' AND '2024-03-10';

■ Tip: BETWEEN is inclusive — it includes both boundary values.

5.4 LIKE (Pattern Matching)

-- % matches any number of characters
SELECT name FROM customers WHERE name LIKE 'A%'; -- starts with A
SELECT name FROM products WHERE name LIKE '%Pro%'; -- contains 'Pro'
SELECT email FROM customers WHERE email LIKE '%@[Link]';
-- _ matches exactly one character
SELECT name FROM customers WHERE name LIKE '_ob%'; -- e.g. Bob

5.5 IS NULL / IS NOT NULL

SELECT name FROM customers WHERE city IS NULL;
SELECT name FROM customers WHERE city IS NOT NULL;

■ Note: Never use = NULL. Use IS NULL / IS NOT NULL instead.

© SQL Complete Guide — All examples use the e-commerce sample database Page 8
SQL Complete Guide From Basics to Advanced

Chapter 6 – Aggregate Functions & GROUP BY

Aggregate functions collapse many rows into a single value.

6.1 COUNT, SUM, AVG, MIN, MAX

SELECT COUNT(*) AS total_customers FROM customers;
SELECT COUNT(DISTINCT city) AS unique_cities FROM customers;
SELECT SUM(total) AS revenue FROM orders;
SELECT AVG(price) AS avg_price FROM products;
SELECT MIN(price) AS cheapest, MAX(price) AS priciest FROM products;

Query Result

COUNT(*) customers 5

COUNT(DISTINCT city) 4

SUM(total) revenue 82898.00

AVG(price) 16009.80

MIN price / MAX price 350.00 / 75000.00

6.2 GROUP BY
GROUP BY groups rows by a column so you can aggregate per group.

-- How many customers per city?

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
ORDER BY customers DESC;

city customers

Mumbai 2

Delhi 1

Pune 1

Chennai 1

-- Revenue per order status

SELECT status, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
GROUP BY status;

6.3 HAVING (filter on groups)

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

-- Categories with average price above 1000

SELECT category, ROUND(AVG(price),2) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 1000
ORDER BY avg_price DESC;

category avg_price

Electronics 38050.00

© SQL Complete Guide — All examples use the e-commerce sample database Page 9
SQL Complete Guide From Basics to Advanced

Furniture 2500.00
■ Tip: Rule of thumb: WHERE = filter rows, HAVING = filter groups.

© SQL Complete Guide — All examples use the e-commerce sample database Page 10
SQL Complete Guide From Basics to Advanced

Chapter 7 – JOINs
JOINs combine rows from two or more tables based on a related column. Understanding JOINs is the
single most important SQL skill.

7.1 INNER JOIN

Returns only rows that have a match in both tables.

-- Customer name with their orders

SELECT [Link], o.order_id, o.order_date, [Link]
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

name order_id order_date status

Alice Chen 1 2024-03-01 delivered

Bob Singh 2 2024-03-05 shipped

Alice Chen 3 2024-03-10 pending

Cara Mehta 4 2024-03-12 delivered

■ Tip: Eva Rao (no orders) and Dev Patel (cancelled, deleted) do NOT appear — INNER JOIN excludes
non-matching rows.

7.2 LEFT JOIN

Returns all rows from the left table, NULL for non-matching right rows.

-- All customers, even those with no orders

SELECT [Link], COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY [Link]
ORDER BY order_count DESC;

name order_count

Alice Chen 2

Bob Singh 1

Cara Mehta 1

Dev Patel 0

Eva Rao 0

7.3 RIGHT JOIN & FULL OUTER JOIN

-- RIGHT JOIN: all orders, even if customer is missing
SELECT [Link], o.order_id FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;
-- FULL OUTER JOIN (PostgreSQL / SQLite UNION trick)
SELECT [Link], o.order_id FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
UNION
SELECT [Link], o.order_id FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

7.4 Multi-table JOIN

© SQL Complete Guide — All examples use the e-commerce sample database Page 11
SQL Complete Guide From Basics to Advanced

Chain multiple JOINs to pull data from 3+ tables.

-- Full order details: customer → order → items → product

SELECT [Link], o.order_date, [Link] AS product,
[Link], oi.unit_price, ([Link] * oi.unit_price) AS line_total
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
ORDER BY o.order_date, [Link];

7.5 SELF JOIN

A table joined with itself — useful for hierarchical or comparison data.

-- Find products in the same category (self-join example)

SELECT [Link] AS product_1, [Link] AS product_2, [Link]
FROM products a
JOIN products b ON [Link] = [Link] AND a.product_id < b.product_id;

© SQL Complete Guide — All examples use the e-commerce sample database Page 12
SQL Complete Guide From Basics to Advanced

Chapter 8 – Subqueries
A subquery is a SELECT inside another SQL statement. It runs first and feeds its result outward.

8.1 Scalar Subquery (single value)

-- Products priced above the average
SELECT name, price FROM products
WHERE price > (SELECT AVG(price) FROM products);

name price

Laptop Pro 15 75000.00

8.2 Subquery with IN

-- Customers who have placed at least one order
SELECT name FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders);

8.3 EXISTS
EXISTS returns TRUE if the subquery returns any row — often faster than IN for large datasets.

-- Customers with at least one delivered order

SELECT name FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND [Link] = 'delivered'
);

8.4 Subquery in FROM (Derived Table)

-- Average revenue per city
SELECT city, ROUND(AVG(order_total),2) AS avg_order
FROM (
SELECT [Link], [Link] AS order_total
FROM customers c JOIN orders o ON c.customer_id = o.customer_id
) AS city_orders
GROUP BY city;

■ Tip: A subquery in FROM must be given an alias (here: city_orders).

8.5 Correlated Subquery

Runs once per outer row — references the outer query inside.

-- Each customer's most recent order date

SELECT [Link],
(SELECT MAX(o.order_date)
FROM orders o
WHERE o.customer_id = c.customer_id) AS last_order
FROM customers c;

© SQL Complete Guide — All examples use the e-commerce sample database Page 13
SQL Complete Guide From Basics to Advanced

Chapter 9 – String, Date & Math Functions

9.1 String Functions
SELECT UPPER(name) FROM customers; -- ALICE CHEN
SELECT LOWER(email) FROM customers; -- alice@[Link]
SELECT LENGTH(name) FROM customers; -- 10
SELECT SUBSTRING(name,1,5) FROM customers; -- Alice
SELECT CONCAT(name,' - ',city) AS label FROM customers;
SELECT TRIM(' hello '); -- 'hello'
SELECT REPLACE(name,'Pro','Premium') FROM products;

9.2 Date Functions

SELECT CURDATE(); -- today's date
SELECT NOW(); -- current datetime
SELECT YEAR(order_date) FROM orders; -- 2024
SELECT MONTH(order_date) FROM orders; -- 3
SELECT DAY(order_date) FROM orders; -- 1
SELECT DATEDIFF('2024-12-31','2024-01-01'); -- 365
SELECT DATE_ADD(order_date, INTERVAL 7 DAY) AS delivery_eta FROM orders;

9.3 Math Functions

SELECT ROUND(AVG(price), 2) FROM products; -- 16009.80
SELECT CEIL(1.2); -- 2
SELECT FLOOR(1.9); -- 1
SELECT ABS(-250); -- 250
SELECT MOD(10, 3); -- 1
SELECT POWER(2, 10); -- 1024

9.4 CASE Expression

CASE is SQL's if-else. Use it to create conditional columns.

SELECT name, price,

CASE
WHEN price < 1000 THEN 'Budget'
WHEN price < 10000 THEN 'Mid-range'
ELSE 'Premium'
END AS price_tier
FROM products;

name price price_tier

Laptop Pro 15 75000 Premium

Wireless Mouse 1100 Mid-range

SQL Mastery Book 899 Budget

Desk Lamp LED 2500 Mid-range

Coffee Mug 350 Budget

© SQL Complete Guide — All examples use the e-commerce sample database Page 14
SQL Complete Guide From Basics to Advanced

Chapter 10 – Views
A view is a saved SELECT query stored in the database as a virtual table. You query it like a real table,
but it always reflects live data.

10.1 Create a View

-- View: order summary with customer name
CREATE VIEW order_summary AS
SELECT o.order_id, [Link] AS customer, [Link],
o.order_date, [Link], [Link]
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

10.2 Use the View

-- Query the view just like a table
SELECT * FROM order_summary WHERE status = 'delivered';
SELECT customer, SUM(total) FROM order_summary GROUP BY customer;

10.3 Replace / Drop a View

CREATE OR REPLACE VIEW order_summary AS -- update the view
SELECT o.order_id, [Link] AS customer, [Link] FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
DROP VIEW IF EXISTS order_summary; -- remove view

■ Tip: Views simplify complex queries, improve security (expose only certain columns), and make your
code reusable.

© SQL Complete Guide — All examples use the e-commerce sample database Page 15
SQL Complete Guide From Basics to Advanced

Chapter 11 – Indexes
An index is a data structure that makes lookups faster — like a book's index. Without one, the database
scans every row (full table scan).

11.1 Create an Index

-- Single-column index on customer email (already UNIQUE but good example)
CREATE INDEX idx_customer_city ON customers(city);
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_order_status ON orders(status);
-- Composite index (multi-column)
CREATE INDEX idx_order_cust_date ON orders(customer_id, order_date);
-- Unique index
CREATE UNIQUE INDEX idx_email ON customers(email);

11.2 When indexes help vs hurt

Situation Use Index?

Column used frequently in WHERE ■ Yes

Column used in JOIN ON clause ■ Yes

Column used in ORDER BY (large tbl) ■ Yes

Table with very few rows (<1000) ■ No benefit

Columns updated very frequently ■ Use carefully

SELECT * with no filters ■ No benefit

11.3 EXPLAIN (Query Plan)

EXPLAIN SELECT * FROM orders WHERE status = 'delivered';
-- Look at 'type' column: 'ref' or 'range' = index used ■
-- 'ALL' = full scan, consider adding index

11.4 Drop an Index

DROP INDEX idx_order_status ON orders; -- MySQL
DROP INDEX idx_order_status; -- PostgreSQL

© SQL Complete Guide — All examples use the e-commerce sample database Page 16
SQL Complete Guide From Basics to Advanced

Chapter 12 – Transactions
A transaction groups multiple SQL statements so they all succeed or all fail together. This protects data
consistency — critical for financial operations.

12.1 ACID Properties

Property Meaning Example

Atomicity All or nothing Transfer: debit + credit both happen, or neither

Consistency Rules always satisfied No order with non-existent customer_id

Isolation Concurrent txns don't interfereTwo users booking last seat see correct stock

Durability Committed data survives crashOrder stays in DB even if server reboots

12.2 BEGIN / COMMIT / ROLLBACK

-- Place an order safely inside a transaction
START TRANSACTION;
INSERT INTO orders (customer_id, order_date, status, total)
VALUES (1, CURDATE(), 'pending', 3500.00);
INSERT INTO order_items (order_id, product_id, qty, unit_price)
VALUES (LAST_INSERT_ID(), 4, 1, 2500.00),
(LAST_INSERT_ID(), 5, 2, 350.00);
UPDATE products SET stock = stock - 1 WHERE product_id = 4;
UPDATE products SET stock = stock - 2 WHERE product_id = 5;
COMMIT; -- make all changes permanent
-- If anything goes wrong, undo everything:
-- ROLLBACK;

12.3 SAVEPOINT
START TRANSACTION;
INSERT INTO orders ... ;
SAVEPOINT after_order; -- checkpoint
INSERT INTO order_items ... ;
ROLLBACK TO SAVEPOINT after_order; -- undo items only, keep order
COMMIT;

© SQL Complete Guide — All examples use the e-commerce sample database Page 17
SQL Complete Guide From Basics to Advanced

Chapter 13 – Window Functions

Window functions perform calculations across a set of rows related to the current row without
collapsing them (unlike GROUP BY). They use the OVER() clause.

13.1 ROW_NUMBER, RANK, DENSE_RANK

-- Rank products by price (highest first)
SELECT name, price,
ROW_NUMBER() OVER (ORDER BY price DESC) AS row_num,
RANK() OVER (ORDER BY price DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY price DESC) AS dense_rnk
FROM products;

name price row_num rnk dense_rnk

Laptop Pro 15 75000 1 1 1

Desk Lamp LED 2500 2 2 2

Wireless Mouse 1100 3 3 3

SQL Mastery Book 899 4 4 4

Coffee Mug 350 5 5 5

■ Tip: RANK skips numbers after ties; DENSE_RANK does not. E.g. two items tied at rank 2 → RANK
gives 2,2,4; DENSE_RANK gives 2,2,3.

13.2 PARTITION BY
PARTITION BY resets the window per group — like GROUP BY but without collapsing rows.

-- Rank products within each category

SELECT name, category, price,
RANK() OVER (PARTITION BY category ORDER BY price DESC) AS cat_rank
FROM products;

13.3 SUM / AVG running totals

-- Running total of orders by date
SELECT order_date, total,
SUM(total) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date;

13.4 LAG & LEAD

LAG looks at the previous row; LEAD looks at the next row.

-- Compare each order total to the previous order

SELECT order_id, order_date, total,
LAG(total) OVER (ORDER BY order_date) AS prev_total,
total - LAG(total) OVER (ORDER BY order_date) AS change
FROM orders;

© SQL Complete Guide — All examples use the e-commerce sample database Page 18
SQL Complete Guide From Basics to Advanced

Chapter 14 – Quick Reference Cheat Sheet

DDL Commands
Command Syntax

CREATE TABLE CREATE TABLE t (col type constraint, ...);

ALTER TABLE ALTER TABLE t ADD COLUMN / DROP COLUMN / RENAME COLUMN;

DROP TABLE DROP TABLE IF EXISTS t;

TRUNCATE TRUNCATE TABLE t;

DML Commands
Command Syntax

INSERT INSERT INTO t (cols) VALUES (vals), (vals);

UPDATE UPDATE t SET col=val WHERE condition;

DELETE DELETE FROM t WHERE condition;

SELECT Clauses (in execution order)

Clause Purpose

FROM / JOIN Choose tables and join them

WHERE Filter rows before grouping

GROUP BY Group rows for aggregation

HAVING Filter groups after aggregation

SELECT Pick columns / compute expressions

ORDER BY Sort result set

LIMIT / OFFSET Paginate result set

Aggregate Functions
Function Returns

COUNT(*) Number of rows

SUM(col) Total of a numeric column

AVG(col) Mean value

MIN(col) / MAX(col) Smallest / largest value

JOIN Types
Type Returns

INNER JOIN Matching rows in both tables

LEFT JOIN All left rows + matching right (NULL if none)

RIGHT JOIN All right rows + matching left (NULL if none)

© SQL Complete Guide — All examples use the e-commerce sample database Page 19
SQL Complete Guide From Basics to Advanced

FULL OUTER JOIN All rows from both tables

SELF JOIN Table joined with itself

Practice tip: Spin up a free MySQL / PostgreSQL instance, run all the CREATE and INSERT
statements from Chapter 2–3, then attempt every query in this guide from memory. Repetition is the
fastest path to SQL fluency.

© SQL Complete Guide — All examples use the e-commerce sample database Page 20