exam

Click the link bellow to download database named EX1A.  Use Windows Explorer / Finder to drag the file to your desktop.  Once there, rename the database to be YOUR name.  After the file has been renamed you can open it.  Each query should be saved as “Question1”, “Question2” etc. When you have completed the exam click the link at the bottom of the page to upload your file.

Download file —> Ex1A.accdb

Begin-Exam

With the economy in the tank, people are spending their money fixing their cars rather than purchasing new ones.  The local dealer has a large number of mechanics who deal with hundreds of maintenance issues each week.  One of your most important tasks as service manager, is to keep track of the work done by the mechanics and be able to pull up the details of any particular job.  You have decided to use a database to store the maintenance data.

The database:

The Cars database has two tables – Mechanic and Jobs.  Each mechanic can be involved with many jobs, but each job involves just one mechanic.  Check out the relationship diagram to familiarize yourself with this database – then create queries to answer these questions.

Questions:

1. List the transmission jobs by mechanic Smith that are in the $250 to $500 range.

2. What is the total dollar amount of the jobs done by each mechanic?

3. Similar to question 2, how many dollars have been brought in each day?

4. There has been concern that one of the mechanics is padding the cost.  Customers are upset.  You have decided to check on all jobs completed within the past 3 weeks.  Get the query set up…it has to work whenever we run it (3 weeks from whenever we run the query – i.e. can’t use a specific date).

5. Things are heating up.  You boss has heard from national headquarters that valve jobs since Febuary 4, 2016 used a faulty part…we need a list of all those jobs.

6. The mechanics who work on transmissions get a bonus of 5% for each job they complete.  List their bonuses in a new field named BONUS.

7. Many customers are unhappy with the cost of car repair.  Once in awhile you need to change the price of a job.  For all the oil changes that took place last week, we are taking $5 off the price we quoted.  Write a query to update these bills.

8. We need flexible query that will let our manager enter the name of any mechanic and see a list of the jobs.  Write a query that will ask the user for the name of the mechanic…then list that mechanic’s jobs within the past week.

9. As a manager of 30 mechanics, I should be able to recall their names – but the memory is slipping seriously!  I’m too embarrassed to ask for the guy’s name, so I use wildcards to find his name.  All I can remember is that the name seemed to start with either a C or S and probably ends with a Y.  List the possibilities.

10. Oops…a huge problem – we have a mechanic who put in several fake jobs.  Delete all jobs involving mechanic Smith made between 2/2/2016 and 2/10/2016 by using a query.  We now train new mechanics in ethics in the hope of eliminating future problems.  The deletions will come from the Jobs table.  Smith is mechanic M123.

End-Exam

Click here to upload and submit exam for grading