Giáo trình SQL và PL SQL part 3

Chia sẻ: Mr Yukogaru | Ngày: | Loại File: PDF | Số trang:7

Thêm vào BST

Báo xấu

198
lượt xem 66
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Bài tập: một số bài tập về toán tử. sau đó là phần bài học về các hàm áp dụng cho 1 dòng dữ liêu........

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Giáo trình SQL và PL SQL part 3

C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 3.4 Bµi tËp 1. Chän nh©n viªn trong b¶ng EMP cã møc l−¬ng tõ 1000 ®Õn 2000 (chän c¸c tr−êng ENAME, DEPTNO, SAL). ENAME DEPTNO SAL ---------- ---------- ---------- ALLEN 30 1600 WARD 30 1250 MARTIN 30 1250 TURNER 30 1500 ADAMS 20 1100 MILLER 10 1300 Sal Between 1000 to 2000 2.HiÓn thÞ m· phßng ban, tªn phßng ban, s¾p xÕp theo thø tù tªn phßng ban. DEPTNO DNAME ------ ----------- 10 ACCOUNTING 40 OPERATIONS 20 RESEARCH 30 SALES Order by dname 3. HiÓn thÞ danh s¸ch nh÷ng nh©n viªn lµm t¹i phßng 10 vµ 20 theo thø tù A,B,C EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ----------------- ----- ---------- ---------- ----- ----- 7876 ADAMS CLERK 7788 12-01-1983 1100 20 7782 CLARK MANAGER 7839 09-06-1981 2450 10 7902 FORD ANALYST 7566 03-12-1981 3000 20 7566 JONES MANAGER 7839 02-04-1981 2975 20 7839 KING PRESIDENT 17-11-1981 5000 10 7934 MILLER CLERK 7782 23-01-1982 1300 10 7788 SCOTT ANALYST 7566 09-12-1982 3000 20 7369 SMITH CLERK 7902 17-12-1980 800 20 Where deptno in (10,20) order by ename asc 4. HiÓn thÞ tªn vµ nghÒ nghiÖp nh÷ng nh©n viªn lµm nghÒ th− ký (clerk) t¹i phßng 20. ENAME JOB ---------- --------- SMITH CLERK ADAMS CLERK Where upper(job)= upper('clerk') and deptno = '20'; (l−u ý vÊn ®Ò ch÷ Hoa-th−êng) 5. HiÓn thÞ tÊt c¶ nh÷ng nh©n viªn mµ tªn cã c¸c ký tù TH vµ LL. ENAME ---------- SMITH ALLEN MILLER Where ename like “%TH%“ or ename like “%LL%“ 6. HiÓn thÞ tªn nh©n viªn, nghÒ nghiÖp, l−¬ng cña nh÷ng nh©n viªn cã gi¸m ®èc qu¶n lý. ENAME JOB SAL ---------- --------- ---------- SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 2975 §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 19
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 FORD ANALYST 3000 MILLER CLERK 1300 13 rows selected. 7. HiÓn thÞ tªn nh©n viªn, m· phßng ban, ngµy gia nhËp c«ng ty sao cho gia nhËp c«ng ty trong n¨m 1983. ENAME DEPTNO HIREDATE ---------- ---------- --------- ADAMS 20 12-JAN-83 Where to_char(hiredate) like “%83“ Where hiredate like “%83“ 8. HiÓn thÞ tªn nh©n viªn, l−¬ng mét n¨m (ANUAL_SAL ), th−ëng sao cho l−¬ng lín h¬n th−ëng vµ nghÒ nghiÖp lµ SALEMAN, s¾p theo thø tù l−¬ng gi¶m dÇn vµ tªn t¨ng dÇn. ANUAL_SAL COMM ---------- ---------- 19200 300 18000 0 15000 500 4 C¸c hµm ¸p dông cho 1 dßng d÷ liÖu 4.1 C¸c hµm sè §Çu vµo vµ ®Çu ra lµ c¸c gi¸ trÞ kiÓu sè ROUND(n[,m]) cho gi¸ trÞ lµm trßn cña n (®Õn cÊp m, mÆc nhiªn m=0) TRUNC(n[,m]) cho gi¸ trÞ n lÊy m ch÷ sè tÝnh tõ chÊm thËp ph©n = Format(dl, “99999,00“) CEIL(n) cho sè nguyªn nhá nhÊt lín h¬n hoÆc b»ng n = FLOOR(n) cho sè nguyªn lín nhÊt b»ng hoÆc nhá h¬n n = POWER(m,n) cho lòy thõa bËc n cña m = EXP(n) cho gi¸ trÞ cña en = SQRT(n) cho c¨n bËc 2 cña n, n>=0 = SIGN(n) cho dÊu cña n. = n0 cã SIGN(n)= 1 ABS(n) cho gi¸ trÞ tuyÖt ®èi = MOD(m,n) cho phÇn d− cña phÐp chia m cho n = Mét sè hµm kiÓu sè tham kh¶o kh¸c: LOG(m,n) cho logarit c¬ sè m cña n = §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 20
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL SIN(n) cosin cña n (n tÝnh b»ng radian) = COS(n) cho cosin cña n (n tÝnh b»ng radian) = TAN(n) cotang cña n (n tÝnh b»ng radian) = VÝ dô hµm ROUND(n[,m]) SELECT ROUND(4.923,1), ROUND(4.923), ROUND(4.923,-1), ROUND(4.923,2) FROM DUMMY; ROUND(4.923,1) ROUND(4.923) ROUND(4.923,-1) ROUND(4.923,2) -------------- ------------ --------------- -------------- 4.9 5 0 4.92 VÝ dô hµm TRUNC(n[,m]) SELECT TRUNC (4.923,1), TRUNC (4.923), TRUNC (4.923,-1), TRUNC (4.923,2) FROM DUMMY; TRUNC(4.923,1) TRUNC(4.923) TRUNC(4.923,-1) TRUNC(4.923,2) -------------- ------------ --------------- -------------- 4.9 4 0 4.92 VÝ dô hµm CEIL(n) SELECT CEIL (SAL), CEIL(99.9),CEIL(101.76), CEIL(-11.1) FROM EMP WHERE SAL BETWEEN 3000 AND 5000; CEIL(SAL) CEIL(99.9) CEIL(101.76) CEIL(-11.1) ---------- ---------- ------------ ----------- 5000 100 102 -11 3000 100 102 -11 3000 100 102 -11 VÝ dô hµm FLOOR(n) SELECT FLOOR (SAL), FLOOR (99.9), FLOOR (101.76), FLOOR (-11.1) FROM EMP WHERE SAL BETWEEN 3000 AND 5000; FLOOR(SAL) FLOOR(99.9) FLOOR(101.76) FLOOR(-11.1) ---------- ----------- ------------- ------------ 5000 99 101 -12 3000 99 101 -12 3000 99 101 -12 VÝ dô hµm POWER(m,n) SELECT SAL, POWER(SAL,2), POWER(SAL,3), POWER(50,5) FROM EMP WHERE DEPTNO =10; SAL POWER(SAL,2) POWER(SAL,3) POWER(50,5) ---------- ------------ ------------ ----------- 5000 25000000 1.2500E+11 312500000 2450 6002500 1.4706E+10 312500000 1300 1690000 2197000000 312500000 VÝ dô hµm EXP(n) SELECT EXP(4) FROM DUMMY; EXP(4) §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 21
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL ---------- 54.59815 VÝ dô hµm SQRT(n) SELECT SAL, SQRT(SAL), SQRT(40), SQRT (COMM) FROM EMP WHERE DEPTNO =10; SAL SQRT(SAL) SQRT(40) SQRT(COMM) ---------- ---------- ---------- ---------- 5000 70.7106781 6.32455532 2450 49.4974747 6.32455532 1300 36.0555128 6.32455532 VÝ dô hµm SIGN(n) SELECT SAL-NVL(COMM,0), SIGN(SAL-NVL(COMM,0)), NVL(COMM,0)-SAL, SIGN(NVL(COMM,0)-SAL) FROM EMP WHERE DEPTNO =30 SAL-NVL(COMM,0)SIGN(SAL-NVL(COMM,0))NVL(COMM,0)-SAL SIGN(NVL(COMM,0)-SAL) --------------- ----------- --------------- --------------------- 2850 1 -2850 -1 -150 -1 150 1 1300 1 -1300 -1 1500 1 -1500 -1 950 1 -950 -1 750 1 -750 -1 4.2 C¸c hµm ký tù CONCAT(char1, char2) cho kÕt hîp cña 2 chuçi ký tù, t−¬ng tù nh− sö dông to¸n tö || INITCAP(char) cho chuçi víi ký tù ®Çu c¸c tõ lµ ký tù hoa LOWER(char) cho chuçi ký tù viÕt th−êng (kh«ng viÕt hoa) LPAD(char1, n [,char2]) cho chuçi ký tù cã chiÒu dµi b»ng n. NÕu chuçi char1 ng¾n h¬n n th× thªm vµo bªn tr¸i chuçi char2 cho ®ñ n ký tù. NÕu chuçi char1 dµi h¬n n th× gi÷ l¹i n ký tõ tÝnh tõ tr¸i sang LTRIM(char1, n [,char2]) bá c¸c ký tù trèng bªn tr¸i NLS_INITCAP(char) cho chuçi víi ký tù ®Çu c¸c tõ lµ ch÷ hoa, c¸c ch÷ cßn l¹i lµ ch÷ th−êng REPLACE(char,search_string[,replacement_string]) : thay tÊt c¶ c¸c chuçi search_string cã trong chuçi char b»ng chuçi replacement_string. RPAD(char1, n [,char2]) Gièng LPAD(char1, n [,char2]) nh−ng c¨n ph¶i RTRIM(char1, n [,char2]) bá c¸c ký tù trèng bªn ph¶i SOUNDEX(char) cho chuçi ®ång ©m cña char. SUBSTR(char, m [,n]) cho chuçi con cña chuçi char lÊy tõ vÞ trÝ m vÕ ph¶i n ký tù, nÕu kh«ng chØ n th× lÊy cho ®Õn cuèi chuçi TRANSLATE(char, from, to) cho chuçi trong ®ã mçi ký tù trong chuçi from thay b»ng ký tù t−¬ng øng trong chuçi to, nh÷ng ký tù trong chuçi from kh«ng cã t−¬ng øng trong chuçi to sÏ bÞ lo¹i bá. UPPER(char) cho chuçi ch÷ hoa cña chuçi char §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 22
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL ASCII(char) cho ký tù ASCII cña byte ®Çu tiªn cña chuçi char INSTR(char1, char2 [,n[,m]]) t×m vÞ trÝ chuçi char2 trong chuçi char1 b¾t ®Çu tõ vÞ trÝ n, lÇn xuÊt hiÖn thø m. LENGTH(char) cho chiÒu dµi cña chuçi char VÝ dô hµm LOWER(char) SELECT LOWER(DNAME), LOWER(‘SQL COURSE’) FROM DEPT; LOWER(DNAME) LOWER('SQL -------------- ---------- accounting sql course research sql course sales sql course operations sql course VÝ dô hµm UPPER(char) SELECT ENAME FROM EMP WHERE ENAME = UPPER(‘Smith’); ENAME ---------- SMITH VÝ dô hµm INITCAP(char) SELECT INITCAP(DNAME), INITCAP(LOC) FROM DEPT; INITCAP(DNAME) INITCAP(LOC) -------------- ------------- Accounting New York Research Dallas Sales Chicago Operations Boston VÝ dô hµm CONCAT(char1, char2) SELECT CONCAT(ENAME, JOB) JOB FROM EMP WHERE EMPNO = 7900; JOB ------------------- JAMES CLERK VÝ dô hµm LPAD(char1, n [,char2]) SELECT LPAD(DNAME,20,’*’), LPAD(DNAME,20), LPAD(DEptno,20,’ ’) FROM DEPT; LPAD(DNAME,20,'*') LPAD(DNAME,20) LPAD(DEPTNO,20,'') -------------------- -------------------- -------------------- ******ACCOUNTING ACCOUNTING 10 ******RESEARCH RESEARCH 20 ******SALES SALES 30 ******OPERATIONS OPERATIONS 40 VÝ dô hµm RPAD(char1, n [,char2]) SELECT RPAD(DNAME,20,’*’), RPAD(DNAME,20), RPAD(DEptno,20,’ ’) FROM DEPT; §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 23
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL RPAD(DNAME,20,'*') RPAD(DNAME,20) RPAD(DEPTNO,20,'') -------------------- -------------------- -------------------- ACCOUNTING ****** ACCOUNTING 10 RESEARCH ****** RESEARCH 20 SALES ****** SALES 30 OPERATIONS ****** OPERATIONS 40 VÝ dô hµm SUBSTR(char, m [,n]) SELECT SUBSTR(‘ORACLE’,2,4), SUBSTR(DNAME,2), SUBSTR(DNAME,3,5) FROM DEPT; SUBS SUBSTR(DNAME, SUBST ---- ------------- ----- RACL CCOUNTING COUNT RACL ESEARCH SEARC RACL ALES LES RACL PERATIONS ERATI VÝ dô hµm INSTR(char1, char2 [,n[,m]]) SELECT DNAME, INSTR(DNAME, ‘A’), INSTR(DNAME,’ES’), INSTR(DNAME,’C’,1,2) FROM DEPT; DNAME INSTR(DNAME,'A') INSTR(DNAME,'ES') INSTR(DNAME,'C',1,2) -------------- ---------------- ----------------- -------------------- ACCOUNTING 1 0 3 RESEARCH 5 2 0 SALES 2 4 0 OPERATIONS 5 0 0 VÝ dô hµm LTRIM(char1, n [,char2]) SELECT DNAME, LTRIM(DNAME,’A’), LTRIM(DNAME,’AS’), LTRIM(DNAME,’ASOP’) FROM DEPT; DNAME LTRIM(DNAME,'A LTRIM(DNAME,'A LTRIM(DNAME,'A -------------- -------------- -------------- -------------- ACCOUNTING CCOUNTING CCOUNTING CCOUNTING RESEARCH RESEARCH RESEARCH RESEARCH SALES SALES LES LES OPERATIONS OPERATIONS OPERATIONS ERATIONS VÝ dô hµm RTRIM(char1, n [,char2]) SELECT DNAME, RTRIM(DNAME,’A’), RTRIM(DNAME,’AS’), RTRIM(DNAME,’ASOP’) FROM DEPT; DNAME RTRIM(DNAME,'A RTRIM(DNAME,'A RTRIM(DNAME,'A -------------- -------------- -------------- -------------- ACCOUNTING ACCOUNTING ACCOUNTING ACCOUNTING RESEARCH RESEARCH RESEARCH RESEARCH SALES SALES SALES SALES OPERATIONS OPERATIONS OPERATIONS OPERATIONS VÝ dô hµm SOUNDEX(char) SELECT ENAME, SOUNDEX(ENAME) FROM EMP WHERE SOUNDEX(ENAME)= SOUNDEX(‘FRED’); ENAME SOUN ---------- ---- FORD F630 VÝ dô hµm LENGTH(char) SELECT LENGTH(‘SQL COURSE’), LENGTH(DEPTNO), LENGTH(DNAME) FROM DEPT; §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 24
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 25