- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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
Advertisements
To Continue Learning Please Login
Login with Google