Introduction to SQL

Beginner
What is SQL?

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It allows you to:

  • Query data from databases
  • Insert, update, and delete records
  • Create and modify database structures
  • Control access to data
Our Sample Database

Throughout this guide, we will use a university database with these tables:

students
student_idINT PK
first_nameVARCHAR
last_nameVARCHAR
emailVARCHAR
majorVARCHAR
gpaDECIMAL
enrollment_dateDATE
courses
course_idINT PK
course_nameVARCHAR
departmentVARCHAR
creditsINT
professor_idINT FK
enrollments
enrollment_idINT PK
student_idINT FK
course_idINT FK
semesterVARCHAR
gradeVARCHAR
professors
professor_idINT PK
first_nameVARCHAR
last_nameVARCHAR
departmentVARCHAR
salaryDECIMAL
Basic SQL Syntax
SQL
SELECT column1, column2
FROM table_name
WHERE condition;

Key Rules:

  • SQL keywords are case-insensitive, but UPPERCASE is conventional
  • Statements end with a semicolon ;
  • Use single quotes ' ' for text values
  • Comments start with -- (single line) or /* */ (multi-line)
1
Activity: Your First Query

Try running a query to see all students in the database. Type SELECT * FROM students; in the playground below and click Run.

SQL Playground
Click "Run" to see results

SELECT Statements

Beginner
Selecting Specific Columns

Instead of selecting all columns with *, you can specify exactly which columns you need:

SQL
SELECT first_name, last_name, gpa
FROM students;
Column Aliases (AS)

Rename columns in your output for better readability:

SQL
SELECT 
 first_name AS "First Name",
 last_name AS "Last Name",
 gpa AS "Grade Point Average"
FROM students;
Removing Duplicates (DISTINCT)
SQL
SELECT DISTINCT major
FROM students;
LIMIT / TOP

Restrict the number of rows returned:

SQL
-- MySQL/PostgreSQL
SELECT * FROM students LIMIT 5;

-- SQL Server
SELECT TOP 5 * FROM students;

-- Oracle
SELECT * 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
OperatorDescriptionExample
=Equal toWHERE major = 'Computer Science'
!= or <>Not equal toWHERE gpa != 4.0
>Greater thanWHERE gpa > 3.0
<Less thanWHERE gpa < 2.5
>=Greater than or equalWHERE gpa >= 3.5
<=Less than or equalWHERE gpa <= 2.0
Logical Operators
SQL
-- AND: Both conditions must be true
SELECT * FROM students
WHERE major = 'Computer Science' AND gpa >= 3.5;

-- OR: At least one condition must be true
SELECT * FROM students
WHERE major = 'Computer Science' OR major = 'Mathematics';

-- NOT: Negates a condition
SELECT * FROM students
WHERE NOT major = 'Biology';

-- IN: Match any value in a list
SELECT * FROM students
WHERE major IN ('Computer Science', 'Mathematics', 'Physics');

-- BETWEEN: Range check
SELECT * FROM students
WHERE gpa BETWEEN 3.0 AND 3.5;
Pattern Matching (LIKE)
SQL
-- % = any number of characters
SELECT * FROM students
WHERE last_name LIKE 'S%'; -- Starts with S

-- _ = single character
SELECT * 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 order
SELECT * FROM students
ORDER BY gpa DESC;

-- Multiple columns
SELECT * FROM students
ORDER BY major ASC, gpa DESC;
Sorting by Column Position
SQL
SELECT first_name, last_name, gpa
FROM students
ORDER BY 3 DESC; -- 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
FunctionDescriptionExample
COUNT(*)Count all rowsSELECT COUNT(*) FROM students
COUNT(column)Count non-NULL valuesSELECT COUNT(gpa) FROM students
SUM(column)Sum of valuesSELECT SUM(credits) FROM courses
AVG(column)Average valueSELECT AVG(gpa) FROM students
MAX(column)Maximum valueSELECT MAX(gpa) FROM students
MIN(column)Minimum valueSELECT MIN(gpa) FROM students
String Functions
SQL
SELECT
                        CONCAT(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 TypeDescription
INNER JOINMatching rows from both tables only
LEFT JOINAll rows from left table + matching from right
RIGHT JOINAll rows from right table + matching from left
FULL JOINAll rows from both tables
CROSS JOINCartesian product of both tables
JOIN Syntax Examples
SQL
-- INNER JOIN: Only matching rows
SELECT 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 enrolled
SELECT 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 students
SELECT 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 both
SELECT * FROM students s
FULL OUTER JOIN enrollments e ON s.student_id = e.student_id;

-- CROSS JOIN: Cartesian product
SELECT s.first_name, c.course_name
FROM students s
CROSS JOIN courses c;

-- SELF JOIN: Table joined to itself
SELECT 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 major
SELECT major, COUNT(*) AS student_count
FROM students
GROUP BY major;

-- Average GPA per major
SELECT 
 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 students
SELECT major, COUNT(*) AS count
FROM students
GROUP BY major
HAVING COUNT(*) > 2;

-- WHERE vs HAVING
SELECT major, AVG(gpa) AS avg_gpa
FROM students
WHERE enrollment_date >= '2023-01-01' -- Filters rows BEFORE grouping
GROUP 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 IN
SELECT * FROM students
WHERE student_id IN (
                        SELECT student_id FROM enrollments
                        WHERE grade = 'A'
);

-- Correlated Subquery
SELECT s.first_name, s.last_name
FROM students s
WHERE s.gpa > (SELECT AVG(gpa) FROM students WHERE major = s.major);

-- Subquery in FROM clause
SELECT major, avg_gpa
FROM (
                        SELECT major, AVG(gpa) AS avg_gpa
                        FROM students
                        GROUP BY major
) AS major_stats
WHERE avg_gpa > 3.0;

-- EXISTS
SELECT * FROM students s
WHERE EXISTS (
                        SELECT 1 FROM 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 duplicates
SELECT first_name, last_name FROM students
UNION
SELECT first_name, last_name FROM professors;

-- UNION ALL: Combine results, keep duplicates
SELECT first_name, last_name FROM students
UNION ALL
SELECT first_name, last_name FROM professors;

-- INTERSECT: Rows common to both (not in MySQL)
SELECT department FROM courses
INTERSECT
SELECT department FROM professors;

-- EXCEPT/MINUS: Rows in first but not second
SELECT department FROM courses
EXCEPT
SELECT 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 CTE
WITH high_achievers AS (
                        SELECT * FROM students WHERE gpa >= 3.7
)
SELECT * FROM high_achievers;

-- Multiple CTEs
WITH 
 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 CTE
WITH RECURSIVE numbers AS (
                        SELECT 1 AS n
                        UNION ALL
                        SELECT n + 1 FROM 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 numbers
SELECT 
 first_name,
 gpa,
                        ROW_NUMBER() OVER (ORDER BY gpa DESC) AS rank_num
FROM students;

-- RANK: Handle ties
SELECT 
 first_name,
 gpa,
                        RANK() OVER (ORDER BY gpa DESC) AS rank_num
FROM students;

-- PARTITION BY: Rank within groups
SELECT 
 first_name,
 major,
 gpa,
                        RANK() OVER (
                        PARTITION BY major 
                        ORDER BY gpa DESC
 ) AS major_rank
FROM students;

-- Running totals
SELECT 
 course_name,
 credits,
                        SUM(credits) OVER (ORDER BY course_id) AS running_total
FROM courses;

-- LAG/LEAD: Previous/Next row values
SELECT 
 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 index
CREATE INDEX idx_student_email ON students(email);

-- Composite index
CREATE INDEX idx_name ON students(last_name, first_name);

-- Unique index
CREATE UNIQUE INDEX idx_unique_email ON students(email);

-- Drop index
DROP INDEX idx_student_email;

-- Check if index is used
EXPLAIN SELECT * 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
PropertyDescription
AtomicityAll operations complete or none do
ConsistencyDatabase remains in valid state
IsolationTransactions don't interfere
DurabilityCommitted changes persist
Transaction Control
SQL
-- Basic transaction
BEGIN TRANSACTION;

UPDATE students SET gpa = gpa + 0.1 WHERE student_id = 1;
INSERT INTO enrollments (student_id, course_id, semester, grade)
VALUES (1, 5, 'Fall 2024', 'A');

COMMIT; -- Save changes
-- OR
ROLLBACK; -- Undo changes

-- Savepoints
BEGIN TRANSACTION;
SAVEPOINT before_update;
UPDATE students SET gpa = 4.0;
-- Oops! Rollback to savepoint
ROLLBACK TO before_update;
COMMIT;

-- Isolation levels
SET 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 syntax
DELIMITER //
CREATE PROCEDUREGetStudentByMajor(IN major_name VARCHAR(100))
BEGIN
                        SELECT * FROM students WHERE major = major_name;
END //
DELIMITER ;

-- Call the procedure
CALLGetStudentByMajor('Computer Science');

-- With OUT parameter
DELIMITER //
CREATE PROCEDUREGetStudentCount(OUT total INT)
BEGIN
                        SELECT COUNT(*) INTO total FROM students;
END //
DELIMITER ;

CALLGetStudentCount(@count);
SELECT @count;
Functions vs Procedures
FeatureFunctionProcedure
Return valueMust return one valueCan return multiple or none
UsageCan be used in SELECTCalled with CALL
DML operationsCannot modify dataCan modify data
ParametersOnly ININ, 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 trigger
CREATE TRIGGER after_student_insert
AFTER INSERT ON students
FOR EACH ROW
BEGIN
                        INSERT 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 ROW
BEGIN
                        IFNEW.gpa < 0 ORNEW.gpa > 4.0 THEN
                        SIGNAL SQLSTATE '45000'
                        SET MESSAGE_TEXT = 'GPA must be between 0 and 4.0';
                        END IF;
END;

-- Drop trigger
DROP TRIGGER after_student_insert;
⏰ Scheduled Events
SQL
-- Enable event scheduler
SET GLOBAL event_scheduler = ON;

-- Create a scheduled event
CREATE EVENT archive_old_records
ON SCHEDULE EVERY 1 DAY
DO
                        DELETE FROM audit_log WHERE timestamp < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- One-time event
CREATE EVENT one_time_cleanup
ON SCHEDULE AT '2025-01-01 00:00:00'
DO
                        TRUNCATE 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 >= 0 AND gpa <= 4.0),
 enrollment_date DATE DEFAULT CURRENT_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
ActionDescription
CASCADEDelete/update related rows automatically
SET NULLSet foreign key to NULL
SET DEFAULTSet to default value
RESTRICTPrevent deletion if references exist
NO ACTIONSame 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
FormRuleExample Fix
1NFAtomic values, no repeating groupsSplit comma-separated values into rows
2NF1NF + no partial dependenciesMove non-key attributes to separate tables
3NF2NF + no transitive dependenciesRemove columns dependent on other non-key columns
BCNFEvery determinant is a candidate keyFurther 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!
);
SQL - Normalized (Good)
CREATE TABLE students (
 student_id INT PRIMARY KEY,
 student_name VARCHAR(100)
);

CREATE TABLE professors (
 professor_id INT PRIMARY KEY,
 professor_name VARCHAR(100),
 department_id INT,
                        FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE enrollments (
 student_id INT,
 course_id INT,
                        PRIMARY KEY (student_id, course_id),
                        FOREIGN KEY (student_id) REFERENCES students(student_id),
                        FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
17
Activity: Normalize This

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?

Free SQL Playground

Practice Anything
Available Tables
students
student_idINT PK
first_nameVARCHAR
last_nameVARCHAR
emailVARCHAR
majorVARCHAR
gpaDECIMAL
enrollment_dateDATE
courses
course_idINT PK
course_nameVARCHAR
departmentVARCHAR
creditsINT
professor_idINT FK
enrollments
enrollment_idINT PK
student_idINT FK
course_idINT FK
semesterVARCHAR
gradeVARCHAR
professors
professor_idINT PK
first_nameVARCHAR
last_nameVARCHAR
departmentVARCHAR
salaryDECIMAL
SQL Playground
Click "Run" to see results