SQL Implementation
28/07/2009 16:02
Select
What do we use SQL commands for? A common use is to select data from the tables located in a database. Immediately, we see two keywords: we need to SELECT information FROM a table. (Note that a table is a container that resides in the database where the data is stored. For more information about how to manipulate tables, go to the Table Manipulation Section). Hence we have the most basic SQL structure:
SELECT "column_name" FROM "table_name"
To illustrate the above example, assume that we have the following table:
Table Store_Information
|
store_name |
Sales |
Date |
|
Los Angeles |
$1500 |
Jan-05-1999 |
|
San Diego |
$250 |
Jan-07-1999 |
|
Los Angeles |
$300 |
Jan-08-1999 |
|
Boston |
$700 |
Jan-08-1999 |
We shall use this table as an example throughout the tutorial (this table will appear in all sections). To select all the stores in this table, we key in,
SELECT store_name FROM Store_Information
Result:
|
store_name |
|
Los Angeles |
|
San Diego |
|
Los Angeles |
|
Boston |
Multiple column names can be selected, as well as multiple table names.
Distinct
The SELECT keyword allows us to grab all information from a column (or columns) on a table. This, of course, necessarily mean that there will be redundancies. What if we only want to select each DISTINCT element? This is easy to accomplish in SQL. All we need to do is to add DISTINCT after SELECT. The syntax is as follows:
SELECT DISTINCT "column_name"
FROM "table_name"
For example, to select all distinct stores in Table Store_Information,
Table Store_Information
|
store_name |
Sales |
Date |
|
Los Angeles |
$1500 |
Jan-05-1999 |
|
San Diego |
$250 |
Jan-07-1999 |
|
Los Angeles |
$300 |
Jan-08-1999 |
|
Boston |
$700 |
Jan-08-1999 |
we key in,
SELECT DISTINCT store_name FROM Store_Information
Result:
|
store_name |
|
Los Angeles |
|
San Diego |
|
Boston |
Where
Next, we might want to conditionally select the data from a table. For example, we may want to only retrieve stores with sales above $1,000. To do this, we use the WHERE keyword. The syntax is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "condition"
For example, to select all stores with sales above $1,000 in Table Store_Information,
Table Store_Information
|
store_name |
Sales |
Date |
|
Los Angeles |
$1500 |
Jan-05-1999 |
|
San Diego |
$250 |
Jan-07-1999 |
|
Los Angeles |
$300 |
Jan-08-1999 |
|
Boston |
$700 |
Jan-08-1999 |
we key in,
SELECT store_name
FROM Store_Information
WHERE Sales > 1000
Result:
|
store_name |
|
Los Angeles |
And Or
Between
In
In SQL, there are two uses of the IN keyword, and this section introduces the one that is related to the WHERE clause. When used in this context, we know exactly the value of the returned values we want to see for at least one of the columns. The syntax for using the IN keyword is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1', 'value2', ...)
The number of values in the parenthesis can be one or more, with each values separated by comma. Values can be numerical or characters. If there is only one value inside the parenthesis, this commend is equivalent to
WHERE "column_name" = 'value1'
For example, we may wish to select all records for the Los Angeles and the San Diego stores in Table Store_Information,
Table Store_Information
|
store_name |
Sales |
Date |
|
Los Angeles |
$1500 |
Jan-05-1999 |
|
San Diego |
$250 |
Jan-07-1999 |
|
San Francisco |
$300 |
Jan-08-1999 |
|
Boston |
$700 |
Jan-08-1999 |
we key in,
SELECT *
FROM Store_Information
WHERE store_name IN ('Los Angeles',
'San
Diego')
Result:
|
store_name |
Sales |
Date |
|
Los Angeles |
$1500 |
Jan-05-1999 |
|
San Diego |
$250 |
Jan-07-1999 |
Between
Whereas the IN keyword help people to limit the selection criteria to one or more discrete values, the BETWEEN keyword allows for selecting a range. The syntax for the BETWEEN clause is as follows:
SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN 'value1' AND 'value2'
———
Back