﻿ SQL: List the employees along with department name

# 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.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿

## SQL: Tips of the Day

Grouped LIMIT in PostgreSQL: Show the first N rows for each group?

```db=# SELECT * FROM xxx;
id | section_id | name
----+------------+------
1 |          1 | A
2 |          1 | B
3 |          1 | C
4 |          1 | D
5 |          2 | E
6 |          2 | F
7 |          3 | G
8 |          2 | H
(8 rows)
```

I need the first 2 rows (ordered by name) for each section_id, i.e. a result similar to:

```id | section_id | name
----+------------+------
1 |          1 | A
2 |          1 | B
5 |          2 | E
6 |          2 | F
7 |          3 | G
(5 rows)
```

PostgreSQL v9.3 you can do a lateral join

```select distinct t_outer.section_id, t_top.id, t_top.name from t t_outer
join lateral (
select * from t t_inner
where t_inner.section_id = t_outer.section_id
order by t_inner.name
limit 2
) t_top on true
order by t_outer.section_id;
```

Database: PostgreSQL

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

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