|
目录
1、求各系学生的平均成绩，并把结果存入新建的数据库表中（请自己创建一个表存放结果）
2、统计每门课程的选修学生人数及各门课程的平均成绩
3、找出各系年龄最大的学生，显示其学号、姓名；（利用相关子查询来实现）
或：
4、统计各系学生的人数，结果按升序排列
5、按系统计各系学生的平均年龄，结果按降序排列
6、统计无先修课的课程的学分总数
7、统计每位学生选修课程的门数、总学分及其平均成绩
8、查询每位选修了课程的学生的学号，姓名，课程号，课程名，成绩
9、找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩升序排列
10、查询选修了“1”或“2”号课程的学生学号和姓名
11、查询选修了课程名为“数据库”且成绩在60分以下的学生的学号、姓名和成绩
12、查询没有选修课程的学生的基本信息
13、查询选修了3门及以上课程的学生学号
14、查询选修课程成绩至少有一门在80分以上的学生学号
15、查询选修课程成绩都在80分以上的学生的学号
16、 查询选修课程的平均成绩在80分以上的学生的学号
17、按系别统计各系平均成绩在80分以上的人数，结果按降序排列
18、把选修了课程名为“数据库”的学生的各门课成绩提高10%
19、把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉
初始数据：
1、求各系学生的平均成绩，并把结果存入新建的数据库表中（请自己创建一个表存放结果）
Create view ss_avg
As
Select Sdept,avg(Grade) as '平均成绩' from Student,Sc
Group by Sdept
2、统计每门课程的选修学生人数及各门课程的平均成绩
Select cno,avg(grade) as '平均成绩',count (sno) as '选课人数' from Sc
Group by cno Order by cno
3、找出各系年龄最大的学生，显示其学号、姓名；（利用相关子查询来实现）
Select Sno,Sdept,Sname,max(Sage) as '年龄最大'from Student
Group by Sdept,Sno,Sname Order by Sdept,Sno,Sname
或：
Select Sno,Sname
from Student a where Sage = (select max(Sage) from Student b where a.Sdept = b.Sdept)
4、统计各系学生的人数，结果按升序排列
Select Sdept,count(Sno) as '学生人数'from Student
Group by Sdept Order by Sdept
5、按系统计各系学生的平均年龄，结果按降序排列
Select Sdept,min(Sage) as '平均年龄'from Student
Group by Sdept,Sage Order by Sage desc
6、统计无先修课的课程的学分总数
Select sum(Credit) as '学分总数' From Course where Cpon is null
Group by Cpon Order by Cpon
7、统计每位学生选修课程的门数、总学分及其平均成绩
Select sno,avg(grade) as '平均成绩',sum (Credit) as '总学分',count(Sc.Cno) as '选课门数' From Sc,Course
Where Course.Cno = Sc.Cno //Where这一句很重要！
Group by sno Order by sno
8、查询每位选修了课程的学生的学号，姓名，课程号，课程名，成绩
Select s.sno,sname,sc.cno,cname,grade
from student as s right join Sc
on s.sno = sc.Sno join Course as c on sc.cno = c.cno
或：
Select s.sno,sname,sc.cno,cname,grade
from student as s,Sc,Course as c
where s.sno = sc.Sno and sc.cno = c.cno
或：
Select Sc.sno as '学号',Student.sname as '姓名',Sc.cno as '课程号',Course.cname as '课程名',Sc.grade as '成绩'
From Course,Student,Sc
Where Sc.sno = Student.sno and Sc.cno = Course.cno //这一步很重要!
Group by Student.sname,Sc.Sno,Sc.cno,Course.cname,Sc.grade Order by Student.sname,Sc.Sno,Sc.cno,Course.cname,Sc.grade
9、找出平均成绩在85分以上的学生，结果按系分组，并按平均成绩升序排列
Select S.Sdept,avg(grade) as '平均成绩',S.Sname as '姓名'
from Student as s left join Sc on s.sno = Sc.sno
group by Sdept,S.Sname having avg(grade)>85 order by avg(grade)
10、查询选修了“1”或“2”号课程的学生学号和姓名
Select s.sno,s.Sname from Student as s,Sc
where (Sc.Cno = 1 or Sc.Cno = 2) and s.sno = Sc.sno
Group by s.sno,s.Sname Order by s.sno,s.Sname
11、查询选修了课程名为“数据库”且成绩在60分以下的学生的学号、姓名和成绩
Select s.sno,s.Sname,Sc.Grade from Student as s,Sc,Course as c
where c.Cname = '数据库' and grade > 85 and Sc.Cno = c.Cno and Sc.Sno = s.Sno
12、查询没有选修课程的学生的基本信息
Select s.sno,s.Sname from Student as s
left join Sc on Sc.Sno = s.Sno
group by s.sno,s.Sname having count(Cno) = 0 order by s.sno,s.Sname
13、查询选修了3门及以上课程的学生学号
Select s.sno,s.Sname from Student as s
left join Sc on Sc.Sno = s.Sno
group by s.sno,s.Sname having count(Cno) > 2 order by s.sno,s.Sname
14、查询选修课程成绩至少有一门在80分以上的学生学号
Select s.sno from Student as s
left join Sc on Sc.Sno = s.Sno
where Grade > 80 and Grade is not NULL
group by s.sno order by s.sno
15、查询选修课程成绩都在80分以上的学生的学号
Select s.sno from Student as s
left join Sc on Sc.Sno = s.Sno
where S.Sno not in (select s.Sno from Student as s left join Sc on s.Sno = Sc.Sno where (grade 80 order by s.sno
17、按系别统计各系平均成绩在80分以上的人数，结果按降序排列
distinct：唯一不同，即一样的只算一次
Select s.Sdept,count(distinct s.sno) as '人数' from Student as s
left join Sc on Sc.Sno = s.Sno where Grade is not null
group by s.Sdept order by count(s.sno) desc
18、把选修了课程名为“数据库”的学生的各门课成绩提高10%
Select *from Sc
Update Sc Set grade = grade * 1.1
Where cno in (select cno from course Where cname = '数据库')
执行之前
执行之后
19、把选修了“2”号课程，且成绩低于该门课程的平均成绩的学生成绩删除掉
Delete from sc
Where grade < (select avg(grade) from course,Sc Where course.Cno = 2)
Select *from Sc
对于连接：无论是right join 还是left join，规律是“右边补null”！
对于group by x,y order by x,y可以理解为按照(x,y)这个的组合来进行分组排序！
Count会计算值为NULL的Grade，Avg(Grade)不会！
|