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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/103282.html
標籤：其他
下一篇：CentOS7-8、CentOS8.2、阿里云服務器，無GUI圖形化界面，CentOS使用RPM方式安裝Oracle 19c