orcale练习题二

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

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

create table t_student (no varchar2(11) primary key,
name varchar2(10) null,sex varchar2(10) default '男',constraint 
s_check check(sex in('男','女')));

create table t_score(no varchar2(11),cno varchar2(10),score number(3,1) null,
constraint c_socre check(score>0),primary key(no,cno),foreign key (no) references t_student(no));

alter table t_student move tablespace lwq;
alter table t_score move tablespace lwq;

二、用户管理

.创建2个用户testA和testB，密码为testA、testB，然后创建一个角色demo，并将建立连接、建表、建视图及对scott用户的emp表的select等权限赋给该角色，然后将该角色赋给testA和testB。写出以上语句。

create user c##testA identified by testA;
create user c##testB identified by testB;
create role c##demo;
grant create session to c##demo;
grant create table to c##demo;
grant create view to c##demo;
grant all on c##scott.emp to c##demo;
grant c##demo to c##testA;
grant c##demo to c##testB;

三、sql语句查询

1．从emp表中检索雇员姓名。
2．列出员工表中每个部门的员工数，和部门no
3．从emp表中检索工资超过1000的雇员信息。
4．显示雇员名，雇员工资及所在部门的名字，并按部门排序
5．显示与 SMITH 同部门的所有员工
6．显示高于自己部门平均工资的员工的信息
7．显示每个员工的上级领导的姓名。
8．求平均工资大于2000的工作岗位
9．显示 1980 年入职的所有员工
10. 找出工资比JONES多的员工

--1．从emp表中检索雇员姓名。
select ename from emp;
--2．列出员工表中每个部门的员工数，和部门no
select deptno no ,count(*)雇员数 from emp group by deptno;
--3．从emp表中检索工资超过1000的雇员信息。
select *from emp where sal>1000;
--4．显示雇员名，雇员工资及所在部门的名字，并按部门排序
select  ename,sal,dname from emp,dept where emp.deptno=dept.deptno order by emp.deptno;
--5．显示与 SMITH 同部门的所有员工
select * from emp where deptno=(select deptno from emp where ename ='SMITH');
--6．显示高于自己部门平均工资的员工的信息
select * from emp ,(select deptno,avg(sal) sal from emp group by deptno) a where emp.deptno=a.deptno and emp.sal>a.sal;
--7．显示每个员工的上级领导的姓名
select a.ename,b.ename from  emp a,emp b where a.empno=b.mgr;
--8．求平均工资大于2000的工作岗位
select job from cemp group by job having avg(sal)>2000;
--9．显示 1980 年入职的所有员工
select *from emp where to_char(hiredate,'yyyy')=1980;
--10. 找出工资比JONES多的员工
select *from emp where sal>(select sal from emp where ename='JONES');

四、综合编程题

1：创建一个存储过程，根据提供的雇员姓名（作为过程的参数），将该雇员的工资改为2000；

create or replace procedure test1 (v_ename emp.ename%type) is
begin
  update emp set sal=2000 where ename=v_ename;
end;
execute test1('SMITH');

2：创建一个存储过程，根据提供的雇员姓名，查询该雇员的上级领导人的姓名，并返回。

create or replace procedure test2 (v_ename emp.ename%type) is
  l_ename emp.ename%type;
begin
  select ename into l_ename from emp where empno=(select mgr from emp where ename=v_ename);
  dbms_output.put_line(l_ename);
end;
execute test2('BLAKE');

3：创建一个存储过程，输入部门号，返回该部门所有雇员的姓名，工资和佣金。

create or replace procedure test3(v_deptno in emp.deptno%type) is
  cursor youbiao is select * from emp where deptno=v_deptno;
begin
   loop
      for v_emp in youbiao
        dbms_output.put_line(v_emp.ename||''||v_emp.sal||''||v_emp.comm);
   end loop;
end;

execute test3(10);

4：编写一个过程，输入部门号，返回该部门所有雇员信息。

create or replace procedure every (v_deptno dept.deptno%type)
is
cursor yb is select *from emp where deptno=v_deptno;
v_emp emp%rowtype;
begin
  open yb;
    loop
      fetch yb into v_emp;
        dbms_output.put_line(v_emp.empno||''||v_emp.ename||''||v_emp.sal||''||v_emp.comm);
      exit when yb%notfound;
    end loop;
  close yb;
end;

execute every(20);