My SQL Notebook

Introduction

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. This notebook serves as a living document for core concepts, syntax patterns, and best practices.

---

Core Relationships

In relational database design, relationships define how data in one table links to data in another. This is the “Relational” part of RDBMS (Relational Database Management System).

1. One-to-One (1:1)

A record in Table A is associated with exactly one record in Table B. This is often used to split large tables for performance or to isolate sensitive data.

• Example: A User and their User_Settings.
• Key Detail: The primary key of the parent table usually serves as both the primary key and foreign key in the child table.

CREATE TABLE Users (
    id INTEGER PRIMARY KEY,
    email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE User_Settings (
    user_id INTEGER PRIMARY KEY,
    send_emails BOOLEAN NOT NULL,
    theme_preference VARCHAR(20),
    FOREIGN KEY (user_id) REFERENCES Users(id)
);

2. One-to-Many (1:N)

The most common relationship type. A single record in Table A can be related to multiple records in Table B, but each record in Table B links back to only one in Table A.

• Example: One Department has many Employees.
• Key Detail: The “Many” side table contains a Foreign Key pointing to the “One” side.

Note: When deleting a record on the “One” side, consider using ON DELETE CASCADE if the “Many” side records shouldn’t exist without their parent.

CREATE TABLE Departments (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    manager VARCHAR(50) NOT NULL
);

CREATE TABLE Employees (
    id INTEGER PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary INTEGER NOT NULL,
    active BOOLEAN NOT NULL,
    department_id INTEGER REFERENCES Departments(id)
);

Note: The Foreign Key in Employees is currently nullable so the relationship isn’t strictly forced. That is a choice for you to make. Will there be employees that don’t belong to a department?

3. Many-to-Many (N:N)

Multiple records in Table A can be associated with multiple records in Table B.

• Example: Students and Courses. A student takes many courses, and a course has many students.
• Key Detail: This requires a junction-table to map the relationships.

CREATE TABLE Enrollments (
    student_id INTEGER,
    course_id INTEGER,
    enrolled_at TIMESTAMP NOT NULL,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (course_id) REFERENCES Courses(id)
);

Note: Relational databases cannot model many-to-many relationships directly without a junction-table.

---

Common SQL Snippets

1. Data Manipulation (DML)

These are the queries you’ll use most often to interact with the data inside your tables.

Adding Records

-- Inserting a single record
INSERT INTO Employees (first_name, last_name, department_id)
VALUES ('Jane', 'Doe', 1);

-- Inserting multiple records at once
INSERT INTO Departments (name, manager)
VALUES ('Engineering', 'Alice'), ('Marketing', 'Bob');

Retrieving Records

-- Select specific columns with a condition and sorting
SELECT first_name, last_name 
FROM Employees 
WHERE department_id = 1 
ORDER BY last_name ASC;

-- Using aliases for cleaner output
SELECT name AS Department, manager AS Lead 
FROM Departments;

This is the order of execution for all operations.

Order	Clause	Function
1	FROM & JOIN	Choose and join tables to get base data.
2	WHERE	Filters the base data.
3	GROUP BY	Aggregates the base data.
4	HAVING	Filters the aggregated data.
5	SELECT	Returns the final data.
6	ORDER BY	Sorts the final data.
7	LIMIT	Limits the returned data to a row count.

Modifying Records

Warning: Always use a WHERE clause with UPDATE. Forgetting it will update every row in the table!

UPDATE Employees 
SET last_name = 'Smith' 
WHERE id = 101;

Removing Records

DELETE FROM Employees WHERE id = 101;

---

2. Data Definition (DDL)

These snippets are for managing the structure of the database itself.

Changing Table Structure

Sometimes you need to add a column after the table is already created.

-- Adding a new column
ALTER TABLE Employees 
ADD COLUMN hired_date DATE;

-- Renaming a column (PostgreSQL syntax)
ALTER TABLE Departments 
RENAME COLUMN manager TO department_head;

Deleting Tables

-- Deletes the table and all its data permanently
DROP TABLE IF EXISTS Temporary_Logs;

---

3. Aggregation & Grouping

Used for reporting and getting insights from your data. This query retrieves a list of all departments that pay an average salary greater than 29,000 for their currently active staff. It calculates the total employee count and the mean salary (rounded to two decimal places) for each department, then sorts the list to show only the top three highest-paying departments.

SELECT 
    d.name AS department_name, 
    COUNT(e.id) AS staff_count, 
    ROUND(AVG(e.salary), 2) AS average_salary
FROM Departments d
JOIN Employees e ON d.id = e.department_id
WHERE e.active IS TRUE
GROUP BY d.id
HAVING average_salary > 29000
ORDER BY average_salary DESC
LIMIT 3;

Tip: Use HAVING to filter results after a GROUP BY. Use WHERE to filter rows before they are grouped.

Common Pitfalls

NULL Comparison

In SQL, NULL does not mean “empty” or “zero”. It means “unknown” or “missing”. Because of that, SQL treats NULL very differently from normal values.

When you write:

SELECT * FROM Employees WHERE department_id = NULL;

This will always return zero rows.

Because:

• If either side is NULL, the result is UNKNOWN, not TRUE
• Rows with NULL will not match

NULL = 5       -- UNKNOWN
NULL = NULL    -- UNKNOWN
TRUE = NULL    -- UNKNOWN
TRUE = TRUE    -- TRUE
TRUE = FALSE   -- FALSE

The correct way

SQL provides a special operator IS:

SELECT * FROM Employees WHERE department_id IS NULL;

This explicitly asks:

“Does this column have no value?”