1 select * from STUDENT;
2 select * from mark;
3 select * from COURSE;
4
5 select * from teacher;
6
7 --注释
8 select sname from student;--投影查询
9 select * from student;
10 select sname as 姓名,ssex as 性别,sage from student ;--属性起别名
11 select sname 姓名,ssex 性别,sage from student ;
12 select distinct sage,ssex from student;--distinct 修饰组合，去掉重复的
13 select 5
14 from 1
15 where 2
16 group by 3
17 having 4
18 order by 6
19 select * from student
20 where SNATIVEPLACE='上海'
21 select * from student where sage=19;--查询十九岁
22 = > <= >= ！= and or not not > and >or
23 select * from student
24 where(sage=19 or sage =21)and ssex ='男';
25
26 select * from student where SNATIVEPLACE !='福建' and
27 select * from student where not sage=19 or ssex='女' and SNATIVEPLACE ='福建'--除福建
28 select * from student where (SNATIVEPLACE='福建'and ssex='女') or ( not SNATIVEPLACE='上海'and ssex='男')
29 --来自福建的女生以及上海以外的男生
30 select * from STUDENT
31 where SAGE between 19 and 20---20 dao 19
32 select * from STUDENT
33 where sage in(19,20,22) and ssex='男'--in集合数 19 20 22 男生
34 select * from STUDENT where sname like '王%'
35 --模糊查找 like '%' 下划线表示几个名__ %任意几个名
36 select * from STUDENT where sname like '徐__'
37 insert into STUDENT(sid,SNAME) values(10039,'小雅')
38 select * from STUDENT where SSEX is null --数据为空 is null
39 select ssex,min(SAGE),max(SAGE),avg(SAGE),sum(SAGE),count(*) from STUDENT group by SSEX
40 select SNATIVEPLACE,count(*) from STUDENT where SSEX='男' group by SNATIVEPLACE; 统计各个地区男生数
41 select * from MARK
42 select sid,avg(cmark) from mark group by sid;--每个学生均分
43 select sid,min(cmark),count(*) from mark where CMARK<60 group by sid--不及格数目（查询条件出现个，每个就是分组
44 select SCLASS,count(distinct SNATIVEPLACE) from STUDENT group by SCLASS--各个学生分别来自多少不同的地区
45 select *from STUDENT
46
47 select ssex,sage, count(*) from student group by ssex,sage --可以同时性别，年龄进行分组
48 select max(SAGE) from STUDENT
49 select sid from mark group by sid having avg(cmark)>80
50 select SID from MARK where CMARK>80 group by SID--where中不能出现avg 之类的 where avg（mark）》80
51 select SNATIVEPLACE,count(*)from STUDENT where SSEX='男' group by SNATIVEPLACE---各个地区的男生人数
52 select SNATIVEPLACE,count(*) from STUDENT group by SNATIVEPLACE,SSEX having SSEX='男'---各个地区的男生人数
53 select * from STUDENT order by SAGE desc--asc升序，desc降序（空属性第一大 根据年龄排序
54 select * from STUDENT order by SAGE desc,sid --二阶排序在年龄相同的情况下sid排序
55 select * from STUDENT order by SAGE asc
56 select sid,avg(cmark) amk from MARK group by SID order by amk desc--j降序均分
57 select trunc(CMARK/10)*10||'`'||(trunc(CMARK/10)*10+9),count(*) from MARK where CID=2001--(学科
58 group by trunc(CMARK/10)
59
60 --分段，turnc取证
61 select * from STUDENT,MARK where STUDENT.sid=mark.sid--俩个表合一
62 select * from STUDENT s,MARK m where s.SID=m.SID
63 select * from STUDENT,MARK,COURSE
64 where STUDENT.sid=mark.sid and mark.cid=COURSE.cid;
65
66 select SNATIVEPLACE,avg(cmark) from STUDENT,mark,COURSE where STUDENT.sid=mark.sid and mark.cid=COURSE.cid and CNAME='数学'
67 group by SNATIVEPLACE---各个地区数学均分
68 select sname,avg(CMARK) from STUDENT s,MARK m where s.sid=m.sid and SSEX='男' group by s.sid,SNAME--找出每个男生平均分，显示其姓名，均分
69 select * from STU,DENT s,MARK m where s.sid=m.sid and SSEX='男'
70 select * from MARK a,MARK b where a.sid=10002 and b.sid=10003 and a.cid=b.cid and a.cmark>b.cmark
71 select SNAME from MARK a,MARK b,STUDENT s where a.sid=b.sid and a.cid=2001 and b.cid=2002 and a.cmark>b.cmark
72 --那些同学20001号课程成绩比自己的20002成绩高，找sid
73 --查询核心1.from：将表格从硬盘的文件进行提取，并形成实例 将内存的实例从上往下依次循环，每次提取一条记录，并交给后续的子句 在多表中会对多张表进行笛卡尔想成，生成巨大的笛卡尔乘积
74 -- 2.每个字句在执行顺序
75 --where 接受from 给自己的一一条记录，对记录进行合法判断 = >= <= != <>,这些是比大小 而且可以比字符串，而且比大小的过程中，式子两边必须一个是属性，一个是值 支持逻辑 and or not not》and
76 》or 支持between连续空间 如果空间不连续 有in（） 模糊查询like %表示多个任意字母而__表示一个任意字母
77 -- 空的匹配使用 is null 或者is not null 不可以使用匹配null
78 -- group by sage 含义是将整个表根据sage 进行分组，分组：sage值相同的记录合并至一个“小组”中
79 解释器不会遍历记录，而是遍历小组
80 因此select 语句可以查询的特征语句必须来自group by 的分组特征
81 --group by 之后可以跟随多个属性进行分组特征，先后顺序不重要
82 ---可以没有group by 语句直接进行查询统计结果 这类语句默认分组，就是将整个结果当成一个组看待
83 select 语句的作用投影的信息至屏幕上，属性之间分隔符号为英文逗号，属性可以起别名，as 最好省，*是通配符，表示这张表所有的属性select
84 可以去重，distinct
85 order by 的作用排序，须指定属性
86 desc 降序
87 asc 升序
88 order by 表示一阶属性相同时，使用二节顺序的值
89
90 select sname from MARK a,MARK b,STUDENT s
91 where a.cid!=b.cid and a.sid==10002 and a.sid=b.sid a.sname='数学' and b.sname='英语'
92 and a.cmark>b.cmark
93 那些学生2001号成绩大于2002号成绩
94 --1、与“张三”同乡的男生姓名
95 select * from STUDENT where SNAME='张三'
96 select SNAME from STUDENT where SNATIVEPLACE='江苏'and SSEX='男'
97 select * from STUDENT a,STUDENT b where a.SNATIVEPLACE=b.SNATIVEPLACE;
98 select a.*from STUDENT a,STUDENT b
99 where a.SNATIVEPLACE=b.SNATIVEPLACE and b.SNAME='张三' and a.SSEX='男' and a.SNAME!='张三'
100 --统计每门课程的平均成绩,并按照成绩降序排序
101 select a.CNAME,avg(CMARK) from COURSE a,MARK b where a.cid=b.CID group by CNAME order by avg(CMARK) desc
102 --查询“福建”地区学生所选修的全部课程名称
103 select CNAME from STUDENT,MARK,COURSE
104 where STUDENT.sid=mark.sid and mark.cid=COURSE.cid and SNATIVEPLACE='福建'
105 --查询所在班级和该班内学生的年龄之和,对该班级中每个人的年龄进行比对，(要求大于20岁的人参与统计)
106 select a.SCLASS,sum(a.SAGE) from STUDENT a,STUDENT b where a.sclass=b.sclass and sage >20 group by SCLASS
107 select a.SCLASS,sum(a.SAGE) from STUDENT a,STUDENT b where a.SAGE=b.SAGE and b.SAGE>20 group by a.SCLASS+
108 select SCLASS from STUDENT a,STUDENT b where a. group by SCLASS
109 --5、查询所在班级和该班内学生的年龄之和,(要求该班级中每个人的年龄都大于20岁)
110 select a.SCLASS,sum(a.SAGE) from STUDENT a,STUDENT b where a.SAGE=b.SAGE and b.SAGE>20 group by a.SCLASS
111 男生与女生数学均分
112 select SSEX,trunc(avg(CMARK) ,2) from STUDENT s,MARK m,COURSE c
113 where s.sid=m.sid and m.cid=c.cid and CNAME='数学' and ssex is not null group by SSEX
114 数学每个分段多少人数
115 select trunc(CMARK/10)*10||'`'||(trunc(CMARK/10)*10+9),count(*) from STUDENT s,MARK m,COURSE c
116 where s.sid=m.sid and m.cid=c.cid and CNAME='数学' group by trunc(CMARK/10)
117 找出各科成绩及格与不及格的人数，要求表示课程名称0 不及格 1 及格
118 select (trunc(CMARk/60)||'`'||(trunc(CMARK/60)+1)) 1, count(*) from STUDENT s,MARK m,COURSE c
119 where s.sid=m.sid and m.cid=c.cid and cmark/60>1 and CMARK/60<1 group by trunc(CMARk/60)
120 select cname,trunc(CMARK/60),count(*)
121 from MARK m,COURSE c
122 where m.cid=c.cid
123 group by c.cid,CNAME,trunc(CMARK/60)
124 找出福建与安徽学生的数学均分
125 select SNATIVEPLACE,avg(CMARK)
126 from STUDENT s,MARK m,COURSE c
127 where s.sid=m.sid and m.cid=c.cid and CNAME='数学'and (SNATIVEPLACE='福建 'or SNATIVEPLACE='安徽')
128 group by SNATIVEPLACE
129
130
131 找出福建与安徽的所有学生总分
132 select SNATIVEPLACE,sum(CMARK) from STUDENT s,MARK m,COURSE c
133 where s.sid=m.sid and m.cid=c.cid and (SNATIVEPLACE='福建'or SNATIVEPLACE='安徽')
134 group by SNATIVEPLACE
135
136 找出福建与安徽的所有学生 各科总分
137 select CNAME,sum(CMARK) from STUDENT s,MARK m,COURSE c
138 where s.sid=m.sid and m.cid=c.cid and (SNATIVEPLACE='福建'or SNATIVEPLACE='安徽')
139 group by CNAME
140
141
142
143 找出福建与安徽的所有学生每个学生总分
144 select SNATIVEPLACE,SNAME,sum(CMARK)
145 from STUDENT s,MARK m,COURSE c
146 where s.sid=m.sid and m.cid=c.cid and (SNATIVEPLACE='福建'or SNATIVEPLACE='安徽')
147 group by SNAME,SNATIVEPLACE
148
149
150
151 子查询
152 select sid from STUDENT
153 where SNAME='张三'
154 select * from STUDENT
155 where SNATIVEPLACE=(select SNATIVEPLACE from STUDENT where SNAME='张三' )
156 找出数学成绩比张三高的男生姓名
157 select SNAME from STUDENT
158 where SSEX='男' and CMARK=(select cmark from COURSE where SNAME='张三' and cmark=() )
159
160 select cid from COURSE
161 where CNAME='数学' and sid=
162 (select sid from STUDENT where SNAME='张三' and cmark=
163 (select CMARK from MARK where SNAME='张三' and sid=
164 (select sid from STUDENT where SSEX='男')) )
165 select cid from COURSE
166 where CNAME='数学' sid=(select sid from STUDENT where sname='张三' cmark=(select CMARK from MARK where sid= ))
167 stp1 在course表找数学cid
168 select cid from COURSE where CNAME='数学'
169 2 在student中找张三的sid
170 select sid from STUDENT where SNAME='张三'
171 3 在mark中找出张三数学成绩
172 select CMARK from MARK where sid=(2) and cid=(1)
173 4 在mark中找出比张三高的人sdi
174 select sid from MARK where CMARK>(3) and cid=(1)
175 5在student中找出姓名，男性
176 select SNAME from STUDENT where sid in(4) and ssex='男'
177 select SNAME from STUDENT where sid in(
178 select sid from MARK where CMARK>(
179 select CMARK from MARK where sid=(
180 select sid from STUDENT where SNAME='张三') and cid=(
181 select cid from COURSE where CNAME='数学')) and cid=(
182 select cid from COURSE where CNAME='数学')
183 ) and ssex='男'
184 找出均分比张三高的男生姓名
185 select sid from STUDENT where SNAME='张三'
186 均分
187 select avg(CMARK) from MARK where
188
189
190
191 找出最大年龄姓名
192 select max(sage) from STUDENT
193 select sname from select where age=(select max(sage) from STUDENT )
194
195 select * from mark group by sid
196
197
198 找出六老师学生数学最高分姓名
199
200 select TID from TEACHER where TNAME='马六'
201 数学
202 select CID from COURSE where tid='30001' and CNAME='数学'
203 最高分数
204
205 select max(CMARK) from MARK where cid=2001
206 select sid from MARK where CMARK=97
207 姓名
208
209 select SNAME from STUDENT where sid=10008
210 select SNAME from STUDENT where sid in(
211 select sid from MARK where CMARK=(
212 select max(CMARK) from MARK where cid=(
213 select CID from COURSE where CNAME='数学' and tid=(
214 select TID from TEACHER where TNAME='马六'
215 )
216 )
217 )
218
219 )
220 张三地区
221 SELECT SNATIVEPLACE FROM STUDENT WHERE SNAME='张三'
222
223 同学
224 select sid from STUDENT where SNATIVEPLACE in (1)
225 select cid from COURSE where CNAME='数学'
226
227 select max(CMARK) from MARK where CID=() SID in (2)
228 select sid from MARK where CMARK=(3)
229 select SNAME from STUDENT where SID=(4)
230 数学成绩
231 select SNAME from STUDENT where SID=(
232 select sid
233 from MARK
234 where CMARK = (
235 select max(CMARK) from MARK where CID=(
236 select cid from COURSE where CNAME='数学'
237 ) and SID in (
238 select sid from STUDENT where SNATIVEPLACE in (
239 SELECT SNATIVEPLACE FROM STUDENT WHERE SNAME='张三'
240 )
241 )
242
243 )
244 )
245
246
247 找出均分最高的姓名
248 1 找出均分表
249 select sid ,avg(CMARK) amk from MARK group by SID
250 2 找出最高
251 select max(amk) from (1)
252 3 在均分表中，谁的均分等于最高分
253 select sid from (1) where amk=(2)
254 select name from STUDENT
255
256 select sid from (select sid ,avg(CMARK) amk from MARK group by SID) where amk=(select max(amk) from (select sid ,avg(CMARK) amk from MARK group by SID))
257
258 select sid ,avg(cmark) m from mark where group by SID
259 select min(m) from (1)
260 select sid from (1) where m=(2)
261 select SNAME from STUDENT where sid=(3)
262
263 select ROWNUM,s.* from STUDENT s where ROWNUM<=10
264 select SAGE,max()
265 select SAGE from STUDENT order by desc
266 select * from () where ROWNUM=1
267
268 select * from (select * from STUDENT order by desc ) where ROWNUM=1
269 找出年龄从大到小第5至8 学生信息
270 select * from STUDENT where ROWNUM<=8 order by SAGE desc
271 select * from STUDENT where ROWNUM <=4 order by SAGE
272
273 select * from (select * from STUDENT where ROWNUM<=8 order by SAGE desc) where ROWNUM <=4 order by SAGE asc
274 找出均分高到低5到8 学生信息
275 select
276 1 与“张三”同乡的男生姓名
277 select SNATIVEPLACE from STUDENT where SNAME='张三'
278 select sid from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from STUDENT where SNAME='张三')
279 select SNAME from STUDENT where sid (2).
280 select SNAME from STUDENT where sid in (
281 select sid from STUDENT where SNATIVEPLACE=(
282 select SNATIVEPLACE from STUDENT where SNAME='张三'
283 )
284 )
285 选修了马六老师所讲课程的学生人数
286 1.赵露老师techer 求tid
287 select tid from TEACHER where TNAME='马六'
288
289 2.tid cid
290 select cid from COURSE where tid in (select tid from TEACHER where TNAME='马六')
291 cid sid
292
293 select sid from MARK where cid= 2001
294 sid count
295 select count(*) from STUDENT where sid in (select sid from MARK where cid= 2001)
296
297
298
299 查询没学过“胡”姓老师课的同学的学号、姓名
300 模糊查询胡老师，tid
301 select tid from TEACHER where TNAME like '胡%'
302 tid cid
303 select cid from COURSE where tid in ()
304
305 cid sid
306 select sid from MARK where cid in ()
307
308 sid 名字，学号
309 select SNAME,sid from STUDENT where sid in ()
310
311 select SNAME,sid from STUDENT where sid not in (
312 select sid from MARK where cid in (
313 select cid from COURSE where tid in (
314 select tid from TEACHER where TNAME like '胡%'
315 )
316 )
317 )
318
319 “数学”课程得最高分的学生姓名、性别
320 数学cid
321 select cid from COURSE where CNAME='数学'
322
323
324 最高分
325 select max(CMARK) from MARK where cid in ()
326
327 姓名
328 select sid from MARK where CMARK in ()
329 select sid,SNAME from STUDENT where sid=()
330
331 select sid,SNAME from STUDENT where sid=(
332 select sid from MARK where CMARK in (
333 select max(CMARK) from MARK where cid in (
334 select cid from COURSE where CNAME='数学'
335 )
336 )
337 )
338
339 统计每门课程的平均成绩,并按照成绩降序排序
340 select cid,avg(cmark) from mark group by cid
341 select czg from (select cid,avg(cmark) czg from mark group by cid) order by czg desc
342
343 select * from czg order by desc
344
345 子查询实现查询‘3-2班’"张三"同学的"英语"成绩
346 select sid from STUDENT where SNAME='张三'
347 select cid from COURSE where CNAME='英语'
348 select CMARK from MARK where sid =() and cid=()
349
350 select CMARK from MARK where sid =(
351 select sid from STUDENT where SNAME='张三'
352 ) and cid=(
353 select cid from COURSE where CNAME='英语'
354 )
355
356 查询“福建”地区学生所选修的全部课程名称
357 select SNATIVEPLACE from STUDENT where SNATIVEPLACE='福建'
358 select sid from STUDENT where SNATIVEPLACE='福建'
359
360 select cid from MARK where sid in ()
361 select CNAME from COURSE where cid in (
362 select cid from MARK where sid in (
363 select sid from STUDENT where SNATIVEPLACE='福建'
364 )
365 )
366
367 用子查询实现查询选修“数学”课的全部学生的数学总成绩
368 select cid from COURSE where CNAME='数学'
369 select sid from MARK where cid in ()
370 select sum(CMARK) from MARK where sid in()
371 select sum(CMARK) from MARK where sid in(
372 select sid from MARK where cid in (
373 select cid from COURSE where CNAME='数学'
374 )
375 ) and cid =(select cid from COURSE where CNAME='数学')
376 用子查询实现查询选修“高等数学”课的全部学生的所有课程总成绩高等数学总成绩
377 select cid from COURSE where CNAME='数学'
378 select sid from MARK where cid in (2001)
379 --select cid from COURSE where sid in ()
380 select sum(CMARK) from MARK where cid in ()
381
382 select sum(CMARK) from MARK where sid in (
383 select sid from MARK where cid in (
384 select cid from COURSE where CNAME='数学'
385 )
386 )
387
388
389
390 请用两种方法实现:查找所有成绩都在68分以上的学生姓名
391 select sid,min(CMARK) from MARK group by sid having min(CMARK)<68
392 select min(CMARK) from MARK group by sid
393 select sid from (select min(CMARK) from MARK group by sid
394
395 ) where cmark>68
396 查找至少2门成绩在80分以上的学生姓名
397 select sid from MARK group by SID
398 select cid from MARK where CMARK>80
399 select sid from MARK where cid=()
400 select sid,count(*) c from MARK where CMARK> 80 group by sid
401 select sid from (select sid,count(*) c from MARK where CMARK> 80 group by sid ) where c>2
402 select SNAME from STUDENT where sid in ()
403
404
405 查询个人平均总成绩小于平均总成绩的学生姓名
406 select avg(CMARK) c from MARK m
407 select sid,avg(CMARK) z from MARK m group by sid;
408 select sid from (select avg(CMARK) c from MARK m),(select sid,avg(CMARK) z from MARK m group by sid) WHERE Z>C
409
410 用子查询实现张三在其各科成绩中最高分成绩所对应的课程号和成绩
411 select SID from STUDENT where SNAME='张三'
412
413 select cid,CMARK c from MARK x where sid=(select SID from STUDENT where SNAME='张三')
414 select max(CMARK) from (select cid,CMARK from MARK x where sid=(select SID from STUDENT where SNAME='张三'));
415 select CID,CMARK from MARK where sid=(select SID from STUDENT where SNAME='张三')
416 and cmark=(select max(CMARK) from (select cid,CMARK from MARK x where sid=(select SID from STUDENT where SNAME='张三')))
417
418 8 找出张三的最高分和最低分以及对应的课程名
419
420 select max(CMARK) from (select cid,CMARK from MARK x where sid=(select SID from STUDENT where SNAME='张三'));
421 select min(CMARK) from (select cid,CMARK from MARK x where sid=(select SID from STUDENT where SNAME='张三'));
422 select cid from MARK
423 where sid=(select SID from STUDENT where SNAME='张三')
424 and
425 (CMARK=(select max(CMARK) from (select cid,CMARK from MARK x where sid=(select SID from STUDENT where SNAME='张三')))OR
426 CMARK=(select min(CMARK) from (select cid,CMARK from MARK x where sid=(select SID from STUDENT where SNAME='张三'))))
427
428 分页查询
429 找出没有数学课的学生姓名
430 select cid from COURSE where CNAME='数学'
431 select sid from MARK where cid in ()
432 select SNAME from STUDENT where sid not in ()
433 select SNAME from STUDENT where sid not in (
434 select sid from MARK where cid in (
435 select cid from COURSE where CNAME='数学'
436 )
437 )
438
439 找出各科成绩均比张三高的学生姓名
440 组合表
441 select * from MARK a,MARK b where a.cid=b.cid and b.sid=10002
442 select max(CMARK) c from MARK where sid=10002
443 张三 选修的cid
444 select sid,min(CMARK)z from ( select * from MARK a,MARK b where a.cid=b.cid and b.sid=10002 )
445 where c.max(CMARK)<z.min(CMARK)
446 group by SID
447
448 cid sid
449
450 select sid,min(CMARK) from MARK group by sid
451 组合表格，选课有重复
452 s1 select a.sid sid1 ,b.sid sid2,a.cid cid1 ,a.CMARK cmarka,b.CMARK cmarkb
453 from MARK a,MARK b where a.cid=b.cid and b.sid=10002
454 在表找至少有一门成绩低于张三的
455 s2 select sid1 from (s1) where cmarka<cmarkb
456
457 select sid from (1) where b.sid<cmark
458 在排除低于的
459 s3 select sid1 from (s1) where cid1 not in (2)
460
461 找年龄最大的
462 年龄排序
463 select * from STUDENT order by sage desc
464
465 取第一个
466 select * from (1) where ROWNUM=1
467
468 找出最大，在问谁等于最大
469
470 select max(SAGE) from STUDENT
471 select * from student where sage =()
472
473 我的年龄比所有人大包括自己
474 1 找出所有人
475 select SAGE from STUDENT where SAGE is not null
476 2扫描学生表，和所有人年龄进行比对
477 select * from STUDENT where not SAGE<any(1) and SAGE is not null
478
479
480 找出张三选课内容完全相同的
481 1 找出张三sid
482
483 2找出cid
484 select sid from MARK where sid=(1)
485
486 3张三没选cid
487 select cid from MARK where cid not in (2)
488 4
489 谁选了张三未选的si
490 select sid from MARK where cid in (3)
491 5找出张三课程数量
492 select * from MARK where sid =(1)
493 6比课程
494 select sid from MARK where sid not in (4)
495 group by sid having count(*) = (5)
496 7 替换姓名
497 select SNAME from STUDENT where SID in (6)
498
499
500 找出张三数学英语成绩 显示数学英语姓名三列
501 select SNAME,(
502 select CMARK from MARK where sid=10002 and cid=2001
503 )数学成绩,(
504 select CMARK from MARK where sid=10002 and cid=2002
505 )英语成绩
506 from STUDENT
507 where SNAME='张三'
508 查询所有人数学英语成绩
509 select SNAME,(
510 select CMARK from MARK where sid=s.sid and cid=2001
511 )数学成绩,(
512 select CMARK from MARK where sid=s.sid and cid=2002
513 )英语成绩
514 from STUDENT s
515
516
517 exists存在
518 where exists (select ..) 存在一个就是ture
519 where not exists (select ..)
520 如果我是年龄最大的，那么一定不存在有人比我大
521 select * from STUDENT s where not exists(
522 select * from STUDENT where sage>s.SAGE
523 )
524
525 找出选修所有的课程
526 select * from STUDENT s where not exists(
527 select * from COURSE c where not exists(
528 select * from MARK where sid=s.sid and c=c.cid
529 )
530 )
531
532 集合运算
533 集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括:
534 INTERSECT(交集)，返回两个查询共有的记录。
535 UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
536 UNO(并集)，返回各个查询的所有记录，不包括重复记录。
537 MINUS(补集)返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。
538 当使用集合运算时，要注意:查询所返回的列数以及列的类型必须匹配，列名可以不同
539 并集
540 select SNAME,SAGE from STUDENT where SAGE in(19,21)
541 union
542 select SNAME,SAGE from STUDENT where SAGE in(20,22)
543
544 并集
545 select SNAME,SAGE from STUDENT where SAGE in(19,21)
546 union all
547 select SNAME,SAGE from STUDENT where SAGE in(20,22)
548 交集
549 select SNAME,SAGE from STUDENT where SAGE in(19,21)
550 intersect
551 select SNAME,SAGE from STUDENT where SAGE in(20,22)
552
553 补集
554 select SNAME,SAGE from STUDENT where SAGE in(19,21)
555 minus
556 select SNAME,SAGE from STUDENT where SAGE in(20,22)
557
558 找出福建除最高和最低的平均年龄
559 select sid from STUDENT where SNATIVEPLACE='福建'
560 select max(SAGE) from STUDENT where sid in (select sid from STUDENT where SNATIVEPLACE='福建')
561 union
562 select min(SAGE) from STUDENT where SNATIVEPLACE='福建'
563 select sid,avg(SAGE) from STUDENT where sid in ()
564
565 select sid,avg(SAGE) from STUDENT where sid in (
566 select max(SAGE) from STUDENT where sid not in (
567 select sid from STUDENT where SNATIVEPLACE='福建'
568 )
569 union
570 select min(SAGE) from STUDENT where sid in (
571 select sid from STUDENT where SNATIVEPLACE='福建'
572 )
573
574 )
575 1找出福建最高年龄
576 select max(SAGE) from STUDENT where SNATIVEPLACE='福建'
577 select sid from STUDENT where SAGE=( select max(SAGE) from STUDENT where SNATIVEPLACE='福建')
578 2找出福建最低年龄
579 select min(SAGE) from STUDENT where SNATIVEPLACE='福建'
580 select sid from STUDENT where SAGE=(select min(SAGE) from STUDENT where SNATIVEPLACE='福建')
581 3union连接
582 select max(SAGE) from STUDENT where SNATIVEPLACE='福建'
583 union
584 select min(SAGE) from STUDENT where SNATIVEPLACE='福建'
585 4 找出福建地区学生，并去除3中学生，剩下的平均年龄
586 select sid from STUDENT where sid not in (
587 select sid from STUDENT where SAGE=(select min(SAGE) from STUDENT where SNATIVEPLACE='福建')
588 union
589 select sid from STUDENT where SAGE=( select max(SAGE) from STUDENT where SNATIVEPLACE='福建')
590 )
591 select avg(SAGE) from STUDENT where sid in (
592 select sid from STUDENT where sid not in (
593 select sid from STUDENT where SAGE=(select min(SAGE) from STUDENT where SNATIVEPLACE='福建')
594 union
595 select sid from STUDENT where SAGE=( select max(SAGE) from STUDENT where SNATIVEPLACE='福建')
596 )
597 )
598
599 按平均成绩从高到低显示所有学生的“数学”、“英语”、“语文”三门的课程成绩(按如下形式显示:学生ID,高等数学,计算机数学,英语,有效课程数,有效平均分 )
600 select SNAME,(
601 select CMARK from MARK where sid=s.sid and cid=2001
602 )数学成绩,(
603 select CMARK from MARK where sid=s.sid and cid=2002
604 )英语成绩,
605 (select cmark from MARK where sid =s.sid and cid =
606 from STUDENT s
607 查询各科成绩前三名的记录:(不考虑成绩并列情况)
608 课程名 第一名 第二名 第三名;
609 select * from MARK where ROWNUM=1 order by CMARK desc
610 select * from MARK where ROWNUM=1 order by CMARK desc
611 查找至少2门成绩在80分以上的学生姓名
612 select
613
614
615
616
617
618 --34谁的各科成绩均高于各科均分
619 select CID,avg(CMARK) amk from MARK m group by CID;
620 --S2
621 select m1.sid,count(*) c from MARK m1,(select CID,avg(CMARK) amk from MARK m group by CID) m2
622 where m1.CID=m2.cid and m1.CMARK>m2.amk group by m1.sid;
623 --s3
624 select sid from (select m1.sid,count(*) c from MARK m1,(select CID,avg(CMARK) amk from MARK m group by CID) m2
625 where m1.CID=m2.cid and m1.CMARK>m2.amk group by m1.sid) where c=7;
626 --s4
627 select SNAME from STUDENT where SID in (select sid from (select m1.sid,count(*) c from MARK m1,(select CID,avg(CMARK) amk from MARK m group by CID) m2
628 where m1.CID=m2.cid and m1.CMARK>m2.amk group by m1.sid) where c=7)
629
630 -33求各门课程去掉一个最高分和最低分后的平均分
631 --s1
632 select CID,MAX(CMARK) 最高分 ,MIN(CMARK) 最低分 from MARK group by CID;
633 --s2
634 select m1.CID,CMARK from MARK m1,(select CID,MAX(CMARK) 最高分 ,MIN(CMARK) 最低分 from MARK group by CID) m2
635 where m1.CID=m2.CID and m1.CMARK!=m2.最高分 and CMARK!=m2.最低分;
636 --s3
637 select CID,AVG(CMARK) from (select m1.CID,CMARK from MARK m1,(select CID,MAX(CMARK) a ,MIN(CMARK) b from MARK group by CID) m2
638 where m1.CID=m2.CID and m1.CMARK!=m2.a and CMARK!=m2.b)
639 group by CID;
640 --32上海地区哪门课的均分比福建差
641 --s1
642 select sid from STUDENT where SNATIVEPLACE='上海';
643 --s2
644 select cid,avg(CMARK) amk from MARK where sid in (select sid from STUDENT where SNATIVEPLACE='上海') group by CID;
645 --s3
646 select sid from STUDENT where SNATIVEPLACE='福建';
647 --s4
648 select cid,avg(CMARK) amk from MARK where sid in (select sid from STUDENT where SNATIVEPLACE='福建') group by CID;
649 --s5
650 select a.CID
651 from (select cid,avg(CMARK) amk from MARK where sid in (select sid from STUDENT where SNATIVEPLACE='上海') group by CID) a,
652 (select cid,avg(CMARK) amk from MARK where sid in (select sid from STUDENT where SNATIVEPLACE='福建') group by CID) b
653 where a.CID=b.CID and a.amk<b.amk;
654 --s6
655 select CNAME FROM COURSE where CID in (select a.CID
656 from (select cid,avg(CMARK) amk from MARK where sid in (select sid from STUDENT where SNATIVEPLACE='上海') group by CID) a,
657 (select cid,avg(CMARK) amk from MARK where sid in (select sid from STUDENT where SNATIVEPLACE='福建') group by CID) b
658 where a.CID=b.CID and a.amk<b.amk)
659 --30找出数学和英语均分最高的男生姓名
660 --S1
661 select CID 科目 from COURSE where CNAME='数学'
662 union
663 select CID from COURSE where CNAME='英语';
664 --s2
665 select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
666 union
667 select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID;
668 --s3
669 select sid,avg(amk) am
670 from (select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
671 union
672 select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID)
673 group by SID;
674 --s4
675 select max(am) from (select sid,avg(amk) am
676 from (select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
677 union
678 select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID)
679 group by SID);
680 --s5
681 select sid from (select sid,avg(amk) am
682 from (select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
683 union
684 select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID)
685 group by SID) where am=(select max(am) from (select sid,avg(amk) am
686 from (select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
687 union
688 select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID)
689 group by SID));
690 --s6
691 select SNAME from STUDENT
692 where Sid in (select sid from (select sid,avg(amk) am
693 from (select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
694 union
695 select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID)
696 group by SID) where am=(select max(am) from (select sid,avg(amk) am
697 from (select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='数学')group by SID
698 union
699 select sid,avg(CMARK) amk from MARK m where CID=(select CID from COURSE where CNAME='英语')group by SID)
700 group by SID)));
701 --29 请显示张三同学的数学和英语成绩,要求显示姓名，数学成绩，英语成绩
702 select SNAME 学生姓名,(select CMARK from MARK where cid=2001 and sid=10001) 数学成绩,(select CMARK from MARK where cid=2002 and sid=10001) 英语成绩
703 from STUDENT where sname='张三';
704 --28数学状元的同班同学中男生均分最高的学生姓名
705 --s1
706 select cid from COURSE where CNAME='数学';
707 --s2
708 select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学');
709 --s3
710 select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'));
711 --s4
712 select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
713 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学')));
714 --s5
715 select SCLASS from STUDENT where SID=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
716 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))));
717 --s6
718 select sid from STUDENT where SCLASS=(select SCLASS from STUDENT where SID=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
719 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
720 sid!=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
721 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))));
722 --s7
723 select sid,avg(CMARK) am FROM MARK where SID in (select sid from STUDENT where SCLASS=(select SCLASS from STUDENT where SID=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
724 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
725 sid!=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
726 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) group by sid;
727 --s8
728 select max(am) from (select sid,avg(CMARK) am FROM MARK where SID in (select sid from STUDENT where SCLASS=(select SCLASS from STUDENT where SID=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
729 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
730 sid!=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
731 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) group by sid);
732 --s9
733 select SID from (select sid,avg(CMARK) am FROM MARK where SID in (select sid from STUDENT where SCLASS=(select SCLASS from STUDENT where SID=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
734 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
735 sid!=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
736 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) group by sid)
737 where am=(select max(am) from (select sid,avg(CMARK) am FROM MARK where SID in (select sid from STUDENT where SCLASS=(select SCLASS from STUDENT where SID=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
738 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
739 sid!=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
740 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) group by sid));
741 --s10
742 select SNAME from STUDENT where SID in (select SID from
743 (select sid,avg(CMARK) am FROM MARK where SID in (select sid from STUDENT where SCLASS=(select SCLASS from STUDENT
744 where SID=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
745 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
746 sid!=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
747 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) group by sid)
748 where am=(select max(am) from (select sid,avg(CMARK) am FROM MARK where SID in (select sid from STUDENT where SCLASS=(select SCLASS
749 from STUDENT where SID=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
750 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) and SSEX='男' and
751 sid!=(select sid from(select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))
752 where CMARK=(select max(CMARK) from (select sid,CMARK from MARK where CID=(select cid from COURSE where CNAME='数学'))))) group by sid)));
753 --27 男生人数最多的地区中哪个学生年龄最大
754 --s1
755 select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE;
756 --s2
757 select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE);
758 --s3
759 select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
760 where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE));
761 --s4
762 select sid,SAGE from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
763 where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)));
764 --s5
765 select max(sAGE) from (select sid,SAGE from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
766 where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE))));
767 --s6
768 select sid from (select sid,SAGE from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
769 where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE))))
770 where SAGE=(select max(sAGE) from (select sid,SAGE from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
771 where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)))));
772 --s6
773 select SNAME from STUDENT where SID in (select sid from (select sid,SAGE from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
774 where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE))))
775 where SAGE=(select max(sAGE) from (select sid,SAGE from STUDENT where SNATIVEPLACE=(select SNATIVEPLACE from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE)
776 where cu in (select max(cu) from (select SNATIVEPLACE,count(*) cu from STUDENT s group by SNATIVEPLACE))))));
777 select sid 学生id ,(select cmark from mark where cid=
778 (select cid from course where cname='数学') and sid=sc.sid) 数学 ,(select cmark from mark where cid=
779 (select cid from course where cname='英语')and sid=sc.sid) 英语,count(*) 有效课程数,avg(cmark) 有效均分
780 from mark sc group by sid
781 --1.与“张三”同乡的男生姓名
782 1.
783 select SNATIVEPLACE from STUDENT where SNAME='张三'
784 2.
785 select sid from STUDENT where SNATIVEPLACE=()
786 3.
787 select SNAME from STUDENT where sid in ()
788 --
789 select SNAME from STUDENT where sid in (
790 select sid from STUDENT where SNATIVEPLACE=(
791 select SNATIVEPLACE from STUDENT where SNAME='张三'
792 )
793
794 )
795 --2.选修了胡适老师所讲课程的学生人数
796 1.
797 select tid from TEACHER where TNAME='胡适'
798 2.
799 select cid from COURSE where tid in ()
800 3;
801 select sid from MARK where cid in ()
802 4.
803 select SNAME from STUDENT where sid in ()
804
805 5
806 select SNAME from STUDENT where sid in (
807 select sid from MARK where cid in (
808 select cid from COURSE where tid in (
809 select tid from TEACHER where TNAME='胡适'
810
811 )
812 )
813 )
814 --3.查询没学过“胡”姓老师课的同学的学号、姓名
815 1.
816 select tid from TEACHER where TNAME like '胡%'
817 2.
818 select cid from COURSE where tid in (select tid from TEACHER where TNAME like '胡%')
819 3.
820 select sid from MARK where cid in (2002,2004)
821 4.
822 select SNAME from STUDENT where sid in ()
823 5.
824 select SNAME from STUDENT where sid not in (
825 select sid from MARK where cid in (
826 select cid from COURSE where tid in (
827 select tid from TEACHER where TNAME like '胡%'
828 )
829 )
830 )
831 --4“数学”课程得最高分的学生姓名、性别
832 1
833 select cid from COURSE where CNAME='数学'
834 2
835 select max(CMARK) from MARK where cid=()
836 3.
837 select sid from MARK where CMARK in ()
838 4.
839 select SNAME,SSEX from STUDENT where sid in ()
840 5.
841 select SNAME,SSEX from STUDENT where sid in (
842 select sid from MARK where CMARK in (
843 select max(CMARK) from MARK where cid=(
844 select cid from COURSE where CNAME='数学'
845 )
846 )
847 )
848 --5统计每门课程的平均成绩,并按照成绩降序排序
849 select avg(CMARK) from MARK group by cid order by avg(CMARK) desc
850
851 -17 用子查询实现张三在其各科成绩中最高分成绩所对应的课程号和成绩
852 1.
853 select sid from STUDENT where SNAME='张三'
854 2.
855 select max(CMARK) from MARK where sid = ()
856 3.
857 select cid from MARK where sid =()
858 and CMARK=()
859 4
860
861 select cid,CMARK from MARK where sid =(
862 select sid from STUDENT where SNAME='张三'
863 )
864 and CMARK=(
865 select max(CMARK) from MARK where sid = (
866 select sid from STUDENT where SNAME='张三'
867 )
868 )
869
870 --33求各门课程去掉一个最高分和最低分后的平均分
871 --s1
872 select CID,MAX(CMARK) a ,MIN(CMARK) b from MARK group by CID;
873 --s2
874 select m1.CID,CMARK from MARK m1,(select CID,MAX(CMARK) a ,MIN(CMARK) b from MARK group by CID) m2
875 where m1.CID=m2.CID and m1.CMARK!=m2.a and CMARK!=m2.b;
876 --s3
877 select CID,AVG(CMARK) from (select m1.CID,CMARK from MARK m1,(select CID,MAX(CMARK) a ,MIN(CMARK) b from MARK group by CID) m2
878 where m1.CID=m2.CID and m1.CMARK!=m2.a and CMARK!=m2.b)
879 group by CID;
880
881
882 select sid
883 from STUDENT where SNAME='张三'
884 select a.sid from MARK a,MARK b where a.cid=b.sid and b.sid=() and a.CMARK<=b.CMARK
885 select sid from MARK where sid not in ()
886 select SNAME from STUDENT where sid in ()
887
888 select sid 学生id ,(select cmark from mark where cid=
889 (select cid from course where cname='数学') and sid=sc.sid) 数学 ,(select cmark from mark where cid=
890 (select cid from course where cname='英语')and sid=sc.sid) 英语,count(*) 有效课程数,avg(cmark) 有效均分
891 from mark sc group by sid
892
893 select sid from STUDENT where SNATIVEPLACE='上海'
894 select cid,avg(CMARK) amk from MARK where sid in ()
895
896 select sid from STUDENT where SNATIVEPLACE='福建'
897 select cid,avg(CMARK) amk from MARK where sid in ()
898
899 select a.cid from () where a.cid=b.cid and a.cmk<b.cmk
900
901 2019/7/22
902 ---将每个同学的成绩降序排序 开窗
903 select m.*,rank() over (partition by SID order by CMARK desc ) from MARK m
904 select m.*,dense_rank() over (partition by SID order by CMARK desc ) id1,
905 rank() over (partition by SID order by CMARK desc) id2 from MARK m
906
907 找出各个地区各门课均分，各个地区集中显示，均分降序排序
908 1.
909 select SNATIVEPLACE from STUDENT group by SNATIVEPLACE
910 select sid from STUDENT where SNATIVEPLACE in (select SNATIVEPLACE from STUDENT group by SNATIVEPLACE)
911 select avg(CMARK) from ()
912
913 2,
914 select avg(CMARK) from MARK where
915 select SNATIVEPLACE,CNAME,avg(CMARK) amk,row_number() over (
916 partition by SNATIVEPLACE order by avg(CMARK) desc )
917 id1_row_number,
918 rank() over (
919 partition by SNATIVEPLACE order by avg(CMARK) desc
920 )id2_rank,dense_rank() over (
921 partition by SNATIVEPLACE order by avg(CMARK) desc
922 )
923
924 from STUDENT s,MARK m,COURSE c
925 where s.sid=m.sid and m.sid=c.sid
926 group by snativeplace, cname
927
928 select SNATIVEPLACE,CNAME,amk,max(amk) over (partition by SNATIVEPLACE),
929 min(amk) over ( partition by SNATIVEPLACE)
930 first_value() over
931 from(
932 select SNATIVEPLACE,CNAME,avg(CMARK) amk,
933 row_number() over (
934 partition by SNATIVEPLACE order by avg(CMARK) desc
935 ) id_row_number,
936 rank() over (
937 partition by SNATIVEPLACE order by avg(amk) desc
938 ) id2_rank,
939 dense_rank() over (
940 partition by SNATIVEPLACE order by avg(amk) desc
941 ) id3_dense_rank
942 from STUDENT s,MARK m,COURSE c
943 where s.sid=m.sid and m.sid=c.sid
944 group by snativeplace, cname
945
946 )格式，连接查询
947 select * from STUDENT s,MARK m where s.sid=m.sid
948
949 select * from STUDENT s inner join MARK m on s.sid=m.sid--内连，交集
950 select * from STUDENT s left outer join MARK m on s.sid=m.sid--外联
951
952
953 select SNAME,SSEX,amk from STUDENT s left outer join (
954 select sid,avg(CMARK) amk from MARK group by sid
955 ) m on s.sid=m.sid
956 where SSEX='男'
957
958
959 select SNAME,SSEX,amk from STUDENT s full outer join (
960 select sid,avg(CMARK) amk from MARK group by sid
961 ) m on s.sid=m.sid and SSEX='男'
962 --sql 语言
963 DDL 对结构进行操作 create drop alter drop database Data Define Language
964 DML 对数据进行操作 insert delete update Data MANAGEMENT Language
965 DQL 对数据查询 Data Query Language
966
967 DCL Data control Language
968 TCL Transaction Control Languc
969
970 create table STUDENTq(sid number(5,0) , ssex char(2),sage number(10)
971 )
972 insert into STUDENTq(sid,ssex,ssage) values (10005222,'男',20);
973
974 select * from STUDENT
975 drop table studentq
976 insert into STUDENT(SNAME,SAGE) values ('张伟',21)---添加
977 commit --确当数据，提交
978
979 create table stu33 as------复制表
980 select * from STUDENT where ssex='男'
981
982 select * from stu33
983
984
985 drop table newstu2
986
987
988
989 insert into stu1
990 select * from STUDENT where sage>=20
991
992 select * from newwtu2
993
994
995 数据的删除
996 delete from STUDENT where 1=1
997
998 select ROWID,s.* from STUDENT s---内存地址
999 --删除重复数据
1000 delete from STUDENT
1001 where ROWID not in (
1002 select min(ROWID) from STUDENT
1003 group by sid
1004 )
1005
1006 commit
1007
1008 select sid from STUDENT where SNATIVEPLACE='安徽'
1009 select cid from COURSE where CNAME='数学'
1010 修改，多个属性，加，
1011
1012 来自安徽的均分低于70，数学成绩+5
1013 update MARK
1014 set CMARK=CMARK+5
1015 where cid=(
1016 select cid from COURSE where CNAME='数学') and sid in(
1017 select sid from MARK
1018 where sid in (
1019 select sid from STUDENT where SNATIVEPLACE='安徽'
1020 ) group by sid having avg(CMARK) <70
1021 )
1022
1023
1024 select mod(12,5)from DUAL
1025 create table stu2 ( sname
1026 varchar2(80),
1027 birth date
1028
1029 )
1030 select * from STU2
1031 insert into stu2 (sname ,birth) values('张三',to_date('2019-5-5','YYYY-MM-DD'))
1032
1033 select sysdate,
1034 extract(YEAR FROM sysdate),
1035 extract(MONTH FROM sysdate),
1036 extract( DAY sysdate)
1037 FROM DUAL
1038 create table reader(rname number(100),rsex char(3),
1039 )
1040 insert into STUDENTq(sid,ssex,ssage) values (10005222,'男',20);
1041 --to_date插入日期和时间用的
1042 insert into stu2(sname birth)
1043 values('张三',to_date('2019/5/21 16:55:56','YYYY/MM/DD HH24:MI:SS'))
1044 --sysdate sysadte+1加一天的时间 sysdate+1/24加一小时 其他以此类推
1045 select sysdate from dual
1046 --用现在的时间减去你的生日得到你从出生到现在过了多久
1047 select s.*,sysdate-birth from stu2 s
1048
1049
1050 --extract 单取出年月日
1051 select sysdate(
1052 extract(year from sysdate)
1053 extract(month from sysdate)
1054 extract(day from sysdate)
1055 )
1056 from dual
1057
1058 --算岁数
1059 select sysdate(
1060 extract(year from sysdate)-extract(year from birth)+1
1061 )
1062 from dual
1063
1064 select last_day(sysdate) from dual
1065
1066
1067
1068 作者表
1069 create table writer (
1070 wsid char(100),wname char(50),wsex char(3),warea varchar2(100) ,wtelephone char(100),wid char(100),wtotal varchar2(100),wword varchar2 (100),wnote varchar2(100)
1071
1072
1073 )
1074
1075
1076 select * from WRITER
1077
1078
1079 insert into writer values ('001','唐三','男','徐州','18851923638','3203821958265805456','107万','174万','18444')
1080 insert into writer values ('002','土豆','男','南京','18851923635','320382195826580486','105万','141万','1845')
1081 insert into writer values ('003','番茄','女','上海','18851923635','320382195826585586','104万','147万','184844')
1082 insert into writer values ('004','五一','女','南京','18851923634','320382195826580576','108万','177万','18445')
1083 insert into writer values ('005','胡歌','男','长春','188519236378','320382195826580786','152万','152万','1875')
1084 insert into writer values ('006','西瓜','男','徐州','18851923638','3203821958265804456','1474万','14万','1844')
1085 insert into writer values ('007','独角','男','南京','18851923635','3203821958265450486','14万','151万','184')
1086 insert into writer values ('008','南瓜','女','*','18851923635','3203821958885586','17万','14万','1848')
1087 insert into writer values ('009','东瓜','女','香港','18851923634','320382198826580576','18万','147万','185')
1088 insert into writer values ('0010','','男','长春','188519236378','3203821957826580786','15万','14万','185')
1089
1090 drop table writer
1091 打赏表
1092 mony
1093 create table mony(
1094 wsid char(100),mbook char(100),mmony char(100)
1095 )
1096 select * from mony
1097
1098 insert into mony valus('001','斗破苍穹','100万')
1099 insert into mony values('002','圣墟','125万')
1100 insert into mony values('003','霸道总裁爱上我','245万')
1101 insert into mony values('004','高冷男神','152万')
1102 insert into mony values('005','我是流氓','1424万')
1103 insert into mony valu('006','斗破苍穹2','100万')
1104 insert into mony values('007','明朝','125万')
1105 insert into mony values('008','王子','245万')
1106 insert into mony values('009','汉朝','152万')
1107 insert into mony values('0010','981','1424万')
1108 作者等级
1109 grade
1110 create table grade (
1111 wsid char(100),ggrade char(100)
1112 )
1113 insert into grade values ('001','大神')
1114 insert into grade values ('002','大神')
1115 insert into grade values ('003','封圣')
1116 insert into grade values ('004','大神')
1117 insert into grade values ('005','小白')
1118 select * from grade
1119
1120 排行榜
1121 list
1122 create table wlist (
1123 wsid char(100),wname char(100),lrank char(100)
1124
1125 )
1126 select * from wlist
1127 insert into wlist values('001','斗破苍穹','1')
1128 insert into wlist values('002','圣墟','2')
1129 insert into wlist values('003','霸道总裁爱上我','3')
1130 insert into wlist values('004','高冷男神','4')
1131 insert into wlist values('005','我是流氓','5')
1132 insert into wlist values('006','斗破苍穹2','6')
1133 insert into wlist values('007','明朝','7')
1134 insert into wlist values('008','王子','8')
1135 insert into wlist values('009','汉朝','9')
1136 insert into wlist values('0010','981','10')
1137
1138 drop table list
1139
1140
1141 随机0到100的整数
1142 select trunc(DBMS_random. value*100) from dual
1143
1144 select systimestamp from dual
1145 select to_date('2018-7-5','yyy-mm-dd'), to_date ('2018-7-5','yyy-mm-dd') + interval '7' hour from dual
1146
1147 CREATE SEQUENCE stuq
1148 MINVALUE 10005
1149 START WITH 10005
1150 MAXVALUE 100000
1151 INCREMENT BY 1
1152 NOCYCLE
1153 CACHE 30
1154
1155 create table stua(
1156 sid number primary key,
1157 sname varchar2(20)
1158 )
1159 insert into stua values(stuq.NEXTVAL,'小明');
1160 insert into stua values(stuq.NEXTVAL,'小张');
1161 insert into stua values(stuq.NEXTVAL,'小明');
1162 insert into stua values(stuq.NEXTVAL,'小张');
1163 insert into stua values(stuq.NEXTVAL,'小明');
1164 insert into stua values(stuq.NEXTVAL,'小张');
1165 insert into stua values(stuq.NEXTVAL,'小明');
1166 insert into stua values(stuq.NEXTVAL,'小张');
1167 select * from stua
1168 drop SEQUENCE stuq
1169 --表格对数据进行判定，非法的改动拒绝
1170 五大约束
1171 主建 primary，外键 foreign，唯一 Unique，非空 not null，检验 check
1172 主键（灵魂）
1173 不能为空
1174 不能重复（唯一）
1175 --1简写
1176 create table stu2(
1177 sid number primary key ,
1178 sname varchar2(100)
1179 )
1180 --2（完整写法
1181 create table stu2(
1182 sid number,
1183 sname varchar2(100) not null unique,--不为空，不重名
1184 constraint pk_sid primary key (sid)
1185 )
1186 成绩表主键
1187 create table mark2(
1188 sid number,
1189 cid number,
1190 cmark number,
1191 constraint pk_sid primary key (sid,cid)
1192 )
1193 --check = where 时间除外
1194 create table stu4(
1195 sid number primary key ,
1196 sname varchar2(100) not null ,
1197 ssex char(3) check ( ssex in ('男','女') )
1198 --amk number check (amk>=0 and amk<=100) ,--均分
1199
1200 )
1201 --外键，指向的是主键，比如marksid cid 是外建，
1202
1203 select * from STUDENT
1204 drop table student;
1205 drop table mark;
1206 drop table COURSE;
1207 drop table TEACHER;
1208 create table student(
1209 sid int ,
1210 sname varchar2(100) ,
1211 sage int not null ,
1212 ssex char(3) check ( ssex in ('男','女') ),
1213 snativeplace varchar2(100) ,
1214 smajor varchar2(100) ,
1215 sclass varchar2(100) ,
1216 snative varchar2(100) ,
1217 brith varchar2(100) ,
1218 constraint pk_sid primary key (sid)
1219
1220 )
1221
1222 select * from student
1223 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10001,'廖官忠',21,'男','福建','信计','2班','汉族');
1224 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10002,'张三',20,'男','江苏','信计','2班','汉族');
1225 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10003,'李四',19,'男','福建','信计','2班','傣族');
1226 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10004,'萧瑾',21,'女','福建','统计','1班','汉族');
1227 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10005,'叶晶',21,'女','上海','传煤','1班','朝鲜族');
1228 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10006,'萧瑾',19,'女','北京','统计','2班','汉族');
1229 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10007,'李宁',21,'男','云南','信控','1班','傣族');
1230 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10008,'唐品',18,'男','江苏','信计','2班','汉族');
1231 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10009,'吴强',20,'男','山东','统计','1班','汉族');
1232 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10010,'欧阳锋',22,'男','四川','应数','1班','汉族');
1233 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10011,'王充样',23,'男','黑龙江','统计','2班','汉族');
1234 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10012,'卫小宝',17,'女','福建','统计','2班','汉族');
1235 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10013,'李绍',21,'女','福建','信计','2班','汉族');
1236 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10014,'黄马华',22,'男','浙江','心理学','1班','汉族');
1237 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10015,'艾蔚儿',19,'女','福建','计算机','1班','黎族');
1238
1239 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10016,'赵若辰',21,'男','江苏','心理学','1班','汉族');
1240 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10017,'徐扬',22,'男','青海','计算机','1班','汉族');
1241 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative) values (10018,'徐静静',19,'女','安徽','计算机','1班','黎族');
1242 insert into student (sid,sname,sage,ssex,snativeplace,smajor,sclass,snative,brith) values (10019,'徐静静',19,'女','安徽','计算机','1班','黎族',to_date('2018-2-9','yyy-mm-dd'));
1243
1244 create table teacher (
1245 tid int,p
1246 tname varchar2(100),
1247 tsex char(3) check ( tsex in ('男','女') ),
1248 tage int,
1249 tlvl varchar2(100),
1250 constraint te_tid primary key (tid)
1251 )
1252 select * from teacher
1253 insert into teacher (tid,tname,tsex,tage,tlvl) values (30001,'马六','男',45,'高级教师');
1254 insert into teacher (tid,tname,tsex,tage,tlvl) values (30002,'胡美丽','女',32,'中级教师');
1255 insert into teacher (tid,tname,tsex,tage,tlvl) values (30003,'李强','男',40,'高级教师');
1256 insert into teacher (tid,tname,tsex,tage,tlvl) values (30004,'胡适','男',55,'教授');
1257 insert into teacher (tid,tname,tsex,tage,tlvl) values (30005,'钱枫','男',37,'高级教师');
1258 insert into teacher (tid,tname,tsex,tage,tlvl) values (30006,'戴安安','女',27,'中级教师');
1259 insert into teacher (tid,tname,tsex,tage,tlvl) values (30007,'张伯伦','男',47,'教授');
1260
1261 create table mark(
1262 sid number,
1263 cid number,
1264 cmark number,
1265 constraint pk_sid_cid primary key (sid,cid),
1266 constraint fk_sid foreign key (sid) references student(sid),
1267 constraint fk_cid foreign key (cid) references student(cid)
1268 )
1269 insert into mark (sid,cid,cmark) values (10001,2001,85);
1270 insert into mark (sid,cid,cmark) values (10001,2002,75);
1271 insert into mark (sid,cid,cmark) values (10001,2003,80);
1272 insert into mark (sid,cid,cmark) values (10001,2004,70);
1273 insert into mark (sid,cid,cmark) values (10001,2005,60);
1274 insert into mark (sid,cid,cmark) values (10001,2006,95);
1275 insert into mark (sid,cid,cmark) values (10001,2007,70);
1276 insert into mark (sid,cid,cmark) values (10002,2001,80);
1277 insert into mark (sid,cid,cmark) values (10002,2002,65);
1278 insert into mark (sid,cid,cmark) values (10002,2003,70);
1279 insert into mark (sid,cid,cmark) values (10002,2004,80);
1280 insert into mark (sid,cid,cmark) values (10002,2005,55);
1281 insert into mark (sid,cid,cmark) values (10002,2006,78);
1282 insert into mark (sid,cid,cmark) values (10002,2007,82);
1283 insert into mark (sid,cid,cmark) values (10003,2001,69);
1284 insert into mark (sid,cid,cmark) values (10003,2002,57);
1285 insert into mark (sid,cid,cmark) values (10003,2003,90);
1286 insert into mark (sid,cid,cmark) values (10003,2004,80);
1287 insert into mark (sid,cid,cmark) values (10003,2005,77);
1288 insert into mark (sid,cid,cmark) values (10003,2006,92);
1289 insert into mark (sid,cid,cmark) values (10003,2007,80);
1290 insert into mark (sid,cid,cmark) values (10004,2001,85);
1291 insert into mark (sid,cid,cmark) values (10004,2002,76);
1292 insert into mark (sid,cid,cmark) values (10004,2003,66);
1293 insert into mark (sid,cid,cmark) values (10004,2004,54);
1294 insert into mark (sid,cid,cmark) values (10004,2005,80);
1295 insert into mark (sid,cid,cmark) values (10004,2006,73);
1296 insert into mark (sid,cid,cmark) values (10004,2007,80);
1297 insert into mark (sid,cid,cmark) values (10005,2001,93);
1298 insert into mark (sid,cid,cmark) values (10005,2002,82);
1299 insert into mark (sid,cid,cmark) values (10005,2003,71);
1300 insert into mark (sid,cid,cmark) values (10005,2004,68);
1301 insert into mark (sid,cid,cmark) values (10005,2005,70);
1302 insert into mark (sid,cid,cmark) values (10005,2006,86);
1303 insert into mark (sid,cid,cmark) values (10005,2007,90);
1304 insert into mark (sid,cid,cmark) values (10006,2001,69);
1305 insert into mark (sid,cid,cmark) values (10006,2002,48);
1306 insert into mark (sid,cid,cmark) values (10006,2003,90);
1307 insert into mark (sid,cid,cmark) values (10006,2004,68);
1308 insert into mark (sid,cid,cmark) values (10006,2005,80);
1309 insert into mark (sid,cid,cmark) values (10006,2006,88);
1310 insert into mark (sid,cid,cmark) values (10006,2007,70);
1311 insert into mark (sid,cid,cmark) values (10007,2001,77);
1312 insert into mark (sid,cid,cmark) values (10007,2002,75);
1313 insert into mark (sid,cid,cmark) values (10007,2003,82);
1314 insert into mark (sid,cid,cmark) values (10007,2004,67);
1315 insert into mark (sid,cid,cmark) values (10007,2005,84);
1316 insert into mark (sid,cid,cmark) values (10007,2006,95);
1317 insert into mark (sid,cid,cmark) values (10007,2007,73);
1318 insert into mark (sid,cid,cmark) values (10008,2001,97);
1319 insert into mark (sid,cid,cmark) values (10008,2002,86);
1320 insert into mark (sid,cid,cmark) values (10008,2003,68);
1321 insert into mark (sid,cid,cmark) values (10008,2004,81);
1322 insert into mark (sid,cid,cmark) values (10008,2005,71);
1323 insert into mark (sid,cid,cmark) values (10008,2006,78);
1324 insert into mark (sid,cid,cmark) values (10008,2007,64);
1325 insert into mark (sid,cid,cmark) values (10009,2001,90);
1326 insert into mark (sid,cid,cmark) values (10009,2002,75);
1327 insert into mark (sid,cid,cmark) values (10009,2003,60);
1328 insert into mark (sid,cid,cmark) values (10009,2004,57);
1329 insert into mark (sid,cid,cmark) values (10009,2005,90);
1330 insert into mark (sid,cid,cmark) values (10009,2006,85);
1331 insert into mark (sid,cid,cmark) values (10009,2007,91);
1332 insert into mark (sid,cid,cmark) values (10010,2001,85);
1333 insert into mark (sid,cid,cmark) values (10010,2002,62);
1334 insert into mark (sid,cid,cmark) values (10010,2003,72);
1335 insert into mark (sid,cid,cmark) values (10010,2004,81);
1336 insert into mark (sid,cid,cmark) values (10010,2005,91);
1337 insert into mark (sid,cid,cmark) values (10010,2006,90);
1338 insert into mark (sid,cid,cmark) values (10010,2007,66);
1339 insert into mark (sid,cid,cmark) values (10011,2001,55);
1340 insert into mark (sid,cid,cmark) values (10011,2002,75);
1341 insert into mark (sid,cid,cmark) values (10011,2003,67);
1342 insert into mark (sid,cid,cmark) values (10011,2004,87);
1343 insert into mark (sid,cid,cmark) values (10011,2005,88);
1344 insert into mark (sid,cid,cmark) values (10011,2006,77);
1345 insert into mark (sid,cid,cmark) values (10011,2007,61);
1346 insert into mark (sid,cid,cmark) values (10012,2001,77);
1347 insert into mark (sid,cid,cmark) values (10012,2002,81);
1348 insert into mark (sid,cid,cmark) values (10012,2003,91);
1349 insert into mark (sid,cid,cmark) values (10012,2004,67);
1350 insert into mark (sid,cid,cmark) values (10012,2005,60);
1351 insert into mark (sid,cid,cmark) values (10012,2006,80);
1352 insert into mark (sid,cid,cmark) values (10012,2007,74);
1353 insert into mark (sid,cid,cmark) values (10013,2001,88);
1354 insert into mark (sid,cid,cmark) values (10013,2002,75);
1355 insert into mark (sid,cid,cmark) values (10013,2003,90);
1356 insert into mark (sid,cid,cmark) values (10013,2004,60);
1357 insert into mark (sid,cid,cmark) values (10013,2005,71);
1358 insert into mark (sid,cid,cmark) values (10013,2006,95);
1359 insert into mark (sid,cid,cmark) values (10013,2007,89);
1360 insert into mark (sid,cid,cmark) values (10014,2001,65);
1361 insert into mark (sid,cid,cmark) values (10014,2002,72);
1362 insert into mark (sid,cid,cmark) values (10014,2003,68);
1363 insert into mark (sid,cid,cmark) values (10014,2004,67);
1364 insert into mark (sid,cid,cmark) values (10014,2005,60);
1365 insert into mark (sid,cid,cmark) values (10014,2006,75);
1366 insert into mark (sid,cid,cmark) values (10014,2007,81);
1367 insert into mark (sid,cid,cmark) values (10015,2001,55);
1368 insert into mark (sid,cid,cmark) values (10015,2002,67);
1369 insert into mark (sid,cid,cmark) values (10015,2003,61);
1370 insert into mark (sid,cid,cmark) values (10015,2004,71);
1371 insert into mark (sid,cid,cmark) values (10015,2005,81);
1372 insert into mark (sid,cid,cmark) values (10015,2006,88);
1373 insert into mark (sid,cid,cmark) values (10015,2007,90);
1374 insert into mark (sid,cid,cmark) values (10016,2001,72);
1375 insert into mark (sid,cid,cmark) values (10016,2002,82);
1376 insert into mark (sid,cid,cmark) values (10016,2003,92);
1377 insert into mark (sid,cid,cmark) values (10016,2004,62);
1378 insert into mark (sid,cid,cmark) values (10016,2005,63);
1379 insert into mark (sid,cid,cmark) values (10016,2006,73);
1380 insert into mark (sid,cid,cmark) values (10016,2007,83);
1381 insert into mark (sid,cid,cmark) values (10017,2001,85);
1382 insert into mark (sid,cid,cmark) values (10017,2002,77);
1383 insert into mark (sid,cid,cmark) values (10017,2003,87);
1384 insert into mark (sid,cid,cmark) values (10017,2004,79);
1385 insert into mark (sid,cid,cmark) values (10017,2005,69);
1386 insert into mark (sid,cid,cmark) values (10017,2006,58);
1387 insert into mark (sid,cid,cmark) values (10017,2007,74);
1388 insert into mark (sid,cid,cmark) values (10018,2001,88);
1389 insert into mark (sid,cid,cmark) values (10018,2002,78);
1390 insert into mark (sid,cid,cmark) values (10018,2003,88);
1391 insert into mark (sid,cid,cmark) values (10018,2004,78);
1392 insert into mark (sid,cid,cmark) values (10018,2005,68);
1393 insert into mark (sid,cid,cmark) values (10018,2006,98);
1394 insert into mark (sid,cid,cmark) values (10018,2007,78);
1395
1396 create table course(
1397 cid number,
1398 cname varchar2(100),
1399 cval number,
1400 ctime number,
1401 tid number,
1402 constraint pk_cid primary key (cid),
1403 constraint te_tid foreign key (tid) reference teacher(tid)
1404 );
1405 select * from course
1406 insert into course (cid,cname,cval,ctime,tid) values (2001,'数学',6,64,30001);
1407 insert into course (cid,cname,cval,ctime,tid) values (2002,'英语',4,64,30002);
1408 insert into course (cid,cname,cval,ctime,tid) values (2003,'体育',2,32,30003);
1409 insert into course (cid,cname,cval,ctime,tid) values (2004,'马克思主义',6,64,30004);
1410 insert into course (cid,cname,cval,ctime,tid) values (2005,'计算机基础',3,48,30005);
1411 insert into course (cid,cname,cval,ctime,tid) values (2006,'心理学',4,48,30006);
1412 insert into course (cid,cname,cval,ctime,tid) values (2007,'空间天气学',6,64,30007);
1413 insert into teacher (tid,tname,tsex,tage,tlvl) values (30001,'马六','男',45,'高级教师');
1414 insert into teacher (tid,tname,tsex,tage,tlvl) values (30002,'胡美丽','女',32,'中级教师');
1415 insert into teacher (tid,tname,tsex,tage,tlvl) values (30003,'李强','男',40,'高级教师');
1416 insert into teacher (tid,tname,tsex,tage,tlvl) values (30004,'胡适','男',55,'教授');
1417 insert into teacher (tid,tname,tsex,tage,tlvl) values (30005,'钱枫','男',37,'高级教师');
1418 insert into teacher (tid,tname,tsex,tage,tlvl) values (30006,'戴安安','女',27,'中级教师');
1419 insert into teacher (tid,tname,tsex,tage,tlvl) values (30007,'张伯伦','男',47,'教授');
1420
1421
1422
1423 drop table writer;
1424 create table writer (
1425 wsid char(100),
1426 wname char(50),
1427 wsex char(3),
1428 warea varchar2(100) ,
1429 wtelephone char(100) not null ,
1430 wid char(100) not null ,
1431 wtotal varchar2(100),
1432 wword varchar2 (100),
1433 wnote varchar2(100),
1434
1435 constraint w_wsid primary key (wsid)
1436
1437 );
1438 drop table wmony;
1439 --打赏表
1440 --mony
1441 create table wmony(
1442 wsid char(100),
1443 mbook char(100),
1444 mmony char(100),
1445 constraint e_wsid primary key (wsid)
1446 );
1447
1448
1449 --作者等级
1450 --grade
1451 drop table wgrade;
1452 create table wgrade (
1453 wsid char(100),
1454 ggrade char(100),
1455 constraint g_wsid primary key (wsid)
1456 );
1457
1458 --排行榜
1459 --list
1460 drop table wqlist;
1461 create table wqlist (
1462 wsid char(100),
1463 wname char(100),
1464 lrank char(100),
1465 constraint l_wsid primary key (wsid)
1466
1467 );
1468
1469 insert into writer values ('001','唐三','男','徐州','18851923638','32038219565805456','107万','174万','18444');
1470 insert into writer values ('002','土豆','男','南京','18851923635','32038219582658048','105万','141万','1845');
1471 insert into writer values ('003','番茄','女','上海','18851923675','320382195826585586','104万','147万','184844');
1472 insert into writer values ('004','五一','女','南京','18851923634','320382195826587','488万','177万','18445');
1473 insert into writer values ('005','胡歌','男','长春','18851923678','32038219582658071','152万','152万','1875');
1474 insert into writer values ('006','西瓜','男','徐州','18851923688','3203821958265804452','1474万','14万','1844');
1475 insert into writer values ('007','独角','男','南京','18851023635','3203821958265450483','14万','151万','184');
1476 insert into writer values ('008','南瓜','女','*','18851927635','3203821958885584','17万','14万','1848');
1477 insert into writer values ('009','东瓜','女','香港','18851973634','320382198826580575','18万','147万','185');
1478 insert into writer values ('0010','北瓜','男','长春','18851923678','3203821957826580786','15万','14万','185');
1479 commit;
1480
1481
1482
1483 insert into wmony values('001','斗破苍穹','100万');
1484 insert into wmony values('002','圣墟','125万');
1485 insert into wmony values('003','霸道总裁爱上我','245万');
1486 insert into wmony values('004','高冷男神','152万');
1487 insert into wmony values('005','我是流氓','1424万');
1488 insert into wmony values('006','三体','100万');
1489 insert into wmony values('007','明朝','125万');
1490 insert into wmony values('008','王子','245万');
1491 insert into wmony values('009','汉朝','152万');
1492 insert into wmony values('0010','981','1424万');
1493 commit;
1494 insert into wgrade values ('001','大神');
1495 insert into wgrade values ('002','大神');
1496 insert into wgrade values ('003','封圣');
1497 insert into wgrade values ('004','大神');
1498 insert into wgrade values ('005','小白');
1499
1500 commit;
1501 insert into wqlist values('001','斗破苍穹','1');
1502 insert into wqlist values('002','圣墟','2');
1503 insert into wqlist values('003','霸道总裁爱上我','3');
1504 insert into wqlist values('004','高冷男神','4');
1505 insert into wqlist values('005','我是流氓','5');
1506 insert into wqlist values('006','斗破苍穹2','6');
1507 insert into wqlist values('007','明朝','7');
1508 insert into wqlist values('008','王子','8');
1509 insert into wqlist values('009','汉朝','9');
1510 insert into wqlist values('0010','981','10');
1511
1512 commit;
1513 select * from writer;
1514 select * from wmony;
1515 select * from wgrade;
1516 select * from wqlist;
1517
1518
1519
1520 索引
1521 1.什么时候会用到索引
1522
1523 2.索引的作用
1524 节约时间，加快查询速度
1525 3.索引缺点
1526 占空间
1527
1528 create index idx_sage on student (sage) --创建
1529 drop index idx_sage --删除
1530
1531 --视图 view
1532 超链接 指定数据
1533 知保存命令
1534 只有select是才执行
1535 在不违法规则的情况下，可以对数据进行修改
1536
1537 create view myv_stu as
1538 select sid,sname from student where ssex='男'
1539
1540 select * from myv_stu
1541 select * rowid.s,* from student s；
1542
1543
1544 create or replace view avgMark as
1545 select s.sid,sname ,avg(cmark) amk from student s left outer join mark m on s.sid=m.sid group by s.sid,sname;
1546 select * from avgMark
1547
1548 select sid,avg(cmark) amk from mark group by sid ;
1549
1550 select sid
1551 from (select sid,avg(cmark) amk from mark group by sid) where amk=(select sid,avg(cmark) amk from mark group by sid )
1552 ---删除
1553 truncate table student--永久性删除 DDL 不删除结构，删除数据
1554 delete from student where 1=1--在roolback中回到初始值
1555
1556
1557
1558 数据库编程
1559
1560 plsql结构
1561 【declare】可选项
1562
1563 变量定义区
1564 begin
1565 代码区
1566 【exception】
1567
1568 异常处理区
1569 end;
1570
1571
1572 --赋值 i NUMBER:=100;
1573 DECLARE
1574 i NUMBER:=100;
1575
1576 BEGIN
1577 DBMS_OUTLN.PUT_LINE(i);
1578
1579
1580
1581 end;
1582 --赋值
1583 DECLARE
1584 i number:=10002;
1585 s varchar2(100);
1586
1587 BEGIN
1588 select sname into s from student
1589 where sid=i;
1590 DBMS_OUTput.PUT_LINE(s);
1591
1592
1593
1594 end;
1595
1596 DECLARE
1597 i number:=10002;
1598 age number;
1599 s varchar2(100);
1600
1601 BEGIN
1602 select sname,sage into s,age from student
1603 where sid=i;
1604 DBMS_OUTput.PUT_LINE(s||';'||age);
1605
1606
1607
1608
1609
1610
1611
1612
1613
1614
1615 end;
1616
1617
1618
1619
1620 select sname
1621 from student s,(select sid,avg(cmark) from mark group by sid) m
1622 where s.sid=m.sid
1623
1624
1625
1626 create view am as
1627
1628 select sname,amk from student s,(select sid,avg(cmark) amk from mark group by sid) m
1629 where s.sid=m.sid;
1630
1631 select sid,avg(cmark) amk from mark group by sid m
1632
1633 select * from am
1634
1635 select sname, case
1636 when amk>=90 then '优秀'
1637 when amk>=80 then '良好'
1638 when amk>=60 then '一般'
1639
1640 else '不及格'
1641 end
1642 from am;
1643
1644
1645
1646
1647 drop view am
1648
1649
1650 declare
1651 sno student.sid %type ;
1652 begin
1653 select sid from student where sname='&此人的学号为||sno'
1654 exception
1655 when too_many_rows then
1656 dbns_output.put_line('异常')
1657
1658 end;
1659
1660 declare
1661 snm student.snm %type ;
1662 cnt number;
1663
1664 begin
1665 select sname into snm from student where sid='&此人为';
1666 update student set scmark=scmark+5
1667 where sid=(
1668 select sid from student where snativeplace='安徽'
1669 ) and (select avg(cmark) from mark where sid in (select sid from student where snativeplace='安徽') group by sid)<70
1670 and cname='数学'
1671
1672
1673 update student set scmark=scmark+2
1674 where sid=(
1675 select sid from student where snativeplace='北京'
1676 ) and (select avg(cmark) from mark where sid in (select sid from student where snativeplace='北京'))<75
1677
1678 cnt:=sql%rowcount;
1679
1680 dbms_output.put_line('更新'||cnt||'门')；
1681 exception
1682 when too_many_rows then
1683 dbms_output.put_line('异常');
1684 when no_data_found then
1685 dbms_output.put_line('qw异常');
1686 when others then
1687 dbms_output.put_line('qw异常');
1688 end;
1689
1690
1691
1692 declare
1693 snm student.sname%type:='请输入姓名';sno student.sid%type;
1694 amk mark.cmark%type;
1695 score mark.cmark%type;snt student.snativeplace%type;
1696 cnt number;
1697 cno course.cid%type;
1698 begin
1699 --取出所有数据，保存变量中
1700 select sid,snativeplace into sno,snt
1701 from student where sname=snm;
1702 select avg(cmark) into amk from mark where sid=sno;
1703 select cid into cno from course where cname='数学';
1704
1705 select cmark into score from mark where cid=cno and sid=sno;
1706
1707 --条件判断
1708 if snt='安徽' and amk<70
1709 then
1710 score:=score+5;
1711 elsif
1712 snt='北京' and amk<75
1713 then
1714 update mark set cmark=cmark+2
1715
1716
1717 cnt:=sql%rowcount;
1718 dbms_output.put_line('更新'||cnt||'门')
1719 end if;
1720 --在条件骨架插入
1721 exception
1722 when too_many_rows then
1723 dbms_output.put_line('重复名字');
1724 when no_data_found then
1725 dbms_output.put_line('名字为口');
1726 when others then
1727 dbms_output.put_line('未知异常');
1728 --异常
1729
1730 end;
1731
1732
1733 --第二题：
1734 --扫描每个学生：
1735 --如果这个学生来自壮族或者苗族，那么其各科成绩+3分，
1736 --且不进行后续判断
1737 --如果这个学生来自安徽，且均分低于70
1738 --那么将其数学成绩+5分
1739 --如果这个学生来自北京，且均分低于75分，那么将其各课
1740 --成绩低于70分的课程成绩+2分，并打印共更新了几门课。
1741 --当各学生成绩更新完毕后打印这个学生的各科成绩
1742 --要求显示 XXX的数学成绩是。。。分，成绩优秀
1743 -- XXX的英语成绩是。。。分，成绩良好-
1744
1745 select * from student;
1746 declare
1747 snm student.sname%type;sno student.sid%type;
1748 amk mark.cmark%type;
1749 score mark.cmark%type;snt student.snativeplace%type;
1750 cnt number;
1751 cno course.cid%type;
1752 sna student.snative%type;
1753 line student%rowtype;
1754 line2 course%rowtype;
1755 cnm course.cname%type;
1756 cM course.cid%type;
1757 begin
1758 for line in (select * from student) loop
1759 select sid into sno
1760 from student
1761 where sid = line.sid;
1762 select sname into snm
1763 from student
1764 where sid = line.sid;
1765 select snativeplace into snt
1766 from student
1767 where sid = sno;
1768 select snative into sna
1769 from student
1770 where sid = sno;
1771
1772 select avg(cmark) into amk
1773 from mark
1774 where sid = line.sid;
1775
1776 select cid into cno from course where cname='数学';
1777
1778 select cmark into score from mark where sid=sno and cid=cno;
1779 if sna='壮族' or sna='苗族'
1780 then
1781 update mark set cmark=cmark+3
1782 where sid=sno;
1783 elsif
1784 snt='安徽' and amk<70
1785 then
1786 update mark set cmark=cmark+5
1787 where cid=score and sid=sno;
1788 elsif
1789 snt='北京' and amk<75
1790 then
1791 update mark set cmark=cmark+2
1792 where sid = sno and cmark<70;
1793
1794 cnt:=sql%rowcount;
1795 dbms_output.put_line('更新'||cnt||'门')
1796 end if;
1797 for line2 in (select * from course) loop
1798 select cid into cno
1799 from course
1800 where cid = line2.cid;
1801 select cname into cnm
1802 from course
1803 where cid = line2.cid;
1804 select cname into cnm
1805 from course
1806 where cid = cno;
1807 select cmark into cmk
1808 from mark
1809 where cid = cno and sid =sno;
1810 if cmk > 90
1811 then
1812 DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',成绩优秀');
1813 elsif cmk > 80
1814 then
1815 DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',成绩良好');
1816 elsif cmk > 60
1817 then
1818 DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',成绩一般');
1819 else
1820 DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',不及格');
1821 end if;
1822
1823 end loop;
1824 end loop;
1825
1826 exception
1827 when too_many_rows then
1828 dbms_output.put_line('重复名字');
1829 when no_data_found then
1830 dbms_output.put_line('名字为空');
1831 when others then
1832 dbms_output.put_line('未知异常');
1833 end;
1834
1835
1836
1837
1838 declare
1839 line student%rowtype;
1840 snm student.sname%type;
1841 snv student.snative%type;
1842 snp student.snativeplace%type;
1843 sno student.sid%type;
1844 amk mark.cmark%type;
1845 cM course.cid%type;
1846 line2 course%rowtype;
1847 cnm course.cname%type;
1848 cno course.cid%type;
1849 cmk mark.cmark%type;
1850 cnt number;
1851
1852 begin
1853 for line in (select * from student) loop
1854 select sid into sno
1855 from student
1856 where sid = line.sid;
1857 select sname into snm
1858 from student
1859 where sid = sno;
1860 select snativeplace into snp
1861 from student
1862 where sid = sno;
1863 select snative into snv
1864 from student
1865 where sid = sno;
1866 select avg(cmark) into amk
1867 from mark
1868 where sid = sno;
1869 select cid into cM
1870 from course
1871 where cname = '数学';
1872 -----------------------------------------
1873
1874 if snv = '壮族' or snv = '苗族'
1875 then
1876 update mark set cmark = cmark + 3
1877 where sid = sno;
1878 elsif snp = '安徽' and amk < 70
1879 then
1880 update mark set cmark = cmark + 5
1881 where cid = cM and sid = sno;
1882 elsif snp = '北京' and amk < 75
1883 then
1884 update mark set cmark = cmark + 2
1885 where sid = sno and cmark < 70;
1886 cnt := SQL%rowcount;
1887 DBMS_OUTPUT.put_line('共更新了'||cnt||'门课');
1888 end if;
1889 for line2 in (select * from course) loop
1890 select cid into cno
1891 from course
1892 where cid = line2.cid;
1893 select cname into cnm
1894 from course
1895 where cid = cno;
1896 select cmark into cmk
1897 from mark
1898 where cid = cno and sid = sno;
1899 if cmk > 90
1900 then
1901 DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',成绩优秀');
1902 elsif cmk > 80
1903 then
1904 DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',成绩良好');
1905 elsif cmk > 60
1906 then
1907 DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',成绩一般');
1908 else
1909 DBMS_OUTPUT.put_line(snm||'的'||cnm||'是'||cmk||',不及格');
1910 end if;
1911 end loop;
1912 end loop;
1913 end;
1914 S
1915 --第二题：
1916 --扫描每个学生：
1917 --如果这个学生来自壮族或者苗族，那么其各科成绩+3分，
1918 --且不进行后续判断
1919 --如果这个学生来自安徽，且均分低于70
1920 --那么将其数学成绩+5分
1921 --如果这个学生来自北京，且均分低于75分，那么将其各课
1922 --成绩低于70分的课程成绩+2分，并打印共更新了几门课。
1923 --当各学生成绩更新完毕后打印这个学生的各科成绩
1924 --要求显示 XXX的数学成绩是。。。分，成绩优秀
1925 -- XXX的英语成绩是。。。分，成绩良好-
1926 select * from course
1927 select * from student
1928
1929 declare
1930 line student%rowtype;
1931 line2 course%rowtype;
1932 snm student.sname%type;
1933 sna student.snative%type;
1934 snt student.snativeplace%type;
1935 sno student.sid%type;
1936 cmk mark.cmark%type;
1937 amk mark.cmark%type;
1938 cno course.cid%type;
1939 cna course.cname%type;
1940 cm course.cid%type;
1941
1942
1943
1944 begin
1945
1946 end;
1947
1948
1949
1950
1951 select CID,cmark from MARK where SID=(select SID from STUDENT where SNAME='张三')
1952 and CMARK=(select MAX(CMARK) from MARK m where SID=(select SID from STUDENT where SNAME='张三'));
1953 --编写存储过程，modMK，要求输入学号，课程号，成绩
1954 --如果学号不存在，课程号在成绩表中不存在，抛出异常
1955 --如果sid和cid指定的记录不存在测向mark插入新的记录
1956 --sid和cid在这条记录修改为新的marmark已经存在则如果
1957 --如果输入的成绩参数小于0，和大于100，异常
1958 create or replace procedure qa(
1959 sno in student.sid%type,
1960 cna course.cid%type,
1961 cm mark.cmark%type
1962 )
1963 as
1964
1965 nsno student.sid%type;
1966 ncna course.cid%type,
1967 ncm mark.cmark%type
1968
1969 begin
1970 when cm<0 and cm>100
1971 then
1972 dbms_output.put_line('输入值不正确');
1973 when sid!=sno then
1974 dbms_output.put_line('学号不存在');
1975 when cid!=cno then
1976 dbms_output.put_line('课程号不存在');
1977
1978 select cid into ncno from course where cid=cna;
1979 select sid into nsno from student where sid=sno;
1980 select cmark into ncm from mark where sid=sno and cid=cna;
1981 select * from student s,mark m,course c where s.sid=m.sid and c.cid=m.cid m;
1982 if sid!=sno or cid!=cna
1983 then
1984 insert into m values (sno,cna,cm);
1985 end if;
1986 end;
1987
1988
1989 select * from mark
1990 exception
1991 when too_many_rows then
1992 dbms_output.put_line('重复名字');
1993 when no_data_found then
1994 dbms_output.put_line('名字为空');
1995 when others then
1996 dbms_output.put_line('未知异常');
1997
1998
1999
2000 drop procedure qa
2001 create or replace procedure qa(
2002 sno in student.sid%type,
2003 cna in course.cid%type,
2004 cm in mark.cmark%type
2005 )
2006 as
2007
2008 nsno student.sid%type;
2009 ncna course.cid%type;
2010 ncm mark.cmark%type;
2011 numerro exception;
2012 begin
2013 if cm<0 or cm>100
2014 then
2015 raise numerro;
2016 end if;
2017
2018 select cid into ncna from course where cid=cna;
2019 select sid into nsno from student where sid=sno;
2020 select cmark into ncm from mark where sid=sno and cid=cna;
2021
2022
2023 if sno!=nsno and cm !=ncm
2024 then
2025 insert into mark(sid,cid,cmark)values(sno,cna,cm);
2026 elsif
2027 sno = nsno and cm=ncm
2028 then
2029 update mark set cmark=cm
2030 where sid=sno and cid=cna;
2031 end if;
2032
2033 exception
2034 when NO_DATA_FOUND THEN
2035 DBMS_OUTPUT.put_line('此用户不存在');
2036 when OTHERS THEN
2037 DBMS_OUTPUT.put_line('其他未知异常');
2038
2039
2040 end;
2041 begin
2042 qa(10001,2001,45)
2043 end;
2044
2045 create table stubak
2046 as select * from student where 1=2
2047 select * from stubak
2048 create or replace trigger stug
2049 before delete on student--删除
2050 begin
2051 insert into stubak values(
2052 :old.sid,:old.sname,:old.sage,:old.ssex,
2053 :old.snativeplace,
2054 :old.smajor,
2055 :old.sclass,
2056 :old.sname
2057 ) ;
2058 end;
2059
2060
2061
2062
2063 create or replace trigger jf
2064 after update on student
2065 for each row
2066 as
2067 snm student.sname%type;
2068 sno student.sid%type:='&请输入学生学号';
2069 amk mark.cmark%type;
2070
2071
2072
2073 begin
2074 select sname into snm from student where sid =sno;
2075 select avg(cmark) into amk from mark where sid=sno;
2076 dbms_output.put_line(snm||'的均分是'||amk);
2077 if amk>80
2078 then
2079 update student set
2080 sclass='1班' ;
2081 else
2082 sclass='2班';
2083
2084
2085 end if;
2086 end;
2087
2088
2089
2090
2091 -- 从账户一向账户二转账DECLARE
2092 v_money NUMBER(8, 2); -- 转账金额
2093 v_balance account.balance%TYPE; -- 账户余额
2094 BEGIN
2095 v_money := &转账金额; -- 输入转账金额
2096 -- 从账户一减钱
2097 UPDATE account SET balance = balance - v_money WHERE id=&转出账户
2098 RETURNING balance INTO v_balance;
2099 IF SQL%NOTFOUND THEN
2100 RAISE_APPLICATION_ERROR(-20001, '没有该账户：'||&转出账户);
2101 END IF;
2102 IF v_balance < 0 THEN
2103 RAISE_APPLICATION_ERROR(-20002, '账户余额不足');
2104 END IF;
2105
2106 -- 向账户二加钱
2107 UPDATE account SET balance = balance + v_money WHERE id=&转入账户;
2108 IF SQL%NOTFOUND THEN
2109 RAISE_APPLICATION_ERROR(-20001, '没有该账户：'||&转入账户);
2110 END IF;
2111
2112 -- 如果没有异常，则提交事务
2113 COMMIT;
2114 DBMS_OUTPUT.PUT_LINE('转账成功');
2115
2116 EXCEPTION
2117 WHEN OTHERS THEN
2118 ROLLBACK; -- 出现异常则回滚事务
2119 DBMS_OUTPUT.PUT_LINE('转账失败：');
2120 DBMS_OUTPUT.PUT_LINE(SQLERRM);END;
2121
2122
2123
2124 ---
2125
2126 Oracle 用户管理权限
2127
2128
2129 CRESTE PROFILE LIM
2130 failed_login_attempts 3;
2131
2132
2133 ldentified
2134
2135 select s.sid,avg(),from student s,mark m,course c where s.sid=m.sid and m.cid= c.cid group by s.sid,m.cid;
2136 select avg() from (select * from student s,mark m,course c where s.sid=m.sid and m.cid= c.cid )
2137
2138
2139 select sid,avg(cmark) from (
2140 select * from student s,mark m,course c where s.sid=m.sid and m.cid= c.cid s)
2141 group by sid
2142 select sid,avg(cmark) from STUDENT,mark,COURSE where STUDENT.sid=mark.sid and mark.cid=COURSE.cid
2143 group by sid---各个地区数学均分
2144
2145
2146 create view avg_w as
2147 select s.sid,s.sname,avg(cmark) from student s,mark m,course c where s.sid=m.sid and m.cid= c.cid group by s.sid , c.sid;
2148
2149
2150 delimiter//
2151 create procedure add_pro
2152 (a int,b int,out max int,out min int)
2153 begin
2154 set min = (select s.sid,min(cmark) from student s,mark m,course c where s.sid=m.sid and m.cid= c.cid group by s.sid);
2155 set max= (select s.sid,max(cmark) from student s,mark m,course c where s.sid=m.sid and m.cid= c.cid group by s.sid);
2156
2157
2158 select max(cmark) from mark
2159
2160 select * from avgMark
2161
2162
2163
2164
2165 delimiter//
2166 create procedure add_pro
2167 (a String,out max int,out min int)
2168 begin
2169 max=select s.sid,max(cmark)
2170 from student s,mark m,course c
2171 where s.sid=m.sid and m.cid= c.cid and s.sname = a group by s.sid;
2172
2173 min=select s.sid,min(cmark)
2174 from student s,mark m,course c
2175 where s.sid=m.sid and m.cid= c.cid and s.sname = a group by s.sid;
2176 end;
2177
2178
2179
2180
2181
2182
2183
2184
2185
2186
2187
2188
2189 select s.sid,max(cmark)
2190 from student s,mark m,course c
2191 where s.sid=m.sid and m.cid= c.cid group by s.sid;
2192