﻿ SQL: Find employees and departments with a given budget

# SQL Exercise: Find employees and departments with a given budget

## SQL JOINS: Exercise-28 with Solution

From the following tables write a SQL query to find the departments with budgets more than Rs. 50000 and display the first name and last name of employees.

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"
-- 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
-- Adding an additional condition to the join, filtering based on 'dpt_allotment' greater than 50000
AND emp_department.dpt_allotment > 50000;
``````

Output of the Query:

```First Name	Last Name
Alan		Snappy
Maria		Foster
Michale		Robbin
Enric		Dosio
Joseph		Dosni
Zanifer		Emily
Kuleswar	Sitaraman
Henrey		Gabriel
Alex		Manuel
George		Mardy
```

Explanation:

The said SQL query is selecting specific columns from the emp_details table and renaming them as 'First Name' and 'Last Name'. It then joins the emp_details table with the emp_department table based on a matching value in the 'emp_dept' column of the emp_details table and the 'dpt_code' column of the emp_department table. The query also includes a filter that only returns the rows where the 'dpt_allotment' column in the emp_department table is greater than 50000.
The INNER JOIN clause is used which is used to only return the rows where there is a match in both tables, it discards the unmatched rows 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: Employee and sanction amount for their department.
Next SQL Exercise: Departments where more than two employees are working.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.

﻿