MySQL经典练习题（二）
1、查询Score表中的最高分的学生学号和课程号。
select sno,cno,degree
from score
where DEGREE = (select max(DEGREE)max_grade from score )
2、查询‘3-105’号课程的平均分。
select cno,avg(DEGREE) avg_grade
from score
where cno ='3-105'
3、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select cno,avg(degree) avg_grade
from score
where cno like "3%"
group by cno
having count(*)>5
4、查询最低分大于70，最高分小于90的Sno列。
-- 不去重，可能某个sno的学生的多门课程满足该条件
select sno
from score
group by degree
having min(degree) >70 and max(degree)<90
-- 去重，因为最终结果只是需要sno列，所以可以去重处理
select DISTINCT sno
from score
group by degree
having min(degree) >70 and max(degree)<90
5、查询所有学生的Sname、Cno和Degree列。
-- 方法一
select sname,cno,degree
from student s , score sc
where s.sno = sc.sno
-- 方法二
select sname,cno,degree
from student s inner join score sc
on s.sno = sc.sno
-- 方法三
select sname,cno,degree
from student s join score sc
on s.sno = sc.sno
6、查询所有学生的Sno、Cname和Degree列。
-- 方法一
select sno,cname,degree
from score s ,course c
where s.cno =c.cno
-- 方法二
select sno,cname,degree
from score s inner join course c
on s.cno =c.cno
-- 方法三
select sno,cname,degree
from score s join course c
on s.cno =c.cno
7、查询所有学生的Sname、Cname和Degree列。
-- 方法一
select sname,cname,degree
from student s , course c, score sc
where c.cno = sc.cno and sc.sno = s.sno
-- 方法二
select sname,cname,degree
from student s inner join course c inner join score sc
on c.cno = sc.cno and sc.sno = s.sno
-- 方法三
select sname,cname,degree
from student s join course c join score sc
on c.cno = sc.cno and sc.sno = s.sno
8、查询“95033”班所选课程的平均分。
-- 方法一
select class,avg(degree) avg_grade
from score sc ,student s
where sc.sno = s.sno and class ='95033'
-- 方法二
select class,avg(degree) avg_grade
from score sc inner join student s
on sc.sno = s.sno
where class ='95033'
-- 方法三
select class,avg(degree) avg_grade
from score sc join student s
on sc.sno = s.sno
where class ='95033'
-- 方法四
select class,avg(degree) avg_grade
from score sc join student s
on sc.sno = s.sno and class ='95033'
-- 方法五
select class,avg(degree) avg_grade
from score sc inner join student s
on sc.sno = s.sno and class ='95033'
9、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select *
from score
where cno ="3-105" and degree >(select max(degree) from score where sno ='109')
10、查询score中的同学中分数为非最高分成绩的记录。
-- 方法一
select *
from score
where degree != (select max(degree) from score )
-- 方法二
select *
from score
where degree <> (select max(degree) from score )
11、假设使用如下命令建立了一个grade表：
create table grade(low NUMERIC(10, 1),upp NUMERIC(10, 1),rank char(1));
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
现查询所有同学的Sno、Cno和rank列。
方法一：
select sno,cno,degree,rank
from score ,grade
where degree between low and upp;
-- 方法二：
select sno,cno,degree,rank
from score inner join grade
on degree between low and upp;
-- 方法三：
select sno,cno,degree,rank
from score join grade
on degree between low and upp;
-- 方法四：
select sno,cno,degree,rank
from score join grade
on degree >= low and degree <= upp;
-- 方法五：
select sno,cno,degree,rank
from score,grade
where degree >= low and degree <= upp;
-- 方法六：
select sno,cno,degree,rank
from score inner join grade
on degree >= low and degree <= upp;
