查询的相关操作

对相关题目要求做了优化 如浮点类型转换,结果的检查 知识点的注释

-- 1.
--(1)查询学生的专业，过滤重复行
select distinct specialty 专业
from student

--(2)思路1:统计有学生选修的课程数 优点提高查询效率 对于每一个课程号 
--只需对课程表做一个存在性检查即可 存在即返回true
select cname
from course
where exists(select * from score where cno = cno_s)
order by cname

-- 思路2: 查询成绩表中存在的课程名 去重
select distinct cname 课程名, COUNT(cname) 选修人数
from course,score
where cno = cno_s
group by cname
order by cname

-- (3)求选修课程号为"1004"学生的平均年龄
select avg(sage) 平均年龄
from student, score
where sno = sno_s and cno_s = '1004'

-- (4)求学分为3的各门课程的平均成绩 结果转为浮点类型 2位小数
select cname, cast(avg(sscore * 1.0) as decimal(4, 2)) 平均成绩
from course, score
where cno = cno_s and credit = 3
group by cname

-- (5)统计每门课程的选修人数(>=2人的) 按人数降序排序 课程号升序 
select cno, cname 课程名, count(cno_s) 选修人数  
from score,course
group by cname, cno, cno_s
having cno = cno_s and count(cno_s) >= 2
order by 选修人数 desc, cno

-- (6)查询所有姓"刘"的姓名和年龄
select sname 姓名, sage 年龄
from student
where sname like '刘%'

-- (7)查询成绩为空的学号和课程号
select cno_s 课程号, sno_s 学号
from score
where sscore is null

-- (8)查询没有学生选修课的课程名和课程号 -- (相关子查询)
select cname, cno
from course
where cno not in(
select cno_s from score where cno = cno_s)

-- (9)求年龄大于平均男生年龄的女生的学号和姓名
select sno, sname, sage
from student a
where a.sage > (
select avg(sage) from student b where ssex = '男'
) and ssex = '女'

-- (10)求年龄大于所有男生的女生的学号和姓名
select sno, sname, sage
from student a
where sage > (
select max(sage) from student b where ssex = '男'
) and ssex = '女'

-- (11)查询所有和'刘宏伟'同年级,同专业 但比'沈艳'年龄大的学生的姓名,年龄,性别
-- 不考虑重名 (多条无关子查询)
select sname, ssex, sage
from student a
where specialty = (select specialty from student b where sname = '刘宏伟') and
en_time = (select en_time from student c where sname = '刘宏伟') and
sage > (select sage from student d where sname = '沈艳')

-- (12)查询选修'1001'课程中成绩最高的学生的学号 (嵌套的无关子查询)
select sno, sname, sscore
from student, score
where sno = sno_s and sno in (
select sno_s from score where cno_s = '1001' and sscore in (
select max(sscore) from score where cno_s = '1001')
) and cno_s = '1001'

-- 检查语句
select sno, sname, sscore
from student, score
where sno = sno_s and sscore = 95 and cno_s = '1001'

-- (13)查询学生的姓名,所选修的课程号,成绩
select sname, cno_s, sscore
from student, score
where sno = sno_s and sscore is not null order by sname, cno_s

-- (14)查询选修两门以上课程的学生的平均成绩(>=60的) 按平均成绩降序排序 ? (相关子查询)
select sname, cast(avg(sscore * 1.0) as decimal(4, 2)) 平均成绩
from student, score a
where sno = sno_s and (select count(cno_s) from score b where a.sno_s = b.sno_s) >= 2 and sscore >= 60
group by sname
order by 平均成绩 desc

-- (15)求每个学生的平均成绩 取前五名 top 应用
select top 5 sname, cast(avg(sscore * 1.0) as decimal(4, 2)) 平均成绩
from student, score
where sno = sno_s and sscore is not null
group by sname order by 平均成绩 desc

-- (16)查询每个学生的总学分 每个学生选课的学分总和 -- （>两个表的链接）
select sname, sum(credit)
from student, course, score
where sno = sno_s and cno = cno_s
group by sname

-- (17)每门课程学生成绩最低的学号和课程号 (相关查询)
select sno_s 学号 , cno 课程号,cname, sscore 
from course, score a
where cno = cno_s and sscore = (select min(sscore) from score b where a.cno_s = b.cno_s)