SQL编程题复习（24/9/21）

练习题 x25

10-95 在stu表和 sc表中查询所有学生的平均成绩，如果某学生尚未选修课程或成绩为空时，平均分计为0
10-96 显示同时选修了1号与2号课程的同学的学号（`建议二刷`）
10-97 显示2008年每门课程期末成绩的平均分（Avg）,四舍五入包保留两位小数，显示课程号与平均分，并按分数降序排列（`left函数`）
10-98 统计每位教师每周上课的次数（Num）及总时长（Len，每节课45分钟）（`length函数`）
10-99 显示人数5人以上班级的班级编号和人数（Num），并按人数升序排列（`题有问题`）
10-100 统计每个年龄段（Age）的人数（Num），按照年龄段升序排列
10-101 显示计算机科学与技术系同学的名单，包括学号、姓名、性别、年龄（Age）、班级名称、入学年份（`分析需要使用的表`）
10-102 显示选修了"数据库"课程的所有同学的学号、姓名（`分析需要使用的表`）
10-103 删除未担任班导师并且未安排课程的教师记录（`建议二刷`）
10-104 显示白云同学的班主任老师的姓名、联系电话（`分析需要使用的表`）
10-105 显示白云同学所在院系的名称、办公地点与联系电话（`分析需要使用的表`）
10-106 查询统计计算机科学与技术系每个同学已经修完的学分，显示学号、姓名、学分总数（Credit）（`分析需要使用的表`）
10-107 查询显示张飞同学已修课程的课程号，课程名及期末成绩（`分析需要使用的表`）
10-108 显示计算机科学与技术系、信息科学与技术系的班级名称、入学年份、班主任名称与联系电话（`分析需要使用的表`）
10-109 显示2008年入学的同学的学号、姓名、班级名称（`分析需要使用的表`）
10-110 显示已修数据库的同学的学号、姓名及期末成绩（`分析需要使用的表`）
10-111 显示期末平均成绩75分以上的课程名称与期末平均成绩（Avg，结果保留两位小数`round函数`）（`分析需要使用的表`）
10-112 显示期末平均成绩80分以上同学的学号、姓名与期末平均成绩（Avg，结果保留两位小数`round函数`）（`分析需要使用的表`）
10-113 按照班级统计期末平均成绩，显示班级名称与平均成绩（Avg，结果保留两位小数`round函数`）（`分析需要使用的表`）
10-114 按照学期统计计算机科学与技术系学生的期末平均成绩，显示学期名称与平均成绩（Avg，结果保留两位小数`round函数`）（`分析需要使用的表`）
10-115 统计每个院系一周的课时数（Hours），显示院系名称与课时数（`分析需要使用的表`）
10-116 显示没有选修任何课程的学生学号、姓名、班级名称（`分析需要使用的表`）
10-117 显示上过李飞老师的课的学生的学号、姓名与联系电话（`分析需要使用的表`）
10-118 显示一周6节课及以上的课程名称、学分（`分析需要使用的表`）
10-119 显示一周6节课及以上班级名称（`分析需要使用的表`）

10-95 在stu表和 sc表中查询所有学生的平均成绩，如果某学生尚未选修课程或成绩为空时，平均分计为0

-- select stu.sno 学号,avg(if(not grade <=> null,grade,0)) 平均成绩
select stu.sno 学号,avg(ifnull(grade,0)) 平均成绩
from stu
left join sc on stu.sno=sc.sno
group by stu.sno-- ifnull(value1,value2)
-- ifnull 函数，value1 为真返回value1 否则返回value2-- if(value,value1,value2)
-- if 函数，value为真 返回value1 否则返回value2

10-96 显示同时选修了1号与2号课程的同学的学号（`建议二刷`）

-- 方法一：自连接
-- select a.SId
-- from sc a,sc b
-- where a.SId=b.SId
-- and a.CId='1' and b.CId='2'-- 方法二：嵌套子查询
select SId
from sc
where CId= '1'
and SId in(select SIdfrom scwhere CId='2'
)-- 在一个表中 查询同时选择2门课的学生
-- 建议二刷

10-97 显示2008年每门课程期末成绩的平均分（Avg）,四舍五入包保留两位小数，显示课程号与平均分，并按分数降序排列（`left函数`）

select CId,round(avg(SCScore3),2) Avg
from sc
where left(SCTerm,4)=2008
group by CId
order by Avg desc-- 从左往右获取n 个字符，使用left(字符串,n)
-- 从右往左 同理

10-98 统计每位教师每周上课的次数（Num）及总时长（Len，每节课45分钟）（`length函数`）

select TId,count(*) Num,sum(length(ITimeseg)*45) Len
from information
group by TId-- 上课时长和ITimeseg 有关
-- ITimeseg 的长度对应上了几节课 每节课45min
-- length(ITimeseg) 获取上了几节课

10-99 显示人数5人以上班级的班级编号和人数（Num），并按人数升序排列（`题有问题`）

select GId,count(*) Num
from student
group by GId
having count(*) >5
order by Num asc-- 题目存在问题

10-100 统计每个年龄段（Age）的人数（Num），按照年龄段升序排列

-- studentselect year(now()) - year(SBdate) Age,count(*) Num
from student
group by year(now()) - year(SBdate)
order by Age-- 题目没给时间就用curdate() 或 now()

10-101 显示计算机科学与技术系同学的名单，包括学号、姓名、性别、年龄（Age）、班级名称、入学年份（`分析需要使用的表`）

-- student grade deptselect SId,SName,SSexy,timestampdiff(year,SBdate,now()) Age,GName,GYear
from student,grade,dept
where student.GId=grade.GId
and grade.DId=dept.DId
and DName = '计算机科学与技术系'-- 分析需要使用的表
-- 计算年龄
-- 使用timestampdiff 更精确;使用year 只考虑年份 边界不一样会导致误差
-- timestampdiff(year/month/day,列名,now())
-- 时间戳差异函数

10-102 显示选修了"数据库"课程的所有同学的学号、姓名（`分析需要使用的表`）

-- student course scselect student.SId,SName
from student,course,sc
where student.SId=sc.SId
and sc.CId=course.CId
and CName='数据库'-- 避免ambiguous 错误

10-103 删除未担任班导师并且未安排课程的教师记录（`建议二刷`）

-- 为担任教师 grade.TId is null 
-- 未安排课程 TId not in informationdelete 
from teacher
where TId in(select TId from (select TId from teacher  where TId not in(     -- 1.教师不在grade表中TId不为空的人里select distinct TId from grade where TId is not null)and TId not in (     -- 2.不在information表中出现过的记录里select distinct TId from information)) temp 
)-- 不能对同一个表 边查询边修改，使用派生表解决

10-104 显示白云同学的班主任老师的姓名、联系电话（`分析需要使用的表`）

-- student grade teacherselect TName,TTele
from student,grade,teacher
where student.GId=grade.GId
and grade.TId=teacher.TId
and SName='白云'-- 分析对此题有用的表

10-105 显示白云同学所在院系的名称、办公地点与联系电话（`分析需要使用的表`）

-- student dept grade select DName,DAddr,DTele
from student,dept,grade
where student.GId=grade.GId
and grade.DId=dept.DId
and SName='白云'-- 分析对此题有用的表！！！
-- student 学生姓名
-- dept 系名、系编号
-- grade 系编号、班级编号

10-106 查询统计计算机科学与技术系每个同学已经修完的学分，显示学号、姓名、学分总数（Credit）（`分析需要使用的表`）

-- dept course student sc gradeselect student.SId,SName,sum(CCredit) Credit
from dept,course,student,sc,grade
where student.GId=grade.GId
and grade.DId=dept.DId
and course.CId=sc.CId
and sc.SId=student.SId
and DName = '计算机科学与技术系'
group by student.SId,SName-- 分析对此题有用的表
-- 学号、姓名student
-- 学分course
-- 系别 grade(系编号)、dept(系名)

10-107 查询显示张飞同学已修课程的课程号，课程名及期末成绩（`分析需要使用的表`）

-- student sc courseselect sc.CId,CName,SCScore3
from student,sc,course
where student.SId=sc.SId
and sc.CId=course.CId
and SName='张飞'-- 排除干扰表,分析出对此题有用的表
-- 多表连接(等值连接)

10-108 显示计算机科学与技术系、信息科学与技术系的班级名称、入学年份、班主任名称与联系电话（`分析需要使用的表`）

-- dept grade teacherselect GName,GYear,TName,TTele
from dept,grade,teacher
where dept.DId=grade.DId
and grade.TId=teacher.TId
and DName in('计算机科学与技术系','信息科学与技术系')-- 分析对此题有用的表
-- 多表连接查询；
-- 也可以嵌套子查询

10-109 显示2008年入学的同学的学号、姓名、班级名称（`分析需要使用的表`）

-- grade studentselect SId,SName,GName
from grade,student
where grade.GId=student.GId
and GYear = 2008-- 判断相等？
-- 使用where a = b

10-110 显示已修数据库的同学的学号、姓名及期末成绩（`分析需要使用的表`）

-- student sc courseselect student.SId,SName,SCScore3
from sc,course,student
where course.CId=sc.CId
and sc.SId=student.SId
and course.CName='数据库'-- 分析对此题有用的表

10-111 显示期末平均成绩75分以上的课程名称与期末平均成绩（Avg，结果保留两位小数`round函数`）（`分析需要使用的表`）

-- course scselect CName,round(avg(SCScore3),2) Avg
from course,sc
where course.CId=sc.CId
group by CName
having avg(SCScore3)>75-- 分析此题需要用的表
-- 保留两位小数 round(x,2)

10-112 显示期末平均成绩80分以上同学的学号、姓名与期末平均成绩（Avg，结果保留两位小数`round函数`）（`分析需要使用的表`）

-- student scselect student.SId,SName,round(avg(SCScore3),2) Avg
from student,sc
where student.SId=sc.SId
group by student.SId,SName
having avg(SCScore3)>80-- SQL执行顺序
-- from -> on -> left/right join -> where ->
-- group by -> having -> select -> distinct ->
-- order by -> limit

10-113 按照班级统计期末平均成绩，显示班级名称与平均成绩（Avg，结果保留两位小数`round函数`）（`分析需要使用的表`）

-- grade sc studentselect GName,round(avg(SCScore3),2) Avg
from grade,sc,student
where grade.GId=student.GId
and student.SId=sc.SId
group by GName-- 排除干扰表，分析对此题有用的表

10-114 按照学期统计计算机科学与技术系学生的期末平均成绩，显示学期名称与平均成绩（Avg，结果保留两位小数`round函数`）（`分析需要使用的表`）

-- 系、成绩、学期名称
-- dept sc grade student
select SCTerm,round(avg(SCScore3),2) Avg
from dept,sc,grade,student
where dept.DId=grade.DId
and grade.GId=student.GId
and student.SId=sc.SId
and dept.DName='计算机科学与技术系'
group by SCTerm-- 分析对此题有用的表
-- 输出样例保留了 两位小数
-- round(avg(分数),2)

10-115 统计每个院系一周的课时数（Hours），显示院系名称与课时数（`分析需要使用的表`）

-- dept course informationselect dept.DName,sum(length(ITimeseg)) Hours
from dept,course,information
where dept.DId=course.DId
and course.CId=information.CId
group by dept.DName-- ITimeseg 和课时有关
-- 课时数计算sum(length(ITimeseg));
-- 分析对此题有用的表,多表连接
-- 建议二刷

10-116 显示没有选修任何课程的学生学号、姓名、班级名称（`分析需要使用的表`）

-- student grade scselect SId,SName,GName
from student,grade
where grade.GId=student.GId
and SId not in (select distinct SIdfrom sc
)-- 分析对此题有用的表
-- 后台有很多 测试数据
-- 建议二刷

10-117 显示上过李飞老师的课的学生的学号、姓名与联系电话（`分析需要使用的表`）

-- student  sc teacher information
select distinct student.SId,student.SName,student.STele
from student,sc,teacher,information
where student.SId=sc.SId
and sc.CId=information.CId
and information.TId=teacher.TId
and teacher.TId in(select TId from teacher where TName='李飞'
)-- 题目给了很多表，考虑多表连接而不是简单的嵌套子查询；
-- 分析对此题有用的表，等值连接
-- 建议二刷

10-118 显示一周6节课及以上的课程名称、学分（`分析需要使用的表`）

-- course informationselect CName,CCredit
from course
where CId in (select CId          -- 1.课程数量大于6的课程序号from informationgroup by CIdhaving sum(length(ITimeseg))>=6
)-- 嵌套子查询
-- length函数，求字符串长度
-- 分析需要用到的表
-- 建议二刷

10-119 显示一周6节课及以上班级名称（`分析需要使用的表`）

-- grade informationselect GName
from grade
where GId in (select GId   -- 1.查询课程长度大于6的班级编号from informationgroup by GIdhaving sum(length(ITimeseg))>=6
)-- 嵌套子查询
-- length函数，求字符串长度
-- 建议二刷