Database & SQL Notes

Part I — Database Foundations

1 What is a Database
2 RDBMS Properties
3 Types of Keys

Part II — SQL Basics

4 DDL Commands
5 DML Commands
6 Querying Data (DQL)

Part III — Advanced Querying

7 Logical Operators
8 Aggregate Functions
9 CASE Statement

Part IV — Joins, Subqueries & Unions

10 SQL Joins
11 Subqueries
12 Union & Execution Order

Every query below runs against these tables. Refer back here anytime.

customers

id	name	last_name	country	city	age	email
1	Arjun	Patel	India	Mumbai	28	arjun@mail.com
2	Sara	Doe	USA	New York	32	NULL
3	Kenji	Tanaka	Japan	Tokyo	24	kenji@mail.com
4	Anna	Klein	Germany	Berlin	30	anna@mail.com
5	James	Doe	USA	Chicago	22	james@mail.com
6	Priya	Sharma	India	Delhi	27	NULL
7	David	Brown	UK	London	35	david@mail.com
8	Sakura	Ito	Japan	Osaka	19	sakura@mail.com

products

id	name	price	category
1	Laptop	1200	Electronics
2	Headphones	150	Electronics
3	Notebook	8	Stationery
4	Desk Chair	450	Furniture
5	Monitor	600	Electronics
6	Pen Set	25	Stationery

orders

id	customer_id	product_id	quantity
1	1	1	1
2	3	2	2
3	4	4	1
4	2	5	3
5	1	3	5

students

id	name	score
1	Ravi	92
2	Mia	85
3	Chen	73
4	Emma	61
5	Kai	45

shipping

id	order_id	status
1	1	Delivered
2	2	In Transit
3	4	Delivered
4	99	Returned

Part I — Database Foundations

What databases are, why we need them, and how keys keep data organized.

What is a Database

Definition

A database is an organized collection of data stored so it can be easily accessed, managed, and updated. Think of it as a digital filing cabinet — but one that can sort, search, and cross-reference instantly.

A DBMS (Database Management System) is the software that sits between users and the database. It enforces rules that protect the data:

Concern	What it means
Data Integrity	Prevents duplicate, incorrect, or broken data from being stored
Security	Protects sensitive data from unauthorized access or misuse
Concurrency	Allows multiple users to read/write simultaneously without inconsistency

Types of Databases

Type	How data is organized	Best for
Relational	Tables with rows and columns, linked by keys	Structured data with clear relationships
NoSQL	Documents, key-value pairs, graphs, or wide columns	Flexible schemas, large-scale unstructured data

This course focuses entirely on Relational Databases (RDBMS), which use SQL to interact with data.

RDBMS Properties

Every relational database table must follow four rules:

1. Every row must be unique

No two rows can have identical values across all columns.

Invalid — row 2 and row 4 are duplicates

name	age	id_number
Himanshu	80	801046122222
Rahul	92	801034569367
Krish	95	801025683456
Rahul	92	801034569367

2. Column values must share the same data type

If a column is defined as INT, every value in it must be a number. You can't mix types.

Invalid — "Very Old" is not a number

name	age
Himanshu	80
Rahul	92
Rohit	Very Old

3. Values must be atomic (single value per cell)

Each cell holds exactly one value. No lists, no comma-separated entries.

Invalid — two phone numbers in one cell

name	phone
Himanshu	8010461222
Krish	8010256834, 9427823456

4. Every column name must be unique

No two columns in the same table can share a name. This ensures every column can be referenced unambiguously.

Types of Keys

Keys are columns (or combinations of columns) that uniquely identify rows and create relationships between tables. Without keys, you can't reliably tell one "Rahul" from another.

Primary Key

A column (or group of columns) that uniquely identifies each row. Two rules: values must be UNIQUE and never NULL. Each table can have only one primary key.

Example — emp_id is the primary key

emp_id (PK)	name	licence	passport
101	Ravi	DL-4421	P-9981
102	Mia	DL-5532	P-7762
103	Chen	DL-6643	P-5543

Every emp_id is unique and non-null — it can pinpoint exactly one employee.

Candidate Key

Any column (or set of columns) that could serve as the primary key — it's unique and non-null. The primary key is chosen from the candidates.

In the table above, emp_id, licence, and passport are all candidate keys because each uniquely identifies a row.

The primary key is the candidate key you pick. The rest remain candidate keys (sometimes called "alternate keys").

Super Key

Any set of columns that can identify a row — including extra columns beyond what's strictly needed. Every candidate key is a super key, but not every super key is minimal.

Examples of super keys
{emp_id}                         -- minimal (candidate key)
{emp_id, name}                   -- super key (emp_id alone is enough)
{licence, name}                  -- super key
{emp_id, passport, name}         -- super key

Any set containing at least one candidate key is a super key.

Foreign Key

A column in one table that references the primary key of another table, creating a link between them. Unlike primary keys, foreign keys can have duplicates and NULLs.

orders.customer_id → customers.id

order_id (PK)	customer_id (FK)	product_id	quantity
1	1	1	1
2	3	2	2
3	4	4	1
4	2	5	3
5	1	3	5

Here, customer_id in orders points to id in the customers table. Customer 1 (Arjun) has two orders. The foreign key is what makes that relationship possible.

Quick Reference

Key	Unique?	NULL?	Per table
Primary	Yes	No	Exactly one
Candidate	Yes	No	One or more
Super	Yes (may be non-minimal)	Depends	Many
Foreign	No (duplicates allowed)	Yes	Zero or more

Part II — SQL Basics

Creating tables, inserting data, and writing your first queries.

DDL — Data Definition Language

DDL commands define the structure of your database — creating, modifying, and deleting databases and tables. They don't touch the data inside.

Database Commands

Command	What it does
CREATE DATABASE db;	Creates a new database
SHOW DATABASES;	Lists all databases on the server
USE db;	Switches to a database (makes it active)
SELECT DATABASE();	Shows which database is currently selected
SHOW TABLES;	Lists all tables in the active database
DROP DATABASE db;	Deletes an entire database (irreversible)

CREATE TABLE

Defines a new table with columns and their data types.

Syntax
CREATE TABLE customers (
  id        INT PRIMARY KEY,
  name      VARCHAR(50) NOT NULL,
  last_name VARCHAR(50),
  country   VARCHAR(50),
  city      VARCHAR(50),
  age       INT,
  email     VARCHAR(100)
);

Each column needs a name and a data type. Common types:

Type	Stores	Example
INT	Whole numbers	age, quantity, id
VARCHAR(n)	Variable-length text up to n chars	name, email
TEXT	Long text (no limit specified)	description, bio
DECIMAL(p,s)	Precise decimal numbers	price, salary
DATE	Date values	order_date

SQL Constraints

Rules you attach to columns to control what data is allowed.

Constraint	Rule
PRIMARY KEY	Unique + Not Null. Identifies each row.
NOT NULL	Column cannot be empty
UNIQUE	All values must be different
DEFAULT val	Uses a default value if none is provided
FOREIGN KEY	References a primary key in another table
CHECK	Validates a condition (e.g., age > 0)

ALTER TABLE

Modifies an existing table — add, rename, change, or drop columns.

Add a column
ALTER TABLE customers ADD phone VARCHAR(10);

Rename a column
ALTER TABLE customers RENAME COLUMN city TO location;

Change a column's type
ALTER TABLE customers MODIFY COLUMN age VARCHAR(3);

Drop a column
ALTER TABLE customers DROP COLUMN email;

Rename the table
ALTER TABLE customers RENAME TO clients;

DROP TABLE

Permanently deletes a table and all its data.

DROP TABLE customers;

DROP is irreversible. The table and all rows are gone. Always double-check before running.

DML — Data Manipulation Language

DML commands change the data inside tables — inserting new rows, updating existing ones, or deleting them.

INSERT INTO

Adds new rows to a table.

Insert a single row
INSERT INTO customers (id, name, last_name, country, city, age, email)
VALUES (9, 'Liam', 'Park', 'Korea', 'Seoul', 26, 'liam@mail.com');

Table after INSERT — new row added at the end

id	name	last_name	country	city	age	email
1	Arjun	Patel	India	Mumbai	28	arjun@mail.com
... rows 2–8 ...
9	Liam	Park	Korea	Seoul	26	liam@mail.com

Insert multiple rows at once
INSERT INTO customers (id, name, last_name, country, city, age, email)
VALUES
  (9, 'Liam', 'Park', 'Korea', 'Seoul', 26, 'liam@mail.com'),
  (10, 'Zara', 'Ali', 'India', 'Pune', 23, 'zara@mail.com');

UPDATE

Modifies existing rows. Always use WHERE to target specific rows.

Example — change David's city
UPDATE customers
SET city = 'Manchester'
WHERE id = 7;

Before → After

id	name	city (before)	city (after)
7	David	London	Manchester

If you omit WHERE, every row in the table gets updated. This is almost never what you want and it's irreversible.

DELETE

Removes rows from a table.

Delete specific rows
DELETE FROM customers
WHERE country = 'UK';

Result — David (id 7) is removed, 7 rows remain

id	name	country
1	Arjun	India
2	Sara	USA
3	Kenji	Japan
4	Anna	Germany
5	James	USA
6	Priya	India
8	Sakura	Japan

DELETE vs TRUNCATE

DELETE FROM table;	TRUNCATE TABLE table;
Removes all rows one by one	Drops and recreates the table (faster)
Can use WHERE to target specific rows	Always removes all rows
Can be rolled back (if inside a transaction)	Cannot be rolled back

Querying Data — SELECT & Friends

DQL (Data Query Language) is really just one command — SELECT — with many clauses that shape what comes back.

SELECT & SELECT *

Retrieves data from a table. Use * for all columns or list specific ones.

Select specific columns
SELECT name, country FROM customers;

Result — only the 2 requested columns

name	country
Arjun	India
Sara	USA
Kenji	Japan
Anna	Germany
James	USA
Priya	India
David	UK
Sakura	Japan

WHERE Clause

Filters rows based on a condition. Only matching rows appear in the result.

Example
SELECT name, age, country FROM customers
WHERE country = 'USA';

Result

name	age	country
Sara	32	USA
James	22	USA

Comparison Operators

Used inside WHERE to compare values. They return 1 (true) or 0 (false).

Operator	Meaning	Example
=	Equal to	`WHERE age = 25`
!= or <>	Not equal to	`WHERE age != 25`
>	Greater than	`WHERE age > 25`
<	Less than	`WHERE age < 25`
>=	Greater or equal	`WHERE age >= 25`
<=	Less or equal	`WHERE age <= 25`

AS — Column Aliases

Gives a column a temporary display name in the result set.

Example
SELECT name AS customer_name, age AS years_old
FROM customers;

Result — column headers use the aliases

customer_name	years_old
Arjun	28
Sara	32
Kenji	24

The alias only exists in the query output — it doesn't rename the actual column.

ORDER BY

Sorts results. ASC (ascending, default) or DESC (descending).

Sort by age — youngest first
SELECT name, age FROM customers
ORDER BY age ASC;

Result

name	age
Sakura	19
James	22
Kenji	24
Priya	27
Arjun	28
Anna	30
Sara	32
David	35

Multiple columns — sort by country, then by age within each country
                        
SELECT name, country, age FROM customers
ORDER BY country ASC, age DESC;

Result — within each country, oldest first

name	country	age
Anna	Germany	30
Arjun	India	28
Priya	India	27
Kenji	Japan	24
Sakura	Japan	19
David	UK	35
Sara	USA	32
James	USA	22

LIMIT & OFFSET

LIMIT caps how many rows are returned. OFFSET skips rows before starting.

First 3 rows
SELECT name, age FROM customers
ORDER BY age ASC
LIMIT 3;

Result — only 3 youngest

name	age
Sakura	19
James	22
Kenji	24

Skip 2, then take 3 (pagination)
SELECT name, age FROM customers
ORDER BY age ASC
LIMIT 3 OFFSET 2;

Result — skips Sakura and James, takes next 3

name	age
Kenji	24
Priya	27
Arjun	28

LIMIT + OFFSET is how pagination works. Page 1 = LIMIT 10 OFFSET 0, Page 2 = LIMIT 10 OFFSET 10, etc.

SELECT DISTINCT

Returns only unique values, removing duplicates.

Unique countries
SELECT DISTINCT country FROM customers;

Result — 5 unique countries (no repeats)

country
India
USA
Japan
Germany
UK

Distinct on multiple columns — unique country + city pairs
SELECT DISTINCT country, city FROM customers;

Result — 8 unique pairs (each city is different)

country	city
India	Mumbai
USA	New York
Japan	Tokyo
Germany	Berlin
USA	Chicago
India	Delhi
UK	London
Japan	Osaka

Part III — Advanced Querying

Logical operators, aggregate functions, and conditional expressions.

Logical Operators

Logical operators combine or negate conditions in WHERE clauses. They return 1 (true) or 0 (false).

AND Operator

Selects data only if all conditions are true.

Example
SELECT * FROM customers
WHERE country = 'India' AND age > 25;

Result — 2 rows matched

id	name	country	age
1	Arjun	India	28
6	Priya	India	27

OR Operator

Selects data if any one condition is true.

Example
SELECT * FROM customers
WHERE country = 'India' OR country = 'Japan';

Result — 4 rows

id	name	country
1	Arjun	India
3	Kenji	Japan
6	Priya	India
8	Sakura	Japan

NOT Operator

Reverses a condition — selects rows where it's false.

Example
SELECT * FROM customers WHERE NOT country = 'USA';

Result — 6 rows (Sara and James excluded)

id	name	country
1	Arjun	India
3	Kenji	Japan
4	Anna	Germany
6	Priya	India
7	David	UK
8	Sakura	Japan

Parentheses matter when combining — AND binds tighter than OR.

IN / NOT IN

IN matches against a list (shorthand for multiple ORs). NOT IN excludes the list.

Example
SELECT * FROM customers
WHERE country IN ('India', 'Japan', 'Germany');

Result — 5 rows

id	name	country
1	Arjun	India
3	Kenji	Japan
4	Anna	Germany
6	Priya	India
8	Sakura	Japan

BETWEEN / NOT BETWEEN

Selects values within a range (inclusive).

Example
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

Result

id	name	price
2	Headphones	150
4	Desk Chair	450

LIKE & Wildcards

Pattern-matches strings.

Wildcard	Meaning	Example
%	Zero or more characters	`'S%'` — starts with S
_	Exactly one character	`'_r%'` — r is 2nd letter
[]	Any one char in brackets	`'[UK]%'`
!	Exclude chars	`'[!DR]%'`

Example
SELECT * FROM customers WHERE name LIKE 'S%';

Result

id	name	country
2	Sara	USA
8	Sakura	Japan

IS NULL / IS NOT NULL

Checks for missing values. You cannot use = or != for NULL.

Example
SELECT * FROM customers WHERE email IS NULL;

Result — 2 rows with missing emails

id	name	email
2	Sara	NULL
6	Priya	NULL

Aggregate Functions

Aggregate functions calculate a single value from a set of rows. Often paired with GROUP BY.

Function	What it does
MIN()	Smallest value
MAX()	Largest value
COUNT()	Number of rows
SUM()	Total of a numeric column
AVG()	Average of a numeric column

All ignore NULLs except COUNT(*), which counts every row.

MAX() and MIN()

SELECT MAX(price) AS highest FROM products;
SELECT MIN(price) AS lowest FROM products;

Results

highest	lowest
1200	8

Nested SELECT to get the full row:

SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);

Result

id	name	price	category
1	Laptop	1200	Electronics

COUNT()

Variations
SELECT COUNT(*) AS total FROM customers;                 -- 8
SELECT COUNT(email) AS has_email FROM customers;          -- 6
SELECT COUNT(*) FROM customers WHERE country = 'India';   -- 2
SELECT COUNT(DISTINCT country) FROM customers;            -- 5

Why COUNT(email) = 6, not 8

Counted	Skipped (NULL)
Arjun, Kenji, Anna, James, David, Sakura	Sara, Priya

SUM() and AVG()

SELECT SUM(quantity) AS total_items FROM orders;  -- 12
SELECT AVG(price) AS avg_price FROM products;     -- 405.50

SUM breakdown: 1 + 2 + 1 + 3 + 5 = 12

total_items	avg_price
12	405.50

GROUP BY

Groups rows, then runs aggregates per group.

SELECT country, COUNT(*) AS total
FROM customers GROUP BY country;

Result

country	total
India	2
USA	2
Japan	2
Germany	1
UK	1

HAVING

Filters groups after aggregation (WHERE can't do this).

SELECT country, COUNT(*) AS total
FROM customers GROUP BY country
HAVING COUNT(*) > 1;

Result — Germany and UK dropped (total = 1)

country	total
India	2
USA	2
Japan	2

HAVING vs WHERE

WHERE	HAVING
Filters rows before grouping	Filters groups after aggregation
Cannot use aggregate functions	Designed for aggregate functions
Comes before GROUP BY	Comes after GROUP BY

Execution order: WHERE → GROUP BY → HAVING

CASE Statement

Works like if-else inside a query. Evaluates conditions and adds a new column based on which one matches first.

Basic CASE

SELECT name, price,
  CASE
    WHEN price > 1000 THEN 'Expensive'
    WHEN price > 500  THEN 'Mid-range'
  END AS price_category
FROM products;

Result — unmatched rows get NULL

name	price	price_category
Laptop	1200	Expensive
Headphones	150	NULL
Notebook	8	NULL
Desk Chair	450	NULL
Monitor	600	Mid-range
Pen Set	25	NULL

CASE with ELSE

ELSE catches all unmatched rows so nothing becomes NULL.

SELECT name, score,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
  END AS grade
FROM students;

Result — Kai gets 'F' instead of NULL

name	score	grade
Ravi	92	A
Mia	85	B
Chen	73	C
Emma	61	D
Kai	45	F

Always include ELSE when every row should get a value — otherwise unmatched rows silently become NULL.

Part IV — Joins, Subqueries & Unions

Combining data from multiple tables and nesting queries inside queries.

SQL Joins

So far, every query has worked with a single table. JOIN combines rows from two or more tables using a common column, letting you pull related data together in one result.

Join Type	What it returns
INNER JOIN	Only rows that match in both tables
LEFT JOIN	All rows from the left table + matches from the right (NULLs if no match)
RIGHT JOIN	All rows from the right table + matches from the left (NULLs if no match)
FULL JOIN	All rows from both tables (NULLs where no match on either side)

INNER JOIN

Returns only rows with matching values in both tables. Rows that don't match are excluded entirely.

Example — customers who placed orders
SELECT c.name, c.country, o.product_id, o.quantity
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Result — 5 rows (only customers with orders)

name	country	product_id	quantity
Arjun	India	1	1
Arjun	India	3	5
Sara	USA	5	3
Kenji	Japan	2	2
Anna	Germany	4	1

James, Priya, David, and Sakura have no orders — they don't appear at all.

LEFT JOIN

Returns all rows from the left table, plus matches from the right. Unmatched right-side columns become NULL.

Example — all customers, with order info if it exists
SELECT c.name, c.country, o.product_id, o.quantity
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Result — 9 rows (all 8 customers, Arjun appears twice)

name	country	product_id	quantity
Arjun	India	1	1
Arjun	India	3	5
Sara	USA	5	3
Kenji	Japan	2	2
Anna	Germany	4	1
James	USA	NULL	NULL
Priya	India	NULL	NULL
David	UK	NULL	NULL
Sakura	Japan	NULL	NULL

Customers without orders still appear — their order columns are filled with NULL.

Handling NULLs with COALESCE()

COALESCE() replaces NULL with a default value — useful after LEFT/RIGHT joins.

SELECT c.name,
       COALESCE(o.quantity, 0) AS quantity
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

Now instead of NULL, customers without orders show 0.

RIGHT JOIN

Opposite of LEFT — returns all rows from the right table, plus matches from the left.

Example — all shipping records, with order info
SELECT o.id AS order_id, o.quantity, s.status
FROM orders o
RIGHT JOIN shipping s ON o.id = s.order_id;

Result — shipping id=4 (order_id 99) has no matching order

order_id	quantity	status
1	1	Delivered
2	2	In Transit
4	3	Delivered
NULL	NULL	Returned

The "Returned" shipping record references order_id 99 which doesn't exist — LEFT columns become NULL.

FULL OUTER JOIN

Returns all rows from both tables. NULLs fill in wherever there's no match on either side.

Example
SELECT o.id AS order_id, o.quantity, s.status
FROM orders o
FULL OUTER JOIN shipping s ON o.id = s.order_id;

Result — unmatched rows from both sides included

order_id	quantity	status
1	1	Delivered
2	2	In Transit
3	1	NULL
4	3	Delivered
5	5	NULL
NULL	NULL	Returned

Orders 3 and 5 have no shipping. Shipping id=4 has no matching order. Both sides are kept.

Joining Multiple Tables

Chain multiple JOINs to connect 3+ tables.

Example — customer name + product name + shipping status
SELECT c.name, p.name AS product, s.status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id
LEFT JOIN shipping s ON o.id = s.order_id;

Result — 3 tables linked through orders

name	product	status
Arjun	Laptop	Delivered
Kenji	Headphones	In Transit
Anna	Desk Chair	NULL
Sara	Monitor	Delivered
Arjun	Notebook	NULL

SELF JOIN

Joins a table with itself. Requires aliases to distinguish the two copies.

Example — find customers from the same country
SELECT c1.name AS customer_1,
       c2.name AS customer_2,
       c1.country
FROM customers c1
INNER JOIN customers c2
  ON c1.country = c2.country
  AND c1.id < c2.id;

Result — pairs of customers sharing a country

customer_1	customer_2	country
Arjun	Priya	India
Sara	James	USA
Kenji	Sakura	Japan

c1.id < c2.id prevents duplicates (Arjun–Priya appears once, not Priya–Arjun too).

CTE — Common Table Expressions

A CTE creates a temporary named result set you can reference in the main query. Makes complex queries more readable.

Syntax
WITH cte_name AS (
  SELECT ...
)
SELECT * FROM cte_name;

Example — CTE with JOIN
WITH indian_customers AS (
  SELECT id, name FROM customers
  WHERE country = 'India'
)
SELECT ic.name, o.product_id, o.quantity
FROM indian_customers ic
INNER JOIN orders o ON ic.id = o.customer_id;

Result — orders from Indian customers only

name	product_id	quantity
Arjun	1	1
Arjun	3	5

Priya has no orders, so she doesn't appear. The CTE acts like a temporary table that exists only for this query.

Subqueries

A subquery is a query nested inside another query. The inner query runs first, and its result is used by the outer query. Subqueries can return:

Type	Returns	Use with
Single row	One value	`= > < >= <=`
Multiple rows	A list of values	`IN, ANY, ALL`
Multiple columns	Row(s) with several columns	Multi-column comparison

Single Row Subquery

Returns one value — used with comparison operators (=, >, <, etc.).

Example — find the youngest customer
SELECT * FROM customers
WHERE age = (SELECT MIN(age) FROM customers);

Inner query returns 19, outer finds who has age = 19

id	name	country	age
8	Sakura	Japan	19

Subquery with IN

When the subquery returns multiple rows, use IN to match against the list.

Example — customers who have placed an order
SELECT name, country FROM customers
WHERE id IN (SELECT customer_id FROM orders);

Inner returns (1,3,4,2,1) → unique: 1,2,3,4

name	country
Arjun	India
Sara	USA
Kenji	Japan
Anna	Germany

This gives the same result as an INNER JOIN, but subqueries are sometimes more readable for simple lookups.

ANY Operator

ANY returns true if the condition matches at least one value from the subquery.

Example — customers younger than any Indian customer
SELECT name, age FROM customers
WHERE age < ANY (
  SELECT age FROM customers WHERE country = 'India'
);

Indian ages are 28 and 27. < ANY means < 28 (the max)

name	age
Kenji	24
Priya	27
James	22
Sakura	19

< ANY effectively means "less than the largest value in the list."

ALL Operator

ALL returns true only if the condition matches every value from the subquery.

Example — customers older than all Indian customers
SELECT name, age FROM customers
WHERE age > ALL (
  SELECT age FROM customers WHERE country = 'India'
);

Must be > 28 AND > 27, so effectively > 28

name	age
Sara	32
Anna	30
David	35

> ALL effectively means "greater than the largest." < ALL means "less than the smallest."

EXISTS Operator

EXISTS checks whether a subquery returns any rows at all. It doesn't care about values — just "is there at least one row?"

Example — customers who have at least one order
SELECT name, country FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

Result — same 4 customers as the IN example

name	country
Arjun	India
Sara	USA
Kenji	Japan
Anna	Germany

EXISTS is often faster than IN for large datasets because it stops as soon as it finds one match.

Subquery vs JOIN

Many subqueries can be rewritten as JOINs and vice versa. The result is the same — pick whichever is more readable.

Subquery approach
SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

JOIN approach (same result)
SELECT DISTINCT c.name
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

The JOIN version needs DISTINCT because Arjun has two orders and would appear twice otherwise.

UNION & Execution Order

UNION

UNION combines the results of two SELECT statements into one result set, removing duplicates.

Example — merge customer names and product names into one list
SELECT name FROM customers WHERE country = 'India'
UNION
SELECT name FROM products WHERE category = 'Electronics';

Result — combined unique names

name
Arjun
Priya
Laptop
Headphones
Monitor

UNION ALL

Same as UNION but keeps duplicates. Faster because it skips the dedup step.

Example — countries from customers + countries from a second table
SELECT country FROM customers WHERE id <= 3
UNION ALL
SELECT country FROM customers WHERE id >= 3;

Result — Japan (id=3) appears in both sets, so it shows twice

country
India
USA
Japan
Japan
Germany
USA
India
UK
Japan

UNION Rules

Rule	Why
Same # of columns	Both SELECTs must return the same number of columns
Matching data types	Columns must be compatible types (both INT, both VARCHAR, etc.)
Same column order	Columns are matched by position, not by name

UNION vs JOIN

UNION	JOIN
Stacks rows vertically	Combines columns horizontally
Tables don't need a common column	Requires a common column to match on
Must have same number of columns	Can have different number of columns

SQL Execution Order

SQL doesn't execute in the order you write it. Understanding the real execution order explains why certain things work (or don't).

Writing order vs execution order
-- YOU WRITE:              -- ENGINE EXECUTES:
SELECT ...          5      FROM ...           1
FROM ...            1      WHERE ...          2
WHERE ...           2      GROUP BY ...       3
GROUP BY ...        3      HAVING ...         4
HAVING ...          4      SELECT ...         5
ORDER BY ...        6      ORDER BY ...       6
LIMIT ...           7      LIMIT ...          7

This is why you can't use a column alias from SELECT inside WHERE — WHERE runs before SELECT. But you can use it in ORDER BY, since that runs after.

Sample Data (used everywhere)

customers:

id	name	last_name	country	city	age	email
1	Arjun	Patel	India	Mumbai	28	arjun@mail.com
2	Sara	Doe	USA	New York	32	NULL
3	Kenji	Tanaka	Japan	Tokyo	24	kenji@mail.com
4	Anna	Klein	Germany	Berlin	30	anna@mail.com
5	James	Doe	USA	Chicago	22	james@mail.com
6	Priya	Sharma	India	Delhi	27	NULL
7	David	Brown	UK	London	35	david@mail.com
8	Sakura	Ito	Japan	Osaka	19	sakura@mail.com

products:

id	name	price	category
1	Laptop	1200	Electronics
2	Headphones	150	Electronics
3	Notebook	8	Stationery
4	Desk Chair	450	Furniture
5	Monitor	600	Electronics
6	Pen Set	25	Stationery

orders: (qty: 1,2,1,3,5) | students: (Ravi 92, Mia 85, Chen 73, Emma 61, Kai 45)

shipping: (ord 1→Delivered, ord 2→In Transit, ord 4→Delivered, ord 99→Returned)

Page 0

Part I — Database Foundations

1. What is a Database

An organized collection of data — a digital filing cabinet.

DBMS = software to manage the database. It enforces:

Data Integrity — no duplicates, no incorrect values

Security — only authorized users can access

Concurrency — multiple users, no conflicts

Types

Relational (RDBMS) — tables, rows, columns, SQL

NoSQL — documents, key-value, graphs

This course = Relational Databases only.

2. RDBMS Properties

Every row must be unique

Column values must share the same data type

Values must be atomic (one value per cell)

Every column name must be unique

Page 1

3. Types of Keys

Keys uniquely identify rows and link tables.

Primary Key

UNIQUE + NOT NULL. One per table. Identifies each row.

Candidate Key

Any column(s) that could be the primary key.

Example: emp_id, licence, passport — all unique.

Super Key

Any set containing a candidate key (may have extras).

{emp_id}              -- candidate (minimal)
{emp_id, name}        -- super key
{licence, name}       -- super key

Foreign Key

Points to another table's primary key. Creates relationships.

Can have duplicates and NULLs.

↳ orders.customer_id → customers.id

Key	Unique?	NULL?	Per table
Primary	Yes	No	1
Candidate	Yes	No	1+
Super	Yes	Depends	Many
Foreign	No	Yes	0+

Page 2

Part II — SQL Basics

4. DDL — Define Structure

Database commands

CREATE DATABASE shop;
SHOW DATABASES;
USE shop;
SELECT DATABASE();
SHOW TABLES;
DROP DATABASE shop;

CREATE TABLE

CREATE TABLE customers (
  id    INT PRIMARY KEY,
  name  VARCHAR(50) NOT NULL,
  age   INT,
  email VARCHAR(100)
);

Constraints

PRIMARY KEY — unique + not null

NOT NULL — can't be empty

UNIQUE — no duplicates

DEFAULT — fallback value

FOREIGN KEY — links to another table

CHECK — validates condition (e.g. age > 0)

ALTER TABLE

ALTER TABLE t ADD col VARCHAR(10);
ALTER TABLE t RENAME COLUMN old TO new;
ALTER TABLE t MODIFY COLUMN col VARCHAR(3);
ALTER TABLE t DROP COLUMN col;
ALTER TABLE t RENAME TO new_t;

DROP TABLE deletes everything. Irreversible!

Page 3

5. DML — Modify Data

INSERT INTO

INSERT INTO customers (id, name, country, age)
VALUES (9, 'Liam', 'Korea', 26);

↳ New row added to the table.

-- Multiple rows at once:
VALUES (9, 'Liam', ...), (10, 'Zara', ...);

UPDATE

UPDATE customers
SET city = 'Manchester'
WHERE id = 7;

↳ David's city: London → Manchester

Without WHERE, ALL rows get updated!

DELETE

DELETE FROM customers WHERE country = 'UK';

↳ David removed. 7 rows remain.

DELETE vs TRUNCATE

DELETE — row by row, can use WHERE, can roll back

TRUNCATE — drops all rows at once, can't roll back

Page 4

6. Querying Data (SELECT)

SELECT + WHERE

SELECT name, country FROM customers;     -- specific cols
SELECT * FROM customers;                 -- all cols
SELECT * FROM customers
WHERE country = 'USA';                   -- filtered

↳ WHERE: Sara, James (2 rows)

Comparison Operators

= (equal), != or <> (not equal)

> < >= <= (greater, less, or equal)

AS — Aliases

SELECT name AS customer_name FROM customers;

Temporary display name. Doesn't rename the column.

ORDER BY

ORDER BY age ASC;       -- youngest first (default)
ORDER BY age DESC;      -- oldest first
ORDER BY country, age;  -- sort by country, then age

LIMIT & OFFSET

LIMIT 3;            -- first 3 rows
LIMIT 3 OFFSET 2;   -- skip 2, take next 3

Pagination: Page 1 = LIMIT 10 OFFSET 0, Page 2 = LIMIT 10 OFFSET 10

DISTINCT

SELECT DISTINCT country FROM customers; -- 5 unique

Page 5

Part III — Advanced Querying

7. Logical Operators

AND — all must be true

WHERE country = 'India' AND age > 25;

↳ Arjun (28), Priya (27)

OR — any one is enough

WHERE country = 'India' OR country = 'Japan';

↳ Arjun, Kenji, Priya, Sakura

NOT — reverses condition

WHERE NOT country = 'USA';

↳ Everyone except Sara & James (6 rows)

Use parentheses! AND binds tighter than OR.

Page 6

7. contd — IN, BETWEEN, LIKE

IN — match list (shorthand for ORs)

WHERE country IN ('India', 'Japan', 'Germany');

↳ 5 rows. NOT IN excludes the list.

BETWEEN — inclusive range

WHERE price BETWEEN 100 AND 500;

↳ Headphones (150), Desk Chair (450)

LIKE — pattern matching

% → zero or more chars

_ → exactly one char

WHERE name LIKE 'S%';   ↳ Sara, Sakura
WHERE name LIKE '%an%'; ↳ Anna

IS NULL / IS NOT NULL

WHERE email IS NULL;      ↳ Sara, Priya
WHERE email IS NOT NULL;  ↳ 6 rows

Cannot use = for NULL. Always IS NULL.

Page 7

8. Aggregate Functions

MAX(price) → 1200

MIN(price) → 8

COUNT(*) → 8, COUNT(email) → 6

SUM(quantity) → 12

AVG(price) → 405.50

All ignore NULLs except COUNT(*).

GROUP BY

SELECT country, COUNT(*) AS total
FROM customers GROUP BY country;

country	total
India	2
USA	2
Japan	2
Germany	1
UK	1

HAVING — filter AFTER grouping

HAVING COUNT(*) > 1;

↳ India(2), USA(2), Japan(2) — drops Germany, UK

WHERE → GROUP BY → HAVING

Page 8

9. CASE Statement

If-else inside a query. Adds a new column.

Basic CASE

CASE
  WHEN price > 1000 THEN 'Expensive'
  WHEN price > 500  THEN 'Mid-range'
END AS price_category

name	price	category
Laptop	1200	Expensive
Headphones	150	NULL
Monitor	600	Mid-range

CASE with ELSE

CASE
  WHEN score >= 90 THEN 'A'
  WHEN score >= 80 THEN 'B'
  WHEN score >= 70 THEN 'C'
  WHEN score >= 60 THEN 'D'
  ELSE 'F'
END AS grade

name	score	grade
Ravi	92	A
Mia	85	B
Chen	73	C
Emma	61	D
Kai	45	F

Without ELSE, Kai would be NULL instead of F.

Page 9

Part IV — Joins, Subqueries & Unions

10. SQL Joins

JOIN = combine rows from 2+ tables using a common column.

INNER JOIN — only matching rows

SELECT c.name, o.quantity
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

↳ 5 rows. James, Priya, David, Sakura excluded (no orders).

LEFT JOIN — all from left + matches from right

FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id;

↳ 9 rows. Customers without orders get NULLs for order cols.

Tip: COALESCE(col, default) replaces NULLs.

RIGHT JOIN — all from right + matches from left

↳ Opposite of LEFT. Unmatched left cols become NULL.

FULL OUTER JOIN — all from both sides

↳ NULLs on either side where no match.

Join	Returns
INNER	Only matching rows
LEFT	All left + matching right
RIGHT	All right + matching left
FULL	All from both sides

Page 10

10. contd — SELF JOIN, CTE, Multi-table

SELF JOIN — table joins itself

SELECT c1.name, c2.name, c1.country
FROM customers c1
INNER JOIN customers c2
  ON c1.country = c2.country
  AND c1.id < c2.id;

↳ Arjun-Priya (India), Sara-James (USA), Kenji-Sakura (Japan)

c1.id < c2.id prevents duplicate pairs.

CTE — Common Table Expression

WITH indian_customers AS (
  SELECT id, name FROM customers
  WHERE country = 'India'
)
SELECT ic.name, o.quantity
FROM indian_customers ic
INNER JOIN orders o ON ic.id = o.customer_id;

↳ Temporary named result. Makes complex queries readable.

Multi-table JOIN — chain JOINs

FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id
LEFT JOIN shipping s ON o.id = s.order_id;

↳ Links 4 tables through the orders table.

Page 11

11. Subqueries

A query nested inside another query. Inner runs first.

Single row — use with =, >, <

SELECT * FROM customers
WHERE age = (SELECT MIN(age) FROM customers);

↳ Inner returns 19 → Sakura

Multi row — use with IN

SELECT name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

↳ Arjun, Sara, Kenji, Anna (customers with orders)

ANY — matches at least one value

WHERE age < ANY (SELECT age ... WHERE country='India');

↳ < ANY = less than the MAX of subquery list

ALL — matches every value

WHERE age > ALL (SELECT age ... WHERE country='India');

↳ > ALL = greater than the MAX of subquery list

EXISTS — does subquery return any rows?

WHERE EXISTS (SELECT 1 FROM orders o
  WHERE o.customer_id = c.id);

EXISTS stops at first match — often faster than IN.

Page 12

12. UNION & Execution Order

UNION — stack results vertically, remove dupes

SELECT name FROM customers WHERE country='India'
UNION
SELECT name FROM products WHERE category='Electronics';

↳ 5 unique names from both queries.

UNION ALL — keep duplicates (faster)

Same as UNION but doesn't deduplicate.

UNION rules

Same number of columns

Matching data types

Columns matched by position, not name

UNION vs JOIN

UNION stacks rows vertically

JOIN combines columns horizontally

Execution order (not writing order!)

1. FROM       → which table
2. WHERE      → filter rows
3. GROUP BY   → group them
4. HAVING     → filter groups
5. SELECT     → pick columns
6. ORDER BY   → sort
7. LIMIT      → cap results

Can't use SELECT alias in WHERE — WHERE runs first!

CAN use alias in ORDER BY — it runs after SELECT.

Page 13

Database & SQL

Contents

Part I — Database Foundations

What is a Database

Definition

Types of Databases

RDBMS Properties

1. Every row must be unique

2. Column values must share the same data type

3. Values must be atomic (single value per cell)

4. Every column name must be unique

Types of Keys

Primary Key

Candidate Key

Super Key

Foreign Key

Quick Reference

Part II — SQL Basics

DDL — Data Definition Language

Database Commands

CREATE TABLE

SQL Constraints

ALTER TABLE

DROP TABLE

DML — Data Manipulation Language

INSERT INTO

UPDATE

DELETE

DELETE vs TRUNCATE

Querying Data — SELECT & Friends

SELECT & SELECT *

WHERE Clause

Comparison Operators

AS — Column Aliases

ORDER BY

LIMIT & OFFSET

SELECT DISTINCT

Part III — Advanced Querying

Logical Operators

AND Operator

OR Operator

NOT Operator

IN / NOT IN

BETWEEN / NOT BETWEEN

LIKE & Wildcards

IS NULL / IS NOT NULL

Aggregate Functions

MAX() and MIN()

COUNT()

SUM() and AVG()

GROUP BY

HAVING

HAVING vs WHERE

CASE Statement

Basic CASE

CASE with ELSE

Part IV — Joins, Subqueries & Unions

SQL Joins

INNER JOIN

LEFT JOIN

Handling NULLs with COALESCE()

RIGHT JOIN

FULL OUTER JOIN

Joining Multiple Tables

SELF JOIN

CTE — Common Table Expressions

Subqueries

Single Row Subquery

Subquery with IN

ANY Operator

ALL Operator

EXISTS Operator

Subquery vs JOIN

UNION & Execution Order

UNION

UNION ALL

UNION Rules

UNION vs JOIN

SQL Execution Order