SQL Exercise: Departments where at least 2 employees are working

SQL JOINS on HR Database: Exercise-21 with Solution

21. From the following tables, write a SQL query to find out which departments have at least two employees. Group the result set on country name and city. Return country name, city, and number.

Sample table: countries

Sample table: locations

Sample table: employees

Sample table: departments

Sample Solution:

-- Selecting specific columns (country_name, city, COUNT(department_id)) from the 'countries' table
SELECT country_name, city, COUNT(department_id)

-- Performing an INNER JOIN between the 'countries' table and the 'locations' table using the common column 'country_id'
FROM countries 

JOIN locations USING (country_id) 

-- Performing another INNER JOIN between the result set and the 'departments' table using the common column 'location_id'
JOIN departments USING (location_id) 

-- Filtering rows based on the condition that 'department_id' is in the result set of a subquery that counts departments with more than or equal to 2 occurrences
WHERE department_id IN 
    (SELECT department_id 
     FROM employees 
     GROUP BY department_id 
     HAVING COUNT(department_id) >= 2)

-- Grouping the result set by 'country_name' and 'city'
GROUP BY country_name, city;

Sample Output:

country_name			city			count
United States of America 	South San Francisco	1
Canada				Toronto			1
United States of America	Seattle			4
United States of America	Southlake		1

Code Explanation:

The said query in SQL which will return a list of countries and cities where there are at least two departments with employees, along with the number of departments in each city.
The first step of the query is to choose the country name, the city, and the number of department IDs to be counted. The countries and locations tables are then joined using country IDs, and the departments table is joined using location IDs.
Using the WHERE clause, you can filter the results to only include departments with a subquery ID. This subquery selects department IDs from the employees table and groups them by department ID. The HAVING clause ensures that only department IDs with a count of 2 or more are selected.
The query groups the results by country name and city.

Visual Presentation:

Alternative Solutions:

Using INNER JOINs with Explicit Column Names:

SELECT countries.country_name, locations.city, COUNT(departments.department_id)
FROM countries
JOIN locations ON countries.country_id = locations.country_id
JOIN departments ON locations.location_id = departments.location_id
WHERE departments.department_id IN (
    SELECT department_id 
    FROM employees 
    GROUP BY department_id 
    HAVING COUNT(department_id) >= 2
)
GROUP BY countries.country_name, locations.city;

Explanation:

This query uses INNER JOINs with explicitly specifies column names. It performs INNER JOINs based on matching keys and applies a WHERE clause to filter departments with at least two employees. It then counts the number of departments for each country and city.

Using NATURAL JOIN with WHERE Clause:

SELECT country_name, city, COUNT(department_id)
FROM countries
NATURAL JOIN locations
NATURAL JOIN departments
WHERE department_id IN (
    SELECT department_id 
    FROM employees 
    GROUP BY department_id 
    HAVING COUNT(department_id) >= 2
)
GROUP BY country_name, city;

Explanation:

This query uses NATURAL JOINs, which automatically join tables based on columns with the same name. It combines the three tables and applies a WHERE clause to filter departments with at least two employees. It then counts the number of departments for each country and city.

Using INNER JOIN with WHERE Clause and Explicit Column Names:

SELECT countries.country_name, locations.city, COUNT(departments.department_id)
FROM countries
JOIN locations ON countries.country_id = locations.country_id
JOIN departments ON locations.location_id = departments.location_id
WHERE departments.department_id IN (
    SELECT department_id 
    FROM employees 
    GROUP BY department_id 
    HAVING COUNT(department_id) >= 2
)
GROUP BY countries.country_name, locations.city;

Explanation:

This query uses uses INNER JOINs and explicitly specifies column names. It applies a WHERE clause to filter departments with at least two employees and counts the number of departments for each country and city.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Employees who earn a salary over 12000.
Next SQL Exercise: Display the department, manager name, and their city.