The SQL Standard (ISO/IEC 9075)

1
The SQL Standard (ISO/IEC 9075)
Where it isandwhere it is going

• S J Cannan
• Technical Manager

2
The SQL Standard (ISO/IEC 9075)

• Goal Portability of SQL applications
• Effect Increase and stabilisation of the
  database market
• Mechanism Joint efforts between implementers
  and users Joint
  effort among several countries

3
Past History

• 1987 SQL
• 1989 SQL Integrity Enhancement
• 1992 SQL (2)
• 1995 SQL/CLI
• 1996 SQL/PSM
• 1999 SQL (3)
• 2000 SQL/OLB
• 2000 SQL/OLAP
• 2000 SQL/MED

The SQL Standard (ISO/IEC 9075)
4
Current Structure

• ISO/IEC 9075-1 Framework
• ISO/IEC 9075-2 Foundation
• ISO/IEC 9075-3 Call-level interface
• ISO/IEC 9075-4 Persistent Stored Modules
• ISO/IEC 9075-5 Language Bindings
• ISO/IEC 9075-9 Management of External Data
• ISO/IEC 9075-10 Object Language Bindings
• ISO/IEC 9075Amd1 SQL/OLAP

The SQL Standard (ISO/IEC 9075)
5
The Standardisation Process
NWI
Working Draft
Committee Draft
Proposals
Proposals
Comments
Final Committee Draft
Technical Corrigenda
Comments
International Standard
Draft International Standard
Yes/No
The SQL Standard (ISO/IEC 9075)
6
ISO/IEC 9075-1Framework (SQL/Framework)

• Description of Parts
• Terminology
• Basic Concepts
• Basic Conformance Clause

The SQL Standard (ISO/IEC 9075)
7
ISO/IEC 9075-2 Foundation (SQL/Foundation)

• Data Definition
• Data Manipulation
• Data Access Control
• Transaction Management
• Information Schema
• Miscellaneous

The SQL Standard (ISO/IEC 9075)
8
ISO/IEC 9075-2 MOOSE

• Large Objects
• Arrays
• User-defined types
• Subtypes and inheritance
• Encapsulation
• Substitutability
• Transforms

The SQL Standard (ISO/IEC 9075)
9
ISO/IEC 9075-2 Data Access

• Data Manipulation Statements
• INSERT
• UPDATE
• DELETE
• SELECT

The SQL Standard (ISO/IEC 9075)
10
ISO/IEC 9075-2Predicates
Comparison (, lt, gt, lt, gt, ltgt) value BETWEEN
ASYMMETRIC value2 AND value3 value IN subquery
or (value-list) value NOT LIKE pattern ESCAPE
char row-value IS NOT NULL row-value comp-op
ALLSOMEANY subquery EXISTS subquery UNIQUE
subquery row-value MATCH UNIQUE
SIMPLEPARTIALFULL subquery row-value1
OVERLAPS row-value2 value NOT SIMILAR TO
pattern ESCAPE char row-value1 IS DISTINCT FROM
row-value2 value IS NOT OF ( type-list )
The SQL Standard (ISO/IEC 9075)
11
ISO/IEC 9075-2 Cursors
Sensitivity ASENSITIVE SENSITIVE INSENSITIVE
implies READ ONLY Holdable ORDER BY columns
not in select list expressions
The SQL Standard (ISO/IEC 9075)
12
ISO/IEC 9075-2 Common Table Expressions
WITH New_Price AS ( SELECT Source, Destination,
Carrier, Cost discount_rate AS
New_Cost FROM Flights f join Discounts d
on d.carrierf.carrier ) SELECT a.Source,
a.Destination, a.Carrier, a.New_Cost,
b.Carrier, b.New_Cost FROM New_Price a, New_Price
b WHERE (a.Source, a.Destination)
(b.Source, b.Destination) AND a.Carrier ltgt
b.Carrier AND a.NewCost gt b.New_Cost
The SQL Standard (ISO/IEC 9075)
13
ISO/IEC 9075-2 Recursion

• Fixpoint theory
• Unique solutions
• Transformation on the right hand side of a
  recursive definition must be monotonically
  increasingDisallow
• negation that crosses recursion
• aggregation that crosses recursion
• INTERSECT ALL
• EXCEPT ALL
• EXCEPT DISTINCT (on right hand side)
• FULL OUTER JOIN (LEFTRIGHT with recursion on
  rightleft)

The SQL Standard (ISO/IEC 9075)
14
ISO/IEC 9075-2 Recursion
ISO/IEC 9075-2 Recursion
Mutual Recursion
WITH RECURSIVE Even (N) AS ( VALUES (0)
UNION SELECT M 1 FROM Odd )
Odd (M) AS ( SELECT N 1 FROM Even
) SELECT FROM Even WHERE N lt 12
The SQL Standard (ISO/IEC 9075)
15
ISO/IEC 9075-2 F261 Case expression
SELECT CASE WHEN abbreviation CA THEN
California WHEN abbreviation SD THEN
South Dakota WHEN ... ELSE Unknown END
FROM states WHERE ...
SELECT emp_name, deptno FROM employee WHERE (
CASE bonus commission WHEN 0 THEN
NULL ELSE salary/(bonuscommission)
) gt 10
Used to implement COALESCE SELECT COALESCE
(nickname, first_name, surname, Unknown) FROM
people
The SQL Standard (ISO/IEC 9075)
16
ISO/IEC 9075-2 OLAP
Enhances query capabilities CUBE ROLLUP GROU
PING SETS Expressions in ORDER BY
The SQL Standard (ISO/IEC 9075)
17
ISO/IEC 9075-2 OLAP
Find the total sales per region and sales manager
during each month of 1996, with subtotals for
each month, and concluding with the grand total
SELECT month, region, sales_mgr, SUM (price)
FROM Sales WHERE year 1996 GROUP BY ROLLUP
(month, region, sales_mgr)
The SQL Standard (ISO/IEC 9075)
18
ISO/IEC 9075-2 OLAP
The SQL Standard (ISO/IEC 9075)
19
ISO/IEC 9075-2 OLAP
Find the total sales per region and sales manager
during each month of 1996, with subtotals for
each month, region and sales manager and
concluding with the grand total
SELECT month, region, sales_mgr, SUM(price)
FROM Sales WHERE year 1996 GROUP BY CUBE
(month, region, sales_mgr)
The SQL Standard (ISO/IEC 9075)
20
ISO/IEC 9075-2 OLAP
The SQL Standard (ISO/IEC 9075)
21
ISO/IEC 9075-2 Data Protection

• Privileges
• GRANT, REVOKE
• SELECT, UPDATE, DELETE, INSERT, REFERENCE, UNDER,
  TRIGGER, USAGE
• Roles
• CREATE, GRANT, REVOKE, DROP

The SQL Standard (ISO/IEC 9075)
22
ISO/IEC 9075-2 Roles
Enhanced security mechanisms Simplifies
definition of complex sets of privileges
CREATE/DROP role GRANT/REVOKE privileges to
roles GRANT/REVOKE roles to users and other
roles
The SQL Standard (ISO/IEC 9075)
23
ISO/IEC 9075-2 Data Description

• The Information Schema
• short name views
• The Definition Schema

?
The SQL Standard (ISO/IEC 9075)
24
ISO/IEC 9075-2 Miscellaneous

• Transaction Statements
• Connection Statements
• Diagnostic Statements

The SQL Standard (ISO/IEC 9075)
25
ISO/IEC 9075-3 Call Level Interface (SQL/CLI)

• SQL for Shrink-wrapped software
• Resource control
• SQL connection control
• Execution of SQL statements
• Diagnostics
• SQL implementation information

The SQL Standard (ISO/IEC 9075)
26
ISO/IEC 9075-3 Call Level Interface (SQL/CLI)
Application
Call
Return
CLI Driver
Client
Call
Return
Server
Database Management System
The SQL Standard (ISO/IEC 9075)
27
ISO/IEC 9075-4 Persistent Stored Modules
(SQL/PSM)

• Flow of control
• Assignments
• Condition handlers
• Signal and Resignal conditions
• SQL path specification
• Declarations

The SQL Standard (ISO/IEC 9075)
28
ISO/IEC 9075-5 Host Language Bindings
(SQL/Bindings)

• Embedded SQL
• Static SQL
• Dynamic SQL
• Support for
• Ada, C, COBOL, FORTRAN, MUMPS, Pascal, PL/I
• Direct SQL

The SQL Standard (ISO/IEC 9075)
29
ISO/IEC 9075-5 Host Language Bindings
(SQL/Bindings)
Embedded SQL
Precompiler
SQL Module
Standard Programming Language
The SQL Standard (ISO/IEC 9075)
30
ISO/IEC 9075Conformance

• Core
• Features
• Packages

The SQL Standard (ISO/IEC 9075)
31
ISO/IEC 9075 Conformance

• All of SQL1992 Entry level
• Some Transitional SQL1992 features
• Some Intermediate SQL1992 features
• Some Full SQL1992 features
• The following new features of SQL1999
• Distinct data types
• WITH HOLD cursors
• SQL-invoked routines (no PATHs)
• CALL statement
• RETURN statement
• ROUTINES and PARAMETERS view
• SQL-invoked routines written in SQL and an
  external language
• Value expression in order by clause

The SQL Standard (ISO/IEC 9075)
32
ISO/IEC 9075 Conformance
Packages
PKG001 Enhanced datetime facilities PKG002
Enhanced integrity management PKG003 OLAP
facilities PKG004 PSM PKG005 CLI PKG006 Basic
object support PKG007 Enhanced object
support PKG008 Active database PKG009 SQL/MM
support
The SQL Standard (ISO/IEC 9075)
33
ISO/IEC 9075-9 SQL/Management of External Data

• Datalink data type
• Foreign tables
• Foreign servers
• Foreign data wrappers
• User mappings

The SQL Standard (ISO/IEC 9075)
34
ISO/IEC 9075-10Object Language Bindings
(SQL/OLB)
Objectives

• Simple embedding of static SQL statements in Java
  programs
• Permit assembly of binary components produced by
  different tools
• Binary portability
• Hardware
• Operating Systems
• Database Systems
• Co-exist with JDBC

The SQL Standard (ISO/IEC 9075)
35
ISO/IEC 9075-10Object Language Bindings
(SQL/OLB)

• Based on JSQL
• Extends Java to include SQL constructs as
  statements and expressions
• A JSQL translator that transforms those JSQL
  clauses into standard Java code that accesses the
  database through a call interface
• An alternative to JDBC (JavaSoft)
• Static instead of Dynamic
• Tighter integration

The SQL Standard (ISO/IEC 9075)
36
ISO/IEC 9075-10Object Language Bindings
(SQL/OLB)
Advantages

• Simple
• Static
• compile time syntax and type checking
• Strongly typed cursors
• iterators
• Pre-compilation
• Permits vendor customisation
• at deployment

The SQL Standard (ISO/IEC 9075)
37
ISO/IEC 9075-10Object Language Bindings
(SQL/OLB)
SQLJ translator framework
Java Class Files
SQLJ program
SQLChecker
SQLChecker
Java Frontend
SQLJ Customizations
Profile Customizer Utility
SQLJ Profiles
SQLJ Translator
SQLJ JAR FILE
38
ISO/IEC 9075Amd1SQL/OLAP

• ROLLUP
• CUBE
• Unary grouped table aggregate functions
• Binary grouped table aggregate functions
• Inverse distribution functions
• What-if functions
• Numeric Functions
• Windows

The SQL Standard (ISO/IEC 9075)
39
ISO/IEC 9075Amd1SQL/OLAP
Windows
Cumulative sum and centred average SELECT
SH.Territory, SH.Month, SH.Sales, SUM (SH.Sales)
OVER Wa AS Cumulative_sum, AVG (SH.Sales) OVER Wb
AS Centred_average FROM Sales_history AS
SH WINDOW WRoot AS ( PARTITION BY SH.Territory
ORDER BY SH.Month ASC ), Wa
AS ( W12root ROWS UNBOUNDED PRECEDING ), Wb
AS ( W12root ROWS BETWEEN 1 PRECEDING
AND 1 FOLLOWING)
The SQL Standard (ISO/IEC 9075)
40
SQL 4
Revised SQL 3 SQL/JRT Java in Database
Procedures Java Data types in SQL SQL/XML SQL/Repl
ication SQL/Temporal
?
The SQL Standard (ISO/IEC 9075)
41
ISO/IEC 9075SQL/JRT

• Direct use of existing Java libraries
• Any Java static method callable as a stored
  procedure
• Portable across DBMSs
• Deployable across tiers
• Implementation transparent for user
• Equivalent functionality
• Body of SQLJ stored procedure routines can use
  JDBC and/or SQLJ to access SQL, or Java
  computation

The SQL Standard (ISO/IEC 9075)
42
ISO/IEC 9075SQL/JRT

• Use Java classes as SQL data types for
• Columns of SQL tables and views.
• Parameters of SQL routines.
• Advantage to SQL
• A type extension mechanism.
• A supplement to SQL1999 ADTs.
• Advantage to Java
• Direct support for Java objects in SQL databases.
• No need to map Java objects to SQL scalar or BLOB
  types.

The SQL Standard (ISO/IEC 9075)
43
ISO/IEC 9075SQL/XML
Scope

• Specifications for the representation of SQL data
  (specifically rows and tables of rows, as well as
  views and query results) in XML form, and vice
  versa.
• Specifications associated with mapping SQL
  schemata to and from XML schemata. This may
  include performing the mapping between existing
  arbitrary XML and SQL schemata.
• Specifications for the representation of SQL
  Schemas in XML.
• Specifications for the representation of SQL
  actions (insert, update, delete).
• Specifications for messaging for XML when used
  with SQL.
• Specifications of the (perhaps a) manner in
  which SQL language can be used with XML.

The SQL Standard (ISO/IEC 9075)
44
SQL/Replication
Web Thin-Client
N-Tier C/S
An Ideal Distributed System
Head-Office Servers
1-Tier C/S
Mobile Devices
Relational Transport Layer

• no new relational concepts business-rule driven
• every site a peer update-anywhere
• every site autonomous, all transactions local no
  point of failure
• automatic, transparent just add a database
  wherever needed
• all a users favourite tools Java/VB/HTML/X/C/P
  erl/Delphi/, 1-tier/N-tier/Web/Windows/Unix
  OLTP/OLAP/DSS/

The SQL Standard (ISO/IEC 9075)
45
SQL/Replication
Approaches
The SQL Standard (ISO/IEC 9075)
46
SQL/Replication
RequiredBandwidth for Trickle Replication (Offic
e Hours)
10 changeper month41 compression
LocalDatabaseSize
Rate of Change
The SQL Standard (ISO/IEC 9075)
47
SQL/Replication
Decreasingly Consistent Image
conflicts
conflict avoidance
Increasing Propagation Time
Single System Image
log replay
net change
hub-and-spoke
parallelised
multi-master, update-anywhere
single server, or 2PC
single master for all changes
The SQL Standard (ISO/IEC 9075)
48
ISO/IEC 9075-7 Temporal (SQL/Temporal)

• One Valid Time Line
• Transaction Time Line
• Upwards compatible
• Structural constraints
• Simple Syntax
• No interest according to database vendors
• Religious wars in standards body

?
The SQL Standard (ISO/IEC 9075)
49
SQL/MM

• Multimedia Application Packages
• Based on the facilities of SQL1999
• A multi-part standard

• Full Text
• Spatial
• Still Image
• Still Graphics
• Animation
• Full Motion Video
• Seismic
• Music

• Data Mining

50
end