SP10 CSE200 QUIZ#4

Name _____________________________  Seat# ________   Lab=> Friday ______ 1:30pm______ 3: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.

· Aggregate functions: Group By, Sum, Avg, Min, Max, Count, Where, Expression.

· REMEMBER that all the data is not shown. Be sure your queries will work with additional records.

· Leave the “join on” and “relative to” options on the query design view blank.

The tables given represent the database system for the Internship Job-Access Company. The following tables are a collection of information about the people who are interested in obtaining a summer internship:

The PEOPLE table is a list of people (well a portion of them anyway), who are interested in getting a summer internship.

The JOBS table defines the details about each of the different job opportunities available to each person.  Included with each company name is the city and state location; the start and end date of the internship; as well as the pay level which is specified in detail on the next table.

The PAY table is the list of pay levels, each of which includes the hourly pay and whether or not benefits are included.

The JOBREQ table includes the job requests per person where each person may have multiple job requests.

REMEMBER THAT ALL OF QUERIES ARE SINGLE TABLE QUERIES ONLY

Name ___________________________   Seat# ________   Lab=> Friday ______ 1:30pm______ 3:30pm

1. (8 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) for each table AND draw relationship lines between tables (exactly the same as you would see in Access).

	JOBREQ  table Primary Key Foreign Key(s)		PAY table Primary Key Foreign Key(s)
PEOPLE  table Primary Key Foreign Key(s)		JOBS table Primary Key Foreign Key(s)

2. (3 pts)  How do you know that the pnum field on the PEOPLE table is numeric and not text?

3. (3 pts) What is the benefit for having the pnum fields (on PEOPLE and JOBREQ tables) being numeric instead of text?

4. (10 pts) Create an Access query (in the design view given below) to list the company name for all the jobs in Ohio where a person can work at least from June 15^th to September 1^st. Sort the resulting list first alphabetically by city then by start date where the job that starts the latest is listed first.

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

5. (8 pts) Create an Access query (in the design view given below) to list the number associated with each person who has the first name initial and last name initial of KB or KJ.

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

6. (6 pts) Create an Access query (in the design view given below) to list the pay levels that either have benefits or have an hourly pay more than $20.

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

Additional room for Expressions, if necessary

7. (12 pts) Create an Access query (in the design view given below) summarizing by person, the total number of internship jobs they have currently applied for and the total cost of their applications where each application cost is $10. Once you have written the query, determine the resulting dynaset in the space given below (extra space has been provided).

TABLE(S):
JOIN TYPE:	RELATIVE TO:
Field
Table
Total
Sort
Show
Criteria
OR
OR

 Additional room for Expressions, if necessary

                                    Query7

SCORE ________/50