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_details
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
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.
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.
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;
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
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