SQL & Relational Databases

Complete Study Notes

Based on SQL Tutorial  Full Database Course for Beginners

[Link] · Instructor: Mike Dane

Topics Covered
• Databases & RDBMS • Tables, Keys & Constraints
• SQL Fundamentals • CRUD Operations
• Queries & Filtering • Aggregate Functions
• Wildcards & UNION • JOINs (all types)
• Nested Queries • ON DELETE rules
• Triggers • ER Diagrams & Schema Design

Extended with additional examples, explanations, and practice questions.

SQL & Database  Complete Notes freeCodeCamp · Mike Dane

Contents

1 Introduction to Databases 4
1.1 What is a Database? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
1.2 Database Management Systems (DBMS) . . . . . . . . . . . . . . . . . . . 4
1.3 Relational vs. Non-Relational Databases . . . . . . . . . . . . . . . . . . . 4
1.4 CRUD  The Four Core Operations . . . . . . . . . . . . . . . . . . . . . 4

2 Tables and Keys 5

2.1 Tables: The Fundamental Structure . . . . . . . . . . . . . . . . . . . . . . 5
2.2 Primary Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2.3 Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
2.4 Other Key Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6

3 SQL Basics 6
3.1 What is SQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.2 SQL Sub-languages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
3.3 SQL Data Types (MySQL) . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

4 Creating and Managing Databases & Tables 7

4.1 Database Commands . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
4.2 Creating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
4.3 Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
4.4 Altering and Dropping Tables . . . . . . . . . . . . . . . . . . . . . . . . . 9

5 Inserting, Updating, and Deleting Data 9

5.1 INSERT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
5.2 UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
5.3 DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

6 Basic Queries (SELECT) 10

6.1 SELECT Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
6.2 Filtering with WHERE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
6.3 Sorting with ORDER BY . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
6.4 LIMIT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

7 The Company Database 12

7.1 Schema Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
7.2 Creating the Company Database . . . . . . . . . . . . . . . . . . . . . . . 12

8 Aggregate Functions 14
8.1 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
8.2 GROUP BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
8.3 HAVING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
8.4 Full SELECT Query Order . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

9 Wildcards 15
1
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

9.1 The LIKE Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

10 UNION 16
10.1 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

11 JOINs 17
11.1 What is a JOIN? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
11.2 Types of JOINs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
11.3 JOIN Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
11.4 Multiple JOINs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

12 Nested Queries (Subqueries) 18

12.1 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

13 ON DELETE Rules 19
13.1 Referential Integrity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

14 Triggers 20
14.1 What is a Trigger? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
14.2 Trigger Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
14.3 Trigger Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21

15 ER Diagrams 22
15.1 Entity-Relationship Model . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
15.2 ER Diagram Elements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
15.3 Relationship Cardinality . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
15.4 Weak Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
15.5 Designing an ER Diagram: Step-by-Step . . . . . . . . . . . . . . . . . . . 22

16 Converting ER Diagrams to Schemas 23

16.1 Mapping Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
16.2 Junction Table Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

17 Extended Topics 24
17.1 String Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
17.2 Date and Time Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
17.3 CASE Expression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
17.4 Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
17.5 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
17.6 Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25

18 Quick Reference 25
18.1 Most-Used Commands at a Glance . . . . . . . . . . . . . . . . . . . . . . 25

19 Practice Questions 26
19.1 Section 1: Database Fundamentals . . . . . . . . . . . . . . . . . . . . . . 26
19.2 Section 2: DDL  Creating Tables . . . . . . . . . . . . . . . . . . . . . . 26
19.3 Section 3: DML  Inserting, Updating, Deleting . . . . . . . . . . . . . . 27
19.4 Section 4: SELECT Queries . . . . . . . . . . . . . . . . . . . . . . . . . . 27
19.5 Section 5: Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . 27

2
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

19.6 Section 6: JOINs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

19.7 Section 7: Nested Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
19.8 Section 8: Wildcards and UNION . . . . . . . . . . . . . . . . . . . . . . . 28
19.9 Section 9: Triggers and Views . . . . . . . . . . . . . . . . . . . . . . . . . 28
19.10Section 10: ER Diagrams and Design . . . . . . . . . . . . . . . . . . . . . 28
19.11Challenge Questions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29

3
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

1 Introduction to Databases

1.1 What is a Database?

Denition: Database
Adatabase is any collection of related information stored and organized so it can be
easily accessed, managed, and updated.

Databases are everywhere: phone contacts, social media posts, bank transactions, e-
commerce orders. Even a simple written list in a notebook is technically a database. The
need for computers is to manage large, structured collections of information eciently.

1.2 Database Management Systems (DBMS)

A Database Management System (DBMS) is software that allows users to interact
with a database  creating, reading, updating, and deleting data. Common DBMS
examples include:

DBMS Type Use Case

MySQL Relational Web applications
PostgreSQL Relational Enterprise / analytics
SQLite Relational Embedded / mobile
Oracle DB Relational Large enterprise
MongoDB Non-relational (Document) Flexible schema apps
Redis Non-relational (Key-Value) Caching
Cassandra Non-relational (Column) Big data

1.3 Relational vs. Non-Relational Databases

Feature Relational (SQL) Non-Relational (NoSQL)

Structure
Schema
Tables with rows & columns Documents, key-value, graphs

Query lang
Fixed, predened Flexible / dynamic

Scaling
SQL (standardized) Varies by system

ACID
Vertical (mostly) Horizontal (mostly)

Best for
Strong guarantees Often relaxed
Structured, consistent data Unstructured / large-scale data

Note: This course focuses entirely on Relational Databases using MySQL . SQL
(Structured Query Language) is the standard language for all relational databases.

1.4 CRUD  The Four Core Operations

Every database interaction falls into one of four categories:

4
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

Letter Operation SQL Command Description

C Create INSERT Add new data
R Read SELECT Retrieve data
U Update UPDATE Modify existing data
D Delete DELETE / DROP Remove data

2 Tables and Keys

2.1 Tables: The Fundamental Structure

A relational database stores data in tables (also called relations ). Each table:

ˆ name
Has a unique .

ˆ columns
Contains (attributes/elds) with specic data types.

ˆ rows
Contains (records/tuples) of actual data.

Example  Student table:

student_id name major gpa
1 Alice Biology 3.8
2 Bob Chemistry 3.2
3 Claire Biology 3.9

2.2 Primary Keys

Denition: Primary Key
Aprimary key is a column (or set of columns) whose value uniquely identies each
row in a table.

Rules for primary keys:

ˆ Must be unique  no two rows share the same primary key value.

ˆ Must be NOT NULL  every row must have a value.

ˆ one
A table can have only primary key.

ˆ Usually an integer, often auto-incremented (AUTO_INCREMENT).

Types of Primary Keys:

ˆ Surrogate key : An articial key with no real-world meaning (e.g., student_id =
1, 2, 3... ).

ˆ Natural key : A key that has real-world meaning (e.g., a Social Security Number,
ISBN).

5
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

ˆ Composite key : A primary key composed of two or more columns together (neither
column alone is unique).

2.3 Foreign Keys

Denition: Foreign Key
A foreign key is a column in one table that references the primary key of another
table. It creates a link (relationship) between tables.

1 -- branch_id in employee references branch(branch_id)

2 CREATE TABLE employee (
3 emp_id INT PRIMARY KEY,
4 name VARCHAR(40),
5 branch_id INT,
6 FOREIGN KEY (branch_id) REFERENCES branch(branch_id)
7 ON DELETE SET NULL
8 );
Listing 1: Foreign key example

Key concept: The foreign key column in the child table must contain only values that
exist in the referenced (parent) table, or NULL.

2.4 Other Key Types

ˆ Unique Key : Enforces uniqueness but allows one NULL value; a table can have
multiple unique keys.

ˆ Candidate Key : Any column (or combination) that could serve as a primary key.

ˆ Super Key : Any set of columns that uniquely identies a row (includes candidate
keys and their supersets).

3 SQL Basics

3.1 What is SQL?

SQL (Structured Query Language) is the standard language for managing relational
databases. It is:

ˆ Declarative : You describe what you want, not how to get it.

ˆ Standardized : Core SQL works across MySQL, PostgreSQL, Oracle, SQL Server,
etc.

ˆ Case-insensitive for keywords (by convention, keywords are written in UPPER-

CASE).

6
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

3.2 SQL Sub-languages

Sub-language Full Name Commands
DDL Data Denition Language CREATE, ALTER, DROP, TRUNCATE
DML Data Manipulation Language INSERT, SELECT, UPDATE, DELETE
DCL Data Control Language GRANT, REVOKE
TCL Transaction Control Language COMMIT, ROLLBACK, SAVEPOINT

3.3 SQL Data Types (MySQL)

Type Category Description
INT Integer Whole number −231 to 231 − 1
BIGINT Integer Large whole number
TINYINT Integer 0255 (often used for booleans)
DECIMAL(p,s) Decimal Exact decimal, p total digits, s af-
ter point
FLOAT Float Approximate oating-point
DOUBLE Float Double-precision oat
VARCHAR(n) String Variable-length string, max n
characters
CHAR(n) String Fixed-length string of exactly n
characters
TEXT String Long text (up to 65,535 chars)
LONGTEXT String Very long text (up to 4 GB)
DATE Date/Time YYYY-MM-DD
DATETIME Date/Time YYYY-MM-DD HH:MM:SS
TIMESTAMP Date/Time Like DATETIME, tracks time-
zone
BOOLEAN Boolean TRUE/FALSE (stored as 1/0)
BLOB Binary Binary large object
ENUM(...) Enum One value from a predened list

4 Creating and Managing Databases & Tables

4.1 Database Commands

1 -- Create a new database
2 CREATE DATABASE company;
3
4 -- Select a database to use
5 USE company;
6
7 -- List all databases

7
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

8 SHOW DATABASES;
9
10 -- Delete a database (irreversible!)
11 DROP DATABASE company;
Listing 2: Basic database management

4.2 Creating Tables

1 CREATE TABLE table_name (
2 column1 datatype [constraints],
3 column2 datatype [constraints],
4 ...
5 [table_constraints]
6 );
Listing 3: CREATE TABLE syntax

Full example:
1 CREATE TABLE student (
2 student_id INT AUTO_INCREMENT PRIMARY KEY,
3 name VARCHAR(20) NOT NULL,
4 major VARCHAR(20) DEFAULT 'Undecided',
5 gpa DECIMAL(3,2)
6 );
Listing 4: Creating a student table

4.3 Constraints
Constraint Meaning
NOT NULL The column cannot contain a NULL value
UNIQUE All values in the column must be distinct
PRIMARY KEY Combines NOT NULL + UNIQUE; uniquely iden-
ties each row
FOREIGN KEY Value must exist in the referenced table's primary
key
DEFAULT value Sets a default value when none is provided
AUTO_INCREMENT Automatically increments an integer value
(MySQL)
CHECK (expr) Restricts values to those satisfying the expression

1 CREATE TABLE employee (

2 emp_id INT NOT NULL AUTO_INCREMENT,
3 name VARCHAR(40) NOT NULL,
4 salary DECIMAL(10,2) DEFAULT 30000.00,
5 sex CHAR(1) CHECK (sex IN ('M','F')),
6 dept_id INT,

8
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

7 PRIMARY KEY (emp_id),

8 FOREIGN KEY (dept_id) REFERENCES department(dept_id)
9 );
Listing 5: Constraints example

4.4 Altering and Dropping Tables

1 -- Add a new column
2 ALTER TABLE student ADD email VARCHAR(50);
3
4 -- Remove a column
5 ALTER TABLE student DROP COLUMN email;
6
7 -- Change a column definition
8 ALTER TABLE student MODIFY COLUMN gpa FLOAT;
9
10 -- Delete all rows but keep table structure
11 TRUNCATE TABLE student;
12
13 -- Delete the entire table (structure + data)
14 DROP TABLE student;
15
16 -- Show all tables in current database
17 SHOW TABLES;
18
19 -- Describe a table's structure
20 DESCRIBE student;
Listing 6: ALTER and DROP

5 Inserting, Updating, and Deleting Data

5.1 INSERT
1 -- Insert with all columns
2 INSERT INTO student VALUES (1, 'Alice', 'Biology', 3.8);
3
4 -- Insert specific columns (others get default/NULL)
5 INSERT INTO student (name, major)
6 VALUES ('Bob', 'Chemistry');
7
8 -- Insert multiple rows at once
9 INSERT INTO student (name, major, gpa) VALUES
10 ('Claire', 'Biology', 3.9),
11 ('Dave', 'History', 3.1),
12 ('Emma', 'CS', 3.7);
Listing 7: INSERT syntax

9
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

Note: When inserting with AUTO_INCREMENT, you can pass NULL or omit the column
entirely  MySQL assigns the next integer automatically.

5.2 UPDATE
1 -- General syntax
2 UPDATE table_name
3 SET column1 = value1,
4 column2 = value2
5 WHERE condition;
6
7 -- Example: change Bob's major
8 UPDATE student
9 SET major = 'Computer Science'
10 WHERE name = 'Bob';
11
12 -- Update multiple rows matching condition
13 UPDATE student
14 SET major = 'Biochemistry'
15 WHERE major = 'Biology' OR major = 'Chemistry';
Listing 8: UPDATE syntax

Warning: Omitting the WHERE clause from UPDATE updates every row in the table!

5.3 DELETE
1 -- Delete specific rows
2 DELETE FROM student
3 WHERE student_id = 2;
4
5 -- Delete rows matching a condition
6 DELETE FROM student
7 WHERE major = 'History';
8
9 -- Delete ALL rows (use TRUNCATE instead for large tables)
10 DELETE FROM student;
Listing 9: DELETE syntax

6 Basic Queries (SELECT)

6.1 SELECT Fundamentals

1 -- Select all columns
2 SELECT * FROM student;

10
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

3
4 -- Select specific columns
5 SELECT name, major FROM student;
6
7 -- Select with alias
8 SELECT name AS student_name, gpa AS grade_point
9 FROM student;
10
11 -- Select only distinct values
12 SELECT DISTINCT major FROM student;
Listing 10: SELECT syntax

6.2 Filtering with WHERE

1 -- Comparison operators: = <> != < > <= >=
2 SELECT * FROM student WHERE gpa >= 3.5;
3
4 -- Multiple conditions
5 SELECT * FROM student
6 WHERE major = 'Biology'
7 AND gpa > 3.0;
8
9 -- OR condition
10 SELECT * FROM student
11 WHERE major = 'Biology'
12 OR major = 'Chemistry';
13
14 -- NOT
15 SELECT * FROM student
16 WHERE NOT major = 'History';
17
18 -- IN (shorthand for multiple OR)
19 SELECT * FROM student
20 WHERE major IN ('Biology', 'Chemistry', 'CS');
21
22 -- BETWEEN (inclusive on both ends)
23 SELECT * FROM student
24 WHERE gpa BETWEEN 3.0 AND 3.8;
25
26 -- NULL checks
27 SELECT * FROM student WHERE gpa IS NULL;
28 SELECT * FROM student WHERE gpa IS NOT NULL;
Listing 11: WHERE clause

6.3 Sorting with ORDER BY

1 -- Ascending (default)
2 SELECT * FROM student ORDER BY gpa;
3

11
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

4 -- Descending
5 SELECT * FROM student ORDER BY gpa DESC;
6
7 -- Sort by multiple columns
8 SELECT * FROM student ORDER BY major ASC, gpa DESC;
Listing 12: ORDER BY

6.4 LIMIT
1 -- Return only the first 3 rows
2 SELECT * FROM student LIMIT 3;
3
4 -- With an OFFSET (skip 5 rows, return next 3)
5 SELECT * FROM student LIMIT 3 OFFSET 5;
6 -- shorthand:
7 SELECT * FROM student LIMIT 5, 3;
Listing 13: LIMIT clause

7 The Company Database

7.1 Schema Overview

The tutorial builds a company database with the following tables. Understanding the
schema is essential for the advanced queries later.

Table Description
employee All employees; foreign key to branch
and super (manager)
branch Company branches; foreign key to
manager (employee)
client Clients the company works with
works_with Junction table: which employee works
with which client
branch_supplier Suppliers for each branch

7.2 Creating the Company Database

1 CREATE DATABASE company;
2 USE company;
3
4 -- Employee table (branch_id added after branch is created)
5 CREATE TABLE employee (
6 emp_id INT PRIMARY KEY,
7 first_name VARCHAR(40) NOT NULL,
8 last_name VARCHAR(40) NOT NULL,

12
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

9 birth_day DATE,
10 sex CHAR(1),
11 salary INT,
12 super_id INT, -- self-referential FK
13 branch_id INT
14 );
15
16 CREATE TABLE branch (
17 branch_id INT PRIMARY KEY,
18 branch_name VARCHAR(40),
19 mgr_id INT,
20 mgr_start_date DATE,
21 FOREIGN KEY (mgr_id) REFERENCES employee(emp_id)
22 ON DELETE SET NULL
23 );
24
25 -- Now add FK to employee
26 ALTER TABLE employee
27 ADD FOREIGN KEY (branch_id) REFERENCES branch(branch_id)
28 ON DELETE SET NULL;
29
30 ALTER TABLE employee
31 ADD FOREIGN KEY (super_id) REFERENCES employee(emp_id)
32 ON DELETE SET NULL;
33
34 CREATE TABLE client (
35 client_id INT PRIMARY KEY,
36 client_name VARCHAR(40) NOT NULL,
37 branch_id INT,
38 FOREIGN KEY (branch_id) REFERENCES branch(branch_id)
39 ON DELETE SET NULL
40 );
41
42 CREATE TABLE works_with (
43 emp_id INT,
44 client_id INT,
45 total_sales INT,
46 PRIMARY KEY (emp_id, client_id), -- composite PK
47 FOREIGN KEY (emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE,
48 FOREIGN KEY (client_id) REFERENCES client(client_id) ON DELETE CASCADE
49 );
50
51 CREATE TABLE branch_supplier (
52 branch_id INT,
53 supplier_name VARCHAR(40),
54 supply_type VARCHAR(40),
55 PRIMARY KEY (branch_id, supplier_name),
56 FOREIGN KEY (branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
57 );
Listing 14: Creating tables with self-referential and circular FKs

13
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

Circular Foreign Key problem: employee needs branch_id and branch needs
mgr_id (an employee). Neither table can be created rst with both FKs. The
solution is to create both tables rst without the FK, then ALTER TABLE to add it.

8 Aggregate Functions

8.1 Overview
Aggregate functions compute a single result from a set of rows.

Function Description
COUNT(expr) Number of non-NULL values (or rows if *)
SUM(expr) Total sum
AVG(expr) Average value
MIN(expr) Minimum value
MAX(expr) Maximum value

1 -- Count all employees

2 SELECT COUNT(*) FROM employee;
3
4 -- Count employees with a supervisor assigned
5 SELECT COUNT(super_id) FROM employee;
6
7 -- Average salary of female employees
8 SELECT AVG(salary)
9 FROM employee
10 WHERE sex = 'F';
11
12 -- Sum of all sales
13 SELECT SUM(total_sales) FROM works_with;
14
15 -- Min and max salaries
16 SELECT MIN(salary), MAX(salary) FROM employee;
Listing 15: Aggregate examples

8.2 GROUP BY
GROUP BY splits rows into groups, and aggregates are applied per group.
1 -- Total sales per employee
2 SELECT emp_id, SUM(total_sales) AS total
3 FROM works_with
4 GROUP BY emp_id;
5
6 -- Count employees by sex
7 SELECT sex, COUNT(*) AS count

14
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

8 FROM employee
9 GROUP BY sex;
10
11 -- Average salary per branch
12 SELECT branch_id, AVG(salary) AS avg_salary
13 FROM employee
14 GROUP BY branch_id;
Listing 16: GROUP BY

8.3 HAVING
HAVING lters the groups produced by GROUP BY (like WHERE but for groups).

1 -- Only show employees whose total sales exceed 30000

2 SELECT emp_id, SUM(total_sales) AS total
3 FROM works_with
4 GROUP BY emp_id
5 HAVING total > 30000;
Listing 17: HAVING clause

WHERE vs HAVING:
ˆ WHERE lters individual rows before grouping.
ˆ HAVING lters groups after aggregation.
ˆ You cannot use aggregate functions in WHERE; use HAVING instead.

8.4 Full SELECT Query Order

The logical order of a SELECT statement is:

Order Clause
1 FROM (and JOIN)
2 WHERE
3 GROUP BY
4 HAVING
5 SELECT
6 ORDER BY
7 LIMIT

9 Wildcards

9.1 The LIKE Operator

LIKE is used for pattern matching within strings.

15
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

Wildcard Meaning
% Matches zero or more characters
_ Matches exactly one character

1 -- Clients whose name ends with 'LLC'

2 SELECT * FROM client
3 WHERE client_name LIKE '%LLC';
4
5 -- Employees born in October (month = 10)
6 SELECT * FROM employee
7 WHERE birth_day LIKE '____-10%';
8 -- (4 underscores = year digits, then '-10')
9
10 -- Supplier names containing 'label'
11 SELECT * FROM branch_supplier
12 WHERE supplier_name LIKE '%label%';
13
14 -- Names starting with 'J'
15 SELECT first_name FROM employee
16 WHERE first_name LIKE 'J%';
17
18 -- Names that are exactly 5 characters
19 SELECT first_name FROM employee
20 WHERE first_name LIKE '_____';
Listing 18: LIKE examples

10 UNION

10.1 Overview
UNION combines the result sets of two or more SELECT statements into a single result set.

Rules:
ˆ Each SELECT must return the same number of columns.
ˆ Corresponding columns must have compatible data types.
ˆ UNION removes duplicates; UNION ALL keeps them.

ˆ The column names in the result come from the rst SELECT.
1 -- Combine employee first names with client names
2 SELECT first_name AS name FROM employee
3 UNION
4 SELECT client_name FROM client;
5
6 -- Combine salaries and total sales into one "money" column
7 SELECT salary AS money, 'Salary' AS type

16
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

8 FROM employee
9 UNION ALL
10 SELECT total_sales, 'Sales'
11 FROM works_with;
12
13 -- All branch names and client names (with duplicates removed)
14 SELECT branch_name FROM branch
15 UNION
16 SELECT client_name FROM client
17 UNION
18 SELECT supplier_name FROM branch_supplier;
Listing 19: UNION examples

11 JOINs

11.1 What is a JOIN?

A JOIN combines rows from two or more tables based on a related column. Joins are the
most powerful feature of relational databases.

11.2 Types of JOINs

JOIN Type Description
INNER JOIN Returns rows where the condition matches in both
tables
LEFT JOIN Returns all rows from the left table; matched rows
from right (or NULL)
RIGHT JOIN Returns all rows from the right table; matched
rows from left (or NULL)
FULL OUTER JOIN Returns all rows from both tables (MySQL simu-
lates with UNION)
CROSS JOIN Cartesian product of both tables

11.3 JOIN Examples

1 -- Get employee names with their branch names
2 SELECT e.first_name, e.last_name, b.branch_name
3 FROM employee AS e
4 INNER JOIN branch AS b
5 ON e.branch_id = b.branch_id;
Listing 20: INNER JOIN

1 -- All employees and their branch name (NULL if not assigned)

2 SELECT e.first_name, e.last_name, b.branch_name

17
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

3 FROM employee AS e
4 LEFT JOIN branch AS b
5 ON e.branch_id = b.branch_id;
Listing 21: LEFT JOIN

1 -- All branches and their manager (NULL if no manager)

2 SELECT e.first_name, b.branch_name
3 FROM employee AS e
4 RIGHT JOIN branch AS b
5 ON b.mgr_id = e.emp_id;
Listing 22: RIGHT JOIN

1 SELECT e.first_name, b.branch_name

2 FROM employee AS e
3 LEFT JOIN branch AS b ON e.branch_id = b.branch_id
4 UNION
5 SELECT e.first_name, b.branch_name
6 FROM employee AS e
7 RIGHT JOIN branch AS b ON e.branch_id = b.branch_id;
Listing 23: FULL OUTER JOIN (MySQL simulation)

JOIN = INNER JOIN. Writing just JOIN is equivalent to INNER JOIN in

MySQL.

11.4 Multiple JOINs

1 -- Employee name + client name + total sales
2 SELECT e.first_name, e.last_name,
3 c.client_name, w.total_sales
4 FROM employee AS e
5 JOIN works_with AS w ON e.emp_id = w.emp_id
6 JOIN client AS c ON w.client_id = c.client_id
7 ORDER BY w.total_sales DESC;
Listing 24: Joining three tables

12 Nested Queries (Subqueries)

12.1 Overview
A subquery (nested query) is a SELECT statement embedded inside another SQL state-
ment. The inner query runs rst and its result is used by the outer query.

1 -- Find employees who earn more than the average salary

2 SELECT first_name, last_name, salary

18
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

3 FROM employee
4 WHERE salary > (
5 SELECT AVG(salary) FROM employee
6 );
Listing 25: Subquery in WHERE

1 -- Find all clients handled by employees in branch 2

2 SELECT client_name
3 FROM client
4 WHERE client_id IN (
5 SELECT client_id
6 FROM works_with
7 WHERE emp_id IN (
8 SELECT emp_id
9 FROM employee
10 WHERE branch_id = 2
11 )
12 );
Listing 26: Subquery with IN

1 -- Employee who has the highest total sales

2 SELECT first_name, last_name
3 FROM employee
4 WHERE emp_id = (
5 SELECT emp_id
6 FROM works_with
7 GROUP BY emp_id
8 ORDER BY SUM(total_sales) DESC
9 LIMIT 1
10 );
Listing 27: Subquery returning multiple columns

Correlated vs Uncorrelated Subqueries:

ˆ Uncorrelated
ˆ Correlated
: Inner query is independent and runs once.
: Inner query references the outer query and runs once per row.
Correlated subqueries are powerful but can be slow on large data; often replaceable
with JOINs.

13 ON DELETE Rules

13.1 Referential Integrity

When a row in a parent table is deleted, rows in a child table that reference it must be
handled. MySQL provides four options:

19
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

Rule Behaviour
ON DELETE CASCADE Automatically delete the child rows too
ON DELETE SET NULL Set the FK column to NULL in the child rows
ON DELETE SET DEFAULT Set the FK column to its DEFAULT value (less
common)
ON DELETE RESTRICT (default) Prevent the parent deletion if child
rows exist
ON DELETE NO ACTION Same as RESTRICT in MySQL

1 -- works_with rows are deleted when the employee is deleted

2 FOREIGN KEY (emp_id) REFERENCES employee(emp_id)
3 ON DELETE CASCADE;
4
5 -- branch_id is set to NULL when the branch is deleted
6 FOREIGN KEY (branch_id) REFERENCES branch(branch_id)
7 ON DELETE SET NULL;
Listing 28: ON DELETE examples

Rule of thumb:
ˆ Use CASCADE for dependent records (junction tables, order items).

ˆ Use SET NULL for optional relationships (an employee without a branch still exists).

ˆ Use RESTRICT when deletion should never happen (strict data integrity).

14 Triggers

14.1 What is a Trigger?

Denition: Trigger
Atrigger is a block of SQL code that is automatically executed ( triggered ) in response
to a specic event on a table: INSERT, UPDATE, or DELETE.

Use cases: auditing changes, enforcing complex business rules, automatic logging, main-
taining derived data.

14.2 Trigger Syntax

1 DELIMITER $$
2
3 CREATE TRIGGER trigger_name
4 BEFORE | AFTER INSERT | UPDATE | DELETE
5 ON table_name
6 FOR EACH ROW

20
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

7 BEGIN
8 -- SQL statements
9 -- Use [Link] to access the new row value
10 -- Use [Link] to access the old row value
11 END$$
12
13 DELIMITER ;
Listing 29: Trigger syntax

14.3 Trigger Examples

1 -- Table to hold log messages
2 CREATE TABLE trigger_test (
3 message VARCHAR(100)
4 );
5
6 DELIMITER $$
7 CREATE TRIGGER after_employee_insert
8 AFTER INSERT ON employee
9 FOR EACH ROW
10 BEGIN
11 INSERT INTO trigger_test
12 VALUES (CONCAT('New employee added: ', NEW.first_name));
13 END$$
14 DELIMITER ;
Listing 30: Log employee inserts

1 DELIMITER $$
2 CREATE TRIGGER before_employee_delete
3 BEFORE DELETE ON employee
4 FOR EACH ROW
5 BEGIN
6 IF [Link] > 100000 THEN
7 SIGNAL SQLSTATE '45000'
8 SET MESSAGE_TEXT = 'Cannot delete high-salary employee';
9 END IF;
10 END$$
11 DELIMITER ;
Listing 31: Prevent deletion of important employees

DELIMITER: MySQL normally uses ; as a statement terminator. Inside a trig-

ger, ; would end the statement prematurely. We change the delimiter to $$ for the
duration of the trigger denition, then change it back.

15 ER Diagrams
21
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

15.1 Entity-Relationship Model

An Entity-Relationship (ER) Diagram is a visual representation of the database
schema showing entities, their attributes, and the relationships between them.

15.2 ER Diagram Elements

Symbol Name Meaning
Rectangle Entity A real-world object (table)
Ellipse Attribute A property of an entity (column)
Double ellipse Multivalued attr Can hold multiple values
Dashed ellipse Derived attr Computed from other attributes
Underlined text Key attribute Uniquely identies entity instances
Diamond Relationship Association between two entities
Double rectangle Weak entity Cannot be uniquely identied alone
Double diamond Identifying rel Relationship that identies a weak
entity
Line Connection Links entity to attribute / relation-
ship

15.3 Relationship Cardinality

Cardinality describes how many instances of one entity relate to another:

Notation Meaning
1:1 One-to-One (e.g., one employee has one ID badge)
1:N One-to-Many (e.g., one branch has many employees)
N:M Many-to-Many (e.g., employees work with many clients)

Participation Constraints:
ˆ Total participation (double line): every entity instance must participate.

ˆ Partial participation (single line): participation is optional.

15.4 Weak Entities

A weak entity cannot be uniquely identied by its own attributes alone; it depends on a
strong (owner) entity. The combination of the weak entity's partial key and the owner's
primary key forms a unique identier.

Example: An exam (week 1 exam, week 2 exam) within a class is a weak entity; the
exam is identied by the class it belongs to plus the exam number.

15.5 Designing an ER Diagram: Step-by-Step

The video walks through designing an ER diagram for a company. Key design decisions:

1. Identify the entities (nouns): Employee, Branch, Client, Works_With.

22
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

2. Identify the attributes of each entity.

3. Identify the relationships (verbs) between entities.

4. Determine cardinality for each relationship.

5. Identify any weak entities .

6. Annotate total/partial participation .

16 Converting ER Diagrams to Schemas

16.1 Mapping Rules

ER Construct Relational Schema Mapping
Strong entity Becomes a table; key attribute becomes PRIMARY
KEY
Weak entity Becomes a table; primary key = partial key +
owner's PK
Simple attribute Becomes a column
Multivalued attribute Becomes a separate table with FK to the entity
Derived attribute Usually omitted (computed in queries)
1:1 relationship FK placed in either table (prefer total-
participation side)
1:N relationship FK placed in the many side table
N:M relationship Becomes a new junction table with both PKs as
composite PK

16.2 Junction Table Example

1 -- Employee works_with Client (many-to-many)
2 CREATE TABLE works_with (
3 emp_id INT NOT NULL,
4 client_id INT NOT NULL,
5 total_sales INT DEFAULT 0,
6 PRIMARY KEY (emp_id, client_id), -- composite PK
7 FOREIGN KEY (emp_id)
8 REFERENCES employee(emp_id) ON DELETE CASCADE,
9 FOREIGN KEY (client_id)
10 REFERENCES client(client_id) ON DELETE CASCADE
11 );
Listing 32: N:M mapped to junction table

17 Extended Topics
23
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

17.1 String Functions

1 SELECT UPPER(first_name) FROM employee; -- uppercase
2 SELECT LOWER(first_name) FROM employee; -- lowercase
3 SELECT LENGTH(first_name) FROM employee; -- character count
4 SELECT TRIM(' hello '); -- remove spaces
5 SELECT SUBSTRING('Database', 1, 4); -- 'Data'
6 SELECT CONCAT(first_name, ' ', last_name)
7 AS full_name FROM employee; -- concatenate
8 SELECT REPLACE('Hello World', 'World', 'SQL'); -- 'Hello SQL'
Listing 33: Useful string functions

17.2 Date and Time Functions

1 SELECT NOW(); -- current datetime
2 SELECT CURDATE(); -- current date
3 SELECT YEAR(birth_day) FROM employee; -- extract year
4 SELECT MONTH(birth_day) FROM employee; -- extract month
5 SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- days between
6 SELECT DATE_FORMAT(NOW(), '%d/%m/%Y'); -- custom format
Listing 34: Date functions

17.3 CASE Expression

CASE is SQL's conditional expression (like an if-else):

1 SELECT first_name,
2 salary,
3 CASE
4 WHEN salary >= 70000 THEN 'High'
5 WHEN salary >= 50000 THEN 'Medium'
6 ELSE 'Low'
7 END AS salary_band
8 FROM employee;
Listing 35: CASE expression

17.4 Indexes
An index is a data structure that speeds up SELECT queries at the cost of slightly slower
INSERT/UPDATE/DELETE (maintaining the index).

1 -- Create an index on a frequently searched column

2 CREATE INDEX idx_last_name ON employee (last_name);
3
4 -- Composite index
5 CREATE INDEX idx_name ON employee (last_name, first_name);
6
7 -- Show existing indexes
8 SHOW INDEX FROM employee;

24
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

9
10 -- Remove an index
11 DROP INDEX idx_last_name ON employee;
Listing 36: Index management

17.5 Views
A view is a saved SELECT query that behaves like a virtual table.

1 -- Create a view
2 CREATE VIEW employee_details AS
3 SELECT e.first_name, e.last_name,
4 b.branch_name, [Link]
5 FROM employee e
6 JOIN branch b ON e.branch_id = b.branch_id;
7
8 -- Query the view like a table
9 SELECT * FROM employee_details WHERE salary > 60000;
10
11 -- Drop a view
12 DROP VIEW employee_details;
Listing 37: Creating and using views

17.6 Transactions
A transaction is a unit of work that either all succeeds or all fails (ACID).

1 START TRANSACTION;
2
3 UPDATE account SET balance = balance - 500 WHERE acc_id = 1;
4 UPDATE account SET balance = balance + 500 WHERE acc_id = 2;
5
6 -- If everything is OK:
7 COMMIT;
8
9 -- If something went wrong:
10 -- ROLLBACK;
Listing 38: Transaction example

18 Quick Reference

18.1 Most-Used Commands at a Glance

25
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

Command Purpose
CREATE DATABASE db; Create a new database
USE db; Switch to a database
SHOW TABLES; List tables
DESCRIBE table; Show table structure
CREATE TABLE t (...); Create a table
ALTER TABLE t ADD col type; Add a column
DROP TABLE t; Delete a table
INSERT INTO t VALUES (...); Insert a row
SELECT * FROM t; Read all rows
SELECT col FROM t WHERE cond; Filtered read
UPDATE t SET col=val WHERE cond; Modify rows
DELETE FROM t WHERE cond; Remove rows
SELECT ...JOIN ...ON ...; Combine tables
SELECT ...GROUP BY col; Aggregate by group
SELECT ...HAVING cond; Filter groups
SELECT ...LIKE 'pat%'; Pattern match
SELECT A UNION SELECT B; Combine result sets

19 Practice Questions

19.1 Section 1: Database Fundamentals

1. What is the dierence between a DBMS and a RDBMS? Give one example of each.

2. Explain the four CRUD operations with their corresponding SQL commands.

3. What distinguishes a surrogate key from a natural key ? Give an example of each.

4. Under what circumstances would you use a composite primary key ?

5. What problem does a foreign key solve? What is referential integrity ?
6. Compare relational and non-relational databases: when would you choose each?

19.2 Section 2: DDL  Creating Tables

Write SQL to:

1. Create a library database and switch to it.

2. Create a book table with columns: book_id (auto-increment PK), title (max 100
chars, not null), author (max 50 chars), isbn (13 chars, unique), price (decimal, 2
decimal places), published_date (date).

3. Add a column genre (VARCHAR(30)) to the book table.

4. Create a member table with member_id (PK), name, email (unique), join_date.

26
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

5. Create aloan table linking members and books with loan_date, due_date, and
return_date. Include appropriate foreign keys with ON DELETE CASCADE.

19.3 Section 3: DML  Inserting, Updating, Deleting

Using the library schema above:

1. Insert at least three books into the book table.

2. Insert two members into the member table.

3. Insert two loan records.

4. Update the price of the book with book_id = 2 to $19.99.

5. Update the genre of all books with no genre set to 'General'.

6. Delete the loan record where return_date is not null (i.e., already returned).

19.4 Section 4: SELECT Queries

Write SQL queries to:

1. Select all books ordered by published_date descending.

2. Find all books priced between $10 and $30.

3. Find all members whose email contains gmail.

4. Find all books whose title starts with the letter 'T'.
5. Select the 3 most expensive books.

6. Find books that have no genre assigned (NULL).

7. List all distinct genres in the book table.

19.5 Section 5: Aggregate Functions

1. Count the total number of books in the library.

2. Find the average, minimum, and maximum price of all books.

3. How many books exist per genre? (Use GROUP BY.)

4. Which genre has more than 2 books? (Use HAVING.)
5. Find the total amount of money represented by all books (sum of all prices).

6. Count how many books each author has written.

19.6 Section 6: JOINs

1. Write an INNER JOIN to get each loan record along with the member's name and the
book's title.

2. Write a LEFT JOIN to nd all members and any loans they have (include members
with no loans).

27
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

3. Write a RIGHT JOIN to nd all books and whether they are currently on loan.

4. Write a query joining all three tables to show: member name, book title, loan date,
and due date.

5. Find all books that have never been loaned (hint: LEFT JOIN + WHERE ...IS NULL).

19.7 Section 7: Nested Queries

1. Find all books priced above the average price.

2. Find all members who have borrowed more than one book.

3. Find the title of the most expensive book using a subquery.

4. Find all books that have been borrowed by at least one member (using IN subquery
and also using EXISTS).
5. Rewrite the EXISTS query from the previous question as a JOIN and compare.

19.8 Section 8: Wildcards and UNION

1. Find all books whose ISBN starts with 978.
2. Find all authors whose name is exactly 10 characters long.

3. Write a UNION query combining member names and book titles into a single list labelled
item.
4. What is the dierence between UNION and UNION ALL? Write an example of each.

19.9 Section 9: Triggers and Views

1. Create an audit table book_changes and write a trigger that records the title and the
current timestamp every time a new book is inserted.

2. Write a BEFORE DELETE trigger that prevents books from being deleted if they are
currently on loan.

3. Create a view active_loans that shows all current (unreturned) loans with member
names and book titles.

4. Query the active_loans view to nd loans that are overdue (due date before today).

19.10 Section 10: ER Diagrams and Design

1. Draw (or describe in text) an ER diagram for a university system with entities:
Student, Course, Professor, Department. Include attributes and cardinality.

2. Identify which relationships in the university schema are 1:N and which are N:M.

3. Convert the N:M relationship between Student and Course into a junction table. What
additional attribute(s) might you add?

4. What is a weak entity ? Give an example from the university context.

5. Explain the dierence between total and partial participation with an example.

28
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

6. For a 1:1 relationship, on which side should the foreign key be placed, and why?

19.11 Challenge Questions

1. Recursive query: In the company schema, write a query to nd all employees who
report (directly or through a chain) to a specic manager. (Hint: this requires a
self-join or recursive CTE in MySQL 8.)

2. Ranking: Without using window functions, nd the second-highest salary in the
employee table.

3. Pivot: Using conditional aggregation (SUM(CASE WHEN...)), write a query that shows,
for each branch, the count of male and female employees as separate columns.

4. Transaction: Write a transaction that transfers an employee from branch 2 to branch

3, ensuring that if any part fails, the entire change is rolled back.

5. Optimization: You have a table of 10 million orders. The query SELECT * FROM
orders WHERE customer_email = 'test@[Link]' is slow. How would you x it?
Write the SQL.

Answer Hints

Selected Hints

Section 4, Q5 (Top 3 expensive books):

SELECT * FROM book ORDER BY price DESC LIMIT 3;

Section 6, Q5 (Books never loaned):

SELECT [Link]
FROM book b
LEFT JOIN loan l ON b.book_id = l.book_id
WHERE l.book_id IS NULL;

Section 7, Q1 (Books above average price):

SELECT title, price FROM book
WHERE price > (SELECT AVG(price) FROM book);

Challenge Q2 (Second highest salary):

SELECT MAX(salary) FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Challenge Q3 (Pivot):
SELECT branch_id,
SUM(CASE WHEN sex = 'M' THEN 1 ELSE 0 END) AS males,
SUM(CASE WHEN sex = 'F' THEN 1 ELSE 0 END) AS females
FROM employee

29
SQL & Database  Complete Notes freeCodeCamp · Mike Dane

GROUP BY branch_id;

Notes based on SQL Tutorial  Full Database Course for Beginners by Mike Dane /

[Link].

Extended with additional explanations, tables, and practice questions.

30