Creating "2016 Presidential Election" Database and performing queries using MySql

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

Elections data are usually very large, so we can not get any particular data just by looking at the data. Hence to get the required data we need to perform queries.

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 "2016 Presidential Election" :

mysql> CREATE DATABASE '2016 Presidential Election';

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

USE '2016 Presidential Election';

In the database we have to create different tables:

Creating table Demographics

CREATE TABLE IF NOT EXISTS `Demographics` (
  `CountyID` int(5) DEFAULT NULL,
  `Name` varchar(20) DEFAULT NULL,
  `State` varchar(20) DEFAULT NULL,
  `Total_Population` int(7) DEFAULT NULL,
  `Percent_White` int(3) DEFAULT NULL,
  `Percent_Black` int(2) DEFAULT NULL,
  `Percent_Asian` int(2) DEFAULT NULL,
  `Percent_Hispanic` int(2) DEFAULT NULL,
  `Per_Capita_Income` int(5) DEFAULT NULL,
  `Median_Rent` int(4) DEFAULT NULL,
  `Median_Age` decimal(3,1) DEFAULT NULL
)

Creating table GoogleTrends

CREATE TABLE IF NOT EXISTS `GoogleTrends` (
  `State` varchar(20) DEFAULT NULL,
  `Google_Donald_Trump` decimal(4,2) DEFAULT NULL,
  `Google_Hillary_Clinton` decimal(4,2) DEFAULT NULL
)

Creating table Votes

CREATE TABLE IF NOT EXISTS `Votes` (
  `CountyID` int(5) DEFAULT NULL,
  `Democrats` int(7) DEFAULT NULL,
  `Republican` int(6) DEFAULT NULL,
  `Others` int(6) DEFAULT NULL
)

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

The Demographics table:

Demographics Table

The GoogleTrends table:

GoogleTrends Table

The Votes table:

Votes 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 calculate total population, average percentage of white, black, and Asian population, average income per capita, and average median rent, by states. Sort by total population in descending order.

Problem#2:

Write a SQL query to calculate total votes for Democrats, Republican, and Others by states in 2016 Presidential Election.

Problem#3:

Write a SQL query to calculate total population, average percentage of white, black, and Asian population, average income per capita, and average median rent in counties where the votes for Republican were more than those for Democrats.

Problem#4:

Write a SQL query to calculate total population, average percentage of white, black, and Asian population, average income per capita, and average median rent in counties where the votes for Democrats were more than those for Republican.

Problem#5:

Write a SQL query to count the number of counties by states where the votes for Republican were more than those for Democrats. Sort by the number of counties in descending order.

Problem#6:

Write a SQL query to select the county with the most votes for Republican in each state.

Problem#7:

Write a SQL query to calculate total votes for Democrats, Republican, and Others, along with average Google search volume for the candidates, by states.

Problem#8:

Write a SQL query to calculate total votes for Democrats, Republican, and Others in counties where people searched Donald Trump more than the average search volume on Google.

Problem#9:

Write a SQL query to calculate total votes for Democrats, Republican, and Others in counties where people searched Donald Trump less than the average search volume on Google.

Problem#10:

Based on your analyses with SQL, write your opinion on the role of data in politics.

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