当前位置:   article > 正文

实验4 复杂查询(1)_检索至少选修了c1和c2课程的学生姓名

检索至少选修了c1和c2课程的学生姓名

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 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97

对于给查询的属性列加别名有疑问的童鞋,可以点击下方链接进行查看。
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
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/IT小白/article/detail/474005
推荐阅读
相关标签
  

闽ICP备14008679号