Introduction to Databases for the Web: Pt. 2

September 13, 1998

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;







The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers