zaine/vault

Fork 0

Files

Zaine 129ce1442b

Build Quartz Notes / build (push) Failing after 20s

Details

2026-07-13 09:16:09 +01:00

21 KiB

Executable File

Raw Permalink Blame History

note type, date, done, link

note type

date

done

link

sql

database

theory

2026-06-03

true

https://app.pluralsight.com/ilx/video-courses/sql-joins-constraints-normalization-subqueries/course-overview

Common Aggregate Functions

title	cost	duration
Gone with the wind	390000	220
Frankenstein	3000000	50
Creature from the black lagoon	500000	79
NULL	100	10

1. COUNT()

Count all rows

SELECT COUNT(*) FROM Movies;

Output:

Counts all rows, including rows where columns are NULL.

Count non-NULL values in a column

SELECT COUNT(title) FROM Movies;

Output:

NULL title is not counted.

2. MIN()

SELECT MIN(cost) FROM Movies;

Output:

Returns the smallest value in the column.

3. MAX()

SELECT MAX(cost) FROM Movies;

Output:

Returns the largest value.

4. SUM()

SELECT SUM(cost) FROM Movies;

Output:

Calculation:

390000 + 3000000 + 500000 + 100 = 3890100

Adds all values in the column.
Ignores NULL values (none in cost here).

5. AVG()

SELECT AVG(cost) FROM Movies;

Output:

Calculation:

3890100 / 4 = 972525

Computes the average of all values.

Using Multiple Aggregates

SELECT

    COUNT(*) AS total_movies,

    MIN(cost) AS cheapest,

    MAX(cost) AS most_expensive,

    SUM(cost) AS total_cost,

    AVG(cost) AS average_cost

FROM Movies;

Output:

total_movies | cheapest | most_expensive | total_cost | average_cost
4            | 100      | 3000000        | 3890100    | 972525

Filtering Aggregates

title	cost	duration	genre
Gone with the wind	390000	220	Drama
Frankenstein	3000000	50	Horror
Creature from the black lagoon	500000	79	Horror
Casablanca	1000000	102	Drama
Toy Story	2000000	81	Family
NULL	100	10	Horror

GROUP BY

GROUP BY groups rows so aggregates are calculated per group rather than across the whole table.

SELECT genre, SUM(cost) AS total_cost
FROM Movies
GROUP BY genre;

Output:

genre   | total_cost
Drama   | 1390000
Horror  | 3500100
Family  | 2000000

SELECT genre, AVG(duration) AS avg_duration
FROM Movies
GROUP BY genre;

Output:

genre   | avg_duration
Drama   | 161
Horror  | 46.33
Family  | 81

WHERE (filtering rows before aggregation)

WHERE filters rows before grouping and aggregation.

SELECT genre, COUNT(*) AS num_movies
FROM Movies
WHERE duration > 60
GROUP BY genre;

Output:

genre   | num_movies
Drama   | 2
Horror  | 1
Family  | 1

Explanation:

The row with duration = 10 is excluded before grouping.

HAVING (filtering groups after aggregation)

HAVING filters results after aggregation.

SELECT genre, SUM(cost) AS total_cost
FROM Movies
GROUP BY genre
HAVING SUM(cost) >= 2000000;

Output:

genre   | total_cost
Horror  | 3500100
Family  | 2000000   -- note: included only if >= was used

If strictly > 2000000, only:

Horror  | 3500100

SELECT genre, COUNT(*) AS num_movies
FROM Movies
GROUP BY genre
HAVING COUNT(*) > 2;

Output:

genre   | num_movies
Horror  | 3

Combined Example

SELECT genre, AVG(cost) AS avg_cost
FROM Movies
WHERE duration > 60
GROUP BY genre
HAVING AVG(cost) > 1000000;

Steps:

WHERE filters rows (duration > 60)
GROUP BY groups remaining data
HAVING filters grouped results

Output:

genre   | avg_cost
Family  | 2000000

Constraints

Example Table (Promotions)

CREATE TABLE Promotions (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    CONSTRAINT unique_name_category
        UNIQUE (name, category)
);

Purpose of Constraints

Prevent invalid or unwanted data
Enforce rules on table columns
Improve data integrity

NOT NULL

Prevents a column from storing NULL values
Use when a value is required

name VARCHAR(100) NOT NULL

UNIQUE

Ensures all values in a column are distinct
Prevents duplicate entries

name VARCHAR(100) UNIQUE

Composite UNIQUE

Enforces uniqueness across multiple columns

UNIQUE (name, category)

Same name allowed if category differs
Same category allowed if name differs
Duplicate combinations are not allowed

Multiple Constraints on a Column

You can combine constraints:

name VARCHAR(100) NOT NULL UNIQUE

Named Constraints

Assign custom names for easier maintenance

CONSTRAINT unique_name UNIQUE (name)

Column vs Table Constraints

Column constraint: defined inline
Table constraint: defined separately

Both work the same, except:

NOT NULL must be defined at the column level

PRIMARY KEY

Uniquely identifies each row
Automatically enforces:
- NOT NULL
- UNIQUE

id INT PRIMARY KEY

Key Rules

Only one primary key per table
Can consist of one or multiple columns

PRIMARY KEY vs UNIQUE + NOT NULL

Both enforce uniqueness and no nulls
Difference:
- PRIMARY KEY: only one per table
- UNIQUE + NOT NULL: can use multiple columns

Value Constraints (Foreign Key & CHECK)

Example Tables (Movies + Promotions)

CREATE TABLE Movies (
    id INT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    duration INT CHECK (duration > 0)
);

CREATE TABLE Promotions (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    movie_id INT,
    CONSTRAINT fk_movie
        FOREIGN KEY (movie_id)
        REFERENCES Movies(id),
    CONSTRAINT unique_name_category
        UNIQUE (name, category)
);

Foreign Key (FK)

Definition

A foreign key is a column in one table that references a primary key in another table
Used to link tables and avoid duplicating data

movie_id INT REFERENCES movies(id)

This is the same as:

movie_id INT REFERENCES movies

Naming Convention

Format: referencedTable_singular + _id

Examples:

movie_id → references Movies(id)
user_id → references Users(id)

Why Use Foreign Keys

Prevents invalid references
Ensures data integrity
Avoids duplicating data across tables

Behaviour Without FK

You can insert invalid data:

movie_id = 999 -- even if it doesn't exist

Creates bad data

Behaviour With FK

Database blocks invalid inserts

INSERT INTO Promotions (id, name, category, movie_id) VALUES (1, 'Half Off', 'Discount', 999); -- fails

Error: violates foreign key constraint

Table Creation Order Rule

The referenced table must be created first

CREATE TABLE Movies (...)

CREATE TABLE Promotions (... REFERENCES Movies)

Table Constraint Version

FOREIGN KEY (movie_id) REFERENCES movies

Orphan Records

Definition

A row that references data that no longer exists

Example:

Movie deleted
Promotion still points to that movie_id

How FK Prevents Orphans

Prevents deleting parent rows if children exist

DELETE FROM Movies WHERE id = 6; -- fails if Promotions reference it

You must:
1. Delete child rows first
2. Then delete parent

Dropping Tables

Cannot drop a table if another table depends on it

DROP TABLE Movies; -- fails if Promotions references it

Must drop dependent tables first

CHECK Constraint

Definition

Validates column values using a condition

salary int CHECK (salary > 500)

Purpose of CHECK

Prevent logically invalid data

Example:

Duration cannot be negative

Behaviour

INSERT INTO Movies (id, title, duration)
VALUES (1, 'Test Movie', -10);  -- fails

Example Inserts

Valid Movie

INSERT INTO Movies (id, title, duration)
VALUES (1, 'Gone With the Wind', 240);

Valid Promotion

INSERT INTO Promotions (id, name, category, movie_id)
VALUES (1, 'Matinee', 'Discount', 1);

Fails (invalid foreign key)

INSERT INTO Promotions (id, name, category, movie_id)
VALUES (2, 'Half Off', 'Discount', 999);

Fails (negative duration)

INSERT INTO Movies (id, title, duration)
VALUES (2, 'Bad Movie', -10);

SQL Relationships & Normalization (Level 3 Notes)

Overview

Focus: Database relationships and normalization to improve data integrity and flexibility.
Example context: A Movies application where each movie can have multiple genres.

Problem with Current Design

Storing multiple genres in one column (e.g., "Adventure, Fantasy") causes issues:
- Hard to query (e.g., finding all Adventure movies).
- Difficult to update individual values.
- Violates normalization rules.

Example Issue

SELECT * FROM Movies WHERE genre = 'Adventure';

Returns movies with only "Adventure", but misses movies like "Adventure, Fantasy".

Normalization Basics

First Normal Form (1NF)

Rule: No repeating groups in a column.
Each field should contain a single value (atomic).

Fix

Split rows so each genre is separate:

title	genre	duration
Peter Pan	Adventure	120
Peter Pan	Fantasy	120

Eliminates multi-value columns.
Still problematic: duplicate movie data.

Second Normal Form (2NF)

Rule: No redundancy (no repeated unnecessary data).
Each piece of information should be stored once.

Problem in 1NF Table

Movie details (e.g., duration) are duplicated across rows.

Solution: Table Decomposition

Step 1: Create a Movies Table

Store unique movies only.

id	title	duration
1	Don Juan	110
2	Peter Pan	120

Step 2: Create a Genres Table

Store each genre once.

id	name
1	Romance
2	Adventure
3	Fantasy

Step 3: Create a Join Table (Many-to-Many)

Name convention: movies_genres
Purpose: Link movies to genres.

movie_id	genre_id
1	1
2	2
2	3

movie_id → references Movies.id
genre_id → references Genres.id
Both are foreign keys

Benefits of This Design

No duplication (meets 2NF).
Easy updates:
- Change movie duration in one place.
- Add/remove genres without affecting other data.
Scalable for complex relationships.

Querying the Data

Step-by-step (manual approach)

Get movie ID:

SELECT id FROM Movies WHERE title = 'Peter Pan';

Get associated genre IDs:

SELECT genre_id FROM movies_genres WHERE movie_id = 2;

Get genre names:

SELECT name FROM Genres WHERE id IN (2, 3);

Simplified Query Using `IN`

SELECT name FROM Genres WHERE id IN (2, 3);

Example Operation

Add a new genre to a movie

Add "Fantasy" to "Robin Hood":
INSERT INTO movies_genres (movie_id, genre_id)
VALUES (4, 3);

Key Takeaways

Avoid storing multiple values in a single column.
Use normalization to:
- Eliminate redundancy
- Improve data consistency
Use join tables for many-to-many relationships.
Trade-off: Queries become slightly more complex, but data becomes more robust and flexible.

Database Relationships

Overview

There are three fundamental relationship types between tables:

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

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

Definition

A single row in Table A can relate to multiple rows in Table B.
A row in Table B relates to only one row in Table A.

Key Implementation

Add a foreign key in the "many" table.

Example

Movies → Promotions
- One movie can have many promotions.
- Each promotion belongs to one movie.

Movies (id) ← Promotions (movie_id)

Diagram Representation

1 on the "one" side
* on the "many" side

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

Definition

Multiple rows in Table A can relate to multiple rows in Table B.

Key Implementation

Requires a join table (junction table) that holds foreign keys from both tables.

Example

Movies ↔ Genres
- A movie can have many genres.
- A genre can belong to many movies.

Movies_Genres
- movie_id
- genre_id

Diagram Representation

* on both sides
Join table is typically implied (not always shown explicitly)

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

Definition

A row in Table A relates to exactly one row in Table B.

Use Case

Used to split large or complex tables into smaller ones.

Example

Customers ↔ Addresses
- Each customer has one address.
- Each address belongs to one customer.

Customers (address_id) → Addresses (id)

Diagram Representation

1 on both sides

Relationship Identification Examples

Example 1: Movies & Reviews

One movie can have many reviews.
Each review belongs to one movie.

Relationship: One-to-Many

Movies (id) ← Reviews (movie_id)

Example 2: Movies & Promotions

A promotion can apply to many movies.
A movie can have many promotions.

Relationship: Many-to-Many

Movies_Promotions
- movie_id
- promotion_id

Summary

Relationship Type	Key Idea	Implementation
One-to-One	1 row ↔ 1 row	Foreign key on one side
One-to-Many	1 row → many rows	Foreign key in "many" table
Many-to-Many	Many ↔ many	Join table with two FKs

Always identify how entities relate from a business perspective, not just technically.
Use:
- Foreign keys for 1:1 and 1:N
- Join tables for N:N
Diagrams use:
- 1 = single
- * = many

SQL INNER JOIN — Explained with an Example

Example Tables

Movies

id | title
---|-------------
1  | Don Juan
2  | The Lost World
3  | Peter Pan
4  | Robin Hood

Reviews

id | movie_id | body
---|----------|-------------------
1  | 1        | Great movie
2  | 1        | Loved it
3  | 1        | Classic
4  | 2        | Not bad
5  | NULL     | Random review

What INNER JOIN Does

Returns only rows where both tables match.

Ignores:
- Movies with no reviews
- Reviews with no valid movie

Basic Query

SELECT m.title, r.body

FROM Movies m

INNER JOIN Reviews r

ON m.id = r.movie_id;

Result (conceptually)

Don Juan       | Great movie
Don Juan       | Loved it
Don Juan       | Classic
The Lost World | Not bad

Key Observations

Only matching data is returned
Don Juan appears 3 times (3 reviews)
Peter Pan is missing (no reviews)
Random review is missing (movie_id = NULL)

Why Use INNER JOIN

Instead of doing this:

-- Query 1

SELECT movie_id, body FROM Reviews;

-- Query 2

SELECT title FROM Movies WHERE id IN (1, 2);

You can do it in one query:

SELECT m.title, r.body

FROM Movies m

INNER JOIN Reviews r

ON m.id = r.movie_id;

Cleaner and more efficient.

Table Order

These are equivalent:

FROM Movies m

INNER JOIN Reviews r

ON m.id = r.movie_id;

FROM Reviews r

INNER JOIN Movies m

ON m.id = r.movie_id;

Because INNER JOIN only keeps matches.

Selecting Specific Columns

Avoid SELECT *:

SELECT m.title, r.body

FROM Movies m

INNER JOIN Reviews r

ON m.id = r.movie_id;

This keeps output clean and avoids ambiguity.

INNER JOIN with Multiple Tables

Example: Add Genres

Additional Tables

Movies_Genres
movie_id | genre_id
---------|----------
1        | 10
1        | 11
3        | 12

Genres
id | name
---|---------
10 | Drama
11 | Romance
12 | Fantasy

Query

SELECT m.title, g.name

FROM Movies m

INNER JOIN Movies_Genres mg

  ON m.id = mg.movie_id

INNER JOIN Genres g

  ON mg.genre_id = g.id;

Result (conceptually)

Don Juan   | Drama
Don Juan   | Romance
Peter Pan  | Fantasy

Mental Model

Think of INNER JOIN as:

“Give me only rows that exist in both tables”

Behaviour Summary

Situation	Included?
Movie with reviews	Yes
Movie with no reviews	No
Review with no movie	No

One-Line Summary

INNER JOIN = only matching rows between tables

SQL Aliases (Columns & Tables)

Column Aliases

Column aliases allow you to rename output column headers in your query results.
Useful for making results more readable and user-friendly.

Syntax

SELECT column_name AS alias_name
FROM table_name;

The keyword AS is optional:

SELECT column_name alias_name
FROM table_name;

Examples

SELECT Movies.title AS films, Reviews.review AS reviews
FROM Movies
INNER JOIN Reviews ON Movies.id = Reviews.movie_id;

Without AS:

SELECT Movies.title films, Reviews.review reviews
FROM Movies
INNER JOIN Reviews ON Movies.id = Reviews.movie_id;

Using Multiple Words in Aliases

Use quotation marks when the alias contains spaces:

SELECT Movies.title AS "Weekly Movies", Reviews.review AS "Weekly Reviews"
FROM Movies
INNER JOIN Reviews ON Movies.id = Reviews.movie_id;

Table Aliases

Table aliases shorten table names in queries.
Helpful when:
- Working with long table names
- Writing complex joins
- Improving readability

Syntax

FROM table_name alias

Example

SELECT m.title
FROM Movies m;

Here, m is used instead of Movies.

Using Table Aliases in Joins

Once defined, aliases can be used throughout the query (SELECT, JOIN, WHERE, ORDER BY).

Example

SELECT m.title, r.review
FROM Movies m
INNER JOIN Reviews r ON m.id = r.movie_id;

Example

SELECT m.title, g.name
FROM Movies m
INNER JOIN Movies_Genres mg ON m.id = mg.movie_id
INNER JOIN Genres g ON mg.genre_id = g.id;

SQL Outer Joins

Example Tables

Movies

id | title
---|-------------
1  | Don Juan
2  | The Lost World
3  | Peter Pan
4  | Robin Hood

Reviews

id | movie_id | body
---|----------|-------------------
1  | 1        | Great movie
2  | 1        | Loved it
3  | 1        | Classic
4  | 2        | Not bad
5  | NULL     | Random review

LEFT OUTER JOIN

What it means

“Show me all movies, and any reviews they have.”

Query

SELECT m.title, r.body

FROM Movies m

LEFT JOIN Reviews r

ON m.id = r.movie_id;

Result (conceptually)

Don Juan        | Great movie
Don Juan        | Loved it
Don Juan        | Classic
The Lost World  | Not bad
Peter Pan       | NULL
Robin Hood      | NULL

Key idea

Every movie is shown
If a movie has no reviews → NULL
If a movie has many reviews → it repeats

Example:

Don Juan appears 3 times
Peter Pan appears once with NULL

How to remember

LEFT JOIN = “Keep everything on the left (Movies)”

RIGHT OUTER JOIN

What it means

“Show me all reviews, even if they don’t belong to a movie.”

Query

SELECT m.title, r.body

FROM Movies m

RIGHT JOIN Reviews r

ON m.id = r.movie_id;

Result (conceptually)

Don Juan       | Great movie
Don Juan       | Loved it
Don Juan       | Classic
The Lost World | Not bad
NULL           | Random review

Key idea

Every review is shown
If a review has no movie → NULL in movie column

Example:

The Random review has no movie → title is NULL

How to remember

RIGHT JOIN = “Keep everything on the right (Reviews)”

Side-by-Side

Situation	LEFT JOIN	RIGHT JOIN
Movie with no reviews	shown (NULL review)	not shown
Review with no movie	not shown	shown (NULL movie)

Quick Mental Model

LEFT JOIN → “I care about Movies”
RIGHT JOIN → “I care about Reviews”
NULL = missing relationship

One-Line Summary

LEFT JOIN → keep all rows from first table
RIGHT JOIN → keep all rows from second table

SQL Subqueries (Level 5)

Example Tables

Movies

id	title	sales	duration
1	Batman	50000	120
2	Superman	30000	110
3	Iron Man	25000	105
4	Robin Hood	45000	140

Promotions

id	movie_id	category
1	1	non-cash
2	2	cash
3	4	non-cash

What is a Subquery?

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

Example 1: Using `IN` with a Subquery

Goal

Find the sum of sales for movies with a non-cash promotion.

SELECT SUM(sales)

FROM Movies

WHERE id IN (

    SELECT movie_id

    FROM Promotions

    WHERE category = 'non-cash'

);

Inner query returns: 1, 4
Outer query sums: 50000 + 45000 = 95000

Alternative: Using a Join

SELECT SUM(sales)

FROM Movies

INNER JOIN Promotions

  ON Movies.id = Promotions.movie_id

WHERE category = 'non-cash';

Same result, different approach
Join is typically more performant

Subquery Variants

SELECT *

FROM Movies

WHERE id NOT IN (

    SELECT movie_id

    FROM Promotions

);

Returns movies with no promotions

Example 2: Aggregate Subquery

Goal

Find movies with above-average duration

SELECT *

FROM Movies

WHERE duration > (

    SELECT AVG(duration)

    FROM Movies

);

Average duration = (120 + 110 + 105 + 140) / 4 = 118.75
Result: only Robin Hood (140)

Key Takeaway

Subqueries break problems into steps using intermediate results
Use them when aggregates or filtering logic can’t be expressed directly in WHERE

21 KiB Executable File Raw Permalink Blame History Unescape Escape

Common Aggregate Functions

1. COUNT()

Count all rows

Count non-NULL values in a column

2. MIN()

3. MAX()

4. SUM()

5. AVG()

Using Multiple Aggregates

Filtering Aggregates

GROUP BY

WHERE (filtering rows before aggregation)

HAVING (filtering groups after aggregation)

Combined Example

Constraints

Example Table (Promotions)

Purpose of Constraints

NOT NULL

UNIQUE

Composite UNIQUE

Multiple Constraints on a Column

Named Constraints

Column vs Table Constraints

PRIMARY KEY

Key Rules

PRIMARY KEY vs UNIQUE + NOT NULL

Value Constraints (Foreign Key & CHECK)

Example Tables (Movies + Promotions)

Foreign Key (FK)

Definition

Naming Convention

Why Use Foreign Keys

Behaviour Without FK

Behaviour With FK

Table Creation Order Rule

Table Constraint Version

Orphan Records

Definition

How FK Prevents Orphans

Dropping Tables

CHECK Constraint

Definition

Purpose of CHECK

Behaviour

Example Inserts

Valid Movie

Valid Promotion

Fails (invalid foreign key)

Fails (negative duration)

SQL Relationships & Normalization (Level 3 Notes)

Overview

Problem with Current Design

Example Issue

Normalization Basics

First Normal Form (1NF)

Fix

Second Normal Form (2NF)

Problem in 1NF Table

Solution: Table Decomposition

Step 1: Create a Movies Table

Step 2: Create a Genres Table

Step 3: Create a Join Table (Many-to-Many)

Benefits of This Design

Querying the Data

Step-by-step (manual approach)

Simplified Query Using IN

Example Operation

Add a new genre to a movie

Key Takeaways

Database Relationships

Overview

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

Definition

Key Implementation

Example

Diagram Representation

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

Definition

Key Implementation

21 KiB

Executable File

Raw Permalink Blame History

Simplified Query Using `IN`

Example 1: Using `IN` with a Subquery