About SQL

SQL (Structured Query Language) is a language that lets you communicate with databases. For example, you can use SQL to retrieve data from a database, add data to a database, delete or update records in a database, change columns in multiple rows, add columns to tables, and add and delete tables.

Unlike other computer languages, SQL is made up of a small number of language elements that let you interact efficiently with a database. Some of the more frequently used elements include the following SQL commands:

Command

Use

SELECT

Retrieve (query) information in a database.

INSERT

Add records to a database.

UPDATE

Update information in a database.

DELETE

Delete information in a database.

Understanding basic SQL SELECT statements

One of the most widely used SQL statements is the SELECT statement. The SQL SELECT statement retrieves columns of data from a database. The tabular result is stored in a result table (called the record set).

You use the following SELECT statement to retrieve information from a table:

SELECT column_name(s) FROM table_name

Consider a table named Clients that contains the following rows:

LastName

FirstName

Address

City

Brown

Marie

12 State St

Boston

Adams

Russell

521 Beacon St

Boston

Carter

Joan

1 Broadway

New York

To select the columns named LastName and FirstName, use the following SELECT statement:

SELECT LastName, FirstName FROM Clients

The result of this SQL statement contains the following data:

LastName

FirstName

Brown

Marie

Adams

Russell

Carter

Joan

Using the SQL WHERE clause to limit the rows returned

To conditionally select data from a table, you can add a WHERE clause to the SELECT statement, which results in the following syntax:

SELECT column_name FROM table_name WHERE column condition value

With the WHERE clause, you can use any of the following operators:

Operator

Description

=

Equal

<>

Not equal

>

Greater than

<

Less than

>=

Greater than or equal

<=

Less than or equal

BETWEEN

Between an inclusive range

AND

Joins one or more conditions

OR

Joins one or more conditions

LIKE

Specifies a search for a pattern in a column. You can use a percent sign (%) to define wildcards (missing letters in the pattern) before and after the pattern.

For example, to select the columns named LastName and FirstName for Clients whose City is Boston, use the following SELECT statement:

SELECT LastName, FirstName FROM Clients Where City = 'Boston'

The result of the preceding SQL statement contains the following data:

LastName

FirstName

Brown

Marie

Adams

Russell

You can compose a WHERE clause with one or more conditions; these are called subclauses. You join subclauses using the operators AND and OR. The AND operator displays a row if all conditions that are listed are True. The OR operator displays a row if any of the conditions listed are True. The following statement shows an example of a WHERE clause with multiple subclauses:

SELECT LastName, FirstName FROM Clients Where City = 'Boston' AND FirstName = 'Marie'

The result of the preceding SQL statement contains the following data:

LastName

FirstName

Brown

Marie

Note: The preceding SQL SELECT examples use single-quotation marks around the value. SQL uses single-quotation marks around text values. Most database systems also accept double-quotation marks. Do not enclose numeric values in quotation marks.

Sorting the results

You use the ORDER BY clause to sort the result rows. The following SQL statement returns an alphabetic list of people sorted by last name, and then first name, from the Clients table:

SELECT * FROM Clients Order By LastName, FirstName

The default is to return the results in ascending order (top to bottom). If you include the DESC keyword in the ORDER BY clause, the rows are returned in descending order (bottom to top).

The following statement returns a reverse alphabetic list of the Clients table:

SELECT * FROM Clients Order By LastName, FirstName DESC

Note: The SQL SELECT statement is quite powerful. There are several other options for retrieving data from a SQL database using the SELECT statement, which are not described in this manual. For more information, consult a SQL reference.