SQL Exercise: List the employees along with department name

SQL employee Database: Exercise-52 with Solution

[An editor is available at the bottom of the page to write and execute the scripts.]

52. From the following table, write a SQL query to find employees along with their department details. Return employee ID, employee name, job name, manager ID, hire date, salary, commission, department ID, and department name.

Sample table: employees

Sample table: department

Pictorial Presentation:

Sample Solution:

SELECT e.emp_id,
       e.emp_name,
       e.job_name,
       e.manager_id,
       e.hire_date,
       e.salary,
       e.commission,
       e.dep_id,
       d.dep_name
FROM employees e,
     department d
WHERE e.dep_id = d.dep_id;

Sample Output:

 emp_id | emp_name | job_name  | manager_id | hire_date  | salary  | commission | dep_id | dep_name
--------+----------+-----------+------------+------------+---------+------------+--------+----------
  68319 | KAYLING  | PRESIDENT |            | 1991-11-18 | 6000.00 |            |   1001 | FINANCE
  66928 | BLAZE    | MANAGER   |      68319 | 1991-05-01 | 2750.00 |            |   3001 | MARKETING
  67832 | CLARE    | MANAGER   |      68319 | 1991-06-09 | 2550.00 |            |   1001 | FINANCE
  65646 | JONAS    | MANAGER   |      68319 | 1991-04-02 | 2957.00 |            |   2001 | AUDIT
  67858 | SCARLET  | ANALYST   |      65646 | 1997-04-19 | 3100.00 |            |   2001 | AUDIT
  69062 | FRANK    | ANALYST   |      65646 | 1991-12-03 | 3100.00 |            |   2001 | AUDIT
  63679 | SANDRINE | CLERK     |      69062 | 1990-12-18 |  900.00 |            |   2001 | AUDIT
  64989 | ADELYN   | SALESMAN  |      66928 | 1991-02-20 | 1700.00 |     400.00 |   3001 | MARKETING
  65271 | WADE     | SALESMAN  |      66928 | 1991-02-22 | 1350.00 |     600.00 |   3001 | MARKETING
  66564 | MADDEN   | SALESMAN  |      66928 | 1991-09-28 | 1350.00 |    1500.00 |   3001 | MARKETING
  68454 | TUCKER   | SALESMAN  |      66928 | 1991-09-08 | 1600.00 |       0.00 |   3001 | MARKETING
  68736 | ADNRES   | CLERK     |      67858 | 1997-05-23 | 1200.00 |            |   2001 | AUDIT
  69000 | JULIUS   | CLERK     |      66928 | 1991-12-03 | 1050.00 |            |   3001 | MARKETING
  69324 | MARKER   | CLERK     |      67832 | 1992-01-23 | 1400.00 |            |   1001 | FINANCE
(14 rows)

Explanation:

The said statement in SQL that selects the employee ID, employee name, job name, manager ID, hire date, salary, commission, department ID, and department name for all employees in the employees table who belong to a department that exists in the department table.

An inner join happens to combine the data from the tables employees and department based on the dep_id column, which is common to both tables. This allows the query to match up each employee with their corresponding department based on the department ID.

The WHERE clause checks whether the dep_id column in the employees table matches the dep_id column in the department table.

Using aliases e and d can make the query easier to read and write by shortening the table names and reducing repetition.

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: List all the employees who joined before or after 1991.
Next SQL Exercise: Employees earning 60000 or not working as analysts.