Oracle PL/SQL Tutorial/SQL PLUS Session Environment/COLUMN
Содержание
- 1 Add dollar signs to the output with "$990.99"
- 2 COLUMN first_name FORMAT A10 WORD_WRAP HEADING "Name" JUSTIFY CENTER
- 3 COLUMN first_name FORMAT A13 WORD_WRAPPED
- 4 COLUMN first_name HEADING FIRST_NAME FORMAT A13 WORD_WRAPPED
- 5 COLUMN format in action: COLUMN City FORMAT a6
- 6 Column LIKE another Column
- 7 COLUMN Salary FORMAT 909.99
- 8 COLUMN salary FORMAT $99.99 HEADING "Salary" JUSTIFY RIGHT
- 9 Copy column format with "col ... like"
- 10 For alphanumeric columns, if the column is too short, it will be displayed on multiple lines.
- 11 format interval type column
- 12 FORMAT number column as 999,999
- 13 Format the number column with COLUMN salary FORMAT 99999999
- 14 Formatting Columns
- 15 If the column format is too small for a number column, "#" will be used
- 16 If there are decimals or if commas are desired, use the "99999.99"
- 17 Numbers can also be output with leading zeros or dollar signs
- 18 Reporting Tools in Oracle"s SQL*Plus: COLUMN
- 19 SET string to display when value is NULL
- 20 Shorten the varchar column format with COLUMN command:
- 21 To undo all column formatting: CLEAR COLUMNS
- 22 Two digits: COLUMN id FORMAT 99
Add dollar signs to the output with "$990.99"
-- create demo table
create table Employee(
ID VARCHAR2(4 BYTE) NOT NULL,
First_Name VARCHAR2(10 BYTE),
Last_Name VARCHAR2(10 BYTE),
Start_Date DATE,
End_Date DATE,
Salary Number(8,2),
City VARCHAR2(10 BYTE),
Description VARCHAR2(15 BYTE)
)
/
-- prepare data
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
/
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
/
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
/
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
/
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
/
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
/
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
/
insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
/
-- display data in the table
select * from Employee
/
COLUMN Salary FORMAT $99999990.99
/
select * from Employee
/
clear columns;
-- clean the table
drop table Employee
/
COLUMN first_name FORMAT A10 WORD_WRAP HEADING "Name" JUSTIFY CENTER
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL> COLUMN first_name FORMAT A10 WORD_WRAP HEADING "Name" JUSTIFY CENTER
SQL>
SQL> select * from employee;
ID Name LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL> clear columns;
columns cleared
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
COLUMN first_name FORMAT A13 WORD_WRAPPED
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> COLUMN first_name FORMAT A13 WORD_WRAPPED
SQL> /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> clear columns;
columns cleared
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
COLUMN first_name HEADING FIRST_NAME FORMAT A13 WORD_WRAPPED
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> COLUMN first_name HEADING FIRST_NAME FORMAT A13 WORD_WRAPPED
SQL> /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ------------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> clear columns;
columns cleared
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
COLUMN format in action: COLUMN City FORMAT a6
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto
Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver
Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver
Tester
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver
Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver
Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York
Tester
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York
Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver
Tester
8 rows selected.
SQL>
SQL>
SQL> COLUMN City FORMAT a6
SQL> /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ------ ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toront Programmer
o
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancou Tester
ver
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancou Tester
ver
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancou Manager
ver
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ------ ---------------
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancou Tester
ver
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New Yo Tester
rk
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New Yo Manager
rk
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancou Tester
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ------ ---------------
ver
8 rows selected.
SQL>
SQL> select * from employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ------ ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toront Programmer
o
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancou Tester
ver
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancou Tester
ver
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancou Manager
ver
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ------ ---------------
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancou Tester
ver
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New Yo Tester
rk
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New Yo Manager
rk
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancou Tester
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ------ ---------------
ver
8 rows selected.
SQL>
SQL> clear columns;
columns cleared
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
Column LIKE another Column
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> COLUMN portion FORMAT 9.9999
SQL> COLUMN portion2 LIKE portion
SQL> SELECT first_name, salary,
2 salary/SUM(salary) OVER() Portion,
3 RATIO_TO_REPORT(salary) OVER() Portion2
4 FROM employee
5 ORDER BY salary
6 /
FIRST_NAME SALARY PORTION PORTION2
---------- ---------- ------- --------
James 1232.78 .0378 .0378
Jason 1234.56 .0379 .0379
Robert 2334.78 .0717 .0717
Celia 2344.78 .0720 .0720
Linda 4322.78 .1327 .1327
James 6544.78 .2009 .2009
Alison 6661.78 .2045 .2045
David 7897.78 .2425 .2425
8 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
COLUMN Salary FORMAT 909.99
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 61.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 644.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 61.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 644.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> COLUMN Salary FORMAT 909.99
SQL> /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 01.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 61.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 644.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 ####### Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 ####### Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 ####### New York Tester
07 David Larry 31-DEC-90 12-FEB-98 ####### New York Manager
08 James Cat 17-SEP-96 15-APR-02 ####### Vancouver Tester
8 rows selected.
SQL> Select salary from Employee
2 /
SALARY
-------
01.56
61.78
644.78
#######
#######
#######
#######
#######
8 rows selected.
SQL>
SQL> clear columns;
columns cleared
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
COLUMN salary FORMAT $99.99 HEADING "Salary" JUSTIFY RIGHT
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL> COLUMN salary FORMAT $99.99 HEADING "Salary" JUSTIFY RIGHT;
SQL>
SQL> select * from employee;
ID FIRST_NAME LAST_NAME START_DAT END_DATE Salary CITY DESCRIPTION
---- ---------- ---------- --------- --------- ------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 ####### Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 ####### Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 ####### Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 ####### Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 ####### Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 ####### New York Tester
07 David Larry 31-DEC-90 12-FEB-98 ####### New York Manager
08 James Cat 17-SEP-96 15-APR-02 ####### Vancouver Tester
8 rows selected.
SQL> clear columns;
columns cleared
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL> clear columns;
columns cleared
SQL>
SQL>
Copy column format with "col ... like"
SQL>
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL> select empno, ename, bdate
2 , sal as salary
3 , comm as commission
4 from emp;
Enter...
EMPNO ENAME BDATE SALARY COMMISSION
------ -------- ---------- ------ ----------
1 Tom 17-12-1965 800 [N/A]
2 Jack 20-02-1961 1600 300
3 Wil 22-02-1962 1250 500
4 Jane 02-04-1967 2975 [N/A]
5 Mary 28-09-1956 1250 1400
6 Black 01-11-1963 2850 [N/A]
7 Chris 09-06-1965 2450 [N/A]
8 Smart 26-11-1959 3000 [N/A]
9 Peter 17-11-1952 5000 [N/A]
10 Take 28-09-1968 1500 0
11 Ana 30-12-1966 1100 [N/A]
12 Jane 03-12-1969 800 [N/A]
13 Fake 13-02-1959 3000 [N/A]
14 Mike 23-01-1962 1300 [N/A]
14 rows selected.
SQL> col ename format a20 hea lname jus c
SQL> col salary format $9999.99
SQL> col commission like salary
SQL> col salary heading month|salary
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
For alphanumeric columns, if the column is too short, it will be displayed on multiple lines.
For example, if the COLUMN format for first_name were too short, as shown below:
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ----------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> COLUMN First_Name FORMAT a4
SQL> /
ID FIRS LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---- ---------- --------- --------- ---------- ---------- ---------------
01 Jaso Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
n
02 Alis Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
on
03 Jame Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
s
04 Celi Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
a
05 Robe Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
rt
06 Lind Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
a
07 Davi Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
d
08 Jame Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
s
8 rows selected.
SQL> SELECT * FROM employee
2 /
ID FIRS LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---- ---------- --------- --------- ---------- ---------- ---------------
01 Jaso Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
n
02 Alis Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
on
03 Jame Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
s
04 Celi Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
a
05 Robe Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
rt
06 Lind Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
a
07 Davi Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
d
08 Jame Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
s
8 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
format interval type column
SQL> create table myTable(
2 break_time interval day(1) to second(2)
3 );
Table created.
SQL>
SQL>
SQL> insert into myTable (break_time )
2 values (TIMESTAMP "2001-09-03 12:47:00.000000" - TIMESTAMP "2001-09-03 13:13:00.000000" );
1 row created.
SQL>
SQL> insert into myTable ( break_time )
2 values ( TIMESTAMP "2001-09-03 13:35:00.000000" - TIMESTAMP "2001-09-03 13:39:00.000000" );
1 row created.
SQL>
SQL> insert into myTable ( break_time )
2 values (TIMESTAMP "2001-09-03 16:30:00.000000" - TIMESTAMP "2001-09-03 17:00:00.000000" );
1 row created.
SQL>
SQL> insert into myTable ( break_time )
2 values ( TIMESTAMP "2001-09-03 17:00:00.000000" - TIMESTAMP "2001-09-03 17:30:00.000000" );
1 row created.
SQL>
SQL> column break_time format a30
SQL>
SQL> select * from myTable;
BREAK_TIME
------------------------------
-0 00:26:00.00
-0 00:04:00.00
-0 00:30:00.00
-0 00:30:00.00
4 rows selected.
SQL>
SQL> drop table myTable;
Table dropped.
FORMAT number column as 999,999
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRS LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---- ---------- --------- --------- ------ ---------- ---------------
01 Jaso Martin 25-JUL-96 25-JUL-06 ### Toronto Programmer
n
02 Alis Mathews 21-MAR-76 21-FEB-86 ### Vancouver Tester
on
03 Jame Smith 12-DEC-78 15-MAR-90 ### Vancouver Tester
s
04 Celi Rice 24-OCT-82 21-APR-99 ### Vancouver Manager
a
05 Robe Black 15-JAN-84 08-AUG-98 ### Vancouver Tester
rt
06 Lind Green 30-JUL-87 04-JAN-96 ### New York Tester
a
07 Davi Larry 31-DEC-90 12-FEB-98 ### New York Manager
d
08 Jame Cat 17-SEP-96 15-APR-02 ### Vancouver Tester
s
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> COLUMN Salary FORMAT 999,999
SQL> /
ID FIRS LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---- ---------- --------- --------- -------- ---------- ---------------
01 Jaso Martin 25-JUL-96 25-JUL-06 1,235 Toronto Programmer
n
02 Alis Mathews 21-MAR-76 21-FEB-86 6,662 Vancouver Tester
on
03 Jame Smith 12-DEC-78 15-MAR-90 6,545 Vancouver Tester
s
04 Celi Rice 24-OCT-82 21-APR-99 2,345 Vancouver Manager
a
05 Robe Black 15-JAN-84 08-AUG-98 2,335 Vancouver Tester
rt
06 Lind Green 30-JUL-87 04-JAN-96 4,323 New York Tester
a
07 Davi Larry 31-DEC-90 12-FEB-98 7,898 New York Manager
d
08 Jame Cat 17-SEP-96 15-APR-02 1,233 Vancouver Tester
s
8 rows selected.
SQL> SELECT salary FROM employee
2 /
SALARY
--------
1,235
6,662
6,545
2,345
2,335
4,323
7,898
1,233
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Format the number column with COLUMN salary FORMAT 99999999
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRS LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---- ---------- --------- --------- ---------- ---------- ---------------
01 Jaso Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
n
02 Alis Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
on
03 Jame Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
s
04 Celi Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
a
05 Robe Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
rt
06 Lind Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
a
07 Davi Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
d
08 Jame Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
s
8 rows selected.
SQL>
SQL>
SQL> COLUMN salary FORMAT 99999999
SQL> /
ID FIRS LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---- ---------- --------- --------- --------- ---------- ---------------
01 Jaso Martin 25-JUL-96 25-JUL-06 1235 Toronto Programmer
n
02 Alis Mathews 21-MAR-76 21-FEB-86 6662 Vancouver Tester
on
03 Jame Smith 12-DEC-78 15-MAR-90 6545 Vancouver Tester
s
04 Celi Rice 24-OCT-82 21-APR-99 2345 Vancouver Manager
a
05 Robe Black 15-JAN-84 08-AUG-98 2335 Vancouver Tester
rt
06 Lind Green 30-JUL-87 04-JAN-96 4323 New York Tester
a
07 Davi Larry 31-DEC-90 12-FEB-98 7898 New York Manager
d
08 Jame Cat 17-SEP-96 15-APR-02 1233 Vancouver Tester
s
8 rows selected.
SQL> SELECT salary FROM employee
2 /
SALARY
---------
1235
6662
6545
2345
2335
4323
7898
1233
8 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Formatting Columns
COLUMN command formats the display of column headings and column data.
The simplified syntax for the COLUMN command is as follows:
COL[UMN] {column | alias} [options]
where
- column specifies the column name.
- alias specifies the column alias to be formatted. You can then reference your alias in the COLUMN command.
- options specifies one or more options to be used to format the column or alias.
- There are a number of options you can use with the COLUMN command.
The following table shows some of these options.
Option Description FOR[MAT] format Sets the format for the display of the column or alias to that specified in the format string. HEA[DING] heading Sets the text for the heading of the column or alias to that specified in the heading string. JUS[TIFY] [{ left | center | right }] Aligns the column output to the left, center, or right. WRA[PPED] Wraps the end of a string onto the next line of output. This option may cause individual words to be split across multiple lines. WOR[D_WRAPPED] Similar to the WRAPPED option except that individual words aren"t split across two lines. CLE[AR] Clears any formatting of columns (sets the formatting back to the default).
The format string in the previous table may take a number of formatting parameters.
The parameters you specify depend on the data stored in your column:
- If your column contains characters, you can use Ax to format the characters, where x specifies the width for the characters. For example, A12 sets the width to 12 characters.
- If your column contains numbers, you can use any of the number formats. For example, $99.99 sets the format to a dollar sign, followed by two digits, the decimal point, and another two digits.
- If your column contains a date, you can use any of the date formats. For example, MM-DD-YYYY sets the format to a two-digit month (MM), a two-digit day (DD), and a four-digit year (YYYY).
Advanced SQL Functions in Oracle 10g (Wordware Applications Library)
(Paperback)
by Richard Earp (Author)
# Paperback: 350 pages
# Publisher: Wordware Publishing, Inc. (January 25, 2006)
# Language: English
# ISBN-10: 1598220217
# ISBN-13: 978-1598220216
If the column format is too small for a number column, "#" will be used
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRS LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---- ---------- --------- --------- --------- ---------- ---------------
01 Jaso Martin 25-JUL-96 25-JUL-06 1235 Toronto Programmer
n
02 Alis Mathews 21-MAR-76 21-FEB-86 6662 Vancouver Tester
on
03 Jame Smith 12-DEC-78 15-MAR-90 6545 Vancouver Tester
s
04 Celi Rice 24-OCT-82 21-APR-99 2345 Vancouver Manager
a
05 Robe Black 15-JAN-84 08-AUG-98 2335 Vancouver Tester
rt
06 Lind Green 30-JUL-87 04-JAN-96 4323 New York Tester
a
07 Davi Larry 31-DEC-90 12-FEB-98 7898 New York Manager
d
08 Jame Cat 17-SEP-96 15-APR-02 1233 Vancouver Tester
s
8 rows selected.
SQL>
SQL>
SQL>
SQL> COLUMN salary FORMAT 99
SQL> /
ID FIRS LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---- ---------- --------- --------- ------ ---------- ---------------
01 Jaso Martin 25-JUL-96 25-JUL-06 ### Toronto Programmer
n
02 Alis Mathews 21-MAR-76 21-FEB-86 ### Vancouver Tester
on
03 Jame Smith 12-DEC-78 15-MAR-90 ### Vancouver Tester
s
04 Celi Rice 24-OCT-82 21-APR-99 ### Vancouver Manager
a
05 Robe Black 15-JAN-84 08-AUG-98 ### Vancouver Tester
rt
06 Lind Green 30-JUL-87 04-JAN-96 ### New York Tester
a
07 Davi Larry 31-DEC-90 12-FEB-98 ### New York Manager
d
08 Jame Cat 17-SEP-96 15-APR-02 ### Vancouver Tester
s
8 rows selected.
SQL> SELECT * FROM employee
2 /
ID FIRS LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---- ---------- --------- --------- ------ ---------- ---------------
01 Jaso Martin 25-JUL-96 25-JUL-06 ### Toronto Programmer
n
02 Alis Mathews 21-MAR-76 21-FEB-86 ### Vancouver Tester
on
03 Jame Smith 12-DEC-78 15-MAR-90 ### Vancouver Tester
s
04 Celi Rice 24-OCT-82 21-APR-99 ### Vancouver Manager
a
05 Robe Black 15-JAN-84 08-AUG-98 ### Vancouver Tester
rt
06 Lind Green 30-JUL-87 04-JAN-96 ### New York Tester
a
07 Davi Larry 31-DEC-90 12-FEB-98 ### New York Manager
d
08 Jame Cat 17-SEP-96 15-APR-02 ### Vancouver Tester
s
8 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
If there are decimals or if commas are desired, use the "99999.99"
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRS LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---- ---------- --------- --------- -------- ---------- ---------------
01 Jaso Martin 25-JUL-96 25-JUL-06 1,235 Toronto Programmer
n
02 Alis Mathews 21-MAR-76 21-FEB-86 6,662 Vancouver Tester
on
03 Jame Smith 12-DEC-78 15-MAR-90 6,545 Vancouver Tester
s
04 Celi Rice 24-OCT-82 21-APR-99 2,345 Vancouver Manager
a
05 Robe Black 15-JAN-84 08-AUG-98 2,335 Vancouver Tester
rt
06 Lind Green 30-JUL-87 04-JAN-96 4,323 New York Tester
a
07 Davi Larry 31-DEC-90 12-FEB-98 7,898 New York Manager
d
08 Jame Cat 17-SEP-96 15-APR-02 1,233 Vancouver Tester
s
8 rows selected.
SQL>
SQL>
SQL>
SQL> COLUMN Salary FORMAT 99999.99
SQL> /
ID FIRS LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---- ---------- --------- --------- --------- ---------- ---------------
01 Jaso Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
n
02 Alis Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
on
03 Jame Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
s
04 Celi Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
a
05 Robe Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
rt
06 Lind Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
a
07 Davi Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
d
08 Jame Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
s
8 rows selected.
SQL> select * from Employee
2 /
ID FIRS LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---- ---------- --------- --------- --------- ---------- ---------------
01 Jaso Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
n
02 Alis Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
on
03 Jame Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
s
04 Celi Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
a
05 Robe Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
rt
06 Lind Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
a
07 Davi Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
d
08 Jame Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
s
8 rows selected.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Numbers can also be output with leading zeros or dollar signs
SQL>
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), .78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 .78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> COLUMN Salary FORMAT 990.99
SQL> /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 ####### Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 ####### Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 ####### Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 ####### Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 0.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 ####### New York Tester
07 David Larry 31-DEC-90 12-FEB-98 ####### New York Manager
08 James Cat 17-SEP-96 15-APR-02 ####### Vancouver Tester
8 rows selected.
SQL> select salary from Employee
2 /
SALARY
-------
#######
#######
#######
#######
0.78
#######
#######
#######
8 rows selected.
SQL>
SQL> clear columns;
columns cleared
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
Reporting Tools in Oracle"s SQL*Plus: COLUMN
Change the way a column displays by using the COLUMN command
The COLUMN command has the following syntax:
COLUMN column-name FORMAT format-specification
where
column-name is the column heading you wish to format.
The format-specification uses a"s for text and 9"s for numbers, like this:
an - text format for a field width of n
9n - numeric format with no decimals for a field width of numbers of size n
29. 4. COLUMN 29. 4. 1. Reporting Tools in Oracle"s SQL*Plus: COLUMN 29. 4. 2. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/FormattingColumns.htm">Formatting Columns</a> 29. 4. 3. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/COLUMNformatinactionCOLUMNCityFORMATa6.htm">COLUMN format in action: COLUMN City FORMAT a6</a> 29. 4. 4. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/ShortenthevarcharcolumnformatwithCOLUMNcommand.htm">Shorten the varchar column format with COLUMN command:</a> 29. 4. 5. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/Foralphanumericcolumnsifthecolumnistooshortitwillbedisplayedonmultiplelines.htm">For alphanumeric columns, if the column is too short, it will be displayed on multiple lines.</a> 29. 4. 6. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/FormatthenumbercolumnwithCOLUMNsalaryFORMAT99999999.htm">Format the number column with COLUMN salary FORMAT 99999999</a> 29. 4. 7. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/Ifthecolumnformatistoosmallforanumbercolumnwillbeused.htm">If the column format is too small for a number column, "#" will be used</a> 29. 4. 8. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/FORMATnumbercolumnas999999.htm">FORMAT number column as 999,999</a> 29. 4. 9. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/Iftherearedecimalsorifcommasaredesiredusethe9999999.htm">If there are decimals or if commas are desired, use the "99999.99"</a> 29. 4. 10. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/Numberscanalsobeoutputwithleadingzerosordollarsigns.htm">Numbers can also be output with leading zeros or dollar signs</a> 29. 4. 11. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/Adddollarsignstotheoutputwith99099.htm">Add dollar signs to the output with "$990.99"</a> 29. 4. 12. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/COLUMNSalaryFORMAT90999.htm">COLUMN Salary FORMAT 909.99</a> 29. 4. 13. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/ToundoallcolumnformattingCLEARCOLUMNS.htm">To undo all column formatting: CLEAR COLUMNS</a> 29. 4. 14. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/ColumnLIKEanotherColumn.htm">Column LIKE another Column</a> 29. 4. 15. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/TwodigitsCOLUMNidFORMAT99.htm">Two digits: COLUMN id FORMAT 99</a> 29. 4. 16. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/COLUMNfirstnameHEADINGFIRSTNAMEFORMATA13WORDWRAPPED.htm">COLUMN first_name HEADING FIRST_NAME FORMAT A13 WORD_WRAPPED</a> 29. 4. 17. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/COLUMNfirstnameFORMATA13WORDWRAPPED.htm">COLUMN first_name FORMAT A13 WORD_WRAPPED</a> 29. 4. 18. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/COLUMNfirstnameFORMATA10WORDWRAPHEADINGNameJUSTIFYCENTER.htm">COLUMN first_name FORMAT A10 WORD_WRAP HEADING "Name" JUSTIFY CENTER</a> 29. 4. 19. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/COLUMNsalaryFORMAT9999HEADINGSalaryJUSTIFYRIGHT.htm">COLUMN salary FORMAT $99.99 HEADING "Salary" JUSTIFY RIGHT</a> 29. 4. 20. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/Copycolumnformatwithcollike.htm">Copy column format with "col ... like"</a> 29. 4. 21. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/formatintervaltypecolumn.htm">format interval type column</a> 29. 4. 22. <A href="/Tutorial/Oracle/0580__SQL-PLUS-Session-Environment/SETstringtodisplaywhenvalueisNULL.htm">SET string to display when value is NULL</a>
SET string to display when value is NULL
SQL>
SQL> CREATE TABLE emp (
2 emp_id NUMBER,
3 ename VARCHAR2(40),
4 hire_date DATE DEFAULT sysdate,
5 end_date DATE,
6 rate NUMBER(5,2),
7 CONSTRAINT emp_pk PRIMARY KEY (emp_id)
8 );
Table created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL>
SQL> SET ECHO ON
SQL> COLUMN ename FORMAT A18
SQL> INSERT INTO emp (emp_id, ename)
2 VALUES (116, "Roxolana Lisovsky");
1 row created.
SQL>
SQL> SET NULL ***NULL***
SQL>
SQL> SELECT emp_id, ename,
2 hire_date, end_date
3 FROM emp
4 WHERE emp_id = 116;
116 Roxolana Lisovsky 26-OCT-09
***NULL**
*
1 row selected.
SQL>
SQL> SET NULL ""
SQL>
SQL> drop table emp;
Table dropped.
Shorten the varchar column format with COLUMN command:
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ----------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> COLUMN First_Name FORMAT a11
SQL> /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ----------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ----------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
To undo all column formatting: CLEAR COLUMNS
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060
725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860
221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900
315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990
421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980
808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960
104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980
212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,
Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020
415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> COLUMN Salary FORMAT $990.99
SQL> /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- -------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 ######## Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 ######## Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 ######## Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 ######## Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 ######## Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 ######## New York Tester
07 David Larry 31-DEC-90 12-FEB-98 ######## New York Manager
08 James Cat 17-SEP-96 15-APR-02 ######## Vancouver Tester
8 rows selected.
SQL> Select salary from Employee
2 /
SALARY
--------
########
########
########
########
########
########
########
########
8 rows selected.
SQL>
SQL> CLEAR COLUMNS
columns cleared
SQL> /
SALARY
----------
1234.56
6661.78
6544.78
2344.78
2334.78
4322.78
7897.78
1232.78
8 rows selected.
SQL> Select salary from Employee
2 /
SALARY
----------
1234.56
6661.78
6544.78
2344.78
2334.78
4322.78
7897.78
1232.78
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
Two digits: COLUMN id FORMAT 99
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL primary key,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> COLUMN id FORMAT 99
SQL> /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.