徐玉龙 SQL语句基础查询 第 1 页 2011/6/17
实验！SQL语句基础？二？
实验目的！
1. 熟练掌握连接查询
实验要求！
1. 按以下步骤完成如下操作，并对出现的现象进行解释
2. 将命令和操作过程记录到实验过程记录中
实验步骤和方法！
1. 启动本地Oracle服务器
2. 从命令控制台启动SQL*Plus，以scott用户普通身份登录到数据库服务器
3. 按要求查询数据，可尝试多种查询方案！
实验！SQL语句基础？二？ .......................................................................................... 1 查询每个雇员的姓名，部门编号，部门名称，部门所在地 ....................................................... 1 查询每个雇员的薪水等级为4的雇员编号，姓名，薪水，等级 ............................................... 3 查询部门所在地为BOSTON的雇员姓名，编号，部门编号，部门所在地？分别使用自然连
接、INNER JOIN实现？ ................................................................................................................ 4
对EMP和DEPT表进行右外连接查询 ......................................................................................... 4 查询所有员工应缴的个人所得税，计算方法！起征点为2500，超过部分缴纳5%查询最低薪
水和最高薪水的雇员姓名，薪水，部门编号，薪水不足2500的雇员所缴税额为0？使用union？
.......................................................................................................................................................... 8
查询每个雇员经理的名字 ............................................................................................................. 10
查询部门10和部门20都有的职位名称 ..................................................................................... 11 查询部门20中哪些职位是部门10没有的 ................................................................................. 11
4.
查询每个雇员的姓名，部门编号，部门名称，
部门所在地
SQL> select ename,deptno,dname,loc
2 from scott.emp inner join scott.dept
3 using(deptno);
ENAME DEPTNO DNAME LOC
---------- ---------- -------------- -------------
SMITH 20 RESEARCH DALLAS
ALLEN 30 SALES CHICAGO
WARD 30 SALES CHICAGO
JONES 20 RESEARCH DALLAS
MARTIN 30 SALES CHICAGO
徐玉龙 SQL语句基础查询 第 2 页 2011/6/17
BLAKE 30 SALES CHICAGO CLARK 10 ACCOUNTING NEW YORK SCOTT 20 RESEARCH DALLAS KING 10 ACCOUNTING NEW YORK TURNER 30 SALES CHICAGO ADAMS 20 RESEARCH DALLAS
ENAME DEPTNO DNAME LOC ---------- ---------- -------------- ------------- JAMES 30 SALES CHICAGO FORD 20 RESEARCH DALLAS MILLER 10 ACCOUNTING NEW YORK
已选择14行。
SQL> select ename,deptno,dname,loc
2 from scott.emp NATURAL JOIN scott.dept;
ENAME DEPTNO DNAME LOC ---------- ---------- -------------- ---------- CLARK 10 ACCOUNTING NEW YORK KING 10 ACCOUNTING NEW YORK MILLER 10 ACCOUNTING NEW YORK JONES 20 RESEARCH DALLAS FORD 20 RESEARCH DALLAS ADAMS 20 RESEARCH DALLAS SMITH 20 RESEARCH DALLAS SCOTT 20 RESEARCH DALLAS WARD 30 SALES CHICAGO TURNER 30 SALES CHICAGO ALLEN 30 SALES CHICAGO
ENAME DEPTNO DNAME LOC ---------- ---------- -------------- ---------- JAMES 30 SALES CHICAGO BLAKE 30 SALES CHICAGO MARTIN 30 SALES CHICAGO
已选择14行。
徐玉龙 SQL语句基础查询 第 3 页 2011/6/17
查询每个雇员的薪水等级为4的雇员编号，姓名，薪水，等级
SQL> SELECT empno,ename,sal,grade
2 from scott.emp e INNER JOIN scott.salgrade s
3 on e.sal BETWEEN s.losal AND s.hisal
4 where grade='4';
EMPNO ENAME SAL GRADE ---------- ---------- ---------- ----------
7566 JONES 2975 4
7698 BLAKE 2850 4
7782 CLARK 2450 4
7788 SCOTT 3000 4
7902 FORD 3000 4
SQL> SELECT empno,ename,sal,grade
2 from scott.emp e NATURAL JOIN scott.salgrade s
3 where grade='4';
EMPNO ENAME SAL GRADE ---------- ---------- ---------- ----------
7369 SMITH 800 4
7499 ALLEN 1600 4
7521 WARD 1250 4
7566 JONES 2975 4
7654 MARTIN 1250 4
7698 BLAKE 2850 4
7782 CLARK 2450 4
7788 SCOTT 3000 4
7839 KING 5000 4
7844 TURNER 1500 4
7876 ADAMS 1100 4
EMPNO ENAME SAL GRADE ---------- ---------- ---------- ----------
7900 JAMES 950 4
7902 FORD 3000 4
7934 MILLER 1300 4
徐玉龙 SQL语句基础查询 第 4 页 2011/6/17
已选择14行。
查询部门所在地为BOSTON的雇员姓名，编号，部门编号，部门所在地？分别使用自然连接、INNER JOIN实现？
SQL> select ename,empno,deptno,loc
2 from scott.emp NATURAL JOIN scott.dept
3 WHERE loc='DALLAS';
ENAME EMPNO DEPTNO LOC ---------- ---------- ---------- ------------- JONES 7566 20 DALLAS FORD 7902 20 DALLAS ADAMS 7876 20 DALLAS SMITH 7369 20 DALLAS SCOTT 7788 20 DALLAS
SQL> select ename,empno,deptno,loc
2 from scott.emp INNER JOIN scott.dept
3 using(deptno)
4 WHERE loc='DALLAS';
ENAME EMPNO DEPTNO LOC ---------- ---------- ---------- ------------- JONES 7566 20 DALLAS FORD 7902 20 DALLAS ADAMS 7876 20 DALLAS SMITH 7369 20 DALLAS SCOTT 7788 20 DALLAS
对EMP和DEPT表进行右外连接查询 SQL> SELECT DISTINCT *
2 FROM scott.emp e RIGHT OUTER JOIN scott.dept d
徐玉龙 SQL语句基础查询 第 5 页 2011/6/17
3 USING(deptno);
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- ---------- --------- ---------- -------------- ----------
COMM DNAME LOC
---------- -------------- -------------
10 7782 CLARK MANAGER 7839 09-6月 -81 2450
ACCOUNTING NEW YORK
10 7839 KING PRESIDENT 17-11月-81 5000
ACCOUNTING NEW YORK
10 7934 MILLER CLERK 7782 23-1月 -82 1300
ACCOUNTING NEW YORK
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- ---------- --------- ---------- -------------- ----------
COMM DNAME LOC
---------- -------------- -------------
20 7566 JONES MANAGER 7839 02-4月 -81 2975
RESEARCH DALLAS
20 7902 FORD ANALYST 7566 03-12月-81 3000
RESEARCH DALLAS
20 7876 ADAMS CLERK 7788 23-5月 -87 1100
RESEARCH DALLAS
DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL
---------- ---------- ---------- --------- ---------- -------------- ----------
COMM DNAME LOC
---------- -------------- -------------
20 7369 SMITH CLERK 7902 17-12月-80
徐玉龙 SQL语句基础查询 第 6 页 2011/6/17
800
RESEARCH DALLAS
20 7788 SCOTT ANALYST 7566 19-4月 -87 3000
RESEARCH DALLAS
30 7521 WARD SALESMAN 7698 22-2月 -81 1250
500 SALES CHICAGO
DEPTNO EMPNO ENAME JOB MGR HIREDATE
SAL
---------- ---------- ---------- --------- ---------- -------------- ----------
COMM DNAME LOC
---------- -------------- -------------
30 7844 TURNER SALESMAN 7698 08-9月 -81 1500
0 SALES CHICAGO
30 7499 ALLEN SALESMAN 7698 20-2月 -81 1600
300 SALES CHICAGO
30 7900 JAMES CLERK 7698 03-12月-81 950
SALES CHICAGO
DEPTNO EMPNO ENAME JOB MGR HIREDATE
SAL
---------- ---------- ---------- --------- ---------- -------------- ----------
COMM DNAME LOC
---------- -------------- -------------
30 7698 BLAKE MANAGER 7839 01-5月 -81 2850
SALES CHICAGO
30 7654 MARTIN SALESMAN 7698 28-9月 -81 1250
1400 SALES CHICAGO
40
徐玉龙 SQL语句基础查询 第 7 页 2011/6/17
OPERATIONS BOSTON
已选择15行。
SQL> SELECT DISTINCT ename,sal,job,deptno
2 FROM scott.emp e RIGHT OUTER JOIN scott.dept d
3 USING(deptno);
ENAME SAL JOB DEPTNO LOC ---------- ---------- --------- ---------- ------------- KING 5000 PRESIDENT 10 NEW YORK
40 BOSTON MARTIN 1250 SALESMAN 30 CHICAGO FORD 3000 ANALYST 20 DALLAS ADAMS 1100 CLERK 20 DALLAS SMITH 800 CLERK 20 DALLAS JONES 2975 MANAGER 20 DALLAS JAMES 950 CLERK 30 CHICAGO BLAKE 2850 MANAGER 30 CHICAGO SCOTT 3000 ANALYST 20 DALLAS ALLEN 1600 SALESMAN 30 CHICAGO
ENAME SAL JOB DEPTNO LOC ---------- ---------- --------- ---------- ------------- WARD 1250 SALESMAN 30 CHICAGO MILLER 1300 CLERK 10 NEW YORK CLARK 2450 MANAGER 10 NEW YORK TURNER 1500 SALESMAN 30 CHICAGO
已选择15行。
徐玉龙 SQL语句基础查询 第 8 页 2011/6/17
查询所有员工应缴的个人所得税，计算方
法！起征点为2500，超过部分缴纳5%查询
最低薪水和最高薪水的雇员姓名，薪水，部
门编号，薪水不足2500的雇员所缴税额为0
？使用union？
SQL> SELECT ename,sal,deptno,(sal-2500)*0.06 as individualincomtas FROM emp WHE
RE sal=(select max(sal) from emp)
2 UNION select ename,sal,deptno, 0 FROM emp where sal=(select min(sal) from e
mp );
ENAME SAL DEPTNO INDIVIDUALINCOMTAS ---------- ---------- ---------- ------------------ KING 5000 10 150 SMITH 800 20 0
SQL> SELECT ename,sal,deptno,(sal-2500)*0.06 as individualincomtas FROM emp WHE
RE sal>2500
2 UNION select ename,sal,deptno, 0 FROM emp WHERE SAL<2500
3 UNION select ename,sal,deptno, 0 FROM emp where sal=(select min(sal) from e
mp )
4 union SELECT ename,sal,deptno,(sal-2500)*0.06 as individualincomtas FROM em
p WHERE sal=(select max(sal) from emp);
ENAME SAL DEPTNO INDIVIDUALINCOMTAS ---------- ---------- ---------- ------------------ ADAMS 1100 20 0 ALLEN 1600 30 0 BLAKE 2850 30 21 CLARK 2450 10 0 FORD 3000 20 30 JAMES 950 30 0 JONES 2975 20 28.5 KING 5000 10 150 MARTIN 1250 30 0 MILLER 1300 10 0
徐玉龙 SQL语句基础查询 第 9 页 2011/6/17
SCOTT 3000 20 30
ENAME SAL DEPTNO INDIVIDUALINCOMTAS ---------- ---------- ---------- ------------------ SMITH 800 20 0 TURNER 1500 30 0 WARD 1250 30 0
已选择14行。
SQL> SELECT ename,0,0,(sal-2500)*0.06 as individualincomtas FROM emp WHERE sal>
2500
2 UNION select ename,0,0, 0 FROM emp WHERE SAL<2500
3 UNION select ename,sal,deptno, 0 FROM emp where sal=(select min(sal) from e
mp )
4 union SELECT ename,sal,deptno,(sal-2500)*0.06 as individualincomtas FROM em
p WHERE sal=(select max(sal) from emp);
ENAME 0 0 INDIVIDUALINCOMTAS ---------- ---------- ---------- ------------------ ADAMS 0 0 0 ALLEN 0 0 0 BLAKE 0 0 21 CLARK 0 0 0 FORD 0 0 30 JAMES 0 0 0 JONES 0 0 28.5 KING 0 0 150 KING 5000 10 150 MARTIN 0 0 0 MILLER 0 0 0
ENAME 0 0 INDIVIDUALINCOMTAS ---------- ---------- ---------- ------------------ SCOTT 0 0 30 SMITH 0 0 0 SMITH 800 20 0 TURNER 0 0 0 WARD 0 0 0
已选择16行。
徐玉龙 SQL语句基础查询 第 10 页 2011/6/17
查询每个雇员经理的名字
SQL> select ygb.ename as empnm,sjb.ename as mgrnm
2 from emp ygb,emp sjb
3 where ygb.mgr=sjb.empno;
EMPNM MGRNM
---------- ----------
FORD JONES
SCOTT JONES
TURNER BLAKE
ALLEN BLAKE
WARD BLAKE
JAMES BLAKE
MARTIN BLAKE
MILLER CLARK
ADAMS SCOTT
BLAKE KING
JONES KING
EMPNM MGRNM
---------- ----------
CLARK KING
SMITH FORD
已选择13行。
SQL> select e.empno,e.ename,e.mgr,ee.ename as mgrname from emp e
2 left outer join emp ee
3 on ee.empno=e.mgr;
EMPNO ENAME MGR MGRNAME ---------- ---------- ---------- ----------
7902 FORD 7566 JONES
7788 SCOTT 7566 JONES
7900 JAMES 7698 BLAKE
7844 TURNER 7698 BLAKE
7654 MARTIN 7698 BLAKE
7521 WARD 7698 BLAKE
7499 ALLEN 7698 BLAKE
7934 MILLER 7782 CLARK
7876 ADAMS 7788 SCOTT
7782 CLARK 7839 KING
7698 BLAKE 7839 KING