60道部门和员工的查询

最新推荐文章于 2021-07-24 17:24:19 发布
weixin_30338461
最新推荐文章于 2021-07-24 17:24:19 发布
阅读量1.3k
点赞数
原文链接：http://www.cnblogs.com/beiluo712/p/10517607.html
版权
  1 DROP TABLE emp;
  2 drop table dept;
  3 
  4 CREATE TABLE dept(
  5 
  6 deptno INT PRIMARY KEY,
  7 
  8 dname VARCHAR(20),
  9 
 10 loc VARCHAR(20)
 11 
 12 )
 13 
 14 
 15 
 16 CREATE TABLE emp(
 17 
 18 empno INT PRIMARY KEY,
 19 
 20 ename VARCHAR(20) NOT NULL,
 21 
 22 job VARCHAR(20) CHECK (job IN ('CLERK','SALESMAN','MANAGER','SALESMAN','ANALYST')),
 23 
 24 mgp INT ,
 25 
 26 hiredate date ,
 27 
 28 sal DECIMAL(10,2),
 29 
 30 comm DECIMAL(10,2),
 31 
 32 DEPTNO int
 33 
 34 )
 35 
 36 INSERT INTO dept VALUES (10,'ACCOUNTING','NEWTORK');
 37 
 38 INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
 39 
 40 INSERT INTO dept VALUES (30,'SALES','CHICAGO');
 41 
 42 INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');
 43 
 44 insert into emp values(7369,'SMITH','CLERK',7902,'17-12月-1980',1640,NULL,20);
 45 
 46 insert into emp values(7499,'ALLEN','SALESMAN',7698,'20-2月-1981',11400,300,30);
 47 
 48 insert into emp values(7521,'WARD','SALESMAN',7698,'22-2月-1981',5200,500,30);
 49 
 50 insert into emp values(7566,'JOENS','MANAGER',7839,'2-4月-1981',7015,NULL,20);
 51 
 52 insert into emp values(7654,'MARTIN','SALESMAN',7698,'28-9月-1981',5200,1400,30);
 53 
 54 insert into emp values(7698,'BLAKE','MANAGER',7839,'1-5月-1981',5900,NULL,30);
 55 
 56 insert into emp values(7782,'CLARK','MANAGER',7839,'9-6月-1981',2470,NULL,10);
 57 
 58 insert into emp values(7788,'SCOTT','ANALYST',7566,'19-4月-1981',3040,NULL,20);
 59 
 60 insert into emp values(7844,'TURNER','SALESMAN',7698,'17-12月-1980',6200,0,30);
 61 
 62 insert into emp values(7876,'ADAMS','CLERK',7788,'8-9月-1981',2240,NULL,20);
 63 
 64 insert into emp values(7900,'JAMES','CLERK',7698,'23-5月-1987',4000,NULL,30);
 65 
 66 insert into emp values(7902,'FORD','ANALYST',7566,'3-12月-1981',3040,NULL,20);
 67 
 68 insert into emp values(7934,'MILLER','CLERK',7782,'3-12月-1982',2620,NULL,10);
 69 
 70 SELECT * FROM emp;
 71 
 72 select * from dept;
 73 
 74 
 75 
 76 （1） 查询20部门的所有员工信息。
 77  select * from emp where deptno = 20
 78 
 79 （2） 查询所有工种为CLERK的员工的员工号、员工名和部门号。
 80 select empno,ename,deptno from emp where job = 'CLERK'
 81 
 82 （3） 查询奖金（COMM）高于工资（SAL）的员工信息。
 83 select * from emp where comm>sal
 84 
 85 
 86 （4） 查询奖金高于工资的20%的员工信息。
 87 select * from emp where comm>sal*0.2
 88 
 89 
 90 （5） 查询10号部门中工种为MANAGER和20部门中工种为CLERK的员工的信息。
 91 select * from emp where (deptno =10 and job = 'MANAGER') 
 92 or (deptno = 20 and job = 'CLERK')
 93 
 94 （6） 查询所有工种不是MANAGER和CLERK，
 95 select * from emp where job not in('MANAGER','CLERK');
 96 
 97 --且工资大于或等于2000的员工的详细信息。
 98 select * from emp where job not in('MANAGER','CLERK')
 99 and sal >=2000
100 
101 
102 
103 （7） 查询有奖金的员工的不同工种。
104 select distinct job from emp where comm>0
105 
106 
107 （8） 查询所有员工工资与奖金
108 
109 的和。
110 select ename,sal+nvl(comm,0) 总和 from emp
111 
112 
113 （9） 查询没有奖金或奖金低于100的员工信息。
114 select * from emp where comm is null or comm<100
115 
116 
117 --（10） 查询各月倒数第3天(倒数第2天)入职的员工信息。
118 select last_Day(hiredate)-2 from emp
119 
120 
121 （11） 查询工龄大于或等于25年的员工信息。
122 select * from emp where 
123 to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy')>=25
124 
125 --（12） 查询员工信息，要求以首字母大写的方式显示所有员工的姓名。
126 select initcap(ename)  from emp
127 （13） 查询员工名正好为6个字符的员工的信息。
128 select * from emp where ename like '______'; 
129 
130 （14） 查询员工名字中不包含字母“Ｓ”的员工。
131 select * from emp where ename not like '%S%';
132 
133 （15） 查询员工姓名的第二字母为“M”的员工信息。
134 select * from emp where ename like '_M%';
135 
136 --（16） 查询所有员工姓名的前三个字符。
137 select substr(ename,1,3) from emp 
138 --（17） 查询所有员工的姓名，如果包含字母“S”，则用“s”替换。
139 select replace(ename,'S','s')  from emp
140 --返回被替换了指定子串的字符串。
141 
142 
143 （18） 查询员工的姓名和入职日期，并按入职日期从先到后进行排序。
144 select ename,hiredate from emp order by hiredate;
145 
146 （19） 显示所有员工的姓名、工种、工资和奖金，按工种降序排序，
147 select ename,job,sal,comm from emp order by job desc, sal;
148 --若工种相同则按工资升序排序。
149 
150 
151 （20） 显示所有员工的姓名、入职的年份和月份，
152 --按入职日期所在的月份排序，若月份相同则按入职的年份排序。
153 select ename,to_Char(hiredate,'yyyy') 年,
154 to_Char(hiredate,'mm') 月 from emp
155 
156 （21） 查询在2月份入职的所有员工信息。
157 select * from emp where to_char(hiredate,'mm')='02'
158 
159 （22） 查询所有员工入职以来的工作期限，用“XX年XX月XX日”的形式表示。
160 
161 select 
162 floor(months_between(sysdate,hiredate)/12) ||'年'||
163 floor(mod(months_between(sysdate,hiredate),12)) ||'个月'||
164 ceil(sysdate-add_months(hiredate,floor(months_between(sysdate,hiredate)))) ||'日'
165 ,hiredate from emp
166 1.先取得两个时间的月份差，用月差去除12向下取整得到整年份
167 2.再用两个时间的月份差%12得到多出来的月份，向上取整
168 3.把两个时间的月份整的差添加在原本的日期上得到新的时间
169 再用当前系统时间去减去那天的时间得到一个日，向上取整
170 
171 
172 
173 
174 
175 （23.1） 查询至少有一个员工的部门信息。
176 select * from dept where deptno in
177 (select  deptno from emp 
178 group by deptno having count(*)>=1)
179 
180 （23.2） 查询至少有两个员工的部门信息。
181 select deptno
182 from emp group by deptno having count(*)>=2
183 
184 
185 （24） 查询工资比
186 SMITH员工工资
187 高的所有员工信息。
188 
189 select * from emp where sal >
190 (select sal from emp where ename= 'SMITH') 
191 
192 
193 （25） 查询所有员工的姓名及其直接上级的姓名。
194 select e.ename,m.ename from emp e, emp m where e.mgp= m.empno
195 
196 （26） 查询入职日期早于其直接上级领导的所有员工信息。
197 
198 select e.* from emp e, emp m where e.mgp= m.empno
199  and e.hiredate<m.hiredate
200 （27） 查询所有部门及其员工信息，包括那些没有员工的部门。
201 select dept.* ,emp.* from dept ,emp where dept.deptno
202 =  emp.deptno(+)
203 （28） 查询所有员工及其部门信息，包括那些还不属于任何部门的员工。
204 select dept.* ,emp.* from dept ,emp where dept.deptno(+)
205 =  emp.deptno
206 
207 （29） 查询所有工种为CLERK的员工的姓名及其部门名称。
208 select ename,
209 (select dname from dept where e1.deptno = dept.deptno ) 
210 from emp e1 where job = 'CLERK'
211 in适合做外表数据大的
212 exists做内部数据大的
213 
214 （30） 查询最低工资大于2500的各种工作。
215 select job
216 from emp group by job having min(sal)>2500
217 
218 -----------------------------------------------------------------
219 （31） 查询平均工资低于2000的部门及其员工信息。
220 select * from emp where deptno in
221 (select deptno 
222 from emp group by deptno having avg(sal)>3000)
223 
224 （32） 查询在SALES部门工作的员工的姓名信息。
225 select * from dept
226 
227 select b.dname from emp a, dept b where a.deptno=b.deptno and b.dname='SALES'
228 
229 （33） 查询工资高于公司平均工资的所有员工信息。
230 select * from emp where sal>
231 (select avg(sal) from emp)
232 
233 （34） 查询出与SMITH员工从事相同工作的所有员工信息。
234 select * from emp where job in
235 (select job from emp where ename ='SMITH' )
236 
237 （35） 列出工资等于30部门中某个员工的工资的所有员工的姓名和工资。
238 select * from emp where sal in
239 (select sal from emp where deptno = 30)
240 
241 （36） 查询工资高于30部门工作的所有员工的工资的员工姓名和工资。
242 select * from emp where sal >
243 (select max(sal) from emp where deptno = 30)
244 
245 （37） 查询每个部门中的员工数量、平均工资和平均工作年限。
246 select deptno,count(*),avg(sal),
247 avg(to_char(sysdate,'yyyy')-to_char(hiredate,'yyyy'))
248 from emp group by deptno
249 
250 
251 （38） 查询从事同一种工作但不属于同一部门的员工
252 信息。
253 select * from emp where empno in
254 (select 
255  max(dd.empno)
256 from
257 (select distinct e1.*
258 from emp e1,emp e2 where 
259 e1.job = e2.job and e1.deptno!=e2.deptno) dd
260 group by dd.deptno,dd.job)
261 
262 （39） 查询各个部门的详细信息以及部门人数、部门平均工资。
263 select deptno,
264 (select dname from dept where dept.deptno=emp.deptno),
265 count(*),avg(sal)
266 from emp group by deptno
267 
268 （40） 查询各种工作的最低工资。
269 select job,min(sal)
270  from emp group by job
271 
272 （41） 查询各个部门中不同工种的最高工资。
273 select deptno,job,max(sal)
274 from emp group by deptno,job
275 
276 （42） 查询10号部门员工及其领导的信息。
277 select e.*,b.*
278 from emp e,emp b where e.deptno = 10
279 and e.mgp = b.empno
280 
281 （43） 查询各个部门的人数及平均工资。
282 select e.deptno,max(e.dname),count(e.ename),avg(e.sal) 
283 from
284 (select dept.*,emp.sal,emp.ename
285 from dept left join emp on dept.deptno = emp.deptno
286 ) e group by e.deptno
287 
288 select deptno,count(*),avg(sal)
289 from emp group by deptno 
290 
291 
292 （44） 查询工资为某个部门平均工资的员工的信息。
293 select * from emp where sal  in(
294 select avg(sal) from emp group by deptno )
295 
296 （45） 查询工资高于本部门平均工资的员工的信息。
297 select * from emp e1,
298 (select deptno, avg(sal) pj from emp group by deptno )
299 e2 where e1.deptno = e2.deptno and e1.sal >e2.pj
300 
301 
302 （46） 查询工资高于本部门平均工资的员工的信息及其部门的平均工资。
303 select e1.*,e2.pj from emp e1,
304 (select deptno, avg(sal) pj from emp group by deptno )
305 e2 where e1.deptno = e2.deptno and e1.sal >e2.pj
306 
307 
308 （47） 查询工资高于20号部门某个员工工资的员工的信息。
309 select * from emp where sal >
310 (select min(sal) from emp where dpetno = 20)
311 
312 （48）统计各个工种的员工人数与平均工资。
313 select job ,count(*),avg(sal) from emp group by job 
314 
315 （49） 统计每个部门中各工种的人数与平均工资。
316 select job ,deptno,count(*),avg(sal) from emp 
317 group by deptno,job 
318 
319 （50） 查询其他部门中工资、奖金与30号部门某员工工资、
320 
321 
322 
323 
324 （51） 查询部门人数大于5的部门的员工信息。
325 
326 
327 
328 （52） 查询所有员工工资都大于1000的部门的信息。
329 
330 
331 
332 （53） 查询所有员工工资都大于1000的部门的
333 
334 信息及其员工信息。
335 
336 
337 （54） 查询所有员工工资都在900～3000之间的部门的信息。
338 
339 
340 （55） 查询有工资在900～3000之间的员工所在部门的员工信息。
341 
342 
343 
344 （56） 查询每个员工的领导所在部门的信息。
345 
346 
347 （57） 查询人数最多的部门信息。
348 
349 
350 
351 （58） 查询30号部门中工资排序前3名的员工信息。
352 select sc.*,rownum from
353 (select * from t_score sc order by degree desc) sc
354 where rownum <4
355 （59） 查询所有员工中工资排序在5到10名之间的员工信息。
356 select sc1.*,sc1.r from
357 (select sc.*,rownum r from
358 (select * from t_score sc order by degree desc) sc)
359 sc1 where  r between 5 and 10
360 
361 （60） 查询指定年份之间入职的员工信息。(1980-1985)
362 
363 
364 create table testchar(
365        cname varchar2(50) 
366 )
367 
368 select lower(cname)  from testchar
369 select upper(cname) from testchar
370 select translate(cname,'abcABC','123一二三') from testchar
371 
372 create table testnumber(
373        number1  number
374 )
375 select power(number1,2) from testnumber
376 select trunc(number1,-2) from testnumber
377 
378 
379 select months_between(sysdate,hiredate)/12 from emp
380 select add_months(hiredate,-1) from emp order by hiredate
381 
382 insert into emp (empno,ename,hiredate) values (999,'呵呵',sysdate)
383 select next_day(hiredate,'星期四') from emp
384 select last_day(hiredate) from emp