Oracle.练习题

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)

 

posted on 2018-07-31 21:28  亮晶晶的小宇宙  阅读(685)  评论(0)  编辑  收藏  举报