sql练习题1

http://blog.youkuaiyun.com/mrbcy/article/details/68965271#t22
11、查询‘3-105’号课程的平均分。

select avg(degree) as 平均值 from scores where cno ='3-105';

12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

select avg(degree) from scores where cno like '3%' group by cno having count(sno) >= 5;

13、查询最低分大于70，最高分小于90的Sno列。

select * from scores where degree > 70 and degree < 90;

14、查询所有学生的Sname、Cno和Degree列。

 select students.sname,scores.cno,scores.degree from students inner join scores on (students.sno = scores.sno) order by sname;

15、查询所有学生的Sno、Cname和Degree列。

select sno,cname,degree from courses inner join scores on (courses.cno = scores.cno)order by sno;

16、查询所有学生的Sname、Cname和Degree列。

mysql> select sname,cname,degree from students inner join scores on (students.sno = scores.sno) inner join courses on(courses.cno = scores.cno);

17、查询“95033”班所选课程的平均分。

select cname,avg(degree) from students inner join scores on (students.sno = scores.sno) inner join courses on(courses.cno = scores.cno) where class ='95033' group by courses.cno;

18、假设使用如下命令建立了一个grade表：
create table grade(low number(3,0),upp number(3),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’);
commit;
现查询所有同学的Sno、Cno和rank列。

select sno,cno,rank from scores inner join grade on (scores.degree >= grade.low and scores.degree <= grade.upp);

19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

select s1.sno,s1.degree from scores as s1 inner join scores as s2 on (s1.cno = s2.cno and s1.degree > s2.degree) where s1.cno='3-105' and s2.sno ='109' order by s1.sno;

20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。

select * from scores group by sno having count(sno)>1 and degree != max(degree);