IFSM 330 Business Intelligence and Data Analytics Business Intelligence SQL Assignment Instructions

computer science

Description

Student Name:
IFSM 330 Business Intelligence and Data Analytics
Business Intelligence SQL Assignment Instructions

First of all, have a look at the SQL database creation and population code that accompanies this assignment.  You should be familiar with the fields and what kinds of data are represented in the database.  All questions and items in this assignment pertain to the SQL code that accompanies this assignment.

You must include all SQL code here in the spaces below, respecting standard capitalization conventions as presented in the tutorials.  For ease of review, please use BOLD on any SQL code (see the example below).  For certain responses you should also include a ‘snip’ / screenshot of your results.  If you can, try not to take an image of the entire screen, but only of the relevant output.

Here is an example of a database schema image and a query results image from the Khan Academy tutorials.  Due to screen resolution, it’s not always possible to capture all query results.

 

 

You should use the New SQL page at Khan Academy to complete this assignment, both to test code and take screenshots:

https://www.khanacademy.org/computer-programming/new/sql

 
1). 10 points.  As you glance through the database, you note that Houston is spelled with a lower case ‘h’ in at least one case.  First of all, write the SQL code to list all values for Houston, capitalized or not capitalized.  Begin with SELECT * FROM sales to list all fields.

 

 
Then, write the SQL script to update all lower case instances of houston to Houston.  NOTE: use of WHERE id= is not permitted.  This method is used in the tutorial but is not practical if you had a very large database.

 

 

 
2) 10 points. Write an SQL query to list all products and the total sales in dollars for each product.  List products in order from highest total sales to lowest.  Use Total_Sales as the label for the field (column) containing the sales data.

 

INSERT a snip (image) of your QUERY RESULTS here:

 

 

 
3) 10 points.  Write an SQL query to list the customer name and the dollar amount of total purchases. List only names and dollar amounts if total purchases exceed $5000.  List in order of total purchases from highest to lowest and use the column titles Customer_Name and Total_Purchases.

 

INSERT a snip (image) of your QUERY RESULTS here:

 

 

 
4) 10 points.  Write an SQL query to list the states (note Canadian provinces are also included in the database) and the dollar amount of the average purchase.  List only states (and provinces) when the average purchase exceeds $2500.  List in order of average purchase from highest to lowest and use the column titles State_Province and Average_Purchase.  Round the dollar amount of the Average_Purchase so only whole numbers (no decimals) will appear.

 

INSERT a snip (image) of your QUERY RESULTS here:

Preview Solution
Price $15
Buy Ready Solution
(2221 times downloaded)
OR
Get Same Assignment Done From Scratch

Get instant assignment help service

Related Questions in computer science category

Disclaimer
The ready solutions purchased from Library are already used solutions. Please do not submit them directly as it may lead to plagiarism. Once paid, the solution file download link will be sent to your provided email. Please either use them for learning purpose or re-write them in your own language. In case if you haven't get the email, do let us know via chat support.