-- MySQL/PostgreSQLSELECT * FROM students LIMIT5;
-- SQL ServerSELECT TOP5 * FROM students;
-- OracleSELECT * FROM students WHERE ROWNUM <= 5;
2
Activity: Select Practice
Write a query to get the first name, last name, and major of all students, but only show the first 3 rows. Use aliases to rename "major" to "Field of Study".
Need a hint?
Use SELECT, AS, and LIMIT 3. Remember to separate columns with commas!
SQL Playground
Click "Run" to see results
WHERE Clause
Beginner
Filtering Data
The WHERE clause filters rows based on conditions:
SQL
SELECT * FROM students
WHERE gpa >= 3.5;
Comparison Operators
Operator
Description
Example
=
Equal to
WHERE major = 'Computer Science'
!= or <>
Not equal to
WHERE gpa != 4.0
>
Greater than
WHERE gpa > 3.0
<
Less than
WHERE gpa < 2.5
>=
Greater than or equal
WHERE gpa >= 3.5
<=
Less than or equal
WHERE gpa <= 2.0
Logical Operators
SQL
-- AND: Both conditions must be trueSELECT * FROM students
WHERE major = 'Computer Science'AND gpa >= 3.5;
-- OR: At least one condition must be trueSELECT * FROM students
WHERE major = 'Computer Science'OR major = 'Mathematics';
-- NOT: Negates a conditionSELECT * FROM students
WHERE NOT major = 'Biology';
-- IN: Match any value in a listSELECT * FROM students
WHERE major IN ('Computer Science', 'Mathematics', 'Physics');
-- BETWEEN: Range checkSELECT * FROM students
WHERE gpa BETWEEN3.0AND3.5;
Pattern Matching (LIKE)
SQL
-- % = any number of charactersSELECT * FROM students
WHERE last_name LIKE'S%'; -- Starts with S-- _ = single characterSELECT * FROM students
WHERE first_name LIKE'_ohn'; -- John, john, etc.-- ILIKE = case-insensitive (PostgreSQL)SELECT * FROM students
WHERE email ILIKE'%@university.edu';
3
Activity: Filter Challenge
Find all students whose last name starts with "M" AND have a GPA greater than 3.0. Show only their first name, last name, and GPA.
SQL Playground
Click "Run" to see results
ORDER BY
Beginner
Sorting Results
SQL
-- Ascending order (default)SELECT * FROM students
ORDER BY gpa;
-- Descending orderSELECT * FROM students
ORDER BY gpa DESC;
-- Multiple columnsSELECT * FROM students
ORDER BY major ASC, gpa DESC;
Sorting by Column Position
SQL
SELECT first_name, last_name, gpa
FROM students
ORDER BY3DESC; -- Sorts by 3rd column (gpa)
Not recommended for production code - use column names instead.
4
Activity: Sorting Practice
Get all students, sorted by enrollment date (newest first), then by last name (A-Z). Limit to 5 results.
SQL Playground
Click "Run" to see results
Built-in Functions
Beginner
Aggregate Functions
Function
Description
Example
COUNT(*)
Count all rows
SELECT COUNT(*) FROM students
COUNT(column)
Count non-NULL values
SELECT COUNT(gpa) FROM students
SUM(column)
Sum of values
SELECT SUM(credits) FROM courses
AVG(column)
Average value
SELECT AVG(gpa) FROM students
MAX(column)
Maximum value
SELECT MAX(gpa) FROM students
MIN(column)
Minimum value
SELECT MIN(gpa) FROM students
String Functions
SQL
SELECTCONCAT(first_name, ' ', last_name) AS full_name,
UPPER(email) AS email_upper,
LOWER(major) AS major_lower,
LENGTH(first_name) AS name_length,
SUBSTRING(email, 1, 5) AS email_prefix
FROM students;
Date Functions
SQL
SELECT
first_name,
enrollment_date,
YEAR(enrollment_date) AS year_enrolled,
MONTH(enrollment_date) AS month_enrolled,
DATEDIFF(CURRENT_DATE, enrollment_date) AS days_enrolled
FROM students;
5
Activity: Functions Challenge
Write a query that shows: the total number of students, the average GPA, the highest GPA, and the lowest GPA. Give each result a descriptive alias.
SQL Playground
Click "Run" to see results
JOINs
Intermediate
Types of JOINs
JOIN Type
Description
INNER JOIN
Matching rows from both tables only
LEFT JOIN
All rows from left table + matching from right
RIGHT JOIN
All rows from right table + matching from left
FULL JOIN
All rows from both tables
CROSS JOIN
Cartesian product of both tables
JOIN Syntax Examples
SQL
-- INNER JOIN: Only matching rowsSELECT s.first_name, s.last_name, c.course_name
FROM students s
INNER JOIN enrollments e ON s.student_id = e.student_id
INNER JOIN courses c ON e.course_id = c.course_id;
-- LEFT JOIN: All students even if not enrolledSELECT s.first_name, s.last_name, c.course_name
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
LEFT JOIN courses c ON e.course_id = c.course_id;
-- RIGHT JOIN: All courses even with no studentsSELECT s.first_name, c.course_name
FROM students s
RIGHT JOIN enrollments e ON s.student_id = e.student_id
RIGHT JOIN courses c ON e.course_id = c.course_id;
-- FULL OUTER JOIN: All records from bothSELECT * FROM students s
FULL OUTER JOIN enrollments e ON s.student_id = e.student_id;
-- CROSS JOIN: Cartesian productSELECT s.first_name, c.course_name
FROM students s
CROSS JOIN courses c;
-- SELF JOIN: Table joined to itselfSELECT a.first_name, b.first_name
FROM students a, students b
WHERE a.major = b.major AND a.student_id != b.student_id;
6
Activity: JOIN Challenge
Write a query to show each professor's name along with the courses they teach. Use appropriate JOINs and aliases. Include professors even if they do not teach any courses yet.
SQL Playground
Click "Run" to see results
GROUP BY & HAVING
Intermediate
GROUP BY Basics
SQL
-- Count students per majorSELECT major, COUNT(*) AS student_count
FROM students
GROUP BY major;
-- Average GPA per majorSELECT
major,
COUNT(*) AS total,
AVG(gpa) AS avg_gpa,
MAX(gpa) AS highest_gpa
FROM students
GROUP BY major;
HAVING Clause
HAVING filters groups (like WHERE filters rows):
SQL
-- Majors with more than 2 studentsSELECT major, COUNT(*) AS count
FROM students
GROUP BY major
HAVING COUNT(*) > 2;
-- WHERE vs HAVINGSELECT major, AVG(gpa) AS avg_gpa
FROM students
WHERE enrollment_date >= '2023-01-01'-- Filters rows BEFORE groupingGROUP BY major
HAVING AVG(gpa) >= 3.5; -- Filters groups AFTER grouping
7
Activity: Grouping Challenge
Find departments that offer more than 1 course, showing the department name and course count. Sort by course count descending.
SQL Playground
Click "Run" to see results
Subqueries
Intermediate
Subquery Types
SQL
-- Scalar Subquery (returns single value)SELECT first_name, last_name, gpa
FROM students
WHERE gpa > (SELECT AVG(gpa) FROM students);
-- Multi-row Subquery with INSELECT * FROM students
WHERE student_id IN (
SELECT student_id FROM enrollments
WHERE grade = 'A'
);
-- Correlated SubquerySELECT s.first_name, s.last_name
FROM students s
WHERE s.gpa > (SELECT AVG(gpa) FROM students WHERE major = s.major);
-- Subquery in FROM clauseSELECT major, avg_gpa
FROM (
SELECT major, AVG(gpa) AS avg_gpa
FROM students
GROUP BY major
) AS major_stats
WHERE avg_gpa > 3.0;
-- EXISTSSELECT * FROM students s
WHERE EXISTS (
SELECT1FROM enrollments e
WHERE e.student_id = s.student_id
);
8
Activity: Subquery Challenge
Find all students who have a GPA above the overall average GPA. Show their first name, last name, and GPA.
SQL Playground
Click "Run" to see results
Set Operations
Intermediate
UNION, INTERSECT, EXCEPT
SQL
-- UNION: Combine results, remove duplicatesSELECT first_name, last_name FROM students
UNIONSELECT first_name, last_name FROM professors;
-- UNION ALL: Combine results, keep duplicatesSELECT first_name, last_name FROM students
UNION ALLSELECT first_name, last_name FROM professors;
-- INTERSECT: Rows common to both (not in MySQL)SELECT department FROM courses
INTERSECTSELECT department FROM professors;
-- EXCEPT/MINUS: Rows in first but not secondSELECT department FROM courses
EXCEPTSELECT department FROM professors;
Important Rules
Both queries must have the same number of columns
Corresponding columns must have compatible data types
Column names come from the first SELECT
ORDER BY goes at the very end
9
Activity: Set Operations
Create a single list of all unique first names that appear in both the students and professors tables.
SQL Playground
Click "Run" to see results
CTEs & Recursion
Advanced
Common Table Expressions (WITH)
SQL
-- Basic CTEWITH high_achievers AS (
SELECT * FROM students WHERE gpa >= 3.7
)
SELECT * FROM high_achievers;
-- Multiple CTEsWITH
cs_students AS (
SELECT * FROM students WHERE major = 'Computer Science'
),
cs_enrollments AS (
SELECT e.* FROM enrollments e
JOIN cs_students cs ON e.student_id = cs.student_id
)
SELECT * FROM cs_enrollments;
-- Recursive CTEWITH RECURSIVE numbers AS (
SELECT1AS n
UNION ALLSELECT n + 1FROM numbers WHERE n < 10
)
SELECT * FROM numbers;
10
Activity: CTE Practice
Use a CTE to find the average GPA per major, then select only those majors where the average is above 3.2.
SQL Playground
Click "Run" to see results
Window Functions
Advanced
OVER Clause
SQL
-- ROW_NUMBER: Assign unique row numbersSELECT
first_name,
gpa,
ROW_NUMBER() OVER (ORDER BY gpa DESC) AS rank_num
FROM students;
-- RANK: Handle tiesSELECT
first_name,
gpa,
RANK() OVER (ORDER BY gpa DESC) AS rank_num
FROM students;
-- PARTITION BY: Rank within groupsSELECT
first_name,
major,
gpa,
RANK() OVER (
PARTITION BY major
ORDER BY gpa DESC
) AS major_rank
FROM students;
-- Running totalsSELECT
course_name,
credits,
SUM(credits) OVER (ORDER BY course_id) AS running_total
FROM courses;
-- LAG/LEAD: Previous/Next row valuesSELECT
first_name,
gpa,
LAG(gpa) OVER (ORDER BY gpa) AS prev_gpa,
LEAD(gpa) OVER (ORDER BY gpa) AS next_gpa
FROM students;
11
Activity: Window Functions
Find the top 2 students by GPA within each major using a window function.
SQL Playground
Click "Run" to see results
Indexes & Optimization
Advanced
Creating Indexes
SQL
-- Single column indexCREATE INDEX idx_student_email ON students(email);
-- Composite indexCREATE INDEX idx_name ON students(last_name, first_name);
-- Unique indexCREATE UNIQUE INDEX idx_unique_email ON students(email);
-- Drop indexDROP INDEX idx_student_email;
-- Check if index is usedEXPLAINSELECT * FROM students WHERE email = 'john@example.com';
Query Optimization Tips
Use EXPLAIN to analyze query execution plans
Avoid SELECT * - specify only needed columns
Use indexes on columns in WHERE, JOIN, and ORDER BY
Avoid functions on indexed columns in WHERE
Use LIMIT for pagination instead of OFFSET for large datasets
Consider covering indexes (include all queried columns)
Use appropriate data types (INT vs BIGINT, VARCHAR length)
12
Activity: Index Design
Write the SQL to create an index on the enrollments table that would speed up queries filtering by both student_id and course_id together.
SQL Playground
Click "Run" to see results
Transactions
Advanced
ACID Properties
Property
Description
Atomicity
All operations complete or none do
Consistency
Database remains in valid state
Isolation
Transactions don't interfere
Durability
Committed changes persist
Transaction Control
SQL
-- Basic transactionBEGIN TRANSACTION;
UPDATE students SET gpa = gpa + 0.1WHERE student_id = 1;
INSERT INTO enrollments (student_id, course_id, semester, grade)
VALUES (1, 5, 'Fall 2024', 'A');
COMMIT; -- Save changes-- ORROLLBACK; -- Undo changes-- SavepointsBEGIN TRANSACTION;
SAVEPOINT before_update;
UPDATE students SET gpa = 4.0;
-- Oops! Rollback to savepointROLLBACK TO before_update;
COMMIT;
-- Isolation levelsSET TRANSACTION ISOLATION LEVELSERIALIZABLE;
13
Activity: Transaction Practice
Write a transaction that: 1) Updates a student's GPA to 3.8, 2) Inserts a new enrollment record for that student, and 3) Commits the changes.
SQL Playground
Click "Run" to see results
Stored Procedures
Master
Creating Procedures
SQL
-- MySQL syntaxDELIMITER //
CREATE PROCEDUREGetStudentByMajor(IN major_name VARCHAR(100))
BEGINSELECT * FROM students WHERE major = major_name;
END //
DELIMITER ;
-- Call the procedureCALLGetStudentByMajor('Computer Science');
-- With OUT parameterDELIMITER //
CREATE PROCEDUREGetStudentCount(OUT total INT)
BEGINSELECT COUNT(*) INTO total FROM students;
END //
DELIMITER ;
CALLGetStudentCount(@count);
SELECT @count;
Functions vs Procedures
Feature
Function
Procedure
Return value
Must return one value
Can return multiple or none
Usage
Can be used in SELECT
Called with CALL
DML operations
Cannot modify data
Can modify data
Parameters
Only IN
IN, OUT, INOUT
14
Activity: Stored Procedure
Create a stored procedure that accepts a department name and returns all courses in that department along with the professor's name.
SQL Playground
Click "Run" to see results
Triggers & Events
Master
Triggers
SQL
-- AFTER INSERT triggerCREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROWBEGININSERT INTO audit_log (table_name, action, timestamp)
VALUES ('students', 'INSERT', NOW());
END;
-- BEFORE UPDATE trigger (validation)CREATE TRIGGER before_gpa_update
BEFORE UPDATE ON students
FOR EACH ROWBEGINIFNEW.gpa < 0ORNEW.gpa > 4.0THENSIGNAL SQLSTATE'45000'SET MESSAGE_TEXT = 'GPA must be between 0 and 4.0';
END IF;
END;
-- Drop triggerDROP TRIGGER after_student_insert;
⏰ Scheduled Events
SQL
-- Enable event schedulerSET GLOBAL event_scheduler = ON;
-- Create a scheduled eventCREATE EVENT archive_old_records
ON SCHEDULE EVERY1DAYDODELETE FROM audit_log WHERE timestamp < DATE_SUB(NOW(), INTERVAL1YEAR);
-- One-time eventCREATE EVENT one_time_cleanup
ON SCHEDULE AT'2025-01-01 00:00:00'DOTRUNCATE TABLE temp_data;
15
Activity: Trigger Design
Create a trigger that automatically logs whenever a student's GPA is updated, recording the old GPA, new GPA, and timestamp.
SQL Playground
Click "Run" to see results
Database Design
Master
Design Principles
Entity-Relationship (ER) Modeling:Identify entities, attributes, and relationships
Primary Keys:Unique identifier for each row (usually auto-increment INT)
Foreign Keys:Establish relationships between tables
Constraints:NOT NULL, UNIQUE, CHECK, DEFAULT
Data Types:Choose appropriate types to save space and ensure integrity
Creating Tables
SQL
CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
major VARCHAR(100) DEFAULT'Undeclared',
gpa DECIMAL(3,2) CHECK (gpa >= 0AND gpa <= 4.0),
enrollment_date DATE DEFAULTCURRENT_DATE
);
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
semester VARCHAR(20) NOT NULL,
grade VARCHAR(2),
FOREIGN KEY (student_id) REFERENCES students(student_id)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (course_id) REFERENCES courses(course_id)
ON DELETE CASCADE
);
Referential Actions
Action
Description
CASCADE
Delete/update related rows automatically
SET NULL
Set foreign key to NULL
SET DEFAULT
Set to default value
RESTRICT
Prevent deletion if references exist
NO ACTION
Same as RESTRICT (checked at end)
16
Activity: Table Design
Design a "departments" table with: department_id (PK, auto-increment), department_name (NOT NULL, UNIQUE), budget (DECIMAL, DEFAULT 0), and head_professor_id (FK to professors).
SQL Playground
Click "Run" to see results
Normalization
Master
Normal Forms
Form
Rule
Example Fix
1NF
Atomic values, no repeating groups
Split comma-separated values into rows
2NF
1NF + no partial dependencies
Move non-key attributes to separate tables
3NF
2NF + no transitive dependencies
Remove columns dependent on other non-key columns
BCNF
Every determinant is a candidate key
Further decompose if needed
Before vs After Normalization
SQL - Denormalized (Bad)
CREATE TABLE student_courses_bad (
student_id INT,
student_name VARCHAR(100),
courses VARCHAR(500), -- "Math,Physics,Chem" - violates 1NF!
professor_name VARCHAR(100), -- depends on course, not student - violates 3NF!
department VARCHAR(100) -- depends on professor - transitive dependency!
);
Given a table with: order_id, customer_name, customer_email, product1, product2, product3, total_price - identify which normal forms are violated and how to fix them.
Need a hint?
Think about: repeating groups (products), partial dependencies (customer info depends on customer, not order), and whether each cell has a single value.
SQL Playground
Click "Run" to see results
SQL Cheatsheet
Reference
SELECT
SELECT col FROM table WHERE condition ORDER BY col LIMIT n;
INSERT
INSERT INTO table (col1, col2) VALUES (val1, val2);
UPDATE
UPDATE table SET col = val WHERE condition;
DELETE
DELETE FROM table WHERE condition;
INNER JOIN
SELECT * FROM A JOIN B ON A.id = B.id;
GROUP BY
SELECT col, COUNT(*) FROM table GROUP BY col HAVING COUNT(*) > 1;
Subquery
SELECT * FROM table WHERE col > (SELECT AVG(col) FROM table);
CTE
WITH cte AS (SELECT ...) SELECT * FROM cte;
Window
SELECT col, ROW_NUMBER() OVER (PARTITION BY col2 ORDER BY col3) FROM table;
Transaction
BEGIN; ... COMMIT; / ROLLBACK;
Index
CREATE INDEX idx_name ON table(col);
Create Table
CREATE TABLE t (id INT PRIMARY KEY, name VARCHAR(50));
SQL Knowledge Quiz
Test Yourself
Question 1: What does SELECT DISTINCT do?
Question 2: Which JOIN returns all rows from the left table?
Question 3: What is the difference between WHERE and HAVING?
Question 4: What does a CTE (Common Table Expression) do?
Question 5: Which normal form requires atomic values?