IBM Data Science Chicago Assignment

CREATE TABLE chicagocensusdata (
  "community_area_number" DOUBLE,
  "community_area_name" VARCHAR,
  "percent_of_housing_crowded" DOUBLE,
  "percent_households_below_poverty" DOUBLE,
  "percent_aged_16_unemployed" DOUBLE,
  "percent_aged_25_without_high_school_diploma" DOUBLE,
  "percent_aged_under_18_or_over_64" DOUBLE,
  "per_capita_income" BIGINT,
  "hardship_index" DOUBLE
);

CREATE TABLE chicagocrimedata (
  "id" BIGINT,
  "case_number" VARCHAR,
  "date" TIMESTAMP,
  "block" VARCHAR,
  "iucr" VARCHAR,
  "primary_type" VARCHAR,
  "description" VARCHAR,
  "location_description" VARCHAR,
  "arrest" BOOLEAN,
  "domestic" BOOLEAN,
  "beat" BIGINT,
  "district" BIGINT,
  "ward" DOUBLE,
  "community_area_number" DOUBLE,
  "fbicode" VARCHAR,
  "x_coordinate" DOUBLE,
  "y_coordinate" DOUBLE,
  "year" BIGINT,
  "latitude" DOUBLE,
  "longitude" DOUBLE,
  "location" VARCHAR
);

CREATE TABLE chicagopublicschools (
  "school_id" BIGINT,
  "name_of_school" VARCHAR,
  "elementary_middle_or_high_school" VARCHAR,
  "street_address" VARCHAR,
  "city" VARCHAR,
  "state" VARCHAR,
  "zip_code" BIGINT,
  "phone_number" VARCHAR,
  "link" VARCHAR,
  "network_manager" VARCHAR,
  "collaborative_name" VARCHAR,
  "adequate_yearly_progress_made" VARCHAR,
  "track_schedule" VARCHAR,
  "cps_performance_policy_status" VARCHAR,
  "cps_performance_policy_level" VARCHAR,
  "healthy_school_certified" VARCHAR,
  "safety_icon" VARCHAR,
  "safety_score" DOUBLE,
  "family_involvement_icon" VARCHAR,
  "family_involvement_score" VARCHAR,
  "environment_icon" VARCHAR,
  "environment_score" DOUBLE,
  "instruction_icon" VARCHAR,
  "instruction_score" DOUBLE,
  "leaders_icon" VARCHAR,
  "leaders_score" VARCHAR,
  "teachers_icon" VARCHAR,
  "teachers_score" VARCHAR,
  "parent_engagement_icon" VARCHAR,
  "parent_engagement_score" VARCHAR,
  "parent_environment_icon" VARCHAR,
  "parent_environment_score" VARCHAR,
  "average_student_attendance" VARCHAR,
  "rate_of_misconducts_per_100_students" DOUBLE,
  "average_teacher_attendance" VARCHAR,
  "individualized_education_program_compliance_rate" VARCHAR,
  "pk_2_literacy" VARCHAR,
  "pk_2_math" VARCHAR,
  "gr3_5_grade_level_math" VARCHAR,
  "gr3_5_grade_level_read" VARCHAR,
  "gr3_5_keep_pace_read" VARCHAR,
  "gr3_5_keep_pace_math" VARCHAR,
  "gr6_8_grade_level_math" VARCHAR,
  "gr6_8_grade_level_read" VARCHAR,
  "gr6_8_keep_pace_math" VARCHAR,
  "gr6_8_keep_pace_read" VARCHAR,
  "gr_8_explore_math" VARCHAR,
  "gr_8_explore_read" VARCHAR,
  "isat_exceeding_math" DOUBLE,
  "isat_exceeding_reading" DOUBLE,
  "isat_value_add_math" DOUBLE,
  "isat_value_add_read" DOUBLE,
  "isat_value_add_color_math" VARCHAR,
  "isat_value_add_color_read" VARCHAR,
  "students_taking_algebra" VARCHAR,
  "students_passing_algebra" VARCHAR,
  "n_9th_grade_explore_2009" VARCHAR,
  "n_9th_grade_explore_2010" VARCHAR,
  "n_10th_grade_plan_2009" VARCHAR,
  "n_10th_grade_plan_2010" VARCHAR,
  "net_change_explore_and_plan" VARCHAR,
  "n_11th_grade_average_act_2011" VARCHAR,
  "net_change_plan_and_act" VARCHAR,
  "college_eligibility" VARCHAR,
  "graduation_rate" VARCHAR,
  "college_enrollment_rate" VARCHAR,
  "college_enrollment" BIGINT,
  "general_services_route" BIGINT,
  "freshman_on_track_rate" VARCHAR,
  "x_coordinate" DOUBLE,
  "y_coordinate" DOUBLE,
  "latitude" DOUBLE,
  "longitude" DOUBLE,
  "community_area_number" BIGINT,
  "community_area_name" VARCHAR,
  "ward" BIGINT,
  "police_district" BIGINT,
  "location" VARCHAR
);