Transcript CS 430 Database Theory

CS 430
Database Theory
Winter 2005
Lecture 10: Introduction to SQL
1
SQL History


SQL = Structured Query Language
History



Initially defined by IBM Almaden Research Labs for System
R (an experimental relational database)
Database language for IBM’s DB2 RDBMS
Standards


Standardized by ANSI (American National Standards
Institute) and ISO (International Standards Organization)
Many Versions and Components
2
SQL1 or SQL-86





SQL-86 was a subset of IBM’s SQL
Included basic Data Manipulation Language (DML)
Did not include any Data Definition Language (DDL)
Language Bindings: COBOL, FORTRAN, Pascal,
PL/1
“Least Common Denominator”
3
SQL-89


Superset of SQL-86
Added basic DDL





Added Ada and C language bindings
Included Integrity Enhancements


CREATE and GRANT
No DROP, ALTER, REVOKE
Views
NOT NULL, FOREIGN KEY, UNIQUE, …
Still “Least Common Denominator”
4
SQL2 or SQL-92



Superset of SQL-89, some incompatibilities
No longer “Least Common Denominator”
Much bigger standard




SQL-89 -- 120 pages
SQL-92 -- 579 pages
Not all features available in all products (even today)
Defined levels of conformance


Entry, Intermediate, and Full Levels
Entry Level close to SQL-89 with Integrity Enhancements
5
SQL-92 Highlights

More data types with operators




Natural Join and Outer Joins
Dynamic SQL



Date and Time
Varying length Character Strings
SQL constructed dynamically by programs
Cascades for Referential Integrity
More DML, including ALTER, DROP,
REVOKE
6
More SQL-92 Highlights



Standard Catalogs
Transactions
Domains as Macros for constraint checking



Not same as Relational Model domain notion
Multiple Character Sets
Temporary Tables

Automatically vanish at end of transaction or
session
7
SQL3 or SQL-99

Superset of SQL-92, Significantly larger


Large Data Types



All the documents add up to over 2000 pages
Binary Large Objects (BLOBs) and Character Large
Objects (CLOBs)
Recursive Queries
Schemas

Can reference multiple schemas simultaneously
8
More SQL-99

Triggers, Stored Procedures


Standard Programming Language API



Procedural Language (SQL/Persistent Stored Modules
(PSM))
SQL/Call Level Interface (CLI)
Standardized version of Microsoft’s ODBC
Object/Relational features added


User defined types and methods
Structured and composite types
9
More SQL-99

Divided into Foundation and Packages


SQL-99 Foundation includes all of SQL-92 with
extensions
Package highlights:





PSM
CLI
Basic Object Support
Enhanced Object Support
Multi-Media

Full-text, Spatial, Images
10
Some Basic SQL - Data Types





Numeric
 Integers of various sizes
 Float, Double Precision
 DECIMAL(i, j) – i digits, j after decimal point
Character String
 Fixed length (CHAR)
 Varying length (VARCHAR)
Bit Strings – both Fixed and Varying
Boolean
Date and Time
 Basic Date and Time types
 Timestamp
 Interval – Time interval
11
Some Basic SQL - Expressions

Basic operations









Arithmetic (+, *, …)
Comparison (=, <, …)
Numeric Functions, e.g. SIN, …
Control Flow Functions - Case, IF, IFNULL
String Functions, e.g. CONCAT, TRIM, …
Date/Time manipulation
Full Text Search, Pattern Matching
Aggregation – SUM, AVERAGE, …
And more …
12
Some Basic SQL - Data Definition


create table EMPLOYEE
( ID
Int
NOT NULL,
Name
Varchar(30)
NOT NULL,
DNum
Int,
Salary
Decimal(11, 2),
primary key (ID),
foreign key(Dnum) references Dept(DeptNo));
alter table EMPLOYEE
alter DNum set default 999;
13
Some Basic SQL - Data Manipulation

SELECT



INSERT


Add one or more rows to a table
UPDATE


Retrieve a table
Like Relational Calculus
Modify one or more rows in a table
DELETE

Remove one or more rows from a table
14
Some Basic SQL - SELECT


select Id, Name
from EMPLOYEE
where Salary > 1000000;
select DNum, DeptName, average(Salary)
from EMPLOYEE, DEPT
where EMPLOYEE.Dnum = DEPT.DeptNo
group by DNum;
15
Some Basic SQL - INSERT


insert into EMPLOYEE
values(12345, ‘John Smith’, 99, 55000);
insert into Dept_Info( DNum, Name, ASal)
select DNum, DeptName, average(Salary)
from EMPLOYEE, DEPT
where EMPLOYEE.Dnum = DEPT.DeptNo
group by DNum;
16
Some Basic SQL – UPDATE


update EMPLOYEE
set Salary = 100000
where ID = 123;
update EMPLOYEE
set Salary = Salary * 0.05;
17
Some Basic SQL – DELETE


delete from EMPLOYEE
where ID = 123;
delete from EMPLOYEE;
18