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 ?

<span class="pln">create table student</span><span class="pun">(</span><span class="pln">id number</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> name varchar2</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln">classID number</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> marks varchar2</span><span class="pun">(</span><span class="lit">20</span><span class="pun">));</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> student values</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="str">'pinky'</span><span class="pun">,</span><span class="lit">3</span><span class="pun">,</span><span class="lit">2.4</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> student values</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="str">'bob'</span><span class="pun">,</span><span class="lit">3</span><span class="pun">,</span><span class="lit">1.44</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> student values</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="str">'Jam'</span><span class="pun">,</span><span class="lit">1</span><span class="pun">,</span><span class="lit">3.24</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> student values</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="str">'lucky'</span><span class="pun">,</span><span class="lit">2</span><span class="pun">,</span><span class="lit">2.67</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> student values</span><span class="pun">(</span><span class="lit">5</span><span class="pun">,</span><span class="str">'ram'</span><span class="pun">,</span><span class="lit">2</span><span class="pun">,</span><span class="lit">4.56</span><span class="pun">);</span><span class="pln">
</span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> student</span><span class="pun">;</span>

Output

You will get the following output ?

Teacher table

The teacher table is created as follows ?

Example

<span class="typ">Create</span><span class="pln"> table teacher</span><span class="pun">(</span><span class="pln">id number</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> name varchar</span><span class="pun">(</span><span class="lit">20</span><span class="pun">),</span><span class="pln"> subject varchar2</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> classID number</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> salary number</span><span class="pun">(</span><span class="lit">30</span><span class="pun">));</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> teacher values</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,?</span><span class="pln">bhanu</span><span class="pun">?,?</span><span class="pln">computer</span><span class="pun">?,</span><span class="lit">3</span><span class="pun">,</span><span class="lit">5000</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> teacher values</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="str">'rekha'</span><span class="pun">,</span><span class="str">'science'</span><span class="pun">,</span><span class="lit">1</span><span class="pun">,</span><span class="lit">5000</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> teacher values</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="str">'siri'</span><span class="pun">,</span><span class="str">'social'</span><span class="pun">,</span><span class="pln">NULL</span><span class="pun">,</span><span class="lit">4500</span><span class="pun">);</span><span class="pln">
</span><span class="typ">Insert</span><span class="pln"> </span><span class="kwd">into</span><span class="pln"> teacher values</span><span class="pun">(</span><span class="lit">4</span><span class="pun">,</span><span class="str">'kittu'</span><span class="pun">,</span><span class="str">'mathsr'</span><span class="pun">,</span><span class="lit">2</span><span class="pun">,</span><span class="lit">5500</span><span class="pun">);</span><span class="pln">
</span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> teacher</span><span class="pun">;</span>

Output

You will get the following output ?

Class table

The class table is created as follows ?

Example

<span class="typ">Create</span><span class="pln"> table </span><span class="kwd">class</span><span class="pun">(</span><span class="pln">id number</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> grade number</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> teacherID number</span><span class="pun">(</span><span class="lit">10</span><span class="pun">),</span><span class="pln"> noofstudents number</span><span class="pun">(</span><span class="lit">10</span><span class="pun">));</span><span class="pln">
insert </span><span class="kwd">into</span><span class="pln"> </span><span class="kwd">class</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">1</span><span class="pun">,</span><span class="lit">8</span><span class="pun">,</span><span class="lit">2</span><span class="pun">,</span><span class="lit">20</span><span class="pun">);</span><span class="pln">
insert </span><span class="kwd">into</span><span class="pln"> </span><span class="kwd">class</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">2</span><span class="pun">,</span><span class="lit">9</span><span class="pun">,</span><span class="lit">3</span><span class="pun">,</span><span class="lit">40</span><span class="pun">);</span><span class="pln">
insert </span><span class="kwd">into</span><span class="pln"> </span><span class="kwd">class</span><span class="pln"> values</span><span class="pun">(</span><span class="lit">3</span><span class="pun">,</span><span class="lit">10</span><span class="pun">,</span><span class="lit">1</span><span class="pun">,</span><span class="lit">38</span><span class="pun">);</span><span class="pln">
</span><span class="kwd">select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="kwd">class</span><span class="pun">;</span>

Output

You will get the following output ?

Now let?s work on nested queries

Example 1

<span class="typ">Select</span><span class="pln"> AVG</span><span class="pun">(</span><span class="pln">noofstudents</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="kwd">class</span><span class="pln"> </span><span class="kwd">where</span><span class="pln"> teacherID IN</span><span class="pun">(</span><span class="pln">
</span><span class="typ">Select</span><span class="pln"> id </span><span class="kwd">from</span><span class="pln"> teacher
</span><span class="typ">Where</span><span class="pln"> subject</span><span class="pun">=?</span><span class="pln">science</span><span class="pun">?</span><span class="pln"> OR subject</span><span class="pun">=?</span><span class="pln">maths</span><span class="pun">?);</span>

Output

You will get the following output ?

20.0

Example 2

<span class="pln">SELECT </span><span class="pun">*</span><span class="pln"> FROM student
WHERE classID </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="pln">
   SELECT id
   FROM </span><span class="kwd">class</span><span class="pln">
   WHERE noofstudents </span><span class="pun">=</span><span class="pln"> </span><span class="pun">(</span><span class="pln">
      SELECT MAX</span><span class="pun">(</span><span class="pln">noofstudents</span><span class="pun">)</span><span class="pln">
      FROM </span><span class="kwd">class</span><span class="pun">));</span>

Output

You will get the following output ?

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