SQL STUDY GUIDE | BEGINNER TO INTERMEDIATE

LECTURES 115 — 142

SQL
Study Guide
Joins · Filtering · DDL · DML · Constraints · Keys

What youSet
Joins
will learn: Filtering
Ops DDL DML Constraints Keys Advanced
All types of SQL JOINs with real examples
Filtering with LIKE, CASE, and subqueries
Creating tables with correct data types
Inserting and copying data between tables
Constraints: NOT NULL, UNIQUE, CHECK, DEFAULT
Primary Keys, Foreign Keys & referential integrity
SQL order of execution — how queries really run

Designed for complete beginners | 20 Topics | 40+ Practice Questions SQL Mastery Series
 Topics Covered
Lecture Topic Category

Lecture 115 Inner Join Joins

Lecture 117 Left Join Joins

Lecture 119 Right Join Joins

Lecture 121 Anti Join Joins

Lecture 123 Full Outer Join Joins

Lecture 124 Self Join Joins

Lecture 125 Union & Union All Set Operations

Lecture 126 SQL LIKE Operator Filtering

Lecture 127 CASE in SELECT & ORDER BY Filtering

Lecture 128 Nested CASE Statement Filtering

Lecture 129 SQL Data Types DDL

Lecture 130 SQL Create Table DDL

Lecture 131 Insert Into All Columns DML

Lecture 132 Insert Into Certain Columns DML

Lecture 133 Copying Data Between Tables DML

Lecture 134 Sub Queries Advanced

Lecture 142 SQL Order of Execution Advanced

Lecture 135 Not Null Constraint Constraints

Lecture 136 Unique Constraint Constraints

Lecture 137 Check Constraint Constraints

Lecture 138 Default Constraint Constraints

Lecture 139 Primary & Foreign Key Concept Keys

SQL Study Guide — Beginner to Intermediate Page 2

 Inner Join
An INNER JOIN returns only the rows where there is a matching value in BOTH tables. Think of it as
the overlap in a Venn diagram — only shared data appears.

SAMPLE DATA

employees

emp_id name dept_id

1 Alice 10

2 Bob 20

3 Carol 99

departments

dept_id dept_name

10 HR

20 IT

30 Finance

SQL SYNTAX

SELECT [Link], d.dept_name

FROM employees e
INNER JOIN departments d
ON e.dept_id = d.dept_id;

OUTPUT / RESULT

name dept_name

Alice HR

Bob IT

TIP Carol (dept_id 99) is excluded — no matching row exists in departments.

PRACTICE QUESTIONS

Q1: What does INNER JOIN return when there is no match?

Answer: It excludes those rows entirely. Only rows with matching values in both tables appear in the result.

Q2: If Table A has 5 rows and Table B has 3, can INNER JOIN return more than 3 rows?

Answer: Yes! If multiple rows in Table A match one row in Table B, you get repeated matches. Result size
depends on matching pairs.

SQL Study Guide — Beginner to Intermediate Page 3

 Left Join
A LEFT JOIN returns ALL rows from the left (first) table, plus matching rows from the right table.
Where there is no match, NULLs fill in for the right-side columns.

SAMPLE DATA

employees

emp_id name dept_id

1 Alice 10

2 Bob 20

3 Carol 99

departments

dept_id dept_name

10 HR

20 IT

SQL SYNTAX

SELECT [Link], d.dept_name

FROM employees e
LEFT JOIN departments d
ON e.dept_id = d.dept_id;

OUTPUT / RESULT

name dept_name

Alice HR

Bob IT

Carol NULL

TIP Carol appears with NULL because dept_id 99 has no match in departments.

PRACTICE QUESTIONS

Q1: When would you use LEFT JOIN instead of INNER JOIN?

Answer: Use LEFT JOIN when you want to keep ALL records from the left table, even if they have no
matching record in the right table.

Q2: In a LEFT JOIN, which table's rows are always included?

Answer: The LEFT (first) table's rows are always included, even without a match.

SQL Study Guide — Beginner to Intermediate Page 4

 Right Join
A RIGHT JOIN is the mirror of LEFT JOIN. It returns ALL rows from the right (second) table, plus
matching rows from the left. Unmatched left-side columns become NULL.

SAMPLE DATA

employees

emp_id name dept_id

1 Alice 10

2 Bob 20

departments

dept_id dept_name

10 HR

20 IT

30 Finance

SQL SYNTAX

SELECT [Link], d.dept_name

FROM employees e
RIGHT JOIN departments d
ON e.dept_id = d.dept_id;

OUTPUT / RESULT

name dept_name

Alice HR

Bob IT

NULL Finance

TIP Finance appears with NULL because no employee belongs to dept_id 30.

PRACTICE QUESTIONS

Q1: Can a RIGHT JOIN be rewritten as a LEFT JOIN?

Answer: Yes! Simply swap the table order and use LEFT JOIN. RIGHT JOIN is less commonly used for this
reason.

Q2: What value appears in left-table columns when there is no match in RIGHT JOIN?

Answer: NULL appears in those columns.

SQL Study Guide — Beginner to Intermediate Page 5

 Anti Join
An Anti Join returns rows from one table that have NO matching row in the other table. Achieved
using LEFT JOIN combined with WHERE IS NULL.

SAMPLE DATA

employees

emp_id name dept_id

1 Alice 10

2 Bob 20

3 Carol 99

departments

dept_id dept_name

10 HR

20 IT

SQL SYNTAX

-- Left Anti Join (employees with no department)

SELECT [Link]
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

-- Right Anti Join (departments with no employees)

SELECT d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

OUTPUT / RESULT

Left Anti Result Right Anti Result

Carol Finance

TIP Anti Join is perfect for finding 'orphan' records — things that don't belong anywhere.

PRACTICE QUESTIONS

Q1: What is the key WHERE clause used in an Anti Join?

Answer: WHERE [right_table_column] IS NULL — this filters out matched rows, keeping only unmatched
ones.

SQL Study Guide — Beginner to Intermediate Page 6

 Q2: Give a real-world use case for Anti Join.

Answer: Finding customers who have never placed an order, or employees not assigned to any project.

SQL Study Guide — Beginner to Intermediate Page 7

 Full Outer Join
FULL OUTER JOIN returns ALL rows from BOTH tables. Where there is no match on either side,
NULLs fill in. It is the union of LEFT and RIGHT JOIN.

SAMPLE DATA

employees

emp_id name dept_id

1 Alice 10

3 Carol 99

departments

dept_id dept_name

10 HR

30 Finance

SQL SYNTAX

SELECT [Link], d.dept_name

FROM employees e
FULL OUTER JOIN departments d
ON e.dept_id = d.dept_id;

OUTPUT / RESULT

name dept_name

Alice HR

Carol NULL

NULL Finance

MySQL does not support FULL OUTER JOIN directly — simulate it with UNION of LEFT and RIGHT
TIP
JOIN.

PRACTICE QUESTIONS

Q1: How is FULL OUTER JOIN different from INNER JOIN?

Answer: INNER JOIN only returns matched rows. FULL OUTER JOIN returns ALL rows from both tables with
NULLs where data is missing.

Q2: Is FULL OUTER JOIN supported in MySQL?

Answer: No. Use: SELECT ... LEFT JOIN ... UNION SELECT ... RIGHT JOIN ... to simulate it.

SQL Study Guide — Beginner to Intermediate Page 8

 Self Join
A Self Join joins a table to itself. Useful for hierarchical data like an employee-manager relationship
where both are stored in the same table.

SAMPLE DATA

employees

emp_id name manager_id

1 Alice NULL

2 Bob 1

3 Carol 1

4 Dave 2

SQL SYNTAX

SELECT [Link] AS employee, [Link] AS manager

FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.emp_id;

OUTPUT / RESULT

employee manager

Alice NULL

Bob Alice

Carol Alice

Dave Bob

TIP We alias the same table as 'e' (employee) and 'm' (manager) to treat it as two separate tables.

PRACTICE QUESTIONS

Q1: Why do we use aliases in a Self Join?

Answer: Because we join the same table to itself, aliases distinguish which 'copy' is being referenced on each
side.

Q2: Name one real-world scenario where Self Join is useful.

Answer: Employee-manager hierarchy, finding product pairs with the same price, or comparing sales across
periods.

SQL Study Guide — Beginner to Intermediate Page 9

 Union & Union All
UNION combines results of two SELECT statements and removes duplicates. UNION ALL keeps
ALL rows including duplicates and is faster.

SAMPLE DATA

table_a

name

Alice

Bob

Carol

table_b

name

Bob

Dave

Eve

SQL SYNTAX

-- UNION (removes duplicates)

SELECT name FROM table_a
UNION
SELECT name FROM table_b;

-- UNION ALL (keeps duplicates)

SELECT name FROM table_a
UNION ALL
SELECT name FROM table_b;

OUTPUT / RESULT

UNION (5 rows) UNION ALL (6 rows)

Alice Alice

Bob Bob

Carol Carol

Dave Bob

Eve Dave

— Eve

TIP Both SELECT statements must have the same number of columns with compatible data types.

PRACTICE QUESTIONS

SQL Study Guide — Beginner to Intermediate Page 10

 Q1: When should you use UNION ALL instead of UNION?

Answer: Use UNION ALL when there are no duplicates OR you want to keep them. It is also faster since it
skips deduplication.

Q2: What error occurs if two SELECT statements in UNION have different column counts?

Answer: Error: 'The used SELECT statements have a different number of columns'. Both queries must return
the same columns.

SQL Study Guide — Beginner to Intermediate Page 11

 SQL LIKE Operator
LIKE is used in WHERE to search for a pattern. Use % for any number of characters and _ for exactly
one character.

SAMPLE DATA

products

id product_name

1 Apple Juice

2 Apple Pie

3 Banana Shake

4 Orange Juice

5 Pineapple

SQL SYNTAX

-- Starts with 'Apple'

SELECT * FROM products WHERE product_name LIKE 'Apple%';

-- Ends with 'Juice'

SELECT * FROM products WHERE product_name LIKE '%Juice';

-- Contains 'apple' anywhere

SELECT * FROM products WHERE product_name LIKE '%apple%';

-- Exactly 5 characters
SELECT * FROM products WHERE product_name LIKE '_____';

OUTPUT / RESULT

Pattern Matches

'Apple%' Apple Juice, Apple Pie

'%Juice' Apple Juice, Orange Juice

'%apple%' Apple Juice, Apple Pie, Pineapple

TIP LIKE is case-insensitive in most databases by default.

PRACTICE QUESTIONS

Q1: What does the pattern '%a_' match?

Answer: Any string with 'a' as the second-to-last character. Examples: 'can', 'plan', 'ban'.

Q2: How do you search for a literal % character using LIKE?

Answer: Use ESCAPE: LIKE '50\%' ESCAPE '\' — the backslash treats % as a literal character.

SQL Study Guide — Beginner to Intermediate Page 12

SQL Study Guide — Beginner to Intermediate Page 13
 CASE in SELECT & ORDER BY
CASE acts like an if-else statement inside SQL. It creates new columns in SELECT or custom sort
orders in ORDER BY.

SAMPLE DATA

students

name score

Alice 92

Bob 75

Carol 55

Dave 88

SQL SYNTAX

SELECT name, score,

CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'F'
END AS grade
FROM students
ORDER BY
CASE grade WHEN 'A' THEN 1
WHEN 'B' THEN 2
ELSE 3 END;

OUTPUT / RESULT

name score grade

Alice 92 A

Dave 88 B

Bob 75 C

Carol 55 F

TIP CASE WHEN evaluates conditions top-to-bottom and stops at the first TRUE condition.

PRACTICE QUESTIONS

Q1: What happens if no WHEN matches and there is no ELSE?

Answer: The CASE expression returns NULL for that row.

SQL Study Guide — Beginner to Intermediate Page 14

 Q2: Can you use CASE in a WHERE clause?

Answer: Yes, but it is uncommon. In WHERE you typically use AND/OR conditions directly.

SQL Study Guide — Beginner to Intermediate Page 15

 Nested CASE Statement
A nested CASE is a CASE expression inside another CASE. Use it when conditions have
sub-conditions that depend on the outer result.

SAMPLE DATA

employees

name dept salary

Alice IT 90000

Bob HR 45000

Carol IT 60000

Dave HR 80000

SQL SYNTAX

SELECT name, dept,

CASE
WHEN dept = 'IT' THEN
CASE
WHEN salary > 80000 THEN 'IT Senior'
ELSE 'IT Junior'
END
WHEN dept = 'HR' THEN
CASE
WHEN salary > 70000 THEN 'HR Senior'
ELSE 'HR Junior'
END
END AS level
FROM employees;

OUTPUT / RESULT

name dept level

Alice IT IT Senior

Bob HR HR Junior

Carol IT IT Junior

Dave HR HR Senior

TIP Keep nested CASE readable — avoid more than 2-3 levels. Consider a lookup table instead.

PRACTICE QUESTIONS

Q1: What is the difference between CASE WHEN and nested CASE?

SQL Study Guide — Beginner to Intermediate Page 16

 Answer: Regular CASE WHEN has one level of conditions. Nested CASE has a CASE inside another CASE
for sub-conditions.

Q2: Is there a limit to nesting depth?

Answer: Technically no limit, but practically keep it to 2-3 levels for readability.

SQL Study Guide — Beginner to Intermediate Page 17

 SQL Data Types
Data types define what kind of data a column can hold. Choosing the right type saves storage,
prevents errors, and improves performance.

SAMPLE DATA

Common Types

Type Use For Example

INT Whole numbers Age, Count

DECIMAL(p,s) Exact decimals Price: 99.99

VARCHAR(n) Variable text Name, Email

CHAR(n) Fixed text Country: 'US'

DATE Date only 2024-01-15

DATETIME Date + time 2024-01-15 10:30

BOOLEAN True/False is_active: 1

TEXT Long text Description, Bio

SQL SYNTAX

CREATE TABLE products (

product_id INT,
name VARCHAR(100),
price DECIMAL(10,2),
in_stock BOOLEAN,
created_at DATETIME
);

OUTPUT / RESULT

Column Type Why

product_id INT Whole number ID

name VARCHAR(100) Text up to 100 chars

price DECIMAL(10,2) Money: 2 decimal places

in_stock BOOLEAN Yes/No flag

created_at DATETIME Timestamp

TIP Use DECIMAL for money — never FLOAT. FLOAT has rounding errors (0.1+0.2 != 0.3 exactly).

PRACTICE QUESTIONS

Q1: Why use DECIMAL instead of FLOAT for prices?

SQL Study Guide — Beginner to Intermediate Page 18

 Answer: FLOAT is approximate and causes rounding errors. DECIMAL is exact — critical for financial data.

Q2: What is the difference between VARCHAR(50) and CHAR(50)?

Answer: VARCHAR uses only the space needed. CHAR always uses 50 characters, padding shorter values
with spaces.

SQL Study Guide — Beginner to Intermediate Page 19

 SQL Create Table
CREATE TABLE defines the structure (schema) of a new table — its columns, data types, and
constraints. This is the foundation of database design.

SQL SYNTAX

CREATE TABLE students (

student_id INT NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
date_of_birth DATE,
gpa DECIMAL(3,2) DEFAULT 0.00,
PRIMARY KEY (student_id)
);

OUTPUT / RESULT

Column Type Constraint

student_id INT NOT NULL, PRIMARY KEY

first_name VARCHAR(50) NOT NULL

email VARCHAR(100) UNIQUE

gpa DECIMAL(3,2) DEFAULT 0.00

TIP Use CREATE TABLE IF NOT EXISTS to avoid errors if the table already exists.

PRACTICE QUESTIONS

Q1: What does CREATE TABLE do if the table already exists?

Answer: It throws an error. Use CREATE TABLE IF NOT EXISTS to skip creation safely.

Q2: Can you add columns after a table is created?

Answer: Yes: ALTER TABLE students ADD COLUMN phone VARCHAR(20); — but plan your schema
carefully upfront.

SQL Study Guide — Beginner to Intermediate Page 20

 Insert Into All Columns
INSERT INTO adds new rows to a table. When inserting into ALL columns you can omit the column
names, but values must match the table's column order exactly.

SAMPLE DATA

students

student_id name gpa

1 Alice 3.8

SQL SYNTAX

-- Insert into ALL columns

INSERT INTO students
VALUES (2, 'Bob', 3.5);

-- Insert multiple rows at once

INSERT INTO students
VALUES
(3, 'Carol', 3.9),
(4, 'Dave', 2.8);

OUTPUT / RESULT

student_id name gpa

1 Alice 3.8

2 Bob 3.5

3 Carol 3.9

4 Dave 2.8

TIP Values must match the exact column order in the table definition — risky if the schema changes.

PRACTICE QUESTIONS

Q1: What is the risk of omitting column names in INSERT INTO?

Answer: If the table structure changes later, your INSERT statement breaks. Always specify column names
explicitly.

Q2: Can you insert a row without a value for a NOT NULL column?

Answer: No. The database throws an error. You must provide a value for every NOT NULL column with no
DEFAULT.

SQL Study Guide — Beginner to Intermediate Page 21

 Insert Into Certain Columns
You can specify only the columns you want to insert into. Unspecified columns automatically get
NULL or their DEFAULT value.

SAMPLE DATA

students

SQL SYNTAX

-- Specify only certain columns

INSERT INTO students (student_id, name, gpa)
VALUES (1, 'Alice', 3.8);

-- email will be NULL (no value provided)

-- Result: (1, 'Alice', NULL, 3.8)

OUTPUT / RESULT

student_id name email gpa

1 Alice NULL 3.8

TIP Always specify column names when inserting partial data — clearer and more robust.

PRACTICE QUESTIONS

Q1: What happens to a column with DEFAULT value when you skip it in INSERT?

Answer: The DEFAULT value is used automatically. E.g., if gpa DEFAULT 0.00, skipping it fills in 0.00.

Q2: Can you skip a PRIMARY KEY column during INSERT?

Answer: Only if it is set to AUTO_INCREMENT. Otherwise you must provide it.

SQL Study Guide — Beginner to Intermediate Page 22

 Copying Data Between Tables
Use INSERT INTO ... SELECT to copy rows from one table into another. The SELECT can include
WHERE, JOIN, and other clauses.

SAMPLE DATA

all_students

id name gpa

1 Alice 3.9

2 Bob 2.1

3 Carol 3.7

SQL SYNTAX

-- Copy students with gpa > 3.5

INSERT INTO honor_students (id, name, gpa)
SELECT id, name, gpa
FROM all_students
WHERE gpa > 3.5;

-- Copy entire table

INSERT INTO backup_students
SELECT * FROM all_students;

OUTPUT / RESULT

honor_students after copy

Alice (GPA 3.9)

Carol (GPA 3.7)

TIP The target table must already exist. Use CREATE TABLE AS SELECT to create AND fill in one step.

PRACTICE QUESTIONS

Q1: Difference between INSERT INTO...SELECT and CREATE TABLE AS SELECT?

Answer: INSERT INTO...SELECT copies into an EXISTING table. CREATE TABLE AS SELECT creates a
NEW table and fills it.

Q2: Can you copy data between two different databases?

Answer: Yes: INSERT INTO [Link] SELECT * FROM [Link] — if you have access to both.

SQL Study Guide — Beginner to Intermediate Page 23

 Sub Queries
A subquery is a query nested inside another query (in parentheses). It runs first and its result is used
by the outer query.

SAMPLE DATA

employees

name salary dept_id

Alice 90000 10

Bob 70000 20

Carol 85000 10

Dave 60000 20

SQL SYNTAX

-- Find employees earning above average

SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
);

-- Subquery in FROM clause

SELECT dept_id, avg_sal
FROM (
SELECT dept_id, AVG(salary) AS avg_sal
FROM employees GROUP BY dept_id
) AS dept_averages;

OUTPUT / RESULT

name salary

Alice 90000

Carol 85000

Types: scalar (single value), row, column, table subqueries. Correlated subqueries reference the outer
TIP
query.

PRACTICE QUESTIONS

Q1: Difference between correlated and non-correlated subquery?

Answer: Non-correlated runs once independently. Correlated references the outer query and runs once per
row — slower but more flexible.

SQL Study Guide — Beginner to Intermediate Page 24

 Q2: Can a subquery return multiple rows in a WHERE clause?

Answer: Yes, but use IN, ANY, or ALL instead of =. Example: WHERE salary IN (SELECT salary FROM
top_earners).

SQL Study Guide — Beginner to Intermediate Page 25

 Not Null Constraint
NOT NULL ensures a column cannot hold a NULL value. Every inserted or updated row MUST
provide a value for this column.

SQL SYNTAX

CREATE TABLE users (

user_id INT NOT NULL,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
bio TEXT -- bio CAN be null
);

-- This INSERT will FAIL:

INSERT INTO users (user_id, username)
VALUES (1, 'alice');
-- Error: Column 'email' cannot be null

OUTPUT / RESULT

Column Nullable? Why

user_id NO Required ID

username NO Must have a name

email NO Required contact

bio YES Optional field

TIP NULL means 'unknown' or 'missing'. NOT NULL is your first defence against incomplete data.

PRACTICE QUESTIONS

Q1: What is the difference between NULL and empty string ''?

Answer: NULL means unknown/absent. An empty string '' is an actual value — just blank. NOT NULL allows ''
but not NULL.

Q2: Can a PRIMARY KEY column be NULL?

Answer: No. A PRIMARY KEY automatically enforces NOT NULL.

SQL Study Guide — Beginner to Intermediate Page 26

 Unique Constraint
UNIQUE ensures all values in a column (or combination of columns) are distinct. No duplicate values
are allowed.

SQL SYNTAX

CREATE TABLE users (

user_id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);

-- SUCCESS:
INSERT INTO users VALUES (1, 'alice', 'alice@[Link]');

-- FAIL (duplicate username):

INSERT INTO users VALUES (2, 'alice', 'other@[Link]');
-- Error: Duplicate entry 'alice'

OUTPUT / RESULT

Scenario Result

Insert user_id=1 alice Success

Insert user_id=2 alice ERROR — duplicate

Two NULL emails Allowed (NULL != NULL)

TIP Most databases allow multiple NULLs in a UNIQUE column because NULL is not equal to NULL.

PRACTICE QUESTIONS

Q1: Difference between PRIMARY KEY and UNIQUE?

Answer: PRIMARY KEY: no NULLs, only one per table. UNIQUE: allows NULLs, a table can have many.

Q2: Can UNIQUE apply to multiple columns together?

Answer: Yes — composite unique: UNIQUE(first_name, last_name) means the combination must be unique.

SQL Study Guide — Beginner to Intermediate Page 27

 Check Constraint
CHECK ensures that values in a column satisfy a specific condition before being inserted or updated.
It enforces business rules at the database level.

SQL SYNTAX

CREATE TABLE products (

product_id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2) CHECK (price > 0),
quantity INT CHECK (quantity >= 0),
category VARCHAR(20) CHECK (
category IN ('Electronics','Clothing','Food')
)
);

-- FAIL: price is negative

INSERT INTO products VALUES (1, 'X', -5.00, 10, 'Electronics');

OUTPUT / RESULT

Column Check Rule Blocked Example

price price > 0 -5.00

quantity quantity >= 0 -3

category IN list 'Toys'

CHECK constraints run BEFORE the data is written — your business-rule enforcer inside the
TIP
database.

PRACTICE QUESTIONS

Q1: Can a CHECK constraint reference another column in the same table?

Answer: Yes! Example: CHECK (end_date > start_date) validates that end date is always after start date.

Q2: Does CHECK prevent NULL values?

Answer: No. NULL passes CHECK by default. Combine CHECK with NOT NULL to prevent both.

SQL Study Guide — Beginner to Intermediate Page 28

 Default Constraint
DEFAULT provides a fallback value for a column when no value is specified during INSERT. It
automatically fills in the default instead of leaving NULL.

SQL SYNTAX

CREATE TABLE orders (

order_id INT PRIMARY KEY,
customer VARCHAR(100),
status VARCHAR(20) DEFAULT 'Pending',
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
quantity INT DEFAULT 1
);

-- Insert WITHOUT status, created_at, quantity:

INSERT INTO orders (order_id, customer)
VALUES (1, 'Alice');
-- Defaults auto-fill the rest

OUTPUT / RESULT

Column Value Source

order_id 1 Provided

customer Alice Provided

status Pending DEFAULT

created_at 2024-05-27 10:30 DEFAULT (now)

quantity 1 DEFAULT

TIP CURRENT_TIMESTAMP is a dynamic default — always the current date and time at insertion.

PRACTICE QUESTIONS

Q1: Can you override a DEFAULT value?

Answer: Yes! Simply provide the value in INSERT. DEFAULT only applies when you omit that column.

Q2: Difference between DEFAULT and NOT NULL?

Answer: NOT NULL means the column cannot be empty. DEFAULT provides a fallback. They work great
together.

SQL Study Guide — Beginner to Intermediate Page 29

 Primary & Foreign Key Concept
Primary Key uniquely identifies each row in a table. Foreign Key is a column that references a
Primary Key in another table, creating a relationship (link) between tables.

SAMPLE DATA

customers (parent)

customer_id (PK) name

1 Alice

2 Bob

orders (child)

order_id (PK) customer_id (FK) item

101 1 Laptop

102 1 Mouse

103 2 Keyboard

SQL SYNTAX

-- Parent table
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);

-- Child table with Foreign Key

CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
item VARCHAR(100),
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
);

OUTPUT / RESULT

Concept Rule

Primary Key Unique, Not Null, one per table

Foreign Key Must match a PK in parent (or be NULL)

Referential Integrity Cannot insert order for non-existent customer

TIP The FK relationship is what makes JOINs meaningful — it's the actual link between tables.

PRACTICE QUESTIONS

SQL Study Guide — Beginner to Intermediate Page 30

 Q1: What happens if you insert a FK value that doesn't exist in the parent?

Answer: Foreign key constraint violation error. This 'referential integrity' prevents orphaned records.

Q2: Can a table have more than one foreign key?

Answer: Yes! A table can have multiple FKs referencing different parent tables.

SQL Study Guide — Beginner to Intermediate Page 31

 SQL Order of Execution
SQL clauses are NOT executed in the order you write them. Understanding the real execution order
prevents common beginner mistakes.

SQL SYNTAX

-- Written order:
SELECT dept, COUNT(*) AS emp_count
FROM employees
WHERE salary > 50000
GROUP BY dept
HAVING COUNT(*) > 2
ORDER BY emp_count DESC
LIMIT 3;

OUTPUT / RESULT

Step Clause What it does

1 FROM Load the table(s) / JOINs

2 WHERE Filter individual rows

3 GROUP BY Group remaining rows

4 HAVING Filter groups

5 SELECT Pick columns, compute aliases

6 ORDER BY Sort the result

7 LIMIT Return only N rows

WHERE runs BEFORE grouping. HAVING runs AFTER. That is why you cannot use SELECT aliases
TIP
in WHERE.

PRACTICE QUESTIONS

Q1: Why can't you use a SELECT alias in a WHERE clause?

Answer: WHERE executes at step 2, before SELECT (step 5). The alias doesn't exist yet. You CAN use it in
ORDER BY.

Q2: What is the difference between WHERE and HAVING?

Answer: WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY. Use
HAVING for aggregates like COUNT(*) > 5.

SQL Study Guide — Beginner to Intermediate Page 32