oracle面试题经典

员工基本信息表EMPINFO
 CREATE TABLE EMPINFO
 (
  EMP_ID  char(10)  PRIMARY KEY, 
 NAME  char(6) ,
 BIRTHDAY  date ,  
 SAL  number(4)     
 );
输出结果;
Table created

积分表（INTEG）共有3个字段：
 CREATE TABLE INTEG
 (
 INTEGER_ID  char(10) PRIMARY KEY,
 HABITUDE  varchar2(10), 
 SCORE number(4)   
 );
输出结果;
Table created

积分情况表:
CREATE TABLE THING
 (
EMP_ID  char(10), 
 INTEGER_id  char(10)
 );
输出结果;
Table created

信息表表中插入相应的几条数据：
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL )
 VALUES ('iex_0001','王彪','12-8月-1983',600);
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL)
VALUES ('iex_0002','祝新平','11-5月-1984',600);
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL )
VALUES ('iex_0003','谢青营','5-7月-1985',400);
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL )
VALUES ('iex_0004','王淼','17-8月-1986',400);
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL )
VALUES ('iex_0005','张求熙','25-9月-1984',800);
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL )
VALUES ('iex_0006','习哲亮','14-8月-1986',1200);
INSERT INTO EMPINFO(EMP_ID, NAME, BIRTHDAY,SAL )
VALUES ('iex_0007','许丽丽','15-7月-1985',500);
输出结果;
1 row inserted

积分表中插入3条记录如下：
INSERT INTO INTEG values('I_001','公司类别',4);
INSERT INTO INTEG values('I_002','学习类别',2.5);
INSERT INTO INTEG values('I_003','协作类别',2.5);
INSERT INTO INTEG values('I_004','其他类别',1);
输出结果;
1 row inserted

积分情况表插入6条如下;
INSERT INTO THING values('iex_0001','I_001');
INSERT INTO THING values('iex_0001','I_002');
INSERT INTO THING values('iex_0002','I_003');
INSERT INTO THING values('iex_0002','I_002');
INSERT INTO THING values('iex_0004','I_004');
INSERT INTO THING values('iex_0005','I_001');
输出结果;
1 row inserted

select * from thing;
--1.1、 查询员工基本信息表中的所有信息。
select * from empinfo;
--2、查询员工基本信息表中所有员工的员工编号，姓名和薪水。
select EMP_ID as 员工编号,name as 姓名,SAL as 薪水 from empinfo;
--3、 查询所有员工的姓名，出生年月，并在出生年月前显示"出生日期"字串。
select NAME as 姓名,concat('出生日期',BIRTHDAY) as 出生年月 from empinfo;
--4、 查询员工基本信息表中的所有信息，要求列标题显示为中文。
select EMP_ID as 员工编号 ,NAME as 姓名,BIRTHDAY as 生日 ,SAL as 薪水 from EMPINFO;
--5、 查询员工基本信息表中姓名和加200后的薪水。
select NAME as 姓名,(SAL+200) as 薪水 from empinfo;
--6、 查询员工基本信息表中姓名和负值薪水。
select name,sal from empinfo where sal<0;
--7、 查询员工基本信息表中姓名及提高5%后的薪水。
select name,sal*0.05+sal from empinfo;
select sal*1.05 from empinfo;
--8、查询员工基本信息表中姓名及一个月中每天的薪水。
select name,to_char((sal/30),'999.99') as sal from empinfo;
--9、查询积分表中的所有信息，要求输出格式为"XXX----XXX----XXX----XXX"。
select INTEGER_ID,'----'||HABITUDE as HABITUDE,'----'||SCORE as SCORE from INTEG;
--10、 查询员工基本信息表中薪水低于300员的所有员工信息。
select * from empinfo where sal<300
--11、 查询姓名是"王彪"的员工信息。
select * from empinfo where name='王彪';
--12、 查询薪水在300至600之间的员工信息。
select * from empinfo where sal between 300 and 600;
--13、查询出生年月在"1-5月-1983"至"31-12月-1986"之间的所有员工信息。
select * from empinfo where birthday between '1-5月-1983' and '31-12月-1986';
--上面的语句在Command windows(sql>---sql*plus)中是可以用的,这里是sql用下面的可以
select * from empinfo where birthday between to_Date('01-05-1983','DD-MM-YYYY') and to_Date('31-12-1986','DD-MM-YYYY');
--14、查询积分编号为"I_001"和"I_004"，"I_002"的所有信息。
select * from INTEG where INTEGER_ID='I_001' or INTEGER_ID='I_002' or INTEGER_ID='I_004';
--15、 查询所有姓王的员工信息。
select * from empinfo where name like '王%';
--16、 查询没有登记出生年月的员工信息。
select * from empinfo where birthday is null;
--17、 查询公司的薪水发放标准。
--18、 查询员工编号为iex_0001完成的积分总数。
--这里要加上sum()函数,因为iex_0001的积分不只一次,是多次,所以查询要用in,结果用sum()
select sum(score) from integ where  INTEGER_ID in (select INTEGER_id from thing where EMP_ID='iex_0001');
--为什么会是7(两次4和3),3因为score类型number(4),没有小数点,而(第二次)2.5变成3
--19、查询习哲亮的所有信息，并把他的出生年月推迟一个月。
select emp_id,name,sal,add_months(birthday,-1) as birthday from empinfo where name='习哲亮';
--20、 查询习哲亮出生那个月的最后一天。这个结果是日期格式:yyyy-mm-dd
select emp_id,name,sal,last_day(birthday) as birthday from empinfo where name='习哲亮';
--20-1查询习哲亮出生那个月的最后一天是30天,还是31天,28天。日期格式是单数(天数)
select emp_id,name,sal,to_char(birthday,'DD') as birthday from empinfo where name='习哲亮';
select emp_id,name,sal,to_char(last_day(birthday),'DD') as birthday from empinfo where name='习哲亮';
------'DDD'一年中的第几天,'DD'一月中的第几天,'D'一周中的第几天
--21、 查询习哲亮出生有多少岁了。
select to_date(sysdate)-birthday from empinfo where name='习哲亮';
select to_char(sysdate,'YYYY')-to_char(birthday,'YYYY') from empinfo where name='习哲亮';
--22、查询习哲亮出生后的第一个星期四是几号。
select next_day(birthday,'星期四') from empinfo where name='习哲亮';
select to_char(next_day(birthday,'星期四'),'DD') from empinfo where name='习哲亮';
select next_day(to_date(sysdate),'星期四') from dual;--测试用
--23、 查询今天的日期。
select to_char(sysdate) as abc from empinfo ;
--24、 查询所有员工信息，按薪水的升序显示。
select * from empinfo order by sal;
select * from empinfo order by sal desc;--降序 --消除重复行distinct
--25、对员工基本信息表和积分情况表进行非限制连接查询员工编号，姓名，积分编号
select e.emp_id,e.name,t.INTEGER_id from empinfo e left join thing t on t.EMP_ID=e.emp_id;
select * from empinfo e left join thing t on t.EMP_ID=e.emp_id;
--26、 查询所有员工的姓名，所得的积分编号，按姓名升序显示。
select e.name,t.INTEGER_id from empinfo e full join thing t on e.emp_id=t.emp_id order by e.name;
--27、 查询薪水高于400的员工姓名及积分编号，按员工姓名升序，积分编号降序显示。
select  e.name,t.integer_id from empinfo e left join thing t on e.emp_id=t.emp_id
       where e.sal>400
       order by e.name asc,t.integer_id desc;
--28、 查询所有员工信息和这些员工的积分编号。因为有的员工有两个积分编号
select e.emp_id,e.name,e.birthday,e.sal,t.integer_id from empinfo e left join thing t on e.emp_id=t.emp_id;
--29、 查询所有员工信息和他们所有积分信息。
select e.emp_id,e.name,e.birthday,e.sal,i.INTEGER_ID,i.HABITUDE,i.score
       from  empinfo e,thing t,integ i where e.emp_id=t.emp_id and
       t.integer_id=i.integer_id
 union
 select e.emp_id,e.name,e.birthday,e.sal from empinfo e left join thing t
 on e.emp_id=t.emp_id;
--以上只能找到匹配(empinfo与THING)内容,而empinfo中有的员工没有积分表的信息
-则没有显示.如果用Left可以但不能三表连接.left只有二表连接

--30、 查询薪水高于500元的员工姓名，原薪水，及薪水提高10%后的新薪水。
select name,sal,sal+sal*01 from empinfo where sal>500;
--31、 查询已得到积分编号=I_001的所有员工的信息。
select e.emp_id,e.name,e.BIRTHDAY,e.sal from empinfo e where e.emp_id in (select t.emp_id from thing t where t.integer_id='I_001') ;
--32、 查询除了习哲亮和王彪以外的所有员工信息。
select emp_id,name,birthday,sal from empinfo where name <> '习哲亮' and name <> '王彪';
select emp_id,name,birthday,sal from empinfo where name not like '习哲亮' and name not like '王彪';
--33、 查询所有的积分编号。
select * from thing ;
select * from integ;
--34、查询尚未得到任何积分的所有员工信息。
select emp_id,name,birthday,sal from empinfo where emp_id not in (select emp_id from thing);
--35、 在积分情况表上建立外键, THING.emp_id参照EMPINFO.emp_id
alter table thing add constraint thing_emp_id_fk foreign key (emp_id) references  empinfo(emp_id);
--36、 在员工基本信息表中为字段NAME建立唯一约束.
alter table empinfo add constraint empinfo_name_unique unique (name);
select * from empinfo;
insert into empinfo values('11','王彪',to_date('1938-5-9','YYYY-MM-DD'),500);
--37、 在员工基本信息表中建立检查约束,字段员工编号大于001和小于010的不允许插入.
alter table empinfo add constraint empinfo_id_ck emp_id between to_number(emp_id)>001 and emp_id<010;
--38、 将员工基本信息表中姓名为许丽丽的姓名更改为李丽丽.
update empinfo set name='李丽丽' where name='许丽丽';
select * from empinfo;
--39、 将员工基本信息表中编号为0008的员工删除.
delete from empinfo where emp_id='0008';
select * from empinfo;
rollback;
--40在员工基本信息表中创建名为员工信息表视图，只允许察看员工编号和员工姓名2个字段
create or replace view empinfo_view
       as
       select emp_id as 员工编号,name as 姓名 from empinfo;
select * from empinfo_view;
--41、查询40建立的视图
select * from empinfo_view;
--42、删除刚刚建立的视图.
drop view empinfo_view;
select * from empinfo_view;
--43在员工基本信息表中建立以员工姓名字段的索引，名为姓名字段索引。
create index index_empinfo_name on empinfo (name);
drop index index_empinfo_name;
create index 姓名 on empinfo(name);

--44、删除43建立的索引。
drop index 姓名;
--45、建立一个游标，循环提取员工信息表中所有name信息
declare
abc varchar2(10);
cursor empinfo_cursor is
       select name from empinfo;
begin
     for nameTemp in empinfo_cursor
     loop
         DBMS_output.put_line('员工姓名: '||nameTemp.name);
       -- select nameTemp.name from nameTemp;
       --如果要得到这个值只有给一个变量或变量数组
      --不行 select (nameTemp.name into abc) as dd from dual;
     end loop;
end;
---见:请按'OutPut'看结果.
--46,建立存储过程，将员工信息表中出生日期=11-5月-1984送入到定义的变量里。
create or replace procedure
       empinfoCheck(birTemp in date,bir out date)
       as
       empinfo_birthday date;
       begin
       select birthday into empinfo_birthday from empinfo where birthday=birTemp;
      -- DBMS_output.put_line('员工生日: '||empinfo_birthday);
         bir:=empinfo_birthday;
       end;
--运行存储过程empinfoCheck
declare
value2 date;
begin
   empinfoCheck(to_date('1984-5-11','YYYY-MM-DD'),value2);
   DBMS_output.put_line('员工生日: '||to_char(value2,'YYYY-MM-DD'));
end;
--47首先建立一个EMPINFO1结构和数据完全和EMPINFO一致，建立一个触发器，
--当向表EMPINFO插入一行数据之前，则向EMPINFO1表插入2个列，a列为变量，b列为常量.（a代表处发次数）
--1.建立empinfo1表，与empinfo表结构和数据一样
create table empinfo1 as select * from empinfo;
select * from empinfo1;
--2因为a代表触发次数,所以要建立一个序列
create sequence empinfo1_sequence
select * from user_sequences;
--3建立触发器

create or replace trigger empinfo_insert_trigger
      before insert or update of sal
      on empinfo
       for each row
      --where (new.sal<600) --触发器的条件,满足才触发
      begin
      --会报错,不能alter,如果把两行alter放在触发器外运行.触发器成功.47题有问题
       alter table empinfo1 add a varchar2(10);
       alter table empinfo1 add b varchar2(10);
      insert into empinfo1 values('iex_0008','河东',to_date('1986-8-9','YYYY-MM-DD'),650,empinfo1_sequence.nextval,'常量');
      end;
--测试触发器
select * from user_triggers;
insert into empinfo values('iex_0008','河东',to_date('1986-8-9','YYYY-MM-DD'),650);
select * from empinfo;
  
   alter table empinfo1 add a varchar2(10);--增加列
   alter table empinfo1 drop column a;--删除列
   select * from empinfo1;

--48分别创建用户TEMPUSER（UNLOCK）和角色temprole。
--(unlock表示未锁定)
--锁定account lock
create user TEMPUSER identified by accp account unlock;
--创建角色:没有创建角色的权限
create role temprole  identified by accp;
--要以system/accp用户连接数据库,本用户是datatest
grant create role to datatest;
create role temprole; --给角色设置密码identified by accp
--49、把能够创建任何表的权限给tempuser
grant resource to tempuser;
revoke resource from tempuser;
alter user tempuser identified by accp;--修改密码
--50、授权用户tempuser连接到数据库。
grant connect to tempuser
revoke connect from tempuser;

--51、 创建一个事务控制，就是实现汇总表上的示例的功能。
--group by分组汇总语句--rollup 跟在group by 后详细汇总
  select name,birthday,sum(sal) from empinfo group by birthday,rollup(name) having birthday='14-8月-1986'
--cube 实现总汇总 可以实现总汇总和单个明细汇总
  select name,birthday,sum(sal) from empinfo group by cube(name,biarthdy) --having birthday='14-8月-1986'