DOC

SQL2

By Helen Rodriguez,2014-06-17 14:23
12 views 0
SQL2

徐玉龙 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

    EMPDEPT表进行右外连接查询 ......................................................................................... 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

    EMPDEPT表进行右外连接查询 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

Report this document

For any questions or suggestions please email
cust-service@docsford.com