COMP 163 - SQL Only

Welcome to Introduction to SQL

Course: DATA 013
Format: Asynchronous
Semester: Spring 2026

Focus: core SQL concepts with portable examples across multiple database systems.

Course Assumptions

What is expected before starting this course.

No Database Background Required

No prior SQL required
No database theory required
All concepts start from zero
We build skill through practice

Programming Basics Assumed

Variables and values
Basic types: numbers, text
Conditionals: if / else
Reading structured syntax

Any modern programming language background is sufficient.

How SQL Will Be Taught

Concept and syntax
Short, focused examples

How SQL Fits In

We learn SQL as the language that talks to a database system and, through it, to the data.

You

→

SQL

→

DBMS

→

Data

SQL stays the same; the DBMS may change.

Which SQL Standard We Use

Based primarily on the ANSI / ISO SQL standard
Core concepts shared by all major DBMSs
Limited vendor specific extensions at the start

If SQL works here, it will generally work in:

PostgreSQL
MySQL / MariaDB
SQLite
SQL Server

Reference (informal but practical): SQL Standard Keywords Reference

Canvas Course Home

All official course activity runs through Canvas.

Where to Find Everything

Assignments and due dates
Labs and homework instructions
Grades and feedback
Official announcements

Syllabus on Canvas

The syllabus is the authoritative course reference.

Open Syllabus in Canvas

What This Course Is

Practical introduction to SQL
Focused on querying real data

What This Course Is Not

Not database administration training
Not vendor specific certification
Not tool focused
Not theory heavy

How to Do Well

Write SQL regularly
Run every example yourself
Read errors carefully
Repeat until fluent

Week	Topic	Assignment (Canvas)	Database	Comment
1 (Jan 12, 14, 16)	CREATE, DATABASE, SCHEMA, TABLE	Lab 1, Homework 1 (2w)	SQLite
2 (Jan 21, 23)	INSERT, VALUES, INTO, UPDATE, SET, DELETE			Holiday on Monday (Jan 19)
3 (Jan 26, 28, 30)	SELECT, FROM, WHERE, GROUP, BY, HAVING	Lab 2, Homework 2 (2w)
4 (Feb 2, 4, 6)	ORDER, DISTINCT, AS, ALL, LIMIT, OFFSET		PostgreSQL
5 (Feb 9, 11, 13)	JOIN, INNER, LEFT, RIGHT, FULL, OUTER	Lab 3, Homework 3 (2w)
6 (Feb 18, 20)	EXISTS, IN, ANY, BETWEEN, LIKE, ESCAPE			Holiday on Monday (Feb 16)
7 (Feb 23, 25, 27)	CROSS, ON, USING, UNION, INTERSECT, EXCEPT		MariaDB
8 (Mar 2, 4, 6)	NULL, IS, NOT, DEFAULT, CASE, WHEN
9 (–)				Spring Break
10 (Mar 16, 18, 20)	SCHEMA, COLUMN, SEQUENCE, REFERENCES, DATABASE, INDEX	Lab 4, Homework 4 (2w), Project (4w)	Oracle
11 (Mar 23, 25, 27)	CONSTRAINT, PRIMARY, FOREIGN, KEY, UNIQUE, CHECK
12 (Mar 30, Apr 1, 3)	TRANSACTION, BEGIN, COMMIT, ROLLBACK, SAVEPOINT, RELEASE	Lab 5, Homework 5 (2w)
13 (Apr 6, 8, 10)	LOCK, GRANT, REVOKE, USER, ROLE		ClickHouse
14 (Apr 13, 15, 17)	SQL Databases Review	Lab 6, Homework 6 (2w)
15 (Apr 20, 22, 24)	Semester Project Review
16 (Apr 27)	Semester SQL Query Review			Classes End (April 28)

Module 1

SQL Objects: Database, Schema, Table

CREATE · SELECT · DROP · ALTER · RENAME · TABLE · COLUMN · TYPES

Structure first: we define containers, then organization, then tables and columns.

Part 1: Database

Goal: create, inspect, rename, and drop a database safely.

Agenda

Concept: database as top container
Diagram: where it sits
Syntax: create, select, drop
Rename and update name
Naming conventions
Common errors and mitigation
Support table by database system

Create database

Use a stable project name
Create once during setup
Prefer predictable ownership
Keep it simple at first

CREATE DATABASE flower_shop;

Select and inspect databases

Systems use different queries
Goal is the same: confirm it exists
Always verify before continuing

-- PostgreSQL
\l

-- MySQL and MariaDB
SHOW DATABASES;

-- SQLite
-- database is a file; list attached
PRAGMA database_list;

Rename database or update name

Rename is not portable
Rename requires admin access
Plan naming to avoid renames

In many teams, rename is avoided in production.

-- PostgreSQL (typical)
ALTER DATABASE flower_shop RENAME TO flower_store;

-- MySQL and MariaDB
-- often: create new database, migrate data

-- SQLite
-- rename the file (outside SQL)

Drop database ⚠️

Deletes all data
Drops schemas, tables
Backups before drop

DROP DATABASE flower_shop;

Database naming conventions

Lowercase, underscore naming
Use meaningful nouns
Avoid spaces and punctuation
Avoid reserved words
Prefer stable names over dates

-- Good
flower_shop
sales_data
inventory

-- Risky
my database
db1
select
FLOWERS_2026

Common database errors and mitigation

Error pattern	Cause	Mitigation
Already exists	Name collision	Inspect first, then choose unique name
Permission denied	Missing privileges	Use correct account, request access, document roles
Connected to wrong environment	Wrong host or database	Verify connection string and current database before running DDL
Rename not supported	Vendor limitation	Choose stable name early, use migrate strategy if needed

Database support by system

Operation	SQLite	PostgreSQL	MySQL	MariaDB	Oracle	SQL Server
CREATE DATABASE	Concept via file	Yes	Yes	Yes	Yes	Yes
List databases	PRAGMA database_list	\l	SHOW DATABASES	SHOW DATABASES	SELECT from data dictionary	sys.databases
Rename database	Rename file	ALTER DATABASE RENAME	Not standard	Not standard	Admin workflow	ALTER DATABASE MODIFY NAME
DROP DATABASE	Delete file	Yes	Yes	Yes	Yes	Yes

Portable habit: plan names early; treat rename as exceptional.

Part 2: Schema

Goal: organize objects using schemas when the system supports them.

Agenda

Concept: namespace inside database
Diagram: schema and qualified names
Syntax: create, select, drop
Rename and update name
Naming conventions
Common errors and mitigation
Support table by database system

Create schema

Use schema to separate domains
Inventory, sales common splits
Qualified names reduce collisions

CREATE SCHEMA inventory;
CREATE SCHEMA sales;

Select and inspect schemas

Check existing schema
Check existingconventions
Match team structure

-- Standard information schema (many systems)
SELECT schema_name
FROM information_schema.schemata
ORDER BY schema_name;

-- PostgreSQL
\dn

Rename schema or update name

Rename exists in some systems
Not consistent across vendors
Prefer stable names up front

-- PostgreSQL
ALTER SCHEMA inventory RENAME TO stock;

-- MySQL and MariaDB
-- schema is the database name in practice

-- SQLite
-- schemas are limited; prefer clear table names

Drop schema ⚠️

May require CASCADE
CASCADE drops dependents
Use only for demos
Drops tables and data

DROP SCHEMA inventory;

-- If objects exist (system dependent)
DROP SCHEMA inventory CASCADE;

Schema naming conventions

Short nouns, lowercase
One purpose per schema
Avoid spaces and punctuation
Avoid reserved words
Match business domain terms

-- Good
inventory
sales
customers

-- Risky
SalesSchema
sales-data
my schema
select

Common schema errors and mitigation

Error pattern	Cause	Mitigation
Schema not found	Wrong database or search path	Confirm current database; use qualified names
Already exists	Duplicate name	Inspect schemas first; follow naming standard
Cannot drop, objects exist	Tables and views inside	Drop objects first; use CASCADE only in demos
Feature not supported	Vendor differences	Use database namespaces or clear table prefixes

Schema support by system

Operation	SQLite	PostgreSQL	MySQL	MariaDB	Oracle	SQL Server
CREATE SCHEMA	Limited	Yes	Yes (often equals database)	Yes (often equals database)	Yes (user centric)	Yes
List schemas	Limited	\dn or information schema	information schema	information schema	Data dictionary	sys.schemas
Rename schema	No	ALTER SCHEMA RENAME	Not standard	Not standard	Admin workflow	ALTER SCHEMA
DROP SCHEMA	Limited	Yes	Yes (drop database)	Yes (drop database)	Yes	Yes

Portable habit: always use qualified names in examples.

Part 3: Table

Goal: create tables, inspect them, rename them, and define columns with common types.

Agenda

Concept: rows and columns
Diagram: table shape
Syntax: create, select, drop
Rename and update name
Columns and common data types
Naming conventions
Common errors and mitigation
Support table by database system

Create table with common column types

Start small, add constraints later
Use obvious names for columns
Pick types that match meaning
Prefer explicit identifiers

This is a portable baseline; vendors add extra types.

CREATE TABLE flowers (
  flower_id INTEGER,
  name      TEXT,
  color     TEXT,
  price     DECIMAL,
  in_stock  INTEGER
);

Select and inspect table structure

Inspect immediately after create
Confirm columns and types
Fix early when it is cheap

-- SQLite: list tables
SELECT name
FROM sqlite_master
WHERE type='table'
ORDER BY name;

-- SQLite: inspect columns
PRAGMA table_info(flowers);

-- PostgreSQL: describe
-- \d flowers

Rename table or update name

Rename is widely supported
Still check dependencies
Update software references
Restart software server

ALTER TABLE flowers RENAME TO flower;

-- Some systems also support:
-- RENAME TABLE flowers TO flower;

Drop table ⚠️

Deletes data
Deletes structure
Use for demos
Backups recommended

DROP TABLE flowers;

Common SQL data types by meaning

Meaning	Common type	Examples	Notes
Identifier	INTEGER	flower_id, customer_id	Often paired with primary key later
Short text	TEXT or VARCHAR	name, color	VARCHAR is length bounded in many systems
Money	INTEGER cents or DECIMAL	price_cents, unit_price	Prefer integer cents to avoid rounding issues
Boolean flag	BOOLEAN or INTEGER	in_stock	SQLite commonly uses 0 and 1
Date and time	TEXT or TIMESTAMP	created_at, order_date	SQLite stores as text by convention; others have native types

Table naming conventions

Lowercase, underscore naming
Use id suffix for identifiers
Avoid reserved words
Keep names stable over time

-- Good
flowers
order_item
customer

-- Risky
Order
MyTable
flower data
select

Common table errors and mitigation

Error pattern	Cause	Mitigation
Syntax error near keyword	Reserved word used as name	Rename object or quote carefully; prefer safe names
No such table	Wrong schema or typo	Use qualified names; inspect tables list
Type mismatch or constraint failure	Bad data inserted later	Validate inputs; add constraints gradually
Cannot drop due to dependencies	Foreign keys and views reference it	Drop dependents first; avoid CASCADE in real systems

Table support by system

Operation	SQLite	PostgreSQL	MySQL	MariaDB	Oracle	SQL Server
CREATE TABLE	Yes	Yes	Yes	Yes	Yes	Yes
Inspect columns	PRAGMA table_info	\d or information schema	DESCRIBE or information schema	DESCRIBE or information schema	Data dictionary	sp_help or sys.columns
Rename table	ALTER TABLE RENAME	ALTER TABLE RENAME	RENAME TABLE or ALTER TABLE	RENAME TABLE or ALTER TABLE	RENAME (or ALTER TABLE)	sp_rename or ALTER SCHEMA
DROP TABLE	Yes	Yes	Yes	Yes	Yes	Yes
Common types	INTEGER, TEXT, REAL	INT, TEXT, NUMERIC	INT, VARCHAR, DECIMAL	INT, VARCHAR, DECIMAL	NUMBER, VARCHAR2, DATE	INT, NVARCHAR, DECIMAL

Portable habit: use simple types first, then specialize per vendor.

IF EXISTS

Prevents runtime errors
Safe cleanup operations
Useful in automation

-- Drop only if present
DROP TABLE IF EXISTS flowers;

DROP SCHEMA IF EXISTS inventory;

DROP DATABASE IF EXISTS flower_shop;

Not all databases support IF EXISTS in every statement. Check vendor support.

Module 1 recap

Database defines top level scope
Schema organizes objects by domain
Table stores rows with typed columns
Next: insert, update, delete rows

Module 2

SQL Rows: INSERT, UPDATE, DELETE (Single Table CRUD)

INSERT · INTO · VALUES · SELECT · UPDATE · SET · DELETE · WHERE

Focus: safe row changes on one table at a time. Verification is done with SELECT.

Domain table: flowers (single table only)

All examples use one table: flowers
We practice CRUD without foreign keys
Primary key and auto increment are part of the discipline
SELECT is used to preview and confirm

-- Example schema (for reference only)
-- flowers(
--   flower_id INTEGER PRIMARY KEY AUTOINCREMENT,
--   name      TEXT NOT NULL,
--   color     TEXT NOT NULL,
--   price     REAL NOT NULL,
--   stock     INTEGER NOT NULL
-- );

Diagram: one table, row based operations

Part 0: SELECT as verification

Every change is paired with SELECT: preview and confirm.

Safe workflow (single table)

Preview the set: SELECT ... WHERE ...
Change the set: INSERT / UPDATE / DELETE
Confirm the set: SELECT again
Do not skip verification

-- Preview
SELECT flower_id, name, price, stock
FROM flowers
WHERE color = 'Red';

-- Confirm (same query after change)
SELECT flower_id, name, price, stock
FROM flowers
WHERE color = 'Red';

Diagram: SELECT preview → change → SELECT confirm

Part 1: Insert

Goal: add new rows using INSERT, INTO, VALUES, and INSERT FROM SELECT.

Agenda (Insert)

Concept: new rows
Primary key and auto increment
Diagram: row added
Syntax: INSERT INTO VALUES
Insert multiple rows
Insert without specifying id
SELECT to verify
Insert from SELECT
Naming conventions
Common errors and mitigation

Primary key and auto increment (insert planning)

Primary key uniquely identifies a row
Auto increment lets the database create the id
Habit: do not guess ids when auto increment is available
Use SELECT to confirm the generated id

-- SQLite style:
-- flower_id INTEGER PRIMARY KEY AUTOINCREMENT

-- Insert without flower_id:
INSERT INTO flowers (name, color, price, stock)
VALUES ('Lily', 'White', 4.50, 40);

Diagram: primary key targets a row

Diagram: INSERT adds a new row

Insert row (explicit id)

Use when ids are managed manually
Must not duplicate an existing id
Primary key must be unique
Verify with SELECT

INSERT INTO flowers (flower_id, name, color, price, stock)
VALUES (102, 'Tulip', 'Yellow', 2.49, 80);

Insert row (auto increment id)

Preferred when the database generates ids
Omit the primary key column
Less error prone than manual ids
Confirm the inserted row after

INSERT INTO flowers (name, color, price, stock)
VALUES ('Lily', 'White', 4.50, 40);

Insert multiple rows (batch)

Efficient for loading initial data
Readable when kept small
Use stable ordering and spacing

INSERT INTO flowers (name, color, price, stock)
VALUES
  ('Daisy', 'White', 1.50, 60),
  ('Orchid', 'Purple', 5.99, 20),
  ('Carnation', 'Pink', 2.25, 55);

Select and inspect after insert

Confirm new rows exist
Use ORDER BY for stable review
Check price and stock values

SELECT flower_id, name, color, price, stock
FROM flowers
ORDER BY flower_id;

Insert from SELECT (same table, copy pattern)

Insert rows produced by a query
Useful for copying and transforming
Keep it controlled with WHERE

-- Copy all white flowers as "Ivory" variants
INSERT INTO flowers (name, color, price, stock)
SELECT name || ' (Ivory)', 'Ivory', price, stock
FROM flowers
WHERE color = 'White';

Insert naming conventions

Use flower_id for primary key
Keep column names descriptive
Avoid reserved words
Use underscores consistently

-- Good
flower_id
created_at
price_cents

-- Risky
id
date
order

Common insert errors and mitigation

Error pattern	Cause	Mitigation
Column count mismatch	Values do not align	List columns; match order and count
Constraint failure	NOT NULL or UNIQUE	Insert required fields; validate inputs
Type mismatch	Wrong value type	Use correct types; cast if needed
Primary key conflict	Duplicate id	Use auto increment or verify ids first

Part 2: Update

Goal: modify existing rows using UPDATE and SET safely.

Agenda (Update)

Concept: change rows
Primary key in WHERE
Diagram: row edited
Syntax: UPDATE SET
Update multiple columns
Update with math
Preview with SELECT
Confirm with SELECT
Naming conventions
Common errors and mitigation

Primary key in WHERE (update safely)

Use primary key to target one row
Without WHERE, UPDATE can change the whole table
Preview with SELECT using the same WHERE

-- Preview
SELECT flower_id, name, price
FROM flowers
WHERE flower_id = 101;

-- Update (same filter)
UPDATE flowers
SET price = 4.25
WHERE flower_id = 101;

Diagram: UPDATE edits a value

Update one column

Use UPDATE and SET
WHERE targets the row
Prefer primary key filter

UPDATE flowers
SET price = 4.25
WHERE flower_id = 101;

Update multiple columns

Set more than one value
Keep alignment readable
Still one statement

UPDATE flowers
SET price = 4.25,
    color = 'Deep Red'
WHERE flower_id = 101;

Update with math (bulk update)

Useful for controlled price changes
Always include WHERE for the intended subset
Preview with SELECT first

-- Preview
SELECT flower_id, name, price
FROM flowers
WHERE color = 'Red';

-- Change
UPDATE flowers
SET price = price * 1.10
WHERE color = 'Red';

Update stock (increment or decrement)

Stock changes are common
Use + and - carefully
Confirm after update

-- Receive new inventory shipment
UPDATE flowers
SET stock = stock + 25
WHERE flower_id = 103;

-- Correct an overcount
UPDATE flowers
SET stock = stock - 5
WHERE flower_id = 103;

Select to confirm update

Confirm the row matches expectation
Confirm you changed only what you intended
Use the same WHERE as the update

SELECT flower_id, name, color, price, stock
FROM flowers
WHERE flower_id = 103;

Update naming conventions

Use stable column names
Use _id for keys
Prefer clear numeric names
Keep it consistent across tables

-- Good
flower_id
price
stock

-- Risky
val
num
data

Common update errors and mitigation

Error pattern	Cause	Mitigation
Updated too many rows	Missing WHERE	SELECT first; use WHERE always
No rows updated	Wrong filter	Confirm key values before updating
Unexpected price math	Wrong multiplier	Preview with SELECT; test on one row first
Invalid value	Type or constraint issue	Validate inputs; keep domain rules simple

Part 3: Delete

Goal: remove rows safely using DELETE and WHERE.

Agenda (Delete)

Concept: remove rows
Primary key in WHERE
Diagram: row removed
Preview with SELECT
DELETE ... WHERE
Delete a subset (by rule)
Confirm after delete
Soft delete (concept)
Naming conventions
Common errors and mitigation

Primary key in WHERE (delete safely)

Delete should target a specific row when possible
Primary key gives precision
Preview first using the same WHERE

-- Preview
SELECT *
FROM flowers
WHERE flower_id = 102;

-- Delete (same filter)
DELETE FROM flowers
WHERE flower_id = 102;

Diagram: DELETE removes a row

Preview delete with SELECT

Preview the exact set of rows
Confirm the key
Do not delete blind

SELECT flower_id, name, color, price
FROM flowers
WHERE flower_id = 102;

Delete one row ⚠️

⚠️ Deletes data
Always use WHERE
Prefer primary key filter
Confirm after delete

DELETE FROM flowers
WHERE flower_id = 102;

Delete a subset (rule based)

Sometimes you remove a group of rows
Use a careful WHERE rule
Preview with SELECT first

-- Preview candidates
SELECT flower_id, name, stock
FROM flowers
WHERE stock = 0;

-- Delete after review
DELETE FROM flowers
WHERE stock = 0;

Confirm after delete

Confirm the row is gone
Confirm no other rows were removed
Use a stable ORDER BY

SELECT flower_id, name, color, price, stock
FROM flowers
ORDER BY flower_id;

Soft delete pattern (concept)

Sometimes we mark rows instead of removing them
Common approach: is_active or deleted_at
Queries then filter out inactive rows
Safer for audit and recovery

This is a common real world compromise between safety and simplicity.

-- Concept if column exists:
-- UPDATE flowers
-- SET is_active = 0
-- WHERE flower_id = 102;

-- Query habit:
-- WHERE is_active = 1

Delete naming conventions

Use primary key columns in filters
Use stable, narrow WHERE clauses
Avoid ambiguous text matches
Keep identifiers predictable

-- Good filters
WHERE flower_id = 102
WHERE stock = 0

-- Risky filters
WHERE name = 'Rose'

Common delete errors and mitigation

Error pattern	Cause	Mitigation
Deleted too many rows	Missing WHERE	SELECT first; use WHERE always
Deleted the wrong rows	Wrong filter	Preview with SELECT and confirm keys
Cannot recover	No backups / no soft delete	Use backups; consider soft delete
Deleted nothing	Filter did not match	Verify row exists before deleting

Module 2 recap

INSERT adds new rows
UPDATE modifies existing rows
DELETE removes rows
Primary key identifies a row
Auto increment avoids manual id errors
SELECT + WHERE is the safety habit

-- Safe habit (repeat it)
SELECT ... WHERE ...;   -- preview
INSERT / UPDATE / DELETE ...;
SELECT ... WHERE ...;   -- confirm

Module 3

SQL Queries: SELECT, FROM, WHERE, GROUP BY, HAVING

SELECT · FROM · WHERE · GROUP · BY · HAVING

Module goal

Read data correctly
Understand query meaning
Separate rows from groups
Avoid logical mistakes

Domain table: bicycle_orders

Single table only
Each row is one order
bike_type creates groups
quantity supports aggregation

bicycle_orders(
  order_id   INTEGER PRIMARY KEY,
  bike_type  TEXT,
  quantity   INTEGER
);

Sample data

order_id	bike_type	quantity
1	Road	2
2	Road	1
3	Road	3
4	Mountain	4
5	Mountain	2
6	Hybrid	6
7	Hybrid	1
8	BMX	5
9	BMX	2

Rows come first

Query pipeline (concept)

Clause roles

Clause	Role
FROM	Select table
WHERE	Filter rows
GROUP BY	Create groups
HAVING	Filter groups
SELECT	Display result

SELECT and FROM

FROM chooses scope
SELECT chooses columns
No filtering yet

SELECT order_id, bike_type, quantity
FROM bicycle_orders;

SELECT *

SELECT *
FROM bicycle_orders;

Select intentionally

WHERE clause

Filters rows
Before grouping
No aggregates allowed

Concept

Keep only large orders

SELECT *
FROM bicycle_orders
WHERE quantity > 3;

SELECT *
FROM bicycle_orders
WHERE bike_type = 'Road';

SELECT *
FROM bicycle_orders
WHERE bike_type = 'BMX'
  AND quantity >= 2;

WHERE filters rows

GROUP BY concept

Rows collapse into groups
One row per group
Requires aggregates

Rows before grouping

bike_type	quantity
Road	2
Road	1
Road	3

Groups formed

bike_type	values
Road	2, 1, 3
Mountain	4, 2
Hybrid	6, 1
BMX	5, 2

SELECT bike_type, COUNT(*) AS total_orders
FROM bicycle_orders
GROUP BY bike_type;

SELECT bike_type, SUM(quantity) AS total_quantity
FROM bicycle_orders
GROUP BY bike_type;

bike_type	total_quantity
Road	6
Mountain	6
Hybrid	7
BMX	7

Groups summarize rows

HAVING concept

Filters groups
Uses aggregates
After GROUP BY

SELECT bike_type, SUM(quantity) AS total_quantity
FROM bicycle_orders
GROUP BY bike_type
HAVING SUM(quantity) > 6;

bike_type	total_quantity
Hybrid	7
BMX	7

HAVING filters groups

WHERE	HAVING
Row filter	Group filter
No aggregates	Uses aggregates
Before grouping	After grouping

SELECT bike_type, SUM(quantity) AS total_quantity
FROM bicycle_orders
WHERE quantity >= 2
GROUP BY bike_type
HAVING SUM(quantity) > 5;

Start with bicycle_orders
Keep rows with quantity ≥ 2
Group by bike_type
Keep groups with total > 5
Display result

Common mistake: WHERE with SUM

WHERE works on rows
SUM works on groups
This combination is invalid

-- Incorrect
SELECT bike_type, SUM(quantity)
FROM bicycle_orders
WHERE SUM(quantity) > 5
GROUP BY bike_type;

Fix: use HAVING

HAVING filters groups
Aggregates belong here
Runs after GROUP BY

-- Correct
SELECT bike_type, SUM(quantity)
FROM bicycle_orders
GROUP BY bike_type
HAVING SUM(quantity) > 5;

Common mistake: missing GROUP BY

Mixing aggregates and columns
DBMS cannot infer grouping
Leads to errors or ambiguity

-- Incorrect
SELECT bike_type, SUM(quantity)
FROM bicycle_orders;

Fix: group non aggregates

Every non aggregate column
Must appear in GROUP BY
This defines the grouping key

-- Correct
SELECT bike_type, SUM(quantity)
FROM bicycle_orders
GROUP BY bike_type;

Practice reading queries

Read top to bottom
Translate to plain language
Do not rush to execution

SELECT bike_type, COUNT(*)
FROM bicycle_orders
GROUP BY bike_type;

Practice predicting output

Look at input rows
Form groups mentally
Compute results before running

SELECT bike_type, SUM(quantity)
FROM bicycle_orders
GROUP BY bike_type;

Practice drawing tables

Sketch rows first
Then sketch groups
Visual thinking reduces errors

Road      → 2 + 1 + 3 = 6
Mountain  → 4 + 2     = 6
Hybrid    → 6 + 1     = 7
BMX       → 5 + 2     = 7

Rows → Groups → Filter

WHERE filters rows
GROUP BY forms groups
HAVING filters groups

SELECT bike_type, SUM(quantity)
FROM bicycle_orders
WHERE quantity >= 2
GROUP BY bike_type
HAVING SUM(quantity) > 5;

SQL is declarative

You state what you want
Not how to compute it

-- You describe the result,
-- not the algorithm.

You describe results

Focus on output shape
Columns and rows matter

SELECT bike_type, COUNT(*)
FROM bicycle_orders
GROUP BY bike_type;

Clarity

Readable queries survive
Explicit is better than clever

-- Prefer this:
SELECT bike_type, SUM(quantity)
FROM bicycle_orders
GROUP BY bike_type;

Readable queries

Others must maintain this
You will revisit it later

-- Clear structure
-- Predictable formatting
-- Minimal surprises

Module 3 complete

Rows vs groups understood
WHERE vs HAVING clear
Query flow internalized

SELECT → WHERE → GROUP BY → HAVING
A complete mental model

Module 4

PostgreSQL Query Refinement

ORDER · DISTINCT · AS · ALL · LIMIT · OFFSET

Module goal

Build a retail schema
Practice query refinement clauses
Compare PostgreSQL to SQLite where it matters
Keep output predictable and readable

-- This module adds "shape controls" to SELECT:
-- DISTINCT, AS, ORDER BY, LIMIT, OFFSET, ALL

Retail schema map

Create the tables first

Start with a small, clear schema
Primary keys: stable identifiers
Foreign keys: enforce valid references
Then we refine output using Module 4 clauses

-- Recommended order:
-- 1) customers
-- 2) products
-- 3) orders (FK -> customers)
-- 4) order_items (FK -> orders/products)

CREATE TABLE customers

One row per customer
City becomes useful for DISTINCT + ORDER BY
Use SERIAL for demo simplicity

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  name        TEXT NOT NULL,
  city        TEXT NOT NULL
);

CREATE TABLE products

One row per product
Category supports DISTINCT
Price supports ORDER BY

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name       TEXT NOT NULL,
  category   TEXT NOT NULL,
  price      NUMERIC(10,2) NOT NULL
);

CREATE TABLE orders

One row per order
Date supports ORDER BY + LIMIT
Total supports ORDER BY

CREATE TABLE orders (
  order_id      SERIAL PRIMARY KEY,
  customer_id   INTEGER NOT NULL REFERENCES customers(customer_id),
  order_date    DATE NOT NULL,
  total_amount  NUMERIC(10,2) NOT NULL
);

CREATE TABLE order_items

Stores quantities per order
Composite key prevents duplicates
We will query single tables only in this module

CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders(order_id),
  product_id INTEGER NOT NULL REFERENCES products(product_id),
  quantity   INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

Insert small sample data

Data: 3 to 6 rows per table
Enough to see duplicates + sorting
Keep values realistic

INSERT INTO customers(name, city) VALUES
('Ava', 'Stockton'),
('Tom','Sacramento'),
('Mia', 'Stockton');

INSERT INTO products(name, category, price) VALUES
('USB-C Cable', 'Accessories', 9.99),
('Laptop Stand','Accessories', 29.99),
('Notebook',    'Stationery',  3.49);

INSERT INTO orders(customer_id, order_date, total_amount) VALUES
(1, DATE '2026-02-01', 39.98),
(2, DATE '2026-02-02',  3.49),
(1, DATE '2026-02-03',  9.99);

Comparison: IDs and types

Topic	SQLite	PostgreSQL
Auto increment id	`INTEGER PRIMARY KEY AUTOINCREMENT`	`SERIAL` (demo) or `GENERATED AS IDENTITY`
Money type	Often stored as `REAL` or cents as `INTEGER`	`NUMERIC(10,2)` common
Date type	Stored as TEXT/INTEGER/REAL by convention	`DATE` is native

DISTINCT concept

DISTINCT: unique cities

One column = unique values in that column
Common for dropdown filters

SELECT DISTINCT city
FROM customers
ORDER BY city;

DISTINCT: multiple columns

Uniqueness applies to the full row (tuple)
Useful for “unique pairs”

SELECT DISTINCT city, name
FROM customers
ORDER BY city, name;

DISTINCT: unique product categories

Single table example (no relationships needed)
Great for filters and admin menus

SELECT DISTINCT category
FROM products
ORDER BY category;

Comparison: DISTINCT behavior

Item	SQLite	PostgreSQL
DISTINCT meaning	Removes duplicate result rows	Removes duplicate result rows
Interaction with ORDER BY	Works; keep ORDER BY explicit	Works; keep ORDER BY explicit
Good habit	Use for unique labels, not as a “fix”	Use for unique labels, not as a “fix”

AS concept

AS: rename columns

Alias improves readability
Especially in wide SELECT output

SELECT
  order_id,
  total_amount AS order_total
FROM orders;

AS: computed columns (single table)

Compute a derived value
Example: estimated tax at 8%

SELECT
  order_id,
  total_amount,
  (total_amount * 0.08) AS est_tax,
  (total_amount * 1.08) AS total_with_tax
FROM orders
ORDER BY order_id;

AS + ORDER BY

Order by alias for clarity
Prefer explicit ORDER BY in reports

SELECT
  order_id,
  total_amount AS total
FROM orders
ORDER BY total DESC;

Comparison: aliases

Topic	SQLite	PostgreSQL
Basic alias	`expr AS alias`	`expr AS alias`
Quoting alias with spaces	`AS "Order Total"`	`AS "Order Total"`
Best practice	snake_case aliases	snake_case aliases

ORDER BY concept

ORDER BY ascending

Ascending is default
Good for price lists

SELECT name, price
FROM products
ORDER BY price;

ORDER BY descending

Use DESC for “top spenders / biggest orders”
Common in dashboards

SELECT order_id, total_amount
FROM orders
ORDER BY total_amount DESC;

ORDER BY multiple columns

Sort by city first, then by name
Produces stable, readable lists

SELECT city, name
FROM customers
ORDER BY city ASC, name ASC;

ORDER BY + LIMIT habit

Top N requires ORDER BY
Without ORDER BY, “top” is meaningless

-- Top 3 most expensive products
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

Comparison: ordering NULLs

Goal	SQLite	PostgreSQL
Put NULLs last	Workaround: `ORDER BY (col IS NULL), col`	Supports `NULLS LAST` / `NULLS FIRST`
Put NULLs first	Workaround: `ORDER BY (col IS NOT NULL), col`	`ORDER BY col NULLS FIRST`

LIMIT concept

LIMIT: first rows

Quick sample of a table
Use ORDER BY if you care which rows

SELECT *
FROM customers
LIMIT 2;

LIMIT: latest orders

Latest requires date ordering
Classic audit query

SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 10;

PostgreSQL: LIMIT ALL

LIMIT ALL means “no limit”
Equivalent to omitting LIMIT
Mostly seen in generated SQL

-- PostgreSQL
SELECT *
FROM products
LIMIT ALL;  -- same as no LIMIT

Comparison: LIMIT

Pattern	SQLite	PostgreSQL
Return first N rows	`LIMIT 10`	`LIMIT 10`
No limit keyword	Omit LIMIT	`LIMIT ALL` or omit LIMIT

OFFSET concept

OFFSET: skip rows

Skipping without LIMIT returns “the rest”
Always pair with ORDER BY

SELECT order_id, order_date
FROM orders
ORDER BY order_date DESC
OFFSET 10;

LIMIT + OFFSET: page 2

Page size = 10
Page 2 = skip 10, take 10

SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 10 OFFSET 10;

Pagination: keyset alternative

OFFSET can get slow on large tables
Keyset uses “last seen” value
Stable for “load more” patterns

-- Suppose last page ended at order_date = '2026-02-01'
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date < DATE '2026-02-01'
ORDER BY order_date DESC
LIMIT 10;

Comparison: OFFSET

Topic	SQLite	PostgreSQL
Syntax	`LIMIT n OFFSET m`	`LIMIT n OFFSET m`
Performance note	Large OFFSET may still scan many rows	Large OFFSET may still scan many rows

ALL concept

ALL: pricier than all Accessories

Single table + subquery
Reads like “greater than the maximum”

SELECT name, price
FROM products
WHERE price > ALL (
  SELECT price
  FROM products
  WHERE category = 'Accessories'
)
ORDER BY price;

ALL: orders bigger than all orders of a customer

Single table + subquery
Good for “outlier spend” checks

SELECT order_id, total_amount
FROM orders
WHERE total_amount > ALL (
  SELECT total_amount
  FROM orders
  WHERE customer_id = 1
)
ORDER BY total_amount DESC;

Rewrite ALL using MAX

Often clearer for teams
Same meaning as the ALL example

SELECT name, price
FROM products
WHERE price > (
  SELECT MAX(price)
  FROM products
  WHERE category = 'Accessories'
)
ORDER BY price;

Comparison: ALL support

Feature	SQLite	PostgreSQL
`ALL` with subqueries	Not commonly supported	Supported
Portable alternative	`MAX()`, `MIN()`, or a subquery with aggregates	`ALL` or `MAX()`/`MIN()`

DISTINCT + ORDER BY: clean labels

Common admin pattern
Unique values + sorted for humans

SELECT DISTINCT category
FROM products
ORDER BY category;

AS + computed + ORDER BY (single table)

Sort by derived metric
Example: total with tax

SELECT
  order_id,
  total_amount,
  (total_amount * 1.08) AS total_with_tax
FROM orders
ORDER BY total_with_tax DESC
LIMIT 10;

Refinement ladder

Practice 1: unique cities

Return unique customer cities
Sort alphabetically
One row per city

SELECT DISTINCT city
FROM customers
ORDER BY city;

Practice 2: top 5 orders

Show the 5 largest orders
Rename total_amount to total
Sort descending

SELECT order_id, total_amount AS total
FROM orders
ORDER BY total DESC
LIMIT 5;

Pagination in a UI

Clause summary

Clause	Purpose	Retail example
`DISTINCT`	Remove duplicates	Unique cities / categories
`AS`	Rename for clarity	`total_amount AS total`
`ORDER BY`	Sort results	Most recent orders first
`LIMIT`	Return first N rows	Top 10 by value
`OFFSET`	Skip rows	Pagination
`ALL`	Compare to every value in a set	Price above all in a category

Summary

DISTINCT: unique rows in output
AS: readable names
ORDER BY: define “top” and “latest”
LIMIT: cap the result size
OFFSET: page through results
ALL: set comparison (PostgreSQL)

SELECT DISTINCT category AS cat
FROM products
ORDER BY cat
LIMIT 10 OFFSET 0;

Module 5

PostgreSQL Joins

JOIN · INNER · LEFT · RIGHT · FULL · OUTER

Module goal

Build a small bicycle rental schema
Use keys to combine data across tables
Practice INNER, LEFT, RIGHT, FULL OUTER joins
Learn common mistakes: missing or wrong ON

-- The habit:
-- FROM + JOIN ... ON ...
-- Choose join type based on "who must appear"
-- Always verify row counts

Bicycle rental schema map

Create the tables first

Keep keys explicit and stable
Foreign keys express relationships
Joins depend on matching keys
Start small; add complexity later

-- Recommended order:
-- 1) riders
-- 2) bikes
-- 3) stations
-- 4) rentals (FK -> riders/bikes/stations)
-- 5) returns (optional FK -> rentals)

CREATE TABLE riders

One row per rider
City supports grouping later
Email is realistic for uniqueness

CREATE TABLE riders (
  rider_id  SERIAL PRIMARY KEY,
  name      TEXT NOT NULL,
  city      TEXT NOT NULL,
  email     TEXT UNIQUE
);

CREATE TABLE bikes

One row per bike
Status helps show “unrented bikes”
Type supports reporting

CREATE TABLE bikes (
  bike_id   SERIAL PRIMARY KEY,
  bike_type TEXT NOT NULL,     -- road, city, e-bike
  status    TEXT NOT NULL      -- available, maintenance
);

CREATE TABLE stations

Pickup and dropoff locations
Neighborhood supports filters

CREATE TABLE stations (
  station_id   SERIAL PRIMARY KEY,
  name         TEXT NOT NULL,
  neighborhood TEXT NOT NULL
);

CREATE TABLE rentals

Fact table: each rental event
Contains multiple foreign keys
Return time can be NULL (still riding)

CREATE TABLE rentals (
  rental_id           SERIAL PRIMARY KEY,
  rider_id            INTEGER NOT NULL REFERENCES riders(rider_id),
  bike_id             INTEGER NOT NULL REFERENCES bikes(bike_id),
  pickup_station_id   INTEGER NOT NULL REFERENCES stations(station_id),
  pickup_ts           TIMESTAMP NOT NULL,
  dropoff_station_id  INTEGER REFERENCES stations(station_id),
  dropoff_ts          TIMESTAMP
);

Sample data

Include “missing” relationships on purpose
At least one rider with no rentals
At least one bike never rented
At least one active rental (no dropoff yet)

INSERT INTO riders(name, city, email) VALUES
('Ava',  'Stockton',    'ava@example.com'),
('Tom', 'Sacramento',  'tom@example.com'),
('Mia',  'Stockton',    'mia@example.com'),
('Liam', 'Lodi',        'liam@example.com');  -- no rentals

INSERT INTO bikes(bike_type, status) VALUES
('city', 'available'),
('road', 'available'),
('e-bike','maintenance'),
('city', 'available');  -- never rented

INSERT INTO stations(name, neighborhood) VALUES
('Downtown Dock', 'Downtown'),
('Campus Rack',   'University'),
('River Trail',   'Waterfront');

INSERT INTO rentals(rider_id, bike_id, pickup_station_id, pickup_ts, dropoff_station_id, dropoff_ts) VALUES
(1, 1, 1, TIMESTAMP '2026-02-10 09:00', 2, TIMESTAMP '2026-02-10 09:25'),
(2, 2, 2, TIMESTAMP '2026-02-10 10:00', 1, TIMESTAMP '2026-02-10 10:18'),
(1, 2, 1, TIMESTAMP '2026-02-11 08:10', NULL, NULL); -- active ride

Join concept

JOIN syntax (base pattern)

JOIN without a keyword means INNER JOIN
Always include ON with the key match
Use table aliases for readability

SELECT
  r.rental_id,
  rd.name AS rider_name,
  r.pickup_ts
FROM rentals r
JOIN riders rd
  ON r.rider_id = rd.rider_id
ORDER BY r.rental_id;

Join types: what stays in the output

Join type	Keep rows from left table?	Keep rows from right table?	Typical question
`INNER JOIN`	Only matched	Only matched	“Show rentals with rider info”
`LEFT JOIN`	All left rows	Matched when possible	“Show all riders, even with no rentals”
`RIGHT JOIN`	Matched when possible	All right rows	“Show all bikes, even never rented” (if bikes is right)
`FULL OUTER JOIN`	All left rows	All right rows	“Show everything; highlight gaps”

INNER JOIN concept

INNER JOIN: rentals with rider names

Most common join
Only matched rows appear
Good default for “fact + dimension”

SELECT
  r.rental_id,
  rd.name AS rider,
  rd.city,
  r.pickup_ts,
  r.dropoff_ts
FROM rentals r
INNER JOIN riders rd
  ON r.rider_id = rd.rider_id
ORDER BY r.pickup_ts;

Multiple joins: rider + bike + pickup station

Join is composable
Each join adds columns
Keep ON conditions close to the join

SELECT
  r.rental_id,
  rd.name     AS rider,
  b.bike_type AS bike,
  s.name      AS pickup_station,
  r.pickup_ts
FROM rentals r
JOIN riders   rd ON r.rider_id = rd.rider_id
JOIN bikes    b  ON r.bike_id  = b.bike_id
JOIN stations s  ON r.pickup_station_id = s.station_id
ORDER BY r.rental_id;

INNER JOIN + NULLs: active rentals (no dropoff yet)

NULL means “unknown / not recorded”
Filter active rentals with IS NULL
Join still works normally

SELECT
  r.rental_id,
  rd.name AS rider,
  b.bike_type,
  r.pickup_ts
FROM rentals r
JOIN riders rd ON r.rider_id = rd.rider_id
JOIN bikes  b  ON r.bike_id  = b.bike_id
WHERE r.dropoff_ts IS NULL
ORDER BY r.pickup_ts;

LEFT JOIN concept

LEFT JOIN: all riders, include rentals if any

Use when the left table “must” appear
Good for audits and completeness checks
NULLs on the right mean “no match”

SELECT
  rd.rider_id,
  rd.name,
  r.rental_id,
  r.pickup_ts
FROM riders rd
LEFT JOIN rentals r
  ON r.rider_id = rd.rider_id
ORDER BY rd.rider_id, r.pickup_ts;

Quality check: riders with zero rentals

Start from riders
LEFT JOIN rentals
Keep rows where the right side is NULL

SELECT
  rd.rider_id,
  rd.name,
  rd.city
FROM riders rd
LEFT JOIN rentals r
  ON r.rider_id = rd.rider_id
WHERE r.rental_id IS NULL
ORDER BY rd.rider_id;

RIGHT JOIN concept

RIGHT JOIN: show all bikes, rentals if any

Less common than LEFT JOIN
Same idea: choose which side must appear
Can be rewritten as LEFT JOIN by swapping tables

SELECT
  b.bike_id,
  b.bike_type,
  r.rental_id,
  r.pickup_ts
FROM rentals r
RIGHT JOIN bikes b
  ON r.bike_id = b.bike_id
ORDER BY b.bike_id, r.pickup_ts;

Rewrite RIGHT JOIN as LEFT JOIN (team habit)

Many teams standardize on LEFT JOIN
Same results, often easier to read
Keep “must appear” table on the left

SELECT
  b.bike_id,
  b.bike_type,
  r.rental_id,
  r.pickup_ts
FROM bikes b
LEFT JOIN rentals r
  ON r.bike_id = b.bike_id
ORDER BY b.bike_id, r.pickup_ts;

FULL OUTER JOIN concept

FULL OUTER JOIN: show everything, highlight gaps

Audit tool: find missing links on either side
PostgreSQL supports FULL OUTER JOIN
NULLs indicate unmatched rows

SELECT
  rd.rider_id,
  rd.name,
  r.rental_id,
  r.pickup_ts
FROM riders rd
FULL OUTER JOIN rentals r
  ON r.rider_id = rd.rider_id
ORDER BY rd.rider_id NULLS LAST, r.rental_id NULLS LAST;

OUTER keyword

OUTER is optional syntax
LEFT OUTER JOIN = LEFT JOIN
FULL OUTER JOIN is common for clarity

-- Equivalent pairs:
-- LEFT JOIN         == LEFT OUTER JOIN
-- RIGHT JOIN        == RIGHT OUTER JOIN
-- FULL OUTER JOIN   == FULL JOIN (commonly written with OUTER)

Comparison: join support

Feature	SQLite	PostgreSQL
INNER JOIN	Supported	Supported
LEFT JOIN	Supported	Supported
RIGHT JOIN	Not supported in many SQLite versions	Supported
FULL OUTER JOIN	Not supported directly	Supported
Portable workaround	Use LEFT JOIN + UNION (carefully)	Use RIGHT/FULL directly

SQLite workaround idea: emulate FULL OUTER JOIN

Use two LEFT JOIN queries
Combine with UNION
Be careful to avoid duplicates

-- Conceptual pattern (portable-ish):
-- 1) left side keeps all riders
-- 2) second query keeps rentals with no rider match

SELECT rd.rider_id, rd.name, r.rental_id
FROM riders rd
LEFT JOIN rentals r ON r.rider_id = rd.rider_id

UNION

SELECT rd.rider_id, rd.name, r.rental_id
FROM rentals r
LEFT JOIN riders rd ON r.rider_id = rd.rider_id
WHERE rd.rider_id IS NULL;

Common mistake: missing or wrong ON

If the join condition is missing or incorrect, rows multiply
This is how dashboards get “too many rows”
Good habit: sanity check counts

-- Bad idea (cross join effect):
-- SELECT ... FROM rentals r JOIN riders rd;

-- Good:
SELECT COUNT(*) FROM rentals;
SELECT COUNT(*) FROM rentals r JOIN riders rd
  ON r.rider_id = rd.rider_id;

Sanity checks: row counts

Start from the fact table count (rentals)
INNER JOIN should not exceed fact count (usually)
LEFT JOIN may increase if one-to-many relationships exist

-- Fact table baseline:
SELECT COUNT(*) AS rental_rows FROM rentals;

-- Expected: same as rentals (many-to-one)
SELECT COUNT(*) AS joined_rows
FROM rentals r
JOIN riders rd ON r.rider_id = rd.rider_id;

Practice 1: rentals with rider + bike

Show rental id, rider name, bike type
Sort by pickup time
Use table aliases

SELECT
  r.rental_id,
  rd.name AS rider,
  b.bike_type,
  r.pickup_ts
FROM rentals r
JOIN riders rd ON r.rider_id = rd.rider_id
JOIN bikes  b  ON r.bike_id  = b.bike_id
ORDER BY r.pickup_ts;

Practice 2: riders who never rented

Start from riders
LEFT JOIN rentals
Filter where rental is NULL

SELECT rd.rider_id, rd.name
FROM riders rd
LEFT JOIN rentals r ON r.rider_id = rd.rider_id
WHERE r.rental_id IS NULL
ORDER BY rd.rider_id;

Practice 3: bikes never rented

Start from bikes
LEFT JOIN rentals
Keep bikes with no rental match

SELECT b.bike_id, b.bike_type, b.status
FROM bikes b
LEFT JOIN rentals r ON r.bike_id = b.bike_id
WHERE r.rental_id IS NULL
ORDER BY b.bike_id;

Practice 4: pickup and dropoff station names

Join stations twice
Dropoff can be NULL for active rentals
Use two different aliases

SELECT
  r.rental_id,
  sp.name AS pickup_station,
  sd.name AS dropoff_station,
  r.pickup_ts,
  r.dropoff_ts
FROM rentals r
JOIN stations sp ON r.pickup_station_id = sp.station_id
LEFT JOIN stations sd ON r.dropoff_station_id = sd.station_id
ORDER BY r.rental_id;

Join choice ladder

Cheat sheet

Need	Join to use	Bicycle example
Only matched rows	`INNER JOIN`	Rentals + riders
All riders, rentals if any	`LEFT JOIN`	Riders with optional rentals
All bikes, rentals if any	`RIGHT JOIN` (or swap + LEFT)	Bikes with optional rentals
Everything from both sides	`FULL OUTER JOIN`	Audit missing links

Summary

JOIN defaults to INNER JOIN
INNER: only matches
LEFT: keep all left rows (right may be NULL)
RIGHT: keep all right rows (left may be NULL)
FULL OUTER: keep all rows from both tables
Wrong or missing ON can multiply rows

-- A clean, common pattern:
SELECT rd.name, b.bike_type, r.pickup_ts
FROM rentals r
JOIN riders rd ON r.rider_id = rd.rider_id
JOIN bikes  b  ON r.bike_id  = b.bike_id
ORDER BY r.pickup_ts;

Module 6

Filtering with Subqueries & Patterns

EXISTS · IN · ANY · BETWEEN · LIKE · ESCAPE

Module goal

Run a small schema and seed data
Filter with IN, EXISTS, ANY
Filter ranges using BETWEEN
Filter text using LIKE and ESCAPE
Practice “before vs after” query improvements

-- Habit:
-- 1) Start with a base SELECT
-- 2) Add one WHERE filter
-- 3) Validate row counts (COUNT(*))

Setup: Create tables + insert data

Run these once so every example works.

CREATE TABLE riders

One row per rider
City supports filters
Email supports LIKE practice

CREATE TABLE riders (
  rider_id  SERIAL PRIMARY KEY,
  name      TEXT NOT NULL,
  city      TEXT NOT NULL,
  email     TEXT
);

CREATE TABLE bikes

One row per bike
Status enables extra filters

CREATE TABLE bikes (
  bike_id   SERIAL PRIMARY KEY,
  bike_type TEXT NOT NULL,
  status    TEXT NOT NULL
);

CREATE TABLE rentals

Fact table (events)
dropoff_ts can be NULL (active ride)

CREATE TABLE rentals (
  rental_id  SERIAL PRIMARY KEY,
  rider_id   INTEGER REFERENCES riders(rider_id),
  bike_id    INTEGER REFERENCES bikes(bike_id),
  pickup_ts  TIMESTAMP NOT NULL,
  dropoff_ts TIMESTAMP
);

INSERT sample data (riders, bikes, rentals)

INSERT INTO riders(name, city, email) VALUES
('Ava',  'Stockton',   'ava@example.com'),
('Tom', 'Sacramento', 'tom@example.com'),
('Mia',  'Stockton',   'mia@test.com'),
('Liam', 'Lodi',       'liam@sample.org'); -- intentionally different domain

INSERT INTO bikes(bike_type, status) VALUES
('city',  'available'),
('road',  'available'),
('e-bike','maintenance'),
('city',  'available'); -- will show “never rented” later

INSERT INTO rentals(rider_id, bike_id, pickup_ts, dropoff_ts) VALUES
(1, 1, TIMESTAMP '2026-02-10 09:00', TIMESTAMP '2026-02-10 09:25'),
(2, 2, TIMESTAMP '2026-02-11 10:00', TIMESTAMP '2026-02-11 10:18'),
(1, 2, TIMESTAMP '2026-02-12 08:10', NULL); -- active ride

Baseline query (start here)

Establish a baseline
Know expected row count
Then add filters

SELECT rider_id, name, city, email
FROM riders
ORDER BY rider_id;

IN concept

IN checks membership in a list (or subquery result)
Often replaces long OR chains
Good for “allowed set” rules

-- Pattern:
WHERE value IN (v1, v2, v3)

IN example: filter riders by city

Small fixed list
Readable
Easy to extend

SELECT name, city
FROM riders
WHERE city IN ('Stockton', 'Lodi')
ORDER BY name;

Before/After: OR chain → IN

Before: noisy OR chain
After: clean IN list
Team habit: prefer IN for enums

-- Before:
SELECT name, city
FROM riders
WHERE city = 'Stockton'
   OR city = 'Lodi'
   OR city = 'Sacramento';

-- After:
SELECT name, city
FROM riders
WHERE city IN ('Stockton','Lodi','Sacramento');

IN with subquery: riders who have rentals

Subquery returns a set of rider_ids
Outer query keeps members
This is membership logic

SELECT name
FROM riders
WHERE rider_id IN (
  SELECT rider_id
  FROM rentals
)
ORDER BY name;

IN notes

Situation	Use	Why
Short fixed list	`IN`	Clean and readable
Existence question	`EXISTS`	More explicit meaning
NULL involved	Be careful	Three-valued logic

EXISTS concept

EXISTS example: riders with at least one rental

Correlated subquery (uses r.rider_id)
Short-circuits after first match
Best match for “does it exist?” questions

SELECT r.name
FROM riders r
WHERE EXISTS (
  SELECT 1
  FROM rentals rt
  WHERE rt.rider_id = r.rider_id
)
ORDER BY r.name;

Before/After: IN subquery → EXISTS

Before: membership phrasing
After: existence phrasing
Habit: use EXISTS when the meaning is existence

-- Before:
SELECT name
FROM riders
WHERE rider_id IN (SELECT rider_id FROM rentals);

-- After:
SELECT r.name
FROM riders r
WHERE EXISTS (
  SELECT 1 FROM rentals rt
  WHERE rt.rider_id = r.rider_id
);

NOT EXISTS: riders with zero rentals

Audit pattern
Find missing relationships
Common in data quality checks

SELECT r.rider_id, r.name
FROM riders r
WHERE NOT EXISTS (
  SELECT 1
  FROM rentals rt
  WHERE rt.rider_id = r.rider_id
)
ORDER BY r.rider_id;

EXISTS tips

Tip	Why
Use `SELECT 1`	You only need existence
Keep correlation in WHERE	Readable logic
Pair with NOT EXISTS	Find missing links

ANY concept

Compare a value to any value from a set
More flexible than IN (supports <, >, =)
PostgreSQL-focused tool

-- Pattern:
value OP ANY (subquery)

ANY equality: similar to IN

= ANY works like membership
Teach set-thinking
Prefer IN for readability when possible

SELECT r.name
FROM riders r
WHERE r.rider_id = ANY (
  SELECT rider_id
  FROM rentals
);

ANY with comparisons: threshold logic

Example: “greater than any rider_id seen in rentals”
Shows flexibility beyond IN
Know what the set represents

SELECT r.rider_id, r.name
FROM riders r
WHERE r.rider_id > ANY (
  SELECT rt.rider_id
  FROM rentals rt
)
ORDER BY r.rider_id;

IN vs ANY

Need	Use	Reason
Equality membership	`IN`	Most readable
Flexible comparisons	`ANY`	Supports <, >, =

BETWEEN concept

Inclusive range: low and high are included
Good for dates and numeric ranges
Cleaner than >= AND <=< /li>

-- Pattern:
WHERE value BETWEEN low AND high

BETWEEN as a range (inclusive)

BETWEEN example: rentals in a date window

Use explicit timestamps for clarity
Common for daily/weekly reports
Sort to verify results

SELECT rental_id, pickup_ts
FROM rentals
WHERE pickup_ts BETWEEN
  TIMESTAMP '2026-02-10 00:00'
  AND
  TIMESTAMP '2026-02-11 23:59'
ORDER BY pickup_ts;

Before/After: >= and <= → BETWEEN

Before: verbose comparisons
After: compact BETWEEN
Same meaning (inclusive)

-- Before:
SELECT rental_id
FROM rentals
WHERE pickup_ts >= TIMESTAMP '2026-02-10 00:00'
  AND pickup_ts <= TIMESTAMP '2026-02-11 23:59';

-- After:
SELECT rental_id
FROM rentals
WHERE pickup_ts BETWEEN
  TIMESTAMP '2026-02-10 00:00'
  AND
  TIMESTAMP '2026-02-11 23:59';

BETWEEN numeric example: bike_id range

Simple numeric range filter
Useful for demos and sanity checks

SELECT bike_id, bike_type, status
FROM bikes
WHERE bike_id BETWEEN 1 AND 3
ORDER BY bike_id;

LIKE wildcards

Symbol	Meaning	Example
`%`	0 or more characters	`'S%'`
`_`	Exactly one character	`'____'`

LIKE concept

Pattern matching for text
Use prefix/suffix/contains patterns
Leading % can be slower (no prefix anchor)

-- Common patterns:
'prefix%'
'%contains%'
'%suffix'
'____'  -- exact length

LIKE example: city starts with 'S'

Prefix pattern: 'S%'
Good for “starts with” filters

SELECT name, city
FROM riders
WHERE city LIKE 'S%'
ORDER BY city, name;

LIKE example: email contains 'example'

Contains pattern: '%example%'
Useful for quick searches

SELECT rider_id, name, email
FROM riders
WHERE email LIKE '%example%'
ORDER BY rider_id;

PostgreSQL note: ILIKE (case-insensitive)

ILIKE is PostgreSQL-only
Case-insensitive pattern matching
Useful for user-entered searches

SELECT name, city
FROM riders
WHERE city ILIKE 's%';

ESCAPE concept

% and _ are wildcards in LIKE
ESCAPE defines a special character to “escape” wildcards
Then \% means literal percent sign

-- Pattern:
LIKE '%\%%' ESCAPE '\'

ESCAPE example: match a literal percent sign

Needed when data contains wildcard characters
Without ESCAPE, % means “anything”
With ESCAPE, you control literal matching

SELECT *
FROM riders
WHERE email LIKE '%\%%' ESCAPE '\';

Before/After: broken pattern → ESCAPE

Before: intends literal %, but matches too broadly
After: ESCAPE makes % literal

-- Before (not literal):
SELECT * FROM riders
WHERE email LIKE '%%%';  -- just means "anything"

-- After (literal % somewhere):
SELECT * FROM riders
WHERE email LIKE '%\%%' ESCAPE '\';

Combine filters: IN + LIKE

Combine conditions with AND
Keep each filter readable
Use parentheses for complex logic

SELECT name, city, email
FROM riders
WHERE city IN ('Stockton', 'Sacramento')
  AND email LIKE '%@example.com'
ORDER BY name;

Combine filters: EXISTS + BETWEEN

Find riders active in a time window
Existence + range is common in reporting

SELECT r.name
FROM riders r
WHERE EXISTS (
  SELECT 1
  FROM rentals rt
  WHERE rt.rider_id = r.rider_id
    AND rt.pickup_ts BETWEEN
        TIMESTAMP '2026-02-10 00:00'
        AND
        TIMESTAMP '2026-02-12 23:59'
)
ORDER BY r.name;

Common pitfall: NULL comparisons

NULL means “unknown / missing”
= NULL is not correct
Use IS NULL / IS NOT NULL

-- Active rentals (no dropoff yet):
SELECT rental_id, rider_id, pickup_ts
FROM rentals
WHERE dropoff_ts IS NULL;

Sanity checks: row counts

Use COUNT(*) before and after filters
Filtering should reduce rows
Helps catch logic mistakes early

SELECT COUNT(*) AS rider_rows
FROM riders;

SELECT COUNT(*) AS filtered_rows
FROM riders
WHERE city IN ('Stockton','Lodi');

Comparison: feature support

Feature	SQLite	PostgreSQL
IN	Supported	Supported
EXISTS	Supported	Supported
ANY	Limited / uncommon	Supported
BETWEEN	Supported	Supported
LIKE	Supported	Supported
ESCAPE	Varies by build / usage	Supported
ILIKE	No	Supported

Portable habits (works almost everywhere)

Core portable: IN, EXISTS, BETWEEN, LIKE
PostgreSQL extras: ANY, ILIKE
Keep timestamps explicit for portability

-- Portable core:
-- IN / EXISTS / BETWEEN / LIKE

Practice 1: riders who never rented

Use NOT EXISTS
Return rider_id + name
Sort by rider_id

SELECT r.rider_id, r.name
FROM riders r
WHERE NOT EXISTS (
  SELECT 1
  FROM rentals rt
  WHERE rt.rider_id = r.rider_id
)
ORDER BY r.rider_id;

Practice 2: bikes never rented (IN + NOT IN alternative)

Find bikes missing from rentals
Demonstrates “set difference” thinking
Note: NOT IN can be tricky with NULLs; consider NOT EXISTS in real systems

-- Simple approach (works here because rentals.bike_id is not NULL in our inserts):
SELECT b.bike_id, b.bike_type, b.status
FROM bikes b
WHERE b.bike_id NOT IN (
  SELECT rt.bike_id
  FROM rentals rt
)
ORDER BY b.bike_id;

Practice 3: riders with an active rental

EXISTS with extra condition
Active rental = dropoff_ts IS NULL
Return only rider names

SELECT r.name
FROM riders r
WHERE EXISTS (
  SELECT 1
  FROM rentals rt
  WHERE rt.rider_id = r.rider_id
    AND rt.dropoff_ts IS NULL
)
ORDER BY r.name;

Cheat sheet

Need	Use	Bicycle example
Membership in a set	`IN`	Riders in certain cities
Does a related row exist?	`EXISTS`	Riders with rentals
Flexible set comparison	`ANY`	Threshold comparisons
Range filter	`BETWEEN`	Rentals in a date window
Pattern match	`LIKE`	Emails containing “example”
Literal wildcard	`ESCAPE`	Find literal % in text

Summary

IN = membership in a list/set
EXISTS = presence of ≥ 1 related row
ANY = compare to any value in a set
BETWEEN = inclusive range
LIKE = pattern matching
ESCAPE = treat % and _ as literal

-- Clean, common pattern:
SELECT r.name
FROM riders r
WHERE EXISTS (
  SELECT 1
  FROM rentals rt
  WHERE rt.rider_id = r.rider_id
)
ORDER BY r.name;

Module 7

Joins & Set Operators

CROSS · ON · USING · UNION · INTERSECT · EXCEPT

Module goal

Run a small schema and seed data (same domain)
Join tables using ON and USING
Understand CROSS JOIN (Cartesian product)
Combine result sets with UNION, INTERSECT, EXCEPT
Practice “before vs after” query improvements

-- Habit:
-- 1) Start with a small SELECT
-- 2) Add one JOIN condition
-- 3) Validate row counts (COUNT(*))
-- 4) Only then add SET operators

Setup: Create tables + insert data

Run these once so every example works.

CREATE TABLE riders

One row per rider
City supports filters
Email supports unique lists

CREATE TABLE riders (
  rider_id  INT AUTO_INCREMENT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL,
  city      VARCHAR(100) NOT NULL,
  email     VARCHAR(255)
);

CREATE TABLE bikes

One row per bike
Status enables filters
bike_type supports grouping

CREATE TABLE bikes (
  bike_id    INT AUTO_INCREMENT PRIMARY KEY,
  bike_type  VARCHAR(50) NOT NULL,
  status     VARCHAR(50) NOT NULL
);

CREATE TABLE rentals

Fact table (events)
Links riders ↔ bikes
dropoff_ts can be NULL (active ride)

CREATE TABLE rentals (
  rental_id   INT AUTO_INCREMENT PRIMARY KEY,
  rider_id    INT,
  bike_id     INT,
  pickup_ts   DATETIME NOT NULL,
  dropoff_ts  DATETIME,
  FOREIGN KEY (rider_id) REFERENCES riders(rider_id),
  FOREIGN KEY (bike_id)  REFERENCES bikes(bike_id)
);

INSERT sample data (riders, bikes, rentals)

INSERT INTO riders(name, city, email) VALUES
('Ava',  'Stockton',   'ava@example.com'),
('Tom',  'Sacramento', 'tom@example.com'),
('Mia',  'Stockton',   'mia@test.com'),
('Liam', 'Lodi',       'liam@sample.org');

INSERT INTO bikes(bike_type, status) VALUES
('city',   'available'),
('road',   'available'),
('e-bike', 'maintenance'),
('city',   'available');

INSERT INTO rentals(rider_id, bike_id, pickup_ts, dropoff_ts) VALUES
(1, 1, '2026-02-10 09:00:00', '2026-02-10 09:25:00'),
(2, 2, '2026-02-11 10:00:00', '2026-02-11 10:18:00'),
(1, 2, '2026-02-12 08:10:00', NULL);

Baseline query (start here)

Establish a baseline
Know expected row counts
Then add joins and set operators

SELECT rider_id, name, city, email
FROM riders
ORDER BY rider_id;

CROSS JOIN concept

CROSS JOIN = every row with every row (Cartesian product)
Powerful, but easy to explode row counts
Use intentionally (small sets only)

-- Pattern:
FROM A
CROSS JOIN B

CROSS JOIN example: all rider × bike pairs

Think: every possible pairing
Result size grows quickly
Count results to stay safe

SELECT r.name, b.bike_type
FROM riders r
CROSS JOIN bikes b
ORDER BY r.name, b.bike_type;

Sanity check: expected row count

Row count should be: riders × bikes
Good habit before adding filters

SELECT
  (SELECT COUNT(*) FROM riders) AS rider_rows,
  (SELECT COUNT(*) FROM bikes)  AS bike_rows,
  (SELECT COUNT(*) FROM riders) * (SELECT COUNT(*) FROM bikes) AS expected_pairs;

JOIN ... ON concept

ON defines the matching rule between two tables
Most common join style
Works even when column names differ

-- Pattern:
FROM A
JOIN B
  ON A.key = B.key

ON example: attach rider names to rentals

Rentals is a fact table
Join adds human-readable fields
Classic reporting step

SELECT rt.rental_id, r.name, rt.pickup_ts, rt.dropoff_ts
FROM rentals rt
JOIN riders r
  ON rt.rider_id = r.rider_id
ORDER BY rt.rental_id;

ON example: attach bike info to rentals

Join rentals → bikes
Now each rental has bike_type and status
Add one join at a time

SELECT rt.rental_id, b.bike_type, b.status, rt.pickup_ts
FROM rentals rt
JOIN bikes b
  ON rt.bike_id = b.bike_id
ORDER BY rt.rental_id;

Two joins: rentals + riders + bikes

Common “fact + two dimensions” pattern
Each join adds meaning without changing the fact grain
Verify row counts stay stable

SELECT rt.rental_id, r.name, b.bike_type, rt.pickup_ts, rt.dropoff_ts
FROM rentals rt
JOIN riders r
  ON rt.rider_id = r.rider_id
JOIN bikes b
  ON rt.bike_id = b.bike_id
ORDER BY rt.rental_id;

Before/After: CROSS + WHERE → JOIN ... ON

Before: cross product then filter
After: explicit join rule
Prefer JOIN ... ON for clarity

-- Before:
SELECT rt.rental_id, r.name
FROM rentals rt
CROSS JOIN riders r
WHERE rt.rider_id = r.rider_id;

-- After:
SELECT rt.rental_id, r.name
FROM rentals rt
JOIN riders r
  ON rt.rider_id = r.rider_id;

JOIN ... USING concept

USING (col) is shorthand when both tables share the same column name
Returns that column once (clean output)
Good for “natural key” style schemas

-- Pattern:
FROM A
JOIN B USING (shared_key)

Setup for USING: a rider preferences table

Same key name: rider_id
Makes USING natural
Small table, easy to reason about

CREATE TABLE rider_prefs (
  rider_id  INT PRIMARY KEY,
  prefers   VARCHAR(50) NOT NULL,
  FOREIGN KEY (rider_id) REFERENCES riders(rider_id)
);

INSERT INTO rider_prefs(rider_id, prefers) VALUES
(1, 'e-bike'),
(2, 'road');

USING example: riders joined to their preferences

USING (rider_id) avoids repeating r.rider_id = p.rider_id
Cleaner output: one rider_id

SELECT rider_id, name, prefers
FROM riders
JOIN rider_prefs USING (rider_id)
ORDER BY rider_id;

Before/After: ON → USING

Before: explicit equality
After: compact USING
Use only when column names truly match

-- Before:
SELECT r.rider_id, r.name, p.prefers
FROM riders r
JOIN rider_prefs p
  ON r.rider_id = p.rider_id;

-- After:
SELECT rider_id, name, prefers
FROM riders
JOIN rider_prefs USING (rider_id);

ON vs USING

Need	Use	Reason
Different column names	`ON`	Most flexible
Same column name(s)	`USING`	Shorter + cleaner output
All combinations	`CROSS JOIN`	Intentional Cartesian product

Set operators concept

Set operators combine result sets (not tables)
Both queries must return the same number of columns
Compatible data types (e.g., text with text)

-- Shape rule:
SELECT col1, col2
...
UNION / INTERSECT / EXCEPT
SELECT col1, col2
...

UNION concept

UNION combines and removes duplicates
UNION ALL keeps duplicates
Useful for collecting all distinct values

-- Pattern:
queryA
UNION
queryB

UNION example: cities seen in riders or bikes status labels

Two different sources, one combined list
Both queries return 1 text column
UNION removes duplicates

SELECT city AS label
FROM riders
UNION
SELECT status AS label
FROM bikes
ORDER BY label;

UNION ALL example: keep duplicates for counting

Use ALL when duplicates are meaningful
Then aggregate
Measure first, then interpret

SELECT label, COUNT(*) AS occurrences
FROM (
  SELECT city AS label FROM riders
  UNION ALL
  SELECT status AS label FROM bikes
) x
GROUP BY label
ORDER BY occurrences DESC, label;

INTERSECT concept

INTERSECT returns only rows present in both results
Great for “common to both” questions

-- Pattern:
queryA
INTERSECT
queryB

INTERSECT example: riders who appear in rentals

Same idea as “has rental”, expressed as a set intersection
Returns rider_id values common to both
Then join to names if needed

SELECT rider_id
FROM riders
INTERSECT
SELECT rider_id
FROM rentals
ORDER BY rider_id;

INTERSECT + JOIN: show rider names

Set step first (IDs)
Then join for readable fields

SELECT r.rider_id, r.name
FROM riders r
JOIN (
  SELECT rider_id FROM riders
  INTERSECT
  SELECT rider_id FROM rentals
) x USING (rider_id)
ORDER BY r.rider_id;

EXCEPT concept

EXCEPT returns rows in the first result not in the second
Set difference (“A minus B”)
Useful for identifying missing records

-- Pattern:
queryA
EXCEPT
queryB

EXCEPT example: riders who never rented

Return riders missing from rentals
Often used for audits
Clear set thinking

SELECT rider_id
FROM riders
EXCEPT
SELECT rider_id
FROM rentals
ORDER BY rider_id;

EXCEPT + JOIN: show rider names

EXCEPT returns IDs
Join converts IDs to names
Best practice for reporting outputs

SELECT r.rider_id, r.name
FROM riders r
JOIN (
  SELECT rider_id FROM riders
  EXCEPT
  SELECT rider_id FROM rentals
) x USING (rider_id)
ORDER BY r.rider_id;

Set operator cheat table

Operator	Keeps	Typical question
`UNION`	All distinct rows from both	“Show everything from A or B”
`INTERSECT`	Only rows in both	“What’s common to A and B?”
`EXCEPT`	Rows in A but not B	“What’s in A missing from B?”

Important: ORDER BY goes at the end

With set operators, apply ORDER BY after the final query
If you need ordering inside, use subqueries

-- Correct:
SELECT city AS label FROM riders
UNION
SELECT status AS label FROM bikes
ORDER BY label;

Combine: JOIN + EXCEPT (bikes never rented)

Find bikes missing from rentals
EXCEPT gives bike_id set difference
JOIN adds bike details

SELECT b.bike_id, b.bike_type, b.status
FROM bikes b
JOIN (
  SELECT bike_id FROM bikes
  EXCEPT
  SELECT bike_id FROM rentals
) x USING (bike_id)
ORDER BY b.bike_id;

Combine: JOIN + INTERSECT (bikes that were rented)

INTERSECT gives bike_id values seen in both tables
JOIN makes it readable
Set first, then decorate

SELECT b.bike_id, b.bike_type
FROM bikes b
JOIN (
  SELECT bike_id FROM bikes
  INTERSECT
  SELECT bike_id FROM rentals
) x USING (bike_id)
ORDER BY b.bike_id;

Combine: UNION ALL + JOIN (rider names from two sources)

Two rider sets: Stockton riders and riders who rented
UNION ALL keeps duplicates (useful to see overlap)
Then join to names once

SELECT r.rider_id, r.name, x.source
FROM riders r
JOIN (
  SELECT rider_id, 'city:Stockton' AS source
  FROM riders
  WHERE city = 'Stockton'
  UNION ALL
  SELECT rider_id, 'has:rental' AS source
  FROM rentals
) x USING (rider_id)
ORDER BY r.rider_id, x.source;

Comparison: feature support

Feature	SQLite	MariaDB
CROSS JOIN	Supported	Supported
JOIN ... ON	Supported	Supported
JOIN ... USING	Supported	Supported
UNION / UNION ALL	Supported	Supported
INTERSECT	Supported	Supported
EXCEPT	Supported	Supported

Portable habits (works almost everywhere)

Core joins: JOIN ... ON, JOIN ... USING, CROSS JOIN
Core sets: UNION, INTERSECT, EXCEPT
Always validate row counts after each step

-- Portable core:
-- JOIN ... ON / USING
-- UNION / INTERSECT / EXCEPT
-- + COUNT(*) checks

Practice 1: rentals report (3 tables)

Show rental_id, rider name, bike type
Use two JOINs with ON
Sort by rental_id

SELECT rt.rental_id, r.name, b.bike_type, rt.pickup_ts
FROM rentals rt
JOIN riders r
  ON rt.rider_id = r.rider_id
JOIN bikes b
  ON rt.bike_id = b.bike_id
ORDER BY rt.rental_id;

Practice 2: riders with no rentals (EXCEPT)

Return rider_id + name
Use EXCEPT to find missing IDs
Then join for names

SELECT r.rider_id, r.name
FROM riders r
JOIN (
  SELECT rider_id FROM riders
  EXCEPT
  SELECT rider_id FROM rentals
) x USING (rider_id)
ORDER BY r.rider_id;

Practice 3: overlap list (INTERSECT)

Find riders who are in Stockton and have rentals
INTERSECT the two rider_id sets
Join to names

SELECT r.rider_id, r.name
FROM riders r
JOIN (
  SELECT rider_id FROM riders WHERE city = 'Stockton'
  INTERSECT
  SELECT rider_id FROM rentals
) x USING (rider_id)
ORDER BY r.rider_id;

Cheat sheet

Need	Use	Bike example
All combinations	`CROSS JOIN`	All rider × bike pairs
Match rows by rule	`JOIN ... ON`	Rentals ↔ Riders (rider_id)
Match on shared column	`JOIN ... USING`	Riders ↔ Prefs (rider_id)
Combine distinct rows	`UNION`	Labels from two sources
Common rows	`INTERSECT`	Riders who appear in rentals
Missing rows	`EXCEPT`	Riders who never rented

Summary

CROSS JOIN = all pairings (use carefully)
ON = explicit match rule
USING = shorthand for shared key name
UNION = combine distinct rows (ALL keeps duplicates)
INTERSECT = overlap
EXCEPT = difference

-- Clean, common pattern:
SELECT rt.rental_id, r.name, b.bike_type
FROM rentals rt
JOIN riders r ON rt.rider_id = r.rider_id
JOIN bikes  b ON rt.bike_id  = b.bike_id
ORDER BY rt.rental_id;

Module 8

NULL · IS · NOT · DEFAULT · CASE · WHEN

Handling missing values in a flower dataset

Understand NULL values
Filter with IS NULL
Reverse logic with NOT
Use DEFAULT values
Create classifications with CASE

-- Query habit

-- 1 inspect table
-- 2 detect NULL
-- 3 filter safely
-- 4 classify data

Flower dataset

Column	Description
flower_id	unique flower record
flower_name	type of flower
color	flower color
price	price per flower
bloom_season	season flower blooms

Create a simple flower table
Some bloom seasons will be unknown
Use MariaDB-friendly column types

CREATE TABLE flowers (
  flower_id INT PRIMARY KEY,
  flower_name VARCHAR(50),
  color VARCHAR(30),
  price DECIMAL(5,2),
  bloom_season VARCHAR(30)
);

Insert example flowers
Some bloom seasons unknown
NULL valid in MariaDB

INSERT INTO flowers
  (flower_id, flower_name, color, price, bloom_season)
VALUES
  (1, 'Rose',      'Red',    4.50, 'Spring'),
  (2, 'Tulip',     'Yellow', 3.20, 'Spring'),
  (3, 'Orchid',    'Purple', 7.80, NULL),
  (4, 'Lily',      'White',  5.00, 'Summer'),
  (5, 'Sunflower', 'Yellow', 2.80, NULL);

flower_id	flower	color	price	bloom_season
1	Rose	Red	4.50	Spring
3	Orchid	Purple	7.80	NULL
5	Sunflower	Yellow	2.80	NULL

NULL means unknown value
Different from zero
Different from empty text

SELECT *
FROM flowers
WHERE bloom_season IS NULL;

flower	bloom_season
Orchid	NULL
Sunflower	NULL

NULL cannot be compared using =

-- incorrect
WHERE bloom_season = NULL

Use IS NULL instead

WHERE bloom_season IS NULL

Find flowers with known seasons

SELECT *
FROM flowers
WHERE bloom_season IS NOT NULL;

flower	season
Rose	Spring
Lily	Summer

NOT reverses conditions

SELECT *
FROM flowers
WHERE NOT color = 'Yellow';

flower	color
Rose	Red
Orchid	Purple

Assign automatic values
MariaDB column default value

CREATE TABLE flowers_default (
  flower_id INT PRIMARY KEY,
  flower_name VARCHAR(50),
  color VARCHAR(30),
  price DECIMAL(5,2),
  bloom_season VARCHAR(30) DEFAULT 'Unknown'
);

CASE creates conditional output
Supported in MariaDB

CASE
  WHEN condition THEN value
  ELSE value
END

Label flowers with known season

SELECT
  flower_name,
  CASE
    WHEN bloom_season IS NULL THEN 'Unknown Season'
    ELSE 'Season Known'
  END AS season_status
FROM flowers;

flower	status
Rose	Season Known
Orchid	Unknown Season

Multiple conditions possible
MariaDB evaluates top to bottom

SELECT
  flower_name,
  CASE
    WHEN price > 6 THEN 'Premium'
    WHEN price > 3 THEN 'Standard'
    ELSE 'Budget'
  END AS price_category
FROM flowers;

flower	price_category
Orchid	Premium
Rose	Standard
Sunflower	Budget

Concept	Purpose
NULL	missing value
IS NULL	find missing
NOT	reverse logic
DEFAULT	automatic value
CASE	conditional logic

Find flowers with unknown season
Find non-yellow flowers
Classify price category

SELECT *
FROM flowers
WHERE bloom_season IS NULL;

Spring Break

Module 10

SCHEMA · COLUMN · SEQUENCE · REFERENCES · DATABASE · INDEX

Reproducible SQL structure examples in a flower database system

Understand database structure
Build schema and tables in correct order
Use CRUD to reproduce every example
Connect tables with references
Improve lookup with indexes

-- execution order

-- CREATE
-- INSERT
-- UPDATE
-- DELETE
-- SELECT
-- INDEX

phase	purpose
build	create objects
load	insert rows
inspect	query results

Database is the top-level container
PostgreSQL supports multiple databases
SQLite uses a file-based database

CREATE DATABASE flower_shop;

status
CREATE DATABASE

Schema groups related tables
Useful for inventory and sales separation
Improves structure and clarity

CREATE SCHEMA inventory;
CREATE SCHEMA sales;

schema_name
inventory
sales

Sequence generates unique identifiers
Common in PostgreSQL table design
Used before inserting flower rows

CREATE SEQUENCE inventory.flower_seq
START 1
INCREMENT 1;

sequence_name
flower_seq

Customers table stores buyer information
Primary key identifies each customer
City and email support later examples

CREATE TABLE sales.customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name VARCHAR(60) NOT NULL,
  city VARCHAR(40),
  email VARCHAR(80) UNIQUE
);

column	type
customer_id	INTEGER
customer_name	VARCHAR
email	VARCHAR

Columns define flower attributes
Price and stock support CRUD examples
Constraints protect data quality

CREATE TABLE inventory.flowers (
  flower_id INTEGER PRIMARY KEY,
  flower_name VARCHAR(50) NOT NULL,
  color VARCHAR(30),
  price DECIMAL(6,2) NOT NULL,
  stock INTEGER DEFAULT 0
);

column	rule
flower_name	NOT NULL
price	NOT NULL
stock	DEFAULT 0

Orders table stores purchase headers
Customer reference creates parent-child relation
Status supports later updates

CREATE TABLE sales.orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES sales.customers(customer_id),
  order_date DATE,
  status VARCHAR(20)
);

column	reference
customer_id	customers(customer_id)

Order items resolve many-to-many structure
Each row links one order and one flower
Quantity supports sales examples

CREATE TABLE sales.order_items (
  item_id INTEGER PRIMARY KEY,
  order_id INTEGER REFERENCES sales.orders(order_id),
  flower_id INTEGER REFERENCES inventory.flowers(flower_id),
  quantity INTEGER NOT NULL
);

column	reference
order_id	orders(order_id)
flower_id	flowers(flower_id)

Insert customer sample rows first
These rows are needed before inserting orders
City values support filter examples

INSERT INTO sales.customers
(customer_id, customer_name, city, email)
VALUES
(1, 'Alice', 'Stockton', 'alice@demo.com'),
(2, 'Brian', 'Lodi', 'brian@demo.com'),
(3, 'Carla', 'Stockton', 'carla@demo.com');

SELECT customer_id, customer_name, city
FROM sales.customers;

customer_id	customer_name	city
1	Alice	Stockton
2	Brian	Lodi
3	Carla	Stockton

Use sequence-generated flower identifiers
These rows support price, stock, and index examples
Result matches the query shown

INSERT INTO inventory.flowers
(flower_id, flower_name, color, price, stock)
VALUES
(NEXTVAL('inventory.flower_seq'),'Rose','Red',4.50,20),
(NEXTVAL('inventory.flower_seq'),'Tulip','Yellow',3.20,30),
(NEXTVAL('inventory.flower_seq'),'Lily','White',5.00,15);

SELECT flower_id, flower_name, price
FROM inventory.flowers;

flower_id	flower_name	price
1	Rose	4.50
2	Tulip	3.20
3	Lily	5.00

Orders can be inserted only after customers exist
Status and date support later examples
Each customer_id must already be valid

INSERT INTO sales.orders
(order_id, customer_id, order_date, status)
VALUES
(101, 1, '2026-04-01', 'OPEN'),
(102, 2, '2026-04-02', 'PAID'),
(103, 1, '2026-04-03', 'PAID');

SELECT order_id, customer_id, status
FROM sales.orders;

order_id	customer_id	status
101	1	OPEN
102	2	PAID
103	1	PAID

Order items require both orders and flowers
Quantity supports later joins and summaries
Each referenced key must already exist

INSERT INTO sales.order_items
(item_id, order_id, flower_id, quantity)
VALUES
(1, 101, 1, 6),
(2, 101, 2, 4),
(3, 102, 3, 2),
(4, 103, 1, 5);

SELECT item_id, order_id, flower_id, quantity
FROM sales.order_items;

item_id	order_id	flower_id	quantity
1	101	1	6
2	101	2	4
3	102	3	2

Select verifies inserted flower rows
Shows the base table state
This preview is valid because inserts already happened

SELECT flower_id, flower_name, color, price
FROM inventory.flowers;

flower_id	flower_name	color	price
1	Rose	Red	4.50
2	Tulip	Yellow	3.20
3	Lily	White	5.00

Columns can be selected explicitly
Queries should return only needed fields
This improves readability and control

SELECT customer_id, customer_name
FROM sales.customers;

customer_id	customer_name
1	Alice
2	Brian
3	Carla

Update changes an existing row
Price update affects later result tables
The select confirms the changed value

UPDATE inventory.flowers
SET price = 4.80
WHERE flower_name = 'Rose';

SELECT flower_name, price
FROM inventory.flowers
WHERE flower_name = 'Rose';

flower_name	price
Rose	4.80

Read the table after the update
Result now reflects the modified price
This query is reproducible in sequence

SELECT flower_id, flower_name, price
FROM inventory.flowers
WHERE price >= 4.80;

flower_id	flower_name	price
1	Rose	4.80
3	Lily	5.00

Update can use arithmetic expressions
Stock values often change after sales
The result confirms the new stock level

UPDATE inventory.flowers
SET stock = stock - 5
WHERE flower_id = 1;

SELECT flower_id, flower_name, stock
FROM inventory.flowers
WHERE flower_id = 1;

flower_id	flower_name	stock
1	Rose	15

Read the table after stock modification
Only flowers meeting the condition appear
This query depends on earlier inserts and updates

SELECT flower_name, stock
FROM inventory.flowers
WHERE stock >= 15;

flower_name	stock
Rose	15
Tulip	30
Lily	15

Transactional rows often change state
Status update is a common business example
Result confirms the status transition

UPDATE sales.orders
SET status = 'PAID'
WHERE order_id = 101;

SELECT order_id, status
FROM sales.orders
WHERE order_id = 101;

order_id	status
101	PAID

Read after status update
Customer 1 now has only paid orders
This result is valid only after the update slide

SELECT order_id, customer_id, status
FROM sales.orders
WHERE customer_id = 1;

order_id	customer_id	status
101	1	PAID
103	1	PAID

Delete removes a row permanently
Child table is a safe place to demonstrate delete
The result shows remaining rows afterward

DELETE FROM sales.order_items
WHERE item_id = 3;

SELECT item_id, order_id, flower_id
FROM sales.order_items;

item_id	order_id	flower_id
1	101	1
2	101	2
4	103	1

Read confirms the deleted row is gone
The remaining rows still satisfy references
This state is needed for later joins

SELECT order_id, flower_id, quantity
FROM sales.order_items
ORDER BY item_id;

order_id	flower_id	quantity
101	1	6
101	2	4
103	1	5

References protect parent rows in use
Deleting customer 1 should fail
This demonstrates referential integrity

DELETE FROM sales.customers
WHERE customer_id = 1;

result
foreign key violation

Filtering works after rows are loaded
City is a simple searchable column
Only matching rows appear in the result

SELECT customer_id, customer_name, city
FROM sales.customers
WHERE city = 'Stockton';

customer_id	customer_name	city
1	Alice	Stockton
3	Carla	Stockton

Price filter depends on earlier update
Only flowers at or above threshold are shown
Result is reproducible in this sequence

SELECT flower_name, price
FROM inventory.flowers
WHERE price >= 4.80;

flower_name	price
Rose	4.80
Lily	5.00

References allow meaningful lookup by customer
Result depends on earlier order rows
Status also reflects the completed update

SELECT order_id, customer_id, status
FROM sales.orders
WHERE customer_id = 1;

order_id	customer_id	status
101	1	PAID
103	1	PAID

Join combines parent and child tables
References make the join valid
This is a core relational example

SELECT o.order_id, c.customer_name, o.status
FROM sales.orders o
JOIN sales.customers c
  ON o.customer_id = c.customer_id;

order_id	customer_name	status
101	Alice	PAID
102	Brian	PAID
103	Alice	PAID

Join connects transaction rows to product names
The deleted item is no longer present
Result reflects current table state

SELECT oi.order_id, f.flower_name, oi.quantity
FROM sales.order_items oi
JOIN inventory.flowers f
  ON oi.flower_id = f.flower_id;

order_id	flower_name	quantity
101	Rose	6
101	Tulip	4
103	Rose	5

Indexes improve repeated search conditions
Names are common lookup targets
Create the index before index-based examples

CREATE INDEX idx_flower_name
ON inventory.flowers(flower_name);

index_name	column
idx_flower_name	flower_name

Exact-match lookup benefits from indexing
Result table confirms the searched row
This slide comes only after index creation

SELECT flower_id, flower_name, price
FROM inventory.flowers
WHERE flower_name = 'Rose';

flower_id	flower_name	price
1	Rose	4.80

Foreign key columns are strong index candidates
They support joins and filters
Create the index before lookup by customer_id

CREATE INDEX idx_orders_customer
ON sales.orders(customer_id);

index_name	column
idx_orders_customer	customer_id

Customer-based order lookup is very common
Foreign key index supports this access pattern
The result depends on earlier loaded data

SELECT order_id, customer_id
FROM sales.orders
WHERE customer_id = 1;

order_id	customer_id
101	1
103	1

Sequence state advances as rows are inserted
This helps explain generated identifiers
Current value depends on prior inserts

SELECT NEXTVAL('inventory.flower_seq');

nextval
4

Insert a new row using the sequence again
This demonstrates continued key generation
The result confirms the inserted flower

INSERT INTO inventory.flowers
(flower_id, flower_name, color, price, stock)
VALUES
(NEXTVAL('inventory.flower_seq'),'Orchid','Purple',7.80,8);

SELECT flower_id, flower_name, price
FROM inventory.flowers
WHERE flower_name = 'Orchid';

flower_id	flower_name	price
5	Orchid	7.80

Read the newly inserted row directly
This verifies sequence-based insertion
Result is valid because insert happened earlier

SELECT flower_name, color, stock
FROM inventory.flowers
WHERE flower_name = 'Orchid';

flower_name	color	stock
Orchid	Purple	8

Delete the unused flower cleanly
This works because no child row references it
The select shows the remaining flowers

DELETE FROM inventory.flowers
WHERE flower_name = 'Orchid';

SELECT flower_id, flower_name
FROM inventory.flowers;

flower_id	flower_name
1	Rose
2	Tulip
3	Lily

Read confirms the delete succeeded
Only the original flowers remain
This closes the CRUD cycle cleanly

SELECT flower_name, color, stock
FROM inventory.flowers
ORDER BY flower_name;

flower_name	color	stock
Lily	White	15
Rose	Red	15
Tulip	Yellow	30

Concept	Purpose
DATABASE	top container
SCHEMA	object grouping
COLUMN	table attribute
SEQUENCE	generated id
REFERENCES	table relationship
INDEX	query speed
CREATE	add object or row
READ	query data
UPDATE	modify row
DELETE	remove row

Module 11

CONSTRAINT · PRIMARY · FOREIGN · KEY · UNIQUE · CHECK

Reproducible constraint examples in a flower database system

Understand why constraints matter
Use primary and foreign keys correctly
Prevent duplicates with unique
Validate values with check
Reproduce every example in execution order

-- execution order

-- CREATE TABLE
-- INSERT valid rows
-- INSERT invalid rows
-- UPDATE rows
-- DELETE rows
-- SELECT results

focus	goal
constraint	protect data
key	identify rows
check	validate values

Constraints are table rules
They protect structure and values
Good design starts with strong rules

-- common constraint types

PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK

type	purpose
PRIMARY KEY	identify row
UNIQUE	avoid duplicates
CHECK	enforce condition

Customer table uses primary and unique rules
Customer ID identifies each row
Email must not repeat

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name VARCHAR(60) NOT NULL,
  city VARCHAR(40),
  email VARCHAR(80) UNIQUE
);

column	constraint
customer_id	PRIMARY KEY
email	UNIQUE

Flower table uses primary, unique, and check rules
Name should not repeat in this example
Price and stock must be valid

CREATE TABLE flowers (
  flower_id INTEGER PRIMARY KEY,
  flower_name VARCHAR(50) UNIQUE,
  color VARCHAR(30),
  price DECIMAL(6,2) CHECK (price > 0),
  stock INTEGER CHECK (stock >= 0)
);

column	constraint
flower_id	PRIMARY KEY
flower_name	UNIQUE
price	CHECK

Orders link to customers
Foreign key protects valid parent rows
Status is controlled by check rule

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  status VARCHAR(20) CHECK (status IN ('OPEN','PAID','CANCELLED')),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

column	constraint
order_id	PRIMARY KEY
customer_id	FOREIGN KEY
status	CHECK

Order items connect orders and flowers
Two foreign keys protect table links
Quantity must be positive

CREATE TABLE order_items (
  item_id INTEGER PRIMARY KEY,
  order_id INTEGER,
  flower_id INTEGER,
  quantity INTEGER CHECK (quantity > 0),
  FOREIGN KEY (order_id) REFERENCES orders(order_id),
  FOREIGN KEY (flower_id) REFERENCES flowers(flower_id)
);

column	constraint
item_id	PRIMARY KEY
order_id	FOREIGN KEY
quantity	CHECK

Primary key uniquely identifies each row
It cannot be null
It cannot repeat

SELECT customer_id, customer_name
FROM customers;

customer_id	customer_name
1	Alice
2	Brian
3	Carla

Insert valid rows first
Each primary key is unique
Each email is also unique

INSERT INTO customers
(customer_id, customer_name, city, email)
VALUES
(1, 'Alice', 'Stockton', 'alice@demo.com'),
(2, 'Brian', 'Lodi', 'brian@demo.com'),
(3, 'Carla', 'Manteca', 'carla@demo.com');

SELECT customer_id, customer_name, email
FROM customers;

customer_id	customer_name	email
1	Alice	alice@demo.com
2	Brian	brian@demo.com
3	Carla	carla@demo.com

Insert flowers after table creation
Names are unique in this dataset
Price and stock satisfy check rules

INSERT INTO flowers
(flower_id, flower_name, color, price, stock)
VALUES
(1, 'Rose', 'Red', 4.50, 20),
(2, 'Tulip', 'Yellow', 3.20, 30),
(3, 'Lily', 'White', 5.00, 15);

SELECT flower_id, flower_name, price, stock
FROM flowers;

flower_id	flower_name	price	stock
1	Rose	4.50	20
2	Tulip	3.20	30
3	Lily	5.00	15

Orders require existing customer rows
Status must match allowed values
Foreign key and check both apply here

INSERT INTO orders
(order_id, customer_id, status)
VALUES
(101, 1, 'OPEN'),
(102, 2, 'PAID'),
(103, 1, 'PAID');

SELECT order_id, customer_id, status
FROM orders;

order_id	customer_id	status
101	1	OPEN
102	2	PAID
103	1	PAID

Order items need valid orders and flowers
Quantity must be positive
Every relation must already exist

INSERT INTO order_items
(item_id, order_id, flower_id, quantity)
VALUES
(1, 101, 1, 6),
(2, 101, 2, 4),
(3, 102, 3, 2);

SELECT item_id, order_id, flower_id, quantity
FROM order_items;

item_id	order_id	flower_id	quantity
1	101	1	6
2	101	2	4
3	102	3	2

A primary key cannot repeat
Duplicate key inserts must fail
This protects row identity

INSERT INTO customers
(customer_id, customer_name, city, email)
VALUES
(1, 'Diana', 'Lodi', 'diana@demo.com');

result
primary key violation

The original rows remain unchanged
Failed insert does not create a new row
Primary key rule is still preserved

SELECT customer_id, customer_name
FROM customers
ORDER BY customer_id;

customer_id	customer_name
1	Alice
2	Brian
3	Carla

Unique prevents duplicate values
It is useful for email and codes
It is different from primary key because a table can have many unique rules

SELECT customer_name, email
FROM customers;

customer_name	email
Alice	alice@demo.com
Brian	brian@demo.com
Carla	carla@demo.com

Duplicate email should fail
Unique rule protects business identity
This example uses an existing email

INSERT INTO customers
(customer_id, customer_name, city, email)
VALUES
(4, 'Diana', 'Lodi', 'alice@demo.com');

result
unique constraint violation

The duplicate row was not inserted
Original unique values remain intact
Data quality is preserved

SELECT customer_id, email
FROM customers
ORDER BY customer_id;

customer_id	email
1	alice@demo.com
2	brian@demo.com
3	carla@demo.com

Check validates data conditions
It prevents impossible or invalid values
Price, stock, and status are common uses

SELECT flower_name, price, stock
FROM flowers;

flower_name	price	stock
Rose	4.50	20
Tulip	3.20	30
Lily	5.00	15

Negative price should fail
Check rule blocks invalid business data
This protects reporting accuracy

INSERT INTO flowers
(flower_id, flower_name, color, price, stock)
VALUES
(4, 'Orchid', 'Purple', -2.00, 8);

result
check constraint violation

Stock cannot be negative
Check rule prevents impossible inventory
This keeps totals meaningful

INSERT INTO flowers
(flower_id, flower_name, color, price, stock)
VALUES
(4, 'Orchid', 'Purple', 7.80, -1);

result
check constraint violation

Status must match the approved list
Check can control small business vocabularies
Invalid status values must fail

INSERT INTO orders
(order_id, customer_id, status)
VALUES
(104, 2, 'SHIPPED');

result
check constraint violation

Foreign key connects child rows to parent rows
It protects relational consistency
Child rows must point to existing parents

SELECT order_id, customer_id
FROM orders;

order_id	customer_id
101	1
102	2
103	1

Order cannot reference missing customer
This prevents orphaned rows
Foreign key rule blocks the insert

INSERT INTO orders
(order_id, customer_id, status)
VALUES
(104, 99, 'OPEN');

result
foreign key violation

Order item needs valid order and flower
Invalid parent reference must fail
Both foreign keys protect the child table

INSERT INTO order_items
(item_id, order_id, flower_id, quantity)
VALUES
(4, 999, 1, 3);

result
foreign key violation

Deleting a parent in use should fail
Customer 1 is referenced by orders
Foreign key protects existing children

DELETE FROM customers
WHERE customer_id = 1;

result
foreign key violation

Updates are allowed when rules remain valid
This update keeps stock nonnegative
Check constraint still passes

UPDATE flowers
SET stock = 18
WHERE flower_id = 1;

SELECT flower_id, flower_name, stock
FROM flowers
WHERE flower_id = 1;

flower_id	flower_name	stock
1	Rose	18

Update must also satisfy check rules
Negative stock should fail here too
Constraints apply on update, not just insert

UPDATE flowers
SET stock = -4
WHERE flower_id = 2;

result
check constraint violation

Updating a foreign key must keep a valid parent
Changing to missing customer 99 should fail
Relation integrity is preserved

UPDATE orders
SET customer_id = 99
WHERE order_id = 102;

result
foreign key violation

Unique also applies during updates
Changing one email to an existing email should fail
Duplicates remain blocked after creation

UPDATE customers
SET email = 'alice@demo.com'
WHERE customer_id = 2;

result
unique constraint violation

Deleting a child row is usually safe
Parent rows remain valid
This is the normal order before parent deletion

DELETE FROM order_items
WHERE item_id = 3;

SELECT item_id, order_id, flower_id
FROM order_items;

item_id	order_id	flower_id
1	101	1
2	101	2

After deleting the child, parent may be removable
Order 102 no longer has order_items
This delete is now valid

DELETE FROM orders
WHERE order_id = 102;

SELECT order_id, customer_id, status
FROM orders;

order_id	customer_id	status
101	1	OPEN
103	1	PAID

PRIMARY KEY is one full phrase
FOREIGN KEY is one full phrase
KEY alone is part of both concepts

-- examples

customer_id INTEGER PRIMARY KEY

FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)

phrase	role
PRIMARY KEY	identify row
FOREIGN KEY	link tables

One table can use many constraints together
Good schema design combines them carefully
Each rule solves a different problem

SELECT flower_id, flower_name, price, stock
FROM flowers
ORDER BY flower_id;

flower_id	flower_name	price	stock
1	Rose	4.50	18
2	Tulip	3.20	30
3	Lily	5.00	15

Foreign keys make joins reliable
Valid parent-child rows produce clean results
Constraints help query trustworthiness

SELECT o.order_id, c.customer_name, o.status
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id;

order_id	customer_name	status
101	Alice	OPEN
103	Alice	PAID

Check rules keep filtered results meaningful
There are no zero or negative prices
This makes comparisons safer

SELECT flower_name, price
FROM flowers
WHERE price > 3.00;

flower_name	price
Rose	4.50
Tulip	3.20
Lily	5.00

Unique columns are strong lookup fields
Email can identify one customer row
This works because duplicates are blocked

SELECT customer_id, customer_name
FROM customers
WHERE email = 'brian@demo.com';

customer_id	customer_name
2	Brian

Constraint	Main Purpose
PRIMARY KEY	unique row identity
FOREIGN KEY	valid table relationship
UNIQUE	prevent duplicates
CHECK	enforce value rule
KEY	part of relational structure
CONSTRAINT	protect data quality

Insert one valid flower row
Try one invalid duplicate email
Try one invalid negative stock value
Delete child rows before parent rows

SELECT customer_id, city
FROM customers
WHERE city = 'Lodi';

customer_id	city
2	Lodi

Constraints stop bad data before it spreads
Keys organize identity and relationships
Checks and unique rules keep values trustworthy

SELECT order_id, status
FROM orders
ORDER BY order_id;

order_id	status
101	OPEN
103	PAID

Module 12

TRANSACTION · BEGIN · COMMIT · ROLLBACK · SAVEPOINT · RELEASE

Reproducible transaction control examples in a flower database system

Understand transaction control
Start work with BEGIN
Make changes permanent with COMMIT
Undo changes with ROLLBACK
Use SAVEPOINT and RELEASE correctly

-- execution order

-- BEGIN
-- INSERT or UPDATE
-- SAVEPOINT
-- more changes
-- ROLLBACK or COMMIT

phase	purpose
begin	start unit of work
rollback	undo changes
commit	save changes

A transaction groups related operations
Changes should succeed together or fail together
This protects consistency in business work

-- transaction control words

BEGIN
COMMIT
ROLLBACK
SAVEPOINT
RELEASE

term	purpose
BEGIN	start transaction
COMMIT	make permanent
ROLLBACK	undo work

Flower table stores inventory rows
Price and stock support transaction examples
This table is the base for later updates

CREATE TABLE flowers (
  flower_id INTEGER PRIMARY KEY,
  flower_name VARCHAR(50) UNIQUE,
  color VARCHAR(30),
  price DECIMAL(6,2),
  stock INTEGER
);

column	role
flower_id	primary key
flower_name	flower label
stock	inventory count

Customers table stores buyer rows
Used later for multi-table transaction examples
Simple structure keeps examples readable

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  customer_name VARCHAR(60),
  city VARCHAR(40)
);

column	role
customer_id	primary key
customer_name	buyer name
city	location

Orders table supports transactional business flow
Customer link models real purchases
Status supports commit and rollback examples

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  status VARCHAR(20),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

column	role
order_id	primary key
customer_id	customer link
status	order state

Load base inventory before transaction examples
These rows will be updated and rolled back later
Result shows the initial table state

INSERT INTO flowers
(flower_id, flower_name, color, price, stock)
VALUES
(1, 'Rose', 'Red', 4.50, 20),
(2, 'Tulip', 'Yellow', 3.20, 30),
(3, 'Lily', 'White', 5.00, 15);

SELECT flower_id, flower_name, stock
FROM flowers;

flower_id	flower_name	stock
1	Rose	20
2	Tulip	30
3	Lily	15

Load customers before order transactions
These parent rows support later inserts
Result confirms the base customer state

INSERT INTO customers
(customer_id, customer_name, city)
VALUES
(1, 'Alice', 'Stockton'),
(2, 'Brian', 'Lodi');

SELECT customer_id, customer_name, city
FROM customers;

customer_id	customer_name	city
1	Alice	Stockton
2	Brian	Lodi

Insert a few initial orders
These rows will support update and rollback examples
The base state must exist before transactions begin

INSERT INTO orders
(order_id, customer_id, status)
VALUES
(101, 1, 'OPEN'),
(102, 2, 'PAID');

SELECT order_id, customer_id, status
FROM orders;

order_id	customer_id	status
101	1	OPEN
102	2	PAID

BEGIN starts a transaction block
Changes after BEGIN are not yet permanent
This is the first control point

BEGIN;

transaction_state
active

Work inside the transaction after BEGIN
This update changes stock temporarily
The result reflects the current uncommitted state

BEGIN;

UPDATE flowers
SET stock = 12
WHERE flower_id = 1;

SELECT flower_id, flower_name, stock
FROM flowers
WHERE flower_id = 1;

flower_id	flower_name	stock
1	Rose	12

ROLLBACK undoes work in the active transaction
It returns the database to the state before BEGIN
Use it when an operation should be cancelled

ROLLBACK;

transaction_state
cancelled

The earlier stock change was undone
Original value is visible again
This proves rollback reversed the work

SELECT flower_id, flower_name, stock
FROM flowers
WHERE flower_id = 1;

flower_id	flower_name	stock
1	Rose	20

COMMIT makes transaction changes permanent
After commit, rollback cannot undo that finished transaction
This closes the unit of work

COMMIT;

transaction_state
saved

Start a new transaction for a permanent change
Update Tulip stock and commit it
The final select shows the committed result

BEGIN;

UPDATE flowers
SET stock = 25
WHERE flower_id = 2;

COMMIT;

SELECT flower_id, flower_name, stock
FROM flowers
WHERE flower_id = 2;

flower_id	flower_name	stock
2	Tulip	25

The committed change remains in the table
This read occurs after the transaction ended
Commit preserved the new value

SELECT flower_name, stock
FROM flowers
ORDER BY flower_id;

flower_name	stock
Rose	20
Tulip	25
Lily	15

SAVEPOINT marks a partial checkpoint inside a transaction
It lets you roll back part of the work
This gives finer control than full rollback

SAVEPOINT sp_stock;

savepoint
sp_stock

Begin a transaction and create a checkpoint
Change one row before the savepoint
Later changes can be undone back to that marker

BEGIN;

UPDATE flowers
SET stock = 18
WHERE flower_id = 1;

SAVEPOINT sp_stock;

SELECT flower_id, flower_name, stock
FROM flowers
WHERE flower_id = 1;

flower_id	flower_name	stock
1	Rose	18

Make more changes after the savepoint
These are the changes we may partially undo
The result shows current transaction state

UPDATE flowers
SET stock = 10
WHERE flower_id = 3;

SELECT flower_id, flower_name, stock
FROM flowers
WHERE flower_id = 3;

flower_id	flower_name	stock
3	Lily	10

Rollback to savepoint undoes only later work
Earlier work before the savepoint remains
This is partial undo instead of full undo

ROLLBACK TO SAVEPOINT sp_stock;

rollback_scope
after sp_stock only

Rose stayed at the savepoint value
Lily returned to its old value
This proves partial rollback worked

SELECT flower_id, flower_name, stock
FROM flowers
WHERE flower_id IN (1, 3)
ORDER BY flower_id;

flower_id	flower_name	stock
1	Rose	18
3	Lily	15

Commit the transaction after partial rollback
The remaining valid change becomes permanent
This finalizes the checkpointed work

COMMIT;

transaction_state
saved after partial rollback

Rose remains changed because it was before savepoint rollback
Lily remains unchanged because that later change was undone
The current table state is now permanent

SELECT flower_name, stock
FROM flowers
ORDER BY flower_id;

flower_name	stock
Rose	18
Tulip	25
Lily	15

RELEASE removes a savepoint
It keeps the transaction active
After release, that savepoint name is gone

RELEASE SAVEPOINT sp_order;

savepoint_state
released

Create a new transaction for release example
Insert a new order and mark a savepoint
The row exists in current transaction state

BEGIN;

INSERT INTO orders
(order_id, customer_id, status)
VALUES
(103, 2, 'OPEN');

SAVEPOINT sp_order;

SELECT order_id, customer_id, status
FROM orders
WHERE order_id = 103;

order_id	customer_id	status
103	2	OPEN

Release the savepoint after deciding it is no longer needed
The transaction stays active
The inserted row still exists in current state

RELEASE SAVEPOINT sp_order;

savepoint	state
sp_order	removed

Commit after releasing the savepoint
The new order becomes permanent
The transaction ends successfully

COMMIT;

SELECT order_id, customer_id, status
FROM orders
ORDER BY order_id;

order_id	customer_id	status
101	1	OPEN
102	2	PAID
103	2	OPEN

A full rollback undoes all work in the active transaction
This example changes two rows and cancels both
No savepoint is used here

BEGIN;

UPDATE flowers
SET price = 6.00
WHERE flower_id = 1;

UPDATE flowers
SET price = 2.90
WHERE flower_id = 2;

ROLLBACK;

SELECT flower_id, flower_name, price
FROM flowers
ORDER BY flower_id;

flower_id	flower_name	price
1	Rose	4.50
2	Tulip	3.20
3	Lily	5.00

A transaction can include multiple related changes
Commit saves them together as one unit
This is useful for coordinated updates

BEGIN;

UPDATE flowers
SET stock = 17
WHERE flower_id = 1;

UPDATE orders
SET status = 'PAID'
WHERE order_id = 101;

COMMIT;

SELECT order_id, status
FROM orders
WHERE order_id = 101;

order_id	status
101	PAID

Both committed changes remain visible
This proves the transaction saved the full set
Related business updates moved together

SELECT flower_id, flower_name, stock
FROM flowers
WHERE flower_id = 1;

flower_id	flower_name	stock
1	Rose	17

Savepoints also work with insert operations
You can undo only the later insert
This supports careful batch work

BEGIN;

INSERT INTO flowers
(flower_id, flower_name, color, price, stock)
VALUES
(4, 'Orchid', 'Purple', 7.80, 8);

SAVEPOINT sp_insert;

INSERT INTO flowers
(flower_id, flower_name, color, price, stock)
VALUES
(5, 'Daisy', 'White', 2.50, 22);

SELECT flower_id, flower_name
FROM flowers
WHERE flower_id IN (4,5)
ORDER BY flower_id;

flower_id	flower_name
4	Orchid
5	Daisy

Rollback to the savepoint removes only the later insert
The earlier insert remains in the transaction
This is partial undo with rows

ROLLBACK TO SAVEPOINT sp_insert;

SELECT flower_id, flower_name
FROM flowers
WHERE flower_id IN (4,5)
ORDER BY flower_id;

flower_id	flower_name
4	Orchid

Commit preserves the earlier remaining insert
The cancelled insert stays absent
The transaction ends with one saved row

COMMIT;

SELECT flower_id, flower_name
FROM flowers
WHERE flower_id = 4;

flower_id	flower_name
4	Orchid

Release is not the same as commit
Release removes a savepoint only
Commit ends and saves the transaction

RELEASE SAVEPOINT name;
COMMIT;

command	effect
RELEASE	remove savepoint
COMMIT	save transaction

Full rollback cancels the whole transaction
Rollback to savepoint cancels only later work
This distinction is important in long transactions

ROLLBACK;
ROLLBACK TO SAVEPOINT sp1;

command	scope
ROLLBACK	entire transaction
ROLLBACK TO	after savepoint only

Committed transaction results are visible in normal reads
Order 101 was committed as paid
Order 103 was committed as open

SELECT order_id, status
FROM orders
ORDER BY order_id;

order_id	status
101	PAID
102	PAID
103	OPEN

The table now reflects all committed transaction history
Rolled-back changes are not present
Committed insert of Orchid is present

SELECT flower_id, flower_name, stock
FROM flowers
ORDER BY flower_id;

flower_id	flower_name	stock
1	Rose	17
2	Tulip	25
3	Lily	15
4	Orchid	8

Command	Purpose
TRANSACTION	group related work
BEGIN	start transaction
COMMIT	make changes permanent
ROLLBACK	undo entire transaction
SAVEPOINT	mark checkpoint
RELEASE	remove savepoint

Begin a transaction and update one row
Create a savepoint before the second change
Rollback to the savepoint and then commit
Compare final results with a full rollback

SELECT flower_name, stock
FROM flowers
WHERE stock >= 15
ORDER BY flower_id;

flower_name	stock
Rose	17
Tulip	25
Lily	15

Module 13

LOCK · GRANT · REVOKE · USER · ROLE

Reproducible access control examples in a flower database system

Understand database access control
Work with users and roles
Give permissions with grant
Remove permissions with revoke
Understand lock behavior during changes

-- execution order

-- CREATE USER
-- CREATE ROLE
-- GRANT
-- REVOKE
-- LOCK
-- SELECT result

topic	purpose
USER	account identity
ROLE	permission group
LOCK	control concurrent access

Databases need controlled access
Not every user should do every action
Security rules protect data and operations

-- security words

USER
ROLE
GRANT
REVOKE
LOCK

term	meaning
USER	login identity
ROLE	permission set
GRANT	give access

Flower table will be used in permission examples
Read and update permissions can be tested here
Simple structure keeps access control clear

CREATE TABLE flowers (
  flower_id INTEGER PRIMARY KEY,
  flower_name VARCHAR(50),
  color VARCHAR(30),
  price DECIMAL(6,2),
  stock INTEGER
);

column	use
flower_id	primary key
flower_name	product name
stock	inventory value

Load flower rows before permission tests
These rows support select and update examples
Result shows the base table state

INSERT INTO flowers
(flower_id, flower_name, color, price, stock)
VALUES
(1, 'Rose', 'Red', 4.50, 20),
(2, 'Tulip', 'Yellow', 3.20, 30),
(3, 'Lily', 'White', 5.00, 15);

SELECT flower_id, flower_name, stock
FROM flowers;

flower_id	flower_name	stock
1	Rose	20
2	Tulip	30
3	Lily	15

A user is a database account
Users can log in and perform actions
Permissions decide what each user may do

-- user examples

manager_user
analyst_user
cashier_user

user	purpose
manager_user	manage data
analyst_user	read reports
cashier_user	limited operations

Create a user for management work
This account may receive broad permissions
User creation comes before grant

CREATE USER manager_user
IDENTIFIED BY 'demo123';

user_name
manager_user

Create a second user for reporting
This account will receive read-focused permissions
Different users can receive different rights

CREATE USER analyst_user
IDENTIFIED BY 'demo123';

user_name
analyst_user

A role groups permissions together
Roles make administration easier
Many users can share one role

-- role examples

inventory_reader
inventory_editor
sales_manager

role	purpose
inventory_reader	read stock
inventory_editor	change stock

Create a role for read-only flower access
The role can later be assigned to users
This separates identity from permission design

CREATE ROLE inventory_reader;

role_name
inventory_reader

Create a second role for editing inventory
This role will need stronger privileges
Roles can reflect actual job responsibilities

CREATE ROLE inventory_editor;

role_name
inventory_editor

Grant gives a permission to a user or role
Permissions can be fine-grained
Examples include select, insert, update, and delete

GRANT SELECT ON flowers TO analyst_user;

action	object
SELECT	flowers

Grant read permission to the reader role
Users with this role can query the table
This is a common reporting pattern

GRANT SELECT
ON flowers
TO inventory_reader;

grantee	permission
inventory_reader	SELECT

Grant update permission to the editor role
This role may change stock values
Editing should be limited to the right group

GRANT UPDATE
ON flowers
TO inventory_editor;

grantee	permission
inventory_editor	UPDATE

Assign the reader role to analyst_user
The user receives the role’s permissions
This is cleaner than granting many separate rights

GRANT inventory_reader
TO analyst_user;

user	role
analyst_user	inventory_reader

Assign the editor role to manager_user
The manager can now edit inventory rows
Roles can be distributed according to responsibility

GRANT inventory_editor
TO manager_user;

user	role
manager_user	inventory_editor

A read role should allow safe queries
Analyst can read flower inventory
Result shows allowed access pattern

SELECT flower_id, flower_name, stock
FROM flowers;

flower_id	flower_name	stock
1	Rose	20
2	Tulip	30
3	Lily	15

An editor role may update stock
This permission should not be given to every user
Result confirms the allowed change

UPDATE flowers
SET stock = 18
WHERE flower_id = 1;

SELECT flower_id, flower_name, stock
FROM flowers
WHERE flower_id = 1;

flower_id	flower_name	stock
1	Rose	18

Revoke removes previously granted access
This is used when responsibilities change
Security is not only about giving rights

REVOKE UPDATE
ON flowers
FROM inventory_editor;

action	effect
REVOKE	remove permission

Remove update rights from the editor role
Users with that role lose the permission
This narrows allowed operations

REVOKE UPDATE
ON flowers
FROM inventory_editor;

grantee	removed_permission
inventory_editor	UPDATE

After revoke, the same update should fail
This shows that access control changed effectively
Permissions must be checked continuously

UPDATE flowers
SET stock = 16
WHERE flower_id = 1;

result
permission denied

A role assignment can also be removed
The user then loses inherited permissions
This is cleaner than editing many grants by hand

REVOKE inventory_reader
FROM analyst_user;

user	removed_role
analyst_user	inventory_reader

Without the reader role, select may fail
This depends on whether direct rights still exist
In this example, access is removed

SELECT flower_id, flower_name
FROM flowers;

result
permission denied

Permissions may also be granted directly to a user
This works without using a role
Direct grants are simple but less scalable

GRANT SELECT
ON flowers
TO analyst_user;

user	permission
analyst_user	SELECT

The user can read again after direct grant
This shows the difference between role-based and direct permission
Access was restored on the table

SELECT flower_name, price
FROM flowers
ORDER BY flower_id;

flower_name	price
Rose	4.50
Tulip	3.20
Lily	5.00

Locks control concurrent access to data
They help avoid conflicting changes
Writers and readers may be affected differently

LOCK TABLE flowers
IN EXCLUSIVE MODE;

lock_type	target
EXCLUSIVE	flowers

One session can lock a table before changing it
This prevents conflicting concurrent work
Session A now controls the table

BEGIN;

LOCK TABLE flowers
IN EXCLUSIVE MODE;

UPDATE flowers
SET stock = 17
WHERE flower_id = 1;

session	state
A	lock acquired

A second session may be blocked by the lock
This prevents overlapping conflicting writes
The database waits or throws an error depending on settings

UPDATE flowers
SET stock = 40
WHERE flower_id = 2;

session	result
B	blocked by lock

Committing usually releases transaction locks
The protected change becomes permanent
Other sessions may continue afterward

COMMIT;

lock_state
released after commit

After commit, the locked update is visible
The new stock value is now stable
This confirms session A succeeded

SELECT flower_id, flower_name, stock
FROM flowers
WHERE flower_id = 1;

flower_id	flower_name	stock
1	Rose	17

Multiple rights can be granted together
This is useful for broader working roles
The object and grantee remain explicit

GRANT SELECT, INSERT, UPDATE
ON flowers
TO manager_user;

user	permissions
manager_user	SELECT, INSERT, UPDATE

A single permission can be removed later
This allows gradual tightening of access
Other granted rights may remain

REVOKE INSERT
ON flowers
FROM manager_user;

user	removed_permission
manager_user	INSERT

After insert is revoked, the action should fail
This confirms the permission change took effect
Access control is dynamic and enforceable

INSERT INTO flowers
(flower_id, flower_name, color, price, stock)
VALUES
(4, 'Orchid', 'Purple', 7.80, 8);

result
permission denied

Roles reduce repeated grant statements
They simplify administration for many users
This is usually better than many direct grants

GRANT inventory_reader TO analyst_user;
GRANT inventory_reader TO cashier_user;

role	assigned_users
inventory_reader	2 users

User means who is acting
Role means what permissions are grouped
The two ideas should be kept distinct

-- identity vs permission

USER = analyst_user
ROLE = inventory_reader

concept	question answered
USER	who?
ROLE	what rights?

Locks are about concurrency, not permission
Grant answers who may act
Lock answers when others must wait

LOCK TABLE flowers
IN EXCLUSIVE MODE;

question	answered by
who may update?	GRANT
who must wait now?	LOCK

The table reflects the completed allowed updates
No unauthorized insert was added
Current state remains controlled

SELECT flower_id, flower_name, stock
FROM flowers
ORDER BY flower_id;

flower_id	flower_name	stock
1	Rose	17
2	Tulip	30
3	Lily	15

Concept	Purpose
LOCK	control concurrent access
GRANT	give permission
REVOKE	remove permission
USER	database account
ROLE	permission group

Create one user and one role
Grant select on flowers to the role
Assign the role to the user
Revoke the role and compare the result

SELECT flower_name, price
FROM flowers
WHERE price > 3.00;

flower_name	price
Rose	4.50
Tulip	3.20
Lily	5.00