AU07 CSE200 QUIZ4
Name
_________________________________________ Seat# __________
Lab Day/Time _______ Wednesday 3:30pm _______ Thursday 1:30pm
Instructions:
·
Put away all books, papers, and calculators. Turn off all beepers and cell phones.
·
Answers must be legible or they will be marked
incorrect.
·
Be sure that all answers are SYNTACTICALLY correct
i.e. as you would see them prior to running the query… see the below reminders
for some (but possibly not all) hints.
·
REMEMBER that all the data is not shown. Be sure
your queries will work with additional records.
ACCESS REMINDERS
·
No quotes around yes/no field values.
·
Use # signs around dates.
·
Be sure to use the word Like and quotes on
criteria that have wildcard characters.
·
No table name is used when creating an
expression.
·
Use square brackets around the table/query
name and square brackets around the field name with an exclamation point in
between when using table/query data in an expression.
·
Aggregate functions: Group By, Min, Max, Avg,
Count, Sum, Where, Expression.
·
The count aggregate function determines the
number of records in a field so any field from that same table can be used… be
sure to choose a field!
·
When the total line is used (i.e. aggregate
functions are used), all the fields listed must have something in the total
line.
·
When using a total line, sometimes the
resulting dynaset field names will change (min, max, sum, count, avg). For
example, when counting the number of states, the output field will likely be:
CountofState.
FRUIT
DISTRIBUTOR
The tables given represent the database system for the Smith Family Fruit Company. The FRUIT table is a list of fruits that the Smith Company sells to supermarkets and other types of grocery stores. The vendors on this table are the companies that provide the type and quantity of fruit to the Smith Company. The date and quantity shipped is the date and amount of the fruit shipped by the vendor to the Smith Company. The DISTRIBUTOR table lists the companies that ship the fruits from the Smith company to the supermarkets and grocery stores.
DO NOT PUT SCORE ON FRONT PAGE!!!
1. (6 pts) Database Relationships. Set up the relationships of this database. Using the boxes below, fill in the primary key (if any) and foreign key (s) (if any) of each table and draw relationship lines between tables.
2. (4 pts) Name 2 of the 3
rules necessary to be able to draw a relationship between two tables.
- one must be
a primary key 2. must be the
same type 3. must have the same meaning
3. (7 pts) Using the query
design view below, construct a query to list the fruitid first, then the fruit
name, quantity shipped and the state in
your choice of order. Be sure to list
the states in alphabetical order then the quantity shipped from highest to
lowest.
Table(s)
Used ________fruit_______________________________
|
Field |
fruitid
|
fruit |
state |
qty shipped |
|
Table |
fruit |
fruit |
fruit |
fruit |
|
Total |
|
|
|
|
|
|
x |
x |
x |
x |
|
Sort |
|
|
Ascending/ASC |
Descending/DESC |
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
4. (3 pts) Is it okay (yes or
no) to add the fruit id in ascending order
to the above query? Explain your answer.
NO, because you
can’t sort by both fruit id and state at the same time.
Could also say YES,
but the sort would not work; that is, the result of the query would be the same
order as the fruit table.
5. (5 pts) Using the query
design view below, construct a query to list, in this order, the fruit id and
fruit name only that contain all the fruits shipped starting 1/01/03 and are
either from distributor #1 or the state of California.
Table(s)
Used ________fruit_______________________________
|
Field |
Fruited |
Fruit |
Date shipped |
Distributor# |
State |
|
Table |
fruit |
fruit |
fruit |
fruit |
fruit |
|
Total |
|
|
|
|
|
|
|
X |
X |
|
|
|
|
Sort |
|
|
|
|
|
|
Criteria |
|
|
>=#1/01/03# |
=1 |
|
|
OR |
|
|
>=#1/01/03# |
|
=”CA” |
|
OR |
|
|
|
|
|
Notes: date
shipped can also be >#12/31/02#; the
= in front of the two other criteria are optional
6. (5 pts) Using the query
design view below, construct a query to list the fruit id and fruit name of all
the fruits that contain apple or grape.
Table(s)
Used ______fruit __________________
|
Field |
Fruitid |
Fruit |
|
|
|
Table |
Fruit |
Fruit |
|
|
|
Total |
|
|
|
|
|
|
X |
X |
|
|
|
Sort |
|
|
|
|
|
Criteria |
|
Like “*apple*” |
|
|
|
OR |
|
Like “*grape*” |
|
|
|
OR |
|
|
|
|
NOTES: criteria can be all
in once box separated by an OR
7. (5 pts) List the dynaset
created from the query above based solely on the data shown.
|
Fruit id |
Fruit |
|
|
|
1 |
Apples |
|
|
|
2 |
Grapes |
|
|
|
6 |
Pineapples |
|
|
|
9 |
Grapefruits |
|
|
Order does not
matter… i.e. the order of the records
8. (5 pts)
Use the query design view below to construct a query that will generate
the given dynaset.
|
NameOfQuery |
|
|
state |
CountOfstate |
|
CA |
2 |
|
FL |
4 |
|
GA |
2 |
|
TX |
3 |
Table(s)
Used ______fruit______________________________________
|
Field |
State |
State |
|
|
Table |
Fruit |
Fruit |
|
|
Total |
GB/Group By |
Count |
|
|
|
X |
X |
|
|
Sort |
|
|
|
|
Criteria |
|
|
|
|
OR |
|
|
|
|
OR |
|
|
|
Ok if sorted ascending by state
9. (5 pts) A field has been added to the fruit table
called COST which is the cost per fruit item. Using the query design view
below, construct a query to summarize by state the total cost of the shipment
sent to each state.
|
Field |
State |
TotCost: |
|
|
|
Table |
fruit |
|
|
|
|
Total |
Group By |
Sum |
|
|
|
|
X |
X |
|
|
|
Sort |
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
TotCost: [qty shipped]*[cost]
Okay if put the table [fruit]! in front of each field
10. (5 pts) Where in a query do you use the NZ function?
Explain when and why you use an NZ function.
Where: in an
expression
When: if a field
in the expression could possibly be null
Why: to change a
null value to another value to enable the expression to be evaluated to
something other than null.
SCORE _____________ / 50