Deadline 9/13/19
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. 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. 2) 10 points. Write an SQL query to list all products and the total sales 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) 15 points. In 2-3 sentences, given the nature and structure of the 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). 6a) 15 points. You have been asked to produce a report that shows all chairs bought on January 12, 2019. 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, and total sales for that supplier. INSERT a snip (image) of your QUERY RESULTS here: