Database Design And Development

Page 1 of 5Database Design and Development Homework 3Create 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 yourmastery 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 yourdatabase 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 5Manufacturing 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 ERDExplanation 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 5For 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 order then by the quantity produced in ascending order. 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 order. Result:Page 4 of 53. 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 order, and then by the 1-hour operating cost in ascending order. 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 order. 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 5Checklist:• 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 NotAllowed 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?