forked from jiw065/plsql
-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql_practice.sql
601 lines (451 loc) · 23.4 KB
/
sql_practice.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
select * from employees;
select e.last_name,e.employee_id,e.salary,e.hire_date from employees e where e.last_name like '%a%e%' or e.last_name like '%e%a%';
select to_CHAR(sysdate,'YYYY-MM-DD HH:MI:SS'),TO_NUMBER('123,123,111','999,999,999,999') FROM DUAL;
SELECT E.EMPLOYEE_ID,E.FIRST_NAME||' '||E.LAST_NAME AS "EMPLOYEE NAME", E.SALARY,E.SALARY*1.2 NEW_SAL FROM EMPLOYEES E;
/
SELECT LENGTH(E.FIRST_NAME),E.FIRST_NAME FROM EMPLOYEES E ORDER BY E.FIRST_NAME ASC;
/
SELECT E.EMPLOYEE_ID,E.FIRST_NAME||' '||E.LAST_NAME AS "EMPLOYEE NAME",
TRUNC(MONTHS_BETWEEN(SYSDATE,E.HIRE_DATE)),
ROUND(MONTHS_BETWEEN(SYSDATE,E.HIRE_DATE),1) AS WORK_MONTH
FROM EMPLOYEES E
ORDER BY WORK_MONTH DESC;
/
SELECT E.FIRST_NAME||' EARNS'||TO_CHAR(E.SALARY,'$999,999')||' BUT WANTS'||TO_CHAR(E.SALARY*3,'$999,999') FROM EMPLOYEES E;
/
SELECT
DECODE(E.JOB_ID,
'AD_PRES','A',
'ST_MAN','B',
'IT_PROG','C',
'D'), E.FIRST_NAME,E.JOB_ID
FROM EMPLOYEES E;
/
SELECT
CASE E.JOB_ID
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
ELSE 'D' END, E.FIRST_NAME,E.JOB_ID
FROM EMPLOYEES E;
/
SELECT COUNT(*) FROM EMPLOYEES;
/
SELECT E.EMPLOYEE_ID,E.SALARY,E.JOB_ID,J.* FROM EMPLOYEES E, JOBS J WHERE E.SALARY BETWEEN J.MIN_SALARY AND J.MAX_SALARY;
/
-- INNNER CONNECT = INNER JOIN???
SELECT E.* FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
/
-- LEFT JOIN RETURN INCLUDES THE RECORD DOES NOT HAVE DEPARTMET ID
SELECT E.* FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+);
/
-- RIGHT JOIN RETURNS INCLUSES THOSE NOT USED DEPARTMENT ID
SELECT E.*,D.* FROM EMPLOYEES E, DEPARTMENTS D WHERE E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID;
/
-- JOIN
SELECT E.EMPLOYEE_ID,E.DEPARTMENT_ID,D.DEPARTMENT_NAME,B.JOB_TITLE FROM EMPLOYEES E
JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
JOIN JOBS B ON B.JOB_ID = E.JOB_ID;
/
-- JOIN left outer and right outer same as +
SELECT E.EMPLOYEE_ID,E.DEPARTMENT_ID,D.DEPARTMENT_NAME FROM EMPLOYEES E
LEFT OUTER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
/
SELECT E.EMPLOYEE_ID,E.DEPARTMENT_ID,D.DEPARTMENT_NAME FROM EMPLOYEES E
RIGHT OUTER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
/
SELECT E.EMPLOYEE_ID,E.DEPARTMENT_ID,D.DEPARTMENT_NAME FROM EMPLOYEES E
FULL OUTER JOIN DEPARTMENTS D ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
/
-- INNER JOIN (自表查询)
SELECT EMP.LAST_NAME,MAG.LAST_NAME,MAG.EMAIL FROM EMPLOYEES EMP, EMPLOYEES MAG WHERE EMP.MANAGER_ID = MAG.EMPLOYEE_ID AND UPPER(EMP.LAST_NAME) = 'CHEN';
/
--1. 显示所有员工的姓名,部门号和部门名称。
SELECT E.First_Name,D.DEPARTMENT_ID,D.DEPARTMENT_NAME FROM EMPLOYEES E, DEPARTMENTS D WHERE D.DEPARTMENT_ID(+) = E.DEPARTMENT_ID;
/
SELECT E.FIRST_NAME,D.DEPARTMENT_ID,D.DEPARTMENT_NAME FROM EMPLOYEES E
LEFT OUTER JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
/
--2. 查询90号部门员工的job_id和90号部门的location_id
SELECT E.FIRST_NAME,E.DEPARTMENT_ID,J.JOB_ID,J.JOB_TITLE,D.LOCATION_ID FROM EMPLOYEES E, DEPARTMENTS D, JOBS J
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND D.DEPARTMENT_ID = 90
AND j.job_id = E.JOB_ID
--3. 选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT E.LAST_NAME,D.DEPARTMENT_NAME,L.LOCATION_ID,L.CITY FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND E.COMMISSION_PCT IS NOT NULL
/
--4. 选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT E.LAST_NAME, E.JOB_ID,D.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E, LOCATIONS L, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND UPPER(L.CITY) = 'TORONTO';
--5. 选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
SELECT E.FIRST_NAME E_NAME, E.EMPLOYEE_ID E_ID, M.FIRST_NAME M_NAME, M.EMPLOYEE_ID M_ID
FROM EMPLOYEES E, EMPLOYEES M
WHERE E.MANAGER_ID = M.EMPLOYEE_ID(+);
/
SELECT COUNT(DISTINCT E.DEPARTMENT_ID ) FROM EMPLOYEES E;
/
SELECT SUM(E.COMMISSION_PCT)/COUNT(E.EMPLOYEE_ID) FROM EMPLOYEES E;
/
SELECT AVG(NVL(E.COMMISSION_PCT,0)) FROM EMPLOYEES E;
/
SELECT E.DEPARTMENT_ID,AVG(E.SALARY) FROM EMPLOYEES E GROUP BY E.DEPARTMENT_ID;
/
SELECT AVG(E.SALARY) FROM EMPLOYEES E, LOCATIONS L, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
AND UPPER(L.CITY) = 'TORONTO'
/
SELECT L.CITY,AVG(E.SALARY) FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
GROUP BY L.CITY ;
/
SELECT E.DEPARTMENT_ID, AVG(E.SALARY) FROM EMPLOYEES E
HAVING MAX(E.SALARY) > 8000
GROUP BY E.DEPARTMENT_ID;
/
SELECT J.JOB_TITLE, AVG(E.SALARY) FROM EMPLOYEES E, JOBS J
WHERE E.JOB_ID = J.JOB_ID
HAVING AVG(E.SALARY) > 6000
GROUP BY J.JOB_TITLE;
/
-- 4. 查询公司员工工资的最大值,最小值,平均值,总和
SELECT MAX(E.SALARY),MIN(E.SALARY),AVG(E.SALARY),SUM(E.SALARY) FROM EMPLOYEES E ;
--5. 查询各job_id的员工工资的最大值,最小值,平均值,总和
SELECT E.JOB_ID, MAX(E.SALARY),MIN(E.SALARY),AVG(E.SALARY),SUM(E.SALARY) FROM EMPLOYEES E GROUP BY E.JOB_ID;
--6. 选择具有各个job_id的员工人数
SELECT E.JOB_ID, COUNT(E.EMPLOYEE_ID) FROM EMPLOYEES E GROUP BY E.JOB_ID;
--7. 查询员工最高工资和最低工资的差距(DIFFERENCE)
SELECT MAX(E.SALARY) - MIN(E.SALARY) FROM EMPLOYEES E;
--8. 查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT E.MANAGER_ID,MIN(E.SALARY) FROM EMPLOYEES E
WHERE E.MANAGER_ID IS NOT NULL
HAVING MIN(E.SALARY) >= 6000
GROUP BY E.MANAGER_ID;
--9. 查询所有部门的名字,location_id,员工数量和工资平均值
SELECT D.DEPARTMENT_NAME,D.LOCATION_ID,COUNT(E.EMPLOYEE_ID),AVG(E.SALARY)
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME,D.LOCATION_ID;
/
--10. 查询公司在1995-1998年之间,每年雇用的人数,结果类似下面的格式
SELECT 'TOTAL',SUM(YC.EMP_COUNT) FROM
(SELECT TO_CHAR(E.HIRE_DATE,'YYYY') HIRE_YEAR,COUNT(E.EMPLOYEE_ID) EMP_COUNT
FROM EMPLOYEES E
WHERE E.HIRE_DATE BETWEEN TO_DATE('2003','YYYY') AND TO_DATE('2007','YYYY')
GROUP BY TO_CHAR(E.HIRE_DATE,'YYYY')) YC
UNION
SELECT TO_CHAR(E.HIRE_DATE,'YYYY') HIRE_YEAR,COUNT(E.EMPLOYEE_ID) EMP_COUNT
FROM EMPLOYEES E
WHERE E.HIRE_DATE BETWEEN TO_DATE('2003','YYYY') AND TO_DATE('2007','YYYY')
GROUP BY TO_CHAR(E.HIRE_DATE,'YYYY');
/
SELECT COUNT(E.EMPLOYEE_ID) "TOTAL",
COUNT(DECODE(TO_CHAR(E.HIRE_DATE,'YYYY'),'2003',E.EMPLOYEE_ID,NULL)) "2003",
COUNT(DECODE(TO_CHAR(E.HIRE_DATE,'YYYY'),'2004',E.EMPLOYEE_ID,NULL)) "2004",
COUNT(DECODE(TO_CHAR(E.HIRE_DATE,'YYYY'),'2005',E.EMPLOYEE_ID,NULL)) "2005",
COUNT(DECODE(TO_CHAR(E.HIRE_DATE,'YYYY'),'2006',E.EMPLOYEE_ID,NULL)) "2006",
COUNT(DECODE(TO_CHAR(E.HIRE_DATE,'YYYY'),'2007',E.EMPLOYEE_ID,NULL)) "2007"
FROM EMPLOYEES E
WHERE E.HIRE_DATE BETWEEN TO_DATE('2003','YYYY') AND TO_DATE('2007','YYYY');
/
-- WHOSE SALARY IS HIGHER THAN ADEL
SELECT E.SALARY,H.LAST_NAME,H.SALARY FROM EMPLOYEES E, EMPLOYEES H WHERE E.SALARY < H.SALARY
AND E.LAST_NAME = 'Abel';
/
SELECT H.FIRST_NAME,H.LAST_NAME,H.SALARY FROM EMPLOYEES H WHERE H.SALARY >
(SELECT E.SALARY FROM EMPLOYEES E WHERE E.LAST_NAME = 'Abel' );
/
-- NAMED CHEN AS A MANAGER
SELECT * FROM EMPLOYEES M WHERE M.EMPLOYEE_ID = (SELECT E.MANAGER_ID FROM EMPLOYEES E WHERE E.LAST_NAME = 'Chen');
select * from employees e
where e.job_id = (select job_id from employees where employee_id = 141);
/
-- 比50 department 最低工资低的所有department 的 最低工资
select E.DEPARTMENT_ID,D.DEPARTMENT_NAME,MIN(E.SALARY) from departments d, EMPLOYEES E
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
GROUP BY E.DEPARTMENT_ID,D.DEPARTMENT_NAME
HAVING MIN(E.SALARY) >(SELECT MIN(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = 50)
--其他部门中比job_id = it_prod部门任一工资低的员工信息
SELECT E.JOB_ID,E.EMPLOYEE_ID,E.LAST_NAME,E.SALARY FROM EMPLOYEES E WHERE E.JOB_ID <> 'IT_PROG'
AND E.SALARY < ANY(SELECT SALARY FROM EMPLOYEES WHERE JOB_ID = 'IT_PROG');
-- GREATER THAN ALL SALARY FOR JOB_ID = IT_PROG
SELECT E.JOB_ID,E.EMPLOYEE_ID,E.LAST_NAME,E.SALARY FROM EMPLOYEES E WHERE E.JOB_ID <> 'IT_PROG'
AND E.SALARY > ALL(SELECT SALARY FROM EMPLOYEES WHERE JOB_ID = 'IT_PROG');
-- THE EMPLOYEE HAS LOWEST SALARY LAST NAME SALARY
SELECT E.LAST_NAME,E.SALARY FROM EMPLOYEES E WHERE E.SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES);
-- AVG工资最低部门信息
SELECT D.DEPARTMENT_ID,D.DEPARTMENT_NAME,D.MANAGER_ID,D.LOCATION_ID FROM DEPARTMENTS D,EMPLOYEES E WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID,D.DEPARTMENT_NAME,D.MANAGER_ID,D.LOCATION_ID
HAVING AVG(E.SALARY) = (SELECT MIN(AVG(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);
/
--V2
SELECT * FROM DEPARTMENTS D WHERE D.DEPARTMENT_ID IN (
SELECT E.DEPARTMENT_ID FROM EMPLOYEES E
GROUP BY E.DEPARTMENT_ID
HAVING AVG(E.SALARY) = (SELECT MIN(AVG(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)
)
--查询平均工资最低的部门信息和该部门的平均工资
SELECT D.DEPARTMENT_ID,D.DEPARTMENT_NAME,AVG(E.SALARY)
FROM DEPARTMENTS D ,EMPLOYEES E WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID,D.DEPARTMENT_NAME
HAVING AVG(E.SALARY) = (SELECT MIN(AVG(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);
/
--V2
SELECT D.*, (SELECT AVG(SALARY) FROM EMPLOYEES
WHERE DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY DEPARTMENT_ID) AVG_SAL
FROM DEPARTMENTS D WHERE D.DEPARTMENT_ID IN (
SELECT E.DEPARTMENT_ID FROM EMPLOYEES E
GROUP BY E.DEPARTMENT_ID
HAVING AVG(E.SALARY) = (SELECT MIN(AVG(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)
);
/
--查询平均工资最高的job信息
SELECT E.JOB_ID,J.JOB_TITLE,J.MIN_SALARY,J.MAX_SALARY ,AVG(E.SALARY)
FROM JOBS J, EMPLOYEES E WHERE E.JOB_ID = J.JOB_ID
GROUP BY E.JOB_ID,J.JOB_TITLE,J.MIN_SALARY,J.MAX_SALARY
HAVING AVG(E.SALARY)=(SELECT MAX(AVG(SALARY)) FROM EMPLOYEES GROUP BY JOB_ID);
/
-- V2
SELECT * FROM JOBS J WHERE J.JOB_ID = (
SELECT E.JOB_ID FROM EMPLOYEES E
GROUP BY JOB_ID
HAVING AVG(E.SALARY) = (SELECT MAX(AVG(SALARY)) FROM EMPLOYEES GROUP BY JOB_ID)
)
-- 查询平均工资高于公司平均工资的部门有哪些
SELECT D.DEPARTMENT_NAME,D.DEPARTMENT_ID,AVG(E.SALARY) FROM DEPARTMENTS D, EMPLOYEES E
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID,D.DEPARTMENT_NAME
HAVING AVG(E.SALARY) > (SELECT AVG(SALARY) FROM EMPLOYEES );
--V2
SELECT D.* FROM DEPARTMENTS D
WHERE D.DEPARTMENT_ID IN (
SELECT E.DEPARTMENT_ID FROM EMPLOYEES E
GROUP BY E.DEPARTMENT_ID
HAVING AVG(E.SALARY) > (SELECT AVG(SALARY) FROM EMPLOYEES)
);
-- RETURN ALL MANAGERS INFO
SELECT * FROM EMPLOYEES E WHERE E.EMPLOYEE_ID IN(
SELECT DISTINCT MANAGER_ID FROM EMPLOYEES
);
/
--RETURN部门中 最高工资中最低'S部门的最低工资是多少
SELECT D.DEPARTMENT_ID,MIN(E.SALARY) FROM DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID
HAVING MAX(E.SALARY) = (SELECT MIN(MAX(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID);
/
--MANAGER IN DEPARTMENT WHICH HAS HIGHEST AVG SALARY
SELECT M.* FROM EMPLOYEES M WHERE
M.EMPLOYEE_ID IN (SELECT DISTINCT MANAGER_ID FROM EMPLOYEES WHERE DEPARTMENT_ID =
( SELECT E2.DEPARTMENT_ID FROM EMPLOYEES E2
GROUP BY E2.DEPARTMENT_ID
HAVING AVG(E2.SALARY) = (SELECT MAX(AVG(SALARY)) FROM EMPLOYEES GROUP BY DEPARTMENT_ID)))
--RETURN THE EMPLOYEE WHO HAS HIGHEST SALARY FROM 2004
SELECT * FROM EMPLOYEES E WHERE E.SALARY =
(SELECT MAX(SALARY) FROM EMPLOYEES WHERE HIRE_DATE > TO_DATE('2004','YYYY'))
AND E.HIRE_DATE > TO_DATE('2004','YYYY');
/
--1. 查询和Zlotkey相同部门的员工姓名和雇用日期
SELECT E.LAST_NAME,E.HIRE_DATE FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID=
(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE LAST_NAME = 'Zlotkey');
--2. 查询工资比公司平均工资高的员工的员工号,姓名和工资。
SELECT E.EMPLOYEE_ID,E.SALARY,E.LAST_NAME FROM EMPLOYEES E WHERE E.SALARY >
(SELECT AVG(SALARY) FROM EMPLOYEES);
--3. 查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
SELECT E.EMPLOYEE_ID,E.LAST_NAME,E.SALARY,e.department_id FROM EMPLOYEES E
WHERE E.SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES
WHERE DEPARTMENT_ID = E.DEPARTMENT_ID
);
--4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT * FROM EMPLOYEES E WHERE E.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
FROM EMPLOYEES WHERE UPPER(LAST_NAME) LIKE '%U%');
--5. 查询在部门的location_id为1700的部门工作的员工的员工号
SELECT E.EMPLOYEE_ID FROM EMPLOYEES E WHERE E.DEPARTMENT_ID IN (SELECT D.DEPARTMENT_ID
FROM DEPARTMENTS D
WHERE D.LOCATION_ID = 1700);
--6.查询管理者是King的员工姓名和工资
SELECT E.LAST_NAME,E.SALARY FROM EMPLOYEES E
WHERE E.MANAGER_ID in (SELECT EMPLOYEE_ID
FROM EMPLOYEES WHERE LAST_NAME = 'King');
--更新 114号员工的工作和工资使其与205号员工相同。
UPDATE EMPLOYEES E SET E.SALARY = (SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID = 205),
E.JOB_ID = (SELECT JOB_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 205)
WHERE E.EMPLOYEE_ID = 114;
SELECT * FROM EMPLOYEES E WHERE E.EMPLOYEE_ID IN (205,114);
--调整与employee_id 为200的员工job_id相同的员工的department_id为employee_id为100的员工的department_id。
UPDATE EMPLOYEES E SET
E.DEPARTMENT_ID =(SELECT EE.DEPARTMENT_ID
FROM EMPLOYEES EE
WHERE EE.JOB_ID =
(SELECT JOB_ID FROM EMPLOYEES WHERE EMPLOYEE_ID = 200))
WHERE E.EMPLOYEE_ID = 100;
--55. 更改 108 员工的信息: 使其工资变为所在部门中的最高工资, job 变为公司中平均工资最低的 job
SAVEPOINT Q55;
UPDATE EMPLOYEES E SET E.SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = E.DEPARTMENT_ID),
E.JOB_ID = (SELECT JOB_ID FROM EMPLOYEES HAVING AVG(SALARY) = (SELECT MIN(AVG(SALARY)) FROM EMPLOYEES GROUP BY JOB_ID)
GROUP BY JOB_ID)
WHERE E.EMPLOYEE_ID = 108;
SELECT * FROM EMPLOYEES E WHERE E.EMPLOYEE_ID = 113;
SAVEPOINT Q56;
--56. 删除 108 号员工所在部门中工资最低的那个员工.
DELETE FROM EMPLOYEES E WHERE E.SALARY = (SELECT MIN(E2.SALARY) FROM EMPLOYEES E2, EMPLOYEES E8 WHERE E2.DEPARTMENT_ID = E8.DEPARTMENT_ID AND E8.EMPLOYEE_ID = 108);
--ROLLBACK TO Q55;
create table emp2 as select employee_id id, last_name name, salary from employees;
create table dept2 as select department_id id, department_name dept_name from departments;
--1. 向表emp2的id列中添加PRIMARY KEY约束(my_emp_id_pk)
ALTER TABLE EMP2
ADD CONSTRAINT MY_EMP_ID_PK PRIMARY KEY (ID);
--2. 向表dept2的id列中添加PRIMARY KEY约束(my_dept_id_pk)
ALTER TABLE DEPT2
ADD CONSTRAINT MY_DEPT_ID_PK PRIMARY KEY (ID);
--3. 向表emp2中添加列dept_id,并在其中定义FOREIGN KEY约束,与之相关联的列是dept2表中的id列。
ALTER TABLE EMP2
ADD (DEPT_ID NUMBER(10) CONSTRAINT EMP2_DEPT_ID_FK REFERENCES DEPT2(ID));
--62. 查询员工表中 salary 前 10 的员工信息.
SELECT * FROM (SELECT E.* FROM EMPLOYEES E ORDER by E.Salary desc) where rownum <=10;
--63. 查询员工表中 salary 10 - 20 的员工信息.
select * from (SELECT rownum rn ,sal.* FROM (SELECT rownum ,E.* FROM EMPLOYEES E ORDER by E.Salary desc) sal) where rn between 10 and 20;
--64. 对 oralce 数据库中记录进行分页: 每页显示 10 条记录, 查询第 5 页的数据
SELECT sal.*
FROM (SELECT rownum rn,E.*
FROM EMPLOYEES E) sal
where sal.rn between 10*(&pageNum-1) and 10*&pageNum;
--1. 使用表employees创建视图employee_vu,其中包括姓名(LAST_NAME),员工号(EMPLOYEE_ID),部门号(DEPARTMENT_ID).
CREATE OR REPLACE VIEW EMPLOYEE_VU
AS
SELECT LAST_NAME, EMPLOYEE_ID,DEPARTMENT_ID
FROM EMPLOYEES;
--3. 查询视图中的全部内容
SELECT * FROM EMPLOYEE_VU;
--4. 将视图中的数据限定在部门号是80的范围内
CREATE OR REPLACE VIEW EMPLOYEE_VU
AS
SELECT LAST_NAME, EMPLOYEE_ID,DEPARTMENT_ID
FROM EMPLOYEES WHERE DEPARTMENT_ID = 80;
--5. 将视图改变成只读视图
CREATE OR REPLACE VIEW EMPLOYEE_VU
AS
SELECT LAST_NAME, EMPLOYEE_ID,DEPARTMENT_ID
FROM EMPLOYEES WHERE DEPARTMENT_ID = 80
WITH READ ONLY;
--1. 创建序列dept_id_seq,开始值为200,每次增长10,最大值为10000
CREATE SEQUENCE DEPT_ID_SEQ
START WITH 200
INCREMENT BY 10
MINVALUE 1
MAXVALUE 10000
NOCYCLE;
--2. 使用序列向表dept中插入数据
ALTER TABLE DEPT
DROP COLUMN TEST_COLUMN;
INSERT INTO DEPT(ID,
NAME)
VALUES(DEPT_ID_SEQ.NEXTVAL,'TEST');
COMMIT;
SELECT * FROM DEPT;
SELECT DISTINCT D.DEPARTMENT_ID,J.JOB_ID FROM EMPLOYEES E, DEPARTMENTS D, JOBS J
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.JOB_ID = J.JOB_ID
AND D.DEPARTMENT_ID IN (10,50,20)
ORDER BY 1;
SELECT E.DEPARTMENT_ID, E.JOB_ID,1 A_DUMMY FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID = 10
UNION
SELECT E.DEPARTMENT_ID, E.JOB_ID,2 FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID = 50
UNION
SELECT E.DEPARTMENT_ID, E.JOB_ID,3 FROM EMPLOYEES E
WHERE E.DEPARTMENT_ID = 20
ORDER BY 3
--问题:查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id
SELECT E.EMPLOYEE_ID,E.MANAGER_ID,E.DEPARTMENT_ID
FROM EMPLOYEES E
WHERE ( E.MANAGER_ID, E.DEPARTMENT_ID) IN
(SELECT MANAGER_ID, DEPARTMENT_ID FROM EMPLOYEES
WHERE EMPLOYEE_ID IN (141,174))
AND EMPLOYEE_ID NOT IN (141,174);
--返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
SELECT E.LAST_NAME, E.DEPARTMENT_ID ,E.SALARY,(SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID = E.DEPARTMENT_ID)
FROM EMPLOYEES E
WHERE E.SALARY > (SELECT AVG(SALARY) FROM EMPLOYEES
WHERE DEPARTMENT_ID = E.DEPARTMENT_ID
)
ORDER BY E.DEPARTMENT_ID,E.SALARY
SELECT E.LAST_NAME, E.DEPARTMENT_ID ,E.SALARY,DEPT_AVG.AVG_SAL
FROM EMPLOYEES E, (SELECT AVG(SALARY) AVG_SAL, DEPARTMENT_ID
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID) DEPT_AVG
WHERE E.DEPARTMENT_ID = DEPT_AVG.DEPARTMENT_ID
AND E.SALARY > DEPT_AVG.AVG_SAL;
--显式员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’。
SELECT E.EMPLOYEE_ID,E.LAST_NAME,CASE
WHEN E.DEPARTMENT_ID = (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE LOCATION_ID = 1800)
THEN 'CANADA'
ELSE 'USA'
END
FROM EMPLOYEES E;
SELECT E.EMPLOYEE_ID,E.LAST_NAME,CASE E.DEPARTMENT_ID
WHEN (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE LOCATION_ID = 1800)
THEN 'CANADA'
ELSE 'USA'
END
FROM EMPLOYEES E;
--查询员工的employee_id,last_name,要求按照员工的department_name排序
SELECT E.EMPLOYEE_ID,E.LAST_NAME
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY D.DEPARTMENT_NAME;
SELECT EMPLOYEE_ID, LAST_NAME FROM EMPLOYEES E
ORDER BY (SELECT D.DEPARTMENT_NAME FROM DEPARTMENTS D WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID );
--问题:若employees表中employee_id与job_history表中employee_id相同的数目不小于2,输出这些相同id的员工的employee_id,last_name和其job_id
SELECT * FROM EMPLOYEES E
WHERE E.EMPLOYEE_ID IN (SELECT jh.employee_id from job_history jh where jh.employee_id = e.employee_id)
AND (SELECT COUNT (DISTINCT E1.EMPLOYEE_ID) FROM EMPLOYEES E1, JOB_HISTORY JH WHERE JH.EMPLOYEE_ID = E1.EMPLOYEE_ID) >=2;
SELECT E.EMPLOYEE_ID,E.LAST_NAME,E.JOB_ID FROM EMPLOYEES E
WHERE (SELECT COUNT(JH.EMPLOYEE_ID) FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID = E.EMPLOYEE_ID) >=2;
--问题:查询公司管理者的employee_id,last_name,job_id,department_id信息
SELECT DISTINCT E.EMPLOYEE_ID,E.LAST_NAME,E.JOB_ID,E.DEPARTMENT_ID
FROM EMPLOYEES E
WHERE EXISTS --(SELECT * FROM DEPARTMENTS D WHERE D.MANAGER_ID = E.EMPLOYEE_ID)
(SELECT * FROM EMPLOYEES E2 WHERE E2.MANAGER_ID = E.EMPLOYEE_ID);
--问题:查询departments表中,不存在于employees表中的部门的department_id和department_name
SELECT D.DEPARTMENT_ID,D.DEPARTMENT_NAME FROM DEPARTMENTS D
WHERE NOT EXISTS (SELECT * FROM EMPLOYEES E WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID);
--问题:查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
WITH
DEPT_AVG_INFO AS(
SELECT SUM(SALARY)/COUNT(DISTINCT DEPARTMENT_ID) SUM_AVG FROM EMPLOYEES
),
DEPT_SUM_INFO AS(
SELECT DEPARTMENT_ID,SUM(SALARY) SUM_SAL
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
)
SELECT * FROM DEPT_SUM_INFO WHERE SUM_SAL > (SELECT SUM_AVG FROM DEPT_AVG_INFO);
/
SELECT * FROM DEPARTMENTS D,
(SELECT DEPARTMENT_ID,SUM(SALARY) SUM_SAL
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID)DEPT_SUM_INFO
WHERE D.DEPARTMENT_ID = DEPT_SUM_INFO.DEPARTMENT_ID
AND DEPT_SUM_INFO.SUM_SAL >(SELECT SUM(SALARY)/COUNT(DISTINCT DEPARTMENT_ID) SUM_AVG FROM EMPLOYEES)
--1. 查询员工的last_name, department_id, salary.其中员工的salary,department_id与有奖金的任何一个员工的salary,department_id相同即可
SELECT E.LAST_NAME,E.DEPARTMENT_ID,E.SALARY FROM EMPLOYEES E
WHERE (E.SALARY,E.DEPARTMENT_ID) IN (SELECT E2.SALARY, E2.DEPARTMENT_ID FROM EMPLOYEES E2 WHERE E2.COMMISSION_PCT IS NOT NULL);
--2. 选择工资大于所有JOB_ID = 'SA_MAN'的员工的工资的员工的last_name, job_id, salary
SELECT E.LAST_NAME,E.JOB_ID,E.SALARY FROM EMPLOYEES E
WHERE E.SALARY > ALL (SELECT SALARY FROM EMPLOYEES WHERE JOB_ID = 'SA_MAN')
--(SELECT MAX(SALARY) FROM EMPLOYEES WHERE JOB_ID = 'SA_MAN');
--3. 选择所有没有管理者的员工的last_name
SELECT * FROM EMPLOYEES E
WHERE NOT EXISTS (SELECT * FROM EMPLOYEES E1 WHERE E1.EMPLOYEE_ID = E.MANAGER_ID)