Introduction to SQL
The computer industry is criss-crossed with languages and standards, most of which are unintelligible to
each other. Here and there, true standards have emerged, and in these cases it is well worth the time of
any programmer to learn them.
Structured Query Language, or SQL as we commonly call it, has, over
the last ten years, emerged as the standard language for programmers to talk with databases through a
Database Management System (DBMS). Oracle, Microsoft SQL Server, Microsoft Access, IBM's DB2,
Sybase, and virtually every other DBMS sold in the last five years use SQL. Knowledge of SQL is
becoming necessary for almost every IT professional. And as the development of basic web sites
becomes common among non-programmers, a grasp of SQL will help them to integrate data into their
This introductory chapter covers topics that students usually ask in the first hour of classes; essentially,
just what do we mean by SQL? We start by explaining what SQL is and also what it is not, and we
provide a brief overview of the history of the language. We'll also clarify some confusing terminology at
this point. Next we look at the types of system set-up in which SQL may be used; discussing front ends,
back ends and how they connect. We then demonstrate some common implementations. We spend
some time explaining why SQL, as a declarative language, is so different from procedural languages like
Visual Basic, C++ or COBOL. We analyze when to move to using SQL, and finally we examine the
human roles within a large data center. So in this chapter we will:
- Learn exactly what SQL is, and its history
- Understand the term ANSI-SQL
- Learn how SQL is implemented via a connection and how this is configured
- Know when to use SQL as a solution
- Understand the human roles in the data center
What is SQL & What Does It Do?
Structured Query Language (abbreviated SQL, pronounced to rhyme with equal) is a computer language
for communication with databases. The communicating parties are typically a "front end" which sends a
SQL Statement across a connection to a "back end" that holds the data. That statement contains
instructions to create, read, change or delete data. The universal rules of the language have been
established by ANSI (American National Standards Institute); a standards committee composed of
database experts from industry, academia and software vendors. Therefore the SQL language is open,
meaning it is not owned or controlled by any single company.
SQL is a non-proprietary (open) language whose rules have been set by a standards committee.
The strength of SQL is its universal acceptance by database vendors. By learning SQL you have a
language that can be used in Visual Basic or C++ to talk to an Oracle database. You can use SQL in an
ASP page to talk to Microsoft SQL Server. You can send a request for data from IBM's DB2 to a Sybase
datastore. You can even use SQL within Access to describe the items you want to include in a form's list
box. There has been a lot of talk and marketing about "write once, run anywhere" languages like Java.
For database programmers, understanding SQL is the ticket to "learn once, profit anywhere."
SQL has many capabilities, but the most common needs in business are to:
- Read existing data
- Create new records holding data
- Change existing data
- Delete data
SQL contains key words or parts to perform these basic tasks. Learning the basics and embellishments
of those commands will consume most of this book. But before we begin to look at the syntax and lists
of common mistakes, we'll look at some examples of each these operations in the next few paragraphs.
Reading data is the most common task. An ANSI-SQL statement requesting a list of names of all
members of your society that live in New York, can be sent from a Visual Basic application to an Oracle
database. If the database is later changed to IBM's DB2, the SQL statement is still valid. The SQL
language offers many permutations of the request, including the ability to return the names in various
orders, only the first or last few names, a list of names without duplicates and various other requests
where people require specific information from their database.
Records can be created in a datastore using SQL. A form page on a web site can gather information
from a visitor and then put that data into a SQL statement. The SQL statement will instruct the
datastore to insert a new record into a Microsoft SQL Server database. Since SQL is universally
accepted, the same SQL statement could, for example, be used for clerks that create new records from,
say, a Visual Basic application on their local network.
Data can also be changed using SQL. As in the examples above, a front end user interface such as a web
page can accept changes to data and send them via a SQL statement to the datastore. But there does not
have to be direct user interaction. A DB2 database running on an IBM mainframe could have a
procedure to connect to another corporate mainframe running Sybase. The IBM can generate and send
a SQL statement to modify the data in certain records in the Sybase database. Although the systems are
from different vendors and have different ways of storing and using data, they both understand the SQL
Deleting data can be performed using SQL statements. In fact SQL can accommodate very complex sets
of conditions for which records to delete and which to leave intact. Portions of data within a record can