目录
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_avgAsSelect Sdept,avg(Grade) as '平均成绩' from Student,ScGroup 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 StudentGroup 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 StudentGroup by Sdept Order by Sdept
5、按系统计各系学生的平均年龄,结果按降序排列
Select Sdept,min(Sage) as '平均年龄'from StudentGroup 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,gradefrom 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,gradefrom student as s,Sc,Course as cwhere 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.snoGroup 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 cwhere 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.Snogroup 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.Snogroup 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 NULLgroup 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 or grade is null) group by s.Sno) group by s.sno order by s.sno
16、 查询选修课程的平均成绩在80分以上的学生的学号
Select s.sno from Student as s left join Sc on Sc.Sno = s.Sno where Grade is not NULLgroup by s.sno having avg(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 nullgroup by s.Sdept order by count(s.sno) desc
18、把选修了课程名为“数据库”的学生的各门课成绩提高10%
Select *from ScUpdate Sc Set grade = grade * 1.1Where cno in (select cno from course Where cname = '数据库')
执行之前
执行之后
19、把选修了“2”号课程,且成绩低于该门课程的平均成绩的学生成绩删除掉
Delete from scWhere grade < (select avg(grade) from course,Sc Where course.Cno = 2)Select *from Sc