orcale练习题一

一、用sql语句创建如下表，将该表保存到以自己姓名命名的表空间中。

create table goods (goodsId varchar2(8) primary key, goodsName varchar2(10) not null,unitPrice number(6,2), constraint u_check check（unitPrice>0));

create table purchase (goodId varchar2(8),customerId varchar2(8),nums number(5),primary key (goodId,customerId), constraint n_check check(nums between 1 and 30));

create tablespace lwq datafile 'd:/1.dbf'size 100m;

alter table goods move tablespace lwq;
alter table purchase move tablespace lwq;

二、对象操作

1. 创建用户，用户名为你的姓名+学号的最后两位数（如姓名张三，学号201022023，用户名为“张三23”），口令为你姓名的全拼（如zhangsan），默认表空间为user。
2. 向用户授予连接数据库系统权限
3. 向用户授予emp表的select权限，
4. 以新建用户身份查询emp表
5. 撤销向该用户授予的系统权限，向用户授予connect角色
6. 对商品表的单价建立普通索引
7. 建立一个视图，名称为myview，视图中包含雇员编号，姓名，工资和所在部门名称
8. 建立一个序列，初始值为1001，增量为5，缓存30，不循环
9. 使用system用户对dept表创建一个公有同义词d
10. 向商品表中插入一条记录，设置保存点a,再插入一条记录，然后回滚到保存点a,再提交

--1. 创建用户，用户名为你的姓名+学号的最后两位数（如姓名张三，学号201022023，用户名为“张三23”），口令为你姓名的全拼（如zhangsan），默认表空间为users。
create user c##lwq75 identified by liuwenqing default tablespace users;
--2-向用户授予连接数据库系统权限
grant create session to c##lwq75;
--3向用户授予emp表的select权限,
grant select on c##scott.emp to c##lwq75;
--4以新建用户身份查询emp表
--在用户c##lwq75中执行
select*from c##scott.emp;
--5撤销向该用户授予的系统权限,向用户授予connect角色
revoke create session from c##lwq75;
grant c##connect to c##lwq75;I
--6.对商品表的单价建立普通索引
create index u_index on goods(uniterice);
--7．建立一个视图,名称为myview,视图中包含雇员编号,姓名，工资和所在部门名称
create view myview as select empno,ename,sal, dname from c##scott.emp,c##scott.dept where emp.deptno=dept.deptno;
--8．建立一个序列,初始值为1001,增量为5,缓存30，不循环
create sequence seq1 start with 1001 increment by 1001 cache 30 nocycle;
--9．使用system用户对dept表创建一个公有同义词a
create public synonym c##scott.dept for a;
--10. 向商品表中插入一条记录，设置保存点a,再插入一条记录，然后回滚到保存点a,
savepoint a;
insert into dept2(deptno) values(5);
rollback to a;

三、查询

1显示每个雇员的雇员名，雇员工资及所在部门的名字
2显示部门号为 10 的工资大于2000的员工的部门名、员工名和工资。
3显示雇员名，雇员工资及所在部门的名字，并按部门排序
4列出所有员工的姓名和其上级的姓名。
5查询emp表中在部门20工作的雇员的姓名及其上级的姓名
6查询和部门 10 的工作相同的雇员的名字、岗位、工资、部门号
7显示与 SMITH 同部门的所有员工
8以职位分组，找出每种职位的平均工资。
9找到平均工资大于2000的工作职种
10找出工资比Jones多的员工

--1显示每个雇员的雇员名,雇员工资及所在部门的名字
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno;
--2显示部门号为10的工资大于2000的员工的部门名、员工名和工资。
select dname,ename,sal from emp,dept where sal>2000 and emp.deptno=10;
--3显示雇员名,雇员工资及所在部门的名字,并按部门排序
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno order by emp;
--4列出所有员工的姓名和其上级的姓名。
select a.ename, b.ename from emp a,emp b where a.mgr=b.empno;
--5查询emp表中在部门20工作的雇员的姓名及其上级的姓名
select a.ename, b.ename from emp a, emp b where a.mgr=b.empno and a.deptno=20;
--6查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
select ename ,job, sal,deptno from emp where job in(select job from emp where deptno=10);
--7显示与 SMITH 同部门的所有员工
select *from emp where deptno in(select deptno from emnp where ename='SMITH');
--8以职位分组,找出每种职位的乎均工资。
select job,avg(sal) from emp group by job;
--9找到平均工资大于2000的工作职种
select job from emp group by job having avg(sal)>2000;
--10找出工资比Jones多的员工
select *from emp where sal >(select sal from emp where ename='JONES');

四、编程

1．编写PL/SQL块，使用case语句更新相应部门的员工奖金，在原来基础上增加补贴，部门10补贴100元，部门20补贴80元，部门30补贴50元。

declare
  v_deptno emp.deptno%type;
  v_empno emp.empno%type;
  cursor youbiao is select deptno,empno from emp;
begin
  open youbiao;
    loop
      fetch youbiao into v_deptno,v_empno;
      exit when youbiao%notfound;
        case v_deptno when '10' then update emp set comm=comm+100 where deptno=v_deptno and empno=v_empno;
                when '20' then update emp set comm=comm+80 where deptno=v_deptno and empno=v_empno;
                when '30' then update emp set comm=comm+50 where deptno=v_deptno and empno=v_empno;
                else continue;
  end case;
    end loop;
  close youbiao;
end;

2．创建一个存储过程，根据提供的雇员编号（作为过程的参数），将该雇员的奖金改为500；

create or replace procedure addd(v_empno emp.empno%type)
is
begin
  update emp set comm=500 where empno=v_empno;
end;
execute addd(2222);
select *from emp;

3． 创建一个存储过程，输入一个工资数，输出emp表中员工工资高于该工资的员工姓名、部门编号、工资和奖金。

create or replace procedure sal(v_sal emp.sal%type)
is
cursor youbiao is select *from emp;
begin
for yyy in youbiao
loop
  if yyy.sal>v_sal then dbms_output.put_line(yyy.ename||''||yyy.deptno||''||yyy.sal||''||yyy.comm);
  end if;
end loop;
end;
execute sal(2000);
set serveroutput on;

4． 写一个函数，传入员工编号，返回所在部门名称

create or replace function dname (v_empno emp.empno%type) return dept.dname%type
is
v_dname dept.dname%type;
begin
select dname into v_dname from emp,dept where emp.deptno=dept.deptno and empno=v_empno;
return v_dname;
end;

declare 
ename dept.dname%type;
begin
ename:=dname(7499);
dbms_output.put_line(ename);
end;

5． 创建一个触发器，禁止用户删除dept表中的记录。

create or replace trigger dde before delete on dept for each row
begin
  if deleting then raise_application_error(-20011,'禁止删除dept表记录');
  end if;
end;
delete from dept2 where deptno=10;