Thực hành SQL trên Oracle - Trần Thị Lượng

FROM EMP 
WHERE DEPTNO = 10; 
ENAME TRANSLATE( JOB TRANSLATE 
---------- ---------- --------- --------- 
KING KING PRESIDENT PTESIDENT 
CLARK FLARK MANAGER MINIGET 
MILLER MILLER CLERK CLETK 
Ví dụ hàm REPLACE(char,search_string[,replacement_string]) 
SELECT JOB, REPLACE(JOB, ‘SALESMAN’, 
‘SALESPERSON’), ENAME, REPLACE(ENAME, ‘CO’,’PR’) 
FROM EMP 
WHERE DEPTNO =30 OR DEPTNO =20; 
JOB REPLACE(JOB,'SALESMAN', ENAME 
REPLACE(ENAME,'CO',' 
--------- ----------------------- --------- ------
--------- 
GV: Trần Thị Lượng - HVKTMM 
 31
MANAGER MANAGER BLAKE BLAKE 
MANAGER MANAGER JONES JONES 
SALESMAN SALESPERSON MARTIN MARTIN 
SALESMAN SALESPERSON ALLEN ALLEN 
SALESMAN SALESPERSON TURNER TURNER 
CLERK CLERK JAMES JAMES 
SALESMAN SALESPERSON WARD WARD 
ANALYST ANALYST FORD FORD 
CLERK CLERK SMITH SMITH 
ANALYST ANALYST SCOTT SPRTT 
CLERK CLERK ADAMS ADAMS 
Ví dụ các hàm lồng nhau: 
SELECT DNAME, LENGHT(DNAME), 
LENGHT(TRANSLATE,DNAME, ‘AS’,’A’)) 
FROM DEPT; 
DNAME LENGTH(DNAME) 
LENGTH(TRANSLATE(DNAME,'AS','A')) 
-------------- ------------- ----------------------
----------- 
ACCOUNTING 14 
14 
RESEARCH 14 
13 
SALES 14 
12 
OPERATIONS 14 
13 
c. Các hàm ngày 
MONTH_BETWEEN(d1, d2) cho biết só tháng giữa ngày d1 và d2. 
ADD_MONTHS(d,n) cho ngày d thêm n tháng. 
NEXT_DAY(d, char ) cho ngày tiếp theo ngày d có thứ chỉ bởi char. 
LAST_DAY(d) cho ngày cuối cùng trong tháng chỉ bởi d. 
Ví dụ hàm MONTH_BETWEEN(d1, d2) 
GV: Trần Thị Lượng - HVKTMM 
 32
SELECT MONTHS_BETWEEN( SYSDATE, HIREDATE), 
MONTHS_BETWEEN('01-01-2000','05-10-2000') 
FROM EMP 
WHERE MONTHS_BETWEEN( SYSDATE,HIREDATE)>240; 
MONTHS_BETWEEN(SYSDATE,HIREDATE) TWEEN('01-01-
2000','05-10-2000') 
-------------------------------- ------------------
-------------- 
 241.271055 
-9.1290323 
 241.206539 
-9.1290323 
 243.367829 
-9.1290323 
Ví dụ hàm ADD_MONTHS(d,n) 
SELECT HIREDATE, ADD_MONTHS(HIRE,3), 
ADD_MONTHS(HIREDATE,-3) 
FROM EMP 
WHERE DEPTNO=20; 
HIREDATE ADD_MONTHS ADD_MONTHS 
---------- ---------- ---------- 
02-04-1981 02-07-1981 02-01-1981 
03-12-1981 03-03-1982 03-09-1981 
17-12-1980 17-03-1981 17-09-1980 
09-12-1982 09-03-1983 09-09-1982 
12-01-1983 12-04-1983 12-10-1982 
Ví dụ hàm NEXT_DAY(d, char ) 
SELECT HIREDATE, NEXT_DAY(HIREDATE,’FRIDAY’), 
NEXT_DAY(HIREDATE,6) 
FROM EMP 
WHERE DEPTNO = 10; 
HIREDATE NEXT_DAY(H NEXT_DAY(H 
---------- ---------- ---------- 
17-11-1981 20-11-1981 20-11-1981 
09-06-1981 12-06-1981 12-06-1981 
GV: Trần Thị Lượng - HVKTMM 
 33
23-01-1982 29-01-1982 29-01-1982 
Ví dụ hàm LAST_DAY(d) 
SELECT SYSDATE, LAST_DAY(SYSDATE), HIREDATE, 
LAST_DAY(HIREDATE), 
LAST_DAY(’15-01-2001’) 
FROM EMP 
WHERE DEPTNO =20; 
SYSDATE LAST_DAY(S HIREDATE LAST_DAY(H 
LAST_DAY(' 
---------- ---------- ---------- ---------- -------
--- 
28-03-2001 31-03-2001 02-04-1981 30-04-1981 31-01-
2001 
28-03-2001 31-03-2001 03-12-1981 31-12-1981 31-01-
2001 
28-03-2001 31-03-2001 17-12-1980 31-12-1980 31-01-
2001 
28-03-2001 31-03-2001 09-12-1982 31-12-1982 31-01-
2001 
28-03-2001 31-03-2001 12-01-1983 31-01-1983 31-01-
2001 
Một số hàm khác có thể áp dụng cho kiểu ngày: 
ROUND(date1) trả về ngày date 1 tại thời điểm giữa trưa 12:00 AM 
ROUND(date1,’MONTH’) Nếu date 1 nằm trong nửa tháng đầu trả về ngày đầu 
tiên của thàng, ngược lại sẽ trả về ngày đầu tiên của 
tháng sau. 
ROUND(date1,’YEAR’) Nếu date 1 nằm trong nửa năm đầu trả về ngày đầu tiên 
của thàng, ngược lại sẽ trả về ngày đầu tiên của năm sau. 
TRUNC(date1, ’MONTH’) Trả về ngày đầu tiên của tháng chứa date1 
TRUNC(date1, ’YEAR’) Trả về ngày đầu tiên của năm chứa date1 
d. Các hàm chuyển đổi kiểu 
TO_CHAR(number|date, ‘fmt’) Chuyển kiểu số và ngày về kiểu ký tự. 
TO_NUMBER(char) Chuyển ký tự có nội dung số sang số 
GV: Trần Thị Lượng - HVKTMM 
 34
TO_DATE(‘chsr’,’fmt’) Chuyển ký tự sang kiểu ngày với định dạng đặt 
trong fmt. 
DECODE(EXPR, SEARCH1, RESULT1, SEARCH2, RESULT2, DEFAULT): 
So sánh biểu thức expr với giá trị search nếu 
đúng trả về giá trị result nếu không trả về giá trị 
default. 
NVL(COL|VALUE, VAL) Chuyển giá trị COL|VALUE thành val nếu null. 
Greatest(col|value1, col|value2) Trả giá trị lớn nhất trong dãy giá trị. 
Vd: 
SELECT To_char (sysdate, ‘day, ddth month yyyy’) 
from dummy; 
SELECT EMPNO, ENAME, HIREDATE 
FROM EMP 
WHERE HIREDATE = TO_DATE (‘June 4, 1984’, ‘month 
dd, yyyy’); 
INSERT INTO EMP (EMPNO, DEPTNO, HIREDATE 
VALUES (777, 20, TO_DATE(’19-08-2000’, ‘DD-MM-
YYYY’); 
SELECT ENAME, JOB, 
DECODE (JOB, 
‘CLERK’,’WWORKER’,’MANAGER’,’BOSS’,’UNDEFINED’) 
DECODD_JOB 
FROM EMP; 
SELECT GREATEST(1000,2000), GREATEST(SAL,COMM) FROM 
EMP 
WHERE DEPTNO = 10; 
Một số khuôn dạng ngày 
SCC hoc CC th k; S ch ngày BC 
YYYY hoc SYYYY nm; S ch ngày BC 
YYY, YY, Y Ch nm vi 3,2,1 ký t s 
IYYY, IYY, IY, I Ch nm theo chun ISO 
GV: Trần Thị Lượng - HVKTMM 
 35
SYEAR, YEAR Ch nm theo cách phát âm ca ngi 
anh; 
Q Quý trong nm 
MM Giá tr tháng vi 2 s (01-12) 
MONTH Tên y  ca tháng theo ting 
anh,  dài 9 
MON Tháng vi 3 ký t vin tt (JAN, 
FEB...) 
WW, W Tun trong nm hoc trong tháng 
DDD, DD, D Ngày trong nm, tháng hoc tun 
DAY Ch th trong tun 
DY Ch th trong tun vi 3 ký t 
vit tt 
J Ngày Julian; bt u t ngày 
31/12/4713 trc công nguyên 
AM, PM Ch nh sáng, chiu 
HH, HH12 HH24 Ch gi trong ngày (1-12) hoc (0-
23) 
MI Phút (0-59) 
SS Giây (0-59) 
SSSSS S giây n na êm (0-86399) 
/ . , - c t ng thêm khi t trong 
khuôn dng 
“char” on ký t t trong nháy úp c 
t ng thêm khi t trong khuôn 
dng 
TH Thêm phn th t (1st, 2nd, 4th ) 
SP Phát âm s ( FOUR vi DDSP) 
SPTH, THSP Phát âm và chuyn sang dng th t 
( First, second, ...) 
RR Ngày chuyn giao thiên niên k vi 
các nm <1999. 
 N
m 
0-49 50-99 
Nm 
hin 
ti 
0-49 th k 
hin ti 
Th k 
sau 
 50-99 Th k 
trc 
Th k 
hin 
GV: Trần Thị Lượng - HVKTMM 
 36
ti 
Một số khuôn dạng số 
Ký 
t 
Mô t Ví d Kt qu
9 Xác nh hin th 1 
s 
999999 1234 
0 Hin th c s 0  
u nu  dài khuôn 
dng ln hn s hin 
có 
099999 001234
$ Thêm ký t tin t $999999 $1234 
L Thêm ký t tin t 
bn a 
L999999 FF1234
. Du thp phân 999999.9
9 
1234.00
, Du phân cách phn 
nghìn 
999,999 1,234 
MI Du âm  bên phi ( 
vi các giá tr âm) 
999999MI 1234- 
PR Thêm ngoc nhn vào 
các giá tr âm 
999999PR 
EEE Chuyn sang hin th 
s E 
99.9999R
RRR 
1.234E+03
V Nhân vi 10 n, n là s 
các s 9 t sau V 
9999V99 123400
B Hin th c giá tr 0 
nu = 0. 
B9999.99 1234.00
e. Bài tập 
1. Liệt kê tên nhân viên, mã phòng ban và lương nhân viên được tăng 15% 
(PCTSAL). 
 DEPTNO ENAME PCTSAL 
---------- ---------- ---------- 
 10 KING 5000 
 30 BLAKE 2850 
 10 CLARK 2450 
 20 JONES 2975 
 30 MARTIN 1250 
GV: Trần Thị Lượng - HVKTMM 
 37
 30 ALLEN 1600 
 30 TURNER 1500 
 30 JAMES 950 
 30 WARD 1250 
 20 FORD 3000 
 20 SMITH 800 
 20 SCOTT 3000 
 20 ADAMS 1100 
 10 MILLER 1300 
2. Viết câu lệnh hiển thị như sau: 
EMPLOYEE_AND_JOB 
-------------------- 
KING*******PRESIDENT 
BLAKE********MANAGER 
CLARK********MANAGER 
JONES********MANAGER 
MARTIN******SALESMAN 
ALLEN*******SALESMAN 
TURNER******SALESMAN 
JAMES**********CLERK 
WARD********SALESMAN 
FORD*********ANALYST 
SMITH**********CLERK 
SCOTT********ANALYST 
ADAMS**********CLERK 
MILLER*********CLERK 
3. Viết câu lệnh hiển thị như sau: 
EMPLOYEE 
----------------- 
KING (President) 
BLAKE (Manager) 
CLARK (Manager) 
JONES (Manager) 
MARTIN (Salesman) 
ALLEN (Salesman) 
TURNER (Salesman) 
JAMES (Clerk) 
WARD (Salesman) 
FORD (Analyst) 
GV: Trần Thị Lượng - HVKTMM 
 38
SMITH (Clerk) 
SCOTT (Analyst) 
ADAMS (Clerk) 
MILLER (Clerk) 
4. Viết câu lệnh hiển thị như sau: 
ENAME DEPTNO JOB 
---------- ---------- --------------- 
BLAKE 30 Manager 
MARTIN 30 Salesperson 
ALLEN 30 Salesperson 
TURNER 30 Salesperson 
JAMES 30 Clerk 
WARD 30 Salesperson 
5. Tìm ngày thứ 6 đầu tiên cách 2 tháng so với ngày hiện tại hiển thị ngày dưới 
dạng 09 February 1990. 
6. Tìm thông itn về tên nhân viên, ngày gia nhập công ty của nhân viên phòng số 
20, sao cho hiển thị như sau: 
ENAME DATE_HIRED 
---------- -------------------------- 
JONES april,SECOND 1981 
FORD december,THIRD 1981 
SMITH december,SEVENTEENTH 1980 
SCOTT december,NINTH 1982 
ADAMS january,TWELFTH 1983 
7. Hiển thị tên nhân viên, ngày gia nhập công ty, ngày xét nâng lương (sau ngày 
gia nhập công ty 1 năm), sắp xếp theo thứ tự ngày xét nâng lương. 
ENAME HIREDATE REVIEW 
 ---------- ---------- ---------- 
SMITH 17-12-1980 17-12-1981 
ALLEN 20-02-1981 20-02-1982 
WARD 22-02-1981 22-02-1982 
JONES 02-04-1981 02-04-1982 
BLAKE 01-05-1981 01-05-1982 
CLARK 09-06-1981 09-06-1982 
TURNER 08-09-1981 08-09-1982 
MARTIN 28-09-1981 28-09-1982 
GV: Trần Thị Lượng - HVKTMM 
 39
KING 17-11-1981 17-11-1982 
JAMES 03-12-1981 03-12-1982 
FORD 03-12-1981 03-12-1982 
MILLER 23-01-1982 23-01-1983 
SCOTT 09-12-1982 09-12-1983 
ADAMS 12-01-1983 12-01-1984 
8.Hiển thị tên nhân viên và lương dưới dạng 
ENAME SALARY 
---------- ------------ 
ADAMS BELOW 1500 
ALLEN 1600 
BLAKE 2850 
CLARK 2450 
FORD 3000 
JAMES BELOW 1500 
JONES 2975 
KING 5000 
MARTIN BELOW 1500 
MILLER BELOW 1500 
SCOTT 3000 
SMITH BELOW 1500 
TURNER On Target 
WARD BELOW 1500 
9. Cho biết thứ của ngày hiện tại 
10. Đưa chuỗi dưới dạng nn/nn, kiểm tra nếu khúng khuôn dạng 
trả lời là YES, ngược lại là no. Kiểm tra với các chuỗi 12/34, 
01/1a, 99\88 
VALUE VALID? 
----- ------- 
12/34 YES 
11. Hiển thị tên nhân viên, ngày gia nhập công ty, ngày lĩnh lương 
sao cho ngày lĩnh lương phải vào thứ 6, nhân viên chỉ được nhận 
lương sau ít nhất 15 ngày làm việc tại công ty, sắp xếp theo thứ tự 
ngày gia nhập công ty. 
GV: Trần Thị Lượng - HVKTMM 
 40