Sams Teach Yourself SQL in 24 Hours

Recommended Book 

Introduction to the SELECT Statement

The SELECT statement, the command that represents Data Query Language (DQL) in SQL, is the statement used to construct database queries. The SELECT statement is not a standalone statement, which means that one or more additional clauses (elements) are required for a syntactically correct query. In addition to the required clauses, there are optional clauses that increase the overall functionality of the SELECT statement. The SELECT statement is by far one of the most powerful statements in SQL. The FROM clause is the mandatory clause and must always be used in conjunction with the SELECT statement.

There are four keywords, or clauses, that are valuable parts of a SELECT statement. These keywords are as follows:

• SELECT
• FROM
• WHERE
• ORDER BY

Each of these keywords is covered in detail during the following sections.

The SELECT Statement

The SELECT statement is used in conjunction with the FROM clause to extract data from the database in an organized, readable format. The SELECT part of the query is for selecting the data you want to see according to the columns in which they are stored in a table.

The syntax for a simple SELECT statement is as follows:

   
SELECT [ * | ALL | DISTINCT COLUMN1, COLUMN2 ]
FROM TABLE1 [ , TABLE2 ];

The SELECT keyword in a query is followed by a list of columns that you want displayed as part of the query output. The FROM keyword is followed by a list of one or more tables from which you want to select data. The asterisk (*) is used to denote that all columns in a table should be displayed as part of the output. Check your particular implementation for its usage. The ALL option is used to display all values for a column, including duplicates. The DISTINCT option is used to suppress duplicate rows from being displayed in the output. The default between DISTINCT and ALL is ALL, which does not have to be specified. Notice that the columns following the SELECT are separated by commas, as is the table list following the FROM.

Arguments are values that are either required or optional to the syntax of a SQL statement or command.

Explore the basic capabilities of the SELECT statement by studying the following examples. First, perform a simple query from the PRODUCTS_TBL table:

   

   SELECT * FROM PRODUCTS_TBL;

   
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ ------
11235      WITCHES COSTUME                29.99
222        PLASTIC PUMPKIN 18 INCH         7.75
13         FALSE PARAFFIN TEETH            1.1
90         LIGHTED LANTERNS               14.5
15         ASSORTED COSTUMES              10
9          CANDY CORN                      1.35
6          PUMPKIN CANDY                   1.45
87         PLASTIC SPIDERS                 1.05
119        ASSORTED MASKS                  4.95
1234       KEY CHAIN                       5.95
2345       OAK BOOKSHELF                  59.99

11 rows selected.

The asterisk represents all columns in the table, which, as you can see, are displayed in the form PROD_ID, PROD_DESC, and COST. Each column in the output is displayed in the order that it appears in the table. There are 11 records in this table, identified by the feedback 11 rows selected. This feedback differs among implementations; for example, another feedback for the same query would be 11 rows affected.

Now select data from another table, CANDY_TBL. Create this table in the image of the PRODUCTS_TBL table for the following examples. List the column name after the SELECT keyword to display only one column in the table:

   

   SELECT PROD_DESC FROM CANDY_TBL;

   
PROD_DESC
------------------
CANDY CORN
CANDY CORN
HERSHEYS KISS
SMARTIES

4 rows selected.

Four records exist in the CANDY_TBL table. You have used the ALL option in the next statement to show you that the ALL is optional and redundant. There is never a need to specify ALL; it is a default option.

   

   SELECT ALL PROD_DESC

   FROM CANDY_TBL;

   
PROD_DESC
-------------------
CANDY CORN
CANDY CORN
HERSHEYS KISS
SMARTIES

4 rows selected.

The DISTINCT option is used in the following statement to suppress the display of duplicate records. Notice that the value CANDY CORN is only printed once in this example.

   

   SELECT DISTINCT PROD_DESC

   FROM CANDY_TBL;

   
PROD_DESC
------------------
CANDY CORN
HERSHEYS KISS
SMARTIES

3 rows selected.

DISTINCT and ALL can also be used with parentheses enclosing the associated column. The use of parentheses is often used in SQL—as well as many other languages—to improve readability.

   

   SELECT DISTINCT(PROD_DESC)

   FROM CANDY_TBL;

   
PROD_DESC
------------------
CANDY CORN
HERSHEYS KISS
SMARTIES

3 rows selected.

The FROM Clause

The FROM clause must be used in conjunction with the SELECT statement. It is a required element for any query. The FROM clause's purpose is to tell the database what table(s) to access to retrieve the desired data for the query. The FROM clause may contain one or more tables. The FROM clause must always list at least one table.

The syntax for the FROM clause is as follows:

   
FROM TABLE1 [ , TABLE2 ]

Using Conditions to Distinguish Data

A condition is part of a query that is used to display selective information as specified by the user. The value of a condition is either TRUE or FALSE, thereby limiting the data received from the query. The WHERE clause is used to place conditions on a query by eliminating rows that would normally be returned by a query without conditions.

There can be more than one condition in the WHERE clause. If there is more than one condition, they are connected by the AND and OR operators, which are discussed during Hour 8, "Using Operators to Categorize Data." As you also learn during the next hour, there are several conditional operators that can be used to specify conditions in a query. This hour only deals with a single condition for each query.

An operator is a character or keyword in SQL that is used to combine elements in a SQL statement.

The syntax for the WHERE clause is as follows:

   
SELECT [ ALL | * | DISTINCT COLUMN1, COLUMN2 ]
FROM TABLE1 [ , TABLE2 ]
WHERE [ CONDITION1 | EXPRESSION1 ]
[ AND CONDITION2 | EXPRESSION2 ]

The following is a simple SELECT without conditions specified by the WHERE clause:

   

   SELECT *

   FROM PRODUCTS_TBL;

   
PROD_ID    PROD_DESC                       COST
---------- ------------------------------ ------
11235      WITCHES COSTUME                29.99
222        PLASTIC PUMPKIN 18 INCH         7.75
13         FALSE PARAFFIN TEETH            1.1
90         LIGHTED LANTERNS               14.5
15         ASSORTED COSTUMES              10
9          CANDY CORN                      1.35
6          PUMPKIN CANDY                   1.45
87         PLASTIC SPIDERS                 1.05
119        ASSORTED MASKS                  4.95
1234       KEY CHAIN                       5.95
2345       OAK BOOKSHELF                  59.99

11 rows selected.

Now add a condition for the same query.

   

   SELECT * FROM PRODUCTS_TBL

   WHERE COST < 5;

   
PROD_ID    PROD_DESC                       COST
---------- ------------------------------- -----
13         FALSE PARAFFIN TEETH            1.1
9          CANDY CORN                      1.35
6          PUMPKIN CANDY                   1.45
87         PLASTIC SPIDERS                 1.05
119        ASSORTED MASKS                  4.95

5 rows selected.

The only records displayed are those that cost less than $5.

In the following query, you want to display the product description and cost that matches the product identification 119.

   

   SELECT PROD_DESC, COST

   FROM PRODUCTS_TBL

   WHERE PROD_ID = '119';

   
PROD_DESC                       COST
------------------------------- -----
ASSORTED MASKS                  4.95

1 row selected.

Sorting Your Output

You usually want your output to have some kind of order. Data can be sorted by using the ORDER BY clause. The ORDER BY clause arranges the results of a query in a listing format you specify. The default ordering of the ORDER BY clause is an ascending order; the sort displays in the order A–Z if it's sorting output names alphabetically. A descending order for alphabetical output would be displayed in the order Z–A. Ascending order for output for numeric values between 1 and 9 would be displayed 1–9; descending order is displayed as 9–1.

The syntax for the ORDER BY is as follows:

   
SELECT [ ALL | * | DISTINCT COLUMN1, COLUMN2 ]
FROM TABLE1 [ , TABLE2 ]
WHERE [ CONDITION1 | EXPRESSION1 ]
[ AND CONDITION2 | EXPRESSION2 ]
ORDER BY COLUMN1|INTEGER [ ASC|DESC ]

Begin your exploration of the ORDER BY clause with an extension of one of the previous statements. Order by the product description in ascending order or alphabetical order. Note the use of the ASC option. ASC can be specified after any column in the ORDER BY clause.

   

   SELECT PROD_DESC, PROD_ID, COST

   FROM PRODUCTS_TBL

   WHERE COST < 20

   ORDER BY PROD_DESC ASC;

   
PROD_DESC                 PROD_ID          COST
------------------------- --------------- ------
ASSORTED COSTUMES         15              10
ASSORTED MASKS            119              4.95
CANDY CORN                9                1.35
FALSE PARAFFIN TEETH      13               1.1
LIGHTED LANTERNS          90              14.5
PLASTIC PUMPKIN 18 INCH   222              7.75
PLASTIC SPIDERS           87               1.05
PUMPKIN CANDY             6                1.45

8 rows selected.

You can use DESC, as in the following statement, if you want the same output to be sorted in reverse alphabetical order.

   

   SELECT PROD_DESC, PROD_ID, COST

   FROM PRODUCTS_TBL

   WHERE COST < 20

   ORDER BY PROD_DESC DESC;

   
PROD_DESC                 PROD_ID          COST
------------------------- --------------- ------
PUMPKIN CANDY             6                1.45
PLASTIC SPIDERS           87               1.05
PLASTIC PUMPKIN 18 INCH   222              7.75
LIGHTED LANTERNS          90              14.5
FALSE PARAFFIN TEETH      13               1.1
CANDY CORN                9                1.35
ASSORTED MASKS            119              4.95
ASSORTED COSTUMES         15              10

8 rows selected.

There are shortcuts in SQL. A column listed in the ORDER BY clause can be abbreviated with an integer. The INTEGER is a substitution for the actual column name (an alias for the purpose of the sort operation), identifying the position of the column after the SELECT keyword.

An example of using an integer as an identifier in the ORDER BY clause follows:

   

   SELECT PROD_DESC, PROD_ID, COST

   FROM PRODUCTS_TBL

   WHERE COST < 20

   ORDER BY 1;


   
PROD_DESC                 PROD_ID          COST
------------------------- --------------- ------
ASSORTED COSTUMES         15              10
ASSORTED MASKS            119              4.95
CANDY CORN                9                1.35
FALSE PARAFFIN TEETH      13               1.1
LIGHTED LANTERNS          90              14.5
PLASTIC PUMPKIN 18 INCH   222              7.75
PLASTIC SPIDERS           87               1.05
PUMPKIN CANDY             6                1.45

8 rows selected.

In this query, the integer 1 represents the column PROD_DESC. The integer 2 represents the PROD_ID column, 3 represents the COST column, and so on.

You can order by multiple columns in a query, using either the column name itself or the associated number of the column in the SELECT:

ORDER BY 1,2,3 

Columns in an ORDER BY clause are not required to appear in the same order as the associated columns following the SELECT, as shown by the following example:

ORDER BY 1,3,2 

Case Sensitivity

Case sensitivity is a very important concept to understand when coding with SQL. Typically, SQL commands and keywords are not case-sensitive, which allows you to enter your commands and keywords in either uppercase or lowercase—whatever you prefer. The case may be mixed (both uppercase and lowercase for a single word or statement). See Hour 5, "Manipulating Data," on case sensitivity.

Case sensitivity is, however, a factor when dealing with data in SQL. In most situations, data seems to be stored exclusively in uppercase in a relational database to provide data consistency.

For instance, your data would not be consistent if you arbitrarily entered your data using random case:

SMITH 

Smith

smith

If the last name was stored as smith and you issued a query as follows, no rows would be returned.

SELECT * 
FROM EMPLOYEE_TBL
WHERE LAST_NAME = 'SMITH';