SQL Exercise: List id, name, department id, location of all employees
SQL employee Database: Exercise-93 with Solution
[An editor is available at the bottom of the page to write and execute the scripts.]
93. From the following table, write a SQL query to list the employee id, name, department id, location of all the employees.
Sample table: employees
Sample table: department
Sample Solution:
SELECT e.emp_id,
e.emp_name,
e.dep_id,
d.dep_location
FROM employees e,
department d
WHERE e.dep_id = d.dep_id ;
Sample Output:
emp_id | emp_name | dep_id | dep_location --------+----------+--------+-------------- 68319 | KAYLING | 1001 | SYDNEY 66928 | BLAZE | 3001 | PERTH 67832 | CLARE | 1001 | SYDNEY 65646 | JONAS | 2001 | MELBOURNE 67858 | SCARLET | 2001 | MELBOURNE 69062 | FRANK | 2001 | MELBOURNE 63679 | SANDRINE | 2001 | MELBOURNE 64989 | ADELYN | 3001 | PERTH 65271 | WADE | 3001 | PERTH 66564 | MADDEN | 3001 | PERTH 68454 | TUCKER | 3001 | PERTH 68736 | ADNRES | 2001 | MELBOURNE 69000 | JULIUS | 3001 | PERTH 69324 | MARKER | 1001 | SYDNEY (14 rows)
Explanation:
The said query in SQL that selects the emp_id, emp_name, dep_id from the employees table and the dep_location from the department table. The query joins the two tables on the dep_id column where they have the same values.
Relational Algebra Expression:

Relational Algebra Tree:

Practice Online
Sample Database: employee

Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous SQL Exercise: Compute the total salary for each job in 1991.
Next SQL Exercise: List employee id of all the departments 1001 and 2001.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
SQL: Tips of the Day
Concatenate strings of a string field in a PostgreSQL 'group by' query:
Input:
ID COMPANY_ID EMPLOYEE 1 1 Anna 2 1 Bill 3 2 Carol 4 2 Dave
SELECT company_id, string_agg(employee, ', ') FROM mytable GROUP BY company_id;
Output:
COMPANY_ID EMPLOYEE 1 Anna, Bill 2 Carol, Dave
Database: PostgreSQL
Ref: https://bit.ly/2XTiRjq
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook