IT fundamentals
In this assignment, 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 Prime Paints .sql 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 theshold 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.

1. List the names and street addresses of all vendors based in states of Michigan or Ohio. Sort the list by city name.

2. Find total number of stock items the company have in their warehouse.

3. 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.

4. Display the first name and contact details (email, phone) of all ‘product specialists’ who work in Marketing.

5. Find the employee number, full name and hire date of oldest staff member.

6. Find the date(s) with highest amount of sales. Also display the corresponding total sale amount.

7. List all brand names and the total dollar amount of sales for each brand. Sort the list by descending order of brand sales.

8. Find highest paying customers (according to their invoice totals) and list their names along with the city and state they belong to.

9. Find out the total of salaries paid out by all departments during the month of June 2017. Sort the list by department name.

10. For the year 2015, get a list of all professions and their the 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.

[1.5 marks × 10]

For some questions that require date and time calculations, please refer to MySQL documentation to see the available functions.

RATIONALE

back to top

This assessment task will assess the following learning outcome/s:

· be able to use complex SQL commands to query a database.

MARKING CRITERIA AND STANDARDS

back to top

The marking criteria for this assignment is:

Criteria

HD

DI

CR

PS

be able to use complex SQL commands to query a database.

SQL syntax returns all attributes that meet the business requirement from the tables that hold the data and applies the correct restrictions to retrieve the required information.

SQL syntax returns all attributes that meet the business requirement from the tables that hold the data with minor omissions and applies appropriate restrictions to retrieve the required information.

SQL syntax returns key attributes that meet the business requirement from the tables that hold the data and applies relevant restrictions to retrieve the required informationn

SQL syntax returns key attributes that meet the business requirement from the tables that hold the data but the restrictions applied to retrieve the required information contain minor errors.

PRESENTATION

back to top

Please prepare and submit a single Word document containing your SQL queries and result screenshots.

REQUIREMENTS

back to top

Only MySQL RDMBS must be used for this exercise. You may use a graphical front end like MySQL Workbench for executing queries. Instruction videos for this task will be provided on Interact site.