SPRING 2008 KReeves CSE200 QUIZ#4
Name
_________________________________________ Seat#
__________
Lab Day/Time _______ Thursday 3:30pm _______ Friday 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. Remember to put
quotes on the like and text values and pound signs around dates.
·
Be sure to put calculated expression field
names in the Field location with no table name, then repeat the field name
along with the formula below the query design view where designated.
·
Aggregate functions: Group By, Sum, Avg, Min,
Max, Count, Where, and Expression.
·
REMEMBER that all the data is not shown in
the tables; be sure your queries will work with additional/deleted records.
The following Access Tables represents staff utilization of a mid-size ad agency. Each record in the Job1 and Job2 tables represents a task performed on that job by a specific employee. Each employee is uniquely identified by an employee number. Answer the questions in the below problems based on the tables given below. Remember that your answers should still work if data is added or deleted from the tables.
1. (8 pts) Draw the relationship diagram between these 3 tables the same as you would see in Access (as explained in class). Specify each primary and foreign key for each table and draw the lines between them where applicable.
TABLE: Job1 Primary Key: Foreign Key: TABLE: Employees Primary Key: Foreign Key: TABLE: Job2 Primary Key: Foreign Key:
2. (2 pts) Why can’t Emp# be the primary key on the Job1 table?
REPEAT OF TABLES FOR YOUR CONVENIENCE:
3. (6 pts) Design a query
to list the taskid# and employee id for each employee who worked on a Job2 task
which lasted at least 15 minutes on either July 1, 2006 or July 10, 2006.
Table __________________________________
|
Field |
|
|
|
|
|
Table |
|
|
|
|
|
Total |
|
|
|
|
|
Sort |
|
|
|
|
|
|
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
Put expressions here, if
necessary:
4. (6 pts) Design a query
that lists the employee’s id and date and minutes spent for only those tasks Job
1 that were between 15 and 20 minutes long (inclusive)
Table __________________________________
|
Field |
|
|
|
|
|
Table |
|
|
|
|
|
Total |
|
|
|
|
|
Sort |
|
|
|
|
|
|
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
Put expressions here, if
necessary:
5. (6 pts) Using the Access
Query design view below, design a query to list the Employees whose names have
the letters “john” in them somewhere i.e. as the first name of John or as part
of the last name (for example, Johnson).
List each employee’s name, Employee#, and Job Rate. Sort your list by Job Rate (most expensive
first) and then alphabetically by name.
Table _________________________________
|
Field |
|
|
|
|
|
Table |
|
|
|
|
|
Total |
|
|
|
|
|
Sort |
|
|
|
|
|
|
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
Put expressions here, if
necessary:
6. (10 pts) Design a query summarizing the Job1 tasks by emp#. The list should include Emp#, the number of tasks
they worked and the total number of minutes that they worked. Below the query design view, fill in the resulting
dynaset for this query using the given Job1 table data.
Tables _________________________________
|
Field |
|
|
|
|
|
Table |
|
|
|
|
|
Total |
|
|
|
|
|
Sort |
|
|
|
|
|
|
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
Put expressions here, if
necessary:
|
Query6 |
|
||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
7. (6 pts) An overhead fee
of $5 per task is assessed for each task regardless of duration. Using the result from the previous query
(which you named query6), write a query in the design view below which will
return the Emp#, and the total amount of overhead fees for each employee for
Job1. If an employee has not worked on
this job, their name should not appear on this list.
Tables __________________________________
|
Field |
|
|
|
|
|
Table |
|
|
|
|
|
Total |
|
|
|
|
|
Sort |
|
|
|
|
|
|
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
|
OR |
|
|
|
|
Put expressions here, if necessary:
8. (6 pts) Currently
clients are billed the cost of the tasks completed (hourly rate times hours)
plus the overhead fee assessment. An
alternative method of billing overhead has been suggested by management as
follows: Hourly overhead costs will be
billed at 25% of the hourly wage plus $3 per hour. So an Ad Exec for example with an hourly wage
of $125/hour would have an hourly overhead charge of $125*.25 +3. Write a query
to calculate the hourly overhead charge for each of the different employees
listed on the employees table. If
the Job Rate field is blank the overhead charge should simply be $3.
Table __________________________________
|
Field |
|
|
|
|
|
Table |
|
|
|
|
|
Total |
|
|
|
|
|
Sort |
|
|
|
|
|
|
|
|
|
|
|
Criteria |
|
|
|
|
|
OR |
|
|
|
|
Put expressions here, if
necessary: