MYSQL基础上机练习题（五）嵌套查询（UNION、EXCEPT、INTERSECT）

时间  2021-01-05

实验目标：

1．掌握嵌套查询，从而增强SQL的查询能力

2．掌握UNION、EXCEPT、INTERSECT操作符将来自不同查询但结构相同的数据集合组合起来，形成一个具有综合信息的查询结果

sql脚本：

INSERT [dbo].[T] ([TNO], [TN], [SEX], [AGE], [PROF], [SAL], [COMM], [DEPT]) VALUES (N'T1', N'李力    ', N'男', 47, N'教授      ', 1800, 3000, N'计算机    ')
GO
INSERT [dbo].[T] ([TNO], [TN], [SEX], [AGE], [PROF], [SAL], [COMM], [DEPT]) VALUES (N'T2', N'王平    ', N'女', 28, N'讲师      ', 850, 1200, N'信息      ')
GO
INSERT [dbo].[T] ([TNO], [TN], [SEX], [AGE], [PROF], [SAL], [COMM], [DEPT]) VALUES (N'T3', N'刘伟    ', N'男', 30, N'讲师      ', 900, 1200, N'计算机    ')
GO
INSERT [dbo].[T] ([TNO], [TN], [SEX], [AGE], [PROF], [SAL], [COMM], [DEPT]) VALUES (N'T4', N'张雪    ', N'女', 51, N'教授      ', 1900, 3000, N'自动化    ')
GO
INSERT [dbo].[T] ([TNO], [TN], [SEX], [AGE], [PROF], [SAL], [COMM], [DEPT]) VALUES (N'T5', N'张兰    ', N'女', 39, N'副教授    ', 1300, 2000, N'信息      ')
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N'C1', N'程序设计  ', 60)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N'C2', N'微机原理  ', 80)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N'C3', N'数字逻辑  ', 60)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N'C4', N'数据结构  ', 80)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N'C5', N'数据库    ', 60)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N'C6', N'编译原理  ', 60)
GO
INSERT [dbo].[C] ([CNO], [CN], [CT]) VALUES (N'C7', N'操作系统  ', 60)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N'T1', N'C1', NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N'T1', N'C4', NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N'T2', N'C5', NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N'T3', N'C1', NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N'T3', N'C5', NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N'T4', N'C2', NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N'T4', N'C3', NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N'T5', N'C5', NULL)
GO
INSERT [dbo].[TC] ([TNO], [CNO], [Evaluation]) VALUES (N'T5', N'C7', NULL)
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N'S0', N'王青山  ', N'男', 19, N'计算机    ')
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N'S1', N'赵亦    ', N'女', 17, N'计算机    ')
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N'S2', N'钱尔    ', N'男', 18, N'信息      ')
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N'S3', N'张晓明  ', N'男', 18, N'信息      ')
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N'S4', N'李思    ', N'男', 21, N'自动化    ')
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N'S5', N'周武    ', N'男', 19, N'计算机    ')
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N'S6', N'吴丽    ', N'女', 20, N'自动化    ')
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N'S7', N'范思萌  ', N'女', 18, N'信息      ')
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N'S8', N'孙姗    ', N'女', 20, N'信息      ')
GO
INSERT [dbo].[S] ([SNO], [SN], [SEX], [AGE], [DEPT]) VALUES (N'S9', N'张海涛  ', N'男', 19, N'信息      ')
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N'S1', N'C1', 90)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N'S1', N'C2', 85)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N'S2', N'C5', 50)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N'S2', N'C6', 80)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N'S3', N'C1', 75)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N'S3', N'C2', 70)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N'S3', N'C4', 85)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N'S4', N'C1', 93)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N'S4', N'C2', 85)
GO
INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N'S4', N'C3', 83)
GO

INSERT [dbo].[SC] ([SNO], [CNO], [SCORE]) VALUES (N'S5', N'C2', 89)

GO

1．可通过以下步骤，完成查询与“刘伟”教师职称相同的教师号、姓名和职称（必须使用嵌套查询）。

①　先在表Ｔ查询“刘伟”教师的职称，请描述相应的SQL

 

②　得到“刘伟”教师的职称，请问是单个值还是多值？

单值

③　若是单值，直接将上一步的查询作为子查询再结合比较运算符，得到其它与“刘伟”教师相同称职的教师号、姓名和职称。描述相应的SQL并截图说明结果。

 

2．可通过以下步骤，使用ANY谓词查询讲授课程号为C5的教师姓名。

①　先在表TC中得到教授课程号为C5的教师号，请描述相应的SQL

 

②　判断上一步得到的结果是单值还是多值？

多值

③　若是多值，则考虑通过ANY（或者）或ALL谓词（所有）将上一步的查询作为子查询再结合比较运算符，在表T中查询相应的教师姓名。描述相应的SQL并截图说明结果。

 

3．可通过以下步骤，使用ALL谓词查询其他系中比计算机系所有老师工资都高的教师姓名、工资和所在系。

①　先在表T中得到计算机系所有教师的工资，请描述相应的SQL

 

②　判断上一步得到的结果是单值还是多值？

多值

③　若是多值，则考虑通过ANY（或者）或ALL谓词（所有）将上一步的查询作为子查询再结合比较运算符，在表T中查询相应非计算机系的的教师姓名。描述相应的SQL并截图说明结果。

 

④　有其它的方法吗？若有，列举一种。

有吧

4．可通过以下思路，使用EXISTS谓词查询没有讲授课程号为C5的课程的教师姓名、所在系。

 

注明：外层查询将T表中所有教师去内部TC表查询，内部查询每扫描完一次TC表，如果有返回相应的行表示该教师教授了C5这门课，如果返回为空集，则表示该老师没有教授C5这门课。

 

5．使用EXISTS谓词查询至少选修了学生S2选修的全部课程的学生的学号。可通过以下思路得到相应的SQL。

 

6．使用UNION、EXCEPT、INTERSECT操作符组合不同查询但结构相同的数据集，完成下面三题。

①　查询年龄不大于19岁或者属于计算机系的学生

 

②　查询专业为计算机但年龄小于19岁不包括在内的学生。若学生年龄为NULL，请问会不会被查询出？

 

不会被查询出来

 

③　查询专业为计算机且年龄小于19岁的学生。请将计算机系某一学生的年龄改为NULL，再执行此题的SQL语句，请问会不会被查询出该年龄为NULL的学生？

 


 

不会

 


二、课后练习

sql脚本：

--学生表（学号、姓名、年龄、性别）
create table student(
sno varchar(10) primary key,
sname varchar(20),
sage int,
ssex varchar(5)
)
--教师表（教师工号、姓名）
create table teacher(
tno varchar(10) primary key,
tname varchar(20)
)
--课程表（课程号、课程名、教师工号）
create table course(
cno varchar(10),
cname varchar(20),
tno varchar(10),
constraint pk_course primary key (cno,tno)
)
--成绩表（学号、课程号、分数）
create table sc(
sno varchar(10),
cno varchar(10),
score real,
constraint pk_sc primary key (sno,cno)
)
--初始化学生表
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');
--初始化教师表
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');
--初始化课程表
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');
--初始化成绩表
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003',59);

执行shiyan综合题.sql中的脚本创建表并导入相应的数据。请用嵌套查询完成下列问题。

1．查询“c001”课程比“c002”课程成绩高的所有学生的学号；

 

select sno from sc c

where c.cno='c001' and exists (

   select * from sc a

   where a.sno=c.sno and a.cno='c002' and c.score>a.score)

 

2．查询所有同学的学号、姓名、选课数、总成绩。

思路：

①　从SC表中查出选课学号、选课数，总成绩

②　将得到的结果集与student表连接，要求控制总分数的小数位数两位，没有选课的学生选课门数为0，总分数为0，而不是显示NULL）

 


或者



3．查询没学过“谌燕”老师课的同学的学号、姓名；（分别用in或EXISTS子查询），参考查询结果如下。

 


或者


4．查询没有选修全部课程的同学的学号、姓名。（思路：分别用EXISTS、EXCEPT完成）

 


或



5．快速复制表SC至SC1，并在SC1表中删除相应的记录，这些记录满足课程是由“谌燕”老师所讲授。

    

select * into sc1 from sc

delete from sc1

where sc1.cno in

(

select cno from course c

left join teacher t on c.tno=t.tno

where t.tname='谌燕'

)


6．快速复制表SC至SC2，向SC2 表中插入一些记录，这些记录要求符合以下条件：

①　sno的取值为没有上过编号为“c002”课程的同学学号；

②　cno的取值为“c002”；

③　score的取值为“c002”号课的平均成绩。

提示：insert插入的记录可以是由SELECT查询所得到的结果集。

select * into sc2 from sc

insert into sc2(sno,cno,score)

select distinct student.sno,sc2.cno,

(

select avg(score)

from sc2

where cno='c002'

)

from student,sc2

where not exists

(

select * from sc2

 where cno='c002' and sc2.sno=student.sno

) and sc2.cno='c002'

select * from sc2

   
联系我们 沪ICP备2021010478号-7