Chapter 3. Understanding SQL

Table of Contents
Introduction to SQL
Introduction to Relational Databases
SQL Statements
Data Types
Tables in PostgreSQL

This chapter discusses the history and fundamental concepts of SQL and forms the foundation for the next chapter, which is about applying SQL with PostgreSQL. It addresses the basics of relational databases, object-related database extensions, the structure of a SQL statement, and provides an overview of PostgreSQL-supported data types, operators and functions.

Introduction to SQL

SQL, the Structured Query Language, is a mature, powerful, and versatile relational query language. The history of SQL extends back to IBM research begun in 1970. The next few sections discuss the history of SQL, its predecessors, and the various SQL standards that have developed over the years.

A Brief History of SQL

The relational model, from which SQL draws much of its conceptual core, was formally defined in 1970 by Dr. E. F. Codd, a researcher for IBM, in a paper entitled A Relational Model of Data for Large Shared Data Banks. This article generated a great deal of interest in both the feasibility and practical commercial application of such a system.

In 1974 IBM began the System/R project and with the work of Donald Chamberlin and others, developed SEQUEL, or Structured English Query Language. System/R was implemented on an IBM prototype called SEQUEL-XRM in 1974–75. It was then completely rewritten in 1976–1977 to include multi-table and multiuser features. When the system was revised it was briefly called "SEQUEL/2," and then re-named "SQL" for legal reasons.

In 1978, methodical testing commenced at customer test sites. Demonstrating both the usefulness and practicality of the system, this testing proved to be a success for IBM. As a result, IBM began to develop commercial products that implemented SQL based on their System R prototype, including SQL/DS (introduced in 1981), and DB2 (in 1983).

Several other software vendors accepted the rise of the relational model and announced SQL-based products. These included Oracle (who actually beat IBM to market by two years by releasing their first commercial RDBMS, in 1979), Sybase, and Ingres (based on the University of California's Berkeley Ingres project).

Note: PostgreSQL's name is, as you might have guessed, a play on the name Ingres. Both PostgreSQL and Ingres trace their roots back to the UC Berkeley's Ingres RDBMS system.

SQL and Its Predecessors

SQL is based largely on relational algebra and tuple relational calculus. Relational algebra, introduced by E. F. Codd in 1972, provided the basic concepts behind computing SQL syntax. It is a procedural way to construct data-driven queries, and it addresses the how logic of a structured query. The tuple relational calculus (TRC ), on the other hand, affects the underlying appearance of SQL. Relational calculus uses declarative expressions, addressing the what logic of a structured query.

There are additional features that set SQL apart from those that merely implement features that are part of relational algebra or calculus. These features include:

Support for data insertion, modification and deletion

Users are allowed to insert, delete, and modify stored data records.

Arithmetic operators

Arithmetic operations such as addition, subtraction, multiplication, and division (e.g., (value1 * 5) + value2) are allowed, as well as comparison operators (e.g., value3 >= value4).

Display of data

Users may display query-generated relationships (such as a table's contents).


Users may rename a relation that is computed by a query instead of forcing the use of the default relationship name, which may be derived from a column or function name, depending on the query.

Aggregate functions

User may group related rows together and evaluate averages, sums, counts, maximums, and minimums.

SQL Standards

The American National Standards Institute (ANSI) standardized SQL in 1986 (X3.135) and the International Standards Organization (ISO) standardized it in 1987. The United States government's Federal Information Processing Standard (FIPS) adopted the ANSI/ISO standard. In 1989, a revised standard known commonly as SQL89 or SQL1, was published.

Due partially to conflicting interests from commercial vendors, much of the SQL89 standard was intentionally left incomplete, and many features were labeled implementor-defined. In order to strengthen the standard, the ANSI committee revised its previous work with the SQL92 standard ratified in 1992 (also called SQL2). This standard addressed several weaknesses in SQL89 and set forth conceptual SQL features which at that time exceeded the capabilities of any existing RDBMS implementation. In fact, the SQL92 standard was approximately six times the length of its predecessor. As a result of this disparity, the authors defined three levels of SQL92 compliance: Entry-level conformance (only the barest improvements to SQL89), Intermediate-level conformance (a generally achievable set of major advancements), and Full conformance (total compliance with the SQL92 features).

More recently, in 1999, the ANSI/ISO released the SQL99 standard (also called SQL3). This standard addresses some of the more advanced and previously ignored areas of modern SQL systems, such as object-relational database concepts, call level interfaces, and integrity management. SQL99 replaces the SQL92 levels of compliance with its own degrees of conformance: Core SQL99 and Enhanced SQL99.

PostgreSQL presently conforms to most of the Entry-level SQL92 standard, as well as many of the Intermediate- and Full-level features. Additionally, many of the features new in SQL99 are quite similar to the object-relational concepts pioneered by PostgreSQL (arrays, functions, and inheritance).