Don’t just read SQL. Write it. Run it. Experiment with it.

Whether you’re preparing for a SQL interview questions for data engineers, learning data engineering, working with databases, or improving your analytics skills, practicing real SQL problems is one of the best ways to build confidence.

In this guide, we’ll work through 30 SQL queries, from basic SELECT statements to joins, subqueries, aggregations, and advanced window functions such as ROW_NUMBER(), RANK(), LAG(), and LEAD(). You can run every example hands-on using our interactive SQL plugin.

🎯 Try Every Query Yourself

For each problem:

1. Read the question
2. Try writing the query yourself
3. Click “Try Now”
4. Run the query
5. Compare your result with the expected result

🏃 🚀 Open the Srinimfs Interactive SQL Playground

💡 Challenge: Try solving the problem before looking at the solution.

-- ============================================================
-- SQLite Setup Script for All 30 SQL Examples
-- Compatible with: SQLite / SQLite3
--
-- Note:
-- All PRIMARY KEY and FOREIGN KEY constraints have been removed
-- so users can freely experiment with the data.
-- ============================================================
PRAGMA foreign_keys = ON;
-- ============================================================
-- DROP EXISTING TABLES
-- ============================================================
DROP TABLE IF EXISTS employee_salary_history;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS departments;
-- ============================================================
-- CREATE TABLES
-- No PRIMARY KEY / FOREIGN KEY constraints
-- ============================================================
-- ------------------------------------------------------------
-- 1. departments
--    Used by: examples 4, 15, 16
-- ------------------------------------------------------------
CREATE TABLE departments (
    department_id   INTEGER,
    department_name TEXT NOT NULL
);
-- ------------------------------------------------------------
-- 2. employees
--    Used by: examples 1-14, 17-21, 30
-- ------------------------------------------------------------
CREATE TABLE employees (
    employee_id     INTEGER,
    employee_name   TEXT NOT NULL,
    first_name      TEXT,
    last_name       TEXT,
    email           TEXT,
    department      TEXT,
    department_id   INTEGER,
    manager_id      INTEGER,
    hire_date       TEXT,
    salary          REAL NOT NULL DEFAULT 0
);
-- ------------------------------------------------------------
-- 3. customers
--    Used by: examples 23-29
-- ------------------------------------------------------------
CREATE TABLE customers (
    customer_id   INTEGER,
    customer_name TEXT NOT NULL
);
-- ------------------------------------------------------------
-- 4. orders
--    Used by: examples 22-29
-- ------------------------------------------------------------
CREATE TABLE orders (
    order_id    INTEGER,
    customer_id INTEGER,
    order_date  TEXT NOT NULL,
    amount      REAL NOT NULL DEFAULT 0
);
-- ------------------------------------------------------------
-- 5. employee_salary_history
--    Used by: example 30
-- ------------------------------------------------------------
CREATE TABLE employee_salary_history (
    id             INTEGER,
    employee_id    INTEGER NOT NULL,
    effective_date TEXT NOT NULL,
    salary         REAL NOT NULL
);
-- ============================================================
-- SAMPLE DATA
-- ============================================================
-- ------------------------------------------------------------
-- departments
-- ------------------------------------------------------------
INSERT INTO departments
    (department_id, department_name)
VALUES
    (1, 'Engineering'),
    (2, 'Finance'),
    (3, 'Sales'),
    (4, 'HR');
-- ------------------------------------------------------------
-- employees
-- ------------------------------------------------------------
INSERT INTO employees
    (employee_id,
     employee_name,
     first_name,
     last_name,
     email,
     department,
     department_id,
     manager_id,
     hire_date,
     salary)
VALUES
    (101, 'Alice',       'Alice',   'Johnson', 'alice@example.com',
     'Engineering', 1, NULL, '2019-03-01', 95000),
    (102, 'Bob',         'Bob',     'Smith',   'bob@example.com',
     'Finance', 2, NULL, '2020-06-15', 82000),
    (103, 'Carol',       'Carol',   'White',   'carol@example.com',
     'Sales', 3, NULL, '2021-01-10', 76000),
    (104, 'David',       'David',   'Kumar',   'david@example.com',
     'Engineering', 1, 101, '2018-07-20', 88000),
    (105, 'Mark',        'Mark',    'Lee',     'mark@example.com',
     'Engineering', 1, 101, '2020-11-05', 85000),
    (106, 'James',       'James',   'Brown',   'james@example.com',
     'Finance', 2, 102, '2019-09-01', 79000),
    (107, 'Robert',      'Robert',  'Davis',   'robert@example.com',
     'Sales', 3, NULL, '2022-02-14', 78000),
    (108, 'Michael',     'Michael', 'Wilson',  'michael@example.com',
     'Engineering', 1, 101, '2017-04-30', 90000),
    (109, 'Sarah',       'Sarah',   'Taylor',  'sarah@example.com',
     'Finance', 2, 102, '2021-08-22', 75000),
    (110, 'John Smith',  'John',    'Smith',   'jsmith1@example.com',
     'Sales', 3, NULL, '2020-05-18', 72000),
    (111, 'John Smith',  'John',    'Smith',   'jsmith2@example.com',
     'Sales', 3, NULL, '2021-03-11', 72000),
    (112, 'David Kumar', 'David',   'Kumar',   'dkumar2@example.com',
     'HR', 4, NULL, '2019-12-01', 68000),
    (113, 'David Kumar', 'David',   'Kumar',   'dkumar3@example.com',
     'HR', 4, NULL, '2020-06-30', 68000),
    (114, 'David Kumar', 'David',   'Kumar',   'dkumar4@example.com',
     'HR', 4, NULL, '2022-01-15', 68000),
    (145, 'Robert X',    'Robert',  'X',       'robertx@example.com',
     NULL, NULL, NULL, '2023-01-01', 60000),
    (198, 'Sarah Y',     'Sarah',   'Y',       'sarahy@example.com',
     NULL, NULL, NULL, '2023-06-01', 62000),
    (205, 'Michael B',   'Michael', 'B',       'michaelb@example.com',
     'Engineering', 1, NULL, '2016-11-01', 97000);
-- ------------------------------------------------------------
-- customers
-- ------------------------------------------------------------
INSERT INTO customers
    (customer_id, customer_name)
VALUES
    (1001, 'Alice Johnson'),
    (1002, 'Bob Smith'),
    (1003, 'Carol White'),
    (1004, 'James Brown'),
    (1005, 'John'),
    (1021, 'Priya'),
    (1044, 'David');
-- ------------------------------------------------------------
-- orders
-- ------------------------------------------------------------
INSERT INTO orders
    (order_id, customer_id, order_date, amount)
VALUES
    (1, 1001, '2026-01-01', 1000),
    (2, 1001, '2026-01-05', 1500),
    (3, 1001, '2026-01-10', 800),
    (4, 1002, '2026-01-03', 1200),
    (5, 1002, '2026-01-08', 1800),
    (6, 1003, '2026-01-15', 950),
    (7, 1004, '2026-01-20', 2100),
    -- February
    (8, 1001, '2026-02-02', 1300),
    (9, 1002, '2026-02-10', 1600),
    (10, 1003, '2026-02-18', 1100),
    (11, 1004, '2026-02-25', 1750),
    -- March
    (12, 1001, '2026-03-05', 1450),
    (13, 1002, '2026-03-12', 1900),
    (14, 1003, '2026-03-20', 1600);
-- ------------------------------------------------------------
-- employee_salary_history
-- ------------------------------------------------------------
INSERT INTO employee_salary_history
    (id, employee_id, effective_date, salary)
VALUES
    (1, 101, '2024-01-01', 70000),
    (2, 101, '2025-01-01', 78000),
    (3, 101, '2026-01-01', 85000),
    (4, 102, '2024-01-01', 65000),
    (5, 102, '2025-01-01', 72000),
    (6, 102, '2026-01-01', 79000);
-- ============================================================
-- SQLITE DATE NOTE
-- ============================================================
-- SQLite has no native DATE type.
-- Dates are stored as TEXT in 'YYYY-MM-DD' format.
--
-- Extract year:
-- strftime('%Y', order_date)
--
-- Extract month:
-- strftime('%m', order_date)
--
-- Example:
--
-- SELECT
--     strftime('%Y', order_date) AS order_year,
--     strftime('%m', order_date) AS order_month,
--     SUM(amount) AS total_sales
-- FROM orders
-- GROUP BY order_year, order_month
-- ORDER BY order_year, order_month;
-- ============================================================

🟢 Beginner SQL

1. Find All Employees

❓ Question

Retrieve all employees from the employees table.

🎯 Try Now

🏃 Try this query →

💻 Solution

SELECT *
FROM employees;

📊 Example Result

💡 What You Learn

SELECT * returns all columns and rows from the table.

2. Select Specific Columns

❓ Question

Display only the employee name, department, and salary.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT employee_name,
       department,
       salary
FROM employees;

📊 Example Result

💡 What You Learn

Select only the columns needed for your analysis.

3. Filter Records Using WHERE

❓ Question

Find employees whose salary is greater than 80,000.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT *
FROM employees
WHERE salary > 80000;

📊 Example Result

💡 What You Learn

WHERE filters rows based on a condition.

4. Find Employees in a Specific Department

❓ Question

Find all employees working in the Engineering department.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT *
FROM employees
WHERE department = 'Engineering';

📊 Example Result

💡 What You Learn

Use WHERE to filter rows based on column values.

5. Sort Employees by Salary

❓ Question

Display employees from the highest salary to the lowest salary.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT employee_name,
       salary
FROM employees
ORDER BY salary DESC;

📊 Example Result

💡 What You Learn

ORDER BY ... DESC sorts values from highest to lowest.

🟡 Aggregation & Grouping

6. Find the Highest Salary

❓ Question

Find the highest salary in the company.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT MAX(salary) AS highest_salary
FROM employees;

📊 Example Result

💡 What You Learn

MAX() returns the largest value.

7. Find the Lowest Salary

❓ Question

Find the lowest salary.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT MIN(salary) AS lowest_salary
FROM employees;

📊 Example Result

8. Calculate the Average Salary

❓ Question

Find the average employee salary.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT AVG(salary) AS average_salary
FROM employees;

📊 Example Result

9. Count Employees

❓ Question

Find the total number of employees.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT COUNT(*) AS employee_count
FROM employees;

📊 Example Result

10. Count Employees by Department

❓ Question

Find how many employees work in each department.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT department,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department;

📊 Example Result

11. Find Departments With More Than 10 Employees

❓ Question

Find departments that have more than 10 employees.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT department,
       COUNT(*) AS employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

📊 Example Result

💡 What You Learn

HAVING filters groups after aggregation.

🟠 Subqueries & Duplicates

12. Find the Second-Highest Salary

❓ Question

Find the second-highest salary in the company.

🎯 Try Now

👉 Try this interview question →

💻 Solution

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
);

📊 Example Result

💡 What You Learn

The inner query finds the highest salary. The outer query finds the highest value below it.

13. Find Duplicate Employee Records

❓ Question

Find employee names that appear more than once.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT employee_name,
       COUNT(*) AS duplicate_count
FROM employees
GROUP BY employee_name
HAVING COUNT(*) > 1;

📊 Example Result

14. Find Employees Without a Manager

❓ Question

Find employees whose manager_id is missing.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT *
FROM employees
WHERE manager_id IS NULL;

📊 Example Result

💡 What You Learn

Use IS NULL, not = NULL.

🔵 SQL JOINs

15. INNER JOIN Two Tables

❓ Question

Return each employee together with their department name.

🎯 Try Now

👉 Try this JOIN →

💻 Solution

SELECT e.employee_name,
       d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

📊 Example Result

💡 What You Learn

INNER JOIN returns matching records from both tables.

16. Find Employees Without a Matching Department

❓ Question

Find employees whose department doesn’t exist in the department table.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT e.employee_id,
       e.employee_name
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

📊 Example Result

💡 What You Learn

This is a common anti-join pattern.

🔴 Advanced SQL & Window Functions

17. Employees Earning More Than Their Department Average

❓ Question

Find employees whose salary is higher than their department’s average salary.

🎯 Try Now

👉 Try this advanced query →

💻 Solution

SELECT e.employee_id,
       e.employee_name,
       e.department,
       e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e.department
);

📊 Example Result

💡 What You Learn

This is a correlated subquery — the kind of pattern that frequently appears in FAANG-style SQL interview traps.

18. Find the Top 3 Salaries

❓ Question

Find the three highest-paid employees.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT employee_name,
       salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

📊 Example Result

19. Find the Top 3 Employees in Each Department

❓ Question

Find the three highest-paid employees from every department.

🎯 Try Now

👉 Try this window-function challenge →

💻 Solution

SELECT employee_name,
       department,
       salary
FROM (
    SELECT employee_name,
           department,
           salary,
           ROW_NUMBER() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS rn
    FROM employees
) x
WHERE rn <= 3;

📊 Example Result

💡 What You Learn

ROW_NUMBER() creates a unique sequence within each department.

20. Rank Employees by Salary

❓ Question

Assign a salary rank to every employee.

🎯 Try Now

👉 Try this ranking query →

💻 Solution

SELECT employee_name,
       department,
       salary,
       RANK() OVER (
           ORDER BY salary DESC
       ) AS salary_rank
FROM employees;

📊 Example Result

💡 What You Learn

RANK() gives tied rows the same rank and leaves a gap after the tie.

21. Find the Second-Highest Salary in Each Department

❓ Question

Find the employee(s) with the second-highest salary in each department.

🎯 Try Now

👉 Try this challenge →

💻 Solution

SELECT employee_name,
       department,
       salary
FROM (
    SELECT employee_name,
           department,
           salary,
           DENSE_RANK() OVER (
               PARTITION BY department
               ORDER BY salary DESC
           ) AS salary_rank
    FROM employees
) x
WHERE salary_rank = 2;

📊 Example Result

💡 What You Learn

DENSE_RANK() handles ties without leaving gaps.

22. Calculate a Running Total

❓ Question

Calculate a cumulative running total of sales.

🎯 Try Now

👉 Try the running-total query →

💻 Solution

SELECT order_date,
       amount,
       SUM(amount) OVER (
           ORDER BY order_date
       ) AS running_total
FROM orders
ORDER BY order_date;

📊 Example Result

💡 What You Learn

Window functions allow you to calculate cumulative values while keeping individual rows.

⭐ 23. Find the Previous Order Using LAG()

This is where window functions become especially useful.

❓ Question

For each customer, find their previous order amount.

🎯 Try Now

👉 Try the LAG() example →

💻 Solution

SELECT customer_id,
       order_date,
       amount,
       LAG(amount) OVER (
           PARTITION BY customer_id
           ORDER BY order_date
       ) AS previous_order_amount
FROM orders;

📊 Example Result

💡 What You Learn

LAG() lets you access a value from a previous row without performing a self-join.

The first row for each customer has no previous order, so it returns NULL.

🔥 Challenge

Calculate the difference between the current order and the previous order.

Hint:

amount - LAG(amount) OVER (...)

⭐ 24. Find the Next Order Using LEAD()

❓ Question

For each customer, find the amount of their next order.

🎯 Try Now

👉 Try the LEAD() example →

💻 Solution

SELECT customer_id,
       order_date,
       amount,
       LEAD(amount) OVER (
           PARTITION BY customer_id
           ORDER BY order_date
       ) AS next_order_amount
FROM orders;

📊 Example Result

💡 What You Learn

LEAD() lets you access a value from the next row.

The last row for each customer has no next order, so it returns NULL.

🔥 Challenge

Can you calculate the difference between the next order and the current order?

LEAD(amount) OVER (...) - amount

⭐ 25. Calculate Sales Change Using LAG()

❓ Question

Calculate the change in sales compared with the previous order.

🎯 Try Now

👉 Try this LAG() challenge →

💻 Solution

SELECT customer_id,
       order_date,
       amount,
       amount - LAG(amount) OVER (
           PARTITION BY customer_id
           ORDER BY order_date
       ) AS sales_change
FROM orders;

📊 Example Result

💡 What You Learn

LAG() is useful for comparing the current row with a previous row.

This pattern is commonly used for:

• Sales comparisons
• Month-over-month analysis
• Previous transaction analysis
• Trend analysis
• Change detection

⭐ 26. Find Sales Growth Using LEAD()

❓ Question

Calculate the difference between the next order and the current order.

🎯 Try Now

👉 Try this LEAD() challenge →

💻 Solution

SELECT customer_id,
       order_date,
       amount,
       LEAD(amount) OVER (
           PARTITION BY customer_id
           ORDER BY order_date
       ) - amount AS next_order_change
FROM orders;

📊 Example Result

💡 What You Learn

LEAD() is useful when your analysis needs information about a future row.

⭐ 27. Find Monthly Sales

❓ Question

Calculate total sales for each month. For a deeper look at this topic, see the guide on SQL date extraction and monthly trends.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    SUM(amount) AS total_sales
FROM orders
GROUP BY
    EXTRACT(YEAR FROM order_date),
    EXTRACT(MONTH FROM order_date)
ORDER BY
    order_year,
    order_month;

📊 Example Result

28. Find Customers Who Have Never Placed an Order

❓ Question

Find customers who don’t have any orders.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT c.customer_id,
       c.customer_name
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

📊 Example Result

💡 What You Learn

This is a common anti-join pattern.

29. Find the Most Recent Order for Each Customer

❓ Question

Find the latest order placed by every customer.

🎯 Try Now

👉 Try this query →

💻 Solution

SELECT customer_id,
       order_id,
       order_date
FROM (
    SELECT customer_id,
           order_id,
           order_date,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY order_date DESC
           ) AS rn
    FROM orders
) x
WHERE rn = 1;

📊 Example Result

💡 What You Learn

ROW_NUMBER() allows us to select the latest record from each customer group.

30. Compare Current and Previous Salary

❓ Question

Suppose employees receive salary changes over time. Find the previous salary for each employee.

Assume the table contains:

• employee_id
• effective_date
• salary

🎯 Try Now

👉 Try this LAG() challenge →

💻 Solution

SELECT employee_id,
       effective_date,
       salary,
       LAG(salary) OVER (
           PARTITION BY employee_id
           ORDER BY effective_date
       ) AS previous_salary
FROM employee_salary_history;

📊 Example Result

💡 What You Learn

This is a very common real-world use of LAG().

You can extend it to calculate:

salary - previous_salary

and determine how much an employee’s salary changed over time.

🧠 LAG() vs LEAD()

A simple way to remember them:

                 Current Row
                     │
          ┌──────────┴──────────┐
          ↓                     ↓
        LAG()                  LEAD()
     Previous Row             Next Row

LAG()

Looks backward.

LAG(amount)

Useful for:

• Previous transaction
• Previous month’s sales
• Previous salary
• Previous status
• Change calculations

LEAD()

Looks forward.

LEAD(amount)

Useful for:

• Next transaction
• Next month’s sales
• Next event
• Future value comparison
• Time-gap analysis

🧠 What Did We Cover?

These 30 problems cover many SQL patterns used in real projects and technical interviews.

🚀 Don’t Just Read SQL. Run It.

You’ve now seen 30 SQL problems.

But reading the solutions is only the first step.

The real learning happens when you:

Write → Run → Inspect → Modify → Run Again

Try changing the queries.

Can you:

• Find the third-highest salary?
• Find the top 3 salaries in each department?
• Find employees earning above the company average?
• Find duplicate records using another approach?
• Solve the second-highest salary using a CTE?
• Calculate a running total?
• Find the previous transaction using LAG()?
• Find the next transaction using LEAD()?
• Calculate month-over-month changes?
• Compare an employee’s current salary with their previous salary?

🎯 Practice It Yourself

Ready to stop reading and start writing SQL?

👉 🚀 Open the Srinimfs Interactive SQL Playground

Write your query.

Run it.

Inspect the result.

Modify it.

Learn from it.

🔥 What’s Next?

More interactive SQL challenges can cover:

• Advanced JOIN problems
• CTE challenges
• Window-function challenges
• LAG() and LEAD() challenges
• Date and time problems
• Complex aggregations
• SQL interview questions
• Data-engineering SQL problems
• Query optimization
• Real-world business scenarios
• SQL + Databricks examples

The goal is simple:

Don’t just memorize SQL syntax. Learn how to solve problems with SQL.

💬 Your Challenge

Pick one of these problems and solve it in two different ways.

For example:

Find the previous order amount

Try solving it with:

1. LAG()

2. A self-join

Then compare the two approaches.

Or try:

Find the second-highest salary

Using:

1. A subquery

2. A CTE

3. DENSE_RANK()

The more approaches you understand, the better you become at solving SQL problems.

🚀 Start Practicing SQL

Open the Srinimfs Interactive SQL Playground →

Write SQL. Run SQL. Experiment. Learn.

Happy querying!