30 SQL GROUP BY Questions with Schema and Data
Database Schema and Sample Data
Schema for employees table
Schema:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
job_role VARCHAR(50),
salary DECIMAL(10,2),
age INT,
gender VARCHAR(10),
city VARCHAR(50),
join_date DATE,
employment_type VARCHAR(20),
experience INT
);
Sample Data:
INSERT INTO employees (id, name, department, job_role, salary, age, gender, city, join_date, employment_type,
experience) VALUES
(1, 'Alice', 'IT', 'Developer', 60000, 30, 'Female', 'New York', '2020-06-15', 'Permanent', 5),
(2, 'Bob', 'HR', 'Manager', 80000, 45, 'Male', 'Los Angeles', '2018-03-10', 'Permanent', 10),
(3, 'Charlie', 'IT', 'Analyst', 55000, 28, 'Male', 'Chicago', '2019-09-25', 'Permanent', 4),
(4, 'David', 'Finance', 'Accountant', 70000, 35, 'Male', 'New York', '2021-11-01', 'Contract', 7),
(5, 'Eve', 'HR', 'Recruiter', 50000, 29, 'Female', 'Los Angeles', '2022-01-20', 'Permanent', 3);
Schema for orders table
Schema:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
order_date DATE,
amount DECIMAL(10,2)
);
Sample Data:
INSERT INTO orders (order_id, customer_id, product_id, order_date, amount) VALUES
(101, 1, 201, '2023-02-10', 250.00),
(102, 2, 202, '2023-03-15', 450.00),
(103, 1, 203, '2023-04-20', 300.00),
(104, 3, 204, '2023-05-10', 150.00),
(105, 2, 201, '2023-06-18', 350.00);
SQL Questions and Answers
1. Retrieve the total salary paid in each department from the employees table.
SQL Query:
SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department;
2. Count the number of employees in each job role.
SQL Query:
SELECT job_role, COUNT(*) AS employee_count FROM employees GROUP BY job_role;
3. Find the average age of employees in each department.
SQL Query:
SELECT department, AVG(age) AS average_age FROM employees GROUP BY department;
4. Retrieve the total salary for each department and job role combination.
SQL Query:
SELECT department, job_role, SUM(salary) AS total_salary FROM employees GROUP BY department, job_role;
5. Count employees by department and gender.
SQL Query:
SELECT department, gender, COUNT(*) AS employee_count FROM employees GROUP BY department, gender;
6. Find the highest salary in each city and department.
SQL Query:
SELECT city, department, MAX(salary) AS highest_salary FROM employees GROUP BY city, department;
7. Find departments where the total salary expense is greater than 100,000.
SQL Query:
SELECT department, SUM(salary) AS total_salary FROM employees GROUP BY department HAVING SUM(salary) >
100000;
8. Retrieve job roles with an average salary greater than 50,000.
SQL Query:
SELECT job_role, AVG(salary) AS avg_salary FROM employees GROUP BY job_role HAVING AVG(salary) > 50000;
9. Find cities where more than 10 employees are working.
SQL Query:
SELECT city, COUNT(*) AS employee_count FROM employees GROUP BY city HAVING COUNT(*) > 10;
10. Find the department with the maximum number of employees.
SQL Query:
SELECT department, COUNT(*) AS employee_count FROM employees GROUP BY department ORDER BY
employee_count DESC LIMIT 1;
11. Find the minimum salary paid in each department.
SQL Query:
SELECT department, MIN(salary) AS min_salary FROM employees GROUP BY department;
12. Find the sum of salaries for employees who joined in each year.
SQL Query:
SELECT YEAR(join_date) AS joining_year, SUM(salary) AS total_salary FROM employees GROUP BY
YEAR(join_date);
13. Find the number of employees who joined each month.
SQL Query:
SELECT MONTH(join_date) AS month, COUNT(*) AS employee_count FROM employees GROUP BY
MONTH(join_date);
14. Find the total salary paid in each year and month.
SQL Query:
SELECT YEAR(join_date) AS year, MONTH(join_date) AS month, SUM(salary) AS total_salary FROM employees
GROUP BY YEAR(join_date), MONTH(join_date);
15. Find the average salary of employees who joined in the last 5 years.
SQL Query:
SELECT YEAR(join_date) AS year, AVG(salary) AS avg_salary FROM employees WHERE join_date >=
DATE_SUB(CURDATE(), INTERVAL 5 YEAR) GROUP BY YEAR(join_date);
16. Count employees based on salary range (low, medium, high).
SQL Query:
SELECT CASE
WHEN salary < 40000 THEN 'Low'
WHEN salary BETWEEN 40000 AND 80000 THEN 'Medium'
ELSE 'High' END AS salary_range, COUNT(*) AS employee_count
FROM employees GROUP BY salary_range;
17. Calculate the total salary for permanent and contract employees.
SQL Query:
SELECT CASE
WHEN employment_type = 'Permanent' THEN 'Permanent' ELSE 'Contract' END AS employee_type,
SUM(salary) AS total_salary FROM employees GROUP BY employee_type;
18. Find the total number of employees categorized by experience level.
SQL Query:
SELECT CASE
WHEN experience < 3 THEN 'Junior'
WHEN experience BETWEEN 3 AND 7 THEN 'Mid-Level'
ELSE 'Senior' END AS experience_level,
COUNT(*) AS employee_count FROM employees GROUP BY experience_level;
19. Find the total salary paid by each department (joining employees and departments tables).
SQL Query:
SELECT d.department_name, SUM(e.salary) AS total_salary FROM employees e JOIN departments d ON
e.department_id = d.department_id GROUP BY d.department_name;
20. Count orders placed by each customer from orders and customers tables.
SQL Query:
SELECT c.customer_name, COUNT(o.order_id) AS total_orders FROM customers c JOIN orders o ON c.customer_id =
o.customer_id GROUP BY c.customer_name;
21. Find the total sales made by each salesperson.
SQL Query:
SELECT s.salesperson_name, SUM(o.amount) AS total_sales FROM salespersons s JOIN orders o ON
s.salesperson_id = o.salesperson_id GROUP BY s.salesperson_name;
22. Find employees who have the same salary.
SQL Query:
SELECT salary, COUNT(*) AS employee_count FROM employees GROUP BY salary HAVING COUNT(*) > 1;
23. Find the top 3 highest average salaries by department.
SQL Query:
SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department ORDER BY avg_salary
DESC LIMIT 3;
24. Find products that have been ordered more than 100 times.
SQL Query:
SELECT product_id, COUNT(*) AS order_count FROM orders GROUP BY product_id HAVING COUNT(*) > 100;
25. Find customers who have placed multiple orders in a single day.
SQL Query:
SELECT customer_id, order_date, COUNT(*) AS order_count FROM orders GROUP BY customer_id, order_date
HAVING COUNT(*) > 1;
26. Find employees who have received a salary increase more than once.
SQL Query:
SELECT employee_id, COUNT(*) AS salary_changes FROM salary_history GROUP BY employee_id HAVING
COUNT(*) > 1;
27. Find departments where more than 50% of employees are seniors.
SQL Query:
SELECT department FROM employees GROUP BY department HAVING SUM(CASE WHEN experience > 5 THEN 1
ELSE 0 END) > COUNT(*) / 2;
28. Find the departments where the average salary is greater than the overall company average
salary.
SQL Query:
SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) >
(SELECT AVG(salary) FROM employees);
29. Find the number of distinct job roles in each department.
SQL Query:
SELECT department, COUNT(DISTINCT job_role) AS distinct_job_roles FROM employees GROUP BY department;
30. Find the ranking of departments based on total salary expense.
SQL Query:
SELECT department, SUM(salary) AS total_salary, RANK() OVER (ORDER BY SUM(salary) DESC) AS
department_rank FROM employees GROUP BY department;
