The SQL Standard ISO IEC 9075 S J Cannan

The SQL Standard (ISO/IEC 9075) S J Cannan Technical Manager Where it is and where it is going

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

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)

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 9075: Amd 1 SQL/OLAP The SQL Standard (ISO/IEC 9075)

The Standardisation Process NWI s l posa ro Working Draft Proposals Committee Draft Co m P m en ts Final Committee Draft The SQL Standard (ISO/IEC 9075) m om Yes/No Draft International Standard C International Standard en ts Technical Corrigenda

ISO/IEC 9075 -1: Framework (SQL/Framework) Description of Parts Terminology Basic Concepts Basic Conformance Clause The SQL Standard (ISO/IEC 9075)

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)

ISO/IEC 9075 -2: MOOSE Large Objects Arrays User-defined types Subtypes and inheritance Encapsulation Substitutability Transforms The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075 -2: Data Access Data Manipulation Statements INSERT UPDATE DELETE SELECT The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075 -2: Predicates Comparison (=, <, >, <=, >=, <>) value BETWEEN [A]SYMMETRIC value 2 AND value 3 value IN subquery or (value-list) value [NOT] LIKE pattern [ESCAPE char] row-value IS [NOT] NULL row-value comp-op ALL|[SOME|ANY] subquery EXISTS subquery UNIQUE subquery row-value MATCH [UNIQUE] [SIMPLE|PARTIAL|FULL] subquery row-value 1 OVERLAPS row-value 2 value [NOT] SIMILAR TO pattern [ESCAPE char] row-value 1 IS DISTINCT FROM row-value 2 value IS [NOT] OF ( type-list ) The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075 -2: Cursors Sensitivity ASENSITIVE INSENSITIVE implies READ ONLY Holdable ORDER BY columns not in select list expressions The SQL Standard (ISO/IEC 9075)

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. carrier=f. 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 <> b. Carrier AND a. New. Cost >= b. New_Cost The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075 -2: Recursion • Fixpoint theory • Unique solutions • Transformation on the right hand side of a recursive definition must be monotonically increasing Disallow: • negation that crosses recursion • aggregation that crosses recursion • INTERSECT ALL • EXCEPT DISTINCT (on right hand side) • FULL OUTER JOIN (LEFT|RIGHT with recursion on right|left) The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075 -2: Recursion Mutual Recursion WITH RECURSIVE Even (N) AS ( VALUES (0) UNION SELECT M + FROM Odd (M) AS ( SELECT N + FROM Even SELECT * FROM Even WHERE N < 12; The SQL Standard (ISO/IEC 9075) 1 )

ISO/IEC 9075 -2: F 261 Case expression SELECT CASE WHEN abbreviation = ‘CA’ THEN ‘California’ WHEN abbreviation = ‘SD’ THEN ‘South Dakota’ WHEN. . . ELSE ‘Unknown’ SELECT emp_name, deptno END FROM states FROM employee WHERE ( CASE bonus + commission WHERE. . . WHEN 0 THEN NULL ELSE salary/(bonus+commission) ) > 10 Used to “implement” COALESCE SELECT COALESCE (nickname, first_name, surname, ‘Unknown’) FROM people The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075 -2: OLAP Enhances query capabilities CUBE ROLLUP GROUPING SETS Expressions in ORDER BY The SQL Standard (ISO/IEC 9075)

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 FROM WHERE GROUP month, region, sales_mgr, SUM (price) Sales year = 1996 BY ROLLUP (month, region, sales_mgr); The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075 -2: OLAP The SQL Standard (ISO/IEC 9075)

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 FROM WHERE GROUP month, region, sales_mgr, SUM(price) Sales year = 1996 BY CUBE (month, region, sales_mgr); The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075 -2: OLAP The SQL Standard (ISO/IEC 9075)

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)

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)

ISO/IEC 9075 -2: Data Description The Information Schema short name views The Definition Schema The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075 -2: Miscellaneous Transaction Statements Connection Statements Diagnostic Statements The SQL Standard (ISO/IEC 9075)

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)

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)

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)

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)

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

ISO/IEC 9075: Conformance • Core • Features • Packages The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075: Conformance • • • All of SQL: 1992 Entry level Some Transitional SQL: 1992 features Some Intermediate SQL: 1992 features Some Full SQL: 1992 features The following new features of SQL: 1999 • 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)

ISO/IEC 9075: Conformance Packages PKG 001 Enhanced datetime facilities PKG 002 Enhanced integrity management PKG 003 OLAP facilities PKG 004 PSM PKG 005 CLI PKG 006 Basic object support PKG 007 Enhanced object support PKG 008 Active database PKG 009 SQL/MM support The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075 -9: SQL/Management of External Datalink data type Foreign tables Foreign servers Foreign data wrappers User mappings The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075 -10: Object 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)

ISO/IEC 9075 -10: Object 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 (Java. Soft) Static instead of Dynamic Tighter integration The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075 -10: Object 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)

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

ISO/IEC 9075 Amd 1: SQL/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)

ISO/IEC 9075 Amd 1: SQL/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 ( W 12 root ROWS UNBOUNDED PRECEDING ), Wb AS ( W 12 root ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING); The SQL Standard (ISO/IEC 9075)

SQL 4 Revised SQL 3 + SQL/JRT ? Java in Database Procedures Java Data types in SQL/XML SQL/Replication SQL/Temporal The SQL Standard (ISO/IEC 9075)

ISO/IEC 9075: SQL/JRT Direct use of existing Java libraries Any Java static method callable as a stored procedure Portable across DBMS’s 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)

ISO/IEC 9075: SQL/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 SQL: 1999 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)

ISO/IEC 9075: SQL/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)

SQL/Replication An “Ideal” Distributed System N-Tier 1 -Tier C/S Mobile Devices Head-Office Servers Web & Thin-Client 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 user’s favourite tools: Java/VB/HTML/X/C++/Perl/Delphi/…, 1 -tier/N-tier/Web/Windows/Unix…; OLTP/OLAP/DSS/… The SQL Standard (ISO/IEC 9075)

SQL/Replication Approaches Central Server Scalability one site Autonomy/ Availability Performance one site good (if ‘local and one server’ is enough) Points of Failure one site Data immediate Currency The SQL Standard (ISO/IEC 9075) Traditional Synchronous: Queue-Based Ideal N-Phase Asynchronous Commit Replication < 20 sites poor for updates many immediate < 100 sites (hub/spoke) full, local (if updateanywhere) local 1000+ (auto detection, auto config, dyn. load bal. ) full, local any subset live one or many (repl. clustering, (for repl. ) propagated failover) parallelised propagation

SQL/Replication Rate of Change Local Database Size Mobile Laptop Required 10% change Bandwidth for per month Trickle 4: 1 Replication compression (Office Hours) 50 MB Workgroup Server Regional Server Headquarters / Warehouse The SQL Standard (ISO/IEC 9075) 60 K / day Cell, 28. 8 K modem 1 GB 1. 1 M / day 28. 8 K modem 20 GB 23 M / day 56 K modem 1 TB 1 G / day 256 K ISDN

conflicts conflict avoidance SQL/Replication Decreasingly Consistent Image Single System Image Increasing Propagation Time log replay net change hub-and-spoke parallelised single server, or 2 PC “multi-master, ” update-anywhere single “master” for all changes The SQL Standard (ISO/IEC 9075)

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)

SQL/MM Multimedia & Application Packages Based on the facilities of SQL: 1999 A multi-part standard Full Text Data Mining Spatial Still Image Still Graphics Animation Full Motion Video Seismic Music

end…