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
Subqueries in ‘FROM’ clause