Retrieving data is one of the most common tasks performed on a relational database.

Fortunately, SQL Server includes a language that allows users (and programs) to retrieve the data they want. This language is called Structured Query Language. It is usually abbreviated "SQL" and often pronounced "SEE-kwuhl".

Getting data from a database is known as querying the database. The code to retrieve that data is a query. When this code is written in SQL (as most of my queries are), it is known as a SQL query.

The basic syntax for a SQL query that retrieves data from a single table is

SELECT [List of Columns]
    FROM [Table Name]
    WHERE [Filter Condition]
    ORDER BY [Sort column or columns]

In SQL, line breaks and extra spaces are not important. The language parser is smart enough to figure out when a statement or command ends, so I usually try to format my SQL statements to make them easy to read.

Only the SELECT and FROM parts of the query are required, so let's start with those.

EvenEven though the "SELECT" keyword typically comes first in this type of query, I'll start by explaining the "FROM" keyword. When getting data from only one table, follow the keyword FROM with the name of that table. For example, the clause

    FROM Customer 

indicates that we are getting data from a table named Customer. We'll talk later about how to get data from multiple tables in the same query.

It is possible to provide an alias for a table by following the table name with a space, and the alias. For example

    FROM Customer cust

The above SQL clause says that we will get data from the Customer table, but that we will use the string “cust” to refer to this table elsewhere in our query.

This is useful in the following situations

• You want to provide a shorter name for the table in order to avoid retyping a long name elsewhere in the query
• You want to avoid ambiguity when listing the same table name twice in the FROM clause.

SELECT is the first keyword of this type of query and tells SQL Server that we want to retrieve data from the database. The word "SELECT" is followed by a list of columns that the query will return. If the column list contains duplicate column names (as when you are getting data from two different tables and they each have a column with the same name), you should precede the column name with the table name or alias.

You can also use the special character "*" in place of (or in addition to) the list of column names in order to return all columns in the tables.

The following query returns all columns and rows in the Customer table.

SELECT * 
    FROM Customer 

CustID	FirstName	LastName	StreetAddress	City	State	ZipCode	TotalSales
1	David	Giard	123 Oxford Ct	Erlanger	KY	40111	1000.00
2	Magic	Johnson	456 Hollywood Blvd	Lansing	MI	45222	1500.00
3	Bubba	Smith	789 Killbubbakill St	Baltimore	MD	10111	1000.00
4	Ron	Mason	501 E Grand River Ave	Okemos	MI	45333	2000.00
5	Steve	Smith	900 Belle St	Detroit	MI	48888	5000.00

We can return onWe can return only the FirstName and LastName columns from the Customer table with the following table

SELECT 
        FirstName, 
        LastName 
    FROM Customer 

FirstName	LastName
David	Giard
Magic	Johnson
Bubba	Smith
Ron	Mason
Steve	Smith

In the queries above, it is possible to qualify the column names, explicitly indicating that they are from the Customer table. Of course, in this case it is unnecessary because the column names are unique within the Customer table. The following examples qualify the column names and returns the same data.

SELECT
        Customer.FirstName,
        Customer.LastName
    FROM Customer

SELECT
        cu.FirstName,
        cu.LastName
    FROM Customer cu

By default, each column returned by the query retains the name of the corresponding column in the source table. you want to change the name of a If you want to change the name of a column in the query, alias that column by appending the keyword " AS " followed by the alias you want. For example

SELECT
        FirstName AS FirstNm,
        LastName AS LastNm
    FROM Customer

This will return the following output

FirstNm	LastNm
David	Giard
Magic	Johnson
Bubba	Smith
Ron	Mason
Steve	Smith

If wIf we want to sort the output we can add the ORDER BY clause to our query. The syntax for this clause is

ORDER BY [List of Columns on which to sort]

For example, we can sort our output on Last Name by changing our query to

SELECT
        FirstName,
        LastName
    FROM Customer
    ORDER BY LastName

FirstName	LastName
David	Giard
Magic	Johnson
Steve	Smith
Bubba	Smith
Ron	Mason

We can add more columns to the list of sort columns if we separate each with a comma. The second column is only appropriate in our sort if two rows have identical values for the first column.

For example

SELECT FirstNameFor example

SELECT
        FirstName,
        LastName
    FROM Customer
    ORDER BY LastName, FirstName

FirstName	LastName
David	Giard
Magic	Johnson
Bubba	Smith
Steve	Smith
Ron	Mason

The above result set contains 2 rows with the last name "Smith".  These two rows were sorted in order of their FirstName column.

In many cases, we may not want to return eIn many cases, we may not want to return every row in a table. We can use the WHERE clause to filter data. The syntax of the WHERE clause is

   WHERE [Filter Condition]

The Filter condition is a Boolean expression, meaning it evaluates to either TRUE or FALSE for every row. The query will return only those rows for which this condition evaluates to TRUE.

For example, if we want to get only those customers in Michigan we can use the query

SELECT * 
   FROM Customer 
   WHERE State = 'MI'

CustID	FirstName	LastName	StreetAddress	City	State	ZipCode	TotalSales
2	Magic	Johnson	456 Hollywood Blvd	Lansing	MI	45222	1500.00
4	Ron	Mason	501 E Grand River Ave	Okemos	MI	45333	2000.00
5	Steve	Smith	900 Belle St	Detroit	MI	48888	5000.00

This query only returned those rows that match our filter condition.

Of course, we can combine several of these clauses as in the following

SELECT
        cu.FirstName,
        cu.LastName,
        cu.State AS ResidencyState
    FROM Customer cu
    WHERE cu.State = 'MI'
    ORDER BY cu.LastName

which returns the following results

FirstName	LastName	ResidencyState
Magic	Johnson	MI
Ron	Mason	MI
Steve	Smith	MI

As you can see, we can use the SELECT commmand to retrieve data from a table in a database and customize the way that data comes back.  We've just scratched the surface of this command.  In the next article, we'll look at more options of the SELECT command