Description
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.
CREATE TABLE customers (id INTEGER PRIMARY
KEY, name TEXT, age INTEGER, weight REAL);
INSERT INTO customers VALUES (73,
"Brian", 33, 50);
|
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). Your SQL code must be in text format, so that
the instructor can cut and paste for review and testing purposes. Here
is an example of SQL code/text that can be cut and pasted for testing:
For
certain responses you are also asked to 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 can 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:
5a) NOTE:
For all of Q5, this must be an actual situation/scenario that can be executed
with an SQL query on the data provided in the assignment. NOT a hypothetical scenario or partial SQL
query. If this is unclear, please ask.
15 points. In 2-3 sentences, given the nature and structure of the furniturestore.sql data, describe a situation
or scenario (or question that might be answered) that would require use of a
CASE statement in SQL. (This is a very open question with many correct
responses. Think about what the CASE
statement in SQL allows us to do.)
5b) Create the SQL query for the scenario presented in part a). This SQL query must be complete, executable
and produce output relevant to the scenario presented in part a).
INSERT
a snip (image) of your QUERY RESULTS here:
6a) 15 points. You have been asked to produce a report that
shows all chairs bought on January 12, 2019.
In the box below, respond to the following question:
Why does the
following SQL query not yield any results (feel free to test it)?
SELECT
* FROM sales WHERE product = ‘Chair’ AND transaction_date = '1/12/19';
