w3resource

SQL JOINS on HR Database: Display the country name, city, and number of those 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 those departments where at least 2 employees work. Group the result set on country name and city. Return country name, city, and number of departments.

Sample table: countries


Sample table: locations


Sample table: employees


Sample table: departments


Sample Solution:

SELECT country_name,city, COUNT(department_id)
	FROM countries 
		JOIN locations USING (country_id) 
		JOIN departments USING (location_id) 
WHERE department_id IN 
    (SELECT department_id 
		FROM employees 
	 GROUP BY department_id 
	 HAVING COUNT(department_id)>=2)
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

Pictorial Presentation:

SQL Exercises: Display the country name, city, and number of those departments where at leaste 2 employees are working.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the country name, city, and number of those departments where at leaste 2 employees are working - Duration

Rows:

Query visualization of Display the country name, city, and number of those departments where at leaste 2 employees are working - Rows

Cost:

Query visualization of Display the country name, city, and number of those departments where at leaste 2 employees are working - Cost

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

Previous: From the following table, write a SQL query to find those employees who earn $12000 and above. Return employee ID, starting date, end date, job ID and department ID.
Next: From the following tables, write a SQL query to find the department name, full name (first and last name) of the manager and their city.

What is the difficulty level of this exercise?