w3resource
SQL exercises

SQL JOINS on HR Database: Display the country name, city, and number of those departments where at leaste 2 employees are working

SQL JOINS on HR Database: Exercise-21 with Solution

21. Write a query in SQL to display the country name, city, and number of those departments where at least 2 employees are working.

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

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: Write a query in SQL to display the details of jobs which was done by any of the employees who is presently earning a salary on and above 12000.
Next: Write a query in SQL to display the department name, full name (first and last name) of manager, and their city.

What is the difficulty level of this exercise?



New Content: Composer: Dependency manager for PHP, R Programming