Oracle PL/SQL/SQL Plus/Column
Содержание
- 1 Adding a group separator
- 2 Aligning decimals
- 3 Column data is aligned by type
- 4 COLUMN fname heading "emp|Name" format a10
- 5 Column format $9,999.99
- 6 column format: ascii type, 26 letter long
- 7 Column heading format a13
- 8 COLUMN id heading "emp|Number" format 9999
- 9 column localtimestamp format a28
- 10 column number format
- 11 COLUMN Salary heading "Current|Salary" format $9999.99
- 12 Copy column format with "col ... like"
- 13 Disable the column formatting
- 14 Including a currency symbol
- 15 JUSTIFY CENTER
- 16 JUSTIFY RIGHT
- 17 Set column format before doing the query
- 18 Set column heading with column command
- 19 Set column separation with colsep
- 20 Set number column format
- 21 SET string to display when value is NULL
- 22 Use a13 to set the column length during displaying
- 23 Use "format a30 heading" to define column name
- 24 Word Wrapped column format
- 25 Wrapping text
Adding a group separator
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 quantity_on_hand NUMBER(5,0),
5 last_stock_date DATE);
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null);
1 row created.
SQL>
SQL>
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL>
SQL> COLUMN quantity_on_hand FORMAT 99,999
SQL>
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL>
SQL>
SQL>
SQL> DROP TABLE product;
Table dropped.
SQL>
Aligning decimals
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 quantity_on_hand NUMBER(5,0),
5 last_stock_date DATE);
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null);
1 row created.
SQL>
SQL>
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL>
SQL> COLUMN product_price FORMAT 9999.99
SQL>
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL>
SQL>
SQL> DROP TABLE product;
Table dropped.
Column data is aligned by type
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", "Coder", 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 "1962-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", "Coder", 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", "Coder", 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", "Coder", 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>
SQL>
SQL> select ename, init, job, sal
2 from emp
3 where deptno = 30;
ENAME INIT JOB SAL
-------- ----- -------- ----------
Jack JAM Tester 1600
Wil TF Tester 1250
Mary P Tester 1250
Black R Designer 2850
Take JJ Tester 1500
Jane R Manager 800
6 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
COLUMN fname heading "emp|Name" format a10
SQL>
SQL>
SQL> -- create demo table
SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 fname VARCHAR2(10 BYTE),
4 lname 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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> COLUMN fname heading "emp|Name" format a10
SQL>
SQL> select fname from emp;
emp
Name
----------
Jason
Alison
James
Celia
Robert
Linda
David
James
8 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
Column format $9,999.99
SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL>
SQL> select ename, sal
2 from emp
3 where ename like "A%"
4 /
Employee Name Salary
------------- ----------
ALLEN 1600
ADAMS 1100
SQL> column sal format $9,999.99
SQL>
SQL> select ename, sal
2 from emp
3 where ename like "A%";
Employee Name Salary
------------- ----------
ALLEN $1,600.00
ADAMS $1,100.00
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
column format: ascii type, 26 letter long
SQL>
SQL> create table courses
2 ( code VARCHAR2(6) constraint C_PK primary key
3 , description VARCHAR2(30)
4 , category CHAR(3)
5 , duration NUMBER(2)
6 ) ;
Table created.
SQL> insert into courses values("SQL","SQL","GEN",4);
1 row created.
SQL> insert into courses values("OAU","Java","GEN",1);
1 row created.
SQL> insert into courses values("JAV","C++","BLD",4);
1 row created.
SQL> insert into courses values("PLS","C","BLD",1);
1 row created.
SQL> insert into courses values("XML","XML","BLD",2);
1 row created.
SQL> insert into courses values("ERM","ERP","DSG",3);
1 row created.
SQL> insert into courses values("PMT","ERP","DSG",1);
1 row created.
SQL> insert into courses values("RSD","jQuery","DSG",2);
1 row created.
SQL> insert into courses values("PRO","Linux","DSG",5);
1 row created.
SQL> insert into courses values("GEN","Oracle","DSG",4);
1 row created.
SQL>
SQL>
SQL> select * from courses
2 where category = "BLD";
CODE DESCRIPTION CAT DURATION
------ -------------------------- --- ----------
JAV C++ BLD 4
PLS C BLD 1
XML XML BLD 2
SQL> COL description FORMAT a26
SQL> /
CODE DESCRIPTION CAT DURATION
------ -------------------------- --- ----------
JAV C++ BLD 4
PLS C BLD 1
XML XML BLD 2
SQL>
SQL> drop table courses;
Table dropped.
SQL>
Column heading format a13
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> select * from emp;
EMPNO Employee JOB MGR HIREDATE Salary COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO Employee JOB MGR HIREDATE Salary COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
SQL> column ename heading "Employee Name"
SQL> /
EMPNO Employee N JOB MGR HIREDATE Salary COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO Employee N JOB MGR HIREDATE Salary COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> column ename format a13 heading "Employee Name"
SQL> /
EMPNO Employee Name JOB MGR HIREDATE Salary COMM DEPTNO
---------- ------------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO Employee Name JOB MGR HIREDATE Salary COMM DEPTNO
---------- ------------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
SQL> select * from emp;
EMPNO Employee Name JOB MGR HIREDATE Salary COMM DEPTNO
---------- ------------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO Employee Name JOB MGR HIREDATE Salary COMM DEPTNO
---------- ------------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
COLUMN id heading "emp|Number" format 9999
SQL>
SQL>
SQL> -- create demo table
SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 fname VARCHAR2(10 BYTE),
4 lname 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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> COLUMN id heading "emp|Number" format 9999
SQL>
SQL> -- display data in the table
SQL> select * from emp
2 /
emp
Numb FNAME LNAME 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
emp
Numb FNAME LNAME 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
emp
Numb FNAME LNAME START_DAT END_DATE SALARY CITY
---- ---------- ---------- --------- --------- ---------- ----------
DESCRIPTION
---------------
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> drop table emp;
Table dropped.
column localtimestamp format a28
SQL>
SQL> select localtimestamp from dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
16-JUN-08 01.26.59.426000 PM
1 row selected.
SQL>
SQL> select localtimestamp, current_timestamp
2 from dual;
LOCALTIMESTAMP
---------------------------------------------------------------------------
CURRENT_TIMESTAMP
------------------------------------
16-JUN-08 01.26.59.496000 PM
16-JUN-08 01.26.59.496000 PM -11:00
1 row selected.
SQL>
SQL> alter session set time_zone = "-08:00";
Session altered.
SQL>
SQL> select localtimestamp, to_char(sysdate, "DD-MON-YY HH:MI:SS AM") "SYSDATE"
2 from dual;
LOCALTIMESTAMP SYSDATE
--------------------------------------------------------------------------- ---------------------
16-JUN-08 04.26.59.686000 PM 16-JUN-08 05:26:59 PM
1 row selected.
SQL>
SQL> --
column number format
SQL> column Total_IO format 999999999
SQL> column Weight format 999.99
COLUMN Salary heading "Current|Salary" format $9999.99
SQL>
SQL> -- create demo table
SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 fname VARCHAR2(10 BYTE),
4 lname 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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 emp(ID, fname, lname, 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> COLUMN Salary heading "Current|Salary" format $9999.99
SQL>
SQL> select salary from emp;
Current
Salary
---------
$1234.56
$6661.78
$6544.78
$2344.78
$2334.78
$4322.78
$7897.78
$1232.78
8 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
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>
Disable the column formatting
SQL>
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 quantity_on_hand NUMBER(5,0),
5 last_stock_date DATE);
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null);
1 row created.
SQL>
SQL>
SQL>
SQL> COLUMN product_name OFF
SQL> COLUMN product_price OFF
SQL> COLUMN quantity_on_hand OFF
SQL> COLUMN last_stock_date OFF
SQL>
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL>
SQL>
SQL>
SQL> DROP TABLE product;
Table dropped.
SQL>
Including a currency symbol
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 quantity_on_hand NUMBER(5,0),
5 last_stock_date DATE);
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null);
1 row created.
SQL>
SQL>
SQL> COLUMN product_price FORMAT $99.99
SQL>
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL>
SQL>
SQL>
SQL> DROP TABLE product;
Table dropped.
JUSTIFY CENTER
Formatting headings
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 quantity_on_hand NUMBER(5,0),
5 last_stock_date DATE);
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL>
SQL> COLUMN product_name HEADING "Product|Name" JUSTIFY CENTER
SQL>
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL>
SQL>
SQL>
SQL> DROP TABLE product;
Table dropped.
JUSTIFY RIGHT
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 quantity_on_hand NUMBER(5,0),
5 last_stock_date DATE);
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL>
SQL> COLUMN product_name FORMAT A10 WORD_WRAP HEADING "Name" JUSTIFY CENTER
SQL> COLUMN product_price FORMAT $99.99 HEADING "Price" JUSTIFY RIGHT
SQL> COLUMN quantity_on_hand FORMAT 99,999 HEADING "On|Hand" JUSTIFY RIGHT
SQL> COLUMN last_stock_date HEADING "Last|Stock|Date" JUSTIFY RIGHT
SQL>
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL>
SQL>
SQL> DROP TABLE product;
Table dropped.
SQL>
Set column format before doing the query
SQL>
SQL>
SQL> create table student (
2 student_id number primary key,
3 student_name varchar2(25),
4 student_year varchar2(15),
5 student_major varchar2(25) );
Table created.
SQL>
SQL> insert into student values (1,"Tom","First","Art");
1 row created.
SQL> insert into student values (2,"Jack","First","Med");
1 row created.
SQL> insert into student values (3,"Peter","First","History");
1 row created.
SQL> insert into student values (4,"Jason","First","Science");
1 row created.
SQL> insert into student values (5,"Joe","Second","Education");
1 row created.
SQL> insert into student values (6,"Cat","Second","Finance");
1 row created.
SQL> insert into student values (7,"Sill","Second","Art");
1 row created.
SQL> insert into student values (8,"Bill","Second","Med");
1 row created.
SQL> insert into student values (9,"Mary","Third","History");
1 row created.
SQL>
SQL>
SQL> create table class (
2 class_id number primary key,
3 class_desc varchar2(35),
4 credit_hrs number(2) );
Table created.
SQL>
SQL> insert into class values (1,"Public Speaking 101",3);
1 row created.
SQL> insert into class values (2,"English 101",3);
1 row created.
SQL> insert into class values (3,"English 201",3);
1 row created.
SQL> insert into class values (4,"English 301",3);
1 row created.
SQL> insert into class values (5,"English 401",3);
1 row created.
SQL> insert into class values (6,"Marketing 101",3);
1 row created.
SQL> insert into class values (7,"Child Development 101",3);
1 row created.
SQL> insert into class values (8,"Golf for Novices",2);
1 row created.
SQL> insert into class values (9,"Biology 101",4);
1 row created.
SQL>
SQL>
SQL>
SQL> create table grades (
2 student_id number,
3 class_id number,
4 assignment_desc varchar2(200),
5 grade_received number(3) );
Table created.
SQL>
SQL>
SQL> insert into grades values (1,1,"Exam 1",94);
1 row created.
SQL> insert into grades values (7,1,"Exam 1",88);
1 row created.
SQL> insert into grades values (4,1,"Exam 1",85);
1 row created.
SQL> insert into grades values (1,1,"Exam 2",87);
1 row created.
SQL> insert into grades values (7,1,"Exam 2",89);
1 row created.
SQL> insert into grades values (4,1,"Exam 2",91);
1 row created.
SQL> insert into grades values (1,1,"Paper 1",90);
1 row created.
SQL> insert into grades values (7,1,"Paper 1",82);
1 row created.
SQL>
SQL> set lines 90
SQL> set pages 100
SQL> col class format a25
SQL> col student format a30
SQL> col grade_avg format 999.99
SQL>
SQL> select c.class_desc as class, s.student_name as student, avg(g.grade_received) as grade_avg
2 from class c, student s, grades g
3 where c.class_id = g.class_id
4 and s.student_id = g.student_id
5 group by rollup (c.class_desc, s.student_name) ;
CLASS STUDENT GRADE_AVG
------------------------- ------------------------------ ---------
Public Speaking 101 Tom 90.33
Public Speaking 101 Sill 86.33
Public Speaking 101 Jason 88.00
Public Speaking 101 88.25
88.25
5 rows selected.
SQL>
SQL> drop table class;
Table dropped.
SQL> drop table student;
Table dropped.
SQL> drop table grades;
Table dropped.
Set column heading with column command
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>
SQL>
SQL>
SQL> SET PAGESIZE 80
SQL> SET LINESIZE 39
SQL> SET FEEDBACK OFF
SQL> TTITLE LEFT "emp Listing" RIGHT "Page " FORMAT 9 sql.pno SKIP 2
SQL> COLUMN emp_id HEADING "Emp ID"
SQL> COLUMN ename HEADING "Name" FORMAT A19
SQL> COLUMN rate HEADING "Billing|Rate" FORMAT $999.99
SQL> SELECT emp_id, ename, rate
2 FROM emp;
emp Listing Page 1
Billing
Emp ID Name Rate
---------- ------------------- --------
101 Mary $169.00
102 Tom $135.00
104 Peter $99.00
105 Mike $121.00
107 Less $45.00
108 Park $220.00
110 Ink $84.00
111 Tike $100.00
112 Inn $70.00
113 Kate $300.00
SQL> SET FEEDBACK ON
SQL>
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
Set column separation with colsep
SQL>
SQL> create table salary
2 ( grade NUMBER(2) constraint S_PK primary key
3 , lowerlimit NUMBER(6,2)
4 , upperlimit NUMBER(6,2)
5 , bonus NUMBER(6,2)
6 , constraint S_LO_UP_CHK check(lowerlimit <= upperlimit)
7 ) ;
Table created.
SQL>
SQL> insert into salary values (1, 700,1200, 0);
1 row created.
SQL> insert into salary values (2, 1201,1400, 50);
1 row created.
SQL> insert into salary values (3, 1401,2000, 100);
1 row created.
SQL> insert into salary values (4, 2001,3000, 200);
1 row created.
SQL> insert into salary values (5, 3001,9999, 500);
1 row created.
SQL>
SQL> select * from salary;
[Enter]...
GRADE LOWERLIMIT UPPERLIMIT BONUS
---------- ---------- ---------- --------
1 700 1200 .00
2 1201 1400 50.00
3 1401 2000 100.00
4 2001 3000 200.00
5 3001 9999 500.00
5 rows selected.
SQL> set colsep " | "
SQL> set numwidth 10
SQL> /
[Enter]...
GRADE | LOWERLIMIT | UPPERLIMIT | BONUS
---------- | ---------- | ---------- | --------
1 | 700 | 1200 | .00
2 | 1201 | 1400 | 50.00
3 | 1401 | 2000 | 100.00
4 | 2001 | 3000 | 200.00
5 | 3001 | 9999 | 500.00
5 rows selected.
SQL> set colsep " "
SQL>
SQL> drop table salary;
Table dropped.
SQL>
Set number column format
SQL>
SQL> create table salary
2 ( grade NUMBER(2) constraint S_PK primary key
3 , lowerlimit NUMBER(6,2)
4 , upperlimit NUMBER(6,2)
5 , bonus NUMBER(6,2)
6 , constraint S_LO_UP_CHK check (lowerlimit <= upperlimit)
7 ) ;
Table created.
SQL>
SQL> insert into salary values (1, 700,1200, 0);
1 row created.
SQL> insert into salary values (2, 1201,1400, 50);
1 row created.
SQL> insert into salary values (3, 1401,2000, 100);
1 row created.
SQL> insert into salary values (4, 2001,3000, 200);
1 row created.
SQL> insert into salary values (5, 3001,9999, 500);
1 row created.
SQL>
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>
SQL> set numwidth 5
SQL> set null " [N/A]"
SQL>
SQL> select ename, mgr, comm
2 from emp
3 where deptno = 10;
[Enter]...
ENAME MGR COMM
-------- ----- -----
Chris 9 [N/A
]
Peter [N/A [N/A
] ]
Mike 7 [N/A
[Enter]... set numformat 09999.99
ENAME MGR COMM
-------- ----- -----
]
3 rows selected.
SQL>
SQL> select * from salary;
[Enter]...
GRADE LOWERLIMIT UPPERLIMIT BONUS
----- ---------- ---------- --------
1 700 1200 .00
2 1201 1400 50.00
3 1401 2000 100.00
4 2001 3000 200.00
5 3001 9999 500.00
5 rows selected.
SQL> set numformat 99999
SQL>
SQL> drop table salary;
Table dropped.
SQL> drop table emp;
Table dropped.
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.
Use a13 to set the column length during displaying
SQL> CREATE TABLE departments
2 (department_id number(10) not null,
3 department_name varchar2(50) not null,
4 CONSTRAINT departments_pk PRIMARY KEY (department_id)
5 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 1, "Data Group" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 2, "Purchasing" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 3, "Call Center" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 4, "Communication" );
1 row created.
SQL>
SQL>
SQL>
SQL> column department_name format a13
SQL> column employees format a63 word_wrapped
SQL>
SQL> select department_name, department_name
2 from departments
3 /
Department Na Department Na
------------- -------------
Data Group Data Group
Purchasing Purchasing
Call Center Call Center
Communication Communication
SQL>
SQL> drop table departments;
Table dropped.
Use "format a30 heading" to define column name
SQL>
SQL>
SQL> CREATE TABLE departments
2 (department_id number(10) not null,
3 department_name varchar2(50) not null,
4 CONSTRAINT departments_pk PRIMARY KEY (department_id)
5 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 1, "Data Group" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 2, "Purchasing" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 3, "Call Center" );
1 row created.
SQL>
SQL> insert into departments ( department_id, department_name )
2 values( 4, "Communication" );
1 row created.
SQL>
SQL>
SQL> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME
------------- --------------------------------------------------
1 Data Group
2 Purchasing
3 Call Center
4 Communication
SQL>
SQL>
SQL> column department_name format a30 heading "Department Names"
SQL>
SQL> select department_name
2 from departments
3 order by 1
4 /
Department Names
------------------------------
Call Center
Communication
Data Group
Purchasing
SQL>
SQL>
SQL> drop table departments;
Table dropped.
SQL>
Word Wrapped column format
SQL>
SQL> COLUMN table_name FORMAT A20
SQL> COLUMN comments FORMAT A50 WORD_WRAPPED
SQL>
SQL> SELECT table_name, comments
2 FROM dictionary
3 WHERE table_name LIKE "%SEQUENCE%";
USER_SEQUENCES
Description of the user"s own SEQUENCEs
ALL_SEQUENCES
Description of SEQUENCEs accessible to
the user
DBA_SEQUENCES
Description of all SEQUENCEs in the
database
SQL>
SQL>
Wrapping text
SQL>
SQL> CREATE TABLE product (
2 product_name VARCHAR2(25),
3 product_price NUMBER(4,2),
4 quantity_on_hand NUMBER(5,0),
5 last_stock_date DATE);
Table created.
SQL>
SQL> INSERT INTO product VALUES ("Small Widget", 99, 1, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Medium Widget", 75, 1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product Number", 50, 100, "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Round Church Station", 25, 10000, null);
1 row created.
SQL>
SQL>
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL>
SQL> COLUMN product_name FORMAT A10 WORD_WRAP
SQL>
SQL> SELECT * FROM product;
PRODUCT_NAME PRODUCT_PRICE QUANTITY_ON_HAND LAST_STOC
------------------------- ------------- ---------------- ---------
Small Widget 99 1 15-JAN-03
Medium Widget 75 1000 15-JAN-02
Product Number 50 100 15-JAN-03
Round Church Station 25 10000
SQL>
SQL>
SQL>
SQL>
SQL> DROP TABLE product;
Table dropped.