SQLStandardsandFlavors.pptVIP

SQL: Standards and Flavors A presentation for CS157B by David Wortham SQL Definition SQL: Structured Query Language Invented in 1974 by Donald Chamberlin and Raymond Boyce for IBM Used for creation and modification of Relational DataBase data and schema Multiple Standard Revisions and Multiple Flavors (Implementations) exist Standard Revisions Differences: Revisions successively introduced additional features Revisions are mostly Backwards-Compatible with older standards Similarities: Ratified by Standards Organizations Incompletely implemented by vendors Standard Revisions andDates of Standardization SEQUEL/Original SQL - 1974 SQL-86 (ANSI/ISO) - 1986 SQL-92 - 1992 SQL:1999 - 1999 SQL:2003 - 2003 SQL:2006 - 2006 Standards Revisions: SEQUEL Original definition of SQL Mostly defined data manipulation and schema definition controls Standards Revisions: SQL-86 Ratification and acceptance of a formal SQL standard by ANSI and ISO Standards Revisions - SQL:92 New datatypes: DATE, TIME, TIMESTAMP, INTERVAL, VARCHAR New scalar operations: string concatenation, date and time mathematics New set operations: UNION JOIN, NATURAL JOIN Schema changes via ALTER and DROP Among others… Standards Revisions - SQL:99 Adds User Defined Types (UDTs) to SQL Among others… Standards Revisions - SQL:2003 XML-related features MERGE statement defined SEQUENCE generation Among others… Standards Revisions – SQL:2006 Increased support for XML Support for XQuery, an XML-SQL interface standard Flavors of SQL Most implementations do not implement the entire standard Schemas and Stored Procedures for different flavors are rarely portable Timestamp data types are usually proprietary Common Flavors: Oracle MySQL SQLite Postgres MSQL (Microsoft) Microsoft Access Flavors: Stored Procedure Support Common Procedural Languages (PLs): ANSI/ISO Standard: SQL/PSM IBM: SQL PL Microsoft/Sybase: T-SQL MySQL: MySQL Oracle: PL/SQL PostgreSQL: PL/pgSQL Flavors: MySQL 5.x MySQL 5.x Implements most of SQL:9