03多表查询-子查询（一）

第1关：子查询

任务描述

本关任务：求年龄大于’刘东明’ 的所有学生的姓名与出生日期。

相关知识

为了完成本关任务，你需要掌握：
1.什么叫子查询，
2.比较运算符结合标量子查询的使用。

子查询
子查询是指出现在其他SQL语句内的SELECT子句。

例如：

SELECT * FROM t1 WHERE col1=(SELECT col2 FROM t2);
子查询指嵌套在查询内部，且必须始终出现在圆括号内，子查询可以分为四类：

标量子查询：返回单一值的标量，最简单的形式；

列子查询：返回的结果集是 N 行一列；

行子查询：返回的结果集是一行 N 列；

表子查询：返回的结果集是 N 行 N 列。

带比较运算符的子查询
运算符 说明

大于
= 大于或等于
= 等于
!= 或 <> 不等于
< 小于
<= 小于或等于
带有比较运算符的子查询是指父查询和子查询使用比较运算符连接的嵌套查询；

使用场景：当用户能够确切的知道内层查询返回的是单个值时,可以使用比较运算符。

举个例子，现在有一张employee表数据：

id name dept_id
1 Mary 101
2 Allen 102
3 kevin 101
4 Tom 102
5 Nancy 101
我们查询与Tom在同一个部门的同事id和姓名以及部门id。
1.首先，可以查询Tom所在部门id

2.然后查询dept_id=102的同事id和姓名

将上面两条简单的的SQL语句合并成一个嵌套查询：

编程要求
在右侧编辑器补充代码，求年龄大于’刘东明’ 的所有学生的姓名与出生日期。
–学生表student
学生表
####测试说明

答案

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
 select sname, birthday 
 from student 
 where birthday < (select birthday from student where sname = '刘东明')
  
  
 /**********End**********/

第2关：子查询-练习

任务描述

本关任务：求未选修任何课程的学生的学号和姓名。

相关知识

见上一关

编程要求
根据提示，在右侧编辑器补充代码，求未选修任何课程的学生的学号和姓名。
–学生表student
学生表
–成绩表score
成绩表

答案

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
 -- 未选修任何课程 ==> 他的学号不在成绩表中
  select sno, sname from student where sno not in
  (select sno from score);
  
 /**********End**********/

第3关：子查询-练习一

任务描述

本关任务：求比数学系中全体学生年龄大的学生的姓名和系

相关知识

见上一关

编程要求
根据提示，在右侧编辑器补充代码，求比数学系中全体学生年龄大的学生的姓名和系。
–学生表student
学生表

答案

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sname, sdept, birthday 
  from student 
  where birthday < (select min(birthday) from student where sdept = '数学');
  -- select min(birthday) from student where sdept = '数学系';
  -- select * from student;
 /**********End**********/

第4关：子查询-练习二

任务描述

本关任务：求选修了004课程的学生的姓名和系。

相关知识

见上一关

编程要求

根据提示，在右侧编辑器补充代码，求选修了004课程的学生的姓名和系。

答案

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
  select sname,sdept 
  from student 
  where sno in (select sno from score where cno = '004')
  
  
 /**********End**********/

第5关：子查询-练习三

任务描述

本关任务：求选修了’刘东明’ 同学选修的全部课程的学生的学号。

相关知识

见上一关

编程要求

在右侧窗口补充代码，求选修了’刘东明’ 同学选修的全部课程的学生的学号。
–学生表student

答案

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
--  select * from student;
--  select * from score;
  select sno from score where cno in (select cno from score where sno = (select sno from student where sname = '刘东明')) and score.sno!= (select sno from student where sname = '刘东明') group by sno having count(cno)>=2 ;

-- select sno from score where cno in(select cno from score,student where score.sno = student.sno and sname = '刘东明') and score.sno!= (select sno from student where sname = '刘东明') group by sno having count(cno)>=2 ;
  
 /**********End**********/

第6关：子查询-练习四

任务描述

本关任务：求选修了全部课程的学生的学号。

相关知识

见上一关

编程要求

根据提示，在右侧编辑器补充代码， 求选修了全部课程的学生的学号。

答案

 use teachingdb;
 /****请在此编写代码，操作完毕之后点击评测******/
 
 /**********Begin**********/
 /* select sno from student where not exists(select * from course where not exists(select * from score where score.sno = student.sno and score.cno = course.cno));*/
-- 从课程表中统计课程门数和从成绩表中根据学号分组统计课程门数
select sno from score group by sno having count(cno)=
(select count(*) from course);

 /**********End**********/