SQL Quick Start + HTML Labs

Module 1: SQL Foundations and Table Setup

Create a Database and Table

CREATE DATABASE store;
USE store;

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price DECIMAL(10,2),
    category VARCHAR(30)
);

Insert Starter Data

INSERT INTO products VALUES
(1, 'Laptop', 999.99, 'Electronics'),
(2, 'Mouse', 25.50, 'Electronics'),
(3, 'Desk', 299.99, 'Furniture'),
(4, 'Chair', 199.99, 'Furniture'),
(5, 'Monitor', 349.99, 'Electronics');

Module 2: Single-Table Queries

Selection and Projection

-- All columns
SELECT * FROM products;

-- Specific columns (projection)
SELECT name, price FROM products;

Filtering with WHERE

SELECT * FROM products WHERE price < 300;
SELECT * FROM products WHERE category = 'Electronics' AND price < 500;

Pattern Matching with LIKE

SELECT * FROM products WHERE name LIKE 'M%';
SELECT * FROM products WHERE name LIKE '%top%';

DISTINCT and ORDER BY

SELECT DISTINCT category FROM products;
SELECT COUNT(DISTINCT category) AS unique_categories FROM products;

SELECT * FROM products ORDER BY price ASC;
SELECT * FROM products ORDER BY category, price DESC;

Module 3: Multi-Table Queries (Foreign Keys and Joins)

Define Parent/Child Tables

CREATE TABLE suppliers (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    country VARCHAR(30)
);

CREATE TABLE products_fk (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    price DECIMAL(10,2),
    supplier_id INT,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

Join Data Across Tables

SELECT
    p.name AS product_name,
    p.price,
    s.name AS supplier_name,
    s.country
FROM products_fk p
INNER JOIN suppliers s ON p.supplier_id = s.id;

SELECT
    p.name,
    s.name AS supplier_name
FROM products_fk p
LEFT JOIN suppliers s ON p.supplier_id = s.id;

Module 4: Set Operators and Nested Queries

UNION and UNION ALL

SELECT name FROM products WHERE category = 'Electronics'
UNION
SELECT name FROM products WHERE price > 500;

SELECT category FROM products WHERE price < 300
UNION ALL
SELECT category FROM products WHERE price > 200;

Nested Queries with IN / NOT IN

SELECT * FROM products_fk
WHERE supplier_id IN (
    SELECT id FROM suppliers WHERE country = 'USA'
);

SELECT * FROM products_fk
WHERE supplier_id NOT IN (
    SELECT id FROM suppliers WHERE country = 'USA'
);

EXISTS and NOT EXISTS

SELECT name FROM suppliers s
WHERE EXISTS (
    SELECT 1 FROM products_fk p
    WHERE p.supplier_id = s.id
);

SELECT name FROM suppliers s
WHERE NOT EXISTS (
    SELECT 1 FROM products_fk p
    WHERE p.supplier_id = s.id
);

Module 5: Aggregation, GROUP BY, HAVING

Core Aggregates

SELECT COUNT(*) AS total_products FROM products;
SELECT SUM(price) AS total_value FROM products;
SELECT AVG(price) AS average_price FROM products;
SELECT MAX(price) AS max_price, MIN(price) AS min_price FROM products;

Grouping and Group Filters

SELECT category, COUNT(*) AS item_count, AVG(price) AS avg_price
FROM products
GROUP BY category;

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 300;

Module 6: Advanced SQLizing (NULLs, Quantifiers, Outer Joins)

Quantifiers with ANY / ALL

-- More expensive than at least one furniture product
SELECT name, price
FROM products
WHERE price > ANY (
    SELECT price FROM products WHERE category = 'Furniture'
);

-- More expensive than every furniture product
SELECT name, price
FROM products
WHERE price > ALL (
    SELECT price FROM products WHERE category = 'Furniture'
);

NULL Handling

-- Never use "= NULL"
SELECT * FROM products WHERE category IS NULL;
SELECT * FROM products WHERE category IS NOT NULL;

SELECT
    id,
    COALESCE(category, 'Unknown') AS category_label
FROM products;

Outer Join and Missing Data

SELECT
    s.id,
    s.name,
    p.name AS product_name
FROM suppliers s
LEFT JOIN products_fk p ON p.supplier_id = s.id;

Quick Reference

Detailed HTML Labs in This Repo