SQLtools only homework

Page 1 of 7

INFS 4790/5790 Take Home Test #3

Instructions: This is individual effort only – you may not collaborate with any other student (past or present), faculty member, or other person or service (including posting requests for help on website forums). You do not need any SQL clauses or keywords beyond the ones that were covered in class to complete this assignment -- the purpose of the assignment is for you to demonstrate your mastery of the material covered in class, not to make you research code beyond what was covered. Create the tables shown in the ERD using CTAS commands to copy the structure and data from the HAM schema. 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. Note that you must use the exact same table names in your account as are given in the ERD. Submission instructions:

 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 Tools o Is not developed in the baseora database o Uses functions or techniques not covered in this class (The assignment is a test of your mastery of

the material we covered in class.) o Queries that return an error when executed o Queries that contain a Cartesian product

 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 write CTAS commands to copy the tables into your account from the HAM schema. 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 on the first line in the file.

 Remove any extra commands (e.g., SELECT * FROM JOB;) that you used as you were crafting your answers.

 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 7

Healthy Athletic Manufacturing (HAM) 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.

HAM ERD

Explanation of the data model: The WORKER table contains data on some of the people that work at HAM. All of the workers in this system are manufacturing workers that produce parts for the products that HAM sells. The PART table contains data on the parts produced by HAM. 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 7

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.

1. 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 order. Result:

WORKER PART_DESCRIPT Avg Labor Cost per Unit Times Produced

Alex Reiger 1/2in. threaded pedal bar $0.17 4

Louie DePalma Handlebar, left, RDAP $0.31 3

Jeff Bennett Molded left fanguard $0.33 4

Latka Gravas Molded right fanguard $0.46 4

Elaine Nardo Solid molded chain guard $0.96 5

Elaine Nardo RDAP handlebar subassembly $1.33 3

2. Write a query to display the part number, description, and quantity on hand, and the quantity of that part that is scheduled to be produced during each scheduled run of that part. Include parts that are not scheduled at all. Limit the results to only parts that are a kind of “bar” or “bracket”. Further limit the results to only parts for which that are more than 50 units currently in inventory. Finally, limit the results to scheduled run in which the planned quantity is less than 5000 (including nulls, such as when the part is not scheduled to run). Be certain to make the query case insensitive since “bar” and “bracket” may be capitalized in several different ways in the data. Sort the results by the current quantity on hand in ascending order, then by the scheduled quantity in ascending order. Result:

PART_NUM PART_DESCRIPT PART_QOH SCH_PLAN_QTY

1102 Banded HandleBar, Left 60 1500

3832 1/2in. threaded pedal bar 67 800

3832 1/2in. threaded pedal bar 67 1000

1105 Banded HandleBar, Right 70

13058 Handlebar, right, RDAP 75 600

11303 Square bar lift bracket (right) 93

13587 RDAP handlebar subassembly 135 600

11304 Square bar lift bracket (left) 173

13059 Handlebar, left, RDAP 178 600

2956 Corner bracket, slotted 327 1000

8392 Bar plate connector 363 300

2838 Slotted HANDLEBAR subassembly 420 400

2838 Slotted HANDLEBAR subassembly 420 600

1001 3/4in. L-bracket 600 3000

1001 3/4in. L-bracket 600 3000

Page 4 of 7

3. 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 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 order then by the quantity produced in ascending order. Result:

WORK_LNAME EQUIP_NUM EQUIP_TYPE RUN DATE TOTALCOST JOB_QTY_PRODUCED

Ignatowski 25900414 INJECTOR 2018-05-19 348 230

Ignatowski 25900414 INJECTOR 2018-05-09 348 250

Sanders 20049457 INJECTOR 2018-05-05 342 153

Tichner 20049457 INJECTOR 2018-05-11 297.25 50

Sanders 20109386 ARC WELDER 2018-05-08 297 280

Nardo 25900414 INJECTOR 2018-05-08 272 200

Nardo 25900414 INJECTOR 2018-05-18 272 210

Nardo 25900414 INJECTOR 2018-05-18 272 210

Nardo 25900414 INJECTOR 2018-05-13 272 220

Nardo 25900414 INJECTOR 2018-05-09 272 236

Ignatowski 20384857 ROBOTIC WELDER 2018-05-18 210 190

King 20109386 ARC WELDER 2018-05-05 208.75 220

4. Write a query to display the worker number, last name, first name, equipment number, the times that the worker has used that piece of equipment for a job, the average number of parts per hour that the worker has produced on that piece of equipment, and the average number of parts per hour produced on that piece of equipment regardless of which worker was using it. Limit the results to only cases in which the worker has used that piece of equipment more than twice. Sort the result by the worker number in ascending order. Result:

WORK_NUM WORK_LNAME WORK_FNAME EQUIP_NUM Times Worker Used Worker on Equip Avg Equip Avg Productivity

76544 Nardo Elaine 25900414 5 26.9 31.364

87654 Reiger Alex 20000093 4 93.438 80

96531 Gravas Latka 20038459 3 153.125 205.475

97640 DePalma Louie 20484906 3 41.786 41.786

Page 5 of 7

5. Write a query to display the part catalog, number of jobs to produce that part, the number of different workers that have ever produced the part, and the number of different pieces of equipment that have ever produced that part. The part catalog is the part number in parentheses followed by the part description. Limit the results to parts that currently have fewer than 100 units in inventory. Sort the result by the number of jobs that have ever run to produce that part. Result:

PART Number of Jobs Different Workers Different Equipment

(7463) Molded left fanguard 9 5 4

(8384) Solid molded chain guard 7 2 1

(7462) Molded right fanguard 6 3 3

(3832) 1/2in. threaded pedal bar 5 2 2

(8293) RDAP primary frame assembly 4 2 1

(1026) Rounded base plate 2 1 1

(11303) Square bar lift bracket (right) 0 0 0

(11840) Board support assembly 0 0 0

(10322) Board support, front 0 0 0

(7783) Retainer clip 3/4in. 0 0 0

(3823) Square bar lift bracket (center) 0 0 0

(1383) Center board plate 0 0 0

(1105) Banded HandleBar, Right 0 0 0

(13058) Handlebar, right, RDAP 0 0 0

(1102) Banded HandleBar, Left 0 0 0

6. Write a query to display the part number and the average lead time for scheduling parts, where the average lead time is the average difference between the date the schedule entry is made and the date when the part will be needed. Only include scheduled runs that have already resulted in the parts being used (i.e., schedules that have an entry for the actual quantity used). Sort the results by the part number in ascending order. Result:

PART_NUM Average Lead Time

101 5

1001 3.7

2334 6

2838 6

2956 6

3832 5

7462 5

7463 5

7783 6

8384 5.5

11840 2

Page 6 of 7

7. 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 order. Result:

WORK_NUM WORK_LNAME WORK_FNAME

33353 Ali Aziz

33268 Bentley Omar

30776 Patel Raj

31009 Sanders Jeff

33905 Tichner Rondrah

8. Write a query to display the worker number, worker full name (first and last separated by a single space), and the number of jobs that the worker has completed. Include workers that have never completed any jobs. Limit the results to workers that have an hourly wage over $30 per hour. Sort the results by the number of jobs worked in descending order, then by worker last name in ascending order. Result:

WORK_NUM WORKER Number of Jobs

4933 Jim Ignatowski 6

30343 Stephen Sanders 2

33234 Tiffany King 1

31009 Jeff Sanders 1

33905 Rondrah Tichner 1

33353 Aziz Ali 0

33268 Omar Bentley 0

30776 Raj Patel 0

9. Write a query to display the job number, part number, part description, start time, worker last name, worker title, labor cost, and units produced for all jobs that ran on an Injector. The labor cost is the worker’s wage multiplied by the hours worked on that job. Limit the results to only jobs where the labor cost is more than the largest labor cost associated with any job that was performed using a Robotic Welder. Sort the results by the labor cost in descending order, by units produced in ascending order, the part number in ascending order, and then the job number in ascending order. Result:

JOB_NUM PART_NUM PART_DESCRIPT JOB_STARTTIME WORK_LNAME Title Labor Cost Units

Produced

1222 2334 Right shaft assembly 05-MAY-18 Sanders Machinist II 284 153

1275 8384 Solid molded chain guard 19-MAY-18 Ignatowski Machinist III 282 230

1242 8384 Solid molded chain guard 09-MAY-18 Ignatowski Machinist III 282 250

1251 7463 Molded left fanguard 11-MAY-18 Tichner Machinist I 241.5 50

1240 8384 Solid molded chain guard 08-MAY-18 Nardo Machinist I 206 200

1271 8384 Solid molded chain guard 18-MAY-18 Nardo Machinist I 206 210

1272 8384 Solid molded chain guard 18-MAY-18 Nardo Machinist I 206 210

1256 8384 Solid molded chain guard 13-MAY-18 Nardo Machinist I 206 220

1243 8384 Solid molded chain guard 09-MAY-18 Nardo Machinist I 206 236

Page 7 of 7

10. 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 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 order, and then by the 1 hour operating cost in ascending order. Result:

EQUIP_NUM EQUIP_TYPE 1 HOUR RUN 4 HOUR RUN 8 HOUR RUN

20009956 ARC WELDER $4.25 $8.00 $13.00

20109386 ARC WELDER $6.00 $9.00 $13.00

21003848 ARC WELDER $6.10 $9.40 $13.80

10384590 MANUAL PRESS $0.35 $1.40 $2.80

20119451 REAMER $3.55 $8.20 $14.40

20989450 REAMER $5.45 $6.80 $8.60

20000093 REAMER $5.75 $11.00 $18.00

Checklist:

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

 Avoid these common mistakes: o Using To_Char to display a date in native format o Using LIKE when all you need is “=” o Using LIKE without a wildcard o Using Upper or Lower on numeric data o Using Upper or Lower on a literal o Using a Cartesian product o Using a subquery when it is not needed o Including unnecessary tables in a query o Using outer joins when only inner joins are needed

 Did you put your name in a comment at the top of your file?

 Did you number your answers using comments?

 Did you include the CTAS commands from your file?

 Did you remove all extra commands (there should be only five CTAS commands and ten SELECT queries in the file)?

 Did you remember to save your file as .sql in SQL Tools?

 Did you remember to upload your submission to the “Take Home Test 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?