1 create database xuexiao
2 go
3
4 use xuexiao
5 go
6
7 --表一:学生表 student
8 --学号:code int （主键）从1开始
9 --姓名:name varchar(50)
10 --性别:sex char(10)
11 --班级:banji char(10)
12 --语文教师编号:yujiao int
13 --数学教师编号:yujiao int
14 --英语教师编号:yujiao int
15 create table student
16 (
17 scode int primary key identity(10001,1)not null,--主键
18 name varchar(50),
19 sex char(10),
20 banji char(10),
21 yujiao int ,--外键
22 shujiao int,--外键
23 yingjiao int,--外键
24 )
25 --插入学生表数据
26 --一班数据
27 insert into student values('王红','女','一班',1001,1004,1007)
28 insert into student values('周瑜','男','一班',1001,1004,1007)
29 insert into student values('张飞','男','一班',1001,1004,1007)
30 insert into student values('乔雨','女','一班',1001,1004,1007)
31 insert into student values('王发','女','一班',1001,1004,1007)
32 --二班数据
33 insert into student values('杜鹃','女','二班',1002,1005,1008)
34 insert into student values('白小飞','男','二班',1002,1005,1008)
35 insert into student values('刘小峰','男','二班',1002,1005,1008)
36 insert into student values('张强','男','二班',1002,1005,1008)
37 insert into student values('周莹莹','女','二班',1002,1005,1008)
38 --三班数据
39 insert into student values('赵晓红','女','三班',1003,1006,1009)
40 insert into student values('王国章','男','三班',1003,1006,1009)
41 insert into student values('张三更','男','三班',1003,1006,1009)
42 insert into student values('潘晓丽','女','三班',1003,1006,1009)
43 insert into student values('楚红红','女','三班',1003,1006,1009)
44
45 --表二:教师表 teacher
46 --教师编号:code int （主键） 从1001开始
47 --负责课程:lesson char(10)（语文、数学、英语）
48 --年龄:age int
49 --生日:birth datetime
50 create table teacher
51 (
52 tcode int primary key identity(1001,1)not null,--主键
53 tname varchar(20),
54 lesson char(10),
55 age int,
56 birth datetime,
57 )
58 insert into teacher values('张斌','语文',30,1986-3-30)
59 insert into teacher values('齐红','语文',40,1976-9-20)
60 insert into teacher values('王雪丽','语文',25,1991-2-3)
61
62 insert into teacher values('崔刚','数学',36,1980-2-10)
63 insert into teacher values('余兵','数学',30,1986-6-30)
64 insert into teacher values('刘备','数学',50,1966-7-20)
65
66 insert into teacher values('张灿灿','英语',30,1986-5-5)
67 insert into teacher values('王丽丽','英语',26,1990-7-7)
68 insert into teacher values('张婷婷','英语',35,1981-6-3)
69
70 --表三：分数表 score
71 --学号：code int （学生学号的外键）
72 --语文分数：yufen decimal（18,2）
73 --数学分数：shufen decimal（18,2）
74 --英语分数：yingfen decimal（18,2）
75
76 create table score
77 (
78 fcode int ,--外键
79 yufen decimal(18,2),
80 shufen decimal(18,2),
81 yingfen decimal(18,2),
82 )
83 --插入分数数据
84 --一班数据
85 insert into score values(10001,80,90,70)
86 insert into score values(10002,50,80,60)
87 insert into score values(10003,60,55,80)
88 insert into score values(10004,70,90,55)
89 insert into score values(10005,50,70,80)
90 --二班数据
91 insert into score values(10006,90,60,80)
92 insert into score values(10007,60,50,66)
93 insert into score values(10008,70,82,59)
94 insert into score values(10009,63,87,85)
95 insert into score values(10010,45,55,64)
96 --三班分数
97 insert into score values(10011,90,90,70)
98 insert into score values(10012,60,80,70)
99 insert into score values(10013,55,70,56)
100 insert into score values(10014,70,80,70)
101 insert into score values(10015,85,55,70)
102
103
104 --查询此次语文成绩最高的学生的信息
105 select * from student where scode=
106 (
107 select top 1 fcode from score order by yufen desc
108 )
109 --查询此次数学成绩最高的学生的信息
110 select * from student where scode=
111 (select top 1 fcode from score order by shufen desc)
112
113 --查询此次英语成绩最高的学生的信息
114 select *from student where scode=
115 (select top 1 fcode from score order by yingfen desc)
116
117 --查询此次语文成绩最低的学生的信息
118 select *from student where scode=
119 (
120 select top 1 fcode from score order by yufen
121 )
122 --查询此次数学成绩最低的学生的信息
123 select * from Student where scode=
124 (select top 1 fcode from score order by shufen)
125 --查询此次英语成绩最低的学生的信息
126 select * from student where scode =
127 (select top 1 fcode from score order by yingfen )
128 --查询此次语文成绩最低的学生所任课教师的信息
129 select * from teacher where tcode=
130 (select yujiao from student where scode=
131 (select top 1 fcode from score order by yufen)
132 )
133
134 --查询此次数学成绩最低的学生所任课教师的信息
135 select * from teacher where tcode=
136 (select shujiao from student where scode=
137 (select top 1 fcode from score order by shufen)
138 )
139
140
141 --查询此次英语成绩最低的学生所任课教师的信息
142 select * from teacher where tcode=
143 (select yingjiao from student where scode=
144 (select top 1 fcode from score order by yingfen)
145 )
146
147 --查询此次语文成绩最高的学生所任课教师的信息
148 select * from teacher where tcode=
149 (select yujiao from student where scode=
150 (select top 1 fcode from score order by yufen desc)
151 )
152 --查询此次数学成绩最高的学生所任课教师的信息
153 select * from teacher where tcode=
154 (select shujiao from student where scode=
155 (select top 1 fcode from score order by shufen desc)
156 )
157 --查询此次英语成绩最高的学生所任课教师的信息
158 select * from teacher where tcode=
159 (select yingjiao from student where scode=
160 (select top 1 fcode from score order by yingfen desc)
161 )
162 --查询学生信息，将所有语文任课教师编号改为该科目的任课教师名字显示
163 select student.scode,name,sex,banji,teacher.tname from student
164 join teacher on student.yujiao=teacher.tcode
165
166 --查询学生信息，将所有任课教师编号改为该科目的任课教师名字显示
167 select scode,name,sex,banji,
168 (select tname from teacher where student.yujiao=teacher.tcode) as 语文,
169 (select tname from teacher where student.shujiao=teacher.tcode) as 数学,
170 (select tname from teacher where student.yingjiao=teacher.tcode) as 英语
171 from student
172
173
174 select student.scode,name,sex,banji,teacher.tname,
175 (select tname from teacher where student.shujiao=teacher.tcode) as 数学,
176 (select tname from teacher where student.yingjiao=teacher.tcode) as 英语
177 from student
178 join teacher on student.yujiao=teacher.tcode
179
180
181
182 --查询各个学生的学号,姓名,语文分数,数学分数,英语分数,以及三门课里面每一门课的任课教师姓名
183 select scode,name,banji,
184 (select yufen from score where student.scode=score.fcode) as 语文分数,
185 (select tname from teacher where student.yujiao=teacher.tcode) as 语文教师,
186 (select shufen from score where student.scode=score.fcode) as 数学分数,
187 (select tname from teacher where student.shujiao=teacher.tcode) as 数学教师,
188 (select yingfen from score where student.scode=score.fcode) as 英语分数,
189 (select tname from teacher where student.yingjiao=teacher.tcode) as 英语教师
190 from student
191
192 --查询每个班级里的语文最高分
193 --一班
194 select top 1 yufen from score where fcode in
195 (select scode from student where banji ='一班')order by yufen desc
196 --二班
197 select top 1 yufen from score where fcode in
198 (select scode from student where banji ='二班班')order by yufen desc
199 --三班
200 select top 1 yufen from score where fcode in
201 (select scode from student where banji ='三班')order by yufen desc
202
203 --查询每个班级里的数学最高分
204 --一班
205 select top 1 shufen from score where fcode in
206 (select scode from student where banji ='一班')order by shufen desc
207 --二班
208 select top 1 shufen from score where fcode in
209 (select scode from student where banji ='二班')order by shufen desc
210 --三班
211 select top 1 shufen from score where fcode in
212 (select scode from student where banji ='三班')order by shufen desc
213
214
215 --查询每个班级里的英语最高分
216 --一班
217 select top 1 yingfen from score where fcode in
218 (select scode from student where banji ='一班')order by yingfen desc
219 --二班
220 select top 1 yingfen from score where fcode in
221 (select scode from student where banji ='二班')order by yingfen desc
222 --三班
223 select top 1 yingfen from score where fcode in
224 (select scode from student where banji ='三班')order by yingfen desc
225
226 --查看每个班的语文平均分
227 select AVG(yufen) as 一班 from score where fcode in
228 (select scode from student where banji='一班')
229
230 select AVG(yufen) as 二班 from score where fcode in
231 (select scode from student where banji='二班')
232
233 select AVG(yufen) as 三班 from score where fcode in
234 (select scode from student where banji='三班')
235 --查询语文课程平均分最高的班级的语文教师的信息
236 declare @a decimal(18,2)
237 select @a=AVG(yufen) from score where fcode in
238 (select scode from student where banji='一班')
239 declare @b decimal(18,2)
240 select @b=AVG(yufen) from score where fcode in
241 (select scode from student where banji='二班')
242 declare @c decimal(18,2)
243 select @c =AVG(yufen) from score where fcode in
244 (select scode from student where banji='三班')
245 declare @jie varchar(20)
246 if @a>@b and @a>@c
247 set @jie='一班'
248 else if @b>@a and @b>@c
249 set @jie='二班'
250 else if @c>@a and @c>@b
251 set @jie='三班'
252
253 select * from teacher where tcode in
254 (select yujiao from student where banji=@jie)
255
256 --查询数学课程平均分最高的班级的数学教师的信息
257 declare @x decimal(18,2)
258 select @x=AVG(shufen) from score where fcode in
259 (select scode from student where banji='一班')
260 declare @y decimal(18,2)
261 select @y=AVG(shufen) from score where fcode in
262 (select scode from student where banji='二班')
263 declare @z decimal(18,2)
264 select @z =AVG(shufen) from score where fcode in
265 (select scode from student where banji='三班')
266 declare @guo varchar(20)
267 if @x>@y and @x>@z
268 set @guo ='一班'
269 else if @y>@x and @y>@z
270 set @guo ='二班'
271 else if @z>@x and @z>@y
272 set @guo ='三班'
273
274 select * from teacher where tcode in
275 (select shujiao from student where banji=@guo)
276
277 --查询英语课程平均分最高的班级的英语教师的信息
278 declare @x decimal(18,2)
279 select @x=AVG(yingfen) from score where fcode in
280 (select scode from student where banji='一班')
281 declare @y decimal(18,2)
282 select @y=AVG(yingfen) from score where fcode in
283 (select scode from student where banji='二班')
284 declare @z decimal(18,2)
285 select @z =AVG(yingfen) from score where fcode in
286 (select scode from student where banji='三班')
287 declare @guo varchar(20)
288 if @x>@y and @x>@z
289 set @guo ='一班'
290 else if @y>@x and @y>@z
291 set @guo ='二班'
292 else if @z>@x and @z>@y
293 set @guo ='三班'
294
295 select * from teacher where tcode in
296 (select yingjiao from student where banji=@guo)
