﻿ SQL: Display employees including their department

# SQL Exercise: Display employees including their department

## SQL JOINS: Exercise-26 with Solution

From the following tables write a SQL query to display all the data of employees including their department.

Sample table: emp_department

```DPT_CODE DPT_NAME        DPT_ALLOTMENT
-------- --------------- -------------
57 IT                      65000
63 Finance                 15000
47 HR                     240000
27 RD                      55000
89 QC                      75000
```

Sample table: emp_details

``` EMP_IDNO EMP_FNAME       EMP_LNAME         EMP_DEPT
--------- --------------- --------------- ----------
127323 Michale         Robbin                  57
526689 Carlos          Snares                  63
843795 Enric           Dosio                   57
328717 Jhon            Snares                  63
444527 Joseph          Dosni                   47
659831 Zanifer         Emily                   47
847674 Kuleswar        Sitaraman               57
748681 Henrey          Gabriel                 47
555935 Alex            Manuel                  57
539569 George          Mardy                   27
733843 Mario           Saule                   63
631548 Alan            Snappy                  27
839139 Maria           Foster                  57
```

Sample Solution:

``````-- Selecting specific columns with renamed aliases from the result of an inner join between 'emp_details' and 'emp_department' tables
SELECT emp_idno, A.emp_fname AS "First Name", emp_lname AS "Last Name",
B.dpt_name AS "Department", emp_dept, dpt_code, dpt_allotment
-- Specifying the tables to retrieve data from ('emp_details' as 'A' and 'emp_department' as 'B')
FROM emp_details A
-- Performing an inner join based on the equality of 'emp_dept' in 'emp_details' and 'dpt_code' in 'emp_department'
INNER JOIN emp_department B
ON A.emp_dept = B.dpt_code;
``````

Output of the Query:

```emp_idno	First Name	Last Name	Department	emp_dept	dpt_code	dpt_allotment
631548		Alan		Snappy		RD		27		27		55000
839139		Maria		Foster		IT		57		57		65000
127323		Michale		Robbin		IT		57		57		65000
526689		Carlos		Snares		Finance		63		63		15000
843795		Enric		Dosio		IT		57		57		65000
328717		Jhon		Snares		Finance		63		63		15000
444527		Joseph		Dosni		HR		47		47		240000
659831		Zanifer		Emily		HR		47		47		240000
847674		Kuleswar	Sitaraman	IT		57		57		65000
748681		Henrey		Gabriel		HR		47		47		240000
555935		Alex		Manuel		IT		57		57		65000
539569		George		Mardy		RD		27		27		55000
733843		Mario		Saule		Finance		63		63		15000
```

Explanation:

The said SQL query is selecting the employee ID (emp_idno), first name (A.emp_fname) with an alias 'First Name', last name (emp_lname) with an alias 'Last Name', department name (B.dpt_name) with an alias 'Department', emp_dept and department code (dpt_code) and department allotment (dpt_allotment) by joining the emp_details table A and emp_department table B on the emp_dept column of the emp_details table and the dpt_code column of the emp_department table. The query is joining the two tables together and displaying the selected columns from both tables.

## Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Display ID and price of most expensive product.
Next SQL Exercise: Employee and sanction amount for their department.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿