--編寫多表查詢語句的一般過程
--（1）、分析句子要涉及到哪些表
--（2）、對應的表中要查詢哪些關聯欄位
--（3）、確定連線條件或篩選條件
--（4）、寫成完整的SQL查詢語句
--1、查詢出每一位僱員的姓名、職位、以及領導的姓名。
SELECT e.ename 僱傭姓名,e.job 職位,m.ename 領導姓名 FROM EMP e,EMP m WHERE e.mgr = m.empno;
--結果
僱傭姓名 職位 領導姓名
---------- --------- ----------
FORD ANALYST JONES
SCOTT ANALYST JONES
TURNER SALESMAN BLAKE
ALLEN SALESMAN BLAKE
WARD SALESMAN BLAKE
JAMES CLERK BLAKE
MARTIN SALESMAN BLAKE
MILLER CLERK CLARK
ADAMS CLERK SCOTT
BLAKE MANAGER KING
JONES MANAGER KING
CLARK MANAGER KING
SMITH CLERK FORD
13 rows selected
--2、查詢每個僱傭的僱傭編號，姓名、基本工資、職位、領導的姓名、部門名稱及位置
select e.empno 編號,e.ename 姓名,e.sal 基本工資,e.job 職位,m.ename 領導姓名, d.dname 部門名稱, d.loc 所在位置 from emp e LEFT OUTER JOIN emp m ON e.mgr = m.empno, dept d where d.deptno = e.deptno;
--結果
編號 姓名 工資 職位 領導姓名 部門名稱 所在位置
----- ---------- --------- --------- ---------- -------------- -------------
7369 SMITH 800.00 CLERK FORD RESEARCH DALLAS
7499 ALLEN 1600.00 SALESMAN BLAKE SALES CHICAGO
7521 WARD 1250.00 SALESMAN BLAKE SALES CHICAGO
7566 JONES 2975.00 MANAGER KING ACCOUNTING NEW YORK
7654 MARTIN 1250.00 SALESMAN BLAKE SALES CHICAGO
7698 BLAKE 2850.00 MANAGER KING ACCOUNTING NEW YORK
7782 CLARK 2450.00 MANAGER KING ACCOUNTING NEW YORK
7788 SCOTT 3000.00 ANALYST JONES RESEARCH DALLAS
7844 TURNER 1500.00 SALESMAN BLAKE SALES CHICAGO
7876 ADAMS 1100.00 CLERK SCOTT RESEARCH DALLAS
7900 JAMES 950.00 CLERK BLAKE SALES CHICAGO
7902 FORD 3000.00 ANALYST JONES RESEARCH DALLAS
7934 MILLER 1300.00 CLERK CLARK ACCOUNTING NEW YORK
7839 KING 5000 PRESIDENT (null) ACCOUNTING NEW YORK
14 rows selected
--要求查詢出每一個僱員的編號、姓名、工資、部門名稱、工資在所在公司的工資等級
SELECT e.empno,e.ename,e.sal,d.dname,sg.grade FROM emp e,dept d,salgrade sg WHERE e.deptno = d.deptno AND sg.hisal >=e.sal AND sg.losal <=e.sal;
--結果
EMPNO ENAME SAL DNAME GRADE
----- ---------- --------- -------------- ----------
7369 SMITH 800.00 RESEARCH 1
7900 JAMES 950.00 SALES 1
7876 ADAMS 1100.00 RESEARCH 1
7654 MARTIN 1250.00 SALES 2
7521 WARD 1250.00 SALES 2
7934 MILLER 1300.00 ACCOUNTING 2
7844 TURNER 1500.00 SALES 3
7499 ALLEN 1600.00 SALES 3
7782 CLARK 2450.00 ACCOUNTING 4
7698 BLAKE 2850.00 SALES 4
7566 JONES 2975.00 RESEARCH 4
7902 FORD 3000.00 RESEARCH 4
7788 SCOTT 3000.00 RESEARCH 4
7839 KING 5000.00 ACCOUNTING 5
14 rows selected
--或者
SELECT e.empno,e.ename,e.sal,d.dname,sg.grade FROM emp e,dept d,salgrade sg WHERE e.deptno = d.deptno AND e.sal BETWEEN sg.losal AND sg.hisal;
--結果
EMPNO ENAME SAL DNAME GRADE
----- ---------- --------- -------------- ----------
7839 KING 5000.00 ACCOUNTING 5
7902 FORD 3000.00 RESEARCH 4
7788 SCOTT 3000.00 RESEARCH 4
7566 JONES 2975.00 RESEARCH 4
7698 BLAKE 2850.00 SALES 4
7782 CLARK 2450.00 ACCOUNTING 4
7499 ALLEN 1600.00 SALES 3
7844 TURNER 1500.00 SALES 3
7934 MILLER 1300.00 ACCOUNTING 2
7521 WARD 1250.00 SALES 2
7654 MARTIN 1250.00 SALES 2
7876 ADAMS 1100.00 RESEARCH 1
7900 JAMES 950.00 SALES 1
7369 SMITH 800.00 RESEARCH 1
--還或者
SELECT e.empno,e.ename,e.sal,d.dname,sg.grade,DECODE(sg.grade,'1'，'第一等級工資','2'，'第二等級工資','3'，'第三等級工資','4'，'第四等級工資','5'，'第五等級工資') 等級 FROM emp e,dept d,salgrade sg WHERE e.deptno = d.deptno AND e.sal BETWEEN sg.losal AND sg.hisal;
--結果
EMPNO ENAME SAL DNAME GRADE 等級
----- ---------- --------- -------------- ---------- ------------
7839 KING 5000.00 ACCOUNTING 5 第五等級工資
7902 FORD 3000.00 RESEARCH 4 第四等級工資
7788 SCOTT 3000.00 RESEARCH 4 第四等級工資
7566 JONES 2975.00 RESEARCH 4 第四等級工資
7698 BLAKE 2850.00 SALES 4 第四等級工資
7782 CLARK 2450.00 ACCOUNTING 4 第四等級工資
7499 ALLEN 1600.00 SALES 3 第三等級工資
7844 TURNER 1500.00 SALES 3 第三等級工資
7934 MILLER 1300.00 ACCOUNTING 2 第二等級工資
7521 WARD 1250.00 SALES 2 第二等級工資
7654 MARTIN 1250.00 SALES 2 第二等級工資
7876 ADAMS 1100.00 RESEARCH 1 第一等級工資
7900 JAMES 950.00 SALES 1 第一等級工資
7369 SMITH 800.00 RESEARCH 1 第一等級工資
14 rows selected
--左右連線
--當(+)在連線條件的左邊的時候，表示的是右連線
--當(+)在連線條件的右邊的時候，表示的是左連線
--3、查詢每個僱員的姓名和領導的姓名
SELECT e.ename 僱員姓名,m.ename 領導姓名 FROM emp e,emp m WHERE m.empno(+) = e.mgr;
--結果
僱員姓名 領導姓名
---------- ----------
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
KING
14 rows selected
--4、統計出領取佣金和不領取佣金的僱員人數和平均工資
SELECT comm,COUNT(empno),AVG(sal) FROM emp GROUP BY comm;--（此語句不完善，那位看了之後又什麼想法的話歡迎大家回覆）
--結果
COMM COUNT(EMPNO) AVG(SAL)
--------- ------------ ----------
10 2342.5
1400.00 1 1250
500.00 1 1250
300.00 1 1600
0.00 1 1500
--5、按照職位分組，求出每個職位的最高工資、最低工資以及平均工資
SELECT job ,COUNT(empno),MAX(sal),MIN(sal),AVG(sal) FROM emp GROUP BY job;
--結果
JOB COUNT(EMPNO) MAX(SAL) MIN(SAL) AVG(SAL)
--------- ------------ ---------- ---------- ----------
CLERK 4 1300 800 1037.5
SALESMAN 4 1600 1250 1400
PRESIDENT 1 5000 5000 5000
MANAGER 3 2975 2450 2758.33333
ANALYST 2 3000 3000 3000
--6、統計平均工資最高和最低
SELECT MAX(AVG(sal)),MIN(AVG(sal)) FROM EMP GROUP BY job;
--結果
MAX(AVG(SAL)) MIN(AVG(SAL))
------------- -------------
5000 1037.5
--7、查詢出每個部門的名稱、部門人數、平均工資
SELECT d.dname,COUNT(e.empno),NVL(AVG(e.sal),0) FROM emp e,dept d WHERE d.deptno = e.deptno(+) GROUP BY d.dname;
--結果
DNAME COUNT(E.EMPNO) NVL(AVG(E.SAL),0)
-------------- -------------- -----------------
ACCOUNTING 3 2916.66666666667
OPERATIONS 0 0
RESEARCH 5 2175
SALES 6 1566.66666666667
--8、查詢出每個部門的名稱、位置、部門人數、平均工資(這是一個多欄位分組查詢)
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),NVL(AVG(sal),0) FROM emp e, dept d WHERE d.deptno = e.deptno(+) GROUP BY d.deptno,d.dname,d.loc ;
DEPTNO DNAME LOC COUNT(E.EMPNO) NVL(AVG(SAL),0)
------ -------------- ------------- -------------- ---------------
20 RESEARCH DALLAS 5 2175
40 OPERATIONS BOSTON 0 0
10 ACCOUNTING NEW YORK 3 2916.6666666666
30 SALES CHICAGO 6 1566.6666666666
--9、統計平均工資大於2000的部門的詳細資訊
SELECT d.*,AVG(e.sal) FROM emp e,dept d WHERE d.deptno = e.deptno GROUP BY d.deptno,d.dname,d.loc HAVING AVG(e.sal)>2000;
--結果
DEPTNO DNAME LOC AVG(E.SAL)
------ -------------- ------------- ----------
20 RESEARCH DALLAS 2175
10 ACCOUNTING NEW YORK 2916.66666
--10、顯示非銷售人員工資名稱及從事同一工作僱員的月工資的總和，並且要滿足從事同一工作的僱員的月工資合計大於5000，
--給出的結果按月工資的合計升序排序：
SELECT job,SUM(sal) FROM emp GROUP BY job HAVING SUM(sal)>5000 ORDER BY SUM(sal);
--結果
JOB SUM(SAL)
--------- ----------
SALESMAN 5600
ANALYST 6000
MANAGER 8275
--11、計算出工資比SMITH的要高的員工詳細資訊
SELECT * FROM EMP WHERE sal > (SELECT sal FROM EMP WHERE ename='SMITH');
--結果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 20-二月-81 1600.00 300.00 30
7521 WARD SALESMAN 7698 22-二月-81 1250.00 500.00 30
7566 JONES MANAGER 7839 02-四月-81 2975.00 20
7654 MARTIN SALESMAN 7698 28-九月-81 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30
7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20
7839 KING PRESIDENT 17-十一月-81 5000.00 10
7844 TURNER SALESMAN 7698 08-九月-81 1500.00 0.00 30
7876 ADAMS CLERK 7788 23-五月-87 1100.00 20
7900 JAMES CLERK 7698 03-十二月-81 950.00 30
7902 FORD ANALYST 7566 03-十二月-81 3000.00 20
7934 MILLER CLERK 7782 23-一月-82 1300.00 10
13 rows selected
--12、查詢出工資高於公司平均工資的員工詳細資訊
SELECT * FROM EMP WHERE sal >(SELECT AVG(sal) FROM EMP);
--結果
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7566 JONES MANAGER 7839 02-四月-81 2975.00 20
7698 BLAKE MANAGER 7839 01-五月-81 2850.00 30
7782 CLARK MANAGER 7839 09-六月-81 2450.00 10
7788 SCOTT ANALYST 7566 19-四月-87 3000.00 20
7839 KING PRESIDENT 17-十一月-81 5000.00 10
7902 FORD ANALYST 7566 03-十二月-81 3000.00 20
6 rows selected