Homework 1

data analytics exercise and need the explanation and answer to help me learn.

Please Import following data(Customer,Orderdetail,orders and product) from Excel CSV files into the MySQL tables :
Question 1 is worth 1 point, and each query in Question 2 is worth 0.9 points, making a total of 10 points when combined. No late submission is acceptable.
Some instructions for Question 2( Query2): go to slide 22 on the MYSQL_Lecture2 using SELECT DISTINCT Clause. If want to get only distinct values in a specific column of a table( no duplication), you should use this statement.
Requirements: Follow the instruction
SQL-Homework 1: MySQL
Fall 2023, Due date: October 30th, 11:59pm
Question 1)
Based on the plumbing supply store database (PlumbingStore.accdb), create a MySQL database:
Database name: plumbing
Import data from Excel CSV files into the following tables:
Question 2)
Write SQL queries that display the following information:
Query 1) The name of each product.
Query 2) All orders with no duplicate entries in the case of more than one order placed on the same day.
Query 3) The entire PRODUCT table.
Query 4) For each product, the product ID, product name, and the ratio of the number of units on order to the number of units in stock. Use AS clause to rename the last column of the query result as “OrderRatio”.
Query 5) Show the name and unit price of all products priced below $50.
(Note: SQL treats currency amounts like any other number, so you should not use a $ sign or quotes in your query.)
Query 6) Show the product name, units on order, and units in stock for all products for which the number of units on order is at least 40% of the number of units in stock.
Query 7) Show the same information as in Query 6, but with the additional restriction that the number of units on order is no more than 10.
Query 8) Show the first name, last name, city, and state of all customers outside New Jersey (state code “NJ”).
Query 9) Show the first name, last name, city, and state of all customers who are outside New Jersey or have the first name “Robert” (or both).
Query 10) Show the name, unit price, units in stock, and units on order for each product, sorted from the largest number of units in stock to the smallest. For products with the same number of units in stock, sort the rows of output from the largest number of units on order to the smallest.
Submission guidelines:
Submit your MySQL file as PlumbingStore_lastname_firstname.sql on Canvas.
Rename HW1Report.docx as HW1Report_lastname_firstname.docx, complete the report by pasting the SQL statement for each query, and submit it on Canvas along with your MySQL file.
MIS Homework 1 Report: MySQL
Fall 2023, Due date: October 30th, 11:59pm

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *