MSITM-6301 Business Data Warehousing
Homework 2 – Spring 2024
1
For this assignment, you will be writing MySQL code.
Before you can complete this assignment, you must install MySQL on your PC. To access the installation
instructions, login to Blackboard then click Content > MySQL Training Materials.
If you are a beginner to MySQL, I strongly recommend that you work through MySQL Tutorial for Beginners [Full
Course]. To see a Table of Contents, scroll down just a little under the video and click on SHOW MORE. If you
have already installed MySQL on your PC, skip this part of the video, and continue on to the next part of the
video.
Additional training videos/websites posted on Blackboard are also very helpful
• W3Schools SQL Tutorial – great reference to use while working through your homework assignments
• MySQL – Data Types
• MySQL Essential Training – LinkdIn Learning
• Learning MySQL Development
• Programming Foundations: Databases,
These resources are posted on Blackboard. (Content > MySQL Training Materials)
Also, posted on Blackboard for your review under Content > Chapter Slides are the slides Introduction to MySQL.
It is not required to complete all of the training videos. Complete as many as you need to understand the basics
of MySQL, especially SELECT statements.
To submit your work (tables, views, scripts, etc.), you will execute a dump to a file named StudentLastName-
MySQL-HW1-Dump. Also, save the script file(s) as StudentLastName-MySQL-HW1-Script. Submit these files
to Blackboard. Follow the instructions in the file MySQL Assignment Submission Instructions posted on
Blackboard. (Content > MySQL Training Materials)
After watching the MySQL tutorials and reviewing the Introduction to MySQL slides, complete the following
exercises.
1. Download the following files containing the code to create the tables you will use in this assignment.
• DW_HW1_ConCo_MySQL.txt (Problems 1-8)
• DW_HW1_SaleCo_MySQL.txt (Problems 9 – 16)
• DW_HW1_SaleCo2_MySQL.txt (Problems 17 – 18)
2. Open MySQL
3. Choose option A or B below to import the file/code into MySQL to complete the problems. Once
imported run the script file by clicking on the Execute Icon (lightning bolt) to create the needed database
and tables for the queries.
(Note: Import DW_HW1_ConCo_MySQL.txt to complete problems 1 – 8.
Import DW_HW1_SaleCo_MySQL.to complete problems 9 – 16, then
Import DW_HW1_Saleco2_MySQL to complete problems 17 – 18)
Import the script by:
A. Copy the text into MySQL and then execute the code. This requires a text editor to access and
copy the code. This file was created using the Notepad++ text editor. This program can be
downloaded for free from Notepad-Plus-Plus.org.
B. Import the text file into MySQL. Its basically the same as the process of exporting, you are just
importing.
MSITM-6301 Business Data Warehousing
Homework 2 – Spring 2024
2
Using the DW_HW1_Conco database and tables you have created, complete the following problems in MySQL.
When you have completed them, submit your dump and script file to Blackboard.
The DW_HW1_Conco database stores data for a consulting company that tracks all charges to projects. The
charges are based on the hours each employee works on each project. Below is a picture of the database
structure and contents.
1. Write the SQL code required to list the employee number, last name, first name, and middle initial of all
employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should
be included in the listing. Sort the results by employee number. Assume case sensitivity
2. Using the EMPLOYEE, JOB, and PROJECT tables in the DW_HW1_ConCo database, write the SQL code that
will join the EMPLOYEE and PROJECT tables using EMP_NUM as the common attribute. Display the
attributes shown in the results presented below, sorted by project value.
3. Write the SQL code that will produce the same information that was shown in Problem 2 but sorted by the
employee’s last name.
4. Write the SQL code that will list only the distinct project numbers in the ASSIGN-MENT table, sorted by
project number.
MSITM-6301 Business Data Warehousing
Homework 2 – Spring 2024
3
5. Write the SQL code to validate the ASSIGN_CHARGE values in the ASSIGN-MENT table. Your query should
retrieve the assignment number, employee number, project number, the stored assignment charge
(ASSIGN_CHARGE), and the calculated assignment charge (calculated by multiplying ASSIGN_CHG_HR by
ASSIGN_HOURS). Sort the results by the assignment number.
6. Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked
for each employee and the total charges stemming from those hours worked, sorted by employee number.
The results of running that query are shown below.
7. Write a query to produce the total number of hours and charges for each of the projects represented in the
ASSIGNMENT table, sorted by project number. The output is shown below.
8. Write the SQL code to generate the total hours worked and the total charges made by all employees.
MSITM-6301 Business Data Warehousing
Homework 2 – Spring 2024
4
For the problems 9 – 16 use the DW_HW1_SaleCo database created from DW_HW1_SaleCo_MySQL.txt
The structure and contents of the DW_HW1_SaleCo database are shown below.
9. Write a query to count the number of invoices.
10. Write a query to count the number of customers with a balance of more than $500.
11. Generate a listing of all purchases made by the customers, using the output shown below as your guide. Sort
the results by customer code, invoice number, and product description.
12. Using the output shown below as your guide, generate a list of customer purchases, including the subtotals
for each of the invoice line numbers. The subtotal is a derived attribute calculated by multiplying LINE_UNITS
by LINE_PRICE. Sort the output by customer code, invoice number, and product description. Be certain to use
the column aliases as shown in the figure.
MSITM-6301 Business Data Warehousing
Homework 2 – Spring 2024
5
13. Write a query to display the customer code, balance, and total purchases for each customer. Total purchase
is calculated by summing the line subtotals (as calculated in Problem 12) for each customer. Sort the results
by customer code, and use aliases as shown below.
14. Modify the query in Problem 13 to include the number of individual product purchases made by each
customer. (In other words, if the customer’s invoice is based on three products, one per LINE_NUMBER, you
count three product purchases. Note that in the original invoice data, customer 10011 generated three
invoices, which contained a total of six lines, each representing a product purchase.) Your output values
must match those shown below.
15. Use a query to compute the total of all purchases, the number of purchases, and the average purchase
amount made by each customer. Your output values must match those shown below. Sort the results by
customer code.
16. Create a query to produce the total purchase per invoice, generating the results shown below, sorted by
invoice number. The invoice total is the sum of the product purchases in the LINE that corresponds to the
INVOICE.
MSITM-6301 Business Data Warehousing
Homework 2 – Spring 2024
6
ROLLUP command
The ROLLUP command used with the GROUP BY clause to generate aggregates by different dimensions. The
GROUP BY clause will generate only one aggregate for each new value combination of attributes listed in the
GROUP BY clause. The ROLLUP command goes one step further and enables you to get a subtotal for each
column listed except for the last one, which gets grand total instead.
The syntax for the command is:
SELECT column1[, column2, …], aggregate_function(expression)
FROM table 1 [, table2, …]
[WHERE condition]
GROUP BY ROLLUP (column1 [, column2, …])
[HAVING condition]
[ORDER BY column1 [, column2, …]]
Click on the following link for MySQLTutorial for using ROLLUP.
17. Download the files DW_HW1_SaleCo2_MySQL.txt and import it into MySQL
This file contains the code to create the database and tables you will use in the following steps.
Choose one of the following options to input the script into MySQL and then run the script.
A. Copy the text into MySQL and then execute the code (this requires a text editor. This file was
created using Notepad++. This program can be downloaded for free from Notepad-Plus-Plus.org.
B. Import the text file into MySQL. It’s basically the same as the export, you are just importing.
18. Using the DW_HW1_SaleCo2 database and tables you have created, complete the following problems in
MySQL. When you have completed them, submit your dump and script file to Blackboard.
A. List the total sales by customer and by product, with subtotals by customer and grand total for all
product sales.
B. List the total sales by customer, month, and product, with subtotals by customer and by month
and grand total for all product sales.
C. In the Word document, list at least 2 decisions a manager could make with the information
provided from A and B.
