/    /  DBMS – Subqueries in ‘FROM’ clause

Subqueries in ‘FROM’ clause

The FROM clause lists the table and joins needed for an SQL statement.

Let us understand how it works with an example.

   Consider the following relation R4

R4 : FACULTY-1

———————————————————— 

FNo FName              DNo Qual           Salary

————————————————————

522 Ria  21 Ph.D. 35000

524 Priya  22 MTech 30000

525 Sia  22 MTech 42000

527 Maggie 23 MTech 28000

530 Alex 23 MTech 32000

533 Ben 24 Ph.D.          33000

535 Becca  24 Ph.D.          32000

537 Justin  25 MTech 26000

539 Pat  25 MTech 24000

540 Jack 25 MTech 34000

————————————————————

 

Example-1 : Find the DNo where the average salary of the employees  of that department is greater than 33000.

 

Sub Query : SELECT DNo

   FROM(Select DNo, avg(Salary) as avg-sal

 FROM FACULTY-1

 GROUP BY DNo)

   WHERE avg-sal>33000 ;

  

 

Output :    DNo

   21

   22

 

Example-2 : Find the DNo, and avg-sal of all Ph.D. Faculty, department wise, whose average salary is more than 33000.

 

 

Sub Query :    SELECT DNo, avg-sal

   FROM (SELECT DNo, avg(Salary) as avg-sal

   FROM FACULTY-1

   GROUP BY DNo

   WHERE Qual = ‘Ph.D.’ )

   WHERE avg-sal>33000 ;

  

Output :    DNo  avg-sal

   21 35000

 

Reference

Subqueries in ‘FROM’ clause