According to the database with the sequence of related knowledge to complete the problem:
1, employee information and staff wages are encapsulated into view, and through the view query each how many wages
(1) to create employee information table
The create table a_demo_emp
(
Empno varchar (64), - employee number
Ename varchar (10) not null, - employee name
The age number (3) not null, - employees age
Job varchar (50),
- positionMgr_no varchar (64), - superior leadership work number
Hiredate date not null, induction time
-Sal number (12, 2), wages,
Comm number (12, 2), - commission
Dept_no varchar (64), the number - department
The constraint age_check check (age> 18 and age<100),
The constraint empno_PRIMARY primary key (empno)
)
(2) entry staff basic information
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 the age, 'CLERK' as the job, '7902' as mgr_no, to_date (' 1980/12/17 ', 'yyyy/mm/dd) as hiredate, 800 as sal, NULL as the comm,' 20 'as dept_no from dual
Union all
Select '7499' as empno, 'Mr. ALLEN as ename, 30 as the age,' SALESMAN 'as the job,' 7698 'as mgr_no, to_date (' 1981/2/20', 'yyyy/mm/dd) as hiredate, as sal 1600, 300 as the comm,' 30 'as dept_no from dual
Union all
Select '7521' as empno, 'WARD as ename, 28 as the age,' SALESMAN 'as the job,' 7698 'as mgr_no, to_date (' 1981/2/22', 'yyyy/mm/dd) as hiredate, as sal 1250, 500 as the comm,' 30 'as dept_no from dual
Union all
Select '7566' as empno, 'JONES' as ename, 22 as the age, 'MANAGER' as the job, '7839' as mgr_no, to_date (' 1981/4/2 ', 'yyyy/mm/dd) as hiredate, 2975 as sal, NULL as the comm,' 20 'as dept_no from dual
Union all
Select '7654' as empno, 'MARTIN as ename, 27 as the age,' SALESMAN 'as the job,' 7698 'as mgr_no, to_date (' 1981/9/28', 'yyyy/mm/dd) as hiredate, as sal 1250, 1400 as the comm,' 30 'as dept_no from dual
Union all
Select '7698' as empno, 'BLAKE' as ename, 34 as the age, 'MANAGER' as the job, '7839' as mgr_no, to_date (' 1981/5/1 ', 'yyyy/mm/dd) as hiredate, 2850 as sal, NULL as the comm,' 30 'as dept_no from dual
Union all
Select '7782' as empno, 'CLARK as ename, 42 as the age,' MANAGER 'as the job,' 7839 'as mgr_no, to_date (' 1981/6/9', 'yyyy/mm/dd) as hiredate, 2450 as sal, NULL as the comm,' 10 'as dept_no from dual
Union all
Select '7788' as empno, 'SCOTT as ename, 31 as the age,' ANALYST 'as the job,' 7566 'as mgr_no, to_date (' 1987/4/19', 'yyyy/mm/dd) as hiredate, 3000 as sal, NULL as the comm,' 20 'as dept_no from dual
Union all
Select '7839' as empno, 'KING' as ename, 28 as the age, 'PRESIDENT' as the job, NULL as mgr_no, to_date (' 1981/11/17 ', 'yyyy/mm/dd) as hiredate, 5000 as sal, NULL as the comm,' 10 'as dept_no from dual
Union all
Select '7844' as empno, 'TURNER as ename, 39 as the age,' SALESMAN 'as the job,' 7698 'as mgr_no, to_date (' 1981/9/8', 'yyyy/mm/dd) as hiredate, 1500 as sal, 0 as the comm,' 30 'as dept_no from dual
Union all
Select '7876' as empno, 'ADAMS' as ename, 29 as the age, 'CLERK' as the job, '7788' as mgr_no, to_date (' 1987/5/23 ', 'yyyy/mm/dd) as hiredate, 1100 as sal, NULL as the comm,' 20 'as dept_no from dual
Union all
Select '7900' as empno, 'JAMES' as ename, 24 as the age, 'CLERK' as the job, '7698' as mgr_no, to_date (' 1981/12/3 ', 'yyyy/mm/dd) as hiredate, 950 as sal, NULL as the comm,' 30 'as dept_no from dual
Union all
Select '7902' as empno, 'FORD as ename, 25 as the age,' ANALYST 'as the job,' 7566 'as mgr_no, to_date (' 1981/12/3', 'yyyy/mm/dd) as hiredate, 3000 as sal, NULL as the comm,' 20 'as dept_no from dual
Union all
Select '7934' as empno, 'MILLER as ename, 29 as the age,' CLERK 'as the job,' 7782 'as mgr_no, to_date (' 1982/1/23', 'yyyy/mm/dd) as hiredate, 1300 as sal, NULL as the comm,' 10 'as dept_no from dual;
Select * from a_demo_emp;
(3) to create a wage scale
The create table a_demo_salleve
(
Grade a varchar (64), - grade name
Losal number (12, 13), the minimum wage
-Hisal number (12, 2), the highest salary
)
(4) entry salary grade information
Insert into a_demo_salleve (grade, losal hisal)
Select '1', 700120 0 from dual
Union all
Select '2', 1201140 0 from dual
Union all
Select '3', 1401200 0 from dual
Union all
Select the '4', 2001300 0 from dual
Union all
Select '5', 3001500 0 from dual;
Select * from a_demo_salleve;
(5) of each employee wages, and encapsulate it into view
Select * from a_demo_emp e;
Select * from a_demo_salleve sl;
The create view emp_test2
As the select e.e mpno, e.e name, e.s al, sl. Grade
The from a_demo_emp e
The join a_demo_salleve sl
On e.s al>=sl. Losal and e.s al<=sl. Hisal;
Select * from emp_test2;
To check whether the view is to create success
Select * from user_objects where OBJECT_TYPE='VIEW;
(6) through the view query the number of each level of wages
The select grade, count (1) as the count
The from emp_test2
Group by grade
The order by grade asc.

2, the student information, for example, to create a sequence, and create a table, with the sequence to add records to the table
(1) create sequence
The create sequence student_id
Increment by 2
Start with 1
Nomaxvalue
Nocycle
Nocache
(2) create a table
The create table student
(
No number, primary key,
Name varchar2 (30) not null,
Department varchar2 (30) not null
);
(3) input data (to run 5 times)
Insert into student (no, name, department)
Values (student_id nextval, 'zhang', 'cheers');
(4) the query
select * from student; nullnullnullnullnullnull