Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

SQL JOINS on HR Database: Display the name of the country, city, and the departments which are running there

SQL JOINS on HR Database: Exercise-17 with Solution

17. From the following table, write a SQL query to find the name of the country, city, and departments, which are running there.

Sample table: countries


Sample table: locations


Sample table: departments


Sample Solution: 

SELECT country_name,city, department_name 
	FROM countries 
		JOIN locations USING (country_id) 
			JOIN departments USING (location_id);

Sample Output:

country_name			city		department_name
Canada				Toronto		Marketing
Germany				Munich		Public Relations
United Kingdom			London		Human Resources
United States of America	Seattle		Payroll
United States of America	Seattle		Recruiting
United States of America	Seattle		Retail Sales
United States of America	Seattle		Government Sales
United States of America	Seattle		IT Helpdesk
United States of America	Seattle		NOC
United States of America	Seattle		IT Support
United States of America	Seattle		Operations
United States of America	Seattle		Contracting
United States of America	Seattle		Construction
United States of America	Seattle		Manufacturing
United States of America	Seattle		Benefits
United States of America	Seattle		Shareholder Services
United States of America	Seattle		Control And Credit
United States of America	Seattle		Corporate Tax
United States of America	Seattle		Treasury
United States of America	Seattle		Accounting
United States of America	Seattle		Finance
United States of America	Seattle		Executive
United States of America	Southlake	IT
United States of America	South San Francisco Shipping
United States of America	Seattle		Purchasing
United States of America	Seattle		Administration

Relational Algebra Expression:

Relational Algebra Expression: Display the name of the country, city, and the departments which are running there.

Relational Algebra Tree:

Relational Algebra Tree: Display the name of the country, city, and the departments which are running there.

Pictorial Presentation:

SQL Exercises: Display the name of the country, city, and the departments which are running there.

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the name of the country, city, and the departments which are running there - Duration

Rows:

Query visualization of Display the name of the country, city, and the departments which are running there - Rows

Cost:

Query visualization of Display the name of the country, city, and the departments which are running there - Cost

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

Previous: From the following tables, write a SQL query to compute the difference between maximum salary and salary of all the employees who works the department of ID 80. Return job title, employee name and salary difference.
Next: From the following tables, write a SQL query to find the department name and the full name (first and last name) of the manager.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3