An Introduction to SQL

The way in which you access most of the data in your databases is through the Structured Query Language, or SQL. In 1970, an IBM employee by the name of Edgar F. Codd published some of the earliest works on relational database modeling. Over the next few years, IBM developed the Structured English Query Language (SEQUEL) to apply the model, which eventually became SQL. Over the next decade, a number of companies came out with relational database systems and their own query languages, creating the obvious need for a common functionality.

The American National Standards Institute (ANSI) took up the language for standardization and certification in the 1980s. The result has been a series of ANSI SQL standards, including SQL89, SQL92, and SQL99. The specifications are monstrous (hundreds of pages) and cover a full range of topics.

Most modern database servers strive to be compatible with the SQL92 standard and succeed to varying degrees. In direct conflict with the desire to conform to standards is the compulsion for database server vendors to distinguish their products from others with more features and extensions, and there is always some conflict in interpretation of the standards. Fortunately, we use a subset of ANSI SQL that is sufficiently narrow so that there are only manageable amounts of variation among the database servers.

SQL is different from other languages with which you may be familiar (certainly PHP) because it is a declarative language. Many popular programming languages, such as PHP, C/C++, Java, Perl, and Python are imperative programming languages. In an imperative programming language, you give the computer an explicit sequence of instructions and tasks to perform, and it does them. However, in declarative programming, you give the computer a set of conditions to fulfill and let it decide how to best satisfy them.

An excellent analogy is getting into a taxi. You can simply tell the taxi driver, "Please take me to the Santa Maria Cathedral" and leave it to him to decide which route to take.

Conversely, you could direct the driver to "take Highway 204, the Fuller River Tunnel, and 212^th Street to the Santa Maria Cathedral," in which case he is simply following your directions (even if he knows this is not the best route to take).

Due to being based on the English language, the SQL language is recognizable to readers (at least those with proficiency in English), and is typically a series of verbal instructions followed by details and clarifications. For example, you could instruct the database to

 SELECT user_name,email   FROM Users  WHERE user_name LIKE '%Smith%'; 

which tells it to fetch from the Users table the username and e-mail address for any users with "Smith" anywhere in their username, or

 CREATE TABLE US_States (      id INTEGER PRIMARY KEY,      abbrev CHAR(2),      fullName VARCHAR(50) ); 

which instructs the server to create a table named US_States to store a unique numeric identifier for each state, along with a two-character abbreviation and the full name of the state. SQL statements can span any number of lines and always end in a semi-colon (;). We work with SQL throughout this book, so we will introduce you to features as we use them.

Unfortunately, SQL is so complex that it is difficult to provide a systematic approach to the entire language and its capabilities, especially within the confines of this book. Instead, we will learn the language by area of functionality, learn how to use it to perform certain tasks, and expand our knowledge of it through examples and new requirements. We will first learn how to use it to create databases.