Top SQL Queries for Practice (With Short Answers) - Aggregate Queries (16–30)

 

Aggregate Queries (16–30)

16. Count total employees

SELECT COUNT(*) FROM Employee;

17. Count employees in HR

SELECT COUNT(*) FROM Employee WHERE department = 'HR';

18. Find total salary payout

SELECT SUM(salary) FROM Employee;

19. Find average salary

SELECT AVG(salary) FROM Employee;

20. Find highest salary

SELECT MAX(salary) FROM Employee;

21. Find lowest salary

SELECT MIN(salary) FROM Employee;

22. Count employees by department

SELECT department, COUNT(*) FROM Employee GROUP BY department;

23. Find avg salary by department

SELECT department, AVG(salary) FROM Employee GROUP BY department;

24. Find highest salary by department

SELECT department, MAX(salary) FROM Employee GROUP BY department;

25. Find departments with more than 5 employees

SELECT department, COUNT(*) 
FROM Employee
GROUP BY department
HAVING COUNT(*) > 5;

26. Find departments with avg salary > 60000

SELECT department, AVG(salary)
FROM Employee
GROUP BY department
HAVING AVG(salary) > 60000;

27. Count distinct departments

SELECT COUNT(DISTINCT department) FROM Employee;

28. Find total salary by department

SELECT department, SUM(salary) FROM Employee GROUP BY department;

29. Find total employees in each location

SELECT location, COUNT(*) FROM Employee GROUP BY location;

30. Find max salary where department = IT

SELECT MAX(salary) FROM Employee WHERE department = 'IT';

---

SQL Scenario-Based Interview Questions ( 85 -100)

 

86. How do you find least-selling product?

Group by product and sort by total sales asc.

---

87. How do you calculate average order value?

Total revenue ÷ total orders.

---

88. How do you find repeat customers?

Find customers with more than one order.

---

89. How do you identify one-time customers?

Find customers with exactly one order.

---

90. How do you segment customers by spending?

Use CASE on total spend ranges.

---

91. How do you find highest sales month?

Group by month and sort by total sales desc.

---

92. How do you compare YoY sales?

Compare same period current year vs previous year.

---

93. How do you detect sales drop?

Compare current period sales with previous period using LAG().

---

94. How do you find seasonal trends?

Aggregate sales by month/quarter across years.

---

95. How do you calculate contribution % by category?

Category sales ÷ total sales * 100.

---

96. How do you rank products by sales?

Use RANK() on sales descending.

---

97. How do you find top customer per region?

Use ROW_NUMBER() partitioned by region ordered by sales desc.

---

98. How do you identify null-heavy columns?

Profile columns using NULL counts.

---

99. How do you perform data quality check in SQL?

Validate nulls, duplicates, formats, and referential integrity.

---

100. How do you explain SQL approach in interview?

Explain logic first, then SQL method, then optimization approach.

Aggregate Functions & Grouping (51–65)

 

Aggregate Functions & Grouping (51–65)

51. What is an aggregate function?
    It performs calculation on multiple rows and returns one result.
52. Common aggregate functions?
    COUNT, SUM, AVG, MIN, MAX.
53. What does COUNT(*) do?
    Counts all rows including NULLs.
54. Difference between COUNT(*) and COUNT(column)?
    COUNT(column) ignores NULLs.
55. What is GROUP BY?
    Groups rows with same values for aggregation.
56. What is HAVING?
    Filters grouped data.
57. Can we use WHERE with aggregate?
    No, use HAVING.
58. Find total salary by department.
    SELECT dept, SUM(salary) FROM emp GROUP BY dept;
59. Find departments with more than 5 employees.
    Use GROUP BY dept HAVING COUNT(*) > 5.
60. What is AVG?
    Returns average value.
61. What is MAX?
    Returns highest value.
62. What is MIN?
    Returns lowest value.
63. Can GROUP BY be used with multiple columns?
    Yes.
64. What is the order of execution?
    FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
65. Can we use alias in GROUP BY?
    Usually no (depends on DB).

Bhagavad Gita Wisdom #shorts

https://www.youtube.com/playlist?list=PLQM-BpTd9ZSumxwKgJjuJjlx2OcP_W516