MIS 431 Fall 2021, SQL Team Assignment For each question, write the query and test it in MySql. For each problem, you will be submitting the query and a screenshot of the first 5 records of your output. If it’s less than 5 records, then those results. You must use names and descriptions and not the id number. For example, if it asks for the Customer contact, then it would be the Customer first and last name and not their Customer Number. Note, the limit keyword is not accepted in your query. Please turn in 1 copy per team along with the signature page below. Please type team members names on the honor code sheet. By having your name on it, you are agreeing to abide by the honor code. One person uploads the entire assignment to the team assignment in Canvas. There will be a peer evaluation after the assignment is due. If you do not significantly contribute to the assignment, you will not get full credit for the assignment. 1. What are the product names, product lines, and product description of all products whose scale is 1:18? 2. What products can be purchased for prices between $95 and $105? 3. List the product code, quantity in stock, MSRP, and buy price for the lowest priced motorcycle. 4. List the product name and buy price for products that are Motorcycles and 1:10 Product Scale that are greater than the average buy price for products that are Motorcycles and 1:10 product scale. Please format your average to two decimal places. 5. Which employees have a last name starting with the letter T? Format so the output is only one column with a comma and space between the last name and the first name. 6. List the product name and buy price of those products with the same buy price; only include buy prices under $70. 7. List the customer contact name, productline, orderdate, and product name for the sales that occurred in January. Customer name should be formatted to one column, with a space between first name and last name and then labeled as customer in the heading. 8. List the customers with the Sales Reps who have the employee numbers 1370 or 1165. Please use the In keyword and not a subquery. 9. List the customer name, the contact first and last name and if they have a sales rep, the sales rep first and last name. Concatinate the first and last name of the contact, and also the first and last name of the sales rep and name the headers appropriately. 10. List the Employees last and first name separated by a comma, and the country they are working in and list the Customer contact last and first name separated by a comma and the country they are in. Name the headers Employees_And_Customers, and Location. Your result should be in two columns and sort the output by country. 11. List the product description and number of products that are Classic Cars. 12. Using the case keyword, for each Product, display the name, product line, product vendor, and if the product is product scale 1:10, display “Small Size”, if 1:12, display “Mid Size”, else display “Other Size”. 13. List the Product name, Product Description, and Product Vendor of the cheapest Product using BuyPrice 14. List every customer contact name that has not purchased any products. 15. How many different customers bought the 1968 Ford Mustang? 16. Display the different products and their quantity in stock. Use the IF function to generate inventory replenishment status messages. Use 4000 as your benchmark to replenish inventory and put the items in alphabetical order. Therefore, if it’s less than 4000, the message should be Replenish inventory, else it should be Inventory levels are Fine. Also, the column header should be Message. 17. List the Customer name, order number, and total cost for the customer who has the most expensive order. Note, a sale may have several items on the order. 18. List the product name and that has a higher-than-average MSRP and a lower than average buyPrice 19. List the product name and MSRP where the MSRP is the minimum amount greater than the average MSRP. 20. List the Product Number, Name, Scale, Order Date, and number of days since the order date of all orders within the past 25 days. Name any headers appropriately. MIS 431 SQL Team Assignment I affirm that I have not given, received, nor witnessed unauthorized aid on this deliverable and have completed this work honestly and according to the professor’s guidelines. Please type your name below. By having your name on it, you are agreeing to abide by the honor code. Team Member _______________________________________ Team Member _______________________________________ Team Member _______________________________________ Team Member _______________________________________ Team Member _______________________________________