The department name and the number of employees working for that department.
SELECT
department_name,
COUNT(*) AS number_of_employees
FROM employees
WHERE salary > 30000
GROUP BY department_name;
What will be the output?
This query will output the following results:
department_name | number_of_employees
IT 3
Finance 3
Marketing 2
The Complete Question
for each department whose average employee salary is more than $30,000, retrieve the department name and the number of employees working for that department.
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
department_name VAR CHAR (255) NOT NULL,
salary INT NOT NULL
);
INSERT INTO employees (employee_id, department_name, salary)
VALUES
(1, 'IT', 40000),
(2, 'IT', 50000),
(3, 'IT', 35000),
(4, 'Sales', 28000),
(5, 'Sales', 25000),
(6, 'Sales', 32000),
(7, 'Marketing', 30000),
(8, 'Marketing', 35000),
(9, 'Marketing', 25000),
(10, 'Finance', 45000),
(11, 'Finance', 40000),
(12, 'Finance', 35000),
(13, 'HR', 25000),
(14, 'HR', 30000),
(15, 'HR', 28000);