Please note, this is a STATIC archive of website www.w3resource.com from 19 Jul 2022, cach3.com does not collect or store any user information, there is no "phishing" involved.
w3resource

SQL Subquery Exercises: Display the first name and last name of employees working for the department which allotment amount is more than Rs.50000

SQL SUBQUERY: Exercise-36 with Solution

36. From the following tables write a SQL query to find those employees who work for the department where the departmental allotment amount is more than Rs. 50000. Return emp_fname and emp_lname.

Sample table: emp_department


Sample table: emp_details


Sample Solution: 

SELECT emp_fname, emp_lname 
 FROM emp_details
  WHERE emp_dept IN
  (SELECT dpt_code 
     FROM emp_department 
       WHERE dpt_allotment > 50000);

Output of the Query:

emp_fname	emp_lname
Alan		Snappy
Maria		Foster
Michale		Robbin
Enric		Dosio
Joseph		Dosni
Zanifer		Emily
Kuleswar	Sitaraman
Henrey		Gabriel
Alex		Manuel
George		Mardy

Pictorial Presentation:

SQL Subqueries Inventory Exercises: Display the first name and last name of employees working for the department which allotment amount is more than Rs.50000.

Practice Online


Sample Database:

Model Database

Query Visualization:

Duration:

Query visualization of Display the first name and last name of employees working for the department which allotment amount is more than Rs.50000 - Duration

Rows:

Query visualization of Display the first name and last name of employees working for the department which allotment amount is more than Rs.50000 - Rows

Cost:

Query visualization of Display the first name and last name of employees working for the department which allotment amount is more than Rs.50000 - Cost

Contribute your code and comments through Disqus.

Previous: From the following tables, write a SQL query to find the employees who work in department 89 or 63. Return emp_idno, emp_fname, emp_lname and emp_dept.
Next: From the following tables, write a SQL query to find the departments where the sanction amount is higher than the average sanction amount of all the departments. Return dpt_code, dpt_name and dpt_allotment.

What is the difficulty level of this exercise?

Test your Programming skills with w3resource's quiz.



SQL: Tips of the Day

SQL Server SELECT into existing table. 

INSERT INTO dbo.TABLETWO
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

This assumes there's only two columns in dbo.TABLETWO - you need to specify the columns otherwise:

INSERT INTO dbo.TABLETWO
  (col1, col2)
SELECT col1, col2
  FROM dbo.TABLEONE
 WHERE col3 LIKE @search_key

Database: SQL Server

Ref: https://bit.ly/3y6tpA3