w3resource

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:

SQL exercises on employee Database: List the employees along with department name

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 Expression: List the employees along with department name.

Relational Algebra Tree:

Relational Algebra Tree: List the employees along with department name.

Practice Online


Sample Database: employee

employee database structure

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.



Follow us on Facebook and Twitter for latest update.

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