a.deptno=b.deptno and b.deptno=20;
显示获得补助的所有雇员名、补助及所在部门
select ename,comm,deptno from emp where comm is not null;
显示在DALLAS工作的所有雇员名、雇员工资及所在部门名
select a.ename,a.sal,b.dname from emp a,dept b where a.deptno=b.deptno and b.loc='DALLAS';
显示雇员SCOTT的管理者名
select a.ename from emp a,emp b where a.empno=b.mgr and b.ename='SCOTT';
使用子查询完成：
显示BLAKE同部门的所有雇员，但不显示BLAKE。 SELECT * FROM emp WHERE deptno=
(SELECT deptno FROM emp WHERE ename=?BLAKE?) AND ename<>?BLAKE?
显示超过平均工资的所有雇员名，工资及部门号。
SELECT ename,sal,deptno FROM emp WHERE sal> (SELECT avg(sal) FROM emp);
显示超过部门平均工资的所有雇员、工资及部门号。 SELECT a.ename,a.sal,a.deptno FROM emp a,
(SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno) b WHERE a.deptno=b.deptno AND a.sal>b.avgsal;
显示高于CLERK岗位所有雇员工资的所有雇员名、工资及岗位。 SELECT ename,sal,job FROM emp WHERE sal>ALL (SELECT sal FROM emp WHERE job=?CLERK?);
显示工资、补助与SCOTT完全一致的所有雇员名、工资及补助。
SELECT ename,sal,comm FROM emp WHERE (sal,nvl(comm,-1))= (SELECT sal,nvl(comm,-1) FROM emp WHERE ename=?SCOTT?);
使用集合操作符
执行如下语句建立视图
CREATE VIEW dept_20 AS SELECT * FROM emp WHERE deptno=20; CREATE VIEW job_clerk AS SELECT * FROM emp WHERE job=?CLERK?;
使用视图dept_20和job_clerk取得部门20或岗位为CLERK的所有雇员名、工资（显示重复值）。
SELECT ename,sal FROM dept_20 UNION ALL
第 16 页 共 27 页
SELECT ename,sal FROM job_clerk；
使用视图dept_20和job_clerk取得部门20并且岗位为CLERK的所有雇员名和工资。 SELECT ename,sal FROM dept_20 INTERSECT
SELEC ename,sal FROM job_clerk;
实验五 SQL查询
根据Oracle数据库scott模式下的emp表和dept表，完成以下操作。
向emp表中插入一条记录，员工号为1357，员工名为oracle，工资为2050，部门号为20，入职日期为2002年5月10日。
insert into emp(empno,ename,sal,deptno,hiredate)
values(1357,?oracle?,2050,20,TO_DATE(?2002-5-10?,?YYYY-MM-DD?);
查询所有员工及其所在部门的信息。
select ename,empno,sal,dept.deptno,dname,loc from emp left join dept on emp.deptno=dept.depno
查询所有部门及其员工的信息。
select ename,empno,sal,dept.deptno,dname,loc
from emp right join dept on emp.deptno=dept.deptno
查询10号部门员工及其领导的信息，查询所有员工及其领导的信息。 select
worker.ename,worker.empno,worker.sal,manager.ename,manager.empno,manager.sal from emp worker,emp manager where worker.mgr=manager.empno and worker.deptno=10
select
worker.ename,worker.empno,worker.sal,manager.ename,manager.empno,manager.sal from emp worker left join emp manager on worker.mgr=manager.empno
查询各个部门的人数及平均工资。
select count(*) ,avg(sal) from emp group by deptno
查询工资为某个部门平均工资的员工的信息。
select * from emp where sal = any(select avg(sal) from emp group by deptno)
查询工资高于本部门平均工资的员工的信息。
select * from emp e where e.sal > (select avg(sal) from emp where deptno=e.deptno)
查询工资高于本部门平均工资的员工的信息及其部门的平均工资。
第 17 页 共 27 页
select e.ename,e.empno,e.sal,e.deptno,s.avgsal from emp e,(select deptno ,avg(sal) avgsal from emp group by deptno) s where e.sal>s.avgsal and e.deptno=s.deptno
查询工资高于20号部门某个员工工资的员工信息。
select * from emp sal>any(select sal from emp where deptno=20)
统计各个工种的员工人数与平均工资。
Select job, count(*),avg(sal) from emp group by job
统计每个部门中各工种的人数与平均工资。
select deptno,job,count(*), avg(sal) from emp group by deptno,job
查询工资、奖金与10号部门某员工工资、奖金都相同的员工信息。
select * from emp where (sal, nvl(comm.,0)) in (select sal,nvl(comm,0) from emp where deptno=10)
查询部门人数 大于5的部门的员工信息。
select * from emp where deptno in (select deptno from emp group by deptno having count(*)>5)
查询所有员工工资都大于2000的部门的信息。
select * from dept where deptno not in (select deptno from emp where sal<2000)
查询所有员工工资都大于2000的部门的信息及其员工信息。
select dept.deptno,dname,loc,ename,empno,sal from emp ,dept
where emp.deptno=dept.deptno and deptno not in (select deptno from emp where sal<2000)
查询所有员工工资都在2000~3000之间的部门信息。 select * from dept where deptno not in
(select deptno from emp where sal not between 2000 and 3000)
查询有工资在2000~3000之间的员工所在部门的员工信息。
select * from emp where deptno in (select distinct deptno from emp where sal between 2000 and 3000)
查询每个员工的领导所在部门的信息。
select detp.deptno,dname,loc from dept, emp worker,emp manager
where dept.deptno=manager.deptno and worker.mgr=manager.empno
向emp表中插入一条记录，其员工名为FAN，员工号为8000，其他信息与SMITH员工的信息相同。
第 18 页 共 27 页
insert into emp(ename,empno,mgr,job,hirdate,sal, comm.,deptno)
select ?FAN?,8000,mgr,job,hirdate,sal,comm.,deptno from emp where ename=?SMITH?
将各部门员工的工资修改为该员工所在部门平均工资加1000。
update emp e set sal=1000+(select avg(sal) from emp where deptno=e.deptno)
实验六 Oracle安全管理 用户登录、查询、更新、收授权限
建立两数据库用户：用户ALLEN（口令：ALLEN）。 C:\\>sqlplus system/manager
sql>create user allen identified by allen； sql>create user clark identified by clark； 授予和收回用户的系统权限
以SYSTEM 用户登陆，给 ALLEN 用户授予 CREATE SESSION (带有WITH ADMIN OPTION)
sql>grant create session to allen with admin option;
以ALLEN 用户登陆，将系统权限 CREATE SESSION 授予CLARK用户 sql> conn allen/allen
sql>grant create session to clark;
以SYSTEM 用户登陆，收回ALLEN 的CREATE SESSION 权限 sql>conn system/manager
sql>revoke create session from allen;
检查ALLEN 和CLARK用户是否能够连接到数据库，为什么？
收回用户的系统权限时，因为系统权限不会级联收回，所以ALLEN用户将不能连接到数据库，而CLARK用户仍然可以连接到数据库。 授予和收回用户的对象权限
以SCOTT 用户登陆，将查询DEPT 表的权限授予用户 ALLEN(带有WITH GRANT OPTION)，
SQL>conn scott/tiger
SQL>GRANT SELECT ON dept TO allen WITH GEANT OPTION; 将更新表DEPT 的LOC 列权限授予用户ALLEN SQL>GRANT UPDATE(loc) ON dept TO allen;
以ALLEN 用户登陆，查询 SCOTT.DEPT表中部门 10的信息 ，更新部门 10的部门位置为DALLAS，更新部门10的部门名为SALES
第 19 页 共 27 页
SQL>conn allen/allen
SQL>SELECT * FROM scott.dept WHERE deptno = 10;
SQL>UPDATE scott.dept SET loc=?DALLAS? WHERE deptno =10; SQL>UPDATE scott.dept SET dname=?SALES? WHERE deptno =10;
以ALLEN 用户登陆，将查询 DEPT 表的权限授予用户CLARK SQL>conn allen/allen
SQL>GRANT SELECT ON scott.dept TO clark;
以CLARK用户登陆，查询 SCOTT.DEPT 表中部门 10的信息。 SQL>conn clark/clark
SQL>SELECT * FROM scott.dept WHERE deptno=10;
以SYSTEM用户登陆，收回 ALLEN 用户查询 SCOTT.DEPT 表的权限 SQL>conn system/manager
SQL>REVOKE SELECT ON scott.dept FROM ALLEN;
以CLARK用户登陆，查村 SCOTT.DEPT 表，是否可以成功？为什么？ 因为对象权限会被级联收回，所以clark用户也不能查询scott.dept表。 建立两个角色：DEF_ROLE（不验证）和PRV_ROLE（口令：PRV） SQL>CREATE ROLE dept_role NOT IDENTIFIED； SQL>CREATE ROLE prv_role IDENTIFIED BY prv; 授予和收回角色权限：
以SYSTEM用户登录，将CREATE SESSION权限授予角色DEF_ROLE。 SQL>conn system/manager
SQL>GRANT CREATE SESSION TO def_role;
以SCOTT用户登录，将查询EMP表的权限授予角色DEF_ROLE，将插入、更新和删除EMP表数据的权限授予角色PRV_ROLE。 SQL>conn scott/tiger
SQL>GRANT SELECT ON emp TO def_role;
SQL>GRANT INSERT,UPDATE,DELETE ON emp TO prv_role;
以SYSTEM用户登录，将角色DEF_ROLE和PRV_ROLE授予用户ALLEN和CLARK。 SQL>conn system/manager
SQL>GRANT def_role,prv_role TO allen,clark;
设置用户ALLEN和CLARK的默认角色为DEF_ROLE。 SQL>ALTER user allen DEFAULT ROLE def_role; SQL>ALTER user clark DEFAULT ROLE def_role;
以ALLEN用户登录，在COTT.EMP表中查询雇员号为7788的雇员名及SAL； 修改SCOTT.EMP表中雇员号为7788的雇员SAL列数据为4000，有什么结果？
第 20 页 共 27 页