SQL Fundamentals

Chapter 1 — What is SQL?

SQL (Structured Query Language) is the language for talking to databases. Almost every website, app, and business uses a database to store data — and SQL is how you get data in and out.

-- SQL databases (flavours — syntax is very similar):
SQLite    → lightweight, file-based, perfect for learning
MySQL     → most popular for web apps
PostgreSQL→ powerful, open-source
SQL Server→ Microsoft
Oracle    → enterprise

-- SQL can:
-- SELECT → read data
-- INSERT → add data
-- UPDATE → change data
-- DELETE → remove data
-- CREATE → create tables/databases
-- DROP   → delete tables/databases

Chapter 2 — Setting Up SQLite

# Install SQLite on Linux/Kali:
sudo apt install sqlite3 -y

# Open/create a database file:
sqlite3 mydb.db

# SQLite commands (start with dot):
.tables          → list all tables
.schema          → show table structure
.quit            → exit
.help            → show all commands
.headers on      → show column names in output
.mode column     → formatted output

-- SQL statements end with semicolon (;)
-- Comments start with -- (two dashes)

Chapter 3 — CREATE & DROP Tables

-- Create a table for storing users
CREATE TABLE users (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    name    TEXT    NOT NULL,
    email   TEXT    UNIQUE NOT NULL,
    age     INTEGER,
    created TEXT    DEFAULT (datetime('now'))
);

-- Column constraints:
-- PRIMARY KEY   → unique identifier for each row
-- AUTOINCREMENT → number increases automatically (1,2,3...)
-- NOT NULL      → value required (can't be empty)
-- UNIQUE        → no duplicate values allowed
-- DEFAULT       → value if none provided

-- Common data types:
INTEGER   → whole numbers (1, 42, -5)
REAL      → decimal numbers (3.14, 9.99)
TEXT      → text strings ("Alice", "hello@email.com")
BLOB      → binary data (files, images)
NULL      → no value

-- Delete a table (careful — no undo!):
DROP TABLE users;

-- Delete only if it exists (no error if missing):
DROP TABLE IF EXISTS users;

-- View table structure:
.schema users

Chapter 4 — INSERT Data

-- Insert one row (specify all columns):
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@email.com', 25);

-- Insert multiple rows at once:
INSERT INTO users (name, email, age) VALUES
    ('Bob',     'bob@email.com',   30),
    ('Charlie', 'charlie@email.com', 22),
    ('Diana',   'diana@email.com',  28),
    ('Eve',     'eve@email.com',    35);

-- If you insert all columns in order, you can skip the column list:
INSERT INTO users VALUES (NULL, 'Frank', 'frank@email.com', 27, datetime('now'));
-- NULL for id → AUTOINCREMENT fills it in

-- Insert from another table:
INSERT INTO users_backup SELECT * FROM users;

Chapter 5 — SELECT Queries

-- Get all rows, all columns:
SELECT * FROM users;

-- Get specific columns only:
SELECT name, email FROM users;
SELECT name, age FROM users;

-- Rename columns in output (alias):
SELECT name AS "Full Name", age AS "Years Old" FROM users;

-- Get distinct (unique) values:
SELECT DISTINCT age FROM users;

-- Count rows:
SELECT COUNT(*) FROM users;
SELECT COUNT(*) AS total_users FROM users;

-- Basic calculations:
SELECT name, age, age + 10 AS "Age in 10 years" FROM users;

Chapter 6 — WHERE Filtering

-- Filter rows with WHERE:
SELECT * FROM users WHERE age = 25;
SELECT * FROM users WHERE name = 'Alice';

-- Comparison operators:
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE age >= 25;
SELECT * FROM users WHERE age < 30;
SELECT * FROM users WHERE age != 25;
SELECT * FROM users WHERE age BETWEEN 20 AND 30;

-- Combine conditions:
SELECT * FROM users WHERE age > 20 AND age < 30;
SELECT * FROM users WHERE name = 'Alice' OR name = 'Bob';

-- Check for NULL:
SELECT * FROM users WHERE age IS NULL;
SELECT * FROM users WHERE age IS NOT NULL;

-- IN — match multiple values:
SELECT * FROM users WHERE name IN ('Alice', 'Bob', 'Charlie');

-- LIKE — pattern matching:
SELECT * FROM users WHERE email LIKE '%gmail.com';    -- ends with gmail.com
SELECT * FROM users WHERE name LIKE 'A%';            -- starts with A
SELECT * FROM users WHERE name LIKE '%li%';          -- contains "li"
-- % = wildcard (any characters)
-- _ = single character wildcard

-- NOT — reverse a condition:
SELECT * FROM users WHERE NOT age = 25;
SELECT * FROM users WHERE name NOT LIKE 'A%';

Chapter 7 — UPDATE & DELETE

-- Update one row (ALWAYS use WHERE or you update ALL rows!):
UPDATE users SET age = 26 WHERE name = 'Alice';

-- Update multiple columns:
UPDATE users
SET age = 26, email = 'newalice@email.com'
WHERE id = 1;

-- Update all rows (be careful!):
UPDATE users SET age = age + 1;   -- everyone gets +1 year

-- Delete specific rows (ALWAYS use WHERE!):
DELETE FROM users WHERE name = 'Bob';
DELETE FROM users WHERE age < 18;

-- Delete ALL rows (dangerous!):
DELETE FROM users;

-- Best practice: always SELECT first to verify before UPDATE/DELETE:
SELECT * FROM users WHERE name = 'Bob';   -- check what will be deleted
DELETE FROM users WHERE name = 'Bob';     -- then delete

Chapter 8 — ORDER BY & LIMIT

-- Sort results:
SELECT * FROM users ORDER BY name;            -- A → Z
SELECT * FROM users ORDER BY name ASC;        -- same as above
SELECT * FROM users ORDER BY name DESC;       -- Z → A
SELECT * FROM users ORDER BY age DESC;        -- oldest first
SELECT * FROM users ORDER BY age ASC;         -- youngest first

-- Sort by multiple columns:
SELECT * FROM users ORDER BY age DESC, name ASC;
-- (sort by age descending, then by name alphabetically for same age)

-- Limit results:
SELECT * FROM users LIMIT 5;               -- first 5 rows only
SELECT * FROM users LIMIT 5 OFFSET 10;    -- rows 11-15 (for pagination)

-- Top 3 oldest users:
SELECT name, age FROM users ORDER BY age DESC LIMIT 3;

Chapter 9 — Aggregate Functions

-- Aggregate functions calculate values from multiple rows
SELECT COUNT(*) FROM users;          -- total number of rows
SELECT COUNT(age) FROM users;        -- rows where age is NOT NULL
SELECT SUM(age) FROM users;          -- sum of all ages
SELECT AVG(age) FROM users;          -- average age
SELECT MIN(age) FROM users;          -- youngest
SELECT MAX(age) FROM users;          -- oldest

-- Multiple aggregates at once:
SELECT
    COUNT(*) AS total,
    AVG(age) AS average_age,
    MIN(age) AS youngest,
    MAX(age) AS oldest
FROM users;

-- String functions:
SELECT UPPER(name) FROM users;       -- ALICE, BOB...
SELECT LOWER(email) FROM users;      -- lowercase emails
SELECT LENGTH(name) FROM users;      -- character count
SELECT SUBSTR(name, 1, 3) FROM users; -- first 3 chars

Chapter 10 — GROUP BY & HAVING

-- GROUP BY groups rows with same value and lets you aggregate each group

-- Count users by age:
SELECT age, COUNT(*) AS count
FROM users
GROUP BY age;

-- Average salary by department (if we had a salary column):
SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS headcount
FROM employees
GROUP BY department;

-- HAVING filters groups (like WHERE but after GROUP BY):
SELECT age, COUNT(*) AS count
FROM users
GROUP BY age
HAVING count > 1;    -- only show ages with more than 1 person

-- WHERE vs HAVING:
-- WHERE filters ROWS (before grouping)
-- HAVING filters GROUPS (after grouping)

SELECT age, COUNT(*) AS count
FROM users
WHERE age > 18         -- filter rows first
GROUP BY age
HAVING count > 1;      -- then filter groups

Chapter 11 — JOINs

-- Create tables to practice JOINs:
CREATE TABLE orders (
    id         INTEGER PRIMARY KEY,
    user_id    INTEGER,
    product    TEXT,
    amount     REAL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

INSERT INTO orders (user_id, product, amount) VALUES
    (1, 'Laptop',  999.99),
    (1, 'Mouse',    29.99),
    (2, 'Keyboard', 79.99),
    (4, 'Monitor', 349.99);

-- INNER JOIN — only rows that match in BOTH tables:
SELECT users.name, orders.product, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;
-- Only users who have placed orders

-- LEFT JOIN — ALL rows from left table, matching from right:
SELECT users.name, orders.product
FROM users
LEFT JOIN orders ON users.id = orders.user_id;
-- ALL users, orders are NULL for those with no orders

-- Aliases for cleaner queries:
SELECT u.name, o.product, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100
ORDER BY o.amount DESC;

-- Join summary:
INNER JOIN → only matching rows (intersection)
LEFT JOIN  → all left + matching right (NULLs for no match)
RIGHT JOIN → all right + matching left (not in SQLite)
FULL JOIN  → everything from both tables

Chapter 12 — Real Project: Student Database

-- Build a student grade tracking system

CREATE TABLE students (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    name    TEXT NOT NULL,
    email   TEXT UNIQUE,
    grade   TEXT DEFAULT 'Active'
);

CREATE TABLE courses (
    id   INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    code TEXT UNIQUE
);

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id  INTEGER,
    score      REAL,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id)  REFERENCES courses(id)
);

-- Add data:
INSERT INTO students (name, email) VALUES
    ('Uzair', 'uzair@example.com'),
    ('Sara',  'sara@example.com'),
    ('Ahmed', 'ahmed@example.com');

INSERT INTO courses (name, code) VALUES
    ('Python Programming', 'PY101'),
    ('Cybersecurity',      'CS201'),
    ('Networking',         'NET301');

INSERT INTO enrollments VALUES
    (1, 1, 95), (1, 2, 88),
    (2, 1, 72), (2, 3, 90),
    (3, 2, 85), (3, 3, 78);

-- Query: each student's average score:
SELECT s.name, ROUND(AVG(e.score), 2) AS average
FROM students s
JOIN enrollments e ON s.id = e.student_id
GROUP BY s.id
ORDER BY average DESC;

-- Query: best student per course:
SELECT c.name AS course, s.name AS top_student, MAX(e.score) AS score
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses  c ON c.id = e.course_id
GROUP BY c.id;