欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle综合练习

程序员文章站 2022-05-07 08:01:48
...

一、创建和使用表

在scott用户中,完成如下各题操作:注意一定要建立worker、depart和salary 3个表之间的关系(主外键的设置)。

1.1 创建表
create table worker(
wk_id int primary key,
wk_name varchar2(20),
wk_gender char(2),
wk_birth	Date,
wk_isdang char(2),
wk_hiredate Date,
wk_deptno int
);

create table depart(
dp_id	int primary key,
dp_name varchar2(20)
);

create table salary(
sal_id int,
sal_name varchar2(20),
sal_date Date,
primary key(sal_id,sal_date),
sal_sal number(6,1)
);
2、增加外键约束
alter table worker add constraint fk_wk_dp foreign key(wk_deptno) references depart(dp_id);

alter table salary add constraint fk_sal_wk foreign key(sal_id) references worker(wk_id);
3、插入数据

depart表

insert into depart values(1,'财务处');
insert into depart values(2,'人事处');
insert into depart values(3,'市场部');

批量导入数据的话,会因为系统自动补齐的日期格式问题造成日期不对,计算年龄出现问题,所以手动补齐了年份19XX或20XX

worker表

insert into worker values(1,'孙华','男',date'1952-1-3','否',date'1970-10-10',2);
insert into worker values(3,'陈明','男',date'1945-5-8','否',date'1965-1-1',2);
insert into worker values(7,'程西','女',date'1980-6-10','否',date'2002-7-10',1);
insert into worker values(2,'孙天奇','女',date'1965-3-10','是',date'1987-7-10',2);
insert into worker values(9,'刘夫文','男',date'1942-1-11','否',date'1960-8-10',2);
insert into worker values(11,'刘星','男',date'1952-10-8','否',date'1970-1-10',1);
insert into worker values(5,'余惠','男',date'1980-12-4','否',date'2002-7-10',3);
insert into worker values(8,'张旗','男',date'1980-11-10','否',date'2002-7-10',2);
insert into worker values(13,'王小燕','女',date'1964-2-10','否',date'1989-7-15',1);
insert into worker values(4,'李华','男',date'1956-8-7','否',date'1983-7-20',3);
insert into worker values(10,'陈涛','男',date'1965-4-19','是',date'1984-7-12',2);
insert into worker values(14,'李艺','女',date'1963-2-10','否',date'1990-7-20',3);
insert into worker values(12,'孙涵','男',date'1965-4-19','是',date'1989-7-10',3);
insert into worker values(15,'魏君','女',date'1970-1-10','否',date'1993-7-10',3);
insert into worker values(6,'欧阳少兵','男',date'1971-12-9','是',date'1992-7-20',3);

salary表

insert into salary values(1,'孙华',to_date('01/04/04','MM/dd/yy'),1201.5);
insert into salary values(3,'陈明',to_date('01/04/04','MM/dd/yy'),1350.6);
insert into salary values(7,'程西',to_date('01/04/04','MM/dd/yy'),750.8);
insert into salary values(2,'孙天奇',to_date('01/04/04','MM/dd/yy'),900.0);
insert into salary values(9,'刘夫文',to_date('01/04/04','MM/dd/yy'),2006.8);
insert into salary values(11,'刘欣',to_date('01/04/04','MM/dd/yy'),1250.0);
insert into salary values(5,'余慧',to_date('01/04/04','MM/dd/yy'),725.0);
insert into salary values(8,'张旗',to_date('01/04/04','MM/dd/yy'),728.0);
insert into salary values(13,'王小燕',to_date('01/04/04','MM/dd/yy'),1200.0);
insert into salary values(4,'李华',to_date('01/04/04','MM/dd/yy'),1500.5);
insert into salary values(10,'陈涛',to_date('01/04/04','MM/dd/yy'),1245.8);
insert into salary values(14,'李艺',to_date('01/04/04','MM/dd/yy'),1000.6);
insert into salary values(12,'孙涵',to_date('01/04/04','MM/dd/yy'),1345.0);
insert into salary values(15,'魏君',to_date('01/04/04','MM/dd/yy'),1100.0);
insert into salary values(6,'欧阳少兵',to_date('01/04/04','MM/dd/yy'),1085.0);
insert into salary values(1,'孙华',to_date('02/03/04','MM/dd/yy'),1206.5);
insert into salary values(3,'陈明',to_date('02/03/04','MM/dd/yy'),1355.6);
insert into salary values(7,'程西',to_date('02/03/04','MM/dd/yy'),755.8);
insert into salary values(2,'孙天奇',to_date('02/03/04','MM/dd/yy'),905.0);
insert into salary values(9,'刘夫文',to_date('02/03/04','MM/dd/yy'),2011.8);
insert into salary values(11,'刘欣',to_date('02/03/04','MM/dd/yy'),1255.0);
insert into salary values(5,'余慧',to_date('02/03/04','MM/dd/yy'),730.0);
insert into salary values(8,'张旗',to_date('02/03/04','MM/dd/yy'),733.0);
insert into salary values(13,'王小燕',to_date('02/03/04','MM/dd/yy'),1205.0);
insert into salary values(4,'李华',to_date('02/03/04','MM/dd/yy'),1505.5);
insert into salary values(10,'陈涛',to_date('02/03/04','MM/dd/yy'),1250.8);
insert into salary values(14,'李艺',to_date('02/03/04','MM/dd/yy'),1005.6);
insert into salary values(12,'孙涵',to_date('02/03/04','MM/dd/yy'),1350.0);
insert into salary values(15,'魏君',to_date('02/03/04','MM/dd/yy'),1105.0);
insert into salary values(6,'欧阳少兵',to_date('02/03/04','MM/dd/yy'),1085.0);

二、SQL

在完成建表的基础上,完成如下各题:
(1)显示所有职工的年龄。

select wk_name,floor((sysdate-wk_birth)/365) as 年龄 from worker;

Oracle综合练习
(2)求出各部门党员的人数。

select wk_deptno,count(*) 党员人数 
from (select * from worker where wk_isdang='是') 
group by wk_deptno;

Oracle综合练习
(3)显示所有职工的姓名和2004年1月份工资数。

select worker.wk_name, salary.sal_sal
  from worker, salary
 where to_char(sal_date, 'yyyy') = 2004
   and to_char(sal_date, 'mm') = 1
   and worker.wk_id = salary.sal_id;

Oracle综合练习
(4)显示所有职工的职工号、姓名和平均工资。

select worker.wk_id 职工号, worker.wk_name 姓名, t.avgsal 平均工资
  from worker,
       (select sal_id, avg(sal_sal) avgsal from salary group by sal_id) t
 where worker.wk_id = t.sal_id;

Oracle综合练习
(5)显示所有职工的职工号、姓名、部门名和2004年2月份工资,并按部门名顺序排列。

select w.wk_id 职工号, w.wk_name 姓名, t.sal_sal 工资, d.dp_name 部门名
 from worker w,
      depart d,
      (select * from salary where sal_date like '%2月 -04') t
where w.wk_id = t.sal_id
  and w.wk_deptno = d.dp_id
order by d.dp_name;

Oracle综合练习
(6)显示各部门名和该部门的所有职工的平均工资。

select d.dp_name, avg(s.sal_sal) avgsal
 from worker w, depart d, salary s
where w.wk_deptno = d.dp_id
  and s.sal_id = w.wk_id
group by d.dp_name;

Oracle综合练习
(7)显示所有平均工资高于1200的部门名和对应的平均工资。

select *
 from (select d.dp_name 部门名, t3.avgsal 平均工资
         from depart d,
              (select t2.wk_deptno, avg(t2.sal_sal) avgsal
                 from (select t.*, w.wk_deptno
                         from worker w, (select * from salary) t
                        where w.wk_id = t.sal_id) t2
                group by t2.wk_deptno) t3
        where d.dp_id = t3.wk_deptno) t4
where 平均工资 > 1200;

Oracle综合练习
(8)显示所有职工的职工号、姓名和部门类型,其中财务处和人事处属管理部门、市场部属市场部门。

select t.*,
       case
         when t.dp_name in ('财务处', '人事处') then
          '管理部门'
         when t.dp_name = '市场部' then
          '市场部门'
       end
  from (select w.wk_id, w.wk_name, d.dp_name
          from worker w, depart d
         where w.wk_deptno = d.dp_id) t;

Oracle综合练习
(9)若存在职工号为10的职工,则显示其工作部门名称,否则显示相应提示信息。

select case
         when rownum = 1 then
          (select dp_name
             from depart
            where dp_id = (select wk_deptno from worker where wk_id = 10))
         else
          '不存在10号员工'
       end 部门名称
  from (select rownum, worker.* from worker where wk_id = 10);

Oracle综合练习
(10)求出男女职工的平均工资,若男职工平均工资高出女职工平均工资50%,则显示“男职工比女职工的工资高多了”的信息;若男职工平均工资与女职工平均工资比率在1.5~0.8之间,则显示“男职工比女职工的工资差不多”的信息;否则,显示“”女职工比男职工的工资差不多“的信息

select case
        when m.avgsal > f.avgsal * 1.5 then
         '男职工比女职工的工资高多了'
        when m.avgsal between f.avgsal * 0.8 and f.avgsal * 1.5 then
         '男职工比女职工的工资差不多'
        else
         '女职工比男职工的工资差不多'
      end
 from (select t2.avgsal
         from (select t.wk_gender, avg(t.sal_sal) avgsal
                 from (select s.*, w.wk_gender
                         from salary s, worker w
                        where s.sal_id = w.wk_id) t
                group by t.wk_gender) t2
        where t2.wk_gender = '男') m,
      (select t2.avgsal
         from (select t.wk_gender, avg(t.sal_sal) avgsal
                 from (select s.*, w.wk_gender
                         from salary s, worker w
                        where s.sal_id = w.wk_id) t
                group by t.wk_gender) t2
        where t2.wk_gender = '女') f;

Oracle综合练习

三、SQL高级应用

(1)删除factory数据库上各个表之间建立的关系。

alter table worker drop constraint fk_wk_dp;
alter table salary drop constraint fk_sal_wk;

Oracle综合练习
(2)显示各职工的工资记录的相应的工资小计。

select worker.wk_id 职工号, worker.wk_name 姓名, t.sumsal 总工资
from worker,
     (select sal_id, sum(sal_sal) sumsal from salary group by sal_id) t
where worker.wk_id = t.sal_id;

Oracle综合练习
(3)按性别和部门名的所有组合方式列出相应的平均工资。

select w.wk_gender, d.dp_name, avg(s.sal_sal)
  from worker w, depart d, salary s
 where w.wk_id = s.sal_id
   and w.wk_deptno = d.dp_id
 group by wk_gender, d.dp_name;

Oracle综合练习
(4)在worker表中使用以下语句插入一个职工记录:
insert into worker values(20,‘陈立’,‘女’,date’1955-03-08’,1,date’1975-10-10’,4);
在depart表中使用以下语句插入一个部门记录:
insert into depart values(5,‘设备处’);
再对worker和depart表进行完整外部联接显示职工的职工号、姓名和部门名,然后删除这两个插入的记录。

select w.wk_id,w.wk_name,d.dp_name 
from worker w,depart d 
where w.wk_deptno=d.dp_id(+);

Oracle综合练习

select w.wk_id,w.wk_name,d.dp_name 
from worker w,depart d 
where d.dp_id=w.wk_deptno(+);

Oracle综合练习
delete from worker where wk_id = 20;
delete from depart where dp_id = 5;

(5)显示最高工资的职工的职工号、姓名、部门名、工资发放日期和工资。

select t2.*, d.dp_name
  from depart d,
       (select t.*, w.wk_deptno
          from worker w,
               (select s.*
                  from salary s
                 where s.sal_sal = (select max(sal_sal) from salary)) t
         where t.sal_id = w.wk_id) t2
 where t2.wk_deptno = d.dp_id;

Oracle综合练习
(6)显示最高工资的职工所在的部门名。

select d.dp_name
 from depart d,
      (select t.*, w.wk_deptno
         from worker w,
              (select s.*
                 from salary s
                where s.sal_sal = (select max(sal_sal) from salary)) t
        where t.sal_id = w.wk_id) t2
where t2.wk_deptno = d.dp_id;

Oracle综合练习
(7)显示所有平均工资低于全部职工平均工资的职工的职工号和姓名。

select *
 from (select worker.wk_id   职工号,
              worker.wk_name 姓名,
              t.avgsal       个人平均工资
         from worker,
              (select sal_id, avg(sal_sal) avgsal
                 from salary
                group by sal_id) t
        where worker.wk_id = t.sal_id) a,
      (select avg(sal_sal) 全部职工平均工资 from salary) b
where a.个人平均工资 < b.全部职工平均工资;

Oracle综合练习
(8)采用游标方式实现(6)的功能。

set serveroutput on
declare
   cursor cur1 is select s.* from salary s where s.sal_sal =(select max(sal_sal) from salary);
rec_sal cur1%rowtype;
v_deptno salary.sal_sal%type;
v_name varchar2(20);
begin
open cur1;
fetch cur1 into rec_sal;
select wk_deptno into v_deptno from worker where wk_id=rec_sal.sal_id;
select dp_name into v_name from depart where dp_id=v_deptno;
dbms_output.put_line(v_name);
close cur1;
end;

Oracle综合练习
(9)采用游标方式实现(7)的功能。显示所有平均工资低于全部职工平均工资的职工的职工号和姓名

declare
  cursor cur2 is
    select worker.wk_id 职工号, worker.wk_name 姓名, t.avgsal 平均工资
      from worker,
           (select sal_id, avg(sal_sal) avgsal from salary group by sal_id) t
     where worker.wk_id = t.sal_id;
  rec_sal  cur2%rowtype;
  v_allavg salary.sal_sal%type;
begin
  select avg(sal_sal) into v_allavg from salary;
  open cur2;
  fetch cur2
    into rec_sal;
  while cur2%found loop
    if rec_sal.平均工资 < v_allavg then
      dbms_output.put_line(rec_sal.职工号 || '  ' || rec_sal.姓名);
    end if;
    fetch cur2
      into rec_sal;
  end loop;
  close cur2;
end;

Oracle综合练习
(10)先显示worker表中的职工人数,开始一个事务。插入一个职工记录,再显示worker表中的职工人数,回滚该事务,最后显示worker表中的职工人数。

oracle使用隐式事务,不需要定义事务开始,第一个sql语句自动开始一个事务,直到commit/rollback/ddl命令该事务结束。之后的第一个sql语句又自动开始一个事务

insert into worker values(20,'陈立','女',date'1955-03-08',1,date'1975-10-10',4);
select count(*) from worker;
rollback;
select count(*) from worker;

Oracle综合练习

四、索引

(1)在worker表中的“部门号“列上创建一个普通索引,若该索引已存在,则删除后重建。

create index idx_wk_deptno on worker(wk_deptno);

(2)在salary表的“职工号”和“日期”列创建唯一索引。

create unique index idx_sal_sid_date on salary(sal_id,sal_date);

五、视图

(1)建立视图view1,查询所有职工的职工号、姓名、部门名和2004年2月份工资,并按部门名顺序排列。

conn sys/Oracle11 as sysdba
grant create view to scott;
conn scott/tiger
create or replace view view1 as
select w.wk_id 职工号, w.wk_name 姓名, t.sal_sal 工资, d.dp_name 部门名
  from worker w,
       depart d,
       (select * from salary where sal_date like '%2月 -04') t
 where w.wk_id = t.sal_id
   and w.wk_deptno = d.dp_id
 order by d.dp_name;

Oracle综合练习
(2)建立视图view2,查询所有职工的职工号、姓名和平均工资。

create or replace view view2 as
select worker.wk_id 职工号, worker.wk_name 姓名, t.avgsal 平均工资
  from worker,
       (select sal_id, avg(sal_sal) avgsal from salary group by sal_id) t
 where worker.wk_id = t.sal_id;

Oracle综合练习
(3)建立视图view3,查询各部门名和该部门的所有职工的平均工资。

create or replace view view3 as
select d.dp_name 部门名, t3.avgsal 平均工资
 from depart d,
      (select t2.wk_deptno, avg(t2.sal_sal) avgsal
         from (select t.*, w.wk_deptno
                 from worker w, (select * from salary) t
                where w.wk_id = t.sal_id) t2
        group by t2.wk_deptno) t3
where d.dp_id = t3.wk_deptno;

Oracle综合练习
(4)显示视图view3的定义。 (提示用user_views数据字典)

select * from user_views where view_name = 'VIEW3';

六、数据库完整性

(1)实施worker表的“性别”字段默认值为“男”的约束。

alter table worker modify wk_gender default '男';

(2)实施salary表的“工资”字段值限定有0~9999的约束。

alter table salary modify sal_sal check(sal_sal>0 and sal_sal <9999);

(3)为worker表建立外键“部门号”,参考表depart的“部门号”列。

alter table worker add constraint fk_wk_dp 
foreign key(wk_deptno) references depart(dp_id);

(4)删除前3小题所建立的约束。

select constraint_name,table_name,constraint_type from user_constraints;

Oracle综合练习

alter table worker modify wk_gender default null;
alter table salary drop constraint SYS_C0011104;
alter table worker drop constraint  FK_WK_DP;

Oracle综合练习

七、用PL/SQL程序完成第2大题的(8),(9),(10)三题(游标)。

(8)显示所有职工的职工号、姓名和部门类型,其中财务处和人事处属管理部门、市场部属市场部门。

declare
  cursor cur1 is
    select w.wk_id, w.wk_name, d.dp_name
      from worker w, depart d
     where w.wk_deptno = d.dp_id;
  rec_cur1 cur1%rowtype;
begin
  open cur1;
  fetch cur1
    into rec_cur1;
  while cur1%found loop
    if rec_cur1.dp_name = '财务处' or rec_cur1.dp_name = '人事处' then
      dbms_output.put_line(rec_cur1.wk_id || '   ' || rec_cur1.wk_name ||
                           '   ' || '管理部门');
    else
      dbms_output.put_line(rec_cur1.wk_id || '   ' || rec_cur1.wk_name ||
                           '   ' || '市场部门');
    end if;
    fetch cur1
      into rec_cur1;
  end loop;
  close cur1;
end;

Oracle综合练习
(9)若存在职工号为10的职工,则显示其工作部门名称,否则显示相应提示信息。

declare
  cursor cur2 is
    select * from worker where wk_id = 10;
  rec_cur2 cur2%rowtype;
  v_dname  depart.dp_name%type;
begin
  open cur2;
  fetch cur2
    into rec_cur2;
  if cur2%rowcount = 1 then
    select dp_name
      into v_dname
      from depart
     where dp_id = rec_cur2.wk_deptno;
    dbms_output.put_line(v_dname);
  else
    dbms_output.put_line('没有10号员工');
  end if;
  close cur2;
end;

Oracle综合练习
(10)求出男女职工的平均工资,若男职工平均工资高出女职工平均工资50%,则显示“男职工比女职工的工资高多了”的信息;若男职工平均工资与女职工平均工资比率在1.5~0.8之间,则显示“男职工比女职工的工资差不多”的信息;否则,显示“”女职工比男职工的工资差不多“的信息

declare
  cursor cur3 is
    select t.wk_gender, avg(t.sal_sal) avgsal
      from (select s.*, w.wk_gender
              from salary s, worker w
             where s.sal_id = w.wk_id) t
     group by t.wk_gender;
  rec_cur3 cur3%rowtype;
  v_m      salary.sal_sal%type;
  v_f      salary.sal_sal%type;

begin
  open cur3;
  fetch cur3
    into rec_cur3;
  while cur3%found loop
    if rec_cur3.wk_gender = '男' then
      v_m := rec_cur3.avgsal;
    else
      v_f := rec_cur3.avgsal;
    end if;
    fetch cur3
      into rec_cur3;
  end loop;
  if v_m > v_f * 1.5 then
    dbms_output.put_line('男职工比女职工的工资高多了');
  elsif v_m between v_f * 0.8 and v_f * 1.5 then
    dbms_output.put_line('男职工比女职工的工资差不多');
  else
    dbms_output.put_line('男职工比女职工的工资差多了');
  end if;
  close cur3;
end;

Oracle综合练习

八、存储过程

(1)创建一个为worker表添加职工记录的存储过程Addworker。

create or replace procedure Addworker(
v_wk_id in worker.wk_id%type,
v_wk_name in worker.wk_name%type,
v_wk_gender in worker.wk_gender%type,
v_wk_birth in worker.wk_birth%type,
v_wk_isdang in worker.wk_isdang%type,
v_wk_hiredate in worker.wk_hiredate%type,
v_wk_deptno in worker.wk_deptno%type
)
as
begin
    insert into worker values(v_wk_id,v_wk_name,v_wk_gender,v_wk_birth,v_wk_isdang,v_wk_hiredate,v_wk_deptno);
    dbms_output.put_line('插入成功');
end;
/

execute Addworker(20,'Liang','男','3-11月-95','是','3-9月-99',1);
execute addworker(21,'Lin','男',date'1997-11-12','是','3-9月-29',1);

Oracle综合练习
(2)创建一个存储过程Delworker删除worker表中指定职工号的记录。

create or replace procedure Delworker(v_wk_id in worker.wk_id%type)
as
begin
    delete from worker where wk_id=v_wk_id;
    dbms_output.put_line('删除成功');
end;
/
 execute delworker(21);

Oracle综合练习
(3)显示存储过程Delworker。

select text from user_source 
where name='DELWORKER' order by line;

(4)删除存储过程Addworker和Delworker。

drop procedure addworker;
drop procedure delworker;

九、触发器

(1)在表depart上创建一个触发器depart_update,当更改部门号时同步更改表worker中对应的部门号。

create or replace trigger depart_update
    after
    update
    on depart
    for each row
begin
    update worker set wk_deptno=:new.dp_id where wk_deptno=:old.dp_id;
    dbms_output.put_line('同步修改完成');
end;
/
update depart set dp_id = 5 where dp_name='财务处';

Oracle综合练习
(2)在表worker上创建一个触发器worker_delete,当删除职工记录时同步删除salary表中对应的职工的工资记录。

create or replace trigger worker_delete
    after
    delete
    on worker
    for each row
begin
delete from salary where sal_id=:old.wk_id;
dbms_output.put_line('级联删除成功');
end;
/
delete from worker where wk_id=1;
 select * from salary where sal_id=1;

Oracle综合练习
(3)删除触发器depart_update。

drop trigger depart_update;

(4)删除触发器worker_delete。

drop trigger worker_delete;

本人作此练习时水平有限,许多sql有待优化。请见谅。