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

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

Thêm vào BST

Báo xấu

110
lượt xem 38
download

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

Mênh đề group by : mệnh đề group by sẽ nhóm ác dòng dữ liệu có cùng giá trị của expr . Ví dụ Group by job nghĩa là sẽ nhóm các mệnh đề giống nhau.................

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 5

C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL SUM([DISTINCT/ALL] n) Tæng cña cña n kh«ng kÓ trÞ null VARIANCE([DISTINCT/ALL] n) Variance cña n kh«ng kÓ trÞ null Chó ý tÊt c¶ c¸c hµm trªn nhãm mÉu tin ®Òu bá qua gi¸ trÞ NULL trõ hµm COUNT. Dïng hµm NVL ®Ó chuyÓn ®æi vµ tÝnh gi¸ trÞ NULL. Cã 2 c¸ch ®Ó dïng c¸c c¸c hµm nµy • T¸c ®éng trªn toµn bé c¸c dßng d÷ liÖu cña c©u lÖnh truy vÊn • T¸c ®éng trªn mét nhãm d÷ liÖu cïng tÝnh chÊt cña c©u lÖnh truy vÊn. Cïng tÝnh chÊt ®−îc chØ bëi mÖnh ®Ò [GROUP BY expr] [HAVING condition] §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 33
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL VÝ dô T¸c ®éng trªn toµn bé c¸c dßng d÷ liÖu cña c©u lÖnh truy vÊn: Select AVG(SAL) FROM EMP: /TÝnh møc l−¬ng trung b×nh cña toµn bé nh©n viªn / Select MIN(SAL) FROM EMP WHERE JOB =’CLERK’: /TÝnh møc l−¬ng thÊp nhÊt cña nh©n viªn lµm nghÒ CLERK / VÝ dô t¸c ®éng trªn mét nhãm d÷ liÖu cïng tÝnh chÊt cña c©u lÖnh truy vÊn. SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB; / TÝnh møc l−¬ng trung b×nh cña tõng lo¹i nghÒ nghiÖp/ Chó ý: ChØ ®−îc cïng ®Æt trong mÖnh ®Ó SELECT c¸c hµm nhãm hoÆc c¸c column ®· ®Æt trong mÖnh ®Ò GROUP BY. VÝ dô §óng: SELECT MAX(SAL), JOB FROM EMP GROUP BY JOB; Sai: SELECT MAX(SAL), JOB FROM EMP; 6.2 MÖnh ®Ò GROUP BY Có ph¸p: SELECT [DISTINCT ] {*, column [alias],...} FROM table; [WHERE condition] [GROUP BY expr] [HAVING [HAVING condition] [ORDER BY expr/position [DESC/ASC]] MÖnh ®Ò GROUP BY sÏ nhãm c¸c dßng d÷ liÖu cã cïng gi¸ trÞ cña expr. VÝ dô GROUP BY JOB nghÜa lµ sÏ nhãm c¸c nghÒ gièng nhau. MÖnh ®Ò HAVING lµ ®Æt ®iÒu kiÖn cña nhãm d÷ liÖu. MÖnh ®Ò nµy kh¸c mÖnh ®Ò WHERE ë chç mÖnh ®Ò WHERE ®Æt ®iÒu kiÖn cho toµn bé c©u lÖnh SELECT. VÝ dô: SELECT JOB, MAX(SAL) FROM EMP WHERE JOB !=’MANAGER’ GROUP BY JOB; JOB MAX(SAL) --------- ---------- ANALYST 3000 CLERK 1300 PRESIDENT 5000 SALESMAN 1600 SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB HAVING COUNT(*)>3; JOB MAX(SAL) --------- ---------- CLERK 1300 SALESMAN 1600 SELECT JOB, MAX(SAL) FROM EMP §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 34
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL HAVING MAX(SAL)>=3000 GROUP BY JOB; JOB MAX(SAL) --------- ---------- ANALYST 3000 PRESIDENT 5000 6.3 Bµi tËp 1. T×m l−¬ng thÊp nhÊt, lín nhÊt vµ l−¬ng trung b×nh cña tÊt c¶ c¸c nh©n viªn 2. t×m l−¬ng nhá nhÊt vµ lín cña mçi lo¹i nghÒ nghiÖp 3. T×m xem cã bao nhiªu gi¸m ®èc trong danh s¸ch nh©n viªn. 4. T×m tÊt c¶ c¸c phßng ban mµ sè nh©n viªn trong phßng >3 5. T×m ra møc l−¬ng nhá nhÊt cña mçi nh©n viªn lµm viÖc cho mét gi¸m ®èc nµo ®ã s¾p xÕp theo thø tù t¨ng dÇn cña møc l−¬ng. 7 HiÓn thÞ néi dung d÷ liÖu tõ nhiÒu b¶ng 7.1 Mèi liªn kÕt t−¬ng ®−¬ng • Mèi liªn kÕt t−¬ng ®−¬ng ®−îc thÓ hiÖn trong mÖnh ®Ó WHERE. • §Ó liªn kÕt trong mÖnh ®Ó WHERE ph¶i chØ râ tªn cña c¸c column vµ mÖnh ®Ò ®−îc ®Æt t−¬ng ®−¬ng. Vd: emp.deptno =dept.deptno • C¸c column trïng tªn ph¶i ®−îc chØ râ column ®ã n»m ë b¶ng n¶o th«ng qua tªn hoÆc qua alias. Tªn trïng nµy cã thÓ ®Æt trong c¸c mÖnh ®Ò kh¸c nh− SELECT, ORDER BY.. Vd: SELECT DEPT.DEPTNO, ENAME,JOB, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO ORDER BY DEPT.DEPTNO; SELECT A.DEPTNO, A.ENAME, A.JOB, B.DNAME FROM EMP A, DEPT B WHERE A.DEPTNO = B.DEPTNO ORDER BY A.DEPTNO; 7.2 Mèi liªn kÕt kh«ng t−¬ng ®−¬ng • Mèi liªn kÕt t−¬ng ®−¬ng ®−îc thÓ hiÖn trong mÖnh ®Ó WHERE. • §Ó liªn kÕt trong mÖnh ®Ó WHERE ph¶i chØ râ tªn cña c¸c column vµ mÖnh ®Ò ®−îc ®Æt KH¤NG t−¬ng ®−¬ng. Vd: WHERE E.SAL BETWEEN S. LOSAL AND S.HISAL • C¸c column trïng tªn ph¶i ®−îc chØ râ column ®ã n»m ë b¶ng nµo th«ng qua tªn hoÆc qua alias. Tªn trïng nµy cã thÓ ®Æt trong c¸c mÖnh ®Ò kh¸c nh− SELECT, ORDER BY.. VD: SELECT E.ENAME,E.JOB, S.GRADE FROM EMP E, SALGRADE S WHERE E.SAL BETWEEN S. LOSAL AND S.HISAL; §iÒu kiÖn liªn kÕt ®óng lµ sè c¸c b¶ng - 1 = sè c¸c ®iÒu kiÖn liªn kÕt §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 35
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 7.3 Mèi liªn kÕt céng • Mèi liªn kÕt céng tr¶ vÒ c¶ c¸c gi¸ trÞ NULL trong biÓu thøc ®iÒu kiÖn. DÊu (+) ®Ó ë vÕ nµo tÝnh thªm c¸c gi¸ trÞ NULL ë vÕ ®ã. • Mét c©u lÖnh select chØ ®Æt ®−îc 1 mèi liªn kÕt céng, dÊu (+) ®Æt ë bªn ph¶i column liªn kÕt • Trong mÖnh ®Ò WHERE cña mèi liªn kÕt céng kh«ng ®−îc dïng to¸n tö IN hoÆc OR ®Ó nèi c¸c ®iÒu kiÖn liªn kÕt kh¸c. Vd: SELECT E.ENAME, D.DEPTNO, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO (+)=D.DEPTNO AND D.DEPTNO IN (30, 40); ENAME DEPTNO DNAME ---------- ---------- ------------- BLAKE 30 SALES MARTIN 30 SALES ALLEN 30 SALES TURNER 30 SALES JAMES 30 SALES WARD 30 SALES 40 OPERATIONS 7.4 Liªn kÕt cña b¶ng víi chÝnh nã Cã thÓ liªn kÐt b¶ng víi chÝnh nã b»ng c¸ch ®Æt alias. VÝ du: Select e.ename emp_name, e.sal emp_sal, m.ename mgr_name, m.sal mgr_sal from emp e, emp m where e.mgr = m.empno and e.sal
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL JOB --------- CLERK MANAGER PRESIDENT SALESMAN 7.6 Bµi tËp 1. HiÓn thÞ toµn bé tªn nh©n viªn vµ tªn phßng ban lµm viÖc s¾p xÕp theo tªn phßng ban. 2. HiÓn thÞ tªn nh©n viªn, vÞ trÝ ®Þa lý, tªn phßng víi ®iÒu kiÖn l−¬ng >1500. ENAME LOC DNAME ---------- ------------- ----------- KING NEW YORK ACCOUNTING BLAKE CHICAGO SALES CLARK NEW YORK ACCOUNTING JONES DALLAS RESEARCH ALLEN CHICAGO SALES FORD DALLAS RESEARCH SCOTT DALLAS RESEARCH 3. HiÓn thÞ tªn nh©n viªn, nghÒ nghiÖp, l−¬ng vµ møc l−¬ng. ENAME JOB SAL GRADE ---------- --------- ---------- ---------- JAMES CLERK 950 1 SMITH CLERK 800 1 ADAMS CLERK 1100 1 MARTIN SALESMAN 1250 2 WARD SALESMAN 1250 2 MILLER CLERK 1300 2 ALLEN SALESMAN 1600 3 TURNER SALESMAN 1500 3 BLAKE MANAGER 2850 4 CLARK MANAGER 2450 4 JONES MANAGER 2975 4 FORD ANALYST 3000 4 SCOTT ANALYST 3000 4 KING PRESIDENT 5000 5 4. HiÓn thÞ tªn nh©n viªn, nghÒ nghiÖp, l−¬ng vµ møc l−¬ng, víi ®iÒu kiÖn møc l−¬ng = 3. ENAME JOB SAL GRADE ---------- --------- ---------- ---------- ALLEN SALESMAN 1600 3 TURNER SALESMAN 1500 3 5. HiÓn thÞ nh÷ng nh©n viªn t¹i DALLAS ENAME LOC SAL ---------- ------------- ---------- JONES DALLAS 2975 FORD DALLAS 3000 SMITH DALLAS 800 SCOTT DALLAS 3000 ADAMS DALLAS 1100 6. HiÓn thÞ tªn nh©n viªn , nghÒ nghiÖp, l−¬ng, møc l−¬ng, tªn phßng lµm viÖc trõ nh©n viªn cã nghÒ lµ cleck vµ s¾p xÕp theo chiÒu gi¶m. ENAME JOB SAL GRADE DNAME ---------- --------- ---------- ---------- -------------- MARTIN SALESMAN 1250 2 SALES WARD SALESMAN 1250 2 SALES ALLEN SALESMAN 1600 3 SALES TURNER SALESMAN 1500 3 SALES §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 37
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL BLAKE MANAGER 2850 4 SALES CLARK MANAGER 2450 4 ACCOUNTING JONES MANAGER 2975 4 RESEARCH FORD ANALYST 3000 4 RESEARCH SCOTT ANALYST 3000 4 RESEARCH KING PRESIDENT 5000 5 ACCOUNTING 7. HiÓn thÞ chi tiÕt vÒ nh÷ng nh©n viªn kiÕm ®−îc 36000 $ 1 n¨m hoÆc nghÒ lµ cleck. (gåm c¸c tr−êng tªn, nghÒ, thu nhËp, m· phßng, tªn phßng, møc l−¬ng) ENAME JOB ANUAL_SAL DNAME GRADE ---------- --------- ---------- -------------- ---------- JAMES CLERK 11400 SALES 1 SMITH CLERK 9600 RESEARCH 1 ADAMS CLERK 13200 RESEARCH 1 MILLER CLERK 15600 ACCOUNTING 2 FORD ANALYST 36000 RESEARCH 4 SCOTT ANALYST 36000 RESEARCH 4 8. HiÓn thÞ nh÷ng phßng kh«ng cã nh©n viªn nµo lµm viÖc. DEPTNO DNAME LOC ---------- -------------- ------------- 40 OPERATIONS BOSTON 9. HiÓn thÞ m· nh©n viªn, tªn nh©n viªn, m· ng−êi qu¶n lý, tªn ng−êi qu¶n lý EMP_NAME EMP_SAL MGR_NAME MGR_SAL ---------- ---------- ---------- ---------- BLAKE 2850 KING 5000 CLARK 2450 KING 5000 JONES 2975 KING 5000 MARTIN 1250 BLAKE 2850 ALLEN 1600 BLAKE 2850 TURNER 1500 BLAKE 2850 JAMES 950 BLAKE 2850 WARD 1250 BLAKE 2850 FORD 3000 JONES 2975 SMITH 800 FORD 3000 SCOTT 3000 JONES 2975 ADAMS 1100 SCOTT 3000 MILLER 1300 CLARK 2450 10. Nh− c©u 9 hiÓn thÞ thªm th«ng tin vÒ «ng KING. EMP_NAME EMP_SAL MGR_NAME MGR_SAL ---------- ---------- ---------- ---------- KING 5000 BLAKE 2850 KING 5000 CLARK 2450 KING 5000 JONES 2975 KING 5000 MARTIN 1250 BLAKE 2850 ALLEN 1600 BLAKE 2850 TURNER 1500 BLAKE 2850 JAMES 950 BLAKE 2850 WARD 1250 BLAKE 2850 FORD 3000 JONES 2975 SMITH 800 FORD 3000 SCOTT 3000 JONES 2975 ADAMS 1100 SCOTT 3000 MILLER 1300 CLARK 2450 EMP_NAME EMP_SAL MGR_NAME MGR_SAL §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 38
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL ---------- ---------- ---------- ---------- BLAKE 2850 BLAKE 2850 MARTIN 1250 BLAKE 2850 ALLEN 1600 BLAKE 2850 TURNER 1500 BLAKE 2850 JAMES 950 BLAKE 2850 WARD 1250 BLAKE 2850 CLARK 2450 CLARK 2450 MILLER 1300 CLARK 2450 JONES 2975 JONES 2975 FORD 3000 JONES 2975 SMITH 800 JONES 2975 SCOTT 3000 JONES 2975 ADAMS 1100 JONES 2975 13 rows selected. 11. HiÓn thÞ nghÒ nghiÖp ®−îc tuyÓn dông vµo n¨m 1981 vµ kh«ng ®−îc tuyÓn dông vµo n¨m 1994. 12. T×m nh÷ng nh©n viªn gia nhËp c«ng ty tr−íc gi¸m ®èc cña hä. 8 C¸c lÖnh truy vÊn lång nhau 8.1 C©u lÖnh SELECT lång nhau. Trong mÖnh ®Ò WHERE /T×m nh÷ng nh©n viªn lµm cïng nghÒ víi BLAKE/ select ename, job from emp where job = (select job from emp where ename = ‘BLAKE’); ENAME JOB ---------- -------- BLAKE MANAGER CLARK MANAGER JONES MANAGER Trong mÖnh ®Ò HAVING /T×m nh÷ng phßng cã møc l−¬ng trung b×nh lín h¬n phßng 30/ SELECT DEPTNO, AVG(SAL) FROM EMP HAVING AVG(SAL) > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO =30) GROUP BY DEPTNO; DEPTNO AVG(SAL) --------- ---------- 10 2916.66667 20 2175 To¸n tö SOME/ANY/ALL/NOT IN/EXITS NOT IN : Kh«ng thuéc ANY vµ SOME : So s¸nh mét gi¸ trÞ víi mçi gi¸ trÞ trong mét danh s¸ch hay trong kÕt qu¶ tr¶ vÒ cña c©u hái con, ph¶i sau to¸n tö = ALL : So s¸nh mét gi¸ trÞ víi mäi gi¸ trÞ trong danh s¸ch hay trong kÕt qu¶ tr¶ vÒ cña c©u hái con. EXISTS : Tr¶ vÒ TRUE nÕu cã tån t¹i. VÝ dô §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 39