数据库中视图与序列的简单应用

根据数据库中视图与序列的相关知识解决以下两个问题：
1、将员工信息和员工工资水平封装成view视图，并通过视图查询每个工资水平有多少人。
（1）先创建员工信息表
create table a_demo_emp
(
empno varchar(64),–员工编号
ename varchar(10) not null,–员工姓名
age number(3) not null,–员工年龄
job varchar(50),–职位
mgr_no varchar(64),–上级领导工号
hiredate date not null,–入职时间
sal number(12,2),–工资
comm number(12,2)，–提成
dept_no varchar(64)，–部门号
constraint age_check check(age>18 and age<100),
constraint empno_PRIMARY primary key(empno)
)
（2）录入员工基本信息
insert into a_demo_emp(empno,ename,age,job,mgr_no,hiredate,sal,comm,dept_no)
select ‘7369’ as empno,‘SMITH’ as ename,25 as age,‘CLERK’ as job,‘7902’ as mgr_no,to_date(‘1980/12/17’,‘yyyy/mm/dd’) as hiredate,800 as sal,NULL as comm,‘20’ as dept_no from dual
union all
select ‘7499’ as empno,‘ALLEN’ as ename,30 as age,‘SALESMAN’ as job,‘7698’ as mgr_no,to_date(‘1981/2/20’,‘yyyy/mm/dd’) as hiredate,1600 as sal,300 as comm,‘30’ as dept_no from dual
union all
select ‘7521’ as empno,‘WARD’ as ename,28 as age,‘SALESMAN’ as job,‘7698’ as mgr_no,to_date(‘1981/2/22’,‘yyyy/mm/dd’) as hiredate,1250 as sal,500 as comm,‘30’ as dept_no from dual
union all
select ‘7566’ as empno,‘JONES’ as ename,22 as age,‘MANAGER’ as job,‘7839’ as mgr_no,to_date(‘1981/4/2’,‘yyyy/mm/dd’) as hiredate,2975 as sal,NULL as comm,‘20’ as dept_no from dual
union all
select ‘7654’ as empno,‘MARTIN’ as ename,27 as age,‘SALESMAN’ as job,‘7698’ as mgr_no,to_date(‘1981/9/28’,‘yyyy/mm/dd’) as hiredate,1250 as sal,1400 as comm,‘30’ as dept_no from dual
union all
select ‘7698’ as empno,‘BLAKE’ as ename,34 as age,‘MANAGER’ as job,‘7839’ as mgr_no,to_date(‘1981/5/1’,‘yyyy/mm/dd’) as hiredate,2850 as sal,NULL as comm,‘30’ as dept_no from dual
union all
select ‘7782’ as empno,‘CLARK’ as ename,42 as age,‘MANAGER’ as job,‘7839’ as mgr_no,to_date(‘1981/6/9’,‘yyyy/mm/dd’) as hiredate,2450 as sal,NULL as comm,‘10’ as dept_no from dual
union all
select ‘7788’ as empno,‘SCOTT’ as ename,31 as age,‘ANALYST’ as job,‘7566’ as mgr_no,to_date(‘1987/4/19’,‘yyyy/mm/dd’) as hiredate,3000 as sal,NULL as comm,‘20’ as dept_no from dual
union all
select ‘7839’ as empno,‘KING’ as ename,28 as age,‘PRESIDENT’ as job,NULL as mgr_no,to_date(‘1981/11/17’,‘yyyy/mm/dd’) as hiredate,5000 as sal,NULL as comm,‘10’ as dept_no from dual
union all
select ‘7844’ as empno,‘TURNER’ as ename,39 as age,‘SALESMAN’ as job,‘7698’ as mgr_no,to_date(‘1981/9/8’,‘yyyy/mm/dd’) as hiredate,1500 as sal,0 as comm,‘30’ as dept_no from dual
union all
select ‘7876’ as empno,‘ADAMS’ as ename,29 as age,‘CLERK’ as job,‘7788’ as mgr_no,to_date(‘1987/5/23’,‘yyyy/mm/dd’) as hiredate,1100 as sal,NULL as comm,‘20’ as dept_no from dual
union all
select ‘7900’ as empno,‘JAMES’ as ename,24 as age,‘CLERK’ as job,‘7698’ as mgr_no,to_date(‘1981/12/3’,‘yyyy/mm/dd’) as hiredate,950 as sal,NULL as comm,‘30’ as dept_no from dual
union all
select ‘7902’ as empno,‘FORD’ as ename,25 as age,‘ANALYST’ as job,‘7566’ as mgr_no,to_date(‘1981/12/3’,‘yyyy/mm/dd’) as hiredate,3000 as sal,NULL as comm,‘20’ as dept_no from dual
union all
select ‘7934’ as empno,‘MILLER’ as ename,29 as age,‘CLERK’ as job,‘7782’ as mgr_no,to_date(‘1982/1/23’,‘yyyy/mm/dd’) as hiredate,1300 as sal,NULL as comm,‘10’ as dept_no from dual;
select * from a_demo_emp;

（3）创建工资等级表
create table a_demo_salleve
(
grade varchar(64),–等级名字
losal number(12,2),–最低工资
hisal number(12,2)–最高工资
)
（4）录入工资等级信息
insert into a_demo_salleve(grade,losal,hisal)
select ‘1’,700,1200 from dual
union all
select ‘2’,1201,1400 from dual
union all
select ‘3’,1401,2000 from dual
union all
select ‘4’,2001,3000 from dual
union all
select ‘5’,3001,5000 from dual;
select * from a_demo_salleve;

（5）查询每位员工的工资水平，并将其封装成视图
select * from a_demo_emp e;
select * from a_demo_salleve sl;
create view emp_test2
as select e.empno,e.ename,e.sal,sl.grade
from a_demo_emp e
join a_demo_salleve sl
on e.sal>=sl.losal and e.sal<=sl.hisal;
select * from emp_test2;

检查视图是否创建成功
select * from user_objects where OBJECT_TYPE=‘VIEW’;

（6）通过视图查询每个工资水平的人数
select grade,count(-1) as Count
from emp_test2
group by grade
order by grade asc;

2、以学生信息为例，创建一个序列，并创建一个表，用序列给表添加记录。
（1）创建序列
create sequence student_id
increment by 2
start with 1
nomaxvalue
nocycle
nocache
(2）创建表
create table student
(
no number primary key,
name varchar2(30) not null,
department varchar2(30) not null
);
（3）录入数据（将其运行5次）
insert into student(no,name,department)
values (student_id.nextval,‘张三’,‘理学院’);
（4）进行查询
select * from student;

（5）录入成功