Explain about nested queries in DBMS

A nested query is a query that has another query embedded within it. The embedded query is called a subquery.

A subquery typically appears within the WHERE clause of a query. It can sometimes appear in the FROM clause or HAVING clause.

Example

Let's learn about nested queries with the help of an example.

Find the names of employee who have regno=103

The query is as follows ?

select E.ename from employee E where E.eid IN (select S.eid from salary S where S.regno=103);

Student table

The student table is created as follows ?

create table student(id number(10), name varchar2(20),classID number(10), marks varchar2(20)); Insert into student values(1,'pinky',3,2.4); Insert into student values(2,'bob',3,1.44); Insert into student values(3,'Jam',1,3.24); Insert into student values(4,'lucky',2,2.67); Insert into student values(5,'ram',2,4.56); select * from student;

Output

You will get the following output ?

Id	Name	classID	Marks
1	Pinky	3	2.4
2	Bob	3	1.44
3	Jam	1	3.24
4	Lucky	2	2.67
5	Ram	2	4.56

Teacher table

The teacher table is created as follows ?

Example

Create table teacher(id number(10), name varchar(20), subject varchar2(10), classID number(10), salary number(30)); Insert into teacher values(1,'bhanu','computer',3,5000); Insert into teacher values(2,'rekha','science',1,5000); Insert into teacher values(3,'siri','social',NULL,4500); Insert into teacher values(4,'kittu','mathsr',2,5500); select * from teacher;

Output

You will get the following output ?

Id	Name	Subject	classID	Salary
1	Bhanu	Computer	3	5000
2	Rekha	Science	1	5000
3	Siri	Social	NULL	4500
4	Kittu	Maths	2	5500

Class table

The class table is created as follows ?

Example

Create table class(id number(10), grade number(10), teacherID number(10), noofstudents number(10)); insert into class values(1,8,2,20); insert into class values(2,9,3,40); insert into class values(3,10,1,38); select * from class;

Output

You will get the following output ?

Id	Grade	teacherID	No.ofstudents
1	8	2	20
2	9	3	40
3	10	1	38

Now let's work on nested queries

Example 1

Select AVG(noofstudents) from class where teacherID IN( Select id from teacher Where subject='science' OR subject='maths');

Output

You will get the following output ?

20.0

Example 2

SELECT * FROM student WHERE classID = (    SELECT id    FROM class    WHERE noofstudents = (       SELECT MAX(noofstudents)       FROM class));

Output

You will get the following output ?

4|lucky |2|2.67 5|ram |2|4.56