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.

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. NOTE: Do NOT put the FK field on the line as sometimes shown on the answer keys in Carmen.

FRUIT table Primary Key Foreign Key(s)

DISTRIBUTOR table Primary Key Foreign Key(s)

2. (4 pts) Name 2 of the 3 rules necessary to be able to draw a relationship between two tables.

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.

4. (3 pts) Is it okay (yes or no) to add the fruit id in ascending order to the above query? Explain your answer.

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.

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.

NOTE: if you worked this problem online from my announcement page, do NOT include the distributor number.

7. (5 pts) List the dynaset created from the query above based solely on the data shown.

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

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.

Expression can be given here:

10. (5 pts) Where in a query do you use the NZ function? Explain when and why you use an NZ function.