Oracle:经典例题解析

1.环境准备

下面所有SQL语句都是建立在employees表和departments表上，具体表结构如下所示：
departments表结构如下所示：

字段名	数据类型	长度	约束	说明
department_id	number	4	primary key	部门编号
dep_name	varchar2	30	not null	部门名称
manager_name	varchar	20		部门管理者姓名

创建departments表的sql语句如下所示

create table departments(
       department_id number(4) primary key,
       dep_name varchar2(30) not null,
       manager_name varchar2(20)
)

employees表结构如下所示：

字段名	数据类型	长度	约束	说明
emp_id	number	6	primary key	员工编号
emp_name	varchar2	20		员工名
sex	number	1		员工性别
birthday	Date			员工生日
salary	number	8,2	大于0	工资
department_id	number	4	foreign key	所属部门编号

对应employees的建表sql语句如下所示：

create table employees(
       emp_id number(6) primary key,
       emp_name varchar2(20),
       sex number(1),
       birthday Date,
       salary number(8,2) check(salary>0),
       department_id number(4) references departments(department_id)
)

2.例题解析

以下例题涉及表空间的设计与管理，创建数据库对象并授权，create建表语句，update更新语句，层次查询，TOP-N查询以及创建复合分区表等知识点。
1、创建一个本地管理永久性表空间LRZYTBS1，数据文件位置、名称自定，大小为50M，区采用定制分配方式512K，段采用自动管理方式。

create tablespace LRZYTBS1 datafile
'D:\professional-course\Oracle\tablespace\LRZYTBS1.dbf' size 50M
extent management local uniform size 512K#表本地管理 区定制分配
segment space management auto;#段自动管理

2、创建一个用户sina，密码为hello,并给该用户授予创建一系列数据库对象的权限，指定默认的表空间为LRZYTBS1表空间。

create user sina identified by hello
default tablespace LRZYTBS1;
grant connect,resource,create view to sina;

3、给出employees表的创建语句。外键department_id ，salary的值大于3000元。

create table employees(
       emp_id number(6) primary key,
       emp_name varchar2(20),
       sex number(1),
       birthday Date,
       salary number(8,2) check(salary>3000),
       department_id number(4) references departments(department_id)
)

4、将各部门员工的工资修改为该员工所在部门平均工资加 1000。

update employees e
set salary=1000+(select avg(salary) from employees
where department_id=e.department_id);

5、查询工资高于本部门平均工资的员工的信息及其部门的平均工资。

select emp_id,emp_name,sex,BIRTHDAY,salary,DEPARTMENT_ID,(select avg(salary) from employees where department_id=e.department_id) 部门平均工资
from employees e
where salary>(
      select avg(salary) from employees
      where department_id=e.department_id
)

6、查询50号部门中工资排序在3~6名之间的员工信息。

select * from employees
where department_id=50
order by salary desc
offset 2 rows fetch first 4 rows with ties

7、创建一个范围-散列复合分区表，将职工工资低于10000的职工信息按所在部门分别保存在LRZYTBS1和LRZYTBS2表空间中，将职工工资高于10000低于20000的职工信息按所在部门分别分别保存在LRZYTBS3和LRZYTBS4表空间中，其他职工信息保存在LRZYTBS5表空间中。

create table employees_range_hash(
       emp_id number(6) primary key,
       emp_name varchar2(20),
       sex number(1),
       birthday Date,
       salary number(8,2) check(salary>3000),
       department_id number(4) references departments(department_id)
)
partition by range(salary)
subpartition by hash(department_id)
(
partition p1 values less than(10000)
(
subpartition p1_sub1 tablespace LRZYTBS1
subpartition p1_sub1 tablespace LRZYTBS2
)
partition p2 values less than(20000)
(
subpartition p2_sub1 tablespace LRZYTBS3
subpartition p2_sub1 tablespace LRZYTBS4
)
partition p3 values less than(MAXVALUE) tablespace LRZYTBS5
);

8.查询30号部门中工资排序前3名的员工信息

select * from employees
where department_id=30
order by salary desc fetch first 3 rows with ties;

9、查询所有员工中工资排序在5-10名之间的员工信息

select * from employees
order by salary desc
offset 4 rows fetch first 6 rows with ties;

10、以树状结构显示所有员工的上下级关系

select * from employees e,departments d
where e.department_id=d.department_id
connect by prior e.emp_name=d.dep_name