SQL语言编程学习系列（四）——数据库嵌套查询和更新操作

        本系列同样是记录代码为主，附带实验内容与要求，可参考代码，SQL编码习惯为小写且习惯性换行。

        实验的环境和软件为Microsoft SQLServer 2019。

实验内容：

1.用嵌套子查询找出课程表中学时最大的课程的编号、名称、学时。

select cno,cname,chours
from course
where chours >= all(
select chours from course)

2.将数据库原理课的学时更正为54。

--3-4-2
update course
set chours=54
where cname='数据库'

3.将学生赵磊从编号为001的班转到002班。

--3-4-3
update student
set clno=002
where sname='赵磊'

4.将19数学(班级名称)的李勇的出生日期更正为1995-12-12。

--3-4-4
update student
set sbirth='1995-12-12'
where sname='李勇' and clno=(
select clno from class where clname='19数学')

5.删除1020号学生的选课记录。

--3-4-5
delete
from sc
where sno=1020

6.删除班号为002的班级的学生信息。

--3-4-6
delete
from student
where clno=002

7.查询选修人数最少的课程的课程号、课程名称。

select sc.cno,cname
from sc,course
where sc.cno=course.cno
group by cname,sc.cno having count(sc.cno)<= all (
select count(cno) from sc group by cno)

8.列出有两门及以上课程不及格的学生的学号及该生的平均成绩。

--3-4-8
select sno,avg(grade) as 'avg_grade'
from sc
group by sno having sum(case when grade<60 then 1 else 0 end)>=2

9.查询选修课程数最多及最少的学生的学号、姓名。

--3-4-9
select student.sno,sname
from student,sc
where student.sno=sc.sno
group by sname,student.sno having count(sc.sno)<=all (
select count(sc.sno) from sc group by sno) or count(sc.sno)>=all (
select count(sc.sno) from sc group by sno)

10.列出所选修课程与李勇完全相同的学生的学号、姓名。

--3-4-10
select student.sno,sname
from student,sc
where  student.sno=sc.sno 
and 
student.sno not in (
select sno from sc where cno not in(
select cno from sc where sno in(
select sno from student where sname='李勇')))
group by student.sname,student.sno,sc.sno having count(sc.sno)=(
select count(sc.sno) from sc where sno in (
select sno from student where sname='李勇'))