1

编辑一个脚本文件，运行后产生一份报表。

内容和要求如下：显示所有薪水低于3000的雇员的工作、姓名和工资。

添加一个居中显示的表头“Employee Report”和一个居中的页脚“Confidential”。

重新命名工作列标题为“Job Category”，分两行。

重新命名姓名列标题为“Employee”，

重新命名工资列标题为“Salary”并且格式化为$9,999.99。

在sqlplus窗口中实现并截图

create table emp(EMPNO number(4) not null,ENAME varchar2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7,2),COMM  NUMBER(7,2),DEPTNO NUMBER(2));

insert into emp values(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,null,20);

insert into emp values(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);

insert into emp values(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);

insert into emp values(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);

insert into emp values(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);

insert into emp values(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);

insert into emp values(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);

insert into emp values(7788,'SCOTT','ANALYST',7566,to_date('13-7-87','dd-mm-rr')-85,3000,NULL,20);

insert into emp values(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);

insert into emp values(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);

insert into emp values(7876,'ADAMS','CLERK',7788,to_date('13-7-87', 'dd-mm-rr')-51,1100,NULL,20);

insert into emp values(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);

insert into emp values(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);

insert into emp values(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

select JOB,ENAME,SAL from EMP where SAL < 3000;

TTI center 'Employee Report';

BTI center 'Confidential';

alter table emp rename column JOB to "Job Category";

col "Job Category" heading 'Job|Category';

alter table emp rename column ENAME to Employee;

alter table emp rename column SAL to "Salary";

col salary format $9,999.99;

select * from emp;

2

显示部门号、雇员号、雇员名、雇员工资，并按照部门进行排序

在sqlplus窗口中实现并截图

要求如下：

1、 页头为“雇员报表”

2、 每个部门只显示一次部门号

3、 设置行宽60个字符

4、 设置页的总计显示行数为40

5、 显示雇员工资时要带有本地货币符号

6、 在select语句之后清除所有选项设置

实现

select DEPTNO,EMPNO,ENAME,SAL from emp order by deptno ASC;

TTI “雇员报表”;

break on deptno skip 0;

set linesize 60;

set pagesize 40;

col SAL format L9,999,99;

select DEPTNO,EMPNO,ENAME,SAL from emp order by deptno ASC;

清除

TTI off;

set linesize 80;

set pagesize 14;

clear break;

column sal clear;

select DEPTNO,EMPNO,ENAME,SAL from emp order by deptno ASC;