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

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 SId
    from sc
    where 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），按照年龄段升序排列

-- student

select 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 dept

select 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 sc

select 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 information

delete 
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 teacher

select 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 grade

select 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 course

select sc.CId,CName,SCScore3
from student,sc,course
where student.SId=sc.SId
and sc.CId=course.CId
and SName='张飞'

-- 排除干扰表,分析出对此题有用的表
-- 多表连接(等值连接)

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

-- dept grade teacher

select 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 student

select SId,SName,GName
from grade,student
where grade.GId=student.GId
and GYear = 2008

-- 判断相等？
-- 使用where a = b

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

-- student sc course

select 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 sc

select 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 sc

select 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 student

select 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 information

select 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 sc

select SId,SName,GName
from student,grade
where grade.GId=student.GId
and SId not in (
    select distinct SId
    from 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 information

select CName,CCredit
from course
where CId in (
    select CId          -- 1.课程数量大于6的课程序号
    from information
    group by CId
    having sum(length(ITimeseg))>=6
)

-- 嵌套子查询
-- length函数，求字符串长度
-- 分析需要用到的表
-- 建议二刷

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

-- grade information

select GName
from grade
where GId in (
    select GId   -- 1.查询课程长度大于6的班级编号
    from information
    group by GId
    having sum(length(ITimeseg))>=6
)

-- 嵌套子查询
-- length函数，求字符串长度
-- 建议二刷