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