1 / 19

Exploring SQL Standards and Flavors: An Overview for CS157B

This presentation by David Wortham delves into the evolution of Structured Query Language (SQL) since its inception in 1974 by Donald Chamberlin and Raymond Boyce at IBM. It covers the various standard revisions, including SQL-86, SQL-92, SQL:1999, SQL:2003, and SQL:2006, highlighting new features introduced and their backward compatibility. The talk also examines common SQL implementations such as Oracle, MySQL, SQLite, and PostgreSQL, explaining differences in stored procedure support and proprietary data types, providing a comprehensive overview of SQL standards and their adoption.

eris
Télécharger la présentation

Exploring SQL Standards and Flavors: An Overview for CS157B

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL: Standards and Flavors A presentation for CS157B by David Wortham

  2. 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

  3. 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

  4. 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

  5. Standards Revisions: SEQUEL • Original definition of SQL • Mostly defined data manipulation and schema definition controls

  6. Standards Revisions: SQL-86 • Ratification and acceptance of a formal SQL standard by ANSI and ISO

  7. 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…

  8. Standards Revisions - SQL:99 • Adds User Defined Types (UDTs) to SQL • Among others…

  9. Standards Revisions - SQL:2003 • XML-related features • MERGE statement defined • SEQUENCE generation • Among others…

  10. Standards Revisions – SQL:2006 • Increased support for XML • Support for XQuery, an XML-SQL interface standard

  11. 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 of SQL

  12. 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

  13. Flavors: MySQL 5.x MySQL 5.x Implements most of SQL:99 standard including: • Stored Procedures • Triggers • XA transactions

  14. Flavors: Oracle 10g Release 2 • Implements much of SQL:2003 • Complete Listing of CORE feature support:http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/ap_standard_sql003.htm

  15. Flavors: SQLite 3.x SQLite 3.x Implements most of SQL:92 standard except: • FOREIGN KEY constraints • Complete trigger support • Complete ALTER TABLE support • Nested transactions • RIGHT and FULL OUTER JOIN • Writing to VIEWs • GRANT and REVOKE

  16. Flavors: PostgreSQL 8.4 • Implements most of SQL:2003 except: • MERGE statement • Assertions, Subqueries in CHECK • Some flagging features • Many array features • BLOB data type • Comprehensive list:http://developer.postgresql.org/pgdocs/postgres/unsupported-features-sql-standard.html

  17. Vendor MarketShare 1 Source: JoinVision E-Services GmbH, July 2006

  18. Vendor MarketShare 2 "Relational Databases Rule the Roost”, SD Times in July 2004

  19. References • MySQL :: MarketSharehttp://www.mysql.com/why-mysql/marketshare/ • SQL:1999http://www.service-architecture.com/database/articles/sql1999.html • PostgreSQL supported and unsupported standard featureshttp://developer.postgresql.org/pgdocs/postgres/features-sql-standard.htmlhttp://developer.postgresql.org/pgdocs/postgres/unsupported-features-sql-standard.html • SQL Features That SQLite Does Not Implementhttp://www.sqlite.org/omitted.html

More Related