Oracle作業1

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;