Beginner SQL Problems

Problem 1: Find High Salary Employees

Schema:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2),
    hire_date DATE
);

INSERT INTO employees VALUES
(1, 'Alice', 'Engineering', 95000, '2020-01-15'),
(2, 'Bob', 'Marketing', 65000, '2019-03-20'),
(3, 'Charlie', 'Engineering', 105000, '2018-07-10'),
(4, 'Diana', 'HR', 72000, '2021-06-01'),
(5, 'Eve', 'Engineering', 110000, '2017-11-25'),
(6, 'Frank', 'Marketing', 58000, '2022-02-14'),
(7, 'Grace', 'HR', 68000, '2020-09-30'),
(8, 'Henry', 'Engineering', 98000, '2019-05-18');

Question: Find all employees who earn more than $80,000, sorted by salary descending.

Solution:

SELECT emp_id, emp_name, department, salary
FROM employees
WHERE salary > 80000
ORDER BY salary DESC;

Output:

emp_id | emp_name | department  | salary
5      | Eve      | Engineering | 110000
3      | Charlie  | Engineering | 105000
8      | Henry    | Engineering | 98000
1      | Alice    | Engineering | 95000

Explanation:

• WHERE salary > 80000 filters employees with salary above $80K
• ORDER BY salary DESC sorts from highest to lowest
• All high earners are in Engineering department (pattern worth noting)

Problem 2: Department Employee Count

Schema: (same employees table as Problem 1)

Question: Count the number of employees in each department and display departments with 2 or more employees, sorted by count descending.

Solution:

SELECT department, COUNT(*) as employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 2
ORDER BY employee_count DESC;

Output:

department  | employee_count
Engineering | 4
Marketing   | 2
HR          | 2

Explanation:

• GROUP BY department groups rows by department
• COUNT(*) counts employees in each group
• HAVING COUNT(*) >= 2 filters groups (not rows) with 2+ employees
• ORDER BY employee_count DESC sorts by count

Key Concept: Use WHERE to filter rows before grouping, HAVING to filter groups after aggregation.

Problem 3: Average Salary by Department

Schema: (same employees table)

Question: Find the average salary for each department, rounded to 2 decimal places. Only include departments where the average salary is above $70,000.

Solution:

SELECT 
    department,
    ROUND(AVG(salary), 2) as avg_salary,
    COUNT(*) as num_employees
FROM employees
GROUP BY department
HAVING AVG(salary) > 70000
ORDER BY avg_salary DESC;

Output:

department  | avg_salary | num_employees
Engineering | 102000.00  | 4
HR          | 70000.00   | 2

Explanation:

• AVG(salary) calculates mean salary per department
• ROUND(..., 2) formats to 2 decimal places
• HAVING AVG(salary) > 70000 filters departments by average
• Note: Marketing excluded (avg $61,500)

Problem 4: Find Customers Without Orders

Schema:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    city VARCHAR(50)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2)
);

INSERT INTO customers VALUES
(1, 'Alice', 'New York'),
(2, 'Bob', 'Chicago'),
(3, 'Charlie', 'Boston'),
(4, 'Diana', 'New York');

INSERT INTO orders VALUES
(101, 1, '2024-01-15', 250.00),
(102, 1, '2024-02-20', 180.50),
(103, 2, '2024-01-22', 320.00);

Question: Find all customers who have never placed an order.

Solution:

-- Method 1: LEFT JOIN
SELECT c.customer_id, c.customer_name, c.city
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Method 2: NOT IN
SELECT customer_id, customer_name, city
FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- Method 3: NOT EXISTS
SELECT c.customer_id, c.customer_name, c.city
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Output:

customer_id | customer_name | city
3           | Charlie       | Boston
4           | Diana         | New York

Explanation:

• LEFT JOIN keeps all customers; NULL order_id means no matching order
• NOT IN excludes customers found in orders subquery
• NOT EXISTS is often most efficient for large datasets

Problem 5: Monthly Order Totals

Schema: (same orders table)

Additional Data:

INSERT INTO orders VALUES
(104, 1, '2024-02-10', 450.00),
(105, 2, '2024-02-15', 120.00),
(106, 3, '2024-02-28', 890.00),
(107, 1, '2024-03-05', 340.00);

Question: Find the total order amount for each month in 2024, showing month name and total. Only include months with total above $500.

Solution:

SELECT 
    MONTHNAME(order_date) as month_name,
    MONTH(order_date) as month_num,
    COUNT(*) as order_count,
    ROUND(SUM(total_amount), 2) as total_sales
FROM orders
WHERE YEAR(order_date) = 2024
GROUP BY MONTH(order_date), MONTHNAME(order_date)
HAVING SUM(total_amount) > 500
ORDER BY month_num;

Output:

month_name | month_num | order_count | total_sales
January    | 1         | 3           | 750.50
February   | 2         | 3           | 1460.00

Explanation:

• MONTHNAME() extracts month name from date
• YEAR(order_date) = 2024 filters for 2024
• GROUP BY MONTH() groups by month
• HAVING SUM(total_amount) > 500 filters low-revenue months

Problem 6: Find Duplicate Emails

Schema:

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    created_at TIMESTAMP
);

INSERT INTO users VALUES
(1, 'alice', 'alice@example.com', '2024-01-01'),
(2, 'alice2', 'alice@example.com', '2024-01-02'),
(3, 'bob', 'bob@example.com', '2024-01-03'),
(4, 'bob2', 'bob@example.com', '2024-01-04'),
(5, 'charlie', 'charlie@example.com', '2024-01-05');

Question: Find all email addresses that are used by more than one user.

Solution:

SELECT email, COUNT(*) as usage_count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Output:

email             | usage_count
alice@example.com | 2
bob@example.com   | 2

Explanation:

• GROUP BY email groups users by email
• COUNT(*) > 1 identifies emails used by multiple users
• Common pattern for data deduplication

Problem 7: Second Highest Salary

Schema: (same employees table)

Question: Find the second highest salary from the employees table.

Solution:

-- Method 1: Subquery
SELECT MAX(salary) as second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 2: LIMIT/OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Method 3: Window function
SELECT salary as second_highest
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM employees
) ranked
WHERE rank = 2;

Output:

second_highest
105000

Explanation:

• Method 1: Find max salary that's less than the overall max
• Method 2: Sort descending, skip 1st, return 1st (2nd highest)
• Method 3: Rank all salaries, pick rank 2
• DENSE_RANK handles ties (two people with same salary = same rank)

Problem 8: Employee Hire Date Range

Schema: (same employees table)

Question: Find the earliest and latest hire dates, and count how many employees were hired in each year.

Solution:

-- Overall stats
SELECT 
    MIN(hire_date) as earliest_hire,
    MAX(hire_date) as latest_hire,
    COUNT(*) as total_employees
FROM employees;

-- By year
SELECT 
    YEAR(hire_date) as hire_year,
    COUNT(*) as employees_hired
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY hire_year;

Output (by year):

hire_year | employees_hired
2017      | 1
2018      | 1
2019      | 2
2020      | 2
2021      | 1
2022      | 1

Explanation:

• MIN(hire_date) finds earliest hire
• MAX(hire_date) finds latest hire
• GROUP BY YEAR(hire_date) groups by year
• Useful for workforce planning analysis

Problem 9: Cross-Department Salary Comparison

Schema: (same employees table)

Question: For each employee, show their name, department, salary, and the average salary of their department.

Solution:

SELECT 
    emp_name,
    department,
    salary,
    ROUND(AVG(salary) OVER (PARTITION BY department), 2) as dept_avg_salary,
    salary - ROUND(AVG(salary) OVER (PARTITION BY department), 2) as diff_from_avg
FROM employees
ORDER BY department, salary DESC;

Output:

emp_name | department  | salary | dept_avg_salary | diff_from_avg
Eve      | Engineering | 110000 | 102000.00       | 8000.00
Charlie  | Engineering | 105000 | 102000.00       | 3000.00
Henry    | Engineering | 98000  | 102000.00       | -4000.00
Alice    | Engineering | 95000  | 102000.00       | -7000.00
Diana    | HR          | 72000  | 70000.00        | 2000.00
Grace    | HR          | 68000  | 70000.00        | -2000.00
Bob      | Marketing   | 65000  | 61500.00        | 3500.00
Frank    | Marketing   | 58000  | 61500.00        | -3500.00

Explanation:

• AVG(salary) OVER (PARTITION BY department) calculates department average for each row
• PARTITION BY groups the window function without collapsing rows
• Shows how each employee compares to their department average

Problem 10: Customer Order Summary

Schema: (customers and orders tables from Problem 4)

Question: For each customer, show their name, total orders, total amount spent, and average order size. Include customers with no orders (show 0).

Solution:

SELECT 
    c.customer_name,
    c.city,
    COALESCE(COUNT(o.order_id), 0) as total_orders,
    COALESCE(ROUND(SUM(o.total_amount), 2), 0) as total_spent,
    COALESCE(ROUND(AVG(o.total_amount), 2), 0) as avg_order_size
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.city
ORDER BY total_spent DESC;

Output:

customer_name | city     | total_orders | total_spent | avg_order_size
Alice         | New York | 3            | 880.50      | 293.50
Bob           | Chicago  | 1            | 320.00      | 320.00
Charlie       | Boston   | 0            | 0.00        | 0.00
Diana         | New York | 0            | 0.00        | 0.00

Explanation:

• LEFT JOIN includes customers without orders
• COALESCE(..., 0) converts NULL to 0 for customers with no orders
• GROUP BY customer aggregates order data per customer
• Useful for customer lifetime value analysis