《SQL练习题》.doc

SQL 练习题 一 学生 – 课程数据库 1 查询 7号课程没有考试成绩的学生学号 select sno from sc where cno=’7’ and grade is not null 2 查询 7号课程成绩在90分以上或60分以下的学生学号 select sno from sc where grade90 or grade60 3 查询课程名以“数据”两个字开头的所有课程的课程号和课程名。 Select cno,cname from c where cname like ‘数据%’ 4 查询每个学生所有课程的平均成绩，输出学生学号、平均成绩 select sno,avg(grade) from sc group by sno 5 查询每门课程的选修人数，输出课程号、选修人数。 Select cno,count(*) from sc group by cno 6 查询选修 7号课程的学生的学号、姓名、性别。 Select s.sno, sname,ssex from s , sc where s.sno=sc.sno and cno = ‘7’ 7 查询选修7号课程学生的平均年龄。 Select avg(sage) from s , sc where s.sno=sc.sno and cno = ‘7’ 8 查询由30名以上学生选修的课程号。 Select sno from sc group by cno having count(*)30 9 查询至今没有考试不及格的学生学号 a: select sno from s where sno not in ( select sno from sc where grade60 ) b: select sno from sc group by sno having min(grade)=60 c: select sno from student where not exist ( Select * from sc where grade60 and sc.sno=student.sno ) d: Select sno from student except Select sno from sc where grade60 二 1 找出选修课程号为 C2 的学生学号与成绩。 Select sno,grade from sc where cno=’C2’ 2 找出选修课程号为C4 的学生学号与姓名。 Select s.sno , sname from s,sc where s.sno=sc.sno and cno=’C4’ 3 找出选修课程名为 Maths 的学生学号与姓名。 Select s.sno ,sname from s,sc,c where s.sno=sc.sno and c.cno=sc.cno and cname = ‘Maths’ 4找出选修课程号为C2或C4 的学生学号。 Select distinct sno from sc where cno in (‘C2’,’C4’) 或： Select distinct sno from sc where cno=’C2’ or cno =’C4’ 5找出选修课程号为C2和C4 的学生学号。 Select sno from sc where cno =’C2’ and sno in ( select sno from sc where cno = ‘C4’ ) 6 找出不学C2课程的学生姓名和年龄 select sname , sage from s where sno not in ( select sno from sc where cno=’C2’ ) 或： select sname , sage from s where not exists ( select * from sc where sc.sno=s.sno and cno=’C2’ ) 7 找出选修了数据库课程的所有学生姓名。(与3同) Select s.sno ,sname from s,sc,c where s.sno=sc.sno and c.cno=sc.cno and cname = ‘数据库’ 8 找出数据库课程不及格的女生姓名 嵌套： select sname from s where ssex = ‘女’ and sno in ( select sno from sc where grade60 and cno in ( select cno from c where cname=’数据库’