ITC556 Query databases using SQL Database system Assessment and Tutor Proposal
Charles Sturt University
ITC556 Query databases using SQL Database system
Assessment No: 4
Student’s Score cards
Query databases using SQL
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 PrimePaints.sql data file (https://cloudstor.aarnet.edu.au/plus/s/ fUHA861x1FzvNnV/download). For your convenience an ERD is also provided (https://cloudstor.aarnet.edu.au/plus/s/UD6cDVvbB2hkN1P/download) 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.
- 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 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 (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html) to see the available functions.
Why invest in our services?
Our assignment help team is trained to provide you high quality writing services.
High scores achieved by our students is a portrayal of our high quality online assignment help
You can place your assignment order through 4 easy modes of communication