The Basics of the SQL Database
As we said, SQL
(Structured Query language) is the language of choice for most
modern multi-user, relational databases. That is because SQL provides
the syntax and idioms (language) you need to talk to (query) relational
databases in a standardized, cross-platform/product way
(structured).
The beauty of SQL is that it idiomizes the
relational model. Rather than refer to data as a set of pointers,
SQL provides predefined procedures to allow you to use any value
in a table to relate other tables in a database. So long as
a database is structured using the relational model, SQL will
be a natural fit because SQL was designed to make sense in
a relational system. SQL by its very design is a language that
can be used to talk about relating tables.
For the rest of Part One and Two, we will examine
how you will use SQL to access relational databases. However, first
we should say a little bit about the structure of SQL databases before
we plunge into usage.
SQL databases (most modern relational databases),
as you will recall, are composed of a set of row/column-based "tables", indexed by a
"data dictionary". To access data in the tables, you simply use SQL
to navigate the system and produce "views" based on search criteria
defined in the SQL query.
Okay, that was quite a bit of jargon all at once.
Let's step back for a moment and look at each of these terms.
SQL Tables
We have already discussed the concept of tables
in the last part, but let's just refresh our memory in terms of
how tables relate to SQL. A table is a systematic way to store data.
For the most part, a table is just like a spreadsheet.
Tables are composed of rows (records) and each row is
composed of columns (fields).
| Employee Table |
| Employee ID Number |
Employee Name |
Employee Phone |
Salary |
| 001 |
Lim Li Chuen |
654-3210 |
90,000 USD |
| 002 |
Lim Sing Yuen |
651-0987 |
40,000 USD |
| 003 |
Loo Soon Keat |
123-4567 |
50,000 USD |
How the tables are stored by the database you
are using does not really make a difference for you. The
beauty of SQL is that it works independently of the
internal structure of the database. The tables could be
stored as simple flat files on a local PC or as complex,
networked, compressed, encrypted and proprietary data
structures.
All you need to know is the table's
name. If you know the name, you can use SQL to call up
the table.
We'll look at manipulating tables
in detail a bit later. But first, let's look at the
data dictionary.
The Data Dictionary
How does the database know where all of these tables are
located? Well, behind the scenes, the database maintains
a "data dictionary" (a.k.a. catalog) which contains a list of
all the tables in the database as well as pointers to their
locations.
Essentially, the data dictionary is a table
of tables containing a list of all the tables in the database,
as well as the structure of the tables and often, special
information about the database itself.
When you use SQL to talk to the database and
provide a table name, the database looks up the table you referred
to in the data dictionary. Of course, you needn't worry about
the data dictionary; the database does all the searching itself.
As we said before, you just need to know the name of the table you want
to look at.
It is interesting to note that
because the data dictionary is a table,
in many databases, you can even query the data
dictionary itself to get information about your
environment. This can often be a very useful tool when
exploring a new database.
Okay, so how do you actually grab table
data using the data dictionary? Well, in an SQL database
you create "views". Let's examine views a bit.
IE 7
Firefox 2.0