<repo_name>clemiblac/ExporatoryDataAnalysis_with_SQL<file_sep>/Scripts/insert_data.sql
/* Important to pull data directory from C folder not in documents or other folders*/
COPY daly
FROM 'C:\sql_data\envdaly.csv'
DELIMITER ','
CSV HEADER;


COPY f_life_exp
FROM 'C:\sql_data\lifexfemale.csv'
DELIMITER ','
CSV HEADER;

COPY gdp
FROM 'C:\sql_data\gdpcapita.csv'
DELIMITER ','
CSV HEADER;


COPY malaria
FROM 'C:\sql_data\malaria.csv'
DELIMITER ','
CSV HEADER;

COPY transparency
FROM 'C:\sql_data\transparency.csv'
DELIMITER ','
CSV HEADER;


<file_sep>/Scripts/create_tables.sql
DROP TABLE IF EXISTS daly;
CREATE TABLE daly (
  Country VARCHAR(80) PRIMARY KEY NOT NULL,
  total_env_DALYs NUMERIC
);

DROP TABLE IF EXISTS gdp;
CREATE TABLE gdp (
  Country VARCHAR(80) PRIMARY KEY NOT NULL,
  gdp_capita NUMERIC
);

DROP TABLE IF EXISTS f_life_exp;
CREATE TABLE f_life_exp (
  Country VARCHAR(80) PRIMARY KEY NOT NULL,
  years NUMERIC
);

DROP TABLE IF EXISTS malaria;
CREATE TABLE malaria (
  Country VARCHAR(80) PRIMARY KEY NOT NULL,
  malaria_deaths NUMERIC
);

DROP TABLE IF EXISTS transparency;
CREATE TABLE transparency (
  Country VARCHAR(80) PRIMARY KEY NOT NULL,
  cpia NUMERIC
);

<file_sep>/README.md
# ExporatoryDataAnalysis_with_SQL

### Background

Six tables from the World Bank website and the WHO website were downloaded and cleaned  for data exploration purposes.
All data is 2012 data
 
1. Malaria deaths
2. disability adjusted life years attributed to the environment
3. female life expectancy at birth (Years)
4. GDP per capita (current US $)
5. CPIA transparency, accountability, and corruption in the public sector rating (1=low to 6=high) 


### Data Sources

The World Bank. (n.d.). The World Bank Data Bank World Development Indicators. Retrieved from The World Bank Website: https://databank.worldbank.org/source/world-development-indicators#

World Health Organization. (2016, 03 09). Burden of disease attributable to the environment. Retrieved from World Health Organization Website: https://apps.who.int/gho/data/view.main.ENVDALYSBYCOUNTRYv

World Health Organization. (2019, 02 19). Estimated number of malaria deaths (Malaria). Retrieved from World Health Organization Website: https://apps.who.int/gho/data/view.main.14119

* Female life expectancy at birth, GDP and CPIA transparency data were all retreived from The World Bank Data Bank






 <file_sep>/Scripts/data_exploration.sql
select * from daly FETCH FIRST 5 ROWS ONLY;
select * from f_life_exp FETCH FIRST 5 ROWS ONLY;
select * from gdp FETCH FIRST 5 ROWS ONLY;
select * from malaria FETCH FIRST 5 ROWS ONLY;
select * from transparency FETCH FIRST 5 ROWS ONLY;

--Joining all five tables to make a single dataset and creating a table from the result
select * from
(select daly.country,daly.total_env_dalys,f_life_exp.years,gdp.gdp_capita,malaria.malaria_deaths,transparency.cpia
from daly inner join f_life_exp on daly.country=f_life_exp.country
inner join gdp on daly.country=gdp.country
inner join malaria on daly.country=malaria.country
inner join transparency on daly.country=transparency.country) as new;

COPY 
(select daly.country,daly.total_env_dalys,f_life_exp.years,gdp.gdp_capita,malaria.malaria_deaths,transparency.cpia
from daly inner join f_life_exp on daly.country=f_life_exp.country
inner join gdp on daly.country=gdp.country
inner join malaria on daly.country=malaria.country
inner join transparency on daly.country=transparency.country)

TO 'C:\sql_data\new.csv' DELIMITER ',' CSV HEADER;
