Crime Case Files 🔫⚔️

CREATE TABLE n_24_anti_corruption_arrests (
  "area_name" VARCHAR,
  "year" BIGINT,
  "aca01_no_of_persons_in_custody_or_on_bail_during_the_s_e3f4ea76" DOUBLE  -- ACA01 No Of Persons In Custody Or On Bail During The Stage Of Investigation At The Beginning Of The Year,
  "aca02_no_of_persons_arrested_during_the_year" DOUBLE,
  "aca04_no_of_persons_in_custody_or_on_bail_during_the_s_0417847e" DOUBLE  -- ACA04 No Of Persons In Custody Or On Bail During The Stage Of Investigation At The End Of The Year,
  "aca05_no_of_persons_in_whose_cases_charge_sheets_were__db35cf2b" DOUBLE  -- ACA05 No Of Persons In Whose Cases Charge Sheets Were Laid During The Year,
  "aca06_no_of_persons_under_trial_at_the_beginning_of_the_year" DOUBLE,
  "aca07_total_no_of_persons_under_trial_during_the_year" DOUBLE,
  "aca08_no_of_persons_whose_cases_were_withdrawn_or_othe_d3d4a652" DOUBLE  -- ACA08 No Of Persons Whose Cases Were Withdrawn Or Otherwise Disposed Off During The Year,
  "aca09_no_of_persons_in_custody_or_on_bail_during_the_s_eabfd263" DOUBLE  -- ACA09 No Of Persons In Custody Or On Bail During The Stage Of Trial At The End Of The Year,
  "aca10_no_of_persons_in_whose_cases_trials_were_complet_0ff20b61" DOUBLE  -- ACA10 No Of Persons In Whose Cases Trials Were Completed During The Year,
  "aca11_no_of_persons_convicted_during_the_year" DOUBLE,
  "aca12_no_of_persons_acquitted_during_the_year" DOUBLE,
  "aca13_percentage_of_persons_convicted_to_total_persons_a4175b8f" DOUBLE  -- ACA13 Percentage Of Persons Convicted To Total Persons In Whose Cases Trials Were Completed During The Year,
  "aca14_no_of_persons_involved_in_the_cases_reported_for_67c17bba" DOUBLE  -- ACA14 No Of Persons Involved In The Cases Reported For Regular Departmental Action During The Year,
  "aca15_no_of_persons_involved_in_the_cases_reported_for_421d16ab" DOUBLE  -- ACA15 No Of Persons Involved In The Cases Reported For Suitable Action During The Year,
  "aca16_no_of_persons_punished_departmentally_during_the_year" DOUBLE  -- ACA16 No Of Persons Punished Departmentally During The Year:,
  "aca161_no_of_persons_dismissed_from_service_during_the_year" DOUBLE,
  "aca162_no_of_persons_removed_from_service_during_the_year" DOUBLE,
  "aca163_no_of_persons_awarded_other_major_punishments_d_5ce60039" DOUBLE  -- ACA163 No Of Persons Awarded Other Major Punishments During The Year,
  "aca164_no_of_persons_awarded_minor_punishments_during_the_year" DOUBLE,
  "aca171_no_of_group_a_officers_out_of_above" DOUBLE  -- ACA171 No Of Group `A' Officers Out Of Above,
  "aca172_no_of_group_b_officers_out_of_above" DOUBLE  -- ACA172 No Of Group `B' Officers Out Of Above,
  "aca19_no_of_private_persons_involved_during_the_year" DOUBLE
);

CREATE TABLE n_25_complaints_against_police (
  "area_name" VARCHAR,
  "year" BIGINT,
  "sub_group" VARCHAR,
  "cpa_cases_registered" BIGINT  -- CPA - Cases Registered,
  "cpa_cases_reported_for_dept_action" BIGINT  -- CPA - Cases Reported For Dept. Action,
  "cpa_complaints_cases_declared_false_unsubstantiated" BIGINT  -- CPA - Complaints/Cases Declared False/Unsubstantiated,
  "cpa_complaints_received_alleged" BIGINT  -- CPA - Complaints Received/Alleged,
  "cpa_no_of_departmental_enquiries" BIGINT  -- CPA - No Of Departmental Enquiries,
  "cpa_no_of_magisterial_enquiries" BIGINT  -- CPA - No Of Magisterial Enquiries,
  "cpa_cases_sent_for_trials_charge_sheeted" BIGINT  -- CPA- Cases Sent For Trials/Charge-sheeted,
  "cpa_no_of_judicial_enquiries" BIGINT  -- CPA- No Of Judicial Enquiries,
  "cpb_police_personnel_acquitted" BIGINT  -- CPB - Police Personnel Acquitted,
  "cpb_police_personnel_convicted" BIGINT  -- CPB - Police Personnel Convicted,
  "cpb_police_personnel_sent_up_for_trial" BIGINT  -- CPB - Police Personnel Sent Up For Trial,
  "cpb_police_personnel_trial_completed" BIGINT  -- CPB - Police Personnel Trial Completed,
  "cpb_police_personnel_cases_withdrawn_or_otherwise_disposed_of" BIGINT  -- CPB- Police Personnel Cases Withdrawn Or Otherwise Disposed Of,
  "cpc_police_personnel_cases_trial_completed" BIGINT  -- CPC - Police Personnel Cases Trial Completed,
  "cpc_police_personnel_cases_withdrawn_or_otherwise_disposed_of" BIGINT  -- CPC - Police Personnel Cases Withdrawn Or Otherwise Disposed Of,
  "cpc_police_personnel_disciplinary_action_initiated" BIGINT  -- CPC - Police Personnel Disciplinary Action Initiated,
  "cpc_police_personnel_dismissal_removal_from_service" BIGINT  -- CPC - Police Personnel Dismissal/Removal From Service,
  "cpc_police_personnel_major_punishment_awarded" BIGINT  -- CPC - Police Personnel Major Punishment Awarded,
  "cpc_police_personnel_minor_punishment_awarded" BIGINT  -- CPC - Police Personnel Minor Punishment Awarded
);

CREATE TABLE n_27_nature_of_complaints_received_by_police (
  "area_name" VARCHAR,
  "year" BIGINT,
  "pc1_oral_complaints" DOUBLE,
  "pc2_written_complaints" BIGINT,
  "pc3_distress_call_over_phoneno_100_etc" DOUBLE,
  "pc4_complaints_initiated_sue_motto_by_police" DOUBLE,
  "pc5_total_complaints_sum_of_1_4_above" BIGINT,
  "pc6_total_complaints_as_recorded_in_gd" DOUBLE,
  "pc7_ipc_cases_registered" BIGINT,
  "pc8_sll_cases_registered" BIGINT
);

CREATE TABLE n_28_trial_of_violent_crimes_by_courts (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "trial_of_violent_crimes_by_courts_by_confession" DOUBLE,
  "trial_of_violent_crimes_by_courts_by_trial" DOUBLE,
  "trial_of_violent_crimes_by_courts_total" DOUBLE
);

CREATE TABLE n_29_period_of_trials_by_courts (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "pt_1_3_years" DOUBLE,
  "pt_3_5_years" DOUBLE,
  "pt_5_10_years" DOUBLE,
  "pt_6_12_months" DOUBLE,
  "pt_less_than_6_months" DOUBLE,
  "pt_over_10_years" DOUBLE,
  "pt_total" DOUBLE
);

CREATE TABLE n_30_auto_theft (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "auto_theft_coordinated_traced" DOUBLE,
  "auto_theft_recovered" DOUBLE,
  "auto_theft_stolen" BIGINT
);

CREATE TABLE n_31_serious_fraud (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "loss_of_property_1_10_crores" DOUBLE,
  "loss_of_property_10_25_crores" DOUBLE,
  "loss_of_property_25_50_crores" DOUBLE,
  "loss_of_property_50_100_crores" DOUBLE,
  "loss_of_property_above_100_crores" DOUBLE
);

CREATE TABLE n_32_murder_victim_age_sex (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "victims_above_50_yrs" DOUBLE,
  "victims_total" BIGINT,
  "victims_upto_10_15_yrs" DOUBLE,
  "victims_upto_10_yrs" DOUBLE,
  "victims_upto_15_18_yrs" DOUBLE,
  "victims_upto_18_30_yrs" DOUBLE,
  "victims_upto_30_50_yrs" DOUBLE
);

CREATE TABLE n_33_ch_not_murder_victim_age_sex (
  "area_name" VARCHAR,
  "year" BIGINT,
  "sub_group_name" VARCHAR,
  "victims_above_50_yrs" DOUBLE,
  "victims_total" DOUBLE,
  "victims_upto_10_15_yrs" DOUBLE,
  "victims_upto_10_yrs" DOUBLE,
  "victims_upto_15_18_yrs" DOUBLE,
  "victims_upto_18_30_yrs" DOUBLE,
  "victims_upto_30_50_yrs" DOUBLE
);

CREATE TABLE n_34_use_of_fire_arms_in_murder_cases (
  "area_name" VARCHAR,
  "year" BIGINT,
  "victims_of_murder_by_fire_arms" BIGINT,
  "victims_of_murder_by_licensed_arms" DOUBLE,
  "victims_of_murder_by_un_licensedimprovisedcrudecountry_538b039e" DOUBLE  -- Victims Of Murder By Un LicensedImprovisedCrudeCountry Made Arms Etc
);

CREATE TABLE n_35_human_rights_violation_by_police (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "cases_registered_under_human_rights_violations" DOUBLE,
  "policemen_chargesheeted" DOUBLE,
  "policemen_convicted" DOUBLE
);

CREATE TABLE n_36_police_housing (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "ph_houses_provided_by_department" DOUBLE,
  "ph_houses_provided_on_leaserentgpra" DOUBLE,
  "ph_sanctioned_strength" BIGINT
);

CREATE TABLE n_37_home_guards_and_auxilliary_force (
  "area_name" VARCHAR,
  "year" BIGINT,
  "hg_lower_subordinates_actual_strength" DOUBLE,
  "hg_lower_subordinates_sanctioned_strength" DOUBLE,
  "hg_officers_actual_strength" DOUBLE,
  "hg_officers_sanctioned_strength" DOUBLE,
  "hg_upper_subordinates_actual_strength" DOUBLE,
  "hg_upper_subordinates_sanctioned_strength" DOUBLE
);

CREATE TABLE n_38_unidentified_dead_bodies_recovered_and_inquest_conducted (
  "area_name" VARCHAR,
  "year" BIGINT,
  "unidentified_dead_bodies_recovered_inquest_conducted" BIGINT
);

CREATE TABLE n_39_specific_purpose_of_kidnapping_and_abduction (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "k_a_cases_reported" DOUBLE,
  "k_a_female_10_15_years" DOUBLE,
  "k_a_female_15_18_years" DOUBLE,
  "k_a_female_18_30_years" DOUBLE,
  "k_a_female_30_50_years" DOUBLE,
  "k_a_female_above_50_years" DOUBLE,
  "k_a_female_total" DOUBLE,
  "k_a_female_upto_10_years" DOUBLE,
  "k_a_grand_total" DOUBLE,
  "k_a_male_10_15_years" DOUBLE,
  "k_a_male_15_18_years" DOUBLE,
  "k_a_male_18_30_years" DOUBLE,
  "k_a_male_30_50_years" DOUBLE,
  "k_a_male_above_50_years" DOUBLE,
  "k_a_male_total" DOUBLE,
  "k_a_male_upto_10_years" DOUBLE
);

CREATE TABLE n_40_01_custodial_death_person_remanded (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "cd_deaths_reported" DOUBLE,
  "cd_no_of_autopsy_conducted" DOUBLE,
  "cd_no_of_cases_registered_in_connection_with_deaths" DOUBLE,
  "cd_no_of_judicial_enquiry_orderedconducted" DOUBLE,
  "cd_no_of_magisterial_enquiry_orderedconducted" DOUBLE,
  "cd_no_of_policemen_charge_sheeted" DOUBLE,
  "cd_no_of_policemen_convicted" DOUBLE
);

CREATE TABLE n_40_02_custodial_death_person_not_remanded (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "cd_deaths_reported" BIGINT,
  "cd_no_of_autopsy_conducted" DOUBLE,
  "cd_no_of_cases_registered_in_connection_with_deaths" DOUBLE,
  "cd_no_of_judicial_enquiry_orderedconducted" DOUBLE,
  "cd_no_of_magisterial_enquiry_orderedconducted" DOUBLE,
  "cd_no_of_policemen_charge_sheeted" DOUBLE,
  "cd_no_of_policemen_convicted" DOUBLE
);

CREATE TABLE n_40_03_custodial_death_during_production (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "cd_deaths_reported" BIGINT,
  "cd_no_of_autopsy_conducted" DOUBLE,
  "cd_no_of_cases_registered_in_connection_with_deaths" DOUBLE,
  "cd_no_of_judicial_enquiry_orderedconducted" DOUBLE,
  "cd_no_of_magisterial_enquiry_orderedconducted" DOUBLE,
  "cd_no_of_policemen_charge_sheeted" DOUBLE,
  "cd_no_of_policemen_convicted" DOUBLE
);

CREATE TABLE n_40_04_custodial_death_during_hospitalization_or_treatment (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "cd_hospitalisation_treatment" BIGINT
);

CREATE TABLE n_40_05_custodial_death_others (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "cd_accidents" DOUBLE,
  "cd_by_mob_attackriots" DOUBLE,
  "cd_by_other_criminals" DOUBLE,
  "cd_by_suicide" DOUBLE,
  "cd_illnessnatural_death" DOUBLE,
  "cd_while_escaping_from_custody" DOUBLE
);

CREATE TABLE n_41_escapes_from_police_custody (
  "area_name" VARCHAR,
  "year" BIGINT,
  "epc_cases_cases_acquitted" DOUBLE,
  "epc_cases_cases_convicted" DOUBLE,
  "epc_cases_cases_pending_for_trial" DOUBLE,
  "epc_cases_registered" DOUBLE,
  "epc_cases_trial_completed" DOUBLE,
  "epc_escapees_re_arrested_from_lockup" DOUBLE,
  "epc_escapees_re_arrested_from_others" DOUBLE,
  "epc_fr_submitted" DOUBLE,
  "epc_persons_awarded_more_than_3_years_imprisonment" DOUBLE,
  "epc_persons_awarded_upto_3_years_imprisonment" DOUBLE,
  "epc_persons_cases_acquitted" DOUBLE,
  "epc_persons_cases_convicted" DOUBLE,
  "epc_persons_cases_pending_for_trial" DOUBLE,
  "epc_persons_chargesheeted_for_escape" DOUBLE,
  "epc_persons_escaped" DOUBLE,
  "epc_persons_escaped_from_lockup" DOUBLE,
  "epc_persons_escaped_outside_the_lockup" DOUBLE,
  "epc_persons_escaped_total" DOUBLE,
  "epc_persons_trial_completed" DOUBLE
);

CREATE TABLE n_42_cases_under_crime_against_women (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "cases_acquitted_or_discharged" BIGINT,
  "cases_charge_sheets_were_not_laid_but_final_report_submitted" BIGINT,
  "cases_chargesheeted" BIGINT,
  "cases_compounded_or_withdrawn" BIGINT,
  "cases_convicted" BIGINT,
  "cases_declared_false_on_account_of_mistake_of_fact_or_of_law" BIGINT,
  "cases_investigated_chargesheets_fr_submitted" BIGINT,
  "cases_not_investigated_or_in_which_investigation_was_refused" BIGINT,
  "cases_pending_investigation_at_year_end" BIGINT,
  "cases_pending_investigation_from_previous_year" BIGINT,
  "cases_pending_trial_at_year_end" BIGINT,
  "cases_pending_trial_from_the_previous_year" BIGINT,
  "cases_reported" BIGINT,
  "cases_sent_for_trial" BIGINT,
  "cases_trials_completed" BIGINT,
  "cases_withdrawn_by_the_govt" BIGINT,
  "cases_withdrawn_by_the_govt_during_investigation" BIGINT,
  "total_cases_for_trial" BIGINT
);

CREATE TABLE n_42_district_wise_crimes_committed_against_women_2001_2012 (
  "state_ut" VARCHAR,
  "district" VARCHAR,
  "year" BIGINT,
  "rape" BIGINT,
  "kidnapping_and_abduction" BIGINT,
  "dowry_deaths" BIGINT,
  "assault_on_women_with_intent_to_outrage_her_modesty" BIGINT,
  "insult_to_modesty_of_women" BIGINT,
  "cruelty_by_husband_or_his_relatives" BIGINT,
  "importation_of_girls" BIGINT
);

CREATE TABLE n_42_district_wise_crimes_committed_against_women_2013 (
  "state_ut" VARCHAR,
  "district" VARCHAR,
  "year" BIGINT,
  "rape" BIGINT,
  "kidnapping_and_abduction" BIGINT,
  "dowry_deaths" BIGINT,
  "assault_on_women_with_intent_to_outrage_her_modesty" BIGINT,
  "insult_to_modesty_of_women" BIGINT,
  "cruelty_by_husband_or_his_relatives" BIGINT,
  "importation_of_girls" BIGINT
);

CREATE TABLE n_42_district_wise_crimes_committed_against_women_2014 (
  "states_uts" VARCHAR,
  "district" VARCHAR,
  "year" BIGINT,
  "rape" BIGINT,
  "custodial_rape" BIGINT,
  "custodial_gang_rape" BIGINT,
  "custodial_other_rape" BIGINT,
  "rape_other_than_custodial" BIGINT,
  "rape_gang_rape" BIGINT,
  "rape_others" BIGINT,
  "attempt_to_commit_rape" BIGINT,
  "kidnapping_abduction_total" BIGINT  -- Kidnapping & Abduction Total,
  "kidnaping_abduction" BIGINT  -- Kidnaping & Abduction,
  "kidnaping_abduction_in_order_to_murder" BIGINT  -- Kidnaping & Abduction In Order To Murder,
  "kidnapping_for_ransom" BIGINT,
  "kidnapping_abduction_of_women_to_compel_her_for_marriage" BIGINT  -- Kidnapping & Abduction Of Women To Compel Her For Marriage,
  "kidnaping_abduction_others" BIGINT  -- Kidnaping & Abduction Others,
  "dowry_deaths" BIGINT,
  "assault_on_women_with_intent_to_outrage_her_modesty_total" BIGINT,
  "sexual_harassment" BIGINT,
  "assault_on_women_with_intent_to_disrobe" BIGINT,
  "voyeurism" BIGINT,
  "stalking" BIGINT,
  "others" BIGINT,
  "insult_to_the_modesty_of_women_total" BIGINT,
  "at_office_premises" BIGINT,
  "in_places_related_to_work" BIGINT,
  "in_public_transport_system" BIGINT,
  "in_other_places" BIGINT,
  "cruelty_by_husband_or_his_relatives" BIGINT,
  "importation_of_girls_from_foreign_country" BIGINT,
  "murder" BIGINT,
  "attempt_to_commit_murder" BIGINT,
  "culpable_homicide_not_amounting_to_murder" BIGINT,
  "attempt_to_commit_culpable_homicide" BIGINT,
  "grievous_hurt" BIGINT,
  "hurt" BIGINT,
  "acid_attack" BIGINT,
  "attempt_to_acid_attack" BIGINT,
  "deaths_caused_with_intent_to_cause_miscarriage" BIGINT,
  "causing_miscarriage_without_consent_of_women" BIGINT,
  "dacoity_total" BIGINT,
  "dacoity_with_murder" BIGINT,
  "other_dacoity" BIGINT,
  "robbery" BIGINT,
  "arson" BIGINT,
  "humantrafficking" BIGINT,
  "abetment_of_suicides_of_women" BIGINT,
  "unnatural_offences" BIGINT,
  "other_ipc_crimes" BIGINT,
  "dowry_prohibition_act_1961" BIGINT  -- Dowry Prohibition Act, 1961,
  "indecent_representation_of_women_p_act_1986" BIGINT  -- Indecent Representation Of Women (P) Act, 1986,
  "commission_of_sati_prevention_act_1987" BIGINT  -- Commission Of Sati Prevention Act, 1987,
  "protection_of_women_from_domestic_violence_act_2005" BIGINT  -- Protection Of Women From Domestic Violence Act, 2005,
  "immoral_traffic_prevention_act" BIGINT,
  "itp_under_section_5" BIGINT,
  "itp_under_section_6" BIGINT,
  "itp_under_section_7" BIGINT,
  "itp_under_section_8" BIGINT,
  "itp_under_other_sections" BIGINT,
  "other_sll_crimes_against_women" BIGINT,
  "total_crimes_against_women" BIGINT
);

CREATE TABLE n_43_arrests_under_crime_against_women (
  "area_name" VARCHAR,
  "year" BIGINT,
  "group_name" VARCHAR,
  "sub_group_name" VARCHAR,
  "persons_acquitted" BIGINT,
  "persons_against_whom_cases_compounded_or_withdrawn" BIGINT,
  "persons_arrested" BIGINT,
  "persons_chargesheeted" BIGINT,
  "persons_convicted" BIGINT,
  "persons_in_custody_or_on_bail_during_investigation_at__30a0e7fb" BIGINT  -- Persons In Custody Or On Bail During Investigation At Year Beginning,
  "persons_in_custody_or_on_bail_during_investigation_at_year_end" BIGINT,
  "persons_in_custody_or_on_bail_during_trial_at_year_end" BIGINT,
  "persons_released_or_freed_by_police_or_magistrate_befo_efa0e070" BIGINT  -- Persons Released Or Freed By Police Or Magistrate Before Trial For Want Of Evidence Or Any Other Reason,
  "persons_trial_completed" BIGINT,
  "persons_under_trial_at_year_beginning" BIGINT,
  "total_persons_under_trial" BIGINT
);