65.7k views
2 votes
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.

User Balexander
by
7.5k points

1 Answer

2 votes

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);

User Ndrw
by
7.6k points

No related questions found