Part 9I - SQL (For Oracle 9I)

Part 9i

SQL

for

Oracle 9i

Release 9.0.1.0.0 – Production and above.

Available in various editions: personal, lite, workgroup, enterprise.

JServer available as an add-on.

2002 release of the Oracle database management system.

Not to be confused with “Oracle 11”, which is a set of application packages.

More tightly tied to Java and the internet.

Performance enhancements.

Data mining enhancements.

Support for ANSI Standard SQL, SQL/92.

The Oracle 9i SQL Reference Manual is 1652 pages long.

Oracle 9i SQL Commands

SQL command forms:

alter / associate statistics / audit
call / comment / commit
create / delete / disassociate statistics
drop / explain plan / grant
insert / lock table / merge
no audit / rename / revoke
rollback / savepoint / select
set / truncate / update

Oracle 9i Alter/Create/Drop Variants

alter cluster / create cluster / drop cluster
create context / drop context
create controlfile
alter database / create database
create database link / drop database link
alter dimension / create dimension / drop dimension
create directory / drop directory
alter function / create function / drop function
alter index / create index / drop index
alter indextype / create indextype / drop indextype
alter java / create java / drop java
create library / drop library
alter materialized view / create materialized view / drop materialized view
alter materialized view log / create materialized view log / drop materialized view log
alter operator / create operator / drop operator
alter outline / create outline / drop outline
alter package / create package / drop package
create package body
create pfile
alter procedure / create procedure / drop procedure
alter profile / create profile / drop profile
alter resource cost
alter role / create role / drop role
alter rollback segment / create rollback segment / drop rollback segment
create schema
alter sequence / create sequence / drop sequence
alter session
create spfile
create synonym / drop synonym
alter system
alter table / create table / drop table
alter tablespace / create tablespace / drop tablespace
create temporary tablespace
alter trigger / create trigger / drop trigger
alter type / create type / drop type
create type body / drop type body
alter user / create user / drop user
alter view / create view / drop view

Oracle 9i Table Creation

Oracle 9i’s create command not only brings a base table into existence, but it defines the field names, the field formats, storage characterisitics, event handling, object characteristics, and many other things.

The Oracle 9i SQL manual devotes 87 pages to the description of this one command.

To create an “ordinary” relational table, use the syntax:

CREATE

optional GLOBAL TEMPORARY

TABLE

optional schema.

table_name

optional ( relational_properties )

optional ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWS

optional physical_properties

optional table_properties

;

Oracle 9i Object Support

Oracle 9i provides support for objects within the context of its relational database engine, using extended SQL syntax.

To create an Oracle table of objects, use the syntax:

CREATE

optional GLOBAL TEMPORARY

TABLE

optional schema.

table_name OF

optional schema.

object_type

optional object_table_substitution

optional ( object_properties )

optional ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWS

optional OID_clause

optional OID_index_clause

optional physical_properties

optional table_properties

;

Oracle 9i Built-in Datatypes Available

Oracle 9i has six subdivisions of built-in datatypes. The first two are character data and numeric data.

Character: (maximum width determined by the number of bytes per character)

CHAR (width) / fixed length string up to 2000 bytes
CHAR (width BYTE) / fixed length string up to 2000 bytes, byte semantics
CHAR (width CHAR) / fixed length string up to 2000 bytes, character semantics
VARCHAR2 (width) / variable length string up to 4000 bytes
VARCHAR2 (width BYTE) / variable length string up to 4000 bytes, byte semantics
VARCHAR2 (width CHAR) / variable length string up to 4000 bytes, character semantics
NCHAR (width) / fixed length national character string up to 2000 bytes
NVARCHAR2 (width) / variable length national character string up to 4000 bytes

Number:

NUMBER / floating point number of maximum precision
NUMBER (precision) / 1 to 38 digit precision
NUMBER (precision.scale) / -84 to 127 scale

More Oracle 9i Built-in Datatypes

Oracle supports long character and bit streams and dates.

Long and Raw:

LONG / character data up to 2 GB
LONG RAW / variable binary data up to 2 GB
RAW (size) / binary data up to 2000 bytes

Date and Time

DATE / Valid date from January 1, 4712 BC to December 31, 9999 AD.
TIMESTAMP / Year through second to 6 decimals
TIMESTAMP (precision) / Year through second 0 to 9 decimals
TIMESTAMP WITH TIME ZONE
TIMESTAMP (precision) WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP (precision) WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH / Years and months to 2 year decimals
INTERVAL YEAR (precision) TO MONTH / Years and months to 0 to 9 year decimals
INTERVAL DAY TO SECOND / Days to 2 decimals through seconds to 6 decimals
INTERVAL DAY (precision) TO SECOND / Days to 0 to 9 decimals through seconds to 6 decimals
INTERVAL DAY TO SECOND (precision) / Days to 2 decimals through seconds to 0 to 9 decimals
INTERVAL DAY (precision) to SECOND (precision)

Remaining Oracle 9i Built-in Datatypes

Oracle supports long objects and uses row ID addresses internally.

Large Objects

BLOB / binary large object to 4 GB
CLOB / character large object to 4 GB
NCLOB / Unicode large object to 4 GB
BFILE / locator to binary file object to 4 GB

Row ID

ROWID / base 64 string physical address
UROWID / base 64 string logical index address
UROWID(size)

Oracle 9i ANSI, DB2, and SQL/DS Datatypes Available

Oracle 9i provides support for the following ANSI types:

ANSI DATA TYPE / ORACLE IMPLEMENTATION
CHARACTER (size) / CHAR (size)
CHARACTER VARYING (size) / VARCHAR2 (size)
CHAR VARYING (size) / VARCHAR2 (size)
NCHAR VARYING (size) / NVARCHAR2 (size)
VARCHAR (size) / VARCHAR2 (size)
NATIONAL CHAR (size) / NCHAR (size)
NATIONAL CHAR VARYING (size) / NVARCHAR2 (size)
NATIONAL CHARACTER (size) / NCHAR (size)
NATIONAL CHARACTER VARYING (size) / NVARCHAR2 (size)
NUMERIC / NUMBER
DECIMAL / NUMBER
DEC / NUMBER
NUMERIC (precision) / NUMBER (precision)
DECIMAL (precision) / NUMBER (precision)
DEC (precision) / NUMBER (precision)
NUMERIC (precision.scale) / NUMBER (precision.scale)
DECIMAL (precision.scale) / NUMBER (precision.scale)
DEC (precision.scale) / NUMBER (precision.scale)
INTEGER / NUMBER (38)
INT / NUMBER (38)
SMALLINT / NUMBER (38)
FLOAT / NUMBER
FLOAT (size) / NUMBER
DOUBLE PRECISION / NUMBER
REAL / NUMBER

Table Creation Examples

SQL>CREATE TABLEdept

(deptnoNUMBER(3),

dnameCHAR(15),

locCHAR(3),

dbudgetNUMBER(18,2) );

SQL>CREATE TABLEtpsturm.emp

TABLESPACEstudent

(enameCHAR(20),

jobCHAR(10),

mgrCHAR(20),

hiredDATE,

rateNUMBER(18,2),

bonusNUMBER(18,2),

deptnoNUMBER(3) );

SQL>CREATE TABLEtask

(enameCHAR(20) DEFAULT NULL,

project_idCHAR(7) DEFAULT ‘admit’,

tnameCHAR(10) DEFAULT ‘design’,

hoursNUMBER(3) CHECK > 0 );

SQL>CREATE TABLEproj

(project_idCHAR(7) PRIMARY KEY,

descriptionCHAR(20) UNIQUE,

pbudgetNUMBER(18,2) NOT NULL,

due_dateDATE );

Inserting Data Values

Conventional insert (one row at a time, fills in space, checks integrity constraints one row at a time)

SQL> INSERT INTO dept(deptno, dname, loc, dbudget)

VALUES(400, ‘programming’, ‘200’, 150000);

SQL> INSERT INTO dept

VALUES(401, ‘financial’, ‘200’, 275000);

SQL> INSERT INTO dept

SELECT 402, ‘academic’, ‘100’, 390000 FROM dual;

SQL> INSERT INTO dept(deptno, dname, dbudget, loc)

VALUES(400+3, ‘sup’||’port’, 2*3500, ‘300’);

SQL> INSERT INTO emp(ename, job, mgr, hired, rate, deptno) VALUES(‘smith’, ‘programmer’, ‘barger’, ‘17Dec90’, 35, 402);

SQL> INSERT INTO emp(ename, job, hired, rate, bonus, deptno) VALUES(‘turner’, ‘superviosr’, ‘2Mar91’, 75, 1000, 400);

SQL> INSERT INTO task(ename, hours)

VALUES(‘olson’, 75);

SQL> INSERT INTO proj(project_id, description, pbudget, due_date) VALUES(‘admit’, ‘Admissions’, 15000, ‘4/7/98’);

SQL*Loader

Direct-path insert (multiple rows at a time, appended to the end, integrity must exist with data already in the database)

Exact invocation syntax is operating system dependent, since it is invoked at the operating system level, not within SQL.

On the DEC Alpha:

sqlload username/password control=controlfile.ctl

On a PC using Oracle 8i:

sqlldr80 username/password@cs_orcl controlfile.ctl

Four files are used during this process:

1. The data file containing the values to be stored in the fields of the table. Usually the best format is comma delimited format, but fixed format will also work. The file extension is .dat

2. The control file containing the directions on how to map the data in the data file to the table in the database. The file extension is .ctl

3. A log file generated by the run. The file extension is .log

4. An error file generated by the run containing all bad data that did not get inserted into the table. The file extension is .bad

Generally, all the files have the same name as the table.

Control and Data Files for the dept table

dept.ctl

--QMCS 450 sample database

--dept table

load data

infile 'dept.dat'

into table dept

fields terminated by ','

(DeptNointeger external,

Dnamechar,

Locchar,

Dbudgetdecimal external)

dept.dat

400,programming,200,150000

401,financial,200,275000

402,academic,100,390000

403,support,300,7000

Log File for the dept table (page 1)

SQL*Loader: Release 8.0.5.0.0 - Production on Wed Mar 24 15:55:11 1999

Control File: dept.ctl

Data File: dept.dat

Bad File: dept.bad

Discard File: none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array: 64 rows, maximum of 65536 bytes

Continuation: none specified

Path used: Conventional

Table DEPT, loaded from every logical record.

Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype

------

DEPTNO FIRST * , CHARACTER

DNAME NEXT * , CHARACTER

LOC NEXT * , CHARACTER

DBUDGET NEXT * , CHARACTER

Log File for the dept table (page 2)

Table DEPT:

4 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 65016 bytes(63 rows)

Space allocated for memory besides bind array: 0 bytes

Total logical records skipped: 0

Total logical records read: 4

Total logical records rejected: 0

Total logical records discarded: 0

Run began on Wed Mar 24 15:55:11 1999

Run ended on Wed Mar 24 15:55:13 1999

Elapsed time was: 00:00:01.70

CPU time was: 00:00:00.00

Control and Data Files for the emp table

emp.ctl

--QMCS 450 example database

--emp table

load data

infile 'emp.dat'

into table emp

fields terminated by ','

(Enamechar,

Jobchar,

Mgrchar,

Hireddate(11) "DD-mon-YYYY",

Ratedecimal external,

Bonusdecimal external,

DeptNointeger external)

emp.dat

allen,programmer,barger,09-jun-1991,30,,402

barger,supervisor,turner,23-jan-1993,65,550,402

jones,programmer,radl,20-feb-1991,35,,401

king,clerk,barger,22-feb-1991,18,,402

martin,programmer,barger,09-nov-1991,25,,402

olson,analyst,radl,28-apr-1991,55,0,401

pearson,programmer,radl,01-may-1991,30,,401

radl,supervisor,turner,03-dec-1992,65,600,401

rogers,programmer,barger,08-sep-1992,25,,402

smith,programmer,barger,17-dec-1990,35,,402

sturm,clerk,radl,23-sep-1992,18,,401

thomas,analyst,barger,03-dec-1992,50,0,402

turner,supervisor,,02-mar-1991,75,1000,400

vogel,consultant,turner,17-nov-1991,80,,400

Control and Data Files for the proj table

proj.ctl

--QMCS 450 sample database

--proj table

load data

infile 'proj.dat'

into table proj

fields terminated by ','

(Project_idinteger external,

Descriptionchar,

Pbudgetdecimal external,

Due_datedate(11) "dd-mon-yyyy")

proj.dat

admit,Admissions,15000,07-apr-1998

alumni,Alumni development,7500,30-jan-1999

billing,Student billing,11000,30-jan-1998

budget,Budgeting,12500,12-mar-1998

payroll,Payroll,9000,15-mar-1998

records,Student records,6000,11-feb-1998

Control and Data Files for the task table

task.ctl

--QMCS 450 sample database

--task table

load data

infile 'task.dat'

into table task

fields terminated by ','

(Enamechar,

Project_idchar,

Tnamechar,

Hoursinteger external)

task.dat

allen,admit,debug,25

allen,admit,implement,20

allen,billing,debug,30

allen,billing,implement,20

barger,admit,manage,15

barger,alumni,manage,10

barger,billing,manage,8

barger,records,manage,12

jones,billing,implement,35

jones,budget,implement,70

jones,payroll,debug,40

king,admit,clerical,25

king,alumni,clerical,9

king,records,clerical,15

martin,admit,implement,30

olson,admit,design,75

olson,alumni,design,40

olson,billing,design,20

olson,records,design,45

pearson,budget,debug,40

pearson,budget,implement,30

pearson,payroll,implement,80

radl,billing,design,15

radl,billing,manage,10

radl,budget,manage,15

radl,payroll,manage,20

rogers,records,debug,20

rogers,records,design,30

rogers,records,implement,45

smith,alumni,debug,30

smith,alumni,implement,90

smith,billing,implement,40

sturm,billing,clerical,38

sturm,budget,clerical,20

sturm,budget,debug,20

sturm,payroll,clerical,15

thomas,alumni,design,5

thomas,billing,design,45

thomas,budget,design,40

thomas,payroll,design,70

turner,billing,manage,12

turner,budget,design,45

CREATE TABLE AS

The CREATE TABLE … AS syntax allows a new table to be created from an existing table:

SQL> CREATE TABLE emp1 AS SELECT * FROM emp WHERE mgr = 'radl';

Table created

SQL> SELECT * FROM emp1;

SQL> describe emp1;

Name / Type
ENAME / CHAR(20)
JOB / CHAR(10)
MGR / CHAR(20)
HIRED / DATE
RATE / NUMBER(18,2)
BONUS / NUMBER(18,2)
DEPTNO / NUMBER(38)

Set-Theoretic Operators

Oracle supports the set-theoretic operators of union, union all, intersect, and minus.

SQL> CREATE TABLE emp2 AS SELECT * FROM emp WHERE job = 'programmer';

ENAME / JOB / MGR / HIRED / RATE / BONUS / DEPTNO
allen / programmer / barger / 09-JUN-91 / 30 / 402
jones / programmer / radl / 20-FEB-91 / 35 / 401
martin / programmer / barger / 09-NOV-91 / 25 / 402
pearson / programmer / radl / 01-MAY-91 / 30 / 401
rogers / programmer / barger / 08-SEP-92 / 25 / 402
smith / programmer / barger / 17-DEC-90 / 35 / 402

SQL> SELECT * FROM emp1 UNION SELECT * FROM emp2;

ENAME / JOB / MGR / HIRED / RATE / BONUS / DEPTNO
allen / programmer / barger / 09-JUN-91 / 30 / 402
jones / programmer / radl / 20-FEB-91 / 35 / 401
martin / programmer / barger / 09-NOV-91 / 25 / 402
olson / analyst / radl / 28-APR-91 / 55 / 0 / 401
pearson / programmer / radl / 01-MAY-91 / 30 / 401
rogers / programmer / barger / 08-SEP-92 / 25 / 402
smith / programmer / barger / 17-DEC-90 / 35 / 402
sturm / clerk / radl / 23-SEP-92 / 18 / 401

More Set-Theoretic Operators

SQL> SELECT * FROM emp1 INTERSECT SELECT * FROM emp2;

ENAME / JOB / MGR / HIRED / RATE / BONUS / DEPTNO
jones / programmer / radl / 20-FEB-91 / 35 / 401
pearson / programmer / radl / 01-MAY-91 / 30 / 401

SQL> SELECT * FROM emp1 UNION ALL SELECT * FROM emp2;

ENAME / JOB / MGR / HIRED / RATE / BONUS / DEPTNO
jones / programmer / radl / 20-FEB-91 / 35 / 401
olson / analyst / radl / 28-APR-91 / 55 / 0 / 401
pearson / programmer / radl / 01-MAY-91 / 30 / 401
sturm / clerk / radl / 23-SEP-92 / 18 / 401
allen / programmer / barger / 09-JUN-91 / 30 / 402
jones / programmer / radl / 20-FEB-91 / 35 / 401
martin / programmer / barger / 09-NOV-91 / 25 / 402
pearson / programmer / radl / 01-MAY-91 / 30 / 401
rogers / programmer / barger / 08-SEP-92 / 25 / 402
smith / programmer / barger / 17-DEC-90 / 35 / 402

SQL> SELECT * FROM emp1 MINUS SELECT * FROM emp2;

ENAME / JOB / MGR / HIRED / RATE / BONUS / DEPTNO
olson / analyst / radl / 28-APR-91 / 55 / 0 / 401
sturm / clerk / radl / 23-SEP-92 / 18 / 401

ANSI Standard SQL Joins

The inner join can be specified in the FROM clause and the join condition specified in the ON subclause.

SQL> SELECT dept.dname, emp.ename, emp.job FROM dept INNER JOIN emp ON dept.deptno = emp.deptno WHERE emp.job = 'programmer';

SQL> SELECT dept.dname, emp.ename, emp.job FROM dept INNER JOIN emp USING (deptno) WHERE emp.job = 'programmer';

SQL> SELECT dept.dname, emp.ename, emp.job FROM dept NATURAL JOIN emp WHERE emp.job = 'programmer';

DNAME / ENAME / JOB
financial / jones / programmer
financial / pearson / programmer
academic / allen / programmer
academic / smith / programmer
academic / rogers / programmer
academic / martin / programmer

ANSI Left (and Right) Outer Joins

Left (and right) outer joins can be specified in a similar way.

Note the different effect of putting restrictions in the join condition vs. the where clause.

SQL> SELECT dept.dname, emp.ename, emp.job FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno AND emp.job = 'programmer';

DNAME / ENAME / JOB
academic / allen / programmer
financial / jones / programmer
academic / martin / programmer
financial / pearson / programmer
academic / rogers / programmer
academic / smith / programmer
programming
support

SQL> SELECT dept.dname, emp.ename, emp.job FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno WHERE emp.job = 'programmer'

DNAME / ENAME / JOB
financial / pearson / programmer
financial / jones / programmer
academic / smith / programmer
academic / rogers / programmer
academic / martin / programmer
academic / allen / programmer

Three Tables to Test Outer Joins

The following three tables lack referential integrity:

SQL> select * from div;

DNO / Dname
21 / A
22 / B
23 / C
24 / D

SQL> select * from sec;

SNO / DNO / Sname
41 / 21 / E
42 / 22 / F
43 / 33 / G
44 / 34 / H
45 / 21 / I
46 / 22 / J
47 / 33 / K
48 / 34 / L

SQL> select * from grp;

GNO / SNO / DNO / Gname
61 / 41 / 21 / e
62 / 42 / 23 / f
63 / 43 / 33 / g
64 / 44 / 35 / h
65 / 55 / 21 / I
66 / 56 / 23 / j
67 / 57 / 33 / k
68 / 58 / 35 / l

ANSI Natural Joins and Outer Joins

SQL> SELECT * FROM div NATURAL JOIN sec NATURAL JOIN grp;

DNO / SNO / Dname / Sname / GNO / Gname
21 / 41 / A / E / 61 / e

SQL> SELECT * FROM div FULL OUTER JOIN sec ON div.dno = sec.dno

div.DNO / Dname / SNO / sec.DNO / Sname
21 / A / 41 / 21 / E
22 / B / 42 / 22 / F
21 / A / 45 / 21 / I
22 / B / 46 / 22 / J
23 / C
24 / D
47 / 33 / K
43 / 33 / G
48 / 34 / L
44 / 34 / H

ANSI Outer Joins
with Inner Join Column Contraction

The “USING” syntax causes “column contraction”. This process finds matching column names, checks their equality, and keeps only one copy of the columns. This makes complete sense for the inner join or the natural join. It also has advantages for the outer join when cascading joins beyond two tables.