Part 1: SQL Basics

SELECT

SELECT is how you read data from a table. Think of it like asking the database a question: "Show me this information." You pick which columns you want to see.

Select All Columns

The asterisk * means "give me every column."

SQLSELECT * FROM employees;

Output:

id	name	department	salary	hire_date
1	John Smith	Engineering	75000.00	2020-03-15
2	Jane Doe	Marketing	65000.00	2021-07-22
3	Bob Wilson	Engineering	80000.00	2019-11-08
4	Alice Johnson	Sales	55000.00	2022-01-30
5	Charlie Brown	Marketing	70000.00	2021-05-12

Select Specific Columns

List only the columns you care about, separated by commas.

SQLSELECT name, department, salary FROM employees;

Output:

name	department	salary
John Smith	Engineering	75000.00
Jane Doe	Marketing	65000.00
Bob Wilson	Engineering	80000.00
Alice Johnson	Sales	55000.00
Charlie Brown	Marketing	70000.00

INSERT

INSERT adds new rows into a table. You specify which columns get which values. It's like filling out a new employee registration form.

Single Row Insert

SQLINSERT INTO employees (name, department, salary, hire_date)
VALUES ('Diana Prince', 'Sales', 60000.00, '2023-04-10');

Query OK, 1 row affected

Resulting table now contains:

id	name	department	salary	hire_date
1	John Smith	Engineering	75000.00	2020-03-15
2	Jane Doe	Marketing	65000.00	2021-07-22
3	Bob Wilson	Engineering	80000.00	2019-11-08
4	Alice Johnson	Sales	55000.00	2022-01-30
5	Charlie Brown	Marketing	70000.00	2021-05-12
6	Diana Prince	Sales	60000.00	2023-04-10

Multi-Row Insert

Add several rows at once by separating each set of values with a comma.

SQLINSERT INTO products (product_name, category, price)
VALUES
    ('Tablet', 'Electronics', 499.99),
    ('Bookshelf', 'Furniture', 249.99),
    ('Monitor', 'Electronics', 379.99);

Query OK, 3 rows affected

New rows added to products:

id	product_name	category	price
6	Tablet	Electronics	499.99
7	Bookshelf	Furniture	249.99
8	Monitor	Electronics	379.99

UPDATE

UPDATE changes data that already exists in a table. Always use a WHERE clause so you don't accidentally change every row!

Update Single Column

SQLUPDATE employees
SET salary = 78000.00
WHERE id = 1;

Query OK, 1 row affected

Employee id=1 after update:

id	name	department	salary
1	John Smith	Engineering	78000.00

Update Multiple Columns

SQLUPDATE orders
SET status = 'shipped', quantity = 3
WHERE id = 2;

Query OK, 1 row affected

Order id=2 after update:

id	customer_id	product_id	order_date	status	quantity
2	2	1	2026-02-21	shipped	3

Update with WHERE Condition

Update all rows that match a condition — here, marking every pending order as shipped.

SQLUPDATE orders
SET status = 'shipped'
WHERE quantity = 1 AND status = 'pending';

Query OK, 1 row affected (order id=5 updated)

Affected rows:

id	customer_id	status	quantity
5	5	shipped	1

DELETE

DELETE removes rows from a table permanently. Like UPDATE, always use WHERE unless you truly want to wipe every row.

Delete Specific Row

SQLDELETE FROM orders
WHERE id = 5;

Query OK, 1 row affected

orders after deletion:

id	customer_id	product_id	order_date	status	quantity
1	1	1	2026-02-20	delivered	2
2	2	1	2026-02-21	pending	1
3	1	2	2026-02-22	processing	1
4	3	1	2026-02-22	delivered	3

Delete with Conditions

Remove all delivered single-quantity orders.

SQLDELETE FROM orders
WHERE status = 'delivered' AND quantity = 2;

Query OK, 1 row affected (order id=1 deleted)

WHERE

WHERE filters rows so you only get the ones that match your condition. It's like saying "but only show me the ones where..."

Equals ( = )

SQLSELECT * FROM employees WHERE department = 'Engineering';

Output:

id	name	department	salary	hire_date
1	John Smith	Engineering	75000.00	2020-03-15
3	Bob Wilson	Engineering	80000.00	2019-11-08

Not Equals ( != or <> )

SQLSELECT product_name, price FROM products WHERE category != 'Electronics';

Output:

product_name	price
Desk	349.99
Chair	199.99

Greater Than ( > ) and Less Than ( < )

SQLSELECT product_name, price FROM products WHERE price > 300;

Output:

product_name	price
Laptop	999.99
Phone	699.99
Desk	349.99

AND

Both conditions must be true.

SQLSELECT * FROM orders
WHERE status = 'delivered' AND customer_id = 1;

Output:

id	customer_id	product_id	order_date	status	quantity
1	1	1	2026-02-20	delivered	2
4	1	3	2026-02-22	delivered	1

OR

At least one condition must be true.

SQLSELECT * FROM orders
WHERE status = 'pending' OR status = 'processing';

Output:

id	customer_id	product_id	order_date	status	quantity
2	2	1	2026-02-21	pending	1
3	1	2	2026-02-22	processing	1

NOT

Reverses a condition — "give me everything that is NOT this."

SQLSELECT name, department FROM employees
WHERE NOT department = 'Engineering';

Output:

name	department
Jane Doe	Marketing
Alice Johnson	Sales
Charlie Brown	Marketing

ORDER BY

ORDER BY sorts your results. By default it goes A→Z (ascending). Add DESC to reverse it.

Ascending (default)

SQLSELECT product_name, price FROM products ORDER BY price ASC;

Output:

product_name	price
Headphones	149.99
Chair	199.99
Desk	349.99
Phone	699.99
Laptop	999.99

Descending

SQLSELECT product_name, price FROM products ORDER BY price DESC;

Output:

product_name	price
Laptop	999.99
Phone	699.99
Desk	349.99
Chair	199.99
Headphones	149.99

Multiple Columns

Sort by category first (A→Z), then by price highest-first within each category.

SQLSELECT product_name, category, price FROM products
ORDER BY category ASC, price DESC;

Output:

product_name	category	price
Laptop	Electronics	999.99
Phone	Electronics	699.99
Headphones	Electronics	149.99
Desk	Furniture	349.99
Chair	Furniture	199.99

GROUP BY

GROUP BY lumps rows together based on a column's value, so you can run aggregate functions like COUNT(), SUM(), or AVG() on each group. Think of it as "give me a summary per category."

Basic Grouping with COUNT

SQLSELECT category, COUNT(*) AS total_products
FROM products
GROUP BY category;

Output:

category	total_products
Electronics	3
Furniture	2

HAVING

HAVING is like WHERE but for grouped results. You can't use WHERE to filter on COUNT() or SUM() — that's what HAVING is for.

SQLSELECT category, COUNT(*) AS total_products, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 2;

Output:

category	total_products	avg_price
Electronics	3	616.656667

Only Electronics has more than 2 products, so it's the only group that passes the HAVING filter.

LIMIT

LIMIT restricts how many rows the query returns. Great for previewing data or showing "Top N" results.

SQLSELECT product_name, price FROM products
ORDER BY price DESC
LIMIT 3;

Output (top 3 most expensive products):

product_name	price
Laptop	999.99
Phone	699.99
Desk	349.99

OFFSET

OFFSET skips the first N rows before starting to return results. By itself you still need LIMIT to cap how many rows come back.

SQLSELECT name, department FROM employees
ORDER BY id
LIMIT 3 OFFSET 2;

Output (skip first 2, then return 3):

name	department
Bob Wilson	Engineering
Alice Johnson	Sales
Charlie Brown	Marketing

LIMIT + OFFSET (Pagination)

Combine them to build pagination — like showing page 1, page 2, etc. of results on a website. If each page shows 2 records:

Page 1

SQL-- Page 1: skip 0, show 2
SELECT id, name, department FROM employees
ORDER BY id
LIMIT 2 OFFSET 0;

Output (Page 1):

id	name	department
1	John Smith	Engineering
2	Jane Doe	Marketing

Page 2

SQL-- Page 2: skip 2, show 2
SELECT id, name, department FROM employees
ORDER BY id
LIMIT 2 OFFSET 2;

Output (Page 2):

id	name	department
3	Bob Wilson	Engineering
4	Alice Johnson	Sales

Page 3

SQL-- Page 3: skip 4, show 2
SELECT id, name, department FROM employees
ORDER BY id
LIMIT 2 OFFSET 4;

Output (Page 3):

id	name	department
5	Charlie Brown	Marketing

INNER JOIN

INNER JOIN returns only the rows where there is a match in BOTH tables. If a customer has no orders, they won't appear. If an order has no matching customer, it won't appear either.

Only the overlap is returned — customers who have at least one order.

SQLSELECT c.name, o.order_date, o.status
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Output:

name	order_date	status
Acme Corp	2026-02-20	delivered
TechStart Inc	2026-02-21	pending
Acme Corp	2026-02-22	processing
Global Trade	2026-02-22	delivered
CloudNet	2026-02-23	pending

Notice: DataFlow Ltd (id=4) does not appear because they have no orders.

LEFT JOIN

LEFT JOIN returns ALL rows from the left table (customers) and the matching rows from the right table (orders). If there's no match, the right side columns show NULL.

All of the left table is returned, plus matching rows from the right. Unmatched right-side columns become NULL.

SQLSELECT c.name, o.order_date, o.status
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Output:

name	order_date	status
Acme Corp	2026-02-20	delivered
Acme Corp	2026-02-22	processing
TechStart Inc	2026-02-21	pending
Global Trade	2026-02-22	delivered
DataFlow Ltd	NULL	NULL
CloudNet	2026-02-23	pending

DataFlow Ltd now appears with NULL values for order columns because they have no orders — the LEFT JOIN keeps them anyway.

RIGHT JOIN

RIGHT JOIN is the mirror of LEFT JOIN. It returns ALL rows from the right table and matching rows from the left. In practice, most people just swap the table order and use LEFT JOIN instead.

All of the right table is returned, plus matching rows from the left.

SQLSELECT d.dept_name, d.budget, e.id AS emp_id, e.name
FROM departments d
RIGHT JOIN employees e ON d.dept_name = e.department;

Output:

dept_name	budget	emp_id	name
Engineering	500000.00	1	John Smith
Marketing	300000.00	2	Jane Doe
Engineering	500000.00	3	Bob Wilson
Sales	250000.00	4	Alice Johnson
Marketing	300000.00	5	Charlie Brown

All 5 employees appear (right table). If there were an employee with a department not in the departments table, the department columns would be NULL.

CROSS JOIN

CROSS JOIN pairs every row from the first table with every row from the second table. If table A has 3 rows and table B has 2 rows, you get 3 × 2 = 6 rows. This is called a "Cartesian product."

departments (2 rows) × products (5 rows) = 10 rows

departments

Engineering
Marketing

×

products

Laptop
Phone
Desk
Chair
Headphones

=

10 combinations

Every department paired
with every product

SQLSELECT d.dept_name, p.product_name
FROM departments d
CROSS JOIN products p;

Output (showing all 10 rows):

dept_name	product_name
Engineering	Laptop
Engineering	Phone
Engineering	Desk
Engineering	Chair
Engineering	Headphones
Marketing	Laptop
Marketing	Phone
Marketing	Desk
Marketing	Chair
Marketing	Headphones

UNION

UNION stacks the results of two queries on top of each other (vertically). It automatically removes duplicate rows. Both queries must have the same number of columns.

SQL-- Customers who have delivered orders, combined with customers in specific cities
SELECT c.name, c.city FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.status = 'delivered'

UNION

SELECT name, city FROM customers
WHERE country = 'USA';

Output (duplicates removed):

name	city
Acme Corp	New York
Global Trade	Chicago
TechStart Inc	San Francisco
DataFlow Ltd	Boston
CloudNet	Seattle

Acme Corp appears in both queries (delivered order + USA-based). No duplicates in the final result because UNION removes them.

UNION ALL

UNION ALL works like UNION but keeps all rows, including duplicates. It's faster because it doesn't need to check for duplicates.

SQLSELECT name, department FROM employees WHERE department = 'Engineering'

UNION ALL

SELECT name, department FROM employees WHERE id < 4;

Output (duplicates kept):

name	department
John Smith	Engineering
Bob Wilson	Engineering
John Smith	Engineering
Jane Doe	Marketing
Bob Wilson	Engineering

John Smith and Bob Wilson appear twice — once from each query. UNION ALL keeps both copies. The highlighted rows come from the second query.

DISTINCT

DISTINCT removes duplicate values from your results. If 3 products are in Electronics, you'll still only see 'Electronics' once.

SQLSELECT DISTINCT category FROM products;

Output:

category
Electronics
Furniture

SQL-- Compare: without DISTINCT, Electronics appears 3 times
SELECT category FROM products;

Output (without DISTINCT):

category
Electronics
Furniture
Electronics
Furniture
Electronics

AS (Aliases)

AS gives a temporary nickname to a column or table. It makes your output cleaner and your queries easier to read.

Column Alias

SQLSELECT
    name AS 'Employee Name',
    department AS 'Department',
    hire_date AS 'Start Date'
FROM employees;

Output:

Employee Name	Department	Start Date
John Smith	Engineering	2020-03-15
Jane Doe	Marketing	2021-07-22
Bob Wilson	Engineering	2019-11-08
Alice Johnson	Sales	2022-01-30
Charlie Brown	Marketing	2021-05-12

Table Alias

Shorten table names so joins are less repetitive to type.

SQLSELECT c.name, o.status
FROM customers AS c
INNER JOIN orders AS o ON c.id = o.customer_id
WHERE o.status = 'delivered';

Output:

name	status
Acme Corp	delivered
Global Trade	delivered

IN

IN lets you check if a value matches any item in a list. It's a cleaner alternative to writing multiple OR conditions.

SQLSELECT * FROM products
WHERE product_name IN ('Laptop', 'Phone', 'Headphones');

Output:

id	product_name	category	price
1	Laptop	Electronics	999.99
2	Phone	Electronics	699.99
5	Headphones	Electronics	149.99

BETWEEN

BETWEEN filters values within a range (inclusive on both ends). Works with numbers, dates, and strings.

SQLSELECT product_name, price FROM products
WHERE price BETWEEN 200 AND 700;

Output:

product_name	price
Phone	699.99
Desk	349.99

SQL-- BETWEEN works with dates too
SELECT id, order_date, status FROM orders
WHERE order_date BETWEEN '2026-02-21' AND '2026-02-22';

Output:

id	order_date	status
2	2026-02-21	pending
3	2026-02-22	processing
4	2026-02-22	delivered

LIKE

LIKE searches for patterns in text. Use % to match any number of characters and _ to match exactly one character.

% Wildcard (any characters)

SQL-- Names starting with 'J'
SELECT name, department FROM employees
WHERE name LIKE 'J%';

Output:

name	department
John Smith	Engineering
Jane Doe	Marketing

SQL-- Product names containing 'ph' anywhere
SELECT product_name FROM products
WHERE product_name LIKE '%ph%';

Output:

product_name
Headphones

_ Wildcard (single character)

SQL-- Product names with exactly 4 letters
SELECT product_name, price FROM products
WHERE product_name LIKE '____';

Output:

product_name	price
Desk	349.99

IS NULL / IS NOT NULL

NULL means "no value" or "unknown." You can't check for it with = NULL — you must use IS NULL or IS NOT NULL.

IS NULL

SQLSELECT name, email, city FROM customers
WHERE email IS NULL;

Output:

name	email	city
DataFlow Ltd	NULL	Boston

IS NOT NULL

SQLSELECT name, email, city FROM customers
WHERE email IS NOT NULL;

Output:

name	email	city
Acme Corp	info@acme.com	New York
TechStart Inc	hello@techstart.com	San Francisco
Global Trade	contact@globaltrade.com	Chicago
CloudNet	support@cloudnet.com	Seattle

EXISTS

EXISTS checks if a subquery returns any rows at all. It's like asking "is there at least one matching row?" It returns TRUE or FALSE.

SQL-- Find customers who have at least one order
SELECT name, city
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.customer_id = c.id
);

Output:

name	city
Acme Corp	New York
TechStart Inc	San Francisco
Global Trade	Chicago
CloudNet	Seattle

DataFlow Ltd is excluded because the subquery found zero matching orders for their customer_id.

CREATE TABLE

CREATE TABLE builds a brand-new table in your database. You define each column's name, data type, and any constraints (like NOT NULL, PRIMARY KEY, DEFAULT, etc.).

SQLCREATE TABLE order_items (
    id           INT          AUTO_INCREMENT PRIMARY KEY,
    order_id     INT          NOT NULL,
    product_name VARCHAR(100) NOT NULL,
    added_at     DATETIME     DEFAULT CURRENT_TIMESTAMP,
    added_by     VARCHAR(100) DEFAULT NULL,
    unit_price   DECIMAL(10,2) DEFAULT NULL,
    is_returned  TINYINT(1)   DEFAULT 0,
    notes        TEXT,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

Query OK, 0 rows affected

Table structure created:

Column	Type	Null	Key	Default
id	int	NO	PRI	auto_increment
order_id	int	NO	MUL	NULL
product_name	varchar(100)	NO		NULL
added_at	datetime	YES		CURRENT_TIMESTAMP
added_by	varchar(100)	YES		NULL
unit_price	decimal(10,2)	YES		NULL
is_returned	tinyint(1)	YES		0
notes	text	YES		NULL

ALTER TABLE

ALTER TABLE modifies an existing table's structure — adding columns, removing them, changing data types, or renaming columns.

ADD COLUMN

SQLALTER TABLE employees
ADD COLUMN email VARCHAR(255) DEFAULT NULL AFTER hire_date;

Query OK, 0 rows affected

employees table now has:

id	name	department	salary	hire_date	email
1	John Smith	Engineering	75000.00	2020-03-15	NULL
2	Jane Doe	Marketing	65000.00	2021-07-22	NULL

DROP COLUMN

SQLALTER TABLE employees
DROP COLUMN email;

Query OK, 0 rows affected

MODIFY COLUMN (change data type)

SQLALTER TABLE employees
MODIFY COLUMN name VARCHAR(150) NOT NULL;

Query OK, 0 rows affected

DESCRIBE employees (name column changed):

Field	Type	Null	Default
name	varchar(150)	NO	NULL

RENAME COLUMN

SQLALTER TABLE employees
RENAME COLUMN salary TO annual_salary;

Query OK, 0 rows affected

Column renamed from "salary" to "annual_salary":

id	name	department	annual_salary	hire_date
1	John Smith	Engineering	75000.00	2020-03-15

DROP TABLE

DROP TABLE permanently deletes an entire table and all its data. Use IF EXISTS to avoid an error if the table doesn't exist.

SQLDROP TABLE IF EXISTS order_items;

Query OK, 0 rows affected

The order_items table is now completely gone — structure and all data deleted permanently. IF EXISTS prevents an error if the table was already deleted.

CREATE DATABASE

CREATE DATABASE makes a new empty database on your MySQL server. Then you use USE to switch into it.

SQLCREATE DATABASE my_company
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Query OK, 1 row affected

SQLUSE my_company;

Database changed

SHOW DATABASES (partial):

Database
information_schema
my_company
mysql
performance_schema
sys

INSERT INTO...SELECT

This combines INSERT with a SELECT query, letting you copy data from one table (or a filtered/transformed version of it) into another table.

SQL-- First, create an archive table with the same structure
CREATE TABLE delivered_orders_archive (
    id          INT PRIMARY KEY,
    customer_id INT,
    product_id  INT,
    order_date  DATE,
    status      VARCHAR(20),
    quantity    INT
);

Query OK, 0 rows affected

SQL-- Now copy all delivered orders into the archive
INSERT INTO delivered_orders_archive (id, customer_id, product_id, order_date, status, quantity)
SELECT id, customer_id, product_id, order_date, status, quantity
FROM orders
WHERE status = 'delivered';

Query OK, 2 rows affected (2 rows copied)

delivered_orders_archive now contains:

id	customer_id	product_id	order_date	status	quantity
1	1	1	2026-02-20	delivered	2
4	3	3	2026-02-22	delivered	1

Only the 2 orders with status = 'delivered' were copied. The original orders table is unchanged.

Table of Contents

SELECT

Select All Columns

Select Specific Columns

INSERT

Single Row Insert

Multi-Row Insert

UPDATE

Update Single Column

Update Multiple Columns

Update with WHERE Condition

DELETE

Delete Specific Row

Delete with Conditions

WHERE

Equals ( = )

Not Equals ( != or <> )

Greater Than ( > ) and Less Than ( < )

AND

OR

NOT

ORDER BY

Ascending (default)

Descending

Multiple Columns

GROUP BY

Basic Grouping with COUNT

HAVING

LIMIT

OFFSET

LIMIT + OFFSET (Pagination)

Page 1

Page 2

Page 3

INNER JOIN

LEFT JOIN

RIGHT JOIN

CROSS JOIN

UNION

UNION ALL

DISTINCT

AS (Aliases)

Column Alias

Table Alias

IN

BETWEEN

LIKE

% Wildcard (any characters)

_ Wildcard (single character)

IS NULL / IS NOT NULL

IS NULL

IS NOT NULL

EXISTS

CREATE TABLE

ALTER TABLE

ADD COLUMN

DROP COLUMN

MODIFY COLUMN (change data type)

RENAME COLUMN

DROP TABLE

CREATE DATABASE

INSERT INTO...SELECT