解释 DBMS 中的嵌套查询
嵌套查询是其中嵌入了另一个查询的查询。嵌入的查询称为子查询。
子查询通常出现在查询的WHERE子句中。它有时会出现在FROM子句或HAVING子句中。
示例
让我们借助示例了解嵌套查询。
查找regno=103的员工姓名
查询如下-
selectE.enamefrom employee E whereE.eidIN (selectS.eidfrom salary S where S.regno=103);
学生桌
学生表创建如下-
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;输出结果
您将获得以下输出-
教师桌
教师表创建如下-
示例
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;输出结果
您将获得以下输出-
班级表
类表创建如下-
示例
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;输出结果
您将获得以下输出-
现在让我们处理嵌套查询
示例1
Select AVG(noofstudents) from class where teacherID IN( Select id from teacher Where subject=’science’ OR subject=’maths’);输出结果
您将获得以下输出-
20.0
示例2
SELECT * FROM student
WHERE classID = (
SELECT id
FROM class
WHERE noofstudents = (
SELECT MAX(noofstudents)
FROM class));输出结果您将获得以下输出-
4|lucky |2|2.67 5|ram |2|4.56