w3resource

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


Sample table: emp_details


Sample Solution:

SELECT emp_details.emp_fname AS "First Name", emp_lname AS "Last Name"
  FROM emp_details 
    INNER JOIN emp_department
        ON emp_details.emp_dept = emp_department.dpt_code
    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:

Query visualization of Find the first name and last name of employees working for departments with a budget more than Rs. 50000 - Duration

Rows:

Query visualization of Find the first name and last name of employees working for departments with a budget more than Rs. 50000 - Rows

Cost:

Query visualization of Find the first name and last name of employees working for departments with a budget more than Rs. 50000 - 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.



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