SQL Exercise: Find employees and departments with a given budget
28. High Budget Departments Employee List
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.
Practice Online
Query Visualization:
Duration:
Rows:
Cost:
For more Practice: Solve these Related Problems:
- Write a SQL query to join emp_department and emp_details, filter departments with DPT_ALLOTMENT > 50000, and display EMP_FNAME and EMP_LNAME.
- Write a SQL query to list the first and last names of employees who work in departments with budgets exceeding 50000 using an INNER JOIN.
- Write a SQL query to retrieve employee names from high-budget departments by filtering emp_department allotments in the WHERE clause.
- Write a SQL query to output employee first and last names for departments where DPT_ALLOTMENT is more than 50000, ensuring the join condition is met.
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.