根據資料庫中試圖與序列的相關知識完成一下問題：
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;
uj5u.com熱心網友回復：
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/51051.html
標籤：基礎和管理
上一篇：oracle