Complete collection of practical SQL interview, exam, and lab questions organized by topic with toggleable solutions

Comprehensive SQL Practice Bank by Topic

This question bank aggregates the most essential queries, edge cases, and exam problems for every topic in DBMS and SQL. Try writing the SQL query yourself before expanding the solution!

1. DDL & Integrity Constraints

Problem:
Create a table AccountTransactions with:

trans_id (Integer)
account_no (Integer)
amount (Numeric(10, 2), non-zero)
trans_type (Text: must be either 'DEBIT' or 'CREDIT')
trans_time (Text, default current timestamp)
Composite primary key on (trans_id, account_no)

Solution:

CREATE TABLE AccountTransactions (
    trans_id    INTEGER,
    account_no  INTEGER,
    amount      NUMERIC(10, 2) NOT NULL CHECK (amount <> 0),
    trans_type  TEXT NOT NULL CHECK (trans_type IN ('DEBIT', 'CREDIT')),
    trans_time  TEXT DEFAULT (DATETIME('now')),
    PRIMARY KEY (trans_id, account_no)
);

Problem:
Write the CREATE TABLE statement for OrderItems referencing Orders(order_id) and Products(product_id). If an order is deleted, its line items should be removed automatically. If a product is deleted, prevent deletion if it exists in an order.

Solution:

CREATE TABLE OrderItems (
    order_id    INTEGER,
    product_id  INTEGER,
    quantity    INTEGER NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(8, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES Orders(order_id) 
        ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES Products(product_id) 
        ON DELETE RESTRICT
);

2. Single-Table Filtering & Expressions

Problem:
Find all courses whose titles contain the word 'Data' or 'Database', but do NOT contain 'Advanced'.

Solution:

SELECT course_id, title 
FROM course 
WHERE (LOWER(title) LIKE '%data%' OR LOWER(title) LIKE '%database%')
  AND LOWER(title) NOT LIKE '%advanced%';

Problem:
Display student name, tot_cred, and a column standing:

'Senior' if tot_cred >= 90
'Junior' if tot_cred >= 60
'Sophomore' if tot_cred >= 30
Otherwise 'Freshman'.

Solution:

SELECT 
    name,
    tot_cred,
    CASE 
        WHEN tot_cred >= 90 THEN 'Senior'
        WHEN tot_cred >= 60 THEN 'Junior'
        WHEN tot_cred >= 30 THEN 'Sophomore'
        ELSE 'Freshman'
    END AS standing
FROM student
ORDER BY tot_cred DESC;

3. Multi-Table Joins & Self-Joins

Problem:
List the student names, course titles, and semester/year of every course taught by instructor 'Einstein'.

Solution:

SELECT s.name AS student_name, c.title AS course_title, sec.semester, sec.year
FROM instructor i
JOIN teaches t ON i.id = t.id
JOIN section sec ON t.course_id = sec.course_id 
                AND t.sec_id = sec.sec_id 
                AND t.semester = sec.semester 
                AND t.year = sec.year
JOIN takes tk ON sec.course_id = tk.course_id 
             AND sec.sec_id = tk.sec_id 
             AND sec.semester = tk.semester 
             AND sec.year = tk.year
JOIN student s ON tk.id = s.id
JOIN course c ON sec.course_id = c.course_id
WHERE i.name = 'Einstein';

Problem:
Find all pairs of students in the same department who have identical tot_cred. Do not pair a student with themselves, and do not return mirror pairs ((A, B) and (B, A)).

Solution:

SELECT 
    s1.name AS student1, 
    s2.name AS student2, 
    s1.dept_name, 
    s1.tot_cred
FROM student s1
JOIN student s2 ON s1.dept_name = s2.dept_name 
               AND s1.tot_cred = s2.tot_cred 
               AND s1.id < s2.id;

4. Outer Joins & Anti-Joins

Problem:
Find all departments that currently have zero instructors and zero students enrolled.

Solution:

SELECT d.dept_name
FROM department d
LEFT JOIN instructor i ON d.dept_name = i.dept_name
LEFT JOIN student s ON d.dept_name = s.dept_name
WHERE i.id IS NULL AND s.id IS NULL;

Problem:
Find all courses that do not require any prerequisite course, using a LEFT JOIN.

Solution:

SELECT c.course_id, c.title
FROM course c
LEFT JOIN prereq p ON c.course_id = p.course_id
WHERE p.prereq_id IS NULL;

5. Aggregations, `GROUP BY` & `HAVING`

Problem:
For each department, display the department name, total number of instructors, and the number of instructors earning greater than $80,000.

Solution:

SELECT 
    dept_name,
    COUNT(*) AS total_faculty,
    COUNT(CASE WHEN salary > 80000 THEN 1 END) AS high_earners
FROM instructor
GROUP BY dept_name;

Problem:
Find all instructors who have taught at least 3 distinct course sections across all years.

Solution:

SELECT i.id, i.name, COUNT(*) AS sections_taught
FROM instructor i
JOIN teaches t ON i.id = t.id
GROUP BY i.id, i.name
HAVING COUNT(*) >= 3;

6. Subqueries & `EXISTS`

Problem:
Find all instructors whose salary is higher than the average salary of their own department.

Solution:

SELECT i1.id, i1.name, i1.dept_name, i1.salary
FROM instructor i1
WHERE i1.salary > (
    SELECT AVG(i2.salary)
    FROM instructor i2
    WHERE i2.dept_name = i1.dept_name
);

Problem:
Find all students who have never received a grade of 'F', safely avoiding NULL pitfalls.

Solution:

SELECT id, name
FROM student
WHERE id NOT IN (
    SELECT id 
    FROM takes 
    WHERE grade = 'F' AND id IS NOT NULL
);

Problem:
Find all students who have taken EVERY course offered by the Biology department.

Solution:

SELECT s.id, s.name
FROM student s
WHERE NOT EXISTS (
    -- Courses offered by Biology
    SELECT c.course_id
    FROM course c
    WHERE c.dept_name = 'Biology'
    EXCEPT
    -- Courses taken by this specific student
    SELECT t.course_id
    FROM takes t
    WHERE t.id = s.id
);

7. Common Table Expressions (CTEs)

Problem:
Using CTEs, calculate the average department budget across all departments, and find departments whose budget exceeds this overall average.

Solution:

WITH overall_budget_avg AS (
    SELECT AVG(budget) AS avg_b FROM department
)
SELECT d.dept_name, d.budget, ROUND(o.avg_b, 2) AS benchmark
FROM department d, overall_budget_avg o
WHERE d.budget > o.avg_b;

8. Window Functions & Analytics

Problem:
Write a query to find the highest-paid instructor in each department. If there are ties for first place, include all tied instructors.

Solution:

WITH RankedInstructors AS (
    SELECT 
        name, 
        dept_name, 
        salary,
        DENSE_RANK() OVER (
            PARTITION BY dept_name 
            ORDER BY salary DESC
        ) AS rank_num
    FROM instructor
)
SELECT name, dept_name, salary
FROM RankedInstructors
WHERE rank_num = 1;

Problem:
Given an orders(order_id, customer_id, order_date, total_amount) table, compute a cumulative running total of spend for each customer ordered by order_date.

Solution:

SELECT 
    customer_id,
    order_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spent
FROM orders;

Problem:
Display each order along with the previous order's amount for the same customer, and calculate the difference.

Solution:

SELECT 
    customer_id,
    order_id,
    order_date,
    total_amount,
    LAG(total_amount, 1) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS previous_order_amount,
    total_amount - LAG(total_amount, 1) OVER (
        PARTITION BY customer_id 
        ORDER BY order_date
    ) AS delta
FROM orders;

SQL Practice Bank by Topic

1.1 Create an audit table with composite primary key and check constraints

1.2 Foreign Key Cascading Definition

2.1 Case-Insensitive Pattern Filtering with NOT

2.2 Dynamic Tiering with CASE WHEN

3.1 Multi-Table Traversal (4 Tables)

3.2 Self-Join Without Duplicates or Self-Pairs

4.1 Unmatched Rows Anti-Join Pattern

4.2 Courses with No Prerequisites

5.1 Aggregating with Conditional Counts

5.2 Filtering Aggregates with HAVING

6.1 Correlated Subquery in WHERE

6.2 Safe NOT IN Handling

6.3 Universal Quantification with NOT EXISTS

7.1 Multi-Stage CTE Analysis

8.1 Top-1 Earner Per Department Using DENSE_RANK

8.2 Moving Running Sum

8.3 Comparing with Prior Row using LAG()

On this page