Querying Relational Database

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.

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.

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).

Get A Free USA Scholarship, Visa & Accommodation

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).

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.
Write a simple SELECT query

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

Click on the Createtab near the top of the application window.
Next, click on the Query Designbutton (found on the left side of the Create Ribbon in the group of commands labeled as Queries).
In the Show Tabledialog, double-click on the STATS table to add it to the query and click Close, (when you do this in Access 2010 the ribbon switches to the Design ribbon).
Double-click on PLAYER_ID in the list of fields in the STATS table to add that field to the design grid below.
Repeat this step to add the YEAR and RBI fields.
At any time, you can view the SQL that’s created by your GUI settings by accessing the Viewdrop-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.
Restrict the returned records to a desired subset
From the same View drop-down list, select Design Viewto return to the query design GUI.
In the design grid, set the Criteriavalue for the RBI field to >99.
Test the query by clicking on the red exclamation point, (it should return 103 records).
Sort the returned records
Return to Design View.
In the design grid click in the Sortcell under the RBI column and select Descending from the drop-down list. This will sort the records from highest RBI total to lowest.
Test the query.
Add additional criteria to the selection
Return to Design Viewand set the Criteria value for the YEAR field to >1989. This will limit the results to seasons of over 100 RBI since 1990.
Test the query, (it should return 53 records).
Return to Design Viewand modify the Criteria value for the YEAR field to >1989 And<2000, which will further limit the results to just the 1990s.
Test the query, (it should return 34 records).
Return to Design Viewand 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 seasonssince 1989 OR prior to 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.
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.