实验4 复杂查询（1）

SQL数据库中Numeric(10,2)是指字段是数值型，Numeric(10,2)表示总位数为10，小数点后为2位的数，也就是说这个字段的整数位最大是8位。

SQL语句三大难点：连接、分组、子查询。
先分组，再统计。
where子句中是不能用聚集函数作为条件表达式的。聚集函数只能用于select子句和group by中having子句。
where子句与having短语的区别在于作用对象不同。where子句作用于基本表或视图，从中选择满足条件的元组。having短语作用于组，从中选择满足条件的组。

一. 实验目的：
1、掌握SQL语句中的group by 子句。
2. 掌握SQL语句中的子查询。
3. 掌握SQL语句中的order by 子句。

二. 实验内容：
1、数据库如下：
一个“教学数据库”，在该数据库下有如下3张表。






2. 利用SQL语句完成下列查询：
(1) 查询全体学生的学号、姓名、课程名和成绩。
(2) 查询选修了课程的学生的人数。
(3) 查询选修C1或C2且分数大于等于85分学生的姓名、课程名和成绩。
(4) 查询所有姓孙的学生的学号和姓名。
(5) 查询没有考试成绩的学生的学号、姓名和课程名。
(6) 查询选修C1课程的最高分，显示课程号、课程名和最高分。
(7) 查询每个学生的学号、姓名和平均成绩，按平均成绩的降序排列。
(8) 查询比孙珊同学所选修课程最高成绩还高的学生的姓名、课程名和成绩。
(9) 查询至少选修两门课程学生的学号、姓名和选课门数。
(10) 查询所有课程成绩均及格学生的姓名。

参考解答：

/*01、创建S表*/
create table S
(SNO char(5) primary key,
--为主键的属性非空，可不加not null。因为主键自动不为空（实体完整性规则：主键不为空）。
 SN char(8) not null,
 SEX char(2),
 AGE tinyint,
 DEPT char(20)
)

/*02、创建C表*/
create table C
(CNO char(5) primary key,
--为主键的属性非空，可不加not null。因为主键自动不为空（实体完整性规则：主键不为空）。
 CN char(20) not null,
 CT tinyint
)

/*03、创建SC表*/
create table SC
(SNO char(5) references S(SNO),
--为主键的属性非空，可不加not null。因为主键自动不为空（实体完整性规则：主键不为空）。
 CNO char(5) references C(CNO),
--为主键的属性非空，可不加not null。因为主键自动不为空（实体完整性规则：主键不为空）。
 SCORE numeric(4,1),
 primary key(SNO,CNO)
)

---注：为了使查询结果的美观，我给查询的属性列加了别名。
---sqlserver中给查询的属性列加别名的关键字as，可以省略。

/*1、查询全体学生的学号、姓名、课程名和成绩*/
select S.SNO 学号,SN 姓名,CN 课程名,SCORE 成绩
from S,SC,C
where S.SNO=SC.SNO and SC.CNO=C.CNO

/*2、查询选修了课程的学生人数*/
select COUNT(distinct SNO) 选修了课程的学生人数
from SC

/*3、查询选修C1或C2且分数大于等于85分学生的姓名、课程名和成绩*/
select SN 姓名,CN 课程名,SCORE 成绩
from S,SC,C
where S.SNO=SC.SNO and SC.CNO=C.CNO and SC.CNO in('C1','C2') and SCORE>=85

/*4、查询所有姓孙的学生的学号和姓名*/
select SNO 学号,SN 姓名
from S
where SN like '孙%'

/*5、查询没有考试成绩的学生的学号、姓名和课程名*/
select S.SNO 学号,SN 姓名 ,CN 课程名
from S,SC,C
where S.SNO=SC.SNO and SC.CNO=C.CNO and SCORE is null

/*6、查询选修C1课程的最高分，显示课程号、课程名和最高分*/
select C.CNO 课程号,CN 课程名,MAX(SCORE) 最高分
from SC,C
where SC.CNO=C.CNO and SC.CNO='C1'
group by C.CNO,CN

/*7、查询每个学生的学号、姓名和平均成绩，按平均成绩的降序排列*/
select S.SNO 学号,SN 姓名,AVG(SCORE) 平均成绩
from S,SC
where S.SNO=SC.SNO
group by S.SNO,SN
order by AVG(SCORE) desc

/*8、查询比孙珊同学所选课程最高成绩还高的学生的姓名、课程名和成绩*/
select SN 姓名,CN 课程名,SCORE 成绩
from S,SC,C
where S.SNO=SC.SNO and SC.CNO=C.CNO and SCORE>(select MAX(SCORE)
											   from S,SC
											   where S.SNO=SC.SNO and SN='孙珊'
											   )

/*9、查询至少选修两门课程学生的学号、姓名和选课门数*/
select S.SNO 学号,SN 姓名,COUNT(*) 选课门数
from S,SC
where S.SNO=SC.SNO 
group by S.SNO,SN
having COUNT(*)>=2

/*10、查询所有课程成绩均及格学生的姓名（法一：子查询）*/
select distinct SN 所有课程成绩均及格学生的姓名
from S,SC
where S.SNO=SC.SNO and SC.SNO not in(select SNO
									 from  SC
									 where SCORE<60 or SCORE is null
									 )

/*10、查询所有课程成绩均及格学生的姓名(法二：分组)*/
select SN 所有课程成绩均及格学生的姓名
from S,SC
where S.SNO=SC.SNO and SCORE is not null
group by SN
having MIN(SCORE)>=60 

对于给查询的属性列加别名有疑问的童鞋，可以点击下方链接进行查看。
01、SQL 使用别名（使用 AS 关键字）
02、sql server中给列取别名可以省略as吗？

————————————————————————————————————— 更新于2021.5.21

老师提供的参考答案：

(1)查询全体学生的学号、姓名、课程名和成绩。
select sc.sno,sn,cn,score
from s,sc,c
where sc.sno=s.sno and sc.cno=c.cno

(2)查询选修了课程的学生的人数。
select count(distinct sno) as 选修人数
from sc

(3)查询选修C1或C2且分数大于等于85分学生的姓名、课程名和成绩。
select sn,cn,score
from s,c,sc
where sc.sno=s.sno and sc.cno=c.cno
      and sc.cno in('c1' ,'c2')  and score>=85

(4)查询所有姓孙的学生的学号和姓名。
select sn ,sno
from  s
where sn like '孙%'

(5)查询没有考试成绩的学生的学号、姓名和课程名。
select s.sno,sn,cn
from s,c,sc
where sc.sno=s.sno and sc.cno=c.cno
      and score is null

(6)查询选修C1课程的最高分，显示课程号、课程名和最高分。
select c.cno,cn, max(score) as 最高分
from  sc,c
where sc.cno=c.cno and c.cno='c1'
group by c.cno,cn

select c.cno,cn, score
from  sc,c
where sc.cno=c.cno and score=(select max(score) from sc where cno='c1')

(7)查询每个学生的学号、姓名和平均成绩，按平均成绩的降序排列。
select sc.sno,sn,avg(score) as 平均成绩
from sc,s
where  sc.sno=s.sno
group by sc.sno,sn
order by avg(score) desc
	
(8)查询比孙珊同学所选修课程最高成绩还高的学生的姓名、课程名和成绩。
select sn ,cn ,score
from  s,sc,c
where score>(select max(score)
	from sc,s
	where sc.sno=s.sno
		and sn='孙珊')
	and sc.sno=s.sno
	and sc.cno=c.cno

(9)查询至少选修两门课程学生的学号、姓名和选课门数。
select s.sno,sn,count(*) 选修门数
from sc,s
where sc.sno=s.sno
group by s.sno,sn
having  count(*)>=2

(10)查询所有课程成绩均及格学生的姓名。
select  sn 
from sc,s
where  sc.sno=s.sno
group by sn
having min(score)>=60