SQL

SQL
Name	SQL
Paradigm	Declarative
Designer	Donald D. Chamberlain, Raymond F. Boyce
Developer	IBM
Typing	Static, strong
Influenced	CQL, LINQ, SPARQL

SQL. Structured Query Language is a domain-specific language used in programming and designed for managing data held in a relational database management system. It is particularly useful in handling structured data where there are relations between different entities of the data. The language allows users to query, update, and manage relational databases, forming a cornerstone for systems like Oracle Database, Microsoft SQL Server, and MySQL.

Overview

SQL operates through declarative statements, enabling users to specify what data to retrieve or manipulate without detailing the procedural steps. It is the standard language for relational database management systems, as defined by the American National Standards Institute and the International Organization for Standardization. Core commands are grouped into Data Definition Language, Data Manipulation Language, Data Control Language, and Transaction Control Language, facilitating comprehensive database administration. Major implementations from vendors like IBM, Microsoft, and Oracle Corporation often include proprietary extensions alongside standard SQL features.

History

The development of SQL began at IBM in the early 1970s by Donald D. Chamberlain and Raymond F. Boyce, initially named SEQUEL. This work was part of the System R project at the IBM San Jose Research Laboratory, which aimed to prove the feasibility of the relational model conceived by Edgar F. Codd. The first commercial relational database system featuring SQL was Oracle Database, released by Oracle Corporation. Standardization efforts commenced in 1986 by the American National Standards Institute, with subsequent revisions managed jointly with the International Organization for Standardization.

Syntax and semantics

SQL syntax is based on a combination of relational algebra and tuple relational calculus, providing a declarative framework for data operations. A fundamental query uses the `SELECT` statement to retrieve data from tables, often incorporating clauses like `FROM`, `WHERE`, `GROUP BY`, and `ORDER BY`. The semantics define how queries are evaluated, typically following a logical order of processing that begins with the `FROM` clause. While the core syntax is standardized, implementations like Transact-SQL from Microsoft and PL/SQL from Oracle Corporation introduce variations and enhancements.

Language elements

Key SQL elements include clauses, expressions, predicates, queries, and statements. Clauses are constituent components of statements and queries, such as the `WHERE` clause for filtering. Expressions produce scalar values or tables, including scalar subqueries. Predicates specify conditions in search conditions and can use three-valued logic. Queries, constructed with the `SELECT` statement, retrieve data based on specific criteria. Statements may permanently affect schemas and data, like `CREATE TABLE` or `INSERT`, and are often grouped under Data Definition Language and Data Manipulation Language.

Interoperability and standardization

SQL standards are maintained by the International Organization for Standardization and the International Electrotechnical Commission, with notable versions including SQL:1999, SQL:2003, and SQL:2016. These standards aim to ensure portability of code and skills across different systems like PostgreSQL, IBM Db2, and SAP HANA. However, full interoperability is challenged by proprietary extensions from vendors such as Microsoft with Transact-SQL and the divergence in handling advanced features like window functions or JSON support.

Use cases and applications

SQL is fundamental to online transaction processing systems in industries like finance, supporting operations at institutions like JPMorgan Chase. It underpins data warehousing solutions and business intelligence platforms, including those from SAP and Salesforce. In web development, applications built with PHP, Python, and the Django framework commonly use SQL databases like MySQL or PostgreSQL for data persistence. It is also critical for data analysis in scientific research and government agencies such as the United States Census Bureau.

Criticisms and limitations

Criticisms of SQL often focus on its deviation from the original relational model as defined by Edgar F. Codd, particularly regarding duplicate rows and null values. The language has been described as having an inconsistent and sometimes awkward syntax, especially in complex joins and subqueries. Performance tuning can be highly dependent on specific implementations from vendors like Oracle Corporation or Microsoft, leading to vendor lock-in. Alternatives like NoSQL databases, including MongoDB and Apache Cassandra, have emerged to address scalability and flexibility limitations for unstructured data.

Category:Data management Category:Programming languages