|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140 |
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 |
学生表/教师表/课程表/成绩表常见SQL查询,布布扣,bubuko.com
原文：http://www.cnblogs.com/llkey/p/3595254.html