Part 9 - SQL (For Oracle)

Part 9

SQL

SQL

Originally SEQUEL for "Structured English Query Language" developed in 1974.

Prototype implementation of a revised SEQUEL 2 by IBM in System R in 1976

Name changed to SQL for “Structured Query Language” in 1976, but pronounced “sequel” for many years.

Outer join was added and a commercial implementation by Oracle in 1979

IBM implementations

·  operational System R 1981

·  SQL/DS (VM/CMS, DOS/VSE) 1982

·  DB2 (MVS/370, MVS/XA) 1985

·  QMF ad hoc query / report-writing front-end

user-oriented

used with both SQL/DS and DB2

ANSI standard X3.135 October, 1986

Ingres V5.0 SQL to QUEL translator 1987

ANSI revised standard with “integrity enhancement feature” in 1989

ANSI SQL92 with full referential integrity

SQL 3 on drawing board with object-oriented SQL

SQL Commands

Interactive SQL command forms

alter*† / analyze*† / audit*†
comment* / commit / copy†‡
create / delete / drop
explain plan*† / grant / help
insert / label*‡ / lock*
modify†‡ / no audit*† / rename*†
revoke* / rollback / save†‡
savepoint† / select / set†
truncate*† / update / *†

* not available in Ingres

† not available in AS/400

‡ not available in Oracle

Embedded SQL Commands

close / commit / connect
declare / delete / describe
execute / fetch / insert
open / prepare / rollback
savepoint / select / type
update / var / whenever

SQL Command Variants

Alter/Create/and Drop commands

alter cluster*† / create cluster*† / drop cluster*†
create collection*‡ / drop collection*‡
create controlfile*†
alter database*† / create database*†
create database link*† / drop database link*†
alter function*† / create function*† / drop function*†
alter index*† / create index / drop index
create integrity†‡ / drop integrity†‡
alter package*† / create package*† / drop package*†
drop permit†‡
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*†
alter snapshot*† / create snapshot*† / drop snapshot*†
alter snapshot log*† / create snapshot log*† / drop snapshot log*†
create synonym*† / drop synonym*†
alter system*†
alter table*† / create table / drop table
alter tablespace*† / create tablespace*† / drop tablespace*†
alter trigger*† / create trigger*† / drop trigger*†
alter user*† / create user*† / drop user*†
alter view*† / create view / drop view

* not available in Ingres

† not available in AS/400

‡ not available in Oracle

Table Creation

Create brings a base table into existence, defining the field names and formats.

In Ingres:

SQL> / CREATE TABLE dept
(deptno / INTEGER,
dname / CHAR(15),
loc / CHAR(3),
dbudget / MONEY);

In Oracle:

SQL> / CREATE TABLE dept
(deptno / INTEGER,
dname / CHAR(15),
loc / CHAR(3),
dbudget / DECIMAL(18,2) );

On AS/400:

SQL> / CREATE TABLE dept
(deptno / INTEGER not null,
dname / CHAR(15) not null with default,
loc / CHAR(3) not null with default,
dbudget / DECIMAL(18,2) not null with default);
dept table:
deptno / dname / loc / dbudget
(whole #) / (up to 15 char.) / (up to 3 char.) / ($ amount)

Separate column definitions by commas

Enclose table definition within ()

Specify a datatype (and width, if applicable) available in the implementation you are using

Terminate command with a ;

Datatypes Available

C (width)†‡ / character string (up to 255 char)
CHAR*† / 1 character string
CHAR (width) / character string (up to 255 char)
CHAR VARYING(width)*† / character string (to 2000 char)
CHARACTER (width)* / character string (up to 255 char)
CHARACTER VARYING(width)*† / character string (to 2000 char)
DATE† / date
DEC (width, decimal)*† / fixed decimal
DECIMAL (width, decimal)* / fixed decimal
DOUBLE PRECISION*† / double precision
FLOAT† / double precision
FLOAT4†‡ / single precision
FLOAT8†‡ / double precision
FLOAT (precision)* / floating point
INT*† / long integer
INTEGER / long integer
INTEGER1†‡ / one-byte integer
INTEGER2†‡ / short (2-byte) integer
INTEGER4†‡ / long (4-byte) integer
LONG*† / character string (up to 2GB)
LONG RAW*† / binary (up to 2 GB)
LONG VARCHAR*† / character string (up to 2GB)
MONEY† / money
NUMBER*† / double precision
NUMBER(width)*† / long integer
NUMBER(width, decimal)*† / fixed decimal
NUMERIC (width, decimal)*‡ / fixed decimal
RAW*† / binary (up to 255 bytes)
REAL*† / single precision
SMALLINT† / short integer
TEXT†‡ / character string (to 2000 char)
TIME*†‡ / time
TIMESTAMP*†‡ / time
VCHAR (width)†‡ / character string (to 2000 char)
VARCHAR (width)† / character string (to 2000 char)
VARCHAR2 (width)*† / character string (to 2000 char)

* not available in Ingres † not available in AS/400 ‡ not available in Oracle

Remaining Tables in Sample Database

SQL> / CREATE TABLE emp
(ename / CHAR(20),
job / CHAR(10),
mgr / CHAR(20),
hired / DATE,
rate / NUMBER(18,2),
bonus / NUMBER(18,2),
deptno / INTEGER);
SQL> / CREATE TABLE task
(ename / CHAR(20),
project_id / CHAR(7),
tname / CHAR(10),
hours / INTEGER);
SQL> / CREATE TABLE proj
(project_id / CHAR(7),
description / CHAR(20),
pbudget / NUMBER(18,2),
due_date / DATE);

Inserting Data Values

Populates the table with actual data values

SQL> INSERT INTO dept(deptno, dname, loc, dbudget) VALUES(400, 'programming', '200', 150000);

SQL> INSERT INTO dept(deptno, dname, loc, dbudget) VALUES(401, 'financial', '200', 275000);

SQL> INSERT INTO dept(deptno, dname, loc, dbudget) VALUES(402, 'academic', '100', 390000);

SQL> INSERT INTO dept(deptno, dname, loc, dbudget) VALUES(403, 'support', '300', 7000);

·  Separate field names and values by commas

·  Enclose the set of field names and values within ()

·  Field names and values are matched by position

·  CHAR and DATE values are in single (close) quotes '

·  "Null" fields may be entered in two ways

- Omit field name (and provide no value)

- Place two consecutive commas in the values clause

Data Insertion in Sample Database Tables

SQL> INSERT INTO emp(ename, job, mgr, hired, rate, deptno) VALUES('smith', 'programmer', 'barger', '12/17/90', 35, 402);

SQL> INSERT INTO task(ename, project_id, tname, hours) VALUES('olson', 'admit', 'design', 75);

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

Querying a Table

The select statement provides the mechanism for database queries, providing for find, join, selection, projection, entry-level derivations, order, file-level derivations, formatting, and presentation in a single (but perhaps complex) statement.

SQL> SELECT * FROM emp;

ename / job / mgr / hired / rate / bonus / deptno
smith / programmer / barger / 17-dec-90 / 35 / 402
jones / programmer / radl / 20-feb-91 / 35 / 401
king / clerk / barger / 22-feb-91 / 18 / 402
turner / supervisor / 02-mar-91 / 75 / 1000 / 400
olson / analyst / radl / 28-apr-91 / 55 / 0 / 401
pearson / programmer / radl / 01-may-91 / 30 / 401
allen / programmer / barger / 09-jun-91 / 30 / 402
martin / programmer / barger / 09-nov-91 / 25 / 402
vogel / consultant / turner / 17-nov-91 / 80 / 400
rogers / programmer / barger / 08-sep-92 / 25 / 402
sturm / clerk / radl / 23-sep-92 / 18 / 401
radl / supervisor / turner / 03-dec-92 / 65 / 600 / 401
thomas / analyst / barger / 03-dec-92 / 50 / 0 / 402
barger / supervisor / turner / 23-jan-93 / 65 / 550 / 402

·  the * means obtain all columns

·  there are no restrictions, so all rows are selected

Selection (Restriction) of Rows

Allows only specific rows of the table to be retrieved

Give all the information in the employee table for members of department 401.

SQL> SELECT * FROM emp WHERE deptno = 401;

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
radl / supervisor / turner / 03-dec-92 / 65 / 600 / 401

·  the WHERE clause restricts which rows are retrieved

Selecting Specific Columns (Projection)

Specify the names of the columns desired

SQL> SELECT ename, mgr, deptno FROM emp;

ename / mgr / deptno
smith / barger / 402
jones / radl / 401
king / barger / 402
turner / 400
olson / radl / 401
pearson / radl / 401
allen / barger / 402
martin / barger / 402
vogel / turner / 400
rogers / barger / 402
sturm / radl / 401
radl / turner / 401
thomas / barger / 402
barger / turner / 402

·  Place a comma between column names

·  Because there is no WHERE clause, all rows appear for each column named

Reordering and Renaming Columns (Permutation and Presentation)

Columns are presented in the order requested in the select statement, with optional aliases

SQL> SELECT ename, deptno, mgr AS manager FROM emp;

ename / deptno / manager
smith / 402 / barger
jones / 401 / radl
king / 402 / barger
turner / 400
olson / 401 / radl
pearson / 401 / radl
allen / 402 / barger
martin / 402 / barger
vogel / 400 / turner
rogers / 402 / barger
sturm / 401 / radl
radl / 401 / turner
thomas / 402 / barger
barger / 402 / turner

·  the order of the names in the select statement determines the order of the columns in the display

·  this table is logically identical to the previous one

·  the AS (keyword optional) establishes a column alias for the display

Combined Selection and Projection

Selection and projection can be combined in a single statement in the obvious way

Suppose we wished to see the name, department number, and hourly rate (in that order) for employees in department 401

List the name, department number, and hourly rate for all employees of department 401.

SQL> SELECT ename, deptno, rate FROM emp WHERE deptno = 401;

ename / deptno / rate
jones / 401 / 35
olson / 401 / 55
pearson / 401 / 30
sturm / 401 / 18
radl / 401 / 65

Entry-Level Derivations

New columns can be created based on values in existing columns

For example, suppose we wished to see a list of old and new hourly rates for employees in department 401 if we raised the hourly rates by 20%

SQL> SELECT ename, deptno, rate AS old_rate, l.2*rate AS new_rate FROM emp WHERE deptno=401;

ename / deptno / old_rate / new_rate
jones / 401 / 35 / 42
olson / 401 / 55 / 66
parson / 401 / 30 / 36
sturm / 401 / 18 / 21.6
radl / 401 / 65 / 78

·  assigning an alias to rate is optional

·  assigning an alias to 1.2*rate is also optional (the formula will print if there is no alias)

Ordering Rows (Sorting)

Relations are not presumed to be stored, processed, or presented in any particular order

Many implementations will store rows in their order of input

The ORDER BY clause in SQL provides for sorted output

The following construct presents the previous query in order of increasing hourly rate

SQL> SELECT ename, deptno, rate AS old_rate, 1.2*rate AS new_rate FROM emp WHERE deptno=401 ORDER BY old_rate;

ename / deptno / old_rate / new_rate
sturm / 401 / 18 / 21.6
person / 401 / 30 / 36
jones / 401 / 33 / 42
olson / 401 / 53 / 66
radl / 401 / 65 / 78

·  for multiple column ORDER BY clause, first is primary

·  can use column name, alias, or column number to identify sorting column

·  default is ascending order, use DESC after column name for descending order

General Structure of Select Command

The SELECT command consists of two to six clauses, always in a fixed order

required / SELECT / (columns)
required / FROM / (tables)
optional / WHERE / (conditions)
optional / GROUP BY / (columns)
optional / HAVING / (conditions)
optional / ORDER BY / (columns)

If an SQL SELECT command is phrased incorrectly, the user will end up with

·  an error message

·  no result at all

·  answers to a question you didn't realize you asked

Sample Queries

IN Construction

The IN operator selects rows that match one of the values in a set

SQL> SELECT ename, job FROM emp WHERE job IN ('clerk', 'analyst');

ename / job
king / clerk
olson / analyst
sturm / clerk
thomas / analyst

Wildcard Usage

The % may be used to match any size string, the _ to match any single character

SQL> SELECT ename FROM emp WHERE ename LIKE '_a%';

ename

martin

radl

barger

Sample Queries

Multiple Conditions

Multiple conditions may be combined with the logical AND and OR operations, and grouped with ()

SQL> SELECT ename FROM emp WHERE job = 'programmer' AND mgr = 'radl';

ename