Crime Case Files 🔫⚔️

CREATE TABLE n_07_02_persons_arrested_by_sex_and_age_group_sll_2014 (
  "states_uts" VARCHAR,
  "crime_head" VARCHAR,
  "year" BIGINT,
  "n_18_and_above_and_below_30_years_male" BIGINT,
  "n_18_and_above_and_below_30_years_female" BIGINT,
  "n_18_and_above_and_below_30_years_total" BIGINT,
  "n_30_and_above_and_below_45_years_male" BIGINT,
  "n_30_and_above_and_below_45_years_female" BIGINT,
  "n_30_and_above_and_below_45_years_total" BIGINT,
  "n_45_and_above_and_below_60_years_male" BIGINT,
  "n_45_and_above_and_below_60_years_female" BIGINT,
  "n_45_and_above_and_below_60_years_total" BIGINT,
  "n_60_years_and_above_male" BIGINT,
  "n_60_years_and_above_female" BIGINT,
  "n_60_years_and_above_total" BIGINT,
  "total_male" BIGINT,
  "total_female" BIGINT,
  "total_persons_arrested_by_age_and_sex" BIGINT
);

CREATE TABLE n_08_01_juvenile_apprehended_state_ipc (
  "state_ut" VARCHAR,
  "year" BIGINT,
  "crime" VARCHAR,
  "boys_7_12_years" BIGINT,
  "girls_7_12_years" BIGINT,
  "boys_12_16_years" BIGINT,
  "girls_12_16_years" BIGINT,
  "boys_16_18_years" BIGINT,
  "girls_16_18_years" BIGINT,
  "total_for_boys_all_age_groups" BIGINT,
  "total_for_girls_all_age_groups" BIGINT,
  "grand_total" BIGINT
);

CREATE TABLE n_08_02_juvenile_apprehended_state_sll (
  "state_ut" VARCHAR,
  "year" BIGINT,
  "crime" VARCHAR,
  "boys_7_12_years" BIGINT,
  "girls_7_12_years" BIGINT,
  "boys_12_16_years" BIGINT,
  "girls_12_16_years" BIGINT,
  "boys_16_18_years" BIGINT,
  "girls_16_18_years" BIGINT,
  "total_for_boys_all_age_groups" BIGINT,
  "total_for_girls_all_age_groups" BIGINT,
  "grand_total" BIGINT
);

CREATE TABLE n_09_juveniles_arrested_and_their_disposal (
  "area_name" VARCHAR,
  "year" BIGINT,
  "juveniles_acquitted_or_otherwise_disposed_of" BIGINT,
  "juveniles_arrested" BIGINT,
  "juveniles_dealt_with_fine" BIGINT,
  "juveniles_released_on_probation_and_placed_under_the_c_3c4d70f1" BIGINT,
  "juveniles_released_on_probation_and_placed_under_the_c_ee64b8c3" BIGINT,
  "juveniles_sent_home_after_advice_or_admonition" BIGINT,
  "juveniles_sent_to_special_home" BIGINT,
  "juveniles_whose_cases_pending_disposal" BIGINT
);

CREATE TABLE n_10_property_stolen_and_recovered (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "cases_property_recovered" BIGINT,
  "cases_property_stolen" BIGINT,
  "value_of_property_recovered" BIGINT,
  "value_of_property_stolen" BIGINT
);

CREATE TABLE n_11_property_stolen_and_recovered_nature_of_property (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "cases_property_recovered" BIGINT,
  "cases_property_stolen" BIGINT,
  "value_of_property_recovered" BIGINT,
  "value_of_property_stolen" BIGINT
);

CREATE TABLE n_12_police_strength_actual_and_sanctioned (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "rank_all_ranks_total" BIGINT,
  "rank_asi_equivalent" BIGINT,
  "rank_aspdyspassttcommandant" BIGINT,
  "rank_below_hc_and_above_constables" BIGINT,
  "rank_constables" BIGINT,
  "rank_dgaddl_dg" BIGINT,
  "rank_dig" BIGINT,
  "rank_head_constables" BIGINT,
  "rank_igsplig" BIGINT,
  "rank_inspectors_equivalent" BIGINT,
  "rank_si_equivalent" BIGINT,
  "rank_sspspaddlspcommandant" BIGINT
);

CREATE TABLE n_13_police_killed_or_injured_on_duty (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "police_injured_by_criminals" BIGINT,
  "police_injured_by_riotous_mobs" BIGINT,
  "police_injured_in_accidents" BIGINT,
  "police_injured_in_dacoity_operationsother_raids" BIGINT,
  "police_injured_in_terroristsextremists_operations" BIGINT,
  "police_injured_on_border_duties" BIGINT,
  "police_injured_total_policemen" BIGINT,
  "police_killed_by_criminals" BIGINT,
  "police_killed_by_riotous_mobs" BIGINT,
  "police_killed_in_accidents" BIGINT,
  "police_killed_in_dacoity_operationsother_raids" BIGINT,
  "police_killed_in_terroristsextremists_operations" BIGINT,
  "police_killed_on_border_duties" BIGINT,
  "police_killed_total_policemen" BIGINT
);

CREATE TABLE n_14_age_profile_of_police_personnel_killed_on_duty (
  "area_name" VARCHAR,
  "year" BIGINT,
  "age_18_25_yrs" BIGINT,
  "age_25_35_yrs" BIGINT,
  "age_35_45_yrs" BIGINT,
  "age_45_55_yrs" BIGINT,
  "age_above_55_yrs" BIGINT,
  "age_total" BIGINT
);

CREATE TABLE n_15_police_natural_death_and_suicide (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "age_18_25_yrs" BIGINT,
  "age_25_35_yrs" BIGINT,
  "age_35_45_yrs" BIGINT,
  "age_45_55_yrs" BIGINT,
  "age_above_55_yrs" BIGINT,
  "age_total" BIGINT
);

CREATE TABLE n_16_casualties_under_police_firing_and_lathi_charge (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "civilians_injured" BIGINT,
  "civilians_killed" BIGINT,
  "no_of_firings" BIGINT,
  "policemen_injured" BIGINT,
  "policemen_killed" BIGINT
);

CREATE TABLE n_17_case_reported_and_value_of_property_taken_away_by_2c1593e4 (
  "state_ut" VARCHAR,
  "year" BIGINT,
  "place_of_occurrence" VARCHAR,
  "dacoity_section_395_398_ipc_number_of_cases_registered" BIGINT,
  "dacoity_section_395_398_ipc_value_of_property_stolen_in_rupees" BIGINT,
  "robbery_section_392_394_397_398_ipc_number_of_cases_registered" BIGINT,
  "robbery_section_392_394_397_398_ipc_value_of_property__c9c55ffc" BIGINT,
  "burglary_section_449_452_454_455_457_460_ipc_number_of_3c6dddb5" BIGINT,
  "burglary_section_449_452_454_455_457_460_ipc_value_of__5a2d9c23" BIGINT,
  "theft_section_379_382_ipc_number_of_cases_registered" BIGINT,
  "theft_section_379_382_ipc_value_of_property_stolen_in_rupees" BIGINT
);

CREATE TABLE n_17_case_reported_and_value_of_property_taken_away_by_a93c57ca (
  "state_ut" VARCHAR,
  "year" BIGINT,
  "place_of_occurrence" VARCHAR,
  "dacoity_section_395_398_ipc_number_of_cases_registered" BIGINT,
  "dacoity_section_395_398_ipc_value_of_property_stolen_in_rupees" BIGINT,
  "robbery_section_392_394_397_398_ipc_number_of_cases_registered" BIGINT,
  "robbery_section_392_394_397_398_ipc_value_of_property__c9c55ffc" BIGINT,
  "burglary_section_449_452_454_455_457_460_ipc_number_of_3c6dddb5" BIGINT,
  "burglary_section_449_452_454_455_457_460_ipc_value_of__5a2d9c23" BIGINT,
  "theft_section_379_382_ipc_number_of_cases_registered" BIGINT,
  "theft_section_379_382_ipc_value_of_property_stolen_in_rupees" BIGINT
);

CREATE TABLE n_17_crime_by_place_of_occurrence_2001_2012 (
  "state_ut" VARCHAR,
  "year" BIGINT,
  "residential_premises_dacoity" BIGINT,
  "residential_premises_robbery" BIGINT,
  "residential_premises_burglary" BIGINT,
  "residential_premises_theft" BIGINT,
  "highways_dacoity" BIGINT,
  "highways_robbery" BIGINT,
  "highways_burglary" BIGINT,
  "highways_theft" BIGINT,
  "river_and_sea_dacoity" BIGINT,
  "river_and_sea_robbery" BIGINT,
  "river_and_sea_burglary" BIGINT,
  "river_and_sea_theft" BIGINT,
  "railways_dacoity" BIGINT,
  "railways_robbery" BIGINT,
  "railways_burglary" BIGINT,
  "railways_theft" BIGINT,
  "banks_dacoity" BIGINT,
  "banks_robbery" BIGINT,
  "banks_burglary" BIGINT,
  "banks_theft" BIGINT,
  "commercial_establishments_dacoity" BIGINT,
  "commercial_establishments_robbery" BIGINT,
  "commercial_establishments_burglary" BIGINT,
  "commercial_establishments_theft" BIGINT,
  "other_places_dacoity" BIGINT,
  "other_places_robbery" BIGINT,
  "other_places_burglary" BIGINT,
  "other_places_theft" BIGINT,
  "total_dacoity" BIGINT,
  "total_robbery" BIGINT,
  "total_burglary" BIGINT,
  "total_theft" BIGINT
);

CREATE TABLE n_17_crime_by_place_of_occurrence_2013 (
  "state_ut" VARCHAR,
  "year" BIGINT,
  "residential_premises_dacoity" BIGINT,
  "residential_premises_robbery" BIGINT,
  "residential_premises_burglary" BIGINT,
  "residential_premises_theft" BIGINT,
  "highways_dacoity" BIGINT,
  "highways_robbery" BIGINT,
  "highways_burglary" BIGINT,
  "highways_theft" BIGINT,
  "river_and_sea_dacoity" BIGINT,
  "river_and_sea_robbery" BIGINT,
  "river_and_sea_burglary" BIGINT,
  "river_and_sea_theft" BIGINT,
  "railways_dacoity" BIGINT,
  "railways_robbery" BIGINT,
  "railways_burglary" BIGINT,
  "railways_theft" BIGINT,
  "banks_dacoity" BIGINT,
  "banks_robbery" BIGINT,
  "banks_burglary" BIGINT,
  "banks_theft" BIGINT,
  "commercial_establishments_dacoity" BIGINT,
  "commercial_establishments_robbery" BIGINT,
  "commercial_establishments_burglary" BIGINT,
  "commercial_establishments_theft" BIGINT,
  "other_places_dacoity" BIGINT,
  "other_places_robbery" BIGINT,
  "other_places_burglary" BIGINT,
  "other_places_theft" BIGINT,
  "total_dacoity" BIGINT,
  "total_robbery" BIGINT,
  "total_burglary" BIGINT,
  "total_theft" BIGINT
);

CREATE TABLE n_17_crime_by_place_of_occurrence_2014 (
  "states_uts" VARCHAR,
  "year" BIGINT,
  "residence_dacoity_cases_reported" BIGINT,
  "residence_dacoity_value_of_property_stolen" BIGINT,
  "residence_robbery_cases_reported" BIGINT,
  "residence_robbery_value_of_property_stolen" BIGINT,
  "residence_burglary_cases_reported" BIGINT,
  "residence_burglary_value_of_property_stolen" BIGINT,
  "residence_theft_cases_reported" BIGINT,
  "residence_theft_value_of_property_stolen" BIGINT,
  "highways_dacoity_cases_reported" BIGINT,
  "highways_dacoity_value_of_property_stolen" BIGINT,
  "highways_robbery_cases_reported" BIGINT,
  "highways_robbery_value_of_property_stolen" BIGINT,
  "highways_burglary_cases_reported" BIGINT,
  "highways_burglary_value_of_property_stolen" BIGINT,
  "highways_theft_cases_reported" BIGINT,
  "highways_theft_value_of_property_stolen" BIGINT,
  "riverorsea_dacoity_cases_reported" BIGINT,
  "riverorsea_dacoity_value_of_property_stolen" BIGINT,
  "riverorsea_robbery_cases_reported" BIGINT,
  "riverorsea_robbery_value_of_property_stolen" BIGINT,
  "riverorsea_burglary_cases_reported" BIGINT,
  "riverorsea_burglary_value_of_property_stolen" BIGINT,
  "riverorsea_theft_cases_reported" BIGINT,
  "riverorsea_theft_value_of_property_stolen" BIGINT,
  "railways_dacoity_cases_reported" BIGINT,
  "railways_dacoity_value_of_property_stolen" BIGINT,
  "railways_robbery_cases_reported" BIGINT,
  "railways_robbery_value_of_property_stolen" BIGINT,
  "railways_burglary_cases_reported" BIGINT,
  "railways_burglary_value_of_property_stolen" BIGINT,
  "railways_theft_cases_reported" BIGINT,
  "railways_theft_value_of_property_stolen" BIGINT,
  "religious_places_dacoity_cases_reported" BIGINT,
  "religious_places_dacoity_value_of_property_stolen" BIGINT,
  "religious_places_robbery_cases_reported" BIGINT,
  "religious_places_robbery_value_of_property_stolen" BIGINT,
  "religious_places_burglary_cases_reported" BIGINT,
  "religious_places_burglary_value_of_property_stolen" DOUBLE,
  "religious_places_theft_cases_reported" BIGINT,
  "religious_places_theft_value_of_property_stolen" BIGINT,
  "atm_dacoity_cases_reported" BIGINT,
  "atm_dacoity_value_of_property_stolen" BIGINT,
  "atm_robbery_cases_reported" BIGINT,
  "atm_robbery_value_of_property_stolen" BIGINT,
  "atm_burglary_cases_reported" BIGINT,
  "atm_burglary_value_of_property_stolen" BIGINT,
  "atm_theft_cases_reported" BIGINT,
  "atm_theft_value_of_property_stolen" BIGINT,
  "bank_dacoity_cases_reported" BIGINT,
  "bank_dacoity_value_of_property_stolen" BIGINT,
  "bank_robbery_cases_reported" BIGINT,
  "bank_robbery_value_of_property_stolen" BIGINT,
  "bank_burglary_cases_reported" BIGINT,
  "bank_burglary_value_of_property_stolen" BIGINT,
  "bank_theft_cases_reported" BIGINT,
  "bank_theft_value_of_property_stolen" BIGINT,
  "commest_dacoity_cases_reported" BIGINT,
  "commest_dacoity_value_of_property_stolen" BIGINT,
  "commest_robbery_cases_reported" BIGINT,
  "commest_robbery_value_of_property_stolen" BIGINT,
  "commest_burglary_cases_reported" BIGINT,
  "commest_burglary_value_of_property_stolen" BIGINT,
  "commest_theft_cases_reported" BIGINT,
  "commest_theft_value_of_property_stolen" BIGINT,
  "otherplaces_dacoity_cases_reported" BIGINT,
  "otherplaces_dacoity_value_of_property_stolen" BIGINT,
  "otherplaces_robbery_cases_reported" BIGINT,
  "otherplaces_robbery_value_of_property_stolen" BIGINT,
  "otherplaces_burglary_cases_reported" BIGINT,
  "otherplaces_burglary_value_of_property_stolen" BIGINT,
  "otherplaces_theft_cases_reported" BIGINT,
  "otherplaces_theft_value_of_property_stolen" BIGINT,
  "total_dacoity_cases_reported" BIGINT,
  "total_dacoity_value_of_property_stolen" BIGINT,
  "total_robbery_cases_reported" BIGINT,
  "total_robbery_value_of_property_stolen" BIGINT,
  "total_burglary_cases_reported" BIGINT,
  "total_burglary_value_of_property_stolen" DOUBLE,
  "total_theft_cases_reported" BIGINT,
  "total_theft_value_of_property_stolen" BIGINT
);

CREATE TABLE n_18_01_juveniles_arrested_education (
  "area_name" VARCHAR,
  "year" BIGINT,
  "sub_group_name" VARCHAR,
  "education_above_primary_but_below_matric_or_higher_secondary" BIGINT,
  "education_illiterate" BIGINT,
  "education_matric_or_higher_secondary_above" BIGINT,
  "education_total" BIGINT,
  "education_upto_primary" BIGINT
);

CREATE TABLE n_18_02_juveniles_arrested_economic_setup (
  "area_name" VARCHAR,
  "year" BIGINT,
  "sub_group_name" VARCHAR,
  "economic_set_up_annual_income_250001_to_50000" BIGINT,
  "economic_set_up_annual_income_upto_rs_25000" BIGINT,
  "economic_set_up_middle_income_from_100001_to_200000" BIGINT,
  "economic_set_up_middle_income_from_50001_to_100000" BIGINT,
  "economic_set_up_total" BIGINT,
  "economic_set_up_upper_income_above_rs_300000" BIGINT,
  "economic_set_up_upper_middle_income_from_200001_to_300000" BIGINT
);

CREATE TABLE n_18_03_juveniles_arrested_family_background (
  "area_name" VARCHAR,
  "year" BIGINT,
  "sub_group_name" VARCHAR,
  "family_back_ground_homeless" BIGINT,
  "family_back_ground_living_with_guardian" BIGINT,
  "family_back_ground_living_with_parents" BIGINT,
  "family_back_ground_total" BIGINT
);

CREATE TABLE n_18_04_juveniles_arrested_recidivism (
  "area_name" VARCHAR,
  "year" BIGINT,
  "sub_group_name" VARCHAR,
  "recidivism_new_delinquent" BIGINT,
  "recidivism_old_delinquent" BIGINT,
  "recidivism_total" BIGINT
);

CREATE TABLE n_19_motive_or_cause_of_murder_and_culpable_homicide_n_1970921c (
  "area_name" VARCHAR,
  "year" BIGINT,
  "chnamurder_cause_by_terroristextremist" BIGINT,
  "chnamurder_cause_casteism" BIGINT,
  "chnamurder_cause_class_conflict" BIGINT,
  "chnamurder_cause_communalism" BIGINT,
  "chnamurder_cause_dowry" BIGINT,
  "chnamurder_cause_for_political_reason" BIGINT,
  "chnamurder_cause_gain" BIGINT,
  "chnamurder_cause_love_affairssexual_relations" BIGINT,
  "chnamurder_cause_lunacy" BIGINT,
  "chnamurder_cause_other_causes_or_motives" BIGINT,
  "chnamurder_cause_personal_vendetta_or_enmity" BIGINT,
  "chnamurder_cause_property_dispute" BIGINT,
  "chnamurder_cause_total" BIGINT,
  "chnamurder_cause_witchcraft" BIGINT,
  "murder_cause_by_terroristextremist" BIGINT,
  "murder_cause_casteism" BIGINT,
  "murder_cause_class_conflict" BIGINT,
  "murder_cause_communalism" BIGINT,
  "murder_cause_dowry" BIGINT,
  "murder_cause_for_political_reason" BIGINT,
  "murder_cause_gain" BIGINT,
  "murder_cause_love_affairssexual_relations" BIGINT,
  "murder_cause_lunacy" BIGINT,
  "murder_cause_other_causes_or_motives" BIGINT,
  "murder_cause_personal_vendetta_or_enmity" BIGINT,
  "murder_cause_property_dispute" BIGINT,
  "murder_cause_total" BIGINT,
  "murder_cause_witchcraft" BIGINT
);

CREATE TABLE n_20_victims_of_rape (
  "area_name" VARCHAR,
  "year" BIGINT,
  "subgroup" VARCHAR,
  "rape_cases_reported" BIGINT,
  "victims_above_50_yrs" BIGINT,
  "victims_between_10_14_yrs" BIGINT,
  "victims_between_14_18_yrs" BIGINT,
  "victims_between_18_30_yrs" BIGINT,
  "victims_between_30_50_yrs" BIGINT,
  "victims_of_rape_total" BIGINT,
  "victims_upto_10_yrs" BIGINT
);

CREATE TABLE n_21_offenders_known_to_the_victim (
  "area_name" VARCHAR,
  "year" BIGINT,
  "no_of_cases_in_which_offenders_were_known_to_the_victims" BIGINT,
  "no_of_cases_in_which_offenders_were_neighbours" BIGINT,
  "no_of_cases_in_which_offenders_were_other_known_persons" BIGINT,
  "no_of_cases_in_which_offenders_were_parentsclose_family_members" BIGINT,
  "no_of_cases_in_which_offenders_were_relatives" BIGINT
);

CREATE TABLE n_22_persons_arrested_under_recidivism (
  "area_name" VARCHAR,
  "year" BIGINT,
  "offenders_arrested" BIGINT,
  "offenders_arrested_for_the_first_time" BIGINT,
  "offenders_conviction_in_the_past_once" BIGINT,
  "offenders_conviction_in_the_past_three_times_or_more" BIGINT,
  "offenders_conviction_in_the_past_twice" BIGINT
);

CREATE TABLE n_23_anti_corruprion_cases (
  "area_name" VARCHAR,
  "year" BIGINT,
  "ac01_no_of_cases_pending_investigation_from_previous_year" DOUBLE,
  "ac02_no_of_cases_registered_during_the_year" DOUBLE,
  "ac03_total_no_of_cases_for_investigation_during_the_year" DOUBLE,
  "ac04_no_of_cases_investigated_during_the_year" DOUBLE,
  "ac05_no_of_cases_not_investigatedor_in_which_investiga_d6413c7a" DOUBLE,
  "ac06_no_of_cases_transferred_to_local_police_during_the_year" DOUBLE,
  "ac07_no_of_cases_declared_false_mistake_of_fact_or_of__2d1a2451" DOUBLE,
  "ac08_no_of_cases_in_which_charge_sheets_were_laid_duri_6294269c" DOUBLE,
  "ac09_no_of_cases_pending_departmental_sanction_for_pro_d670f501" DOUBLE,
  "ac10_no_of_cases_sent_up_for_trial_and_also_reported_f_41635e49" DOUBLE,
  "ac11_no_of_cases_reported_for_regular_departmental_act_50b4a53c" DOUBLE,
  "ac12_no_of_cases_reported_for_suitable_action_during_the_year" DOUBLE,
  "ac13_no_of_cases_in_which_charge_sheets_were_not_laid__193a1bbf" DOUBLE,
  "ac14_no_of_cases_pending_investigation_at_the_end_of_the_year" DOUBLE,
  "ac15_no_of_cases_resulted_in_recoveries_or_seizures_du_0c5c6e2e" DOUBLE,
  "ac16_value_of_property_recoveredseized_during_the_year_in_rs" DOUBLE,
  "ac17_percentage_of_cases_charge_sheeted_to_total_cases_f7949f22" DOUBLE,
  "ac18_no_of_cases_pending_trial_from_the_previous_year" DOUBLE,
  "ac19_no_of_cases_sent_up_for_trial_during_the_year" DOUBLE,
  "ac20_total_no_of_cases_for_trial_during_the_year" DOUBLE,
  "ac21_no_of_cases_withdrawn_or_other_wise_disposed_off__8ff51e05" DOUBLE,
  "ac22_no_of_cases_in_which_trials_were_completed_during_the_year" DOUBLE,
  "ac23_no_of_cases_convicted_during_the_year" DOUBLE,
  "ac24_no_of_cases_acquitted_or_discharged_during_the_year" DOUBLE,
  "ac25_no_of_cases_pending_trial_at_the_end_of_the_year" DOUBLE,
  "ac26_percentage_of_cases_convicted_to_cases_in_which_t_f8f82422" DOUBLE,
  "ac27_total_amount_of_fine_imposed_during_the_year_in_rs" DOUBLE
);