Introduction to Databases for the Web: Pt. 2

Retrieving Data

Okay, enough of all that DB Admin stuff.
Let’s get down to the nitty gritty of retrieving data from your
database.

In SQL, the “SELECT - FROM” statement
is used to grab data from a database. The statement follows
the generic syntax of:

SELECT column_name
FROM table_name;

There are a few things to notice about
the format of this SQL statement that will help us understand
all SQL statement formats.

First, notice that the statement
ends in a semicolon. Like English, SQL requires termination
punctuation so that the SQL database will know when you
are done speaking to it. In the case of SQL, the semicolon (;)
character works like the period in English.

Second, notice that
the statement spans multiple lines. This is more of
a convention than a necessity. Like HTML, SQL ignores
whitespace in a statement, so you could just as easily write
SELECT column_name FROM table_name. The reason we break the
statement up into multiple lines is to increase readability. As
your SQL statements get more and more complex, you will find
that if you break them up into logical blocks, they will be
easier to read.

Finally, notice that we have used all
uppercase letters for our keywords (like SELECT and FROM).
This is a good idea as even though most implementations of
SQL are case insensitive, some are not. Okay, let’s get back
to the example…

In other words, if you wanted to get
all the names of the employees in the EMPLOYEE table, you
would type:

SELECT EMP_NAME
FROM EMPLOYEES;

In the case of our
sample database,
you would get the following results:

    EMP_NAME
    -------------
    Lim Li Chuen
    Lim Sing Yuen
    Loo Soon Keat
    -------------

We are going to be using the
sample database
throughout the day in order to show examples, so get
familiar with the tables.

You needn’t limit yourself to single
columns of course, The column_name parameter may take
a comma delimited list so that if you also wanted a report for
the employee number and salary, for example, you would simply
use:

SELECT EMP_NAME, EMP_NUMBER, EMP_SALARY
FROM EMPLOYEES;

In this case, you would get the following
results:

EMP_NAME	EMP_NUMBER 	EMP_SALARY
------------------------------------------
Lim Li Chuen	001		90,000
Lim Sing Yuen	002		40,000
Loo Soon Keat	003		50,000
------------------------------------------

Note that if you specify a
column name that does not exist, you will get an error.

Wildcards

As in most languages, SQL provides a set of
wildcards that are used as shortcuts to represent whole
categories of values. For example,
oftentimes, you may want all the data for the columns
in your table but you don’t want to write all the column names
in a comma-delimited list.

To make such queries more efficient,
SQL provides the “*” wildcard that specifies “ALL” of something.
For example, to select all the columns in the
PRODUCTS table, we
would use:

SELECT *
FROM PRODUCTS;

The database would then respond with:

P_NUM	P_QUANTITY	P_PRICE
-------------------------------
001	104	 	 99.99
002	12		865.99
003	2000   		 50.00
-------------------------------

Of course you could achieve the same
results (with more work) using:

SELECT P_NUM, P_QUANTITY, P_PRICE
FROM PRODUCTS;