Difference between revisions of "2015 UK General Election using mysql"

From SQLZOO
Jump to: navigation, search
(Created page with "This tutorial assumes you have access to a Linux account with mysql. Napier students can use the machine inf08104.napier.ac.uk ==Get to the linux prompt and download the csv==...")
(No difference)

Revision as of 17:29, 6 October 2016

This tutorial assumes you have access to a Linux account with mysql. Napier students can use the machine inf08104.napier.ac.uk

Get to the linux prompt and download the csv

wget http://researchbriefings.files.parliament.uk/documents/CBP-7186/hocl-ge2015-results-full.csv

Now move this file to somewhere where the MySQL process can see it (mysql doesn't have read access to your file space)

mv hocl-ge2015-results-full.csv /tmp

Putty1.png

Go into mysql and create a table for the results

mysql -u 40000036 -ptiger 40000036

The create table statement could be:

CREATE TABLE ge(
  ons_id VARCHAR(10),
  ons_region_id VARCHAR(10),
  constituency_name VARCHAR(50),
  county_name VARCHAR(50),
  region_name VARCHAR(50),
  country_name VARCHAR(50),
  constituency_type VARCHAR(10),
  party_name VARCHAR(50),
  party_abbreviation VARCHAR(50),
  firstname VARCHAR(50),
  surname VARCHAR(50),
  gender VARCHAR(6),
  sitting_mp VARCHAR(3),
  former_mp VARCHAR(3),
  votes INT,
  `share` FLOAT,
  `change` VARCHAR(20),
  PRIMARY KEY(ons_id,firstname,surname)
);

You can import the data using this line

LOAD DATA INFILE '/tmp/hocl-ge2015-results-full.csv' INTO TABLE ge COLUMNS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Now let's look at some data. How many female candidates were there?

select count(1) from ge where gender='female';

Who stood in Edinburgh South?

select surname,votes from ge where constituency_name='Edinburgh South';