PTA数据库作业（三）—终

---

前言

这学期的PTA数据库作业相关的内容就算是截止了，在这个过程中真的学到蛮多细节，sql目前也比较多人自学，希望我的这些能帮助到大家。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、题目详解

10-1 查询李小鹏同学所选课程名称

select cname
from sc,course,students
where sc.sno=students.sno and sc.cno=course.cno and students.sname='李小鹏'

10-2 spj-显示供应商供应零件的汇总列表

请使用 " WITH ROLLUP " 语句作答。

select coalesce(sno,'所有供应商') as 供应商,coalesce(pno,'所有零件') as 零件,sum(qty) as 供应量
from spj
group by sno,pno
with rollup;

10-3 查询“王珊”老师所授课程的课程名称

select distinct cname
from course inner join teaching on course.cno=teaching.cno
inner join teachers on teaching.tno=teachers.tno
where teachers.tname='王珊'

10-4 查询“陈晓东”同学所选课程的课号及成绩

select cno,score
from sc 
where sno in(select sno
             from students
             where sname like '陈晓东%')

10-5 查询“19信管2”班的学生所选修的课程号

select cno
from sc
where sno in (select sno 
              from students
              where class like '19信管2' )

10-6 查询信息学院学生所选修的课程号和成绩

select cno,score
from sc
where sno in (select sno
             from students
             where sdept like '信息学院%')

10-7 插入学生总学分表

insert into totalcredit
select sno,sum(credit) as totalcredit
from (select stu.sno as sno,case when sc.grade>=60 then credit else 0 end credit
     from stu left join sc 
     on stu.sno=sc.sno
     left join cou 
     on cou.cno=sc.cno
     group by stu.sno,sc.cno)a
group by sno;

10-8 查找课程选修的情况

select cou.cno 课程号,cname 课程名,count(sno) as 选课人数,ifnull(max(grade),0) 最高成绩,ifnull(min(grade),0) 最低成绩,ifnull(avg(grade),0) 平均成绩
from cou left join sc on sc.cno = cou.cno
group by cou.cno,cou.cname;

10-9 查询没有选修’C语言’课程的学生

select sno as 学号,sname as 姓名 from stu where not exists

(select * from sc where cno=( select cno from cou where cname = 'C语言') and stu.sno=sc.sno ) order by sno asc

这道题也超级磨人，怒刷了5-6次提交，上面是正确代码，下面是我之前一直不过的那个

select sno as 学号,sname as 姓名 
from stu 
where not exists(select * from sc
				where cno in(select cno from cou 
							where cname = 'C语言' and sc.cno=cou.cno)
				)
order by sno asc

两相对比可以看到之前写的这个代码中，没有把stu 表和 sc表联系起来，至于“sc.cno=cou.cno”在exist查询中应该是没起作用。

10-10 查询没有选课的学生学号和姓名

select sno,sname
from students 
where sno not in(select sno
                    from sc);

10-11 查询计算机工程专业学生选修但软件工程专业学生没有选修的课程。

select distinct cname
from cou
where cno not in (select cno from sc,stu,major
    where sc.sno=stu.sno and stu.mno=major.mno and mname='软件工程' )
    and cno in(select cno from sc,stu,major
    where sc.sno=stu.sno and stu.mno=major.mno and mname='计算机工程');

这道题我的方法比较普通就是拆解了题目，先判断软件工程未选修，再去找计算机工程选修的。当然，这部分也可以使用exist。

10-12 查询选修课名中含有“数据库”三个字的课程且成绩在80～90分之间的学生学号及成绩

select sno,score
from sc left join course 
on sc.cno=course.cno
where cname like '%数据库%' and score between 80 and 90

10-13 查询各专业的学生人数

select major.mno as 专业号,mname as 专业,count(sno) as 人数
from major left join stu on major.mno=stu.mno
group by major.mno

注意：这道题我最初使用count(*)以及结尾 group by mno 显示“运行时错误”。这是因为结尾无法判断mno 是哪个表中的，并且在count 时答案会出错。

10-14 查询平均分高于80分的学生(MSSQL)

select distinct sc.sno as 学号,stu.sname as 姓名,avg(sc.grade) as 平均成绩
from sc join stu
on sc.sno=stu.sno
group by sc.sno,stu.sname
having avg(grade)>80

10-15 查询至少选修2门课程的女生姓名

select students.sname
from students join sc
on students.sno=sc.sno
where students.ssex='女'
group by sname
having count(*)>2

10-16 查询课程成绩最高二人(MSSQL)

select top 2 stu.sno,stu.sname,sc.grade
from stu join sc 
on stu.sno=sc.sno
where sc.cno='C002'
order by sc.grade desc

这道题就很尴尬了，如图它题目要求用MySQL 按理我们应该使用limit 进行限制，但仔细看下它实际的编译环境是SQL service，所以实际上要使用 top

10-17 查询同专业的学生

本题目要求编写SQL语句， 检索Student表中与‘张三’在同一个专业的学生记录。

select sno as 学号,sname as 姓名
from stu
where mno in(select mno from stu where sname='张三')and sname<>'张三'

这道题的输出示例中不包含张三本人的信息，说明本题有要求，所以使用 “sname<>‘张三’” ，表示查询结果中不含’张三’

10-6 查询S001或S003学生选修课程（MSSQL）

select cou.cno as 课程号,cou.cname as 课程
from sc join cou on sc.cno=cou.cno
where sno='S001'
UNION
select cou.cno as 课程号,cou.cname as 课程
from sc join cou on sc.cno=cou.cno
where sno='S003'
order by cou.cno asc

---

总结

上述题目是我的作业题，如有错误或者不严谨的地方可以评论区提醒我。

同时，PTA 平台对于环境的编程要求比较规范，例如在出现聚合函数时如果没有用到group by分类的话就一定会报错，同时在多表查询情况下，尽量在属性前加上表，不然容易出现答案错误，因为PTA 后台是使用多组数据来核验代码的，不一定就和你看到的输出输入示例完全一模一样，也有部分数据未展示。