欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

sql练习

程序员文章站 2022-05-28 21:33:11
直接上源码 1 #*****************************创建数据库***************************** 2 -- student(学生表) 3 create table student( 4 sno int primary key not null, -- ......

直接上源码

sql练习
  1 #*****************************创建数据库*****************************
  2 -- student(学生表)
  3 create table student(
  4     sno int primary key not null,  -- 序号(主键),不可为空
  5     sname varchar(20) not null,  -- 姓名
  6     ssex varchar(20) not null, -- 学月
  7     class varchar(20), -- 班级生性别
  8     sbirthday date -- 学生出生年
  9 );
 10 
 11 -- teacher 
 12 create table teacher(
 13     tno int primary key not null,  -- 教工编号(主键)
 14     tname varchar(20) not null,  -- 教工姓名
 15     tsex varchar(10) not null,  -- 教工性别
 16     tbirthday date,  -- 教工出生年月
 17     prof varchar(20), -- 教工职称
 18     depart varchar(20) not null -- 教工所在部门的名称
 19 );
 20 
 21 -- course(课程表)
 22 create table course(
 23     cno int primary key not null, -- 课程号(主键)
 24     cname varchar(20) not null, -- 课程名称
 25     tno int  -- 教工编号(外键)    
 26 );
 27 
 28 -- 成绩表
 29 create table score(
 30     sno int, -- 学号,外键
 31     cno int, -- 课程号,外键
 32     degree decimal(4, 1) -- 成绩
 33 );
 34 
 35 -- 给course(课程表)中的tno添加外键(teacher表中的tno)
 36 alter table course add constraint fk_tno foreign key(tno) references teacher(tno);
 37 
 38 -- 给score(成绩)表中的sno添加外键(student中的sno)
 39 alter table score add constraint fk_sno foreign key(sno) references student(sno);
 40 -- 给score(成绩)表中的cno添加外键(course中的cno)
 41 alter table score add constraint fk_cno foreign key(cno) references course(cno);
 42 -- 给score(成绩)表中的sno和cno添加联合主键
 43 alter table score add constraint pk_sno_cno primary key(sno, cno);
 44 
 45 -- 为student表添加记录
 46 insert student(sno, sname, ssex, sbirthday, class)
 47  values(108, '曾华', '男', '1977-09-01', 95033);
 48 insert student(sno, sname, ssex, sbirthday, class) 
 49 values(105, '匡明', '男', '1975-10-02', 95031);
 50 insert student(sno, sname, ssex, sbirthday, class) 
 51 values(107, '王丽', '女', '1976-01-23', 95033);
 52 insert student(sno, sname, ssex, sbirthday, class) 
 53 values(101, '李军', '男', '1976-02-20', 95033);
 54 insert student(sno, sname, ssex, sbirthday, class) 
 55 values(109, '王芳', '女', '1975-02-10', 95031);
 56 insert student(sno, sname, ssex, sbirthday, class) 
 57 values(103, '陆君', '男', '1974-06-03', 95031);
 58 
 59 -- 为teacher表添加记录
 60 insert teacher(tno, tname, tsex, tbirthday, prof, depart) values(
 61 804, '李诚', '男', '1958-12-02', '教授', '计算机系');
 62 insert teacher(tno, tname, tsex, tbirthday, prof, depart) values(
 63 856, '张旭', '男', '1969-03-12', '副教授', '电子工程系');
 64 insert teacher(tno, tname, tsex, tbirthday, prof, depart) values(
 65 825, '王萍', '女', '1972-12-02', '副教授', '计算机系');
 66 insert teacher(tno, tname, tsex, tbirthday, prof, depart) values(
 67 831, '刘冰', '女', '1978-12-02', '讲师', '电子工程系');
 68 
 69 -- 为course表添加记录
 70 insert course(cno, cname, tno) values (3105, '计算机导论', 825);
 71 insert course(cno, cname, tno) values (3245, '操作系统', 804);
 72 insert course(cno, cname, tno) values (3166, '数字电路', 856);
 73 insert course(cno, cname, tno) values (9888, '高等数学', 831);
 74 
 75 -- 为score表添加记录
 76 insert score(sno, cno, degree) values(103, 3245, 86);
 77 insert score(sno, cno, degree) values(105, 3245, 75);
 78 insert score(sno, cno, degree) values(109, 3245, 68);
 79 insert score(sno, cno, degree) values(103, 3105, 92);
 80 insert score(sno, cno, degree) values(105, 3105, 88);
 81 insert score(sno, cno, degree) values(109, 3105, 76);
 82 insert score(sno, cno, degree) values(101, 3105, 64);
 83 insert score(sno, cno, degree) values(107, 3105, 91);
 84 insert score(sno, cno, degree) values(108, 3105, 78);
 85 insert score(sno, cno, degree) values(101, 3166, 85);
 86 insert score(sno, cno, degree) values(107, 3166, 79);
 87 insert score(sno, cno, degree) values(108, 3166, 81);
 88 
 89 #*****************************查询数据库*****************************
 90 -- 1    查询Student表中的所有记录的Sname、Ssex和Class列。
 91 select sname, ssex, class from student;
 92 
 93 -- 2    查询教师所有的单位即不重复的Depart列。
 94 select distinct depart from teacher;
 95 
 96 -- 3    查询Student表的所有记录。
 97 select * from student;
 98 
 99 -- 4    查询Score表中成绩在60到80之间的所有记录。
100 select * from score where degree between 60 and 80;
101 
102 -- 5    查询Score表中成绩为85,86或88的记录。
103 select * from score where degree in (85, 86, 88);
104 
105 -- 6    查询Student表中“95031”班或性别为“女”的同学记录。
106 select * from student where class = 95031
107 union
108 select * from student where ssex = '女';
109 
110 -- 7    以Class降序查询Student表的所有记录。
111 select * from student order by class desc;
112 
113 -- 8    以cno升序、Degree降序查询Score表的所有记录。
114 select * from score order by cno asc, degree desc;
115 
116 -- 9    查询“95031”班的学生人数。
117 select count(sno) from student where class = 95031;
118 
119 -- 10    查询Score表中的最高分的学生学号和课程号。(子查询或者排序)
120 -- 排序法
121 select sno, cno from score order by degree desc limit 0, 1;
122 -- 子查询法
123 select sno, cno from score where degree = ( select max(degree) from score);
124 
125 -- 11    查询每门课的平均成绩。
126 select avg(degree) from score group by cno;
127 
128 -- 12    查询Score表中至少有5名学生选修的并以31开头的课程的平均分数。
129 select avg(degree) from score where cno like '31%' and exists
130 (select *, count(sno) count_sno from score group by cno having count_sno > 4)
131 
132 -- 13    查询分数大于70,小于90的Sno列。
133 select sno from score where degree between 70 and 90;
134 
135 -- 14    查询所有学生的Sname、Cno和Degree列。
136 select sname, cno, degree from student, score where student.sno = score.sno;
137 
138 -- 15    查询所有学生的Sno、Cname和Degree列。
139 select sno, cname, degree from score, course where score.cno = course.cno;
140 
141 -- 16    查询所有学生的Sname、Cname和Degree列。
142 select sname, cname, degree from student, course, score where 
143 student.sno = score.sno and course.cno = score.cno;
144 
145 -- 17    查询“95033”班学生的平均分。
146 -- 联合查询
147 select avg(degree) from score, student where 
148 score.sno = student.sno and class = 95033
149 -- 子查询
150 select avg(degree) from score where sno in
151 (select sno from student where class = 95033)
152 
153 -- 18    假设使用如下命令建立了一个grade表:
154 create table grade(
155 low  int(3),
156 upp  int(3),
157 rank  char(1)
158 );
159 insert into grade values(90,100, 'A');
160 insert into grade values(80,89, 'B');
161 insert into grade values(70,79,'C');
162 insert into grade values(60,69,'D');
163 insert into grade values(0,59,'E');
164 -- 现查询所有同学的Sno、Cno和rank列。
165 select sno, cno, rank from score, grade where degree > low and degree < upp;
166 
167 -- 19    查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
168 -- 子查询
169 select * from student where sno in
170 (select sno from score where cno = 3105 and degree > 
171 (select degree from score where sno = 109 and cno = 3105)
172 );
173 -- 联合查询
174 select student.* from student, score where student.sno = score.sno and cno = 3105 
175 and degree > (select degree from score where sno = 109 and cno = 3105);
176 
177 -- 20    选了3门课程并且是这个课程下不是最高分的学生的信息
178 select student.*, cno, degree from student, score 
179 where student.sno = score.sno 
180 and cno in 
181 (select cno from score group by cno having count(sno) = 3) 
182 and degree not in
183 (select max(degree) from score group by cno)
184 
185 -- 21    查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
186 select * from student where sno in (
187 select sno from score where cno = 3105 and degree > (
188 select degree from score where sno = 109 and cno = 3105
189 )
190 )
191 
192 -- 22    查询和学号为108、101的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
193 select sno, sname, sbirthday from student where year(sbirthday) in (
194 select year(sbirthday) from student where sno = 108 or sno = 101
195 )
196 
197 -- 23    查询“张旭“教师任课的学生成绩。
198 select degree from score where cno = (
199 select cno from course where tno = (
200 select tno from teacher where tname = '张旭'
201 )
202 )
203 
204 -- 24    查询选修某课程的同学人数多于5人的教师姓名。
205 select tname from teacher where tno in (
206 select tno from course where cno in(
207 select cno from score group by cno  having count(sno) > 5
208 )
209 );
210 
211 -- 25    查询95033班和95031班全体学生的记录。
212 select * from student where class in (95033, 95031);
213 
214 -- 26    查询存在有85分以上成绩的课程Cno.
215 select distinct cno from score where degree > 85
216 
217 -- 27    查询出“计算机系“教师所教课程的成绩表。
218 select * from score where cno in (
219 select cno from course where tno in(
220 select tno from teacher where depart = '计算机系'
221 )
222 )
223 
224 -- 28    查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
225 -- 方法一
226 select * from teacher where prof != all (
227 select prof from teacher where depart = '电子工程系' 
228 )
229 union
230 select * from teacher where prof != all (
231 select prof from teacher where depart = '计算机系' 
232 )
233 -- 方法二
234 select * from teacher t1 where prof != all (
235 select prof from teacher t2 where t1.depart != t2.depart
236 )
237 
238 -- 29    查询选修编号为“3-105“课程
239 -- 且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,
240 -- 并按Degree从高到低次序排序。
241 select * from score where cno = 3105 and degree > any (
242 select degree from score where cno = 3245
243 ) order by degree desc
244 
245 -- 30    查询选修编号为“3-105”
246 -- 且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
247 select * from score where cno = 3105 and degree > all (
248 select degree from score where cno = 3245
249 )
250 
251 -- 31    查询所有教师和同学的name、sex和birthday.
252 select sname name, ssex sex, sbirthday birthday from student
253 union
254 select tname, tsex, tbirthday from teacher
255 
256 -- 32    查询所有“女”教师和“女”同学的name、sex和birthday.
257 select sname name, ssex sex, sbirthday birthday from student where ssex = '女'
258 union 
259 select tname, tsex, tbirthday from teacher where tsex = '女'
260 
261 -- 33    查询成绩比该课程平均成绩低的同学的成绩表。
262 select s1.* from score s1, 
263 (select cno, avg(degree) avg from score group by cno ) s2 
264 where s1.cno = s2.cno and s1.degree < s2.avg
265 
266 -- 34    查询所有任课教师的Tname和Depart.
267 select tname, depart from teacher
268 
269 -- 35     查询所有未讲课的教师的Tname和Depart.
270 select tname, depart from teacher where tno != all (
271 select tno from course )
272 
273 -- 36    查询至少有2名男生的班号。
274 select distinct class from student s1 where exists (
275 select class, count(sno) from (
276 select * from student where ssex = '男') s2 
277 group by class having count(sno) >= 2
278 )
279 
280 -- 37    查询Student表中不姓“王”的同学记录。
281 select * from student where sname not like '王%'
282 
283 -- 38    查询Student表中每个学生的姓名和年龄。
284 select sname, 2018 - year(sbirthday) age from student
285 
286 -- 39    查询Student表中最大和最小的Sbirthday日期值。
287 select max(sbirthday) max, min(sbirthday) min from student
288 
289 -- 40    以班号和年龄从大到小的顺序查询Student表中的全部记录。
290 select * from student order by class desc, year(sbirthday) * 12 + month(sbirthday)
291 -- 41    查询“男”教师及其所上的课程。
292 select t.*, c.cname from teacher t, course c where t.tno = c.tno and tsex = '男'
293 
294 -- 42 查询每门课程最高分同学的Sno、Cno和Degree列。
295 select *from score where degree in (
296 select max(degree) from score group by cno
297 )
298 
299 -- 43    查询和“李军”同性别的所有同学的Sname.
300 select sname from student where ssex = (
301 select ssex from student where sname = '李军'
302 )
303 
304 -- 44    查询和“李军”同性别并同班的同学Sname.
305 -- 方法一
306 select sname from student where ssex = (
307 select ssex from student where sname = '李军'
308 ) and class = (
309 select class from student where sname = '李军'
310 )
311 
312 -- 45    查询所有选修“计算机导论”课程的“男”同学的成绩表。
313 select * from score where cno = (
314 select cno from course where cname = '计算机导论'
315 ) and sno in (
316 select sno from student where ssex = '男'
317 )
View Code