WI08
CSE200 QUIZ#4
Name _____________________________________________ Seat#
__________
Lab Day/Time:
_______
Thur 3:30pm _______
Fri 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.
·
If you use a Group By and you don’t need it, points
will be deducted.
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.
MILITARY DATABASE TABLES – These tables represent the military
men and women currently deployed overseas.
The level in the “personnel” table represents the rank of each
person. The E stands for enlisted and
the O stands for officer. The rest of
the data should be self explanatory. If
you have any questions, ask!
SCORE __________/50
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.
Primary Key Foreign Key(s) Primary Key Foreign Key(s)
PERSONNEL
table
SERVICE table
2. (2 Pts) What is the type of the “idtag” field in the “personnel” table if I tell you that it is a numeric value, but is not represented as the integer type?
_______________________________
3. (5 pts) Using the query design view below, construct a query to list id number, last name and first name for all the personnel currently serving in iraq.
Table _____________________________
|
Field |
|
|
|
|
|
Table |
|
|
|
|
|
Total |
|
|
|
|
|
Sort |
|
|
|
|
|
Show |
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
Write Expressions here, if applicable:
4. (6 pts) Using the query design view below, construct a query to list the last name, branch letter (not branchname) and level of those personnel deployed after 6/05/05 but on or before 9/15/05.
Table _____________________________
|
Field |
|
|
|
|
|
Table |
|
|
|
|
|
Total |
|
|
|
|
|
Sort |
|
|
|
|
|
Show |
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
Write Expressions here, if applicable:
5. (6 pts) Using the query design view below, construct a query to list the id number, level, branch, and location of all personnel sorted alphabetically by branch, then location, then level.
Table _____________________________
|
Field |
|
|
|
|
|
Table |
|
|
|
|
|
Total |
|
|
|
|
|
Sort |
|
|
|
|
|
Show |
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
Write Expressions here, if applicable:
6. (7, 4 pts) Using the query design view below, construct a query to list the last and first name of all the navy and marine officers. Be sure to show the resulting dynaset in the box given below.
Table _____________________________
|
Field |
|
|
|
|
|
Table |
|
|
|
|
|
Total |
|
|
|
|
|
Sort |
|
|
|
|
|
Show |
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
Write Expressions here, if applicable:
Resulting Dynaset:
7. (6,2 pts) Using the query design view below, construct a query to summarize by branch the number of personnel deployed.
***** Can the level field be added to this query? YES _______ NO _________
Table _____________________________
|
Field |
|
|
|
|
|
Table |
|
|
|
|
|
Total |
|
|
|
|
|
Sort |
|
|
|
|
|
Show |
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
Write Expressions here, if applicable:
8. (6 pts) Using the query design view below and the query result given above (call it Q7), construct a query to list the total uniform cost for each branch if uniforms cost $100/person. Be sure to list the branch and the total cost of uniforms for that branch.
Table _____________________________
|
Field |
|
|
|
|
|
Table |
|
|
|
|
|
Total |
|
|
|
|
|
Sort |
|
|
|
|
|
Show |
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
Write Expressions here, if applicable: