You are supplied a simple dataset for which you will write SQL queries. You will be using the well-known MySQL RDBMS for this task.
You are provided a MySQL data file containing data about a paints business based in United States. It contains data about the customers, invoices, employees, products and vendors. Import this file into your MySQL installation by following the instructions on Interact site.
Click here to download [login to view URL] data file. For your convenience an ERD is also provided so that you can better understand the database structure. Some column names are ambiguous, their description is provided below:
Prod_SKU - A unique identifier for every single product in stock.
Prod_Min - A minimum threshold level for a product. When stock falls below that level, it must be reordred.
Prod_QOH - Quantity on hand for the given product.
Cust_Balance - The amount owed by a customer. It is not the same as total of their invoices.
Sal_From and Sal_End - Dates indicating a pay period.
Sal_Amount - Salary paid to the employee in the given pay period
Emp_Comm - Rate of commission for some employees.
Write and execute SQL statements to complete each of the following tasks. Copy your SQL statements into the assignment document.
Secondly, include the screenshots of results obtained by executing those statements on the DBMS. Typing the results of SQL statements is NOT acceptable.
List the names and street addresses of all vendors based in states of Michigan or Ohio. Sort the list by city name.
Find total number of stock items the company have in their warehouse.
Find out the duration (number of days) for which we there are records of sale data. Display the earliest date, latest date and the period between two.
Display the first name and contact details (email, phone) of all 'product specialists' who work in Marketing.
Find the employee number, full name and hire date of oldest staff member.
Find the date(s) with highest amount of sales. Also display the corresponding total sale amount.
List all brand names and the total dollar amount of sales for each brand. Sort the list by descending order of brand sales.
Find highest paying customers (according to their invoice totals) and list their names along with the city and state they belong to.
Find out the total of salaries paid out by all departments during the month of June 2017. Sort the list by department name.
For the year 2015, get a list of all professions and their average salary. Profession is identified employee title. Average must be displayed with 2 decimal places. Sort the list by highest paid jobs at the top.
4 威客就此工作平均出价 $25
Dear Sir/Ma'am, I have 12 years of experience in MySql and would write 10 mentioned queries with screen shot to help you. Please award this project to me and I will start it right now.
Hi,How are you? I'm very confident in MySql. I can finish this project in a short time. I can do this project perfectly. Please send me a message so that we can discuss more about your project. Thanks.