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).
2. (3 pts) How do you know that the pnum field on the PEOPLE table is numeric and not text?
No leading zeroes
3. (3 pts) What is the benefit for having the pnum fields (on PEOPLE and JOBREQ tables) being numeric instead of text?
Ability to use autonumber for PK field
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 15th to September 1st. Sort the resulting list first alphabetically by city then by start date where the job that starts the latest is listed first.
Do not grade
the show line Access is
case insensitive JCITY field
must be to the left of the START DATE field
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.
Do not grade
the show line Order of
fields doesn’t matter Access is
case insensitive
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.
Do not grade
the show line Order of
fields doesn’t matter Access is
case insensitive No quotes
allowed around either criteria
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).
Grade the show
line Order of
fields DOES matter Access is
case insensitive Blank table
for expression Can use
jobnum for COUNT but then calculation needs to say COUNTOFJOBNUM. No table
name on field in expression since hanve’t saved yet Must makeup
own field name
SCORE ________/50