Part 9 SQLCopyright © 1971-2002 Thomas P. Sturm SQL Part 9, Page 2 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 SQLCopyright © 1971-2002 Thomas P. Sturm SQL Part 9, Page 3 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 wheneverCopyright © 1971-2002 Thomas P. Sturm SQL Part 9, Page 4 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 OracleCopyright © 1971-2002 Thomas P. Sturm SQL Part 9, Page 5 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 ;Copyright © 1971-2002 Thomas P. Sturm SQL Part 9, Page 6 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 OracleCopyright © 1971-2002 Thomas P. Sturm SQL Part 9, Page 7 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);Copyright © 1971-2002 Thomas P. Sturm SQL Part 9, Page 8 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