Creating "Neighborhood" Database and performing queries using MySql

This schema consists of a database named "Neighborhood" and various tables are created in this database. These tables helps in managing all sorts of data in a systematic way.

By performing different kinds of queries we can easily extract the required data.

But before performing any queries, we need a database and tables to perform queries on.

So first, we have to create the database "Neighborhood" :

mysql> CREATE DATABASE 'Neighborhood';

After creating the database, then we have to use that database.:

USE 'Neighborhood';

In the database we have to create different tables:

Creating table Cafe

CREATE TABLE `cafe` (
  `census_tract` bigint(11) default NULL,
  `county` varchar(9) default NULL,
  `dunkin_donuts` int(1) default NULL,
  `starbucks` int(1) default NULL
)

Creating table Houseprice

CREATE TABLE `houseprice` (
  `census_tract` bigint(11) NOT NULL default '0',
  `house_price_index` decimal(5,2) default NULL,
  `median_income` int(6) default NULL,
  `population` int(4) default NULL,
  PRIMARY KEY  (`census_tract`)
)

After creating all the tables, we have to insert records into these tables. After inserting records the table will look like this:

The Cafe table:

Cafe Table

The Houseprice table:

HousePrice Table

Performing Queries:

After creating the database as well as creating tables and inserting records into them, we have to perform queries on these tables:

Problem#1:

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

Problem#2:

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

Problem#3:

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

Problem#4:

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

Problem#5:

Write a SQL query to select all columns of the HousePrice table to inspect the table.

Problem#6:

After joining Cafe and HousePrice tables, write a 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)

Problem#7:

After joining Cafe and HousePrice tables, write a 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)

Problem#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 a 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) 

Problem#9:

After joining Cafe and HousePrice tables, write a 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)

Problem#10:

Based on your analysis with SQL, do you agree or disagree that Starbucks is the bellwether of rise in house prices of the neighborhood? How about Dunkin Donuts?

To get solution for the above queries you can contact us on contact@codersarts.com