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

1. 在表中插入符合主键

[sql]

/*成绩表*/ 

CREATETABLESC 

( 

Sid INTREFERENCESStudent(Sid), /*学生学号*/ 

Cid INTREFERENCESCourse(Cid), /*课程编号*/ 

Score INTNOTNULL, /*课程分数*/ 

PRIMARYKEY(Sid,Cid) /*将学生学号和课程编号设为复合主键*/ 

) 

 

2. 查询各科成绩最高分，最低分以及平均分

[sql]

SELECTc.Cname, MAX(s.Score) ASMax, MIN(s.Score) ASMin, AVG(s.Score) ASAverage  

FROMCourse c JOINSC s ONc.Cid = s.Cid  

GROUPBYc.Cname  

/*此处应注意，若不按照c.Cname进行分组，SQL语句会报错，c.Cname在SELECT语句中不合法，因为它 

并未出现在聚合函数中也没有出现在GROUP BY语句中*/ 

 

 

3. 查询平均成绩大于80分的学生姓名以及平均成绩

[sql]

SELECTSname, AVG(Score) ASAverage FROMStudent JOINSC  

ONStudent.Sid=SC.Sid  

GROUPBYSname 

HAVINGAVG(Score)>80  

/*以聚合函数为条件进行删选只能在HAVING语句中进行，WHERE语句不支持聚合函数*/ 

 

 

4. 查询各学生都选了多少门课

[sql]

SELECTSname, COUNT(Cid) ASTOTAL_COURSE FROMStudent  

LEFTJOINSC ONStudent.Sid=SC.Sid 

GROUPBYSname 

/*使用LEFT JOIN可以将一门课也没有选的学生也查询出来， 

若不加LEFT查不出DAISY和SHERRY*/ 

 

 

5. 查询没有选JANE老师课的学生信息

[sql]

SELECTs.Sid,s.Sname,s.Sage,s.Sage FROMStudent s  

WHEREs.Sid NOTIN  

(SELECTs.Sid FROMSC s JOINCourse c ONs.Cid=c.Cid  

JOINTeacher t ONc.Tid=t.Tid  

WHEREt.Tname='JANE') 

/*子查询中查询出所有选择JANE老师课的学生学号， 

主查询去查询在学生表中但不在子查询结果集中的学生信息*/ 

 

 

6. 查询既选择了COMPUTER课程，又选择了MATH课程的学生信息

[sql]

SELECTs.Sid,s.Sname,s.Sage,s.Ssex FROMSTUDENT s  

JOINSC ss ONs.Sid=ss.Sid 

JOINCourse c ONss.Cid=c.Cid WHEREc.Cname='COMPUTER' 

INTERSECT 

SELECTs.Sid,s.Sname,s.Sage,s.Ssex FROMSTUDENT s  

JOINSC ss ONs.Sid=ss.Sid 

JOINCourse c ONss.Cid=c.Cid WHEREc.Cname='MATH' 

/*第一个查询查询出选择COMPUTER课程的学生信息， 

第二个查询查询出选择MATH课程的学生信息， 

用INTERSECT关键字取交集*/ 

 

 

7. 查询COMPUTER课程比MATH课程分数高的学生学号

[sql]

SELECTa.Sid FROM  

(SELECTs.Sid,s.Score FROMSC s JOINCourse c ONs.Cid=c.Cid WHEREc.Cname='COMPUTER') a 

JOIN 

(SELECTs.Sid,s.Score FROMSC s JOINCourse c ONs.Cid=c.Cid WHEREc.Cname='MATH') b 

ONa.Sid=b.Sid 

WHEREa.Score>b.Score 

/*将选了COMPUTER课的学生学号和成绩和选了MATH课的学生学号和成绩连接 

WHERE语句限制COMPUTER课的成绩高于MATH课*/ 

 

 

8. 查询和JOHN选的课相同的学生信息

[sql]

SELECTStudent.Sname FROMStudent JOINSC ONStudent.Sid=SC.Sid 

WHERESC.Cid IN 

(SELECTSC.Cid FROMSC JOINStudent ONSC.Sid=Student.Sid WHEREStudent.Sname='JOHN') /*查询选了的课JOHN也都选了的学生的姓名*/ 

ANDStudent.Sname<>'JOHN'/*限制该学生不能是JOHN本人*/ 

GROUPBYStudent.Sname  

HAVINGCOUNT(SC.Cid)= 

(SELECTCOUNT(*) FROMSC JOINStudent ONSC.Sid=Student.Sid WHEREStudent.Sname='JOHN') /*该学生选的课程总数与JOHN选的课程总数相同*/ 

 

 

9. 按总分为学生排名，总分相同名次相同

[sql]

SELECTRANK() OVER (ORDERBYSUM(ss.Score) DESC) ASRank, s.Sname,ISNULL(SUM(ss.Score),0)  

FROMStudent s LEFTJOINSC ss  

ONs.Sid= ss.Sid  

GROUPBYs.Sname  

ORDERBYSUM(ss.Score)DESC 

/*RANK()是SQL Server的一个built-in函数，语法为 

RANK() OVER ( [ partition_by_clause ] order_by_clause).*/ 

 

 

10. 查询总分在100至200之间的学生姓名及总分

[sql]

SELECTs.Sname,SUM(ss.Score) FROMStudent s JOINSC ss ONs.Sid=ss.Sid 

GROUPBYs.SnameHAVINGSUM(ss.Score)BETWEEN100 AND200 

 

 

11. 查询总分第六到十名的学生姓名以及总分

[sql]

SELECT* FROM 

(SELECTTOP(5) * FROM 

(SELECTTOP(10) SC.Sid,SUM(SC.Score) ASSUMFROMSC GROUPBYSC.Sid ORDERBYSUM(SC.Score)) a 

ORDERBYa.SUM)b 

ORDERBYb.SUMDESC 

/*SELECT TOP(10) SC.Sid,SUM(SC.Score) AS SUM FROM SCGROUP 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]

SELECTs.Sid,s.Cid,s.Score FROMSC s  

WHEREs.Score IN  

(SELECTTOP(3) Score FROMSC WHEREs.Cid= Cid ORDERBYscore DESC)  

ORDERBYs.Cid; 

/*从SC表中查询出学生学号，课程编号以及成绩，WHERE子句限制了查询出的记录成绩必须在子查询集合内 

子查询查询出了各科成绩的前三名并通过课程编号和主查询关联*/  

 

 

13. 查询有不及格科目的学生的姓名，不及格科目以及不及格科目成绩

[sql]

SELECTs.Sname,c.Cname,ss.Score FROMStudent s JOINSC ss ONs.Sid=ss.Sid JOINCourse c ONss.Cid=c.Cid 

WHEREss.Score<60 

 

 

14. 查询所有学生都选修的课程

[sql]

SELECTc.Cname FROMSC s JOINCourse c ONs.Cid=c.Cid  

GROUPBYc.CnameHAVINGCOUNT(s.Sid)=(SELECTCOUNT(*) FROMStudent) 

 

15. 查询选修了两门或以上的学生姓名及选修总科目

[sql]

SELECTs.Sname,COUNT(ss.Cid) ASTOTAL FROMStudent s JOINSC ss ONs.Sid=ss.Sid  

GROUPBYs.SnameHAVINGCOUNT(ss.Cid)>1