Given the nature and structure the furniturestore data

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

Student Name: Jayme Sutton

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.

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.

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.

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.)

Case Use:

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

the transaction_date data also contains has the time and the query is expecting the time for January 12, 2019.

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

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.

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

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

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