学生成绩表的常用查询

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将结果倒序按照从高分到低分排列*/