Learn SQL - 22 Code Examples & CST Typing Practice Test

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases, enabling querying, insertion, updating, and deletion of data efficiently.

View all 22 SQL code examples →

Learn SQL with Real Code Examples

Updated Nov 27, 2025

Introduction & Fundamentals Setup & Configuration Architecture & Deep Internals Performance & Security Development Workflow Learning & Career Growth Business & Strategy Examples

Code Sample Descriptions

PostgreSQL Advanced Queries

-- Create tables with relationships
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    status VARCHAR(20) DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO users (username, email) VALUES 
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com'),
    ('charlie', 'charlie@example.com');

INSERT INTO posts (user_id, title, content, status) VALUES 
    (1, 'First Post', 'This is my first blog post.', 'published'),
    (1, 'Second Post', 'Another interesting post.', 'published'),
    (2, 'Draft Post', 'This is still a draft.', 'draft');

INSERT INTO comments (post_id, user_id, content) VALUES 
    (1, 2, 'Great first post!'),
    (1, 3, 'Thanks for sharing.'),
    (2, 3, 'Very informative.');

-- Complex queries with JOINs, aggregations, and CTEs
WITH user_stats AS (
    SELECT 
        u.id,
        u.username,
        COUNT(DISTINCT p.id) as post_count,
        COUNT(DISTINCT c.id) as comment_count,
        MAX(p.created_at) as last_post_date
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id AND p.status = 'published'
    LEFT JOIN comments c ON u.id = c.user_id
    GROUP BY u.id, u.username
),
popular_posts AS (
    SELECT 
        p.id,
        p.title,
        u.username as author,
        COUNT(c.id) as comment_count,
        RANK() OVER (ORDER BY COUNT(c.id) DESC) as popularity_rank
    FROM posts p
    JOIN users u ON p.user_id = u.id
    LEFT JOIN comments c ON p.id = c.post_id
    WHERE p.status = 'published'
    GROUP BY p.id, p.title, u.username
)
SELECT 
    us.username,
    us.post_count,
    us.comment_count,
    us.last_post_date,
    COALESCE(pp.title, 'No posts') as most_popular_post,
    COALESCE(pp.comment_count, 0) as most_popular_post_comments
FROM user_stats us
LEFT JOIN popular_posts pp ON pp.popularity_rank = 1
ORDER BY us.post_count DESC, us.comment_count DESC;

-- Window functions example
SELECT 
    u.username,
    p.title,
    p.created_at,
    LAG(p.created_at) OVER (
        PARTITION BY u.id 
        ORDER BY p.created_at
    ) as previous_post_date,
    COUNT(*) OVER (
        PARTITION BY u.id
    ) as total_user_posts,
    ROW_NUMBER() OVER (
        PARTITION BY u.id 
        ORDER BY p.created_at
    ) as post_sequence
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE p.status = 'published'
ORDER BY u.username, p.created_at;

Demonstrates advanced SQL features including JOINs, CTEs, window functions, and complex aggregations.

Let’s Try →

SQL Employee Management System

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10,2),
    department_id INTEGER REFERENCES departments(department_id)
);

INSERT INTO departments (department_name, location) VALUES
    ('Engineering', 'New York'),
    ('Marketing', 'Chicago'),
    ('Finance', 'Boston');

INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES
    ('John', 'Smith', 'john@example.com', '2022-01-15', 85000, 1),
    ('Sarah', 'Johnson', 'sarah@example.com', '2021-03-10', 92000, 1),
    ('Mike', 'Brown', 'mike@example.com', '2023-05-11', 70000, 2),
    ('Emma', 'Davis', 'emma@example.com', '2020-07-21', 88000, 3);

SELECT
    d.department_name,
    COUNT(e.employee_id) AS total_employees,
    AVG(e.salary) AS avg_salary,
    MAX(e.salary) AS highest_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

SELECT
    first_name,
    last_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

CREATE VIEW employee_summary AS
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM employee_summary;

Demonstrates employee, department, payroll, and reporting operations using joins, aggregations, views, and window functions.

Let’s Try →

SQL Employee Management System

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10,2),
    department_id INTEGER REFERENCES departments(department_id)
);

INSERT INTO departments (department_name, location) VALUES
    ('Engineering', 'New York'),
    ('Marketing', 'Chicago'),
    ('Finance', 'Boston');

INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES
    ('John', 'Smith', 'john@example.com', '2022-01-15', 85000, 1),
    ('Sarah', 'Johnson', 'sarah@example.com', '2021-03-10', 92000, 1),
    ('Mike', 'Brown', 'mike@example.com', '2023-05-11', 70000, 2),
    ('Emma', 'Davis', 'emma@example.com', '2020-07-21', 88000, 3);

SELECT
    d.department_name,
    COUNT(e.employee_id) AS total_employees,
    AVG(e.salary) AS avg_salary,
    MAX(e.salary) AS highest_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

SELECT
    first_name,
    last_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

CREATE VIEW employee_summary AS
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM employee_summary;

Demonstrates employee, department, payroll, and reporting operations using joins, aggregations, views, and window functions.

Let’s Try →

SQL Inventory Management

CREATE TABLE suppliers (
    supplier_id SERIAL PRIMARY KEY,
    supplier_name VARCHAR(100)
);

CREATE TABLE inventory (
    item_id SERIAL PRIMARY KEY,
    item_name VARCHAR(100),
    stock_quantity INTEGER,
    reorder_level INTEGER,
    supplier_id INTEGER REFERENCES suppliers(supplier_id)
);

INSERT INTO suppliers (supplier_name) VALUES
    ('Tech Supply Co'),
    ('Global Parts Ltd');

INSERT INTO inventory (
    item_name,
    stock_quantity,
    reorder_level,
    supplier_id
) VALUES
    ('SSD Drive', 50, 20, 1),
    ('RAM Module', 15, 25, 1),
    ('Power Supply', 10, 15, 2);

SELECT
    item_name,
    stock_quantity,
    reorder_level,
    CASE
        WHEN stock_quantity < reorder_level THEN 'REORDER'
        ELSE 'OK'
    END AS inventory_status
FROM inventory;

SELECT
    s.supplier_name,
    COUNT(i.item_id) AS supplied_items
FROM suppliers s
JOIN inventory i
    ON s.supplier_id = i.supplier_id
GROUP BY s.supplier_name;

CREATE INDEX idx_inventory_stock
ON inventory(stock_quantity);

SELECT *
FROM inventory
ORDER BY stock_quantity ASC;

Tracks stock levels, suppliers, inventory movement, and reorder alerts.

Let’s Try →

SQL Customer Segmentation

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE purchases (
    purchase_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    purchase_amount DECIMAL(10,2),
    purchase_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob'),
    ('Charlie');

INSERT INTO purchases (
    customer_id,
    purchase_amount,
    purchase_date
) VALUES
    (1, 500, '2025-01-01'),
    (1, 700, '2025-02-01'),
    (2, 100, '2025-02-10'),
    (3, 2000, '2025-01-15');

WITH customer_value AS (
    SELECT
        customer_id,
        COUNT(*) AS purchase_count,
        SUM(purchase_amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
)
SELECT
    customer_id,
    purchase_count,
    total_spent,
    CASE
        WHEN total_spent > 1000 THEN 'VIP'
        WHEN total_spent > 300 THEN 'REGULAR'
        ELSE 'NEW'
    END AS segment
FROM customer_value;

SELECT
    customer_id,
    total_spent,
    NTILE(4) OVER (
        ORDER BY total_spent DESC
    ) AS spending_quartile
FROM (
    SELECT
        customer_id,
        SUM(purchase_amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
) stats;

Segments customers by purchase behavior, frequency, and lifetime value.

Let’s Try →

SQL Customer Segmentation

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE purchases (
    purchase_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    purchase_amount DECIMAL(10,2),
    purchase_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob'),
    ('Charlie');

INSERT INTO purchases (
    customer_id,
    purchase_amount,
    purchase_date
) VALUES
    (1, 500, '2025-01-01'),
    (1, 700, '2025-02-01'),
    (2, 100, '2025-02-10'),
    (3, 2000, '2025-01-15');

WITH customer_value AS (
    SELECT
        customer_id,
        COUNT(*) AS purchase_count,
        SUM(purchase_amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
)
SELECT
    customer_id,
    purchase_count,
    total_spent,
    CASE
        WHEN total_spent > 1000 THEN 'VIP'
        WHEN total_spent > 300 THEN 'REGULAR'
        ELSE 'NEW'
    END AS segment
FROM customer_value;

SELECT
    customer_id,
    total_spent,
    NTILE(4) OVER (
        ORDER BY total_spent DESC
    ) AS spending_quartile
FROM (
    SELECT
        customer_id,
        SUM(purchase_amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
) stats;

Segments customers by purchase behavior, frequency, and lifetime value.

Let’s Try →

SQL Financial Transactions Analysis

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    account_holder VARCHAR(100),
    account_type VARCHAR(50)
);

CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    account_id INTEGER REFERENCES accounts(account_id),
    amount DECIMAL(12,2),
    transaction_type VARCHAR(20),
    transaction_date DATE
);

INSERT INTO accounts (account_holder, account_type) VALUES
    ('Alice Johnson', 'Savings'),
    ('Bob Smith', 'Checking');

INSERT INTO transactions (
    account_id,
    amount,
    transaction_type,
    transaction_date
) VALUES
    (1, 1500, 'deposit', '2025-01-01'),
    (1, 300, 'withdrawal', '2025-01-10'),
    (2, 2200, 'deposit', '2025-01-03'),
    (2, 500, 'withdrawal', '2025-01-15');

SELECT
    account_id,
    SUM(
        CASE
            WHEN transaction_type = 'deposit' THEN amount
            ELSE -amount
        END
    ) AS current_balance
FROM transactions
GROUP BY account_id;

SELECT
    transaction_date,
    amount,
    SUM(amount) OVER (
        ORDER BY transaction_date
    ) AS running_total
FROM transactions;

Analyzes financial transactions, balances, and monthly spending trends using aggregations and window functions.

Let’s Try →

SQL Banking System Queries

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE bank_accounts (
    account_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    balance DECIMAL(12,2)
);

INSERT INTO customers (customer_name, email) VALUES
    ('John Doe', 'john@example.com'),
    ('Emma Wilson', 'emma@example.com');

INSERT INTO bank_accounts (
    customer_id,
    balance
) VALUES
    (1, 10000),
    (2, 7500);

SELECT
    c.customer_name,
    b.account_id,
    b.balance
FROM customers c
JOIN bank_accounts b
    ON c.customer_id = b.customer_id;

SELECT
    AVG(balance) AS average_balance,
    MAX(balance) AS highest_balance,
    MIN(balance) AS lowest_balance
FROM bank_accounts;

Demonstrates customer accounts, transactions, and account summaries.

Let’s Try →

SQL Order Processing Workflow

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_status VARCHAR(30),
    order_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob');

INSERT INTO orders (
    customer_id,
    order_status,
    order_date
) VALUES
    (1, 'Pending', '2025-02-01'),
    (1, 'Shipped', '2025-02-02'),
    (2, 'Delivered', '2025-02-03');

SELECT
    order_status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;

UPDATE orders
SET order_status = 'Completed'
WHERE order_status = 'Delivered';

SELECT *
FROM orders;

Handles order lifecycle, status updates, and customer purchases.

Let’s Try →

SQL Order Processing Workflow

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_status VARCHAR(30),
    order_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob');

INSERT INTO orders (
    customer_id,
    order_status,
    order_date
) VALUES
    (1, 'Pending', '2025-02-01'),
    (1, 'Shipped', '2025-02-02'),
    (2, 'Delivered', '2025-02-03');

SELECT
    order_status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;

UPDATE orders
SET order_status = 'Completed'
WHERE order_status = 'Delivered';

SELECT *
FROM orders;

Handles order lifecycle, status updates, and customer purchases.

Let’s Try →

SQL Order Processing Workflow

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_status VARCHAR(30),
    order_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob');

INSERT INTO orders (
    customer_id,
    order_status,
    order_date
) VALUES
    (1, 'Pending', '2025-02-01'),
    (1, 'Shipped', '2025-02-02'),
    (2, 'Delivered', '2025-02-03');

SELECT
    order_status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;

UPDATE orders
SET order_status = 'Completed'
WHERE order_status = 'Delivered';

SELECT *
FROM orders;

Handles order lifecycle, status updates, and customer purchases.

Let’s Try →

SQL Healthcare Records Management

CREATE TABLE patients (
    patient_id SERIAL PRIMARY KEY,
    patient_name VARCHAR(100),
    date_of_birth DATE
);

CREATE TABLE appointments (
    appointment_id SERIAL PRIMARY KEY,
    patient_id INTEGER REFERENCES patients(patient_id),
    appointment_date DATE,
    doctor_name VARCHAR(100)
);

INSERT INTO patients (
    patient_name,
    date_of_birth
) VALUES
    ('Alice Brown', '1990-05-20'),
    ('David Green', '1985-09-12');

INSERT INTO appointments (
    patient_id,
    appointment_date,
    doctor_name
) VALUES
    (1, '2025-03-01', 'Dr. Smith'),
    (2, '2025-03-02', 'Dr. Johnson');

SELECT
    p.patient_name,
    a.appointment_date,
    a.doctor_name
FROM patients p
JOIN appointments a
    ON p.patient_id = a.patient_id;

SELECT
    doctor_name,
    COUNT(*) AS total_appointments
FROM appointments
GROUP BY doctor_name;

Stores patients, appointments, and medical records with reporting queries.

Let’s Try →

SQL University Management System

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100),
    credits INTEGER
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES students(student_id),
    course_id INTEGER REFERENCES courses(course_id),
    enrollment_date DATE
);

INSERT INTO students (first_name, last_name, email) VALUES
    ('Alice', 'Johnson', 'alice@example.com'),
    ('Bob', 'Smith', 'bob@example.com');

INSERT INTO courses (course_name, credits) VALUES
    ('Database Systems', 3),
    ('Algorithms', 4);

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
    (1, 1, '2025-01-10'),
    (1, 2, '2025-01-10'),
    (2, 1, '2025-01-15');

SELECT
    s.first_name,
    s.last_name,
    c.course_name
FROM students s
JOIN enrollments e
    ON s.student_id = e.student_id
JOIN courses c
    ON c.course_id = e.course_id
ORDER BY s.first_name;

Manages students, courses, enrollments, and academic reporting.

Let’s Try →

SQL Library Management Database

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(150),
    author VARCHAR(100),
    available BOOLEAN DEFAULT TRUE
);

CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    member_name VARCHAR(100)
);

CREATE TABLE borrow_records (
    record_id SERIAL PRIMARY KEY,
    book_id INTEGER REFERENCES books(book_id),
    member_id INTEGER REFERENCES members(member_id),
    borrow_date DATE
);

INSERT INTO books (title, author) VALUES
    ('Clean Code', 'Robert Martin'),
    ('Design Patterns', 'GoF');

INSERT INTO members (member_name) VALUES
    ('John Doe'),
    ('Sarah Brown');

INSERT INTO borrow_records (book_id, member_id, borrow_date) VALUES
    (1, 1, '2025-02-01'),
    (2, 2, '2025-02-05');

SELECT
    m.member_name,
    b.title,
    br.borrow_date
FROM borrow_records br
JOIN books b
    ON br.book_id = b.book_id
JOIN members m
    ON br.member_id = m.member_id;

Tracks books, members, and borrowing records.

Let’s Try →

SQL Hotel Reservation System

CREATE TABLE rooms (
    room_id SERIAL PRIMARY KEY,
    room_number VARCHAR(10),
    room_type VARCHAR(50),
    price_per_night DECIMAL(10,2)
);

CREATE TABLE guests (
    guest_id SERIAL PRIMARY KEY,
    guest_name VARCHAR(100)
);

CREATE TABLE reservations (
    reservation_id SERIAL PRIMARY KEY,
    guest_id INTEGER REFERENCES guests(guest_id),
    room_id INTEGER REFERENCES rooms(room_id),
    check_in DATE,
    check_out DATE
);

INSERT INTO rooms (room_number, room_type, price_per_night) VALUES
    ('101', 'Single', 120),
    ('201', 'Suite', 300);

INSERT INTO guests (guest_name) VALUES
    ('Alice Johnson'),
    ('Mark Wilson');

INSERT INTO reservations (guest_id, room_id, check_in, check_out) VALUES
    (1, 1, '2025-04-01', '2025-04-05'),
    (2, 2, '2025-04-10', '2025-04-15');

SELECT
    g.guest_name,
    r.room_number,
    res.check_in,
    res.check_out
FROM reservations res
JOIN guests g
    ON g.guest_id = res.guest_id
JOIN rooms r
    ON r.room_id = res.room_id;

Handles guests, rooms, reservations, and occupancy reports.

Let’s Try →

SQL Data Warehouse Operations

CREATE TABLE dim_product (
    product_key SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50)
);

CREATE TABLE dim_date (
    date_key DATE PRIMARY KEY,
    year INTEGER,
    month INTEGER
);

CREATE TABLE fact_sales (
    sales_key SERIAL PRIMARY KEY,
    product_key INTEGER REFERENCES dim_product(product_key),
    date_key DATE REFERENCES dim_date(date_key),
    amount DECIMAL(12,2)
);

INSERT INTO dim_product (product_name, category) VALUES
    ('Laptop', 'Electronics'),
    ('Desk', 'Furniture');

INSERT INTO dim_date VALUES
    ('2025-01-01', 2025, 1),
    ('2025-02-01', 2025, 2);

INSERT INTO fact_sales (product_key, date_key, amount) VALUES
    (1, '2025-01-01', 1500),
    (1, '2025-02-01', 1800),
    (2, '2025-02-01', 700);

SELECT
    dp.category,
    SUM(fs.amount) AS total_sales
FROM fact_sales fs
JOIN dim_product dp
    ON fs.product_key = dp.product_key
GROUP BY dp.category;

Performs analytical queries and dimensional aggregations.

Let’s Try →

SQL Audit Trail Reporting

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE audit_logs (
    log_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    action VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username) VALUES
    ('admin'),
    ('manager');

INSERT INTO audit_logs (user_id, action) VALUES
    (1, 'Created employee record'),
    (1, 'Updated salary information'),
    (2, 'Generated monthly report');

SELECT
    u.username,
    a.action,
    a.created_at
FROM audit_logs a
JOIN users u
    ON a.user_id = u.user_id
ORDER BY a.created_at DESC;

SELECT
    u.username,
    COUNT(*) AS total_actions
FROM audit_logs a
JOIN users u
    ON a.user_id = u.user_id
GROUP BY u.username;

Stores user activity logs and generates audit reports.

Let’s Try →

SQL Recursive CTE Examples

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INTEGER REFERENCES employees(employee_id)
);

INSERT INTO employees (employee_name, manager_id) VALUES
    ('CEO', NULL),
    ('Engineering Manager', 1),
    ('Sales Manager', 1),
    ('Developer A', 2),
    ('Developer B', 2),
    ('Sales Executive', 3);

WITH RECURSIVE organization_chart AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        oc.level + 1
    FROM employees e
    JOIN organization_chart oc
        ON e.manager_id = oc.employee_id
)
SELECT *
FROM organization_chart
ORDER BY level, employee_name;

Demonstrates hierarchical queries using recursive common table expressions.

Let’s Try →

SQL Window Functions Analytics

CREATE TABLE monthly_sales (
    sale_id SERIAL PRIMARY KEY,
    salesperson VARCHAR(100),
    sale_month DATE,
    revenue DECIMAL(12,2)
);

INSERT INTO monthly_sales (salesperson, sale_month, revenue) VALUES
    ('John', '2025-01-01', 12000),
    ('John', '2025-02-01', 15000),
    ('Sarah', '2025-01-01', 18000),
    ('Sarah', '2025-02-01', 22000);

SELECT
    salesperson,
    sale_month,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY salesperson
        ORDER BY sale_month
    ) AS running_total,
    LAG(revenue) OVER (
        PARTITION BY salesperson
        ORDER BY sale_month
    ) AS previous_month_revenue,
    RANK() OVER (
        ORDER BY revenue DESC
    ) AS revenue_rank
FROM monthly_sales
ORDER BY salesperson, sale_month;

Uses ranking, running totals, and lag functions for analytical reporting.

Let’s Try →

SQL Performance Optimization

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE,
    total_amount DECIMAL(12,2)
);

CREATE INDEX idx_order_date
ON orders(order_date);

CREATE INDEX idx_customer_name
ON orders(customer_name);

INSERT INTO orders (customer_name, order_date, total_amount) VALUES
    ('Alice', '2025-01-01', 1200),
    ('Bob', '2025-01-05', 450),
    ('Alice', '2025-02-01', 850);

CREATE VIEW customer_totals AS
SELECT
    customer_name,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY customer_name;

SELECT *
FROM customer_totals
ORDER BY revenue DESC;

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_name = 'Alice';

Illustrates indexes, views, and query optimization techniques.

Let’s Try →

SQL Advanced Data Migration

CREATE TABLE legacy_customers (
    customer_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO legacy_customers (full_name, email) VALUES
    ('Alice Johnson', 'alice@example.com'),
    ('Bob Smith', 'bob@example.com');

BEGIN;

INSERT INTO customers (
    first_name,
    last_name,
    email
)
SELECT
    SPLIT_PART(full_name, ' ', 1),
    SPLIT_PART(full_name, ' ', 2),
    email
FROM legacy_customers;

COMMIT;

SELECT *
FROM customers;

Shows data migration between legacy and new tables using transactions.

Let’s Try →

SQL Database Normalization

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL
);

CREATE TABLE publishers (
    publisher_id SERIAL PRIMARY KEY,
    publisher_name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER REFERENCES authors(author_id),
    publisher_id INTEGER REFERENCES publishers(publisher_id),
    publication_year INTEGER
);

INSERT INTO authors (author_name) VALUES
    ('Robert Martin'),
    ('Martin Fowler');

INSERT INTO publishers (publisher_name) VALUES
    ('Prentice Hall'),
    ('Addison-Wesley');

INSERT INTO books (
    title,
    author_id,
    publisher_id,
    publication_year
) VALUES
    ('Clean Code', 1, 1, 2008),
    ('Refactoring', 2, 2, 1999);

SELECT
    b.title,
    a.author_name,
    p.publisher_name,
    b.publication_year
FROM books b
JOIN authors a
    ON b.author_id = a.author_id
JOIN publishers p
    ON b.publisher_id = p.publisher_id
ORDER BY b.publication_year DESC;

Demonstrates normalized database design with relationships and constraints.

Let’s Try →

Frequently Asked Questions about SQL

What is SQL?

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases, enabling querying, insertion, updating, and deletion of data efficiently.

What are the primary use cases for SQL?

Querying relational data for applications. Data aggregation and reporting. Transaction management in business systems. Analytics and business intelligence. Database schema definition and data integrity enforcement

What are the strengths of SQL?

Standardized and widely supported across RDBMS. Powerful for structured data manipulation. Enables complex queries and analytics. ACID-compliant transactions ensure data reliability. Strong community and documentation support

What are the limitations of SQL?

Less flexible for unstructured or hierarchical data. Complex queries can be hard to optimize. Performance depends on indexing and schema design. Portability issues with vendor-specific SQL extensions. Limited in handling very large-scale distributed data compared to NoSQL

How can I practice SQL typing speed?

CodeSpeedTest offers 22+ real SQL code examples for typing practice. You can measure your WPM, track accuracy, and improve your coding speed with guided exercises.

Learn Other Programming Languages

C React Python C++Rust TypeScript Kotlin PHP Java C#Ruby Mql Cql N1ql Cypher Gremlin Partiql Haskell Elixir Fsharp View all languages →

Learn SQL - 22 Code Examples & CST Typing Practice Test

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases, enabling querying, insertion, updating, and deletion of data efficiently.

View all 22 SQL code examples →

Learn SQL with Real Code Examples

Updated Nov 27, 2025

Introduction & Fundamentals Setup & Configuration Architecture & Deep Internals Performance & Security Development Workflow Learning & Career Growth Business & Strategy Examples

Code Sample Descriptions

PostgreSQL Advanced Queries

-- Create tables with relationships
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    status VARCHAR(20) DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO users (username, email) VALUES 
    ('alice', 'alice@example.com'),
    ('bob', 'bob@example.com'),
    ('charlie', 'charlie@example.com');

INSERT INTO posts (user_id, title, content, status) VALUES 
    (1, 'First Post', 'This is my first blog post.', 'published'),
    (1, 'Second Post', 'Another interesting post.', 'published'),
    (2, 'Draft Post', 'This is still a draft.', 'draft');

INSERT INTO comments (post_id, user_id, content) VALUES 
    (1, 2, 'Great first post!'),
    (1, 3, 'Thanks for sharing.'),
    (2, 3, 'Very informative.');

-- Complex queries with JOINs, aggregations, and CTEs
WITH user_stats AS (
    SELECT 
        u.id,
        u.username,
        COUNT(DISTINCT p.id) as post_count,
        COUNT(DISTINCT c.id) as comment_count,
        MAX(p.created_at) as last_post_date
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id AND p.status = 'published'
    LEFT JOIN comments c ON u.id = c.user_id
    GROUP BY u.id, u.username
),
popular_posts AS (
    SELECT 
        p.id,
        p.title,
        u.username as author,
        COUNT(c.id) as comment_count,
        RANK() OVER (ORDER BY COUNT(c.id) DESC) as popularity_rank
    FROM posts p
    JOIN users u ON p.user_id = u.id
    LEFT JOIN comments c ON p.id = c.post_id
    WHERE p.status = 'published'
    GROUP BY p.id, p.title, u.username
)
SELECT 
    us.username,
    us.post_count,
    us.comment_count,
    us.last_post_date,
    COALESCE(pp.title, 'No posts') as most_popular_post,
    COALESCE(pp.comment_count, 0) as most_popular_post_comments
FROM user_stats us
LEFT JOIN popular_posts pp ON pp.popularity_rank = 1
ORDER BY us.post_count DESC, us.comment_count DESC;

-- Window functions example
SELECT 
    u.username,
    p.title,
    p.created_at,
    LAG(p.created_at) OVER (
        PARTITION BY u.id 
        ORDER BY p.created_at
    ) as previous_post_date,
    COUNT(*) OVER (
        PARTITION BY u.id
    ) as total_user_posts,
    ROW_NUMBER() OVER (
        PARTITION BY u.id 
        ORDER BY p.created_at
    ) as post_sequence
FROM users u
JOIN posts p ON u.id = p.user_id
WHERE p.status = 'published'
ORDER BY u.username, p.created_at;

Demonstrates advanced SQL features including JOINs, CTEs, window functions, and complex aggregations.

Let’s Try →

SQL Employee Management System

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10,2),
    department_id INTEGER REFERENCES departments(department_id)
);

INSERT INTO departments (department_name, location) VALUES
    ('Engineering', 'New York'),
    ('Marketing', 'Chicago'),
    ('Finance', 'Boston');

INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES
    ('John', 'Smith', 'john@example.com', '2022-01-15', 85000, 1),
    ('Sarah', 'Johnson', 'sarah@example.com', '2021-03-10', 92000, 1),
    ('Mike', 'Brown', 'mike@example.com', '2023-05-11', 70000, 2),
    ('Emma', 'Davis', 'emma@example.com', '2020-07-21', 88000, 3);

SELECT
    d.department_name,
    COUNT(e.employee_id) AS total_employees,
    AVG(e.salary) AS avg_salary,
    MAX(e.salary) AS highest_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

SELECT
    first_name,
    last_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

CREATE VIEW employee_summary AS
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM employee_summary;

Demonstrates employee, department, payroll, and reporting operations using joins, aggregations, views, and window functions.

Let’s Try →

SQL Employee Management System

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    location VARCHAR(100)
);

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE,
    hire_date DATE,
    salary DECIMAL(10,2),
    department_id INTEGER REFERENCES departments(department_id)
);

INSERT INTO departments (department_name, location) VALUES
    ('Engineering', 'New York'),
    ('Marketing', 'Chicago'),
    ('Finance', 'Boston');

INSERT INTO employees (first_name, last_name, email, hire_date, salary, department_id) VALUES
    ('John', 'Smith', 'john@example.com', '2022-01-15', 85000, 1),
    ('Sarah', 'Johnson', 'sarah@example.com', '2021-03-10', 92000, 1),
    ('Mike', 'Brown', 'mike@example.com', '2023-05-11', 70000, 2),
    ('Emma', 'Davis', 'emma@example.com', '2020-07-21', 88000, 3);

SELECT
    d.department_name,
    COUNT(e.employee_id) AS total_employees,
    AVG(e.salary) AS avg_salary,
    MAX(e.salary) AS highest_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

SELECT
    first_name,
    last_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

CREATE VIEW employee_summary AS
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name,
    e.salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM employee_summary;

Demonstrates employee, department, payroll, and reporting operations using joins, aggregations, views, and window functions.

Let’s Try →

SQL Inventory Management

CREATE TABLE suppliers (
    supplier_id SERIAL PRIMARY KEY,
    supplier_name VARCHAR(100)
);

CREATE TABLE inventory (
    item_id SERIAL PRIMARY KEY,
    item_name VARCHAR(100),
    stock_quantity INTEGER,
    reorder_level INTEGER,
    supplier_id INTEGER REFERENCES suppliers(supplier_id)
);

INSERT INTO suppliers (supplier_name) VALUES
    ('Tech Supply Co'),
    ('Global Parts Ltd');

INSERT INTO inventory (
    item_name,
    stock_quantity,
    reorder_level,
    supplier_id
) VALUES
    ('SSD Drive', 50, 20, 1),
    ('RAM Module', 15, 25, 1),
    ('Power Supply', 10, 15, 2);

SELECT
    item_name,
    stock_quantity,
    reorder_level,
    CASE
        WHEN stock_quantity < reorder_level THEN 'REORDER'
        ELSE 'OK'
    END AS inventory_status
FROM inventory;

SELECT
    s.supplier_name,
    COUNT(i.item_id) AS supplied_items
FROM suppliers s
JOIN inventory i
    ON s.supplier_id = i.supplier_id
GROUP BY s.supplier_name;

CREATE INDEX idx_inventory_stock
ON inventory(stock_quantity);

SELECT *
FROM inventory
ORDER BY stock_quantity ASC;

Tracks stock levels, suppliers, inventory movement, and reorder alerts.

Let’s Try →

SQL Customer Segmentation

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE purchases (
    purchase_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    purchase_amount DECIMAL(10,2),
    purchase_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob'),
    ('Charlie');

INSERT INTO purchases (
    customer_id,
    purchase_amount,
    purchase_date
) VALUES
    (1, 500, '2025-01-01'),
    (1, 700, '2025-02-01'),
    (2, 100, '2025-02-10'),
    (3, 2000, '2025-01-15');

WITH customer_value AS (
    SELECT
        customer_id,
        COUNT(*) AS purchase_count,
        SUM(purchase_amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
)
SELECT
    customer_id,
    purchase_count,
    total_spent,
    CASE
        WHEN total_spent > 1000 THEN 'VIP'
        WHEN total_spent > 300 THEN 'REGULAR'
        ELSE 'NEW'
    END AS segment
FROM customer_value;

SELECT
    customer_id,
    total_spent,
    NTILE(4) OVER (
        ORDER BY total_spent DESC
    ) AS spending_quartile
FROM (
    SELECT
        customer_id,
        SUM(purchase_amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
) stats;

Segments customers by purchase behavior, frequency, and lifetime value.

Let’s Try →

SQL Customer Segmentation

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE purchases (
    purchase_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    purchase_amount DECIMAL(10,2),
    purchase_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob'),
    ('Charlie');

INSERT INTO purchases (
    customer_id,
    purchase_amount,
    purchase_date
) VALUES
    (1, 500, '2025-01-01'),
    (1, 700, '2025-02-01'),
    (2, 100, '2025-02-10'),
    (3, 2000, '2025-01-15');

WITH customer_value AS (
    SELECT
        customer_id,
        COUNT(*) AS purchase_count,
        SUM(purchase_amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
)
SELECT
    customer_id,
    purchase_count,
    total_spent,
    CASE
        WHEN total_spent > 1000 THEN 'VIP'
        WHEN total_spent > 300 THEN 'REGULAR'
        ELSE 'NEW'
    END AS segment
FROM customer_value;

SELECT
    customer_id,
    total_spent,
    NTILE(4) OVER (
        ORDER BY total_spent DESC
    ) AS spending_quartile
FROM (
    SELECT
        customer_id,
        SUM(purchase_amount) AS total_spent
    FROM purchases
    GROUP BY customer_id
) stats;

Segments customers by purchase behavior, frequency, and lifetime value.

Let’s Try →

SQL Financial Transactions Analysis

CREATE TABLE accounts (
    account_id SERIAL PRIMARY KEY,
    account_holder VARCHAR(100),
    account_type VARCHAR(50)
);

CREATE TABLE transactions (
    transaction_id SERIAL PRIMARY KEY,
    account_id INTEGER REFERENCES accounts(account_id),
    amount DECIMAL(12,2),
    transaction_type VARCHAR(20),
    transaction_date DATE
);

INSERT INTO accounts (account_holder, account_type) VALUES
    ('Alice Johnson', 'Savings'),
    ('Bob Smith', 'Checking');

INSERT INTO transactions (
    account_id,
    amount,
    transaction_type,
    transaction_date
) VALUES
    (1, 1500, 'deposit', '2025-01-01'),
    (1, 300, 'withdrawal', '2025-01-10'),
    (2, 2200, 'deposit', '2025-01-03'),
    (2, 500, 'withdrawal', '2025-01-15');

SELECT
    account_id,
    SUM(
        CASE
            WHEN transaction_type = 'deposit' THEN amount
            ELSE -amount
        END
    ) AS current_balance
FROM transactions
GROUP BY account_id;

SELECT
    transaction_date,
    amount,
    SUM(amount) OVER (
        ORDER BY transaction_date
    ) AS running_total
FROM transactions;

Analyzes financial transactions, balances, and monthly spending trends using aggregations and window functions.

Let’s Try →

SQL Banking System Queries

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE bank_accounts (
    account_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    balance DECIMAL(12,2)
);

INSERT INTO customers (customer_name, email) VALUES
    ('John Doe', 'john@example.com'),
    ('Emma Wilson', 'emma@example.com');

INSERT INTO bank_accounts (
    customer_id,
    balance
) VALUES
    (1, 10000),
    (2, 7500);

SELECT
    c.customer_name,
    b.account_id,
    b.balance
FROM customers c
JOIN bank_accounts b
    ON c.customer_id = b.customer_id;

SELECT
    AVG(balance) AS average_balance,
    MAX(balance) AS highest_balance,
    MIN(balance) AS lowest_balance
FROM bank_accounts;

Demonstrates customer accounts, transactions, and account summaries.

Let’s Try →

SQL Order Processing Workflow

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_status VARCHAR(30),
    order_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob');

INSERT INTO orders (
    customer_id,
    order_status,
    order_date
) VALUES
    (1, 'Pending', '2025-02-01'),
    (1, 'Shipped', '2025-02-02'),
    (2, 'Delivered', '2025-02-03');

SELECT
    order_status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;

UPDATE orders
SET order_status = 'Completed'
WHERE order_status = 'Delivered';

SELECT *
FROM orders;

Handles order lifecycle, status updates, and customer purchases.

Let’s Try →

SQL Order Processing Workflow

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_status VARCHAR(30),
    order_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob');

INSERT INTO orders (
    customer_id,
    order_status,
    order_date
) VALUES
    (1, 'Pending', '2025-02-01'),
    (1, 'Shipped', '2025-02-02'),
    (2, 'Delivered', '2025-02-03');

SELECT
    order_status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;

UPDATE orders
SET order_status = 'Completed'
WHERE order_status = 'Delivered';

SELECT *
FROM orders;

Handles order lifecycle, status updates, and customer purchases.

Let’s Try →

SQL Order Processing Workflow

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100)
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(customer_id),
    order_status VARCHAR(30),
    order_date DATE
);

INSERT INTO customers (customer_name) VALUES
    ('Alice'),
    ('Bob');

INSERT INTO orders (
    customer_id,
    order_status,
    order_date
) VALUES
    (1, 'Pending', '2025-02-01'),
    (1, 'Shipped', '2025-02-02'),
    (2, 'Delivered', '2025-02-03');

SELECT
    order_status,
    COUNT(*) AS total_orders
FROM orders
GROUP BY order_status;

UPDATE orders
SET order_status = 'Completed'
WHERE order_status = 'Delivered';

SELECT *
FROM orders;

Handles order lifecycle, status updates, and customer purchases.

Let’s Try →

SQL Healthcare Records Management

CREATE TABLE patients (
    patient_id SERIAL PRIMARY KEY,
    patient_name VARCHAR(100),
    date_of_birth DATE
);

CREATE TABLE appointments (
    appointment_id SERIAL PRIMARY KEY,
    patient_id INTEGER REFERENCES patients(patient_id),
    appointment_date DATE,
    doctor_name VARCHAR(100)
);

INSERT INTO patients (
    patient_name,
    date_of_birth
) VALUES
    ('Alice Brown', '1990-05-20'),
    ('David Green', '1985-09-12');

INSERT INTO appointments (
    patient_id,
    appointment_date,
    doctor_name
) VALUES
    (1, '2025-03-01', 'Dr. Smith'),
    (2, '2025-03-02', 'Dr. Johnson');

SELECT
    p.patient_name,
    a.appointment_date,
    a.doctor_name
FROM patients p
JOIN appointments a
    ON p.patient_id = a.patient_id;

SELECT
    doctor_name,
    COUNT(*) AS total_appointments
FROM appointments
GROUP BY doctor_name;

Stores patients, appointments, and medical records with reporting queries.

Let’s Try →

SQL University Management System

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR(100),
    credits INTEGER
);

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_id INTEGER REFERENCES students(student_id),
    course_id INTEGER REFERENCES courses(course_id),
    enrollment_date DATE
);

INSERT INTO students (first_name, last_name, email) VALUES
    ('Alice', 'Johnson', 'alice@example.com'),
    ('Bob', 'Smith', 'bob@example.com');

INSERT INTO courses (course_name, credits) VALUES
    ('Database Systems', 3),
    ('Algorithms', 4);

INSERT INTO enrollments (student_id, course_id, enrollment_date) VALUES
    (1, 1, '2025-01-10'),
    (1, 2, '2025-01-10'),
    (2, 1, '2025-01-15');

SELECT
    s.first_name,
    s.last_name,
    c.course_name
FROM students s
JOIN enrollments e
    ON s.student_id = e.student_id
JOIN courses c
    ON c.course_id = e.course_id
ORDER BY s.first_name;

Manages students, courses, enrollments, and academic reporting.

Let’s Try →

SQL Library Management Database

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(150),
    author VARCHAR(100),
    available BOOLEAN DEFAULT TRUE
);

CREATE TABLE members (
    member_id SERIAL PRIMARY KEY,
    member_name VARCHAR(100)
);

CREATE TABLE borrow_records (
    record_id SERIAL PRIMARY KEY,
    book_id INTEGER REFERENCES books(book_id),
    member_id INTEGER REFERENCES members(member_id),
    borrow_date DATE
);

INSERT INTO books (title, author) VALUES
    ('Clean Code', 'Robert Martin'),
    ('Design Patterns', 'GoF');

INSERT INTO members (member_name) VALUES
    ('John Doe'),
    ('Sarah Brown');

INSERT INTO borrow_records (book_id, member_id, borrow_date) VALUES
    (1, 1, '2025-02-01'),
    (2, 2, '2025-02-05');

SELECT
    m.member_name,
    b.title,
    br.borrow_date
FROM borrow_records br
JOIN books b
    ON br.book_id = b.book_id
JOIN members m
    ON br.member_id = m.member_id;

Tracks books, members, and borrowing records.

Let’s Try →

SQL Hotel Reservation System

CREATE TABLE rooms (
    room_id SERIAL PRIMARY KEY,
    room_number VARCHAR(10),
    room_type VARCHAR(50),
    price_per_night DECIMAL(10,2)
);

CREATE TABLE guests (
    guest_id SERIAL PRIMARY KEY,
    guest_name VARCHAR(100)
);

CREATE TABLE reservations (
    reservation_id SERIAL PRIMARY KEY,
    guest_id INTEGER REFERENCES guests(guest_id),
    room_id INTEGER REFERENCES rooms(room_id),
    check_in DATE,
    check_out DATE
);

INSERT INTO rooms (room_number, room_type, price_per_night) VALUES
    ('101', 'Single', 120),
    ('201', 'Suite', 300);

INSERT INTO guests (guest_name) VALUES
    ('Alice Johnson'),
    ('Mark Wilson');

INSERT INTO reservations (guest_id, room_id, check_in, check_out) VALUES
    (1, 1, '2025-04-01', '2025-04-05'),
    (2, 2, '2025-04-10', '2025-04-15');

SELECT
    g.guest_name,
    r.room_number,
    res.check_in,
    res.check_out
FROM reservations res
JOIN guests g
    ON g.guest_id = res.guest_id
JOIN rooms r
    ON r.room_id = res.room_id;

Handles guests, rooms, reservations, and occupancy reports.

Let’s Try →

SQL Data Warehouse Operations

CREATE TABLE dim_product (
    product_key SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50)
);

CREATE TABLE dim_date (
    date_key DATE PRIMARY KEY,
    year INTEGER,
    month INTEGER
);

CREATE TABLE fact_sales (
    sales_key SERIAL PRIMARY KEY,
    product_key INTEGER REFERENCES dim_product(product_key),
    date_key DATE REFERENCES dim_date(date_key),
    amount DECIMAL(12,2)
);

INSERT INTO dim_product (product_name, category) VALUES
    ('Laptop', 'Electronics'),
    ('Desk', 'Furniture');

INSERT INTO dim_date VALUES
    ('2025-01-01', 2025, 1),
    ('2025-02-01', 2025, 2);

INSERT INTO fact_sales (product_key, date_key, amount) VALUES
    (1, '2025-01-01', 1500),
    (1, '2025-02-01', 1800),
    (2, '2025-02-01', 700);

SELECT
    dp.category,
    SUM(fs.amount) AS total_sales
FROM fact_sales fs
JOIN dim_product dp
    ON fs.product_key = dp.product_key
GROUP BY dp.category;

Performs analytical queries and dimensional aggregations.

Let’s Try →

SQL Audit Trail Reporting

CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);

CREATE TABLE audit_logs (
    log_id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(user_id),
    action VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username) VALUES
    ('admin'),
    ('manager');

INSERT INTO audit_logs (user_id, action) VALUES
    (1, 'Created employee record'),
    (1, 'Updated salary information'),
    (2, 'Generated monthly report');

SELECT
    u.username,
    a.action,
    a.created_at
FROM audit_logs a
JOIN users u
    ON a.user_id = u.user_id
ORDER BY a.created_at DESC;

SELECT
    u.username,
    COUNT(*) AS total_actions
FROM audit_logs a
JOIN users u
    ON a.user_id = u.user_id
GROUP BY u.username;

Stores user activity logs and generates audit reports.

Let’s Try →

SQL Recursive CTE Examples

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INTEGER REFERENCES employees(employee_id)
);

INSERT INTO employees (employee_name, manager_id) VALUES
    ('CEO', NULL),
    ('Engineering Manager', 1),
    ('Sales Manager', 1),
    ('Developer A', 2),
    ('Developer B', 2),
    ('Sales Executive', 3);

WITH RECURSIVE organization_chart AS (
    SELECT
        employee_id,
        employee_name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        e.employee_id,
        e.employee_name,
        e.manager_id,
        oc.level + 1
    FROM employees e
    JOIN organization_chart oc
        ON e.manager_id = oc.employee_id
)
SELECT *
FROM organization_chart
ORDER BY level, employee_name;

Demonstrates hierarchical queries using recursive common table expressions.

Let’s Try →

SQL Window Functions Analytics

CREATE TABLE monthly_sales (
    sale_id SERIAL PRIMARY KEY,
    salesperson VARCHAR(100),
    sale_month DATE,
    revenue DECIMAL(12,2)
);

INSERT INTO monthly_sales (salesperson, sale_month, revenue) VALUES
    ('John', '2025-01-01', 12000),
    ('John', '2025-02-01', 15000),
    ('Sarah', '2025-01-01', 18000),
    ('Sarah', '2025-02-01', 22000);

SELECT
    salesperson,
    sale_month,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY salesperson
        ORDER BY sale_month
    ) AS running_total,
    LAG(revenue) OVER (
        PARTITION BY salesperson
        ORDER BY sale_month
    ) AS previous_month_revenue,
    RANK() OVER (
        ORDER BY revenue DESC
    ) AS revenue_rank
FROM monthly_sales
ORDER BY salesperson, sale_month;

Uses ranking, running totals, and lag functions for analytical reporting.

Let’s Try →

SQL Performance Optimization

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    order_date DATE,
    total_amount DECIMAL(12,2)
);

CREATE INDEX idx_order_date
ON orders(order_date);

CREATE INDEX idx_customer_name
ON orders(customer_name);

INSERT INTO orders (customer_name, order_date, total_amount) VALUES
    ('Alice', '2025-01-01', 1200),
    ('Bob', '2025-01-05', 450),
    ('Alice', '2025-02-01', 850);

CREATE VIEW customer_totals AS
SELECT
    customer_name,
    COUNT(*) AS total_orders,
    SUM(total_amount) AS revenue
FROM orders
GROUP BY customer_name;

SELECT *
FROM customer_totals
ORDER BY revenue DESC;

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_name = 'Alice';

Illustrates indexes, views, and query optimization techniques.

Let’s Try →

SQL Advanced Data Migration

CREATE TABLE legacy_customers (
    customer_id SERIAL PRIMARY KEY,
    full_name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO legacy_customers (full_name, email) VALUES
    ('Alice Johnson', 'alice@example.com'),
    ('Bob Smith', 'bob@example.com');

BEGIN;

INSERT INTO customers (
    first_name,
    last_name,
    email
)
SELECT
    SPLIT_PART(full_name, ' ', 1),
    SPLIT_PART(full_name, ' ', 2),
    email
FROM legacy_customers;

COMMIT;

SELECT *
FROM customers;

Shows data migration between legacy and new tables using transactions.

Let’s Try →

SQL Database Normalization

CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL
);

CREATE TABLE publishers (
    publisher_id SERIAL PRIMARY KEY,
    publisher_name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INTEGER REFERENCES authors(author_id),
    publisher_id INTEGER REFERENCES publishers(publisher_id),
    publication_year INTEGER
);

INSERT INTO authors (author_name) VALUES
    ('Robert Martin'),
    ('Martin Fowler');

INSERT INTO publishers (publisher_name) VALUES
    ('Prentice Hall'),
    ('Addison-Wesley');

INSERT INTO books (
    title,
    author_id,
    publisher_id,
    publication_year
) VALUES
    ('Clean Code', 1, 1, 2008),
    ('Refactoring', 2, 2, 1999);

SELECT
    b.title,
    a.author_name,
    p.publisher_name,
    b.publication_year
FROM books b
JOIN authors a
    ON b.author_id = a.author_id
JOIN publishers p
    ON b.publisher_id = p.publisher_id
ORDER BY b.publication_year DESC;

Demonstrates normalized database design with relationships and constraints.

Let’s Try →

Frequently Asked Questions about SQL

What is SQL?

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases, enabling querying, insertion, updating, and deletion of data efficiently.

What are the primary use cases for SQL?

What are the strengths of SQL?

What are the limitations of SQL?

How can I practice SQL typing speed?

CodeSpeedTest offers 22+ real SQL code examples for typing practice. You can measure your WPM, track accuracy, and improve your coding speed with guided exercises.

Learn Other Programming Languages

C React Python C++Rust TypeScript Kotlin PHP Java C#Ruby Mql Cql N1ql Cypher Gremlin Partiql Haskell Elixir Fsharp View all languages →