学生表/教师表/课程表/成绩表常见sql查询
1. 在表中插入符合主键 [sql] /*成绩表*/ create table sc ( sid int references student(sid), /*学生学号*/ cid int references course(cid), /*课程编号*/ score int not null, /*课程分数*/ primary key(sid,cid) /*将学生学号和课程编号设为复合主键*/ ) 2. 查询各科成绩最高分，最低分以及平均分 [sql] select c.cname, max(s.score) as max, min(s.score) as min, avg(s.score) as average from course c join sc s on c.cid = s.cid group by c.cname /*此处应注意，若不按照c.cname进行分组，sql语句会报错，c.cname在select语句中不合法，因为它 并未出现在聚合函数中也没有出现在group by语句中*/ 3. 查询平均成绩大于80分的学生姓名以及平均成绩 [sql] select sname, avg(score) as average from student join sc on student.sid=sc.sid group by sname having avg(score)>80 /*以聚合函数为条件进行删选只能在having语句中进行，where语句不支持聚合函数*/ 4. 查询各学生都选了多少门课 [sql] select sname, count(cid) as total_course from student left join sc on student.sid=sc.sid group by sname /*使用left join可以将一门课也没有选的学生也查询出来， 若不加left查不出daisy和sherry*/ 5. 查询没有选jane老师课的学生信息 [sql] select s.sid,s.sname,s.sage,s.sage from student s where s.sid not in (select s.sid from sc s join course c on s.cid=c.cid join teacher t on c.tid=t.tid where t.tname='jane') /*子查询中查询出所有选择jane老师课的学生学号， 主查询去查询在学生表中但不在子查询结果集中的学生信息*/ 6. 查询既选择了computer课程，又选择了math课程的学生信息 [sql] select s.sid,s.sname,s.sage,s.ssex from student s join sc ss on s.sid=ss.sid join course c on ss.cid=c.cid where c.cname='computer' intersect select s.sid,s.sname,s.sage,s.ssex from student s join sc ss on s.sid=ss.sid join course c on ss.cid=c.cid where c.cname='math' /*第一个查询查询出选择computer课程的学生信息， 第二个查询查询出选择math课程的学生信息， 用intersect关键字取交集*/ 7. 查询computer课程比math课程分数高的学生学号 [sql] select a.sid from (select s.sid,s.score from sc s join course c on s.cid=c.cid where c.cname='computer') a join (select s.sid,s.score from sc s join course c on s.cid=c.cid where c.cname='math') b on a.sid=b.sid where a.score>b.score /*将选了computer课的学生学号和成绩和选了math课的学生学号和成绩连接 where语句限制computer课的成绩高于math课*/ 8. 查询和john选的课相同的学生信息 [sql] select student.sname from student join sc on student.sid=sc.sid where sc.cid in (select sc.cid from sc join student on sc.sid=student.sid where student.sname='john') /*查询选了的课john也都选了的学生的姓名*/ and student.sname<>'john' /*限制该学生不能是john本人*/ group by student.sname having count(sc.cid)= (select count(*) from sc join student on sc.sid=student.sid where student.sname='john') /*该学生选的课程总数与john选的课程总数相同*/ 9. 按总分为学生排名，总分相同名次相同 [sql] select rank() over (order by sum(ss.score) desc) as rank, s.sname, isnull(sum(ss.score),0) from student s left join sc ss on s.sid = ss.sid group by s.sname order by sum(ss.score) desc /*rank()是sql server的一个built-in函数，语法为 rank() over ( [ partition_by_clause ] order_by_clause ).*/ 10. 查询总分在100至200之间的学生姓名及总分 [sql] select s.sname,sum(ss.score) from student s join sc ss on s.sid=ss.sid group by s.sname having sum(ss.score) between 100 and 200 11. 查询总分第六到十名的学生姓名以及总分 [sql] select * from (select top(5) * from (select top(10) sc.sid,sum(sc.score) as sum from sc group by sc.sid order by sum(sc.score)) a order by a.sum) b order by b.sum desc /*select top(10) sc.sid,sum(sc.score) as sum from sc group by sc.sid order by sum(sc.score)查询出总分前十名 select top(5) from (...) a order by a.sum查询出成绩六到十名 select * from (...) b order by b.sum desc将结果倒序按照从高分到低分排列*/ 12. 查询各科成绩的前三名以及分数 [sql] select s.sid,s.cid,s.score from sc s where s.score in (select top(3) score from sc where s.cid= cid order by score desc) order by s.cid; /*从sc表中查询出学生学号，课程编号以及成绩，where子句限制了查询出的记录成绩必须在子查询集合内 子查询查询出了各科成绩的前三名并通过课程编号和主查询关联*/ 13. 查询有不及格科目的学生的姓名，不及格科目以及不及格科目成绩 [sql] select s.sname,c.cname,ss.score from student s join sc ss on s.sid=ss.sid join course c on ss.cid=c.cid where ss.score<60 14. 查询所有学生都选修的课程 [sql] select c.cname from sc s join course c on s.cid=c.cid group by c.cname having count(s.sid)=(select count(*) from student) 15. 查询选修了两门或以上的学生姓名及选修总科目 [sql] select s.sname,count(ss.cid) as total from student s join sc ss on s.sid=ss.sid group by s.sname having count(ss.cid)>1