2018-07-31
1 ---练习3
2 ---创建sporter表
3 create table sporter(
4 sporterid number(10) constraint sport_id primary key,
5 sname varchar2(20) not null,
6 sex varchar2(10),
7 department varchar2(10) not null
8 );
9 select * from sporter;
10 ---向sporter表中插入数据
11 insert into sporter values(1001,'李盼','女','计算机系');
12 insert into sporter values(1002,'王玥','女','数学系');
13 insert into sporter values(1003,'丁赛','男','计算机系');
14 insert into sporter values(1004,'汪丽','女','物理系');
15 insert into sporter values(1005,'李娜','女','心理系');
16 insert into sporter values(1006,'王骋','女','化学系');
17
18 ---创建item表
19 create table item(
20 itemid varchar2(10) constraint item_id primary key,
21 itemname varchar2(20) not null,
22 loc varchar2(20)
23 );
24
25 select * from item
26
27 ---向item表中插入数据
28 insert into item values('x001','男子五千米','一操场');
29 insert into item values('x002','男子标枪','一操场');
30 insert into item values('x003','男子跳远','二操场');
31 insert into item values('x004','女子跳高','二操场');
32 insert into item values('x005','女子三千米','三操场');
33
34 ---创建grade积分表
35 create table grade(
36 sprterid number(10),
37 itemid varchar2(10),
38 mark number(10) constraint grade_mark check(mark in(6,4,2,0))
39 );
40 ---修改列名
41 alter table grade rename column sprterid to sporterid;
42
43 select * from grade ;
44 ---向grade积分表中插入数据
45 insert into grade values(1001,'x001',6);
46 insert into grade values(1002,'x001',4);
47 insert into grade values(1003,'x001',2);
48 insert into grade values(1004,'x001',0);
49 insert into grade values(1001,'x003',4);
50 insert into grade values(1002,'x003',6);
51 insert into grade values( 1004,'x003',2);
52 insert into grade values(1004,'x004',6);
53 insert into grade values(1006,'x004',4);
54 --要求
55 --1.求出总积分最高的系名及总积分
56 select department ,sum(mark)
57 from sporter natural join grade
58 group by department
59 having sum(mark)=(select max(sum(mark))
60 from sporter natural join grade
61 group by department );
62 ---???
63 --2.查询在一操场进行比赛的项目名称及其冠军的姓名
64 select itemid,max(mark)
65 from sporter natural join grade
66 group by itemid
67 having itemid in(select itemid
68 from item
69 where loc='一操场')
70
71 --3.找出参加了王玥所参加过的项目的其他同学的姓名
72 select distinct sname
73 from sporter natural join grade
74 where itemid in (select itemid
75 from grade
76 where sporterid =(select sporterid
77 from sporter
78 where sname='王玥'))
79 and sporterid!=(select sporterid
80 from sporter
81 where sname='王玥')
82
83 --4.经查,王玥因为使用了违禁药品,其成绩都记为0分,
84 --请在数据库中做出相应修改
85 update grade set mark =0 where sporterid=(select sporterid from sporter where sname='王玥') ;
86
87 --5.经组委会协商,需要删除女子跳高比赛项目
88
89 delete from item where itemname='女子跳高'
1 ---练习3
2 ---创建sporter表
3 create table sporter(
4 sporterid number(10) constraint sport_id primary key,
5 sname varchar2(20) not null,
6 sex varchar2(10),
7 department varchar2(10) not null
8 );
9 select * from sporter;
10 ---向sporter表中插入数据
11 insert into sporter values(1001,'李盼','女','计算机系');
12 insert into sporter values(1002,'王玥','女','数学系');
13 insert into sporter values(1003,'丁赛','男','计算机系');
14 insert into sporter values(1004,'汪丽','女','物理系');
15 insert into sporter values(1005,'李娜','女','心理系');
16 insert into sporter values(1006,'王骋','女','化学系');
17
18 ---创建item表
19 create table item(
20 itemid varchar2(10) constraint item_id primary key,
21 itemname varchar2(20) not null,
22 loc varchar2(20)
23 );
24
25 select * from item
26
27 ---向item表中插入数据
28 insert into item values('x001','男子五千米','一操场');
29 insert into item values('x002','男子标枪','一操场');
30 insert into item values('x003','男子跳远','二操场');
31 insert into item values('x004','女子跳高','二操场');
32 insert into item values('x005','女子三千米','三操场');
33
34 ---创建grade积分表
35 create table grade(
36 sprterid number(10),
37 itemid varchar2(10),
38 mark number(10) constraint grade_mark check(mark in(6,4,2,0))
39 );
40 ---修改列名
41 alter table grade rename column sprterid to sporterid;
42
43 select * from grade ;
44 ---向grade积分表中插入数据
45 insert into grade values(1001,'x001',6);
46 insert into grade values(1002,'x001',4);
47 insert into grade values(1003,'x001',2);
48 insert into grade values(1004,'x001',0);
49 insert into grade values(1001,'x003',4);
50 insert into grade values(1002,'x003',6);
51 insert into grade values( 1004,'x003',2);
52 insert into grade values(1004,'x004',6);
53 insert into grade values(1006,'x004',4);
54 --要求
55 --1.求出总积分最高的系名及总积分
56 select department ,sum(mark)
57 from sporter natural join grade
58 group by department
59 having sum(mark)=(select max(sum(mark))
60 from sporter natural join grade
61 group by department );
62 ---???
63 --2.查询在一操场进行比赛的项目名称及其冠军的姓名
64 select itemid,max(mark)
65 from sporter natural join grade
66 group by itemid
67 having itemid in(select itemid
68 from item
69 where loc='一操场')
70 ---***
71 select itemname,sname
72 from sporter s,item i, grade g
73 where s.sporterid =g.sporterid and i.itemid=g. itemid
74 and loc='一操场' and mark=6
75
76 --3.找出参加了王玥所参加过的项目的其他同学的姓名
77 select distinct sname
78 from sporter natural join grade
79 where itemid in (select itemid
80 from grade
81 where sporterid =(select sporterid
82 from sporter
83 where sname='王玥'))
84 and sporterid!=(select sporterid
85 from sporter
86 where sname='王玥')
87
88 --4.经查,王玥因为使用了违禁药品,其成绩都记为0分,
89 --请在数据库中做出相应修改
90 update grade set mark =0 where sporterid=(select sporterid from sporter where sname='王玥') ;
91
92 --5.经组委会协商,需要删除女子跳高比赛项目
93
94 delete from item where itemname='女子跳高'
老师
练习作业
1 -------------------第五章----------------
2 ---练习1
3 1.写一个查询,显示所有员工姓名,部门编号,部门名称。
4 select e.ename,e.deptno,d.dname
5 from emp e,dept d
6 where e.deptno = d.deptno
7
8 2.写一个查询,显示所有工作在CHICAGO并且奖金不为空的员工姓名,工作地点,奖金
9 select e.ename,d.loc,e.comm
10 from emp e,dept d
11 where e.deptno = d.deptno and upper(d.loc)='CHICAGO' and e.comm>0;
12
13 3.写一个查询,显示所有姓名中含有A字符的员工姓名,工作地点。
14 select e.ename,d.loc
15 from emp e,dept d
16 where e.deptno = d.deptno and e.ename like '%A%';
17
18
19 ---练习2
20 1.查询每个员工的编号,姓名,工资,工资等级,所在工作城市,按照工资等级进行升序排序。
21 select e.empno,e.ename,e.sal,s.grade,d.loc
22 from emp e,dept d ,salgrade s
23 where e.deptno = d.deptno and e.sal between s.losal and s.hisal
24 order by e.sal ;
25
26
27 ---练习3
28 1.查询所有工作在NEW YORK和CHICAGO的员工姓名,员工编号,以及他们的经理姓名,经理编号。
29 select e.ename,e.empno,e.mgr,m.ename
30 from emp e,dept d,emp m
31 where e.deptno = d.deptno and e.mgr = m.empno
32 and upper(d.loc) in ('NEW YORK','CHICAGO')
33
34 2.第上一题的基础上,添加没有经理的员工King,并按照员工编号排序。
35 select e.ename,e.empno,e.mgr,m.ename
36 ---左连接 table1 left join table2 on ...
37 from dept d,emp e left join emp m on e.mgr = m.empno
38 where e.deptno = d.deptno
39 and upper(d.loc) in ('NEW YORK','CHICAGO')
40 order by e.empno
41
42 3.查询所有员工编号,姓名,部门名称,包括没有部门的员工也要显示出来。
43 select e.empno,e.ename,d.dname
44 from emp e left join dept d on e.deptno= d.deptno
45
46 ---练习4
47 使用SQL-99写法,完成如下练习
48 1.创建一个员工表和部门表的交叉连接。
49 select *
50 from emp cross join dept;
51
52 2.使用自然连接,显示入职日期在80年5月1日之后的员工姓名,部门名称,入职日期
53 select e.ename,d.dname,e.hiredate
54 from emp e natural join dept d
55 ---自然连接 自动匹配两个表中的相同字段
56 where e.hiredate>'1-5月-1980'
57
58 3.使用USING子句,显示工作在CHICAGO的员工姓名,部门名称,工作地点
59 --JOIN...USING(公共列名)
60 --using后不要忘记加()
61 select ename,dname,loc
62 from emp join dept using (deptno)
63 where upper(loc)='CHICAGO'
64
65 ---
66 4.使用ON子句,显示工作在CHICAGO的员工姓名,部门名称,工作地点,薪资等级
67 --语法:table1 join table2 on 连接条件;
68
69 select e.ename,d.dname,d.loc,s.grade
70 from emp e ,dept d ,salgrade s
71 where e.deptno = d.deptno and e.sal between s.losal and s.hisal
72 and upper(d.loc)='CHICAGO'
73
74 5.使用左连接,查询每个员工的姓名,经理姓名,没有经理的King也要显示出来。
75 select e.ename,m.ename
76 from emp e left join emp m on e.mgr = m.empno
77
78 6.使用右连接,查询每个员工的姓名,经理姓名,没有经理的King也要显示出来。
79 select e.ename,m.ename
80 from emp m right join emp e on e.mgr = m.empno
81
82 -------------------第六章----------------
83 ---练习1
84 1.查询部门20的员工,每个月的工资总和及平均工资。
85 select sum(sal),avg(sal)
86 from emp
87 group by deptno
88 having deptno =20
89
90 2.查询工作在CHICAGO的员工人数,最高工资及最低工资。
91 select count(empno) 员工人数, max(sal),min(sal)
92 from emp natural join dept
93 group by loc
94 having upper(loc)= 'CHICAGO';
95
96 3.查询员工表中一共有几种岗位类型
97 select job
98 from emp
99 group by job ;
100
101 ---练习2
102 1.查询每个部门的部门编号,部门名称,部门人数,最高工资,最低工资,工资总和,平均工资。
103 select deptno,dname,count(empno),max(sal),min(sal),sum(sal),avg(sal)
104 from emp e natural join dept d
105 group by deptno,dname
106
107 2.查询每个部门,每个岗位的部门编号,部门名称,岗位名称,部门人数,
108 最高工资,最低工资,工资总和,平均工资。
109 select deptno,dname,job,count(empno),max(sal),min(sal),sum(sal),avg(sal)
110 from emp e natural join dept d
111 group by deptno,dname,job
112
113 3.查询每个经理所管理的人数,经理编号,经理姓名,要求包括没有经理的人员信息。
114 select count(e.empno),e.mgr
115 from emp e left join emp m on e.mgr = m.empno
116 group by e.mgr
117
118 ---练习3
119 1.查询部门人数大于2的部门编号,部门名称,部门人数。
120 select deptno,dname,count(empno)
121 from emp natural join dept
122 group by deptno,dname
123 having count(empno)>2
124
125 2.查询部门平均工资大于2000,且人数大于2的部门编号,部门名称,部门人数,
126 部门平均工资,并按照部门人数升序排序。
127 select deptno,dname,count(empno)
128 from emp natural join dept
129 group by deptno,dname
130 having count(empno)>2 and avg(sal)>2000
131 order by count(empno)
132
133 -------------------第七章----------------
134 ---练习1
135 1.查询入职日期最早的员工姓名,入职日期
136 SELECT rownum,t.ename,t.hiredate
137 FROM (SELECT ROWNUM r,emp.* FROM emp) t
138 where hiredate is not null and t.r=1
139 order by hiredate
140
141 2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名,工资,部门名称
142 select ename,sal,dname
143 from emp natural join dept
144 where sal>(select sal
145 from emp
146 where upper(ename)='SMITH')
147 and upper(loc) ='CHICAGO';
148
149
150 3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名,入职日期
151 select ename,hiredate
152 from emp
153 where hiredate >all(select hiredate
154 from emp
155 where deptno=20);
156
157 4.查询部门人数大于所有部门平均人数的的部门编号,部门名称,部门人数
158 select deptno, dname,count(empno)部门人数
159 from emp natural join dept
160 group by deptno,dname
161 having count(empno)>(select count(empno)/count(distinct deptno)
162 from emp)
163
164
165 ---练习2
166 1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期,不包括10部门员工
167 select ename,hiredate
168 from emp
169 where hiredate>any(select hiredate
170 from emp
171 where deptno=10)
172 and deptno!=10;
173
174 2.查询入职日期比10部门所有员工晚的员工姓名、入职日期,不包括10部门员工
175 select ename,hiredate
176 from emp
177 where hiredate>all(select hiredate
178 from emp
179 where deptno=10)
180 and deptno!=10;
181 ---比子查询的返回结果的最大值要大
182
183 3.查询职位和10部门任意一个员工职位相同的员工姓名,职位,不包括10部门员工
184 select ename,job
185 from emp
186 where job in (select job
187 from emp
188 where deptno=10)
189 and deptno!=10;
190
191 ---练习3
192 1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名,职位,
193 不包括10部门员工
194 select ename,job
195 from emp
196 where (job,mgr) in (select job,mgr
197 from emp
198 where deptno=10)
199 and deptno!=10;
200
201 2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名,职位,
202 不包括10部门员工
203 select ename,job
204 from emp
205 where job in (select job from emp where deptno=10)
206 or mgr in (select mgr from emp where deptno=10)
207 and deptno!=10;
208
209
210 ---练习4
211 1.查询比自己职位平均工资高的员工姓名、职位,部门名称,职位平均工资
212 --相同job,平均工资
213 --表 j 用job 和其它表建立连接
214 select job,avg(sal) a
215 from emp
216 group by job
217 -----------------
218 select e.ename,e.job,d.dname,j.a
219 from emp e,dept d,(select job,avg(sal) a
220 from emp
221 group by job ) j --三个表连接
222 where e.deptno = d.deptno and e.job = j.job
223 and e.sal>j.a ;
224
225
226 2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位,不包括SCOOT和BLAKE本人。
227 select ename,job
228 from emp
229 where (job,mgr) in (select job,mgr
230 from emp
231 where upper(ename) in('SCOTT','BLAKE'))
232 and upper(ename) not in('SCOTT','BLAKE');
233
234 3.查询不是经理的员工姓名。
235 select ename
236 from emp
237 where empno not in(select distinct mgr
238 from emp
239 where mgr is not null);
240
241 ---练习5
242 1.查询入职日期最早的前5名员工姓名,入职日期。
243 SELECT rownum,t.ename,t.hiredate
244 FROM (SELECT ROWNUM r,emp.* FROM emp) t
245 where t.hiredate is not null and t.r<=5
246 order by t.hiredate
247
248 2.查询工作在CHICAGO并且入职日期最早的前2名员工姓名,入职日期。
249 SELECT rownum,t.ename,t.hiredate
250 FROM (SELECT ROWNUM r,emp.* FROM emp) t
251 where t.hiredate is not null and t.r<=2
252 and t.deptno=(select deptno
253 from dept
254 where upper(loc)='CHICAGO')
255 order by t.hiredate
256
257 ---练习6
258 1.按照每页显示5条记录,分别查询第1页,第2页,第3页信息,
259 要求显示员工姓名、入职日期、部门名称。
260 select rownum ,t.ename,t.hiredate,t.dname
261 from (select rownum r, empno,ename,job,mgr,hiredate,sal,comm,deptno,deptno,dname,loc
262 from emp natural join dept) t
263 WHERE t.r>(1-1)*5 and t.r<=1*5
264 or t.r>(2-1)*5 and t.r<=2*5
265 or t.r>(3-1)*5 and t.r<=3*5
266
267 ---练习7
268 1.按照每页显示5条记录,分别查询工资最高的第1页,第2页,第3页信息,
269 要求显示员工姓名、入职日期、部门名称、工资。
270 select rownum ,t.ename,t.hiredate,t.dname,t.sal
271 from (select rownum r, empno,ename,job,mgr,hiredate,sal,comm,deptno,deptno,dname,loc
272 from emp natural join dept) t
273 WHERE t.r>(1-1)*5 and t.r<=1*5
274 or t.r>(2-1)*5 and t.r<=2*5
275 or t.r>(3-1)*5 and t.r<=3*5
276 order by t.sal desc
277
278
279 ---课后作业
280 1.查询工资高于编号为7782的员工工资,并且和7369号员工从事相同工作的员工的编号、姓名及工资。
281 select empno,ename,sal
282 from emp
283 where sal>(select sal
284 from emp
285 where empno=7782)
286 and job = (select job
287 from emp
288 where empno=7369);
289 ---
290 2.查询工资最高的员工姓名和工资。
291 select rownum,t.ename,t.sal
292 from (select rownum r,emp.*
293 from emp
294 order by sal desc) t
295 where t.r=1
296
297 3.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
298 select deptno,dname,min(sal)
299 from emp natural join dept
300 group by deptno,dname
301 having min(sal)>(select min(sal)
302 from emp
303 group by deptno
304 having deptno=10)
305
306 4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
307 select empno,ename,sal
308 from emp
309 where (deptno,sal) in (select deptno ,min(sal)
310 from emp
311 group by deptno)
312
313 5.显示经理是KING的员工姓名,工资。
314 select ename,sal
315 from emp
316 where mgr=(select empno
317 from emp
318 where upper(ename)='KING')
319
320 6.显示比员工SMITH参加工作时间晚的员工姓名,工资,参加工作时间。
321 select ename,sal,hiredate
322 from emp
323 where hiredate>(select hiredate
324 from emp
325 where upper(ename)='SMITH')
326
327 7.使用子查询的方式查询哪些职员在NEW YORK工作。
328 select *
329 from emp
330 where deptno=(select deptno
331 from dept
332 where upper(loc)='NEW YORK')
333
334 8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名,雇用日期,
335 查询结果中排除SMITH。
336 select ename,hiredate
337 from emp
338 where deptno in (select deptno
339 from emp
340 where upper(ename)='SMITH')
341
342 9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
343 select empno,ename
344 from emp
345 where sal>( select avg(sal)
346 from emp)
347
348 10.写一个查询显示其上级领导是King的员工姓名、工资。
349 select ename,sal
350 from emp
351 where mgr=(select empno
352 from emp
353 where upper(ename)='KING')
354
355 11.显示所有工作在RESEARCH部门的员工姓名,职位。
356 select ename,job
357 from emp
358 where deptno =(select deptno
359 from dept
360 where upper(dname)='RESEARCH')
361
362 12.查询每个部门的部门编号、平均工资,要求部门的平均工资高于部门20的平均工资。
363 select deptno,avg(sal)
364 from emp natural join dept
365 group by deptno
366 having avg(sal)>(select avg(sal)
367 from emp natural join dept
368 group by deptno
369 having deptno=20)
370 ---
371 13.查询大于自己部门平均工资的员工姓名,工资,
372 所在部门平均工资,高于部门平均工资的额度。
373 select e.ename,e.sal,a,sal-a
374 from emp e,(select deptno ,avg(sal) a
375 from emp
376 group by deptno) t
377 where e.deptno= t.deptno
378 and sal>a
379
380
381 14. 列出至少有一个雇员的所有部门
382 select deptno
383 from emp
384 group by deptno
385 having count(empno)>0
386
387 15. 列出薪金比"SMITH"多的所有雇员
388 select *
389 from emp
390 where sal>(select sal
391 from emp
392 where upper(ename)='SMITH')
393
394 --16. 列出入职日期早于其直接上级的所有雇员
395 select *
396 from emp e join emp m on e.mgr=m.empno
397 where e.hiredate < m.hiredate
398
399 17. 找员工姓名和直接上级的名字
400 select e.ename,m.ename
401 from emp e join emp m on e.mgr=m.empno
402
403 18. 显示部门名称和人数
404 select dname,count(empno)
405 from emp natural join dept
406 group by dname
407
408 19. 显示每个部门的最高工资的员工
409 select *
410 from emp
411 where (deptno,sal) in (select deptno,max(sal)
412 from emp
413 group by deptno)
414
415 20. 显示出和员工号7369部门相同的员工姓名,工资
416 select ename,sal
417 from emp
418 where deptno=(select deptno
419 from emp
420 where empno=7369);
421
422 21. 显示出和姓名中包含"W"的员工相同部门的员工姓名
423 select ename
424 from emp
425 where deptno=(select deptno
426 from emp
427 where upper(ename) like '%W%');
428
429 22. 显示出工资大于平均工资的员工姓名,工资
430 select ename,sal
431 from emp
432 where sal>(select avg(sal)
433 from emp);
434
435 23. 显示出工资大于本部门平均工资的员工姓名,工资
436 select ename,sal
437 from emp
438 where sal > (select avg(sal)
439 from emp
440 group by deptno)
441
442 24. 显示每位经理管理员工的最低工资,及最低工资者的姓名
443 select m,ename
444 from emp e,(select mgr,min(sal) m
445 from emp
446 group by mgr) t
447 where e.mgr = t.mgr
448 and sal = m
449
450 25. 显示比工资最高的员工参加工作时间晚的员工姓名,参加工作时间
451 select ename,hiredate
452 from emp
453 where hiredate>(select hiredate
454 from emp
455 where sal=(
456 select max(sal)
457 from emp))
458
459 26. 显示出平均工资最高的的部门平均工资及部门名称
460 select dname,avg(sal)
461 from emp natural join dept
462 group by deptno,dname
463 having avg(sal)=(select max(avg(sal))
464 from emp
465 group by deptno)
自己
课后练习
-------------------第五章----------------
---练习1
1.写一个查询,显示所有员工姓名,部门编号,部门名称。
select e.ename,e.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno
2.写一个查询,显示所有工作在CHICAGO并且奖金不为空的员工姓名,工作地点,奖金
select e.ename,d.loc,e.comm
from emp e,dept d
where e.deptno = d.deptno and upper(d.loc)='CHICAGO' and e.comm>0;
3.写一个查询,显示所有姓名中含有A字符的员工姓名,工作地点。
select e.ename,d.loc
from emp e,dept d
where e.deptno = d.deptno and e.ename like '%A%';
---练习2
1.查询每个员工的编号,姓名,工资,工资等级,所在工作城市,按照工资等级进行升序排序。
select e.empno,e.ename,e.sal,s.grade,d.loc
from emp e,dept d ,salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal
order by e.sal ;
---练习3
1.查询所有工作在NEW YORK和CHICAGO的员工姓名,员工编号,以及他们的经理姓名,经理编号。
select e.ename,e.empno,e.mgr,m.ename
from emp e,dept d,emp m
where e.deptno = d.deptno and e.mgr = m.empno
and upper(d.loc) in ('NEW YORK','CHICAGO')
2.第上一题的基础上,添加没有经理的员工King,并按照员工编号排序。
select e.ename,e.empno,e.mgr,m.ename
---左连接 table1 left join table2 on ...
from dept d,emp e left join emp m on e.mgr = m.empno
where e.deptno = d.deptno
and upper(d.loc) in ('NEW YORK','CHICAGO')
order by e.empno
3.查询所有员工编号,姓名,部门名称,包括没有部门的员工也要显示出来。
select e.empno,e.ename,d.dname
from emp e left join dept d on e.deptno= d.deptno
---练习4
使用SQL-99写法,完成如下练习
1.创建一个员工表和部门表的交叉连接。
select *
from emp cross join dept;
2.使用自然连接,显示入职日期在80年5月1日之后的员工姓名,部门名称,入职日期
select e.ename,d.dname,e.hiredate
from emp e natural join dept d
---自然连接 自动匹配两个表中的相同字段
where e.hiredate>'1-5月-1980'
3.使用USING子句,显示工作在CHICAGO的员工姓名,部门名称,工作地点
--JOIN...USING(公共列名)
--using后不要忘记加()
select ename,dname,loc
from emp join dept using (deptno)
where upper(loc)='CHICAGO'
---
4.使用ON子句,显示工作在CHICAGO的员工姓名,部门名称,工作地点,薪资等级
--语法:table1 join table2 on 连接条件;
select e.ename,d.dname,d.loc,s.grade
from emp e ,dept d ,salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal
and upper(d.loc)='CHICAGO'
5.使用左连接,查询每个员工的姓名,经理姓名,没有经理的King也要显示出来。
select e.ename,m.ename
from emp e left join emp m on e.mgr = m.empno
6.使用右连接,查询每个员工的姓名,经理姓名,没有经理的King也要显示出来。
select e.ename,m.ename
from emp m right join emp e on e.mgr = m.empno
-------------------第六章----------------
---练习1
1.查询部门20的员工,每个月的工资总和及平均工资。
select sum(sal),avg(sal)
from emp
group by deptno
having deptno =20
2.查询工作在CHICAGO的员工人数,最高工资及最低工资。
select count(empno) 员工人数, max(sal),min(sal)
from emp natural join dept
group by loc
having upper(loc)= 'CHICAGO';
3.查询员工表中一共有几种岗位类型
select job
from emp
group by job ;
---练习2
1.查询每个部门的部门编号,部门名称,部门人数,最高工资,最低工资,工资总和,平均工资。
select deptno,dname,count(empno),max(sal),min(sal),sum(sal),avg(sal)
from emp e natural join dept d
group by deptno,dname
2.查询每个部门,每个岗位的部门编号,部门名称,岗位名称,部门人数,
最高工资,最低工资,工资总和,平均工资。
select deptno,dname,job,count(empno),max(sal),min(sal),sum(sal),avg(sal)
from emp e natural join dept d
group by deptno,dname,job
3.查询每个经理所管理的人数,经理编号,经理姓名,要求包括没有经理的人员信息。
select count(e.empno),e.mgr
from emp e left join emp m on e.mgr = m.empno
group by e.mgr
---练习3
1.查询部门人数大于2的部门编号,部门名称,部门人数。
select deptno,dname,count(empno)
from emp natural join dept
group by deptno,dname
having count(empno)>2
2.查询部门平均工资大于2000,且人数大于2的部门编号,部门名称,部门人数,
部门平均工资,并按照部门人数升序排序。
select deptno,dname,count(empno)
from emp natural join dept
group by deptno,dname
having count(empno)>2 and avg(sal)>2000
order by count(empno)
-------------------第七章----------------
---练习1
1.查询入职日期最早的员工姓名,入职日期
SELECT rownum,t.ename,t.hiredate
FROM (SELECT ROWNUM r,emp.* FROM emp) t
where hiredate is not null and t.r=1
order by hiredate
2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名,工资,部门名称
select ename,sal,dname
from emp natural join dept
where sal>(select sal
from emp
where upper(ename)='SMITH')
and upper(loc) ='CHICAGO';
3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名,入职日期
select ename,hiredate
from emp
where hiredate >all(select hiredate
from emp
where deptno=20);
4.查询部门人数大于所有部门平均人数的的部门编号,部门名称,部门人数
select deptno, dname,count(empno)部门人数
from emp natural join dept
group by deptno,dname
having count(empno)>(select count(empno)/count(distinct deptno)
from emp)
---练习2
1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期,不包括10部门员工
select ename,hiredate
from emp
where hiredate>any(select hiredate
from emp
where deptno=10)
and deptno!=10;
2.查询入职日期比10部门所有员工晚的员工姓名、入职日期,不包括10部门员工
select ename,hiredate
from emp
where hiredate>all(select hiredate
from emp
where deptno=10)
and deptno!=10;
---比子查询的返回结果的最大值要大
3.查询职位和10部门任意一个员工职位相同的员工姓名,职位,不包括10部门员工
select ename,job
from emp
where job in (select job
from emp
where deptno=10)
and deptno!=10;
---练习3
1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名,职位,
不包括10部门员工
select ename,job
from emp
where (job,mgr) in (select job,mgr
from emp
where deptno=10)
and deptno!=10;
2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名,职位,
不包括10部门员工
select ename,job
from emp
where job in (select job from emp where deptno=10)
or mgr in (select mgr from emp where deptno=10)
and deptno!=10;
---练习4
1.查询比自己职位平均工资高的员工姓名、职位,部门名称,职位平均工资
--相同job,平均工资
--表 j 用job 和其它表建立连接
select job,avg(sal) a
from emp
group by job
-----------------
select e.ename,e.job,d.dname,j.a
from emp e,dept d,(select job,avg(sal) a
from emp
group by job ) j --三个表连接
where e.deptno = d.deptno and e.job = j.job
and e.sal>j.a ;
2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位,不包括SCOOT和BLAKE本人。
select ename,job
from emp
where (job,mgr) in (select job,mgr
from emp
where upper(ename) in('SCOTT','BLAKE'))
and upper(ename) not in('SCOTT','BLAKE');
3.查询不是经理的员工姓名。
select ename
from emp
where empno not in(select distinct mgr
from emp
where mgr is not null);
---练习5
1.查询入职日期最早的前5名员工姓名,入职日期。
SELECT rownum,t.ename,t.hiredate
FROM (SELECT ROWNUM r,emp.* FROM emp) t
where t.hiredate is not null and t.r<=5
order by t.hiredate
2.查询工作在CHICAGO并且入职日期最早的前2名员工姓名,入职日期。
SELECT rownum,t.ename,t.hiredate
FROM (SELECT ROWNUM r,emp.* FROM emp) t
where t.hiredate is not null and t.r<=2
and t.deptno=(select deptno
from dept
where upper(loc)='CHICAGO')
order by t.hiredate
---练习6
1.按照每页显示5条记录,分别查询第1页,第2页,第3页信息,
要求显示员工姓名、入职日期、部门名称。
select rownum ,t.ename,t.hiredate,t.dname
from (select rownum r, empno,ename,job,mgr,hiredate,sal,comm,deptno,deptno,dname,loc
from emp natural join dept) t
WHERE t.r>(1-1)*5 and t.r<=1*5
or t.r>(2-1)*5 and t.r<=2*5
or t.r>(3-1)*5 and t.r<=3*5
---练习7
1.按照每页显示5条记录,分别查询工资最高的第1页,第2页,第3页信息,
要求显示员工姓名、入职日期、部门名称、工资。
select rownum ,t.ename,t.hiredate,t.dname,t.sal
from (select rownum r, empno,ename,job,mgr,hiredate,sal,comm,deptno,deptno,dname,loc
from emp natural join dept) t
WHERE t.r>(1-1)*5 and t.r<=1*5
or t.r>(2-1)*5 and t.r<=2*5
or t.r>(3-1)*5 and t.r<=3*5
order by t.sal desc
---课后作业
1.查询工资高于编号为7782的员工工资,并且和7369号员工从事相同工作的员工的编号、姓名及工资。
select empno,ename,sal
from emp
where sal>(select sal
from emp
where empno=7782)
and job = (select job
from emp
where empno=7369);
---
2.查询工资最高的员工姓名和工资。
select rownum,t.ename,t.sal
from (select rownum r,emp.*
from emp
order by sal desc) t
where t.r=1
3.查询部门最低工资高于10号部门最低工资的部门的编号、名称及部门最低工资。
select deptno,dname,min(sal)
from emp natural join dept
group by deptno,dname
having min(sal)>(select min(sal)
from emp
group by deptno
having deptno=10)
4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。
select empno,ename,sal
from emp
where (deptno,sal) in (select deptno ,min(sal)
from emp
group by deptno)
5.显示经理是KING的员工姓名,工资。
select ename,sal
from emp
where mgr=(select empno
from emp
where upper(ename)='KING')
6.显示比员工SMITH参加工作时间晚的员工姓名,工资,参加工作时间。
select ename,sal,hiredate
from emp
where hiredate>(select hiredate
from emp
where upper(ename)='SMITH')
7.使用子查询的方式查询哪些职员在NEW YORK工作。
select *
from emp
where deptno=(select deptno
from dept
where upper(loc)='NEW YORK')
8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名,雇用日期,
查询结果中排除SMITH。
select ename,hiredate
from emp
where deptno in (select deptno
from emp
where upper(ename)='SMITH')
9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。
select empno,ename
from emp
where sal>( select avg(sal)
from emp)
10.写一个查询显示其上级领导是King的员工姓名、工资。
select ename,sal
from emp
where mgr=(select empno
from emp
where upper(ename)='KING')
11.显示所有工作在RESEARCH部门的员工姓名,职位。
select ename,job
from emp
where deptno =(select deptno
from dept
where upper(dname)='RESEARCH')
12.查询每个部门的部门编号、平均工资,要求部门的平均工资高于部门20的平均工资。
select deptno,avg(sal)
from emp natural join dept
group by deptno
having avg(sal)>(select avg(sal)
from emp natural join dept
group by deptno
having deptno=20)
---
13.查询大于自己部门平均工资的员工姓名,工资,
所在部门平均工资,高于部门平均工资的额度。
select e.ename,e.sal,a,sal-a
from emp e,(select deptno ,avg(sal) a
from emp
group by deptno) t
where e.deptno= t.deptno
and sal>a
14. 列出至少有一个雇员的所有部门
select deptno
from emp
group by deptno
having count(empno)>0
15. 列出薪金比"SMITH"多的所有雇员
select *
from emp
where sal>(select sal
from emp
where upper(ename)='SMITH')
--16. 列出入职日期早于其直接上级的所有雇员
select *
from emp e join emp m on e.mgr=m.empno
where e.hiredate < m.hiredate
17. 找员工姓名和直接上级的名字
select e.ename,m.ename
from emp e join emp m on e.mgr=m.empno
18. 显示部门名称和人数
select dname,count(empno)
from emp natural join dept
group by dname
19. 显示每个部门的最高工资的员工
select *
from emp
where (deptno,sal) in (select deptno,max(sal)
from emp
group by deptno)
20. 显示出和员工号7369部门相同的员工姓名,工资
select ename,sal
from emp
where deptno=(select deptno
from emp
where empno=7369);
21. 显示出和姓名中包含"W"的员工相同部门的员工姓名
select ename
from emp
where deptno=(select deptno
from emp
where upper(ename) like '%W%');
22. 显示出工资大于平均工资的员工姓名,工资
select ename,sal
from emp
where sal>(select avg(sal)
from emp);
23. 显示出工资大于本部门平均工资的员工姓名,工资
select ename,sal
from emp
where sal > (select avg(sal)
from emp
group by deptno)
24. 显示每位经理管理员工的最低工资,及最低工资者的姓名
select m,ename
from emp e,(select mgr,min(sal) m
from emp
group by mgr) t
where e.mgr = t.mgr
and sal = m
25. 显示比工资最高的员工参加工作时间晚的员工姓名,参加工作时间
select ename,hiredate
from emp
where hiredate>(select hiredate
from emp
where sal=(
select max(sal)
from emp))
26. 显示出平均工资最高的的部门平均工资及部门名称
select dname,avg(sal)
from emp natural join dept
group by deptno,dname
having avg(sal)=(select max(avg(sal))
from emp
group by deptno)