According to new research by Harvard University researchers, Starbucks comes by its reputation as the bellwether of gentrification: When the coffee giant colonizes a neighborhood, home prices tend to jump. This research shows that one new Starbucks predicted an extra 0.54 percent rise in local home prices.

Source:

After a Starbucks opens in town, housing prices tend to rise, Harvard study finds

 (Links to an external site.)

 (CNBC, September 4 2018)

Starbucks, florists and other warning signs home prices are about to go up

 (Links to an external site.)

 (Washington Post, September 6 2018)

In Assignment 4, you will write SQL queries to infer the relationship between house prices in Boston and two national coffee chains: Starbucks and Dunkin Donuts.

 

Please write the SQL queries directly below. Before submitting the assignment, you are recommended to execute your SQL queries in online SQL editor. Please follow the instructions to use the online editor.

1. Click the following link and log in: https://mysql-ism218.codeanyapp.com/phpmyadmin (Links to an external site.)

Username: student

Password: ism218

2. Click the Neighborhood database on the left menu.

3. In SQL tab on the top, you can write SQL queries. To execute it, press Ctrl (Control) + Enter, or click GO on the bottom right. PLEASE NOTE that the results on the online editor will not be saved and you must submit your queries in Canvas, below.

Questions: The following shows the ERD for the Neighborhood database that consists of Cafe and HousePrice tables. In the Cafe table, "dunkin_donuts" and "starbucks" indicate the number of stores in the neighborhood (at census tract level).

1. Write SQL query to select all columns of the Cafe table to inspect the table.

question2: Write SQL query to calculate the total and average number of Starbucks and Dunkin Donuts in Boston.

 

question 3: Write  SQL query to calculate the total and average number of Starbucks and Dunkin Donuts in each county.

question4: Write  SQL query to select the neighborhood (census tract) with the most Starbucks stores in each county.

question 5: Write  SQL query to select all columns of the HousePrice table to inspect the table.

question 6: After joining Cafe and HousePrice tables, write  SQL query to calculate average house price index, average income, and average population of the neighborhoods where there is at least one Starbucks store.

How about those of the neighborhoods without Starbucks? (You can write  another query)

 

question 7: After joining Cafe and HousePrice tables, write  SQL query to calculate average house price index, average income, and average population of the neighborhoods with median income higher than the average of Boston where there is at least one Dunkin Donuts store. 

How about those of the neighborhoods without Dunkin Donuts? (You can write  another query)

 

question 8: One might argue that neighborhoods where Starbucks are located are relatively rich with higher house prices. If Starbucks stores are simply located in higher-income neighborhoods, rather than increasing house prices, we would expect to observe no significant relationship between Starbucks and house prices in higher-income neighborhoods.

 

After joining Cafe and HousePrice tables, write  SQL query to calculate average house price index, average income, and average population of the neighborhoods with median income higher than the average of Boston where there is at least one Starbucks store.

How about those of the neighborhoods without Starbucks? (You can write  another query)

question 9: After joining Cafe and HousePrice tables, write  SQL query to calculate average house price index, average income, and average population of the neighborhoods with median income higher than the average of Boston where there is at least one Dunkin Donuts store. 

How about those of the neighborhoods without Dunkin Donuts? (You can write  another query)
 
 

Transcribed Image Text:

phpMyAdmin Recent Favorites Server: localhost Databases SQL Status Export Import Settings information_schema Neighborhood General Settings Change password Server connection collation : utf8mb4_unicode_ci Appearance Settings Language : English O Theme: pmahomme Font size: 102% More settings < Variables Charsets phpMyAdmin Recent Favorites Server: localhost Databases SQL Status Export Import Settings information_schema Neighborhood General Settings Change password Server connection collation : utf8mb4_unicode_ci Appearance Settings Language : English O Theme: pmahomme Font size: 102% More settings < Variables Charsets