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.
https://dbu.blackboard.com/ultra/courses/_53467_1/cl/outline
https://dbu.blackboard.com/ultra/courses/_53467_1/cl/outline
https://dbu.blackboard.com/ultra/courses/_53467_1/cl/outline
https://notepad-plus-plus.org/
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. https://www.mysqltutorial.org/mysql-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.
https://notepad-plus-plus.org/
Interested in getting help with this assignment?
Get a professional writing team to work on your assignment!
Order Now
Recent posts
For this final assignment, you will prepare a brief paper detailing the steps undertaken to complete a presentation that disseminates information you assemble
Please choose to answer only one of the 2 following questions. Option 1: In your opinion and based on scientific, peer-reviewed published evidence, does child
At the beginning of the previous academic year, the institution announced it would drop football at the conclusion of the season. The announcement created pub
you will review current research in Personality and provide a critical evaluation of that personality research through an annotated bibliography. An annotated
In Module 5, we considered the third in our three-part series on research design. Specifically, the focus was on the longitudinal studies, in which the resear
Need help with a similar or different Task?
We have the best writers to help you. Hire Writer Now
