|
SQL>
SQL>
SQL> create table emp(
2 emp_id integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,shortZipCode varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,company_name varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (1,'Jones','Joe','J','1 Ave','New York','NY','11202','1111','212', '221-4333','Big Company');
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (2,'Smith','Sue','J','1 Street','New York','NY','11444','1111','212', '436-6773','Little Company');
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (3,'X','Peggy','J','1 Drive','New York','NY','45502','2222','212', '234-4444','Medium Company');
1 row created.
SQL>
SQL>
SQL> set echo off
SQL> STORE SET settings REPLACE
Wrote file settings
SQL> -- set page variables
SQL> set feedback on
SQL> set pagesize 20
SQL> set linesize 100
SQL> -- format columns
SQL> column name format a36 heading 'emp Name'
SQL> column street format a30 heading 'Street'
SQL> column city format a12 heading 'City' truncated
SQL> column state format a6 heading 'State'
SQL> -- add page header and footer
SQL> ttitle center 'emp REPORT' skip 2
SQL> btitle center 'COMPANY CONFIDENTAL'
SQL> -- write report to file
SQL> spool custrpt
SQL>
SQL> select firstname||' '|| midinit||'.'||' '|| lastname name,
2 street, city, state
3 from emp
4 order by lastname ;
emp REPORT
emp Name Street City State
------------------------------------ ------------------------------ ------------ ------
Joe J. Jones 1 Ave New York NY
Sue J. Smith 1 Street New York NY
Peggy J. X 1 Drive New York NY
COMPANY CONFIDENTAL
3 rows selected.
SQL>
SQL> spool off
SQL> clear columns
columns cleared
SQL> ttitle off
SQL> btitle off
SQL> @settings
SQL> set echo on
SQL>
SQL>
SQL> drop table emp;
Table dropped.
|