Data retrieval - Introduction to the SELECT statement.

SELECT is one of the Data Manipulation Language (DML) commands, used for retrieving specific data from one or more tables, reporting calculated results based on the data retrieved, or displaying the results of “what if?” type calculations.

Example:

1. Click Queries on the Objects pane and run the query Current Product List (double-click or click on Open):

2. Change to SQL view to see the SELECT statement:

Notice the following in the statement above:

         The table name has been aliased (Product List).
         Columns are named as alias_name.column_name (strictly speaking, not necessary for a single-table query).

         Access uses square brackets when names contain punctuation such as spaces; other databases use quotes.   
         Access inserts many more parentheses than actually needed.

3. Close the query.

Exercise: Create a list of all product categories

1. Type the following statement in the SQL window:

SELECT *

FROM Categories

2. View the results without saving by clicking the View button (switches to Datasheet view).

3. Change back to SQL view and run the statement by clicking the Run ( ! ) button.

4. Save the query (e.g., as AllCategories) and close it.

Exercise: Create a phone list of customer contacts (just company and contact names with phone and FAX numbers) sorted by company name.

1. Type the following statement in the SQL window:

SELECT CompanyName, ContactName, Phone, Fax

FROM Customers

ORDER BY CompanyName

2. View the results; then run and save the query (e.g., as AllCustomers). You should see 91 customers.

3. Modify the list to include only customers from the United Kingdom and at the same time, use the aliases Company and Customer for the first two column headers:

SELECT CompanyName AS Company, ContactName AS Customer, Phone, Fax

FROM Customers

WHERE Country = “UK”

ORDER BY CompanyName

4. View the results; then run and save the query (e.g., as UKCustomers).  There should be 7 customers

5. Close the query.