DOC

Oracle

By Harry Gardner,2014-05-01 01:38
7 views 0
Oracle

    广东技术师范学院

    计算机学院上机实验报告

    

     实验2 Oracle数据库管理工具 上机时间, 2010.9.16 上机地点,工业中心 报告时间,2010-9-18 专业班级学号, 计算机科学与技术,师范! 08计师,2 姓名,

     成绩

    实验 ,一! 项目名称,SQL语言;二? 一、 实验目的

    1、熟练掌握SQL语言的自身连接、结合查询和子查询。

    2、进一步掌握SQL*PLUS环境设置。

    3、初步了解ORACLE的权限管理。

    二、 实验要求

     通过完成实验内容,理解视图的作用。

    三、 实验内容

     1、对SCOTT用户授权允许建立视图。

    2、对SCOTT用户下的EMP表建立三个视图,ACCOUNTRESEARCHSALES 3、完成纸质参考材料《SQL基础》中的例2.20(2.37

    四、实验过程

    1、启动服务

    2、登陆SQL*PLUS,并对SCOTT解锁

     conn / as sysdba

    alter user scott account unlock identified by tiger;

    3、对SCOTT授权创建视图并且连接它。

     grant create any view to scott;

    conn scott/tiger

    4、对EMP表创建视图

     Create view account as

     select ename,sal,job from emp where deptno=10;

    Create view research as

     select ename,sal,job from emp where deptno=20;

    Create view sales as

    select ename,sal,job from emp where deptno=30;

    5、连接system对视图授权允许查询

     conn system/口令

    GRANT select on scott.account to scott;

    GRANT select on scott.research to scott;

    GRANT select on scott.sales to scott;

    6、连接scott再进行例题的查询

     conn scott/口令

五、查询例题结果

     20. SELECT worker.ename"员工名",manager.ename"上司名"

    FROM emp worker,emp manager

     WHERE worker.mgr=manager.empno;

    员工名 上司名

    ---------- ----------

    SMITH FORD

    ALLEN BLAKE

    WARD BLAKE

    JONES KING

    MARTIN BLAKE

    BLAKE KING

    CLARK KING

    SCOTT JONES

    TURNER BLAKE

    ADAMS SCOTT

    JAMES BLAKE

    员工名 上司名

    ---------- ----------

    FORD JONES

    MILLER CLARK

    已选择13行。

     21SELECT ename,sal FROM ACCOUNT WHERE sal>2000

     UNION

     SELECT ename,sal FROM RESEARCH WHERE sal>2000

     UNION

     SELECT ename,sal FROM SALES WHERE sal>2000;

    ENAME SAL

    ---------- ----------

    BLAKE 2850

    CLARK 2450

    FORD 3000

    JONES 2975

    KING 5000

    SCOTT 3000

    已选择6行。

     22. SELECT DISTINCT job FROM ACCOUNT

     UNION

     SELECT DISTINCT job FROM RESEARCH

     UNION

     SELECT DISTINCT job FROM SALES;

    JOB

    ---------

    ANALYST

    CLERK

    MANAGER

    PRESIDENT

    SALESMAN

     23. SELECT *FROM ACCOUNT

     UNION

     SELECT *FROM RESEARCH WHERE job='CLERK';

    ENAME SAL JOB

    ---------- ---------- ---------

    ADAMS 1100 CLERK

    CLARK 2450 MANAGER

    KING 5000 PRESIDENT

    MILLER 1300 CLERK

    SMITH 800 CLERK

     24. SELECT ename,sal FROM ACCOUNT WHERE sal>2000

     UNION ALL

     SELECT ename,sal FROM RESEARCH WHERE sal>2000

     UNION ALL

     SELECT ename,sal FROM SALES WHERE sal>2000;

    ENAME SAL

    ---------- ----------

    CLARK 2450

    KING 5000

    JONES 2975

    SCOTT 3000

    FORD 3000

    BLAKE 2850

    已选择6行。

     25. SELECT DISTINCT job FROM ACCOUNT

     UNION ALL

     SELECT DISTINCT job FROM RESEARCH

     UNION ALL

     SELECT DISTINCT job FROM SALES;

    JOB

    ---------

    CLERK

    PRESIDENT

    MANAGER

    CLERK

    MANAGER

    ANALYST

    SALESMAN

    CLERK

    MANAGER

    已选择9行。

     26. SELECT job FROM account

     INTERSECT

     SELECT job FROM research

     INTERSECT

     SELECT job FROM SALES;

    JOB

    ---------

    CLERK

    MANAGER

     27. SELECT job FROM ACCOUNT

     MINUS

     SELECT job FROM SALES;

    JOB

    ---------

    PRESIDENT

     28SELECT *FROM RESEARCH

     MINUS

     SELECT *FROM ACCOUNT

     MINUS

     SELECT *FROM SALES;

    ENAME SAL JOB

    ---------- ---------- -------

    ADAMS 1100 CLERK

    FORD 3000 ANALYST

    JONES 2975 MANAGER

    SCOTT 3000 ANALYST

    SMITH 800 CLERK

     29. SELECT sal FROM emp WHERE ename='JONES';

    ENAME SAL JOB

    ---------- ---------- ---------

    ADAMS 1100 CLERK

    FORD 3000 ANALYST

    JONES 2975 MANAGER

    SCOTT 3000 ANALYST

    SMITH 800 CLERK

     30. SELECT deptno,dname FROM dept

     WHERE deptno IN(SELECT deptno FROM emp WHERE sal>=3000);

     DEPTNO DNAME

    -------- -----------

     10 ACCOUNTING

     20 RESEARCH

     31. SELECT ename,sal,job FROM emp

     WHERE deptno NOT IN(SELECT deptno FROM emp WHERE sal<1000);

    ENAME SAL JOB

    ---------- ---------- ---------

    CLARK 2450 MANAGER

    KING 5000 PRESIDENT

    MILLER 1300 CLERK

     32. SELECT ename,sal,job FROM emp

     WHERE deptno!=ALL(SELECT deptno FROM emp WHERE sal<1000);

    ENAME SAL JOB

    ---------- ---------- ---------

    CLARK 2450 MANAGER

    KING 5000 PRESIDENT

    MILLER 1300 CLERK

     33. SELECT *FROM emp WHERE (TO_CHAR(hiredate,'yy'),deptno) IN (SELECT

     TO_CHAR(hiredate,'yy'),deptno FROM emp WHERE sal>=3000);

     EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------

    7876 ADAMS CLERK 7788 23-5 -87 1100 20 7788 SCOTT ANALYST 7566 19-4 -87 3000 20 7839 KING PRESIDENT 17-11 -81 5000 10 782 CLARK MANAGER 7839 09-6 -81 2450 10 7902 FORD ANALYST 7566 03-12 -81 3000 20 7566 JONES MANAGER 7839 02-4 -81 2975 20

已选择6行。

    34. SELECT e.deptno,a.average,e.ename,e.sal 工资,e.sal-a.average 差值

     FROM emp e,(SELECT deptno,avg(sal)average FROM emp

     GROUP BY deptno)a

     WHERE e.deptno=a.deptno

     ORDER BY 1,2;

     DEPTNO AVERAGE ENAME 工资 差值

    ---------- ---------- ---------- ---------- ----------

     10 2916.66667 CLARK 2450 -466.66667

     10 2916.66667 KING 5000 2083.33333

     10 2916.66667 MILLER 1300 -1616.6667

     20 2175 JONES 2975 800

     20 2175 FORD 3000 825

     20 2175 ADAMS 1100 -1075

     20 2175 SMITH 800 -1375

     20 2175 SCOTT 3000 825

     30 1566.66667 WARD 1250 -316.66667

     30 1566.66667 TURNER 1500 -66.666667

     30 1566.66667 ALLEN 1600 33.3333333

     DEPTNO AVERAGE ENAME 工资 差值

    ---------- ---------- ---------- ---------- ----------

     30 1566.66667 JAMES 950 -616.66667

     30 1566.66667 BLAKE 2850 1283.33333

     30 1566.66667 MARTIN 1250 -316.66667

    已选择14行。

    35 . SELECT deptno,AVG(sal) FROM emp GROUP BY deptno

     HAVING avg(sal)>(SELECT avg(sal)FROM emp WHERE deptno=30);

     DEPTNO AVG(SAL)

    ---------- ----------

     20 2175

     10 2916.66667

    36. SELECT job,AVG(sal) FROM emp GROUP BY job

     HAVING AVG(sal)=(SELECT MIN (AVG(sal))FROM emp GROUP BY job);

    JOB AVG(SAL)

    --------- ----------

    CLERK 1037.5

    37. SELECT ename,job,sal FROM emp,dept WHERE loc='NEW YORK' AND

     emp.deptno=dept.deptno AND sal>(SELECT sal FROM emp WHERE

     ename='SCOTT');

ENAME JOB SAL

---------- --------- ----------

KING PRESIDENT 5000

Report this document

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