Homework answers / question archive / 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

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

Computer Science

Share With

---

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.

You should use sqliteonline.com to complete this assignment, both to test code and take screenshots:

https://sqliteonline.com/

Here is an example of a database schema image and a query results image from sqliteonline.com.  Due to screen resolution, it’s not always possible to capture all query results.  But be sure your images are legible, no smaller than the second image below.   If an image is not legible, your assignment may be returned to you and you’ll have to rerun the SQL scripts.

 

 

 

 

Feel free to take several screenshots as needed or make the columns narrower in the sqliteonline output before taking the screenshot (columns can be manually made more narrow by hovering over the lines marked in red below):

 

 

 

 

For some of these questions, you will need to perform a SUM or AVERAGE in a group-

https://www.khanacademy.org/computing/computer-programming/sql/more-advanced-sql-queries/pt/restricting-grouped-results-with-having

 

 

 

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';

 

 

 

 

 

6b) Rewrite the query so that it yields the correct results.

 

 

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

 

 

 

 

 

PART 2:

Part 2 introduces a second table to the database schema.  When creating queries from two tables, in particular when there may be a field that shares a common name, it is safer to construct your queries with the table name AND field name.  You may wish to review the “JOINing related tables” tutorial and video on Khan Academy.

 

7) 5 points. Using an SQL script, add (CREATE) a suppliers table to the database schema, with an autoincrement integer as the primary key (id), and fields for name and country (both text).  The primary key (id) will correspond to the supplier field in the sales table (e.g. supplier in sales is a foreign key).

 

 

 

 

8) 5 points. Using an SQL script, insert the following data into the suppliers table, in the order presented in the table (Reminder: id primary key should have been set to autoincrement when the table was created):

 

Name	Country
Ikea	Sweden
Rosewood	Thailand
BoConcept	Denmark
AllModern	United States

 

 

 

9) 10 points. Write an SQL query to list transaction date, customer name, product, price, supplier name and country (6 columns) ordered by price from highest to lowest.  List only items with a price greater than 1500.   Construct the SQL query to present field (column) titles as: Date, Customer, Product, Price, Supplier, Country.

 

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

 

 

 

10) 10 points.  Write an SQL query to list supplier name, country and total sales for that supplier. 

 

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

 

 

Insert (copy and paste) all code from Items 7-10 within this textbox: