oracle练习题答案

1) 用sqlplus连接数据库时，为什么会出Oracle not available错误？
        oracle server（即通常所说的数据库）是否启动，ORACLE_SID是否正确设置。


2) 找出员工的姓中（last_name）第三个字母是a的员工名字
        select last_name from s_emp
        where last_name like '__a%'


3) 找出员工名字中含有a和e的
        select first_name from s_emp
        where first_name like '%a%'
        and first_name like '%e%'


        比较
        select first_name from s_emp
        where first_name like '%a%e%'


4) 列出每个员工的名字，工资、涨薪后工资（涨幅为8%），元为单位进行四舍五入
        select first_name,salary,round(salary*1.08)
        from s_emp


5) 找出谁是最高领导，将名字按大写形式显示
        select upper(first_name) from s_emp
        where manager_id is null


6) 找出所有有提成的员工，列出名字、工资、提出，显示结果按工资从小到大，提成从小到大
        select first_name,salary,commission_pct
        from s_emp
        where commission_pct is not null
        order by salary desc,commission_pct


7) 找出31、41部门中工资在1000到1500之间的员工
        select first_name,dept_id,salary
        from s_emp
        where dept_id in (31,41)
        and salary between 1000 and 1500


8) 改变NLS_LANG的值，让
select to_char(salary*12,’L99,999.99’) from s_emp
输出结果的货币单位是￥或$
        setenv NLS_LANG 'SMIPLIFIED CHINESE_CHINA.ZHS16GBK' (￥)
        setenv NLS_LANG AMERICAN_AMERICA.US7ASCII


9) 哪些员工的工资高于他直接上司的工资，列出员工的名字和工资，上司的名字和工资
        select e.first_name,e.salary,m.first_name,m.salary
        from s_emp e,s_emp m
        where e.manager_id = m.id
        and e.salary > m.salary


10) 42部门有哪些职位
        select distinct title from s_emp
        where dept_id = 42


11) 哪些员工的入职日期比Ben晚
        select first_name,start_date
        from s_emp
        where start_date < (select start_date from s_emp
                            where first_name = 'Ben')
        若只有一个名为'Ben'的。


        select first_name,start_date
        from s_emp
        where start_date <all (select start_date from s_emp
                               where first_name = 'Ben')


        select first_name,start_date
        from s_emp
        where start_date < (select max(start_date) from s_emp
                            where first_name = 'Ben')


12) 部门号在20至40之间的员工中，最高和最低工资
        select maxt(salary),min(salary)
        from s_emp
        where dept_id between 20 and 40


13) 入职日期最早和最晚的值
        select max(start_date),min(start_date) from s_emp


14) 整个公司中，最高工资和最低，工资相差多少
        select max(salary) - min(salary) from s_emp


15) 公司有多少人和资金不为空的人数
        select count(*),count(commission_pct) from s_emp


16) 资金大于0的人数
        select count(*) from s_emp
        where commssion_pct > 0


17) 各个部门平均、最大、最小工资、人数，按照部门号升序排列
        select dept_id,max(salary),min(salary),count(*)
        from s_emp
        group by dept_id 
        order by dept_id 


18) 工资大于1500的部门的人数（各个部门中工资大于1500的员工人数）se
        select dept_id,count(*)
        from s_emp
        where salary > 1500
        group by dept_id
        
19) 各个部门平均工资和人数，按照部门名字升序排列
        select max(d.name) dname,max(r.name) rname,avg(e.salary),count(*)
        from s_emp e,s_dept d,s_region r
        where e.dept_id = d.id
        and d.region_id = r.id
        group by dept_id
        order by dname,rname


20) 是否有员工在同一个部门，而且工资相同，列出这样的部门号和工资、人数
        （每个部门中多少有同样工资的员工，列出他们的部门号，工资，人数）
      select e1.dept_id,avg(e1.salary),count(*)
        from s_emp e1 ,s_emp e2
        where e1.dept_id = e2.dept_id 
        and e1.salary = e2.salary
        and e1.first_name <> e2.first_name
        group by e1.dept_id


21) 哪些部门工资高于1000的人数超过2人，列出部门名字
        select max(d.name),max(r.name)
        from s_emp e,s_dept d,s_region r
        where e.dept_id = d.id
        and d.region_id = r.id
        and salary > 1000
        group by dept_id
        having count(*) > 2


        select d.name,r.name
        from s_dept d,s_region r, (select dept_id from s_emp
                                   where salary > 1000
                                   group by dept_id
                                   having count(*) > 2) a 
        where a.dept_id = d.id
        and d.region_id = r.id


22) 哪些员工和Biri(last_name)同部门
        select last_name,dept_id
        from s_emp
        where dept_id = (select dept_id from s_emp
                         where last_name = 'Biri')
        and last_name <> 'Biri'


        select last_name,dept_id
        from s_emp
        where dept_id in (select dept_id from s_emp
                         where last_name = 'Biri')
        and last_name <> 'Biri'


23) 哪些员工的工资，高于整个公司的平均工资，列出员工的名字和工资（降序）
        select first_name,salary
        from s_emp
        where salary > (select avg(salary) from s_emp)
        order by salary desc


24) 哪些员工的工资，介于32和33部门（33高些）平均工资之间
        select first_name,salary
        from s_emp
        where salary between (select avg(salary) from s_emp 
                              where dept_id = 32)
                     and     (select avg(salary) from s_emp
                              where dept_id = 33)
                          
25) 所在部门平均工资高于1500的员工名字
        select first_name,dept_id
        from s_emp
        where dept_id in (select dept_id from s_emp
                          group by dept_id
                          having avg(salary) > 1500)
        
26) 列出各个部门的ID，以及这个部门的最高工资员工名字和工资
        select dept_id,first_name,salary
        from s_emp
        where (dept_id,salary) in (select dept_id,max(salary)
                                   from s_emp
                                   group by dept_id)


27) 哪个员工是整个公司的最高工资
        select first_name,salary
        from s_emp
        where salary = (select max(salary) from s_emp)


28) 部门平均工资最高的是多少
        select max(avg(salary)) from s_emp
        group by dpet_id   


29) 哪些部门的人数比32号部门的人数多
        select dept_id,count(*)
        from s_emp
        group by dept_id
        having count(*) > (select count(*) from s_emp
                           where dept_id = 32)




1) 完成s_emp,s_dept,s_region三张表的E-R图
2) 讨论E-R图在基于数据库的开发中的重要作用
3) 重新理解怎样完成多表查询


1. 工资在1500至3000之间的员工，各自工作的年限（四舍五入，降序）。
        select first_name,salary,round((sysdate-start_date)/365)
        from s_emp
        where salary between 1500 and 3000       
 
2. 公司规定，每个员工在工作25年的第一个周五，可以申请退休，查询每个员工的这天
显示格式：2010-01-01：MON
        select first_name,to_char(add_months(start_date,25*12),'yyyy-mm-dd')
        from s_emp


3. 讨论为什么说数据库里流淌的是一个一个的事务？为什么说程序员写的是事务，而不是DML语句?
4. 在完成基于数据库的开发中，程序员主要操作的数据库对象是什么？对其主要做什么操作？哪些操作是频繁使用的？


课后练习（After Course Exercise）
1. 讨论索引的作用，索引和查询的关系？
2. 在表和视图上可以定义多少种类型的约束？




计费项目
1. 根据E-R图完成以下表结构的设计：管理员表、模块信息表、管理员权限表、用户信息表、用户业务帐号信息表、资源信息表
2. 完成一个脚本，包含对上述表的建表语句。
3. 追加脚本，完成上述表的维护操作。
4. 完成以下查询：
每个管理员有哪些权限？
哪些管理员有所有权限？
没有某项权限的管理员有哪些？
用户选择了哪些业务？
用户选择的业务的资费情况如何？




/*****************************************************
    t_adm   管理员表（用于保存管理员信息）
******************************************************/
drop table t_adm;
create table t_adm(
    id              number primary key,    -- 管理员编码
    name            varchar2(32),                   -- 管理员姓名
    login_name      varchar2(10) unique not null,   -- 登录名
    login_password  varchar2(32),                   -- 登录密码
    phone           varchar2(32),                   -- 联系电话
    email           varchar2(32),                   -- E-Mail
    enroll_date     date,                           -- 注册日期
    close_date      date                            -- (不用字段)
);
insert into t_adm values(0,'tarena','tarena','tarena','62132839','tarena@tarena.com.cn',sysdate,null);


/*****************************************************
    t_module    模块表（用于保存模块信息）
******************************************************/
drop table t_module;
create table t_module(
    id          number primary key, -- 模块编号
    name        varchar2(32),       -- 模块名称
    user_flag   char                -- 使用者标记：0——管理员；1——用户；
);
insert into t_module values(1, '管理员管理', 0);
insert into t_module values(2, '资费管理', 0);
insert into t_module values(3, '用户管理', 0);
insert into t_module values(4, '账务查询', 0);
insert into t_module values(5, '账单查询', 0);
insert into t_module values(6, '用户自服务', 1);






/*****************************************************
    t_adm_rights    管理员权限表（用于保存管理员
                    对模块的访问权限）
******************************************************/
drop table t_adm_rights;
create table t_adm_rights(
    adm_id      number references t_adm(id),       -- 管理员编码
    module_id   number references t_module(id),    -- 模块编号
    primary key(adm_id, module_id)
);






/*****************************************************
    t_pricing   资费表
******************************************************/
drop table t_pricing;
create table t_pricing(
    pricing_id       number primary key,     -- 资费编码(pk)
    pricing_name     varchar2(32) not null unique,  -- 资费名称
    base_fee         number(5,2),                   -- 月租费
    rate_fee         number(4,2),                   -- 每小时费用
    pricing_desc     varchar2(64)               -- 资费描述
);




/*************************************************************
    t_users 用户表（用于保存用户账务帐号信息及个人信息）
**************************************************************/
drop table t_users;
create table t_users (
    id              number(32) primary key,  -- 主键编码
    name            varchar2(32) not null,    -- 用户姓名
    login_name      varchar2(10) unique,      -- 用户账务帐号
    login_password  varchar2(32) not null,    -- 用户账务帐号密码
    status          number(2) not null,       -- 账务帐号状态（0：开通；1：暂停；2：删除）
    phone           varchar2(32),             -- 联系电话
    email           varchar2(32),             -- email
    enroll_date     date,                     -- 开通日期
    close_date      date,                     -- 暂停日期
    payment_style   number(2),                -- 付款方式(0：现金；1：银行转帐；2：邮局汇款；3：其他)
    career          varchar2(32),             -- 职业
    nationality     varchar2(32),             -- 国籍(系统中在本字段记录省份)
    gender          varchar2(8),              -- 性别
    company         varchar2(32),             -- 公司名称
    address         varchar2(32),             -- 地址
    post_code       varchar2(16)              -- 邮编
);




/******************************************************************
    t_users_services 用户业务帐号表（用于保存用户业务帐号信息）
*******************************************************************/
drop table t_user_services;
create table t_user_services (
    id                  number(32) primary key,           -- 主键编码
    user_id             number(32) references t_users(id), -- 用户表主键编码
    lab_login_name      varchar2(10),                     -- 用户业务帐号
    lab_login_password  varchar2(32),                     -- 用户业务帐号密码
    lab_ip              varchar2(32) not null,            -- 服务器编码
    user_status         number(2),                        -- 业务帐号状态（0：正常；1：暂停；2：删除）
    pricing_id          number references t_pricing(pricing_id),  -- 资费编码
    enroll_date         date,                             -- 开通日期
    close_date          date                              -- 暂停日期
);
create unique index ind_us on t_user_services(lab_login_name, lab_ip)




1. 根据三张表S_EMP、S_DEPT、S_REGION三张表完成各个部门的平均工资，显示不要部门号而是该部门的详细部门名称，要求两种写法？
        select max(d.name),max(r.name),round(avg(e.salary),2)
        from s_emp e,s_dept d,s_region r
        where e.dept_id = d.id
        and d.region_id = r.id
        group by e.dept_id


2. 用多种写法完成哪些人是领导？
        select distinct m.first_name 
        from s_emp e,s_emp m
        where e.manager_id = m.id
       
        select first_name from s_emp
        where id in (select manager_id from s_emp)
 
        select first_name from s_emp m
        where exists ( select 1 from s_emp e
                       where m.id = e.manager_id)


3. 用多种写法完成哪些人是员工？
        select m.first_name
        from s_emp e,s_emp m
        where e.manager_id(+) = m.id
        and e.first_name is null


        select first_name from s_emp
        where id not in (select manager_id from s_emp
                         where manager_id is not null)


        select first_name from s_emp m
        where not exists (select 1 from s_emp e
                          where m.id = e.manager_id)
      
4. 用多种写法完成哪些部门有员工？
        select distinct d.dname from emp e,dept d
        where e.deptno = d.deptno
         
        select dname from dept
        where deptno in (select deptno from emp)


        select dname from dept d
        where exists (select 1 from emp e
                      where d.deptno = e.deptno)


5. 用多种写法完成哪些部门没有员工？
        select dname from emp e,dept d
        where e.deptno(+) = d.deptno
        and e.deptno is null


        select dname from dept
        where deptno not in (select deptno from emp
                             where deptno is not null)


        select dname from dept d
        where not exists (select 1 from emp e
                          where d.deptno = e.deptno)


6. 用多种写法完成哪些部门没有名字是‘Ben’的员工？
        select id from s_dept
        where id not in (select dept_id from s_emp
                              where first_name = 'Ben')


        select d.id from s_emp e,s_dept d
        where e.dept_id(+) = d.id
        and e.first_name(+) = 'Ben'
        and e.first_name is null


        select id from s_dept d
        where not exists (select 1 from s_emp e
                          where d.id = e.dept_id
                          and e.first_name = 'Ben')


7. 用多种写法完成哪些地区有员工？
        select distinct r.name
        from s_emp e,s_dept d,s_region r
        where e.dept_id = d.id
        and d.region_id = r.id


        select name from s_region
        where id in (select region_id
                     from s_emp e,s_dept d
                     where e.dept_id = d.id)


        select name from s_region r
        where exists (select 1 from s_emp e,s_dept d
                      where e.dept_id = d.id
                      and d.region_id = r.id)


        select name from s_region r
        where exists (select 1 from s_dept d
                      where exists (select 1 from s_emp e
                                    where e.dept_id = d.id
                                    and d.region_id = r.id
                                    )
                      )


8. 讨论内连接、外连接、非关联子查询（in/not in）、关联子查询（exists/not exists）能解决哪些类似的问题？


9. 用多种写法完成哪些学生选修了所有课程？
        select max(name) from stu s,stu_cou sc
        where s.id = sc.sid
        group by s.id
        having count(sc.cid) = (select count(id) from cou)


        select a.name
        from (select max(name) name,count(sc.sid) cnt from stu s,stu_cou sc
              where s.id = sc.sid
              group by s.id) a,(select count(id) cnt from cou) b
        where a.cnt = b.cnt


        select name from stu s
        where not exists (select 1 from cou c
                          where not exists (select 1 from stu_cou sc
                                            where s.id = sc.sid
                                            and   c.id = sc.cid)
                          )
        


10. 用多种写法完成哪些地区没有员工？
        select name from s_region  
        where id not in (select region_id from s_emp e,s_dept d
                         where e.dept_id = d.id)


        select r.name 
        from s_region r,(select d.region_id from s_emp e,s_dept d
                         where e.dept_id = d.id) a
        where r.id = a.region_id(+)
        and a.region_id is null


        select name from s_region r
        where not exists (select 1 from (select d.region_id from s_emp e,s_dept d
                                         where e.dept_id = d.id) a
                          where r.id = a.region_id)


11. 42部门员工的纳税情况，税率如下：
0.00 ——1999.99 0.00
2000.00——3999.99 0.09
4000.00——5999.99 0.20
6000.00——7999.99 0.30
8000.00——9999.99 0.40
10000.00——11999.99 0.42
12000.00——139999.99 0.44
140000.00 or greater 0.45
分别用decode和case when实现。
select first_name,salary,decode(trunc(salary/2000),0,0,
                                                   1,0.09,
                                                   2,0.20,
                                                   3,0.30,
                                                   4,0.40,
                                                   5,0.42,
                                                   6,0.44,
                                                   0.45)
from s_emp


select first_name,salary,(case when salary between 0 and 1999.99 then 0.00
                               when salary between 2000 and 3999.99 then 0.09
                               when salary between 4000 and 5999.99 then 0.20
                               when salary between 6000 and 7999.99 then 0.30
                               when salary between 8000 and 9999.99 then 0.40
      when salary between 10000 and 11999.99 then 0.42
                               when salary between 12000 and 13999.99 then 0.44
                               else 0.45 
                          end) tax
from s_emp




14. 从emp表和dept表中完成如下查询：各个部门不同职位的工资总和？输出如下：
JOB          DEPT_10    DEPT_20    DEPT_30    DEPT_40
--------- ---------- ---------- ---------- ---------- --------- ---------- ---------- ---


CLERK              1         2          1
SALESMAN                                4
PRESIDENT          1
MANAGER            1         1          1
ANALYST     2


select  count(decode(deptno,10,sal)),count(decode(deptno,20,sal)),
        count(decode(deptno,30,sal)),count(decode(deptno,40,sal))
from emp 
group by job 


select job,max(decode(deptno,10,cnt)) dept_10, max(decode(deptno,20,cnt)) deptno_20,
           max(decode(deptno,30,cnt)) dept_30, max(decode(deptno,40,cnt)) deptno_40
from (select job,deptno,count(sal) cnt from emp
      group by job,deptno)
group by job 


15. 数据如下：
日期                事务所                标章NO.(varchar2) 
12/12                北京                 1
12/12                北京                 2
12/12                北京                 3
12/13                河北                 51
12/13                河北                 52
12/13                河北                 53
12/13                河北                 60
12/13                河北                 70
 输出结果如下: 只要日期和事务所一样,标章号连续就把记录合并
 日期                事务所                标章NO.(varchar2) 
12/12                北京                 1-3
12/13                河北                 51-53
12/13                河北                 60
12/13                河北                 70




select b.日期,b.事务所，min(b.标章号),max(b.标章号)
from (
          select a.*,to_number(a.标章号)-rownum cc
          from (
                 select * from t order by 日期,事务所，标章号
                ) a
       )  b
group by b.日期,b.事务所，b.cc


16. 有一个权限表XT_QXMX:(权限序号,权限名称)
QXXH   QXMC
1 发文填写
2 发文审批
3 收文填写
4 收文审批


权限-人员对应表XT_QXRY,如果某人有某个权限,则在此表有条记录
(序号,权限序号,用户名称)
XH QXXH YHMC
1 2 张三
2 3 张三


(张三有两个权限,发文审批和收文填写)


写一个查询:列出张三有哪些权限,同时列出所有的权限.查询结果如下:
QXXH QXMC  是否有此权限
1 发文填写  false
2 发文审批  true
3 收文填写  true
4 收文审批  false


select m.qxxh,m.qxmc,decode(r.yhmc,'张三','true','false')
from xt_qxmx m ,xt_qxry r
where m.qxxh = r.qxxh(+)
and r.yhmc(+) = '张三'
order by m.qxxh