Homework3.pdf

Page 1 of 5

Database Design and Development
Homework 3

Create the SQL query as requested for each question. Be certain to follow the formatting illustrated in each
question and use column aliases as shown. Note that you are provided with sample data only. The data you are
using is static – it is not being updated by the users as parts are scheduled and jobs completed.

• The assignment is individual effort only. You cannot talk to each other or anyone else regarding the
problems except the instructor. Collaborating with anyone else in any way is a violation of the class
academic misconduct policy.

• NO CREDIT will be given for code that:
o Is developed using a tool other than SQL Server Management Studio (SSMS)
o Is not developed in your assigned database
o Uses functions, keywords, or techniques not covered in this class (The assignment is a test of your

mastery of the material we covered in class. Check the NOT ALLOWED LIST in D2L for common
mistakes.)

o Queries that return an error when executed
o Queries that contain a Cartesian product (watch the video about Cartesian products)
o Queries that contain a subquery anywhere other than FROM or WHERE.

• All queries must be terminated with a semicolon.

• Do not use unnecessary subqueries.

• Submission is to be made through Desire2Learn (D2L). Upload your submission with a .sql extension (do
NOT put it in an MS Word document) to the Take Home Test 3 dropbox in D2L.

• You must create a MAG schema in your database and copy the tables into the MAG schema of your

database from the MAG schema of the STARTERDB database. Do NOT make up your own tables or make
up your own data – only use the table structures and data that already exist on our server.

• Be certain to use the column aliases and formats illustrated in the sample output shown for
each problem.

• Use comments (either a double dash – – or a block comment /* */) to number your answers
with the appropriate question number. You do not need to re-write the question. Do not start
your answer on the same line as the comment that numbers the answer.

• Use a comment at the beginning of the text file to include your name in the file. Nothing but
your name should be on the first line (e.g., –John Smith). (Hint: your name does not start with
“Name:”)

• Remove all extra commands (like SELECT * FROM MAG.JOB;) that you used as you were crafting
your answers. Points will be deducted for extra commands left in the file.

• Only submit one query per question. If there is more than one query for a question, then only
the first query will be evaluated.

Page 2 of 5

Manufacturing Athletic Gear (MAG) is a company that produces a variety of exercise bicycles and
other fitness equipment. You are writing queries that will form the basis of several reports that the
users have requested from the system. The data that you will be working with is a small subset of the
data that were generated for use while developing and testing the system. Your queries must match
the requirements given to ensure that correct results will be produced when applied to the full set of
live data.

MAG ERD

Explanation of the data model:
The WORKER table contains data on some of the people that work at MAG. All of the workers in this
system are manufacturing workers that produce parts for the products that MAG sells. The PART table
contains data on the parts produced by MAG. Product production is planned based on customer
demand data (not included in this data model). A production manager will evaluate the demand data
and determine which products need to be assembled on which dates and in what quantities (not
included in this data model). Based on the products that need to be produced, parts are scheduled for
production. The SCHEDULE table contains data on the parts that need to be produced, what date the
parts will be needed, and an estimate on how many will be needed (sch_plan_qty). After the production
run that uses these parts is finished, the actual number of each part used (sch_actual_qty) is entered.
Based on the upcoming demand for scheduled parts, the shop manager will assign workers to produce
specific parts. The EQUIPMENT table contains data on the manufacturing equipment used to produce
the parts. The JOB table contains data on which worker produces which part on which equipment.

Page 3 of 5

For each of the following questions, write the SELECT query that would return the results requested. Be
certain to use the column aliases and formats illustrated in the sample output shown for each problem.

0. Create the MAG schema in your database. Copy the above tables into your MAG schema from
the MAG schema in the STARTERDB database. You MUST name your tables exactly the same
(pay close attention for typos) as those given in the ERD. No points are associated with this
problem specifically, but if it isn’t right none of your code will work in my database and you’ll
end up with a zero on the homework.

1. Write a query to display the worker’s last name, equipment number, equipment type, job date,

the total cost for that job, and the quantity of parts produced in that job. The total cost is the
labor cost plus the equipment cost, where the labor cost is the worker’s wage multiplied by the
hours that the job ran. The equipment cost is the equipment startup cost plus the run cost. The
run cost is the hourly run cost for the equipment multiplied by the hours that the job ran. Limit
the results to only jobs with a total cost greater than $200 and that produced fewer than 300
parts. Sort the results by the total cost in descending then by the quantity produced in
ascending .
Result:

2. Write a query to display the worker number, last name and first name for all machinists that
have never completed a job using any type of “welder” equipment. Sort the results by worker
last name and then first name, both in ascending .
Result:

Page 4 of 5

3. Write a query to display the equipment number, type and operating cost for a 1-hour run, a 4-
hour run, and an 8-hour run for each piece of equipment. The operating cost is the startup cost
for the equipment plus the run cost. The run cost is the hourly run cost for the equipment
multiplied by the number of hours the equipment will be running. Limit the results to only
equipment that is a Manual Press, Reamer, or Arc Welder. Sort the results by the equipment
type in ascending , and then by the 1-hour operating cost in ascending .
Result:

4. Write a query to display the worker name (first and last names separated with a single space),
part description, average labor cost per unit, and times produced. The average labor cost per
unit is the calculated by averaging the labor cost per unit for all jobs in which that worker
produced that part. The labor cost per unit for a job is calculated as the worker’s hourly wage
multiplied by the number of hours that a job ran, divided by the quantity of the part produced
during that job. The number of times produced is simply the number of jobs in which that
worker produced that part. Limit the results to only ones where the times produced is greater
than 2. Sort the result by the average labor cost per unit in ascending .
Result:

5. Write a query to display the worker number, last name, and first name of the worker that
worked the longest job ever run on a reamer.
Result:

Page 5 of 5

Checklist:

• Does your output match the result output exactly (column aliases, rounding, sorting, etc.)?

• Avoid these common mistakes:
o Using LIKE when all you need is “=”
o Using LIKE without a wildcard
o Using a Cartesian product
o Using a subquery when it is not needed
o Using a subquery anywhere other than FROM or WHERE
o Including unnecessary tables in a query
o Using outer joins when only inner joins are needed
o Using functions, keywords, or techniques not covered in this class (Check the Not

Allowed List in D2L for some common SQL functions and techniques that you might have
seen on the Internet that are NOT covered in this class. The list is not exhaustive, but it
covers the disallowed things that I see the most often.)

• Did you put your name in a comment at the top of your file? Nothing but your name in the
comment?

• Did you number your answers using comments?

• Did you include the commands to copy the tables into the MAG schema (Question 0)?

• Did you remove all extra commands (the only commands should be commands for question 0
and five SELECT queries)?

• Did you remember to end your filename with .sql?

• Did you remember to upload your submission to the Homework 3 dropbox?

• Did you logout of D2L then log back in and go to the dropbox to open your submission and verify
that you uploaded the correct file?