SQL Exercise: Display the employee with ID and present country

SQL JOINS on HR Database: Exercise-27 with Solution

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

Sample table: countries

Sample table: locations

Sample table: employees

Sample table: departments

Sample Solution:

-- Selecting specific columns (first_name || ' ' || last_name AS Employee_name, employee_id, country_name) from the 'employees' table
SELECT first_name || ' ' || last_name AS Employee_name, employee_id, country_name 

-- Performing joins between the 'employees', 'departments', 'locations', and 'countries' tables using the common columns specified by the USING clause
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|
.........

Code Explanation:

The said query in SQL which will return a list of employee names, employee IDs, and the name of the country where their department is located, based on data from the employees, departments, locations, and countries tables.
As a result of the JOIN clause, the employees table is joined with the departments table using the department ID, and then the locations table is joined with the locations ID, and finally the countries table is joined with the countries ID.

Alternative Solutions:

Using Explicit Joins:

SELECT CONCAT(e.first_name, ' ', e.last_name) AS Employee_name, e.employee_id, c.country_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
JOIN countries c ON l.country_id = c.country_id;

Explanation:

This SQL query retrieves information about employees, including their full name, employee ID, and the name of the country where they are located. It achieves this by joining four tables: 'employees', 'departments', 'locations', and 'countries'. The CONCAT function combines the first name and last name of each employee, and aliases it as "Employee_name". Finally, the query ensures that the department, location, and country IDs match across the tables.

Using CROSS JOIN:

SELECT CONCAT(e.first_name, ' ', e.last_name) AS Employee_name, e.employee_id, c.country_name
FROM employees e
CROSS JOIN departments d
CROSS JOIN locations l
CROSS JOIN countries c
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id;

Explanation:

This SQL query performs a cross join operation across the 'employees', 'departments', 'locations', and 'countries' tables. It then applies filtering conditions to ensure that the department, location, and country IDs match appropriately. The CONCAT function is used to combine the first name and last name of each employee, and the result is aliased as "Employee_name". This query essentially produces a Cartesian product of all the records in the mentioned tables and then filters them based on the specified conditions.

Using WHERE Clause:

SELECT CONCAT(e.first_name, ' ', e.last_name) AS Employee_name, e.employee_id, c.country_name
FROM employees e, departments d, locations l, countries c
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id
AND l.country_id = c.country_id;

Explanation:

This SQL query selects records from the 'employees', 'departments', 'locations', and 'countries' tables. It combines the first name and last name of each employee using the CONCAT function, aliasing the result as "Employee_name". The query then applies conditions in the WHERE clause to ensure that the department, location, and country IDs match appropriately. This effectively performs an implicit inner join across the tables based on the specified conditions.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Number of employees in each of the department.
Next SQL Exercise: SQL UNION Exercises Home