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.
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.
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 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).
Assignment
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.
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).
