SELECT Query Basics

SELECT Query Basics

Why MS-Access?

A number of RDBMS vendors provide a GUI to aid their users in developing queries. These can be particularly helpful to novice users, as it enables them to learn the overarching concepts involved in query development without getting bogged down in syntax details. For this reason, we will start the course with Microsoft Access, which provides perhaps the most user-friendly interface.

A. Download an Access database and review its tables

Throughout this lesson, we'll use a database of baseball statistics to help demonstrate the basics of SELECT queries.

1. Click to download the baseball database.
2. Open the database in MS-Access.
   
   One part of the Access interface that you'll use frequently is the "Navigation Pane," which is situated on the left side of the application window. The top of the Navigation Pane is just beneath the "Ribbon" (the strip of controls that runs horizontally along the top of the window).
   
   The Navigation Pane provides access to the objects stored in the database, such as tables, queries, forms, and reports. When you first open the baseball_stats.accdb database, the Navigation Pane should appear with the word Tables at the top, indicating that it is listing the tables stored in the database (PLAYERS, STATS, and TEAMS).
3. Double-click on a table's name in the Navigation Pane to open it. Open all three tables and review the content. Note that the STATS table contains an ID for each player rather than his name. The names associated with the IDs are stored in the PLAYERS table.

B. Write a simple SELECT query

With our first query, we'll retrieve data from selected fields in the STATS table.

1. Click on the Create tab near the top of the application window.
2. Next, click on the Query Design button (found on the left side of the Create Ribbon in the group of commands labeled as Queries).
   When you do this in Access 2010 and higher, the ribbon switches to the Design ribbon.
3. In the Show Table dialog, double-click on the STATS table to add it to the query and click Close.
4. Double-click on PLAYER_ID in the list of fields in the STATS table to add that field to the design grid below.
5. Repeat this step to add the YEAR and RBI fields.
   
6. At any time, you can view the SQL that's created by your GUI settings by accessing the View drop-down list on the far-left side of Design Ribbon (it is also available when you have the Home tab selected, as shown below).
   As you go through the next steps, look at the SQL that corresponds to queries you are building.
   

C. Restrict the returned records to a desired subset

1. From the same View drop-down list, select Design View to return to the query design GUI.
2. In the design grid, set the Criteria value for the RBI field to >99.
   
3. Test the query by clicking on the red exclamation point on the top left next to the View dropdown (it should return 103 records).

D. Sort the returned records

1. Return to Design View by selecting it from the View dropdown.
2. In the design grid, click in the Sort cell under the RBI column and select Descending from the drop-down list. This will sort the records from highest RBI total to lowest.
3. Test the query.

E. Add additional criteria to the selection

1. Return to Design View and set the Criteria value for the YEAR field to >1989. This will limit the results to seasons of over 100 RBI since 1990.
2. Test the query (it should return 53 records).
3. Return to Design View and modify the Criteria value for the YEAR field to >1989 And <2000, which will further limit the results to just the 1990s.
4. Test the query (it should return 34 records).
5. Return to Design View and change the Criteria value for the YEAR field back to >1989, beneath that cell (in the or: cell) add <1960.
   
   As you should be able to guess, I'm asking you to write a query that identifies 100-RBI seasons since 1989 OR prior to 1960. However, the query as written at this point doesn't quite yield that result; look at the WHERE line in the SQL view. Instead, it would return 100-RBI seasons since 1989 and all seasons prior to 1960 (not just the 100-RBI ones). To produce the desired result, you need to repeat the >99 criterion in the RBI field's or: cell. Check the SQL view to see the change.
   
6. Test the query (it should return 74 records).

You've probably recognized by now that the output from these queries is not particularly human friendly. In the next part of the lesson, we'll see how to use a join between the two tables to add the names of the players to the query output.