①从学生表中查询学号的最后一位不是2，3，5的学生的情况。

select *

from student

where sno not like %

①查询无考试成绩的学生的学号和相应的课程号。

select sno,cno

from sc

where grade is null

①查询选修了课程“c02”的学生学号及成绩，查询结果按成绩降序排列。

select sno,grade

from sc

where cno=c02

order by grade desc

①查询学号为9512101的学生考试总成绩。

select sum(grade) as 9512101总成绩

from sc

where sno=9512101

①查询选课门数大于等于4门的学生的学号、平均成绩和选课门数。

select sno,count(*) as 选课门数,avg(grade) as 平均成绩

from sc

group by sno

having count(*)=4

①查询没有选修全部课程的学生学号、姓名。

select student.sno,sname,count(sc.cno)as 选课门数

from student left join sc on student.sno=sc.sno

group by student.sno,sname

having count(sc.cno)(select count(*)from course)

注意：这样可以把没有选修的学生查询出来。

①请分别用等值连接、join连接查询信息系选修VB课程的学生的成绩，要求列出学生姓名，课程名和成绩。

l等值连接

select sname,cname,grade

from sc,student,course

where sc.sno=student.sno and sc.cno=course.cno and course.Cname=VB and student.Sdept=信息系

lJOIN连接查询

select sname,cname,grade

from sc join student on sc.sno=student.sno join course on sc.cno=course.cno

where Cname=VB and Sdept=信息系

①查询与刘晨在同一个系的学生。

select a.sname,a.sdept

from student a,student b

where a.Sdept=b.Sdept and b.sname=刘晨 and a.sname!=b.sname

①查询各科成绩前三名的记录:(不考虑成绩并列情况)（思路：利用row_number()over(partition by 列名 order by 列名 desc)进行分组编号）

select *

from (select cno,sno,row_number()over(partition by cno order by grade desc) as 排名 from sc) temp

where 排名4