Skip to main content

🟢 GeeksforGeeks — SQL Practice (30 Questions)

Fresher Data Analyst — Interview Problem Set​

Use the following schema for all questions unless stated otherwise:

employees

emp_idnamedepartmentsalarymanager_idjoin_date
1AaravSales60000NULL2020-01-15
2PriyaEngineering8500012020-03-22
3RohanSales5500012021-06-10
4SnehaEngineering9000022021-08-01
5VikramHR5000012022-01-05
6NehaEngineering7800022022-04-18
7ArjunSales6200032023-02-14
8KavitaHR5200052023-05-20

orders

order_idcustomer_idproductamountorder_dateregion
101C1Laptop450002023-01-10North
102C2Phone150002023-01-15South
103C1Mouse5002023-02-20North
104C3Laptop480002023-03-05West
105C2Keyboard12002023-03-18South
106C4Phone180002023-04-02East
107C1Monitor220002023-04-15North
108C5Laptop420002023-05-01North
109C3Mouse6002023-06-12West
110C4Laptop500002023-06-25East

SECTION A: Basic Queries (Q1–Q10)​

Q1: Find all employees in the Engineering department.​

SELECT * FROM employees WHERE department = 'Engineering';

Result: Priya, Sneha, Neha (3 rows)

🧠 Concept: Simple WHERE clause filtering. Always use single quotes for string values in SQL.

Q2: Find employees earning more than ₹60,000, sorted by salary descending.​

SELECT name, department, salary
FROM employees
WHERE salary > 60000
ORDER BY salary DESC;

Result: Sneha (90K), Priya (85K), Neha (78K), Arjun (62K)

🧠 Interview tip: ORDER BY DESC = highest first. Default is ASC (ascending).

Q3: Count the number of employees in each department.​

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

Result:

departmentemployee_count
Engineering3
Sales3
HR2

🧠 Key concept: GROUP BY is used with aggregate functions (COUNT, SUM, AVG, MAX, MIN).

Q4: Find the average salary per department.​

SELECT department,
       ROUND(AVG(salary), 2) AS avg_salary,
       MIN(salary) AS min_salary,
       MAX(salary) AS max_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

Result:

departmentavg_salarymin_salarymax_salary
Engineering84333.337800090000
Sales59000.005500062000
HR51000.005000052000

Q5: Find departments where the average salary exceeds ₹55,000.​

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 55000;

Result: Engineering (84333), Sales (59000)

🧠 WHERE vs HAVING: WHERE filters rows BEFORE grouping. HAVING filters groups AFTER aggregation. You cannot use aggregate functions in WHERE.

Q6: Find employees who joined in 2022.​

SELECT name, join_date
FROM employees
WHERE YEAR(join_date) = 2022;
-- Alternative: WHERE join_date BETWEEN '2022-01-01' AND '2022-12-31';

Result: Vikram (2022-01-05), Neha (2022-04-18)

Q7: Find the second-highest salary.​

-- Method 1: Using subquery
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

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

Result: 85000 (Priya's salary)

🧠 Classic interview question! Method 2 is cleaner but Method 1 works on all SQL dialects. For Nth highest, change the inner condition or OFFSET value.

Q8: Find employees whose name starts with 'A' or ends with 'a'.​

SELECT name FROM employees
WHERE name LIKE 'A%' OR name LIKE '%a';

Result: Aarav, Arjun, Priya, Sneha, Neha, Kavita

🧠 LIKE patterns: % = any number of characters, _ = exactly one character. LIKE 'A___' matches 4-letter names starting with A.

Q9: Find total order amount by region, but only regions with total > ₹50,000.​

SELECT region,
       SUM(amount) AS total_sales,
       COUNT(*) AS order_count
FROM orders
GROUP BY region
HAVING SUM(amount) > 50000
ORDER BY total_sales DESC;

Result:

regiontotal_salesorder_count
North1095004
East680002

Q10: Find duplicate products in orders (products ordered more than once).​

SELECT product, COUNT(*) AS times_ordered
FROM orders
GROUP BY product
HAVING COUNT(*) > 1
ORDER BY times_ordered DESC;

Result: Laptop (4), Phone (2), Mouse (2)

SECTION B: Joins & Subqueries (Q11–Q20)​

Q11: Find employees and their manager names (Self Join).​

SELECT e.name AS employee,
       m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

Result:

employeemanager
AaravNULL
PriyaAarav
RohanAarav
SnehaPriya
VikramAarav
NehaPriya
ArjunRohan
KavitaVikram

🧠 Self Join: The table is joined with itself. LEFT JOIN ensures employees without managers (like Aarav, the CEO) are still shown.

Q12: Find employees who earn more than their department's average salary.​

SELECT e.name, e.department, e.salary, dept_avg.avg_salary
FROM employees e
JOIN (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) dept_avg ON e.department = dept_avg.department
WHERE e.salary > dept_avg.avg_salary;

Result: Sneha (90K > 84.3K in Engineering), Arjun (62K > 59K in Sales), Kavita (52K > 51K in HR)

🧠 Subquery in JOIN: The inner query calculates department averages, then the outer query compares each employee against their department's average.

Q13: Find customers who have placed more than 2 orders.​

SELECT customer_id,
       COUNT(*) AS order_count,
       SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 2;

Result: C1 (3 orders, ₹67,500)

SELECT region, product, order_count
FROM (
    SELECT region, product, COUNT(*) AS order_count,
           RANK() OVER (PARTITION BY region ORDER BY COUNT(*) DESC) AS rnk
    FROM orders
    GROUP BY region, product
) ranked
WHERE rnk = 1;

Result:

regionproductorder_count
NorthLaptop2
SouthPhone1
WestLaptop1
EastPhone1

🧠 RANK() OVER (PARTITION BY ...): This is a window function. PARTITION BY groups by region, ORDER BY sorts within each group. RANK=1 gives the top product per region.

Q15: Find employees who earn more than every employee in the HR department.​

-- Using ALL
SELECT name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department = 'HR');

-- Using MAX
SELECT name, salary
FROM employees
WHERE salary > (SELECT MAX(salary) FROM employees WHERE department = 'HR');

Result: Aarav (60K), Priya (85K), Rohan (55K), Sneha (90K), Neha (78K), Arjun (62K) — all > 52K (HR max)

Q16: Find orders where the amount is above the overall average order amount.​

SELECT order_id, product, amount,
       (SELECT ROUND(AVG(amount), 2) FROM orders) AS overall_avg
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

Result: Orders 101, 104, 107, 108, 110 (all above avg ₹24,230)

Q17: INNER JOIN vs LEFT JOIN vs RIGHT JOIN — Explain with example.​

-- Scenario: customers table has C1-C5, orders reference C1-C5
-- What if we add customer C6 who never ordered?

-- INNER JOIN: Only matching rows (C6 excluded)
SELECT c.customer_id, o.order_id
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

-- LEFT JOIN: All customers + their orders (C6 shows with NULL order)
SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- RIGHT JOIN: All orders + customer info (rarely used, same as LEFT with reversed tables)

🧠 Interview mantra: "LEFT JOIN = sabke saab, RIGHT waale match karo. INNER = sirf dono mein common. LEFT = left table ke saare rows rakhho, right se match karo."

Q18: Find customers who ordered laptops but never ordered phones.​

SELECT DISTINCT customer_id
FROM orders
WHERE product = 'Laptop'
  AND customer_id NOT IN (
      SELECT customer_id FROM orders WHERE product = 'Phone'
  );

Result: C1, C5

🧠 NOT IN subquery: Finds laptop buyers and excludes anyone who also bought a phone. Alternative: use LEFT JOIN with IS NULL.

Q19: Find the month with the highest total sales.​

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       SUM(amount) AS total_sales
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY total_sales DESC
LIMIT 1;

Result: 2023-01 (₹60,000) or 2023-06 (₹50,600)

Q20: Find orders that were above their region's average.​

SELECT o.order_id, o.product, o.amount, o.region, r.region_avg
FROM orders o
JOIN (
    SELECT region, AVG(amount) AS region_avg
    FROM orders
    GROUP BY region
) r ON o.region = r.region
WHERE o.amount > r.region_avg;

SECTION C: Window Functions & Advanced (Q21–Q30)​

Q21: Rank employees by salary within each department.​

SELECT name, department, salary,
       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank,
       DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;

🧠 RANK vs DENSE_RANK: If two people tie at rank 1, RANK gives 1,1,3 (skips 2). DENSE_RANK gives 1,1,2 (no skip). ROW_NUMBER gives 1,2,3 (no ties).

Q22: Calculate running total of order amounts ordered by date.​

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

Result: Each row shows cumulative sum up to that date.

🧠 Running total = SUM() + OVER(ORDER BY date). One of the most common window function patterns in analytics.

Q23: Find each employee's salary as a percentage of their department total.​

SELECT name, department, salary,
       SUM(salary) OVER (PARTITION BY department) AS dept_total,
       ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY department), 1) AS pct_of_dept
FROM employees;

Q24: Find the difference between each order and the previous order amount.​

SELECT order_id, order_date, amount,
       LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
       amount - LAG(amount) OVER (ORDER BY order_date) AS diff
FROM orders;

🧠 LAG() looks backward, LEAD() looks forward. LAG(amount, 2) looks 2 rows back. Essential for month-over-month analysis.

Q25: Find the top 3 highest-paid employees per department.​

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

🧠 CTE (WITH clause): Creates a temporary named result set. Cleaner than nested subqueries. Interviewers love seeing CTEs — shows you write production-quality SQL.

Q26: Write a query to pivot monthly sales — show months as columns.​

SELECT 
    product,
    SUM(CASE WHEN MONTH(order_date) = 1 THEN amount ELSE 0 END) AS Jan,
    SUM(CASE WHEN MONTH(order_date) = 2 THEN amount ELSE 0 END) AS Feb,
    SUM(CASE WHEN MONTH(order_date) = 3 THEN amount ELSE 0 END) AS Mar,
    SUM(CASE WHEN MONTH(order_date) = 4 THEN amount ELSE 0 END) AS Apr,
    SUM(CASE WHEN MONTH(order_date) = 5 THEN amount ELSE 0 END) AS May,
    SUM(CASE WHEN MONTH(order_date) = 6 THEN amount ELSE 0 END) AS Jun
FROM orders
GROUP BY product;

🧠 CASE inside SUM = manual PIVOT. This works on all SQL databases. Some databases like MS SQL have a native PIVOT keyword.

Q27: Find customers whose total spending is in the top 50% (above median).​

WITH customer_totals AS (
    SELECT customer_id, SUM(amount) AS total_spent
    FROM orders
    GROUP BY customer_id
),
ranked AS (
    SELECT customer_id, total_spent,
           PERCENT_RANK() OVER (ORDER BY total_spent) AS pctl
    FROM customer_totals
)
SELECT customer_id, total_spent
FROM ranked
WHERE pctl >= 0.5;

Q28: Delete duplicate rows keeping only the one with the lowest ID.​

-- Identify duplicates
SELECT name, department, COUNT(*)
FROM employees
GROUP BY name, department
HAVING COUNT(*) > 1;

-- Delete duplicates (keep lowest emp_id)
DELETE e1
FROM employees e1
JOIN employees e2
ON e1.name = e2.name
   AND e1.department = e2.department
   AND e1.emp_id > e2.emp_id;

🧠 Classic interview question. Self-join where we keep the row with the smaller ID and delete others.

Q29: Find the year-over-year growth rate of total sales.​

WITH yearly AS (
    SELECT YEAR(order_date) AS yr,
           SUM(amount) AS total_sales
    FROM orders
    GROUP BY YEAR(order_date)
)
SELECT yr,
       total_sales,
       LAG(total_sales) OVER (ORDER BY yr) AS prev_year,
       ROUND((total_sales - LAG(total_sales) OVER (ORDER BY yr)) * 100.0 /
             LAG(total_sales) OVER (ORDER BY yr), 2) AS yoy_growth_pct
FROM yearly;

🧠 YoY Growth = (Current - Previous) / Previous × 100. LAG() makes this trivial with window functions.

Q30: Create a report showing employee count, avg salary, and the percentage of total company salary per department — in a single query.​

SELECT department,
       COUNT(*) AS headcount,
       ROUND(AVG(salary), 0) AS avg_salary,
       SUM(salary) AS dept_salary,
       ROUND(SUM(salary) * 100.0 / (SELECT SUM(salary) FROM employees), 1) AS pct_of_company
FROM employees
GROUP BY department
ORDER BY pct_of_company DESC;

Result:

departmentheadcountavg_salarydept_salarypct_of_company
Engineering38433325300047.7
Sales35900017700033.4
HR25100010200019.2

🧠 This is a "show-off" query. It combines GROUP BY, aggregate functions, and a scalar subquery in one statement. Demonstrates strong SQL skills in an interview.