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).
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.
PAY-JOBS-POSITION-JOBREQ
PAY-JOBS-JOBREQ-PEOPLE
JOBS-POSITION-JOBREQ-PEOPLE
3. (3 pts) What type of join would you use to find data integrity errors?
Outer join relative to the FK table
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.
INNER JOIN Relative To: is blank Can COUNT from any field from either table; just make
sure table name matches the table the field comes from Do not grade show line Okay if add pos# field from the position table with a
Group By option Order of fields does not matter
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.
|
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 (4th 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 4th
column in the resulting dynaset is the max weekly pay, in this case $1200,
minus the expenses ($150) and 1200-150 is 1050.
OUTER join RELATIVE TO: people table Order of fiels does not matter MUST have “show” options checked for each field Watch that the second field name matches the one given
in Q5 Watch that the pnum comes from the PEOPLE table
OUTER join RELATIVE TO: people table Order of fields does not matter MUST have “show” options checked for each field Watch that the pnum comes from the PEOPLE table
INNER join RELATIVE TO: blank Order of fields DOES matter; must match given resulting
dynaset including name of calculated field. MUST have “show” options checked for each field Pnum can come from any of the 3 tables listed. Okay if put query/table name on fields in calculated
expression Both fields MUST have a separate NZ function on it. the
second argument defaults to zero, but student can put it in the function if
they like. Lastname and firstname fields can be put on a previous
query with a Group By option, then the PEOPLE table doesn’t have to be
listed here