drop schema allinspections_updated;

Create schema allinspections_updated;

use allinspections_updated;


CREATE TABLE `business` (
  `Business_id` varchar(40) NOT NULL,
  `Name` varchar(100) DEFAULT NULL,
  `Address` varchar(40) DEFAULT NULL,
  `City` varchar(15) DEFAULT NULL,
  `State` varchar(4) DEFAULT NULL,
  `Postal_Code` varchar(10) DEFAULT NULL,
  `Latitude` varchar(10) DEFAULT NULL,
  `Longitude` varchar(10) DEFAULT NULL,
  `Phone_Number` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`Business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop table business;

LOAD DATA LOCAL INFILE 'C:\\businesses.csv'
INTO TABLE business
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY'\n'
IGNORE 1 LINES;

select count(*)
from business;

select *
from business;

CREATE TABLE`inspections` (
  `Business_id` varchar(40) NOT NULL,
  `score` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL);
  
  
  ####KEY `inspections_FK_idx` (`business_id`);

  CONSTRAINT `inspections_FK` FOREIGN KEY (`business_id`) REFERENCES `business` (`Business_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#### note that for this load statement, we used the "set date" function to change the date to the format that MySQL prefers: year, month, day,
##### which will allow us to join it to the date field in "violations" table once we begin linking the tables

LOAD DATA LOCAL INFILE 'C:\\inspections.csv'
INTO TABLE inspections
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY'\n'
IGNORE 1 LINES
(business_id, score, @date)
set date = str_to_date(@date, '%Y%m%d');

####update inspections set date = str_to_date(left(date, 8), '%Y%m%d');

create index busines_id_insp on inspections(business_id);

drop table inspections;



select count(*)
from inspections;

select *
from inspections
order by date desc;

CREATE TABLE`violations` (
  `Business_id` varchar(40) NOT NULL,
  `Date` date DEFAULT NULL,
  `Code` varchar(5) DEFAULT NULL,
  `Description` varchar(200) DEFAULT NULL);
  
  ####KEY `violations_fk_idx` (`Business_id`);
  #####FOREIGN KEY (`Business_id`) REFERENCES `business` (`Business_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
 ENGINE=InnoDB DEFAULT CHARSET=utf8;

drop table violations;

LOAD DATA LOCAL INFILE 'C:\\violations.csv'
INTO TABLE violations
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY'\r\n'
IGNORE 1 LINES;

create index violation_ on VIOLATIONS (BUSINESS_id);
create index violation_date on violations (Date);

DROP TABLE VIOLATIONS;

select count(*)
from violations;

select *
from violations;

#### the legend contains the description for the scores, which comes in handy when using the "where" statement to filter our queries

CREATE TABLE`legend` (
  `Minimum` int(11) DEFAULT NULL,
  `Maximum` int(11) DEFAULT NULL,
  `Legend` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select *
from legend;

#### this query does two things, creates a master table from the tables  that we have joined using "where" statement, which is explained 
#### in The Data Journalists' chapter, beginning on page 100

#
SELECT *
FROM BUSINESS, violations, inspections
where BUSINESS.Business_id = violations.Business_id
and business.Business_id=inspections.business_id;
#


SELECT distinct*
FROM BUSINESS, violations, inspections
where BUSINESS.Business_id = violations.Business_id
and business.Business_id=inspections.business_id
and violations.date=inspections.date
and score = '0' and business.business_id = '2667130B-DF58-4D85-B054-A82E2FA69D52';

select *
from inspections_violations
where business_id='2667130B-DF58-4D85-B054-A82E2FA69D52';


create table Inspections_Violations as 
select distinct b.*, I.score, v.date, v.Code,v.Description
from business as b, inspections as I, violations as v
where b.Business_id=I.Business_id
and b.Business_id=v.Business_id
and I.date=v.date;


select count(*)
from inspections_violations;

select distinct *
from inspections_violations;

select count(Business_id) number, year(date) year
from inspections_violations
group by year
order by count(Business_id) desc;

select count(Business_id) number, year(date) year
from inspections_violations
where name like '%retirement%'
group by year
order by count(Business_id) desc;

select distinct*
from inspections_violations
where business_id ='2667130B-DF58-4D85-B054-A82E2FA69D52';

select *
from inspections_violations
where name like '%retirement%';

select * 
from inspections_violations
where Name like '%nurse%';

select * 
from iinspections_violations
where Name like '%home%';

select * 
from inspections_violations
where Name like '%funeral%';

select *
from inspections_violations
where name like '%hair%';

select count(Business_id), name
from inspections_violations
group by name
order by count(Business_id) desc;

select count(Business_id), code
from inspections_violations
group by code
order by count(Business_id) desc;

#####
