SQL Exercise: Employee and sanction amount for their department

SQL JOINS: Exercise-27 with Solution

From the following tables write a SQL query to display the first and last names of each employee, as well as the department name and sanction amount.

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 and renaming them for clarity
SELECT emp_details.emp_fname AS "First Name", emp_lname AS "Last Name", 
       emp_department.dpt_name AS "Department", 
       dpt_allotment AS "Amount Allotted"
-- Specifying the tables to retrieve data from ('emp_details' and 'emp_department')
FROM emp_details 
-- Performing an inner join based on the equality of 'emp_dept' in 'emp_details' and 'dpt_code' in 'emp_department'
INNER JOIN emp_department
ON emp_details.emp_dept = emp_department.dpt_code;

Output of the Query:

First Name	Last Name	Department	Amount Allotted
Alan		Snappy		RD		55000
Maria		Foster		IT		65000
Michale		Robbin		IT		65000
Carlos		Snares		Finance		15000
Enric		Dosio		IT		65000
Jhon		Snares		Finance		15000
Joseph		Dosni		HR		240000
Zanifer		Emily		HR		240000
Kuleswar	Sitaraman	IT		65000
Henrey		Gabriel		HR		240000
Alex		Manuel		IT		65000
George		Mardy		RD		55000
Mario		Saule		Finance		15000

Explanation:

The said SQL query is selecting the employee's first name (emp_details.emp_fname) with an alias 'First Name', last name (emp_lname) with an alias 'Last Name', department name (emp_department.dpt_name) with an alias 'Department', and department allotment (dpt_allotment) with an alias 'Amount Allotted' by joining the emp_details table and emp_department table 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.

Practice Online

Query Visualization:

Duration:

Rows:

Cost:

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

Previous SQL Exercise: Display employees including their department.
Next SQL Exercise: Find employees and departments with a given budget.