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 )