IST 220 Introduction to Databases 2/2/2005
Queries
Select queries: retrieve info w/o changing the data in the underlying table(s)
Action queries: manipulate (or change) data in the targeted table w/o returning
them
Select Queries
Embodying user questions, and
Generating dynasets that include the answers
^
L the set of records that satisfy the criteria specified in the query
A dynaset is a dynamic subset of the underlying table(s)
Let’s use the Super Bowl database as an example, which has one single table.
Q1: give me all data in the table.
- using wizard: only one table, Super Bowl can be used as data source
- name it Query1 and simply display the result: the whole table w/ 35 rows
Q2: give me all records for the years when an NFC team won
- using wizard: a table and a query (Query1) can be used as data source
- name it Query2, choose to display it in the design view
- add =”NFC” in the Criteria row associated with the field Winner
- 20 rows left in the resulting dynaset
SELECT [Super Bowl].*
FROM [Super Bowl]
WHERE [Super Bowl].Winner="NFC";
Q3: in answering to Q2, I need the year and team-names
- using wizard: select Query2 as data source and choose the three fields
- name it Query3: only 3 columns should be included in the datasheet view
Logical operators: AND and OR
o Assume that you are only interested in recent games, e.g. 1990 and later. Add a new
condition >=1990 in the Criteria row associated with the field Year. Now only 9 records
satisfy the composite condition:
[Winner]=”NFC” AND [Year]>=1990.
o Now assume you are also interested in teams from Atlanta, no matter it was the winner or
not. Then you will need to use the OR operation, by adding a condition in the Or row
associated with the field NFCTeam: =”Atlanta”. You will find 10 records that satisfy the
compound condition:
[Winner]=”NFC” AND [Year]>=1990 OR [NFCTeam]=”Atlanta”.
Q4: I don’t want to know the details—just tell me the NFC teams that had won
and how many times each of they won
- use Query3 as data source, and select two fields: NFCTeam and Year
- in the design grid, select the Year column and click on the toolbar
- a Total row is added
o keep Group by for the NFCTeam field and select Count for Year
- save as Query4
1
IST 220 Introduction to Databases 2/2/2005
Here is the SQL select statement that was generated by Access:
SELECT [Super Bowl].NFCTeam, Count([Super Bowl].Year)
FROM [Super Bowl]
WHERE [Super Bowl].Winner="NFC"
GROUP BY [Super Bowl].NFCTeam;
Q5: tell me which NFC is the biggest winner and how many times it had won
- first create Query5a to generate the max wins
o it’s easier to use Query4 as data source: select CountOfYear only
o click on the toolbar and select Max in the Total row
- then create a new query (Query5) to display both team name and # wins
o use Query4 and select both fields
o add a condition =[Query5a].[MaxOfCountOfYear] in the
Criteria row for CountOfYear
the result should look like this:
Dallas 5
Let’s have a look at the SQL statement that was generated by Access:
SELECT Query1.NFCTeam, Query1.CountOfYear
FROM Query1, Query2
WHERE (((Query1.CountOfYear)=[Query2].[MaxOfCountOfYear]));
Using Aliases
Tired of seeing column titles like CountOfYear or MaxOfCountOfYear? Actually, you can
name them any ways as you like. Here is how:
o append the title you want to use in the Field row for the target field or expression
o use : to separate the title and the field-name/expression
For instance:
- append # of Wins: in front of Year for Q4;
- and Max # of Wins: in front of # of Wins (or any alias that you actually used) for Q5
Q6: NFC vs. AFC: total numbers of wins for both displayed in the same line, e.g.
NFC AFC
20 15
Let’s tackle on this question in two steps. First let see how we can convert the
value “NFC” and “AFC”in the Winner field to 1 and 0, respectively, which can
then be added toward NFC total. The IIF function that is available with Access
can be used to handle this: IIf([winner]="NFC",1,0). So Query6 will have
two calculated fields, one each for NFC and AFC, respectively. Read the Using
Aliases sidebar to see how you can display your results under the titles NFC and
AFC. Now you should see data like this:
NFC AFC
1 0
1 0
0 1
... ...
As a second step, apply the total function Sum( ) to both columns, and then you
will see the result as illustrated at the beginning of this section.
2
IST 220 Introduction to Databases 2/2/2005
Now, let’s look at the SQL statement generated by Access: keywords/functions
are in boldface, and the IIF function only available with Access is underlined.
SELECT Sum(IIf([winner]="NFC",1,0)) AS NFC,
Sum(IIf([winner]="AFC",1,0)) AS AFC
FROM [Super Bowl];
Crosstab Queries
Access provides a convenient mechanism named crosstab query that can display summarized
information as we did above. To use a crosstab query, a user needs to select a column heading,
one or more row heading, and a value option that contains the values to be summarized. You
may choose one attribute to fulfill all three requirements. Here is an example:
o create a query using design view
o choose query type as crosstab query
o choose Winner in the first three columns in the design grid, as column and row heading and
value, respectively
o in the third column that corresponds to the value option, select Count in the Total cell
The result looks similar to what we got from Query6, but not as clear.
Winner AFC NFC
AFC 15
NFC 20
If we change row heading to Year, the result looks similar to that we got from the first half of Q6:
Year AFC NFC
1967 1
1968 1
1969 1
... ... ...
Changing the row heading to NFCTeam will generate a result as follows, which summarizes NFC teams that
had even participated in Super Bowl and the total wins/loses for each of them.
NFCTeam AFC NFC
Atlanta 1
Baltimore 1
Chicago 1
Dallas 3 5
Giants 2
Green Bay 1 3
Los Angeles 1
Minnesota 4
New York 1
Philadelphia 1
San Francisco 5
Tennessee 1
Washington 2 3
The crosstab queries are easy to create: you don’t have to write your own expressions that usually require
less commonly used functions. But they are not so flexible so you may not be able to summarize and/or
display data in exactly the same ways as you want. (For instance, you might want to use # Loses and #
Wins to replace AFC and NFC in the result set above.)
Action Queries
Remember the term CRUD we learned in Lecture 1. What does it stand for?
Yes, it stands for: Create (or insert), Retrieve (or select), Update, and Delete.
Select queries take care of the Retrieve operations. (The underlying SQL select
3
IST 220 Introduction to Databases 2/2/2005
statements are the hidden heroes.) Action queries will take care of the rest.
Access also provides a Make-Table Query type that allows users to create a
table using records from an existing table. See Hands-on Exercise 4 in Chapter
3 for detailed instructions on how to use these queries.
You are encouraged to use the following data from the official Super Bowl
website to continue with our exercises as demonstrated above. Here is a list of
operations that you can try:
Create a make-table query that create a temporary table with any one
record from the Super Bowl table: this will give you a dummy table to add
the most recent three records before appending them to the main table.
Create a delete query to remove the record that you copied from the main
table from the temporary table.
Create a append query to copy the new records in your temporary table to
the main table.
Create an update table to add an appropriate value to the Winner field of
the newly added records.
2004 New England 32, Carolina 29
2003 Tampa Bay 48, Oakland 21
2002 New England 20, St. Louis 17
You can update your table with the result of Super Bowl XXXIX this weekend!
4