First of all, have a look at the SQL database creation and population code that accompanies this assignment. 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 a database schema image and a query results image

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 houstonto 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: