学生表/教师表/课程表/成绩表常见SQL查询

学生表/教师表/课程表/成绩表常见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