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 ?

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 ?

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 ?

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