AdventureWorks Database: Find the departments that each have more than 15 employees

Last update on December 20 2024 11:38:44 (UTC/GMT +8 hours)

94. From the following tables write a SQL query to return the departments of a company that each have more than 15 employees.

Sample table: humanresources.employee

businessentityid|nationalidnumber|loginid                     |jobtitle                                |birthdate |maritalstatus|gender|hiredate  |salariedflag|vacationhours|sickleavehours|currentflag|rowguid                             |modifieddate           |organizationnode|
----------------+----------------+----------------------------+----------------------------------------+----------+-------------+------+----------+------------+-------------+--------------+-----------+------------------------------------+-----------------------+----------------+
               1|295847284       |adventure-works\ken0        |Chief Executive Officer                 |1969-01-29|S            |M     |2009-01-14|true        |           99|            69|true       |f01251e5-96a3-448d-981e-0f99d789110d|2014-06-30 00:00:00.000|/               |
               2|245797967       |adventure-works\terri0      |Vice President of Engineering           |1971-08-01|S            |F     |2008-01-31|true        |            1|            20|true       |45e8f437-670d-4409-93cb-f9424a40d6ee|2014-06-30 00:00:00.000|/1/             |
               3|509647174       |adventure-works\roberto0    |Engineering Manager                     |1974-11-12|M            |M     |2007-11-11|true        |            2|            21|true       |9bbbfb2c-efbb-4217-9ab7-f97689328841|2014-06-30 00:00:00.000|/1/1/           |
               4|112457891       |adventure-works\rob0        |Senior Tool Designer                    |1974-12-23|S            |M     |2007-12-05|false       |           48|            80|true       |59747955-87b8-443f-8ed4-f8ad3afdf3a9|2014-06-30 00:00:00.000|/1/1/1/         |
               5|695256908       |adventure-works\gail0       |Design Engineer                         |1952-09-27|M            |F     |2008-01-06|true        |            5|            22|true       |ec84ae09-f9b8-4a15-b4a9-6ccbab919b08|2014-06-30 00:00:00.000|/1/1/2/         |
               6|998320692       |adventure-works\jossef0     |Design Engineer                         |1959-03-11|M            |M     |2008-01-24|true        |            6|            23|true       |e39056f1-9cd5-478d-8945-14aca7fbdcdd|2014-06-30 00:00:00.000|/1/1/3/         |
               7|134969118       |adventure-works\dylan0      |Research and Development Manager        |1987-02-24|M            |M     |2009-02-08|true        |           61|            50|true       |4f46deca-ef01-41fd-9829-0adab368e431|2014-06-30 00:00:00.000|/1/1/4/         |
               8|811994146       |adventure-works\diane1      |Research and Development Engineer       |1986-06-05|S            |F     |2008-12-29|true        |           62|            51|true       |31112635-663b-4018-b4a2-a685c0bf48a4|2014-06-30 00:00:00.000|/1/1/4/1/       |
               9|658797903       |adventure-works\gigi0       |Research and Development Engineer       |1979-01-21|M            |F     |2009-01-16|true        |           63|            51|true       |50b6cdc6-7570-47ef-9570-48a64b5f2ecf|2014-06-30 00:00:00.000|/1/1/4/2/       |
              10|879342154       |adventure-works\michael6    |Research and Development Manager        |1984-11-30|M            |M     |2009-05-03|true        |           16|            64|true       |eaa43680-5571-40cb-ab1a-3bf68f04459e|2014-06-30 00:00:00.000|/1/1/4/3/       |
			  -- more --

Click to view Full table

Sample Solution:

-- Selecting the job title and counting employees in each designation
SELECT jobtitle,   
       COUNT(businessentityid) AS EmployeesInDesig  
-- From the employee table in the HumanResources schema
FROM humanresources.employee e  
-- Grouping the results by job title
GROUP BY jobtitle  
-- Filtering the results to include only those with more than 15 employees
HAVING COUNT(businessentityid) > 15;

Explanation:

This SQL query retrieves the job titles and counts the number of employees in each job title designation from the employee table in the HumanResources schema.
The COUNT(businessentityid) function is used to count the number of employees for each job title group.
The results are grouped by the job title using the GROUP BY clause.
The HAVING clause is used to filter the results to include only those job titles with more than 15 employees.

Sample Output:

jobtitle                    |employeesindesig|
----------------------------+----------------+
Production Technician - WC30|              25|
Production Technician - WC60|              26|
Production Technician - WC50|              26|
Production Technician - WC20|              22|
Production Technician - WC10|              17|
Production Technician - WC40|              26|

SQL AdventureWorks Editor:

Practice Online

Contribute your code and comments through Disqus.

Previous: Return maximum, minimum, average salary and total employees for each department.
Next: Find the total products ordered in each specified orders.