1、① 从学生表中查询学号的最后一位不是2,3,5的学生的情况。select *from studentwhere sno not like '%[235]'
2、① 查询无考试成绩的学生的学号和相应的课程号。select sno,cnofrom scwhere grade is null
3、① 查询选修了课程“c02”的学生学号及成绩,查询结果按成绩降序排列。select sno,gradefrom scwhere cno='c02'order by grade desc
4、① 查询学号为9512101的学生考试总成绩。select sum(grade) as '9512101总成绩'from scwhere sno='9512101'
5、① 查询选课门数大于等于4门的学生的学号、平均成绩和选课门数。select sno,count(*) as 选课门数,avg(grade) as 平均成绩from scgroup by snohaving count(*)>=4
6、① 查询没有选修全部课程的学生学号、姓名。select student.sno,sname,cou荏鱿胫协nt(sc.cno)as 选课门数from student left join sc on student.sno=sc.snogroup by student.sno,snamehaving count(sc.cno)<(select count(*)from course)注意:这样可以把没有选修的学生查询出来。
7、① 请分别用等值连接、join连接查询信息系选修VB课程的学生的成绩,要求列出学生姓名,课程名和成逼钽李沿绩。l等值连接select sname,cname,gradefrom sc,student,coursewhere sc.sno=student.sno and sc.cno=course.cno and course.Cname='VB' and student.Sdept='信息系'
8、lJOIN连接查询sele罕铞泱殳ct sname,cname,gradefrom sc join student on sc.sno=student.sno join course on sc.cno=course.cnowhere Cname='VB' and Sdept='信息系'① 查询与刘晨在同一个系的学生。select a.sname,a.sdeptfrom student a,student bwhere a.Sdept=b.Sdept and b.sname='刘晨' and a.sname!=b.sname
9、① 查询各科成绩前三名的记录:(不考虑成绩并列情况)(思路:利用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) tempwhere 排名<4