SP10 CSE200 QUIZ#5

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.

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. In addition (i.e. added recently) is the position that the current company is hiring for; a description of which can be found on the POSITION 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.

The EXPENSES table shows the weekly expenses associated with each person. The type of expense and the amount is given on each record.

The POSITION table lists all the different types of possible job positions that could be associated with a job.

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).

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

2. (4 pts) List all of the valid 4-table joins that you can use when creating a single query grid. In other words, what set of 4 tables, if any, can be joined in a single query grid; be sure to list all the possible 4-table combinations.

3. (3 pts) What type of join would you use to find data integrity errors?

4. (7 pts) Create an Access query to list the titles of all the positions for which a company hires for and the number of jobs that hire for that title.

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

Additional room for Expressions, if necessary

5. (8 pts) Write an Access query to list all of the job requests, the person requesting the job and the amount they would earn per 40-hour work week. REMINDER: the pay in the PAY table is dollar per hour.

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

Additional room for Expressions, if necessary

6. (20 pts – 6, 6, 8) Create a set of Access queries using the above query (Q5) as a starting point, to create the following resulting dynaset where the leftover amount (4^th field) is the maximum possible weekly pay for that person minus the total expenses for that person:

Helpful Explanation: For person #1, they have 3 job requests. The first job request with job#1has a level of A which has an hourly pay of $25; this weekly pay would be $25*40 which is $1000 per week (wow!). The second job request is job#4 which has a level of B which pays $30 per hour; this weekly pay would be $30*40 which is $1200 per week (bazinga!). Last (which of course will be your favorite job opportunity because that’s how life works) is the third job request #6 which has a level of F and an hourly pay of $20 which has a weekly pay of $800 per week. However, the problem says that you will choose the job with the maximum possible weekly pay, so you choose the job that pays $1200 per week. Totaling the expenses per week gives a value of $150. The 4^th column in the resulting dynaset is the max weekly pay, in this case $1200, minus the expenses ($150) and 1200-150 is 1050. :o)

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

Additional room for Expressions, if necessary

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

Additional room for Expressions, if necessary

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

Additional room for Expressions, if necessary

SCORE ________/50