Chapter 10: 20 Solved Real-World Interview Queries with Live Outputs

✦

AI Reading

Quick summary of Chapter 10

Ace your technical database interviews with this comprehensive masterclass. In this chapter, you will review 20 real-world SQL challenges, complete with tested queries, execution breakdown, and exact live database output tables.

• Pattern & Regex Queries: Solve string matching challenges using LIKE, LOWER, and REVERSE.
• Compensation Analytics: Write subqueries and window functions to extract maximum, average, and Nth highest salaries.
• Hierarchical Relationships: Query reporting trees and reportee counts using self-joins.
• Date Manipulations: Extract annual hiring trends and day-of-month filters with YEAR, MONTH, and DAY functions.
• Cross-Department Discrepancies: Detect employees and supervisors working in conflicting departments.

Welcome to Chapter 10. Technical interviews for software engineers, backend developers, and data analysts frequently include live SQL coding challenges. Interviewers evaluate not only your ability to produce the correct result set, but also your understanding of index efficiency, tie handling, and clean SQL syntax.

Below are the 20 comprehensive interview queries from our dataset. Every solution below has been executed and verified against our live MariaDB database.

---

Exercise 1: First name starts with ‘S’

Goal: Select first_name, last_name, job_id, and salary whose first name starts with ‘S’.

SELECT first_name, last_name, job_id, salary
FROM employees
WHERE UPPER(first_name) LIKE 'S%';

Live Database Output:

+------------+-----------+----------+--------+
| first_name | last_name | job_id   | salary |
+------------+-----------+----------+--------+
| Steven     | King      | AD_PRES  |  24000 |
| Shelli     | Baida     | PU_CLERK |   2900 |
| Sigal      | Tobias    | PU_CLERK |   2800 |
| Shanta     | Vollman   | ST_MAN   |   6500 |
| Steven     | Markle    | ST_CLERK |   2200 |
+------------+-----------+----------+--------+

---

Exercise 2: Employee with the highest salary

SELECT employee_id, first_name, last_name, job_id, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

Live Database Output:

+-------------+------------+-----------+---------+--------+
| employee_id | first_name | last_name | job_id  | salary |
+-------------+------------+-----------+---------+--------+
|         100 | Steven     | King      | AD_PRES |  24000 |
+-------------+------------+-----------+---------+--------+

---

Exercise 3: Employee with the second highest salary (LIMIT)

SELECT employee_id, first_name, last_name, job_id, salary
FROM employees
WHERE salary != (SELECT MAX(salary) FROM employees)
ORDER BY salary DESC
LIMIT 1;

Live Database Output:

+-------------+------------+-----------+--------+--------+
| employee_id | first_name | last_name | job_id | salary |
+-------------+------------+-----------+--------+--------+
|         102 | Lex        | De Haan   | AD_VP  |  17000 |
+-------------+------------+-----------+--------+--------+

---

Exercise 4: Fetch employees with the Nth highest salary

SET @input := 3;
SELECT employee_id, first_name, last_name, job_id, salary 
FROM employees e 
WHERE @input = (
    SELECT COUNT(DISTINCT p.salary) 
    FROM employees p 
    WHERE p.salary >= e.salary
);

Live Database Output:

+-------------+------------+-----------+--------+--------+
| employee_id | first_name | last_name | job_id | salary |
+-------------+------------+-----------+--------+--------+
|         108 | Nancy      | Greenberg | FI_MGR |  12000 |
+-------------+------------+-----------+--------+--------+

---

Exercise 5: Employees, their managers, and salaries (SELF JOIN)

SELECT 
    CONCAT(emp.first_name, ' ', emp.last_name) AS employee,
    emp.salary AS emp_sal,
    CONCAT(mgr.first_name, ' ', mgr.last_name) AS manager,
    mgr.salary AS mgr_sal
FROM employees emp
JOIN employees mgr ON emp.manager_id = mgr.employee_id;

---

Exercise 6: Count of employees under each manager

SELECT 
    sup.employee_id,
    CONCAT(sup.first_name, ' ', sup.last_name) AS manager_name,
    COUNT(sub.employee_id) AS number_of_reportees
FROM employees sub 
JOIN employees sup ON sub.manager_id = sup.employee_id
GROUP BY sup.employee_id, sup.first_name, sup.last_name
ORDER BY number_of_reportees DESC;

Live Database Output:

+-------------+------------------+---------------------+
| employee_id | manager_name     | number_of_reportees |
+-------------+------------------+---------------------+
|         100 | Steven King      |                   8 |
|         108 | Nancy Greenberg  |                   5 |
|         114 | Den Raphaely     |                   5 |
|         103 | Alexander Hunold |                   4 |
|         120 | Matthew Weiss    |                   4 |
|         121 | Adam Fripp       |                   2 |
|         101 | Neena Kochhar    |                   1 |
|         102 | Lex De Haan      |                   1 |
+-------------+------------------+---------------------+

---

Exercise 7: Employee count per department

SELECT 
    dept.department_name,
    COUNT(emp.employee_id) AS emp_count
FROM employees emp
JOIN departments dept ON emp.department_id = dept.department_id
GROUP BY dept.department_name
ORDER BY emp_count DESC;

---

Exercise 8: Annual hiring trends (Hired year-wise)

SELECT 
    YEAR(hire_date) AS hired_year,
    COUNT(*) AS employees_hired_count
FROM employees
GROUP BY YEAR(hire_date)
ORDER BY employees_hired_count DESC;

Live Database Output:

+------------+-----------------------+
| hired_year | employees_hired_count |
+------------+-----------------------+
|       1997 |                    10 |
|       1999 |                     5 |
|       1998 |                     4 |
|       1994 |                     3 |
|       1995 |                     2 |
|       1996 |                     1 |
|       2000 |                     1 |
|       1987 |                     1 |
|       1989 |                     1 |
|       1993 |                     1 |
|       1990 |                     1 |
|       1991 |                     1 |
+------------+-----------------------+

---

Exercise 9: Overall salary range and average

SELECT 
    MIN(salary) AS min_sal, 
    MAX(salary) AS max_sal,
    ROUND(AVG(salary)) AS avg_sal
FROM employees;

Live Database Output:

+---------+---------+---------+
| min_sal | max_sal | avg_sal |
+---------+---------+---------+
|    2200 |   24000 |    6977 |
+---------+---------+---------+

---

Exercise 10: Segment employees into three salary tiers (CASE Statement)

SELECT 
    CONCAT(first_name, ' ', last_name) AS employee,
    salary,
    CASE
        WHEN salary >= 2000 AND salary < 5000 THEN 'low' 
        WHEN salary >= 5000 AND salary < 10000 THEN 'mid'
        ELSE 'high'
    END AS salary_level
FROM employees
ORDER BY employee ASC;

---

Exercise 11: Employees whose first name contains “an”

SELECT first_name
FROM employees
WHERE LOWER(first_name) LIKE '%an%';

---

Exercise 12: Format phone numbers with hyphens

SELECT 
    CONCAT(first_name, ' ', last_name) AS employee,
    REPLACE(phone_number, '.', '-') AS phone_number
FROM employees;

---

Exercise 13: Employees who joined in August 1994

SELECT 
    CONCAT(first_name, ' ', last_name) AS employee,
    hire_date
FROM employees
WHERE YEAR(hire_date) = 1994
  AND MONTH(hire_date) = 8;

Live Database Output:

+-----------------+------------+
| employee        | hire_date  |
+-----------------+------------+
| Nancy Greenberg | 1994-08-17 |
| Daniel Faviet   | 1994-08-12 |
+-----------------+------------+

---

Exercise 14: Employees who earn more than the company average

SELECT 
    CONCAT(emp.first_name, ' ', emp.last_name) AS name,
    emp.employee_id, 
    dept.department_name AS department,
    dept.department_id,
    emp.salary
FROM departments dept
JOIN employees emp ON dept.department_id = emp.department_id
WHERE emp.salary > (SELECT AVG(salary) FROM employees)
ORDER BY dept.department_id;

---

Exercise 15: Maximum salary from each department

SELECT 
    dept.department_id,
    dept.department_name AS department,
    MAX(emp.salary) AS maximum_salary
FROM departments dept
JOIN employees emp ON dept.department_id = emp.department_id
GROUP BY dept.department_id, dept.department_name
ORDER BY dept.department_id ASC;

---

Exercise 16: The 5 least earning employees

SELECT first_name, last_name, employee_id, salary
FROM employees
ORDER BY salary ASC
LIMIT 5;

Live Database Output:

+------------+-------------+-------------+--------+
| first_name | last_name   | employee_id | salary |
+------------+-------------+-------------+--------+
| Steven     | Markle      |         128 |   2200 |
| James      | Landry      |         127 |   2400 |
| Karen      | Colmenares  |         119 |   2500 |
| Guy        | Himuro      |         118 |   2600 |
| Irene      | Mikkilineni |         126 |   2700 |
+------------+-------------+-------------+--------+

---

Exercise 17: Employees hired in the 1980s

SELECT 
    employee_id,
    CONCAT(first_name, ' ', last_name) AS employee,
    hire_date
FROM employees
WHERE YEAR(hire_date) BETWEEN 1980 AND 1989;

---

Exercise 18: First name and reversed name

SELECT 
    LOWER(first_name) AS name, 
    LOWER(REVERSE(first_name)) AS name_in_reverse
FROM employees
LIMIT 8;

---

Exercise 19: Employees hired after the 15th of the month

SELECT 
    employee_id,
    CONCAT(first_name, ' ', last_name) AS employee,
    hire_date
FROM employees
WHERE DAY(hire_date) > 15;

---

Exercise 20: Managers and reportees in different departments

SELECT 
    CONCAT(mgr.first_name, ' ', mgr.last_name) AS manager,
    CONCAT(emp.first_name, ' ', emp.last_name) AS employee,
    mgr.department_id AS mgr_dept,
    emp.department_id AS emp_dept
FROM employees emp
JOIN employees mgr ON emp.manager_id = mgr.employee_id
WHERE emp.department_id <> mgr.department_id
ORDER BY manager ASC;