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 full name of employee with ID and present country where (s)he is working

SQL JOINS on HR Database: Exercise-27 with Solution

27. From the following tables, write a SQL query to find the full name (first and last name) of the employee with ID and name of the country presently where he/she is working.

Sample table: countries


Sample table: locations


Sample table: employees


Sample table: departments


Sample Solution: 

SELECT first_name || ' ' || last_name 
AS Employee_name, employee_id, country_name 
FROM employees 
JOIN departments 
USING(department_id) 
JOIN locations 
USING( location_id) 
JOIN countries 
USING ( country_id);

Sample Output:

employee_name    |employee_id|country_name            |
-----------------|-----------|------------------------|
Steven King      |        100|United States of America|
Neena Kochhar    |        101|United States of America|
Lex De Haan      |        102|United States of America|
Alexander Hunold |        103|United States of America|
Bruce Ernst      |        104|United States of America|
.........

Practice Online


HR database model

Query Visualization:

Duration:

Query visualization of Display the full name of employee with ID and present country where (s)he is working - Duration

Rows:

Query visualization of Display the full name of employee with ID and present country where (s)he is working - Rows

Cost:

Query visualization of Display the full name of employee with ID and present country where (s)he is working - 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 find the department name, department ID, and number of employees in each department.
Next: SQL UNION Exercises Home

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