SELECT: Syntax for a single-table SELECT statement

SELECT column list

FROM tablename

WHERE criteria

ORDER BY column list

Note:  Although the language is free-form, the clauses that make up the statement must occur in the order shown.  SELECT and FROM are required.  The remaining clauses are optional.

Operators and predicates for the WHERE clause with examples:

Comparison operators: = > < <= >= <> (or !=)

WHERE Country <> “UK”

BETWEEN ... AND ...

WHERE Price BETWEEN 10 AND 20

WHERE CompanyName BETWEEN A AND F

LIKE (with wildcard: * ? in Access; and % _ in most other databases)

WHERE ContactTitle LIKE “Sales%”

            IN (list)

WHERE State IN (“OR”, “WA”, “CA”)

AND, NOT, OR with any of the above

                         WHERE Country = "USA" AND City = "New York"

IS NULL, IS NOT NULL

WHERE PostalCode IS NOT NULL
                         WHERE Discount IS NULL

Exercise: Create alphabetically sorted product lists showing product name (ProductName), supplier (SupplierID), and unit price (UnitPrice) for the following:

- All products (result: 77 products)

- Products that cost no more than $10 (14)

- Products in the $10 to $20 price range (29)

- Chocolates (2 or 3)

Exercise: Modify the AllCustomers phone list to make separate lists for customers with and without an assigned region (two separate queries). Use is null or is not null to make the distinction.