记录一下无聊的数据库作业

题目如下:

1.查询sc表中的全部数据。
2. 查询计算机系学生的姓名和年龄
3.查询成绩在70~80分的学生的学号、课程号和成绩
4.查询计算机系年龄在18~20岁的男生姓名和年龄
s.查询c001课程的最高分
6.查询计算机系学生的最大年龄和最小年龄
7.统计每个系的学生人数
8.统计每]课程的选课人数和最高成绩。
9.统计每个学生的选课门数和考试总成绩，并按选课]数升序显示结果。
10.列出总成绩超过200的学生的学号和总成绩
11.查询选了c002课程的学生姓名和所在系
12.查询考试成绩80分以上的学生姓名、课程号和成绩，并按成绩降序排列结果
13.查询与vb在同一学期开设的课程的课程名和开课学期
14.查询与李勇年龄相同的学生的姓名、所在系和年龄
15.查询哪些课程没有学生选修，列出课程号和课程名
16.查询每个学生的选课情况,包括未选课的学生，列出学生的学号、姓名、选的课程号
17.查询计算机系哪些学生没有选课，列出学生姓名
18.查询计算机系年龄最大的三个学生的姓名和年龄
19.列出“vb"课程考试成绩前三名的学生的学号、姓名、所在系和vb成绩
20.查询选课门]数最多的前2位学生，列出学号和选课门数

代码如下:

-- 1
select *
from sc;
-- 2
select s.sname, s.sage
from student s
where s.sdept = n'计算机系';
-- 3
select sc.sno, sc.cno, sc.grade
from sc sc
where sc.grade between 70 and 80;
-- 4
select s.sname, s.sage
from student s
where s.sdept = n'计算机系'
  and s.sage in (18, 20)
  and s.ssex = n'男';
-- 5
select max(sc.grade) as max_grade
from sc sc
group by sc.cno
having sc.cno = 'c001';
-- 6
select max(s.sage) as max_age, min(s.sage) as min_age
from student s
group by s.sdept
having s.sdept = '计算机系';
-- 7
select concat(s.sdept, ' : ', count(s.sno)) as stu_nums
from student s
group by s.sdept;
-- 8
select sc.cno as cno, count(sc.sno) as c_nums, max(sc.grade) as max_grade
from sc sc
group by sc.cno;
-- 9
select count(sc.cno) as c_nums, sum(sc.grade) as sum_grades
from sc sc
group by sc.sno
order by c_nums;
-- 10
select sc.sno, sum(sc.grade) as sum_grades
from sc sc
group by sc.sno
having sum(sc.grade) > 200;
-- 11
select s.sname, s.sdept
from sc sc
         inner join student s
                    on sc.cno = 'c002';
-- 12
select s.sname, sc.cno, sc.grade
from sc sc
         inner join student s on sc.sno = s.sno
group by s.sname, sc.cno, sc.grade
having sc.grade > 80
order by sc.grade desc;
-- 13
select c.cno, c.semester
from course c
where c.semester = (select semester from course where cname = 'vb')
  and c.cname <> 'vb';
-- 14
select s.sname, s.sdept, s.sage
from student s
where s.sage = (select sage from student where sname = n'李勇')
  and s.sname <> n'李勇';
-- 15
select c.cno, c.cname
from course c
where c.cno not in (select sc.cno from sc sc);
--16
select s.sno,
       s.sname,
       cno=stuff((
                     select ',' + trim(c.cno)
                     from course c,
                          sc sc1
                     where s.sno = sc1.sno
                       and sc1.cno = c.cno
                     for xml path ('')), 1, 1, '')
from sc sc
         right join student s on sc.sno = s.sno
group by s.sno, s.sname;
-- 17
select s.sname
from student s
where s.sno not in (select sc.sno from sc sc);
-- 18
select
top 3
s.sname
,
s.sage
from student s
where s.sdept = n'计算机系'
order by s.sage;
-- 19
select
top 3
s.sno
,
s.sname
,
s.sdept
,
sc.grade
from course c
         inner join sc sc on c.cno = sc.cno
         inner join student s on sc.sno = s.sno
where c.cname = 'vb';
--20
select
top 2
sc.sno
,
count(sc.cno) as course_nums
from sc sc
group by sc.sno;