Government Welfare & Subsidy Management
Download all 4 tables with complete messy data (25+ rows per table)
| citizen_id | full_name | date_of_birth | address | profession | annual_income | family_size | disability_status |
|---|---|---|---|---|---|---|---|
| C001 | rajesh kumar | 15/03/1980 | mumbai, maharashtra | FARMER | 45000 | 5 | NO |
| c002 | PRIYA SHARMA | 1985-07-22 | Delhi | teacher | NULL | 3 | yes |
| C003 | amit patel | 10-05-1992 | ahmedabad, GUJARAT | Engineer | 850000 | 2 | No |
| C004 | sunita devi | 1975/12/08 | patna,bihar | NULL | 25000 | 7 | YES |
| c005 | Mohammed Ali | 22-11-1988 | Hyderabad, Telangana | daily wage worker | 72000 | 4 | no |
| C006 | LAKSHMI IYER | 1990-05-15 | chennai, Tamil Nadu | NULL | 38000 | 6 | YES |
| c007 | vikram singh | 18/08/1983 | jaipur,Rajasthan | shopkeeper | 95000 | 4 | NO |
| C008 | anita desai | 25-02-1978 | pune, maharashtra | NURSE | 180000 | 3 | no |
| c009 | Ramesh Gupta | 1988/09/12 | Lucknow, Uttar Pradesh | driver | NULL | 5 | No |
| C010 | MEERA NAIR | 12-11-1995 | kochi, kerala | NULL | 42000 | 2 | YES |
| c011 | suresh yadav | 30/06/1982 | nagpur,maharashtra | FARMER | 52000 | 8 | no |
| C012 | kavita SHAH | 1987-04-20 | Surat, Gujarat | tailor | 48000 | 4 | NO |
| C013 | ARUN KUMAR | 15-07-1980 | Bangalore, Karnataka | software engineer | 920000 | 3 | No |
| c014 | pooja verma | 1992/10/25 | indore, madhya pradesh | NULL | 35000 | 5 | yes |
| C015 | Rajiv Mehta | 08/12/1985 | chandigarh | accountant | NULL | 3 | NO |
| c016 | SITA RAM | 1970-03-18 | varanasi, uttar pradesh | priest | 28000 | 6 | YES |
| C017 | deepak joshi | 22-09-1989 | dehradun,uttarakhand | teacher | 125000 | 2 | no |
| c018 | anjali KAPOOR | 1993/05/30 | amritsar, Punjab | NULL | 55000 | 4 | No |
| C019 | manoj TIWARI | 10-01-1984 | gwalior, madhya pradesh | daily wage worker | 65000 | 7 | YES |
| c020 | REKHA PILLAI | 1991-08-14 | trivandrum,kerala | nurse | NULL | 3 | no |
| C021 | harish chandra | 05/11/1979 | Kanpur, Uttar Pradesh | shopkeeper | 88000 | 5 | NO |
| c022 | NISHA REDDY | 1986-12-22 | visakhapatnam, andhra pradesh | FARMER | 46000 | 6 | yes |
| C023 | prakash jain | 18-04-1990 | indore, Madhya Pradesh | engineer | NULL | 2 | No |
| c024 | GEETA DEVI | 1976/07/08 | patna, bihar | NULL | 22000 | 9 | YES |
| C025 | anil sharma | 25/09/1988 | shimla, himachal pradesh | driver | 58000 | 4 | no |
| bracket_id | bracket_name | min_income | max_income | priority_level |
|---|---|---|---|---|
| B1 | Below Poverty Line | 0 | 50000 | HIGH |
| B2 | Low Income | 50001 | 150000 | MEDIUM |
| B3 | Middle Income | 150001 | 500000 | LOW |
| B4 | High Income | 500001 | 999999999 | NONE |
| subsidy_id | scheme_name | category | amount | min_family_size | requires_disability | max_income |
|---|---|---|---|---|---|---|
| S001 | Ration Card Subsidy | Food Security | 12000 | 1 | NO | 50000 |
| S002 | Disability Pension | Social Welfare | 24000 | 1 | YES | 150000 |
| S003 | Farmer Support Scheme | Agriculture | 30000 | 3 | NO | 150000 |
| S004 | Education Grant | Education | 15000 | 2 | NO | 200000 |
| S005 | Housing Subsidy | Housing | 50000 | 4 | NO | 100000 |
| S006 | Senior Citizen Pension | Social Welfare | 18000 | 1 | NO | 100000 |
| S007 | Skill Development Grant | Employment | 20000 | 1 | NO | 150000 |
| S008 | Healthcare Subsidy | Health | 25000 | 3 | NO | 200000 |
| application_id | citizen_id | subsidy_id | application_date | status |
|---|---|---|---|---|
| APP001 | C001 | S001 | 2024-01-15 | APPROVED |
| APP002 | c002 | s002 | 15/01/2024 | pending |
| APP003 | C003 | S001 | 2024/01/20 | REJECTED |
| APP004 | C004 | s005 | 20-01-2024 | Approved |
| APP005 | c001 | S003 | 2024-01-22 | APPROVED |
| APP006 | C999 | S002 | 2024-01-25 | pending |
| APP007 | c006 | S001 | 25/01/2024 | APPROVED |
| APP008 | C007 | s003 | 2024/01/28 | Approved |
| APP009 | c008 | S004 | 30-01-2024 | REJECTED |
| APP010 | C009 | s005 | 2024-02-01 | pending |
| APP011 | c010 | S002 | 02/02/2024 | APPROVED |
| APP012 | C011 | s003 | 2024/02/05 | Approved |
| APP013 | c012 | S001 | 08-02-2024 | APPROVED |
| APP014 | C013 | S001 | 2024-02-10 | REJECTED |
| APP015 | c014 | s002 | 12/02/2024 | pending |
| APP016 | C015 | S004 | 2024/02/15 | Approved |
| APP017 | c016 | s005 | 18-02-2024 | APPROVED |
| APP018 | C017 | S004 | 2024-02-20 | APPROVED |
| APP019 | c018 | s003 | 22/02/2024 | pending |
| APP020 | C019 | S002 | 2024/02/25 | Approved |
| APP021 | c020 | S006 | 28-02-2024 | pending |
| APP022 | C021 | s007 | 2024-03-01 | APPROVED |
| APP023 | c022 | S001 | 03/03/2024 | Approved |
| APP024 | C023 | s008 | 2024/03/05 | REJECTED |
| APP025 | c024 | S005 | 08-03-2024 | APPROVED |
| APP026 | C025 | s003 | 2024-03-10 | pending |
| APP027 | C888 | S999 | 12/03/2024 | Approved |
MySQL:
1. Create database: CREATE DATABASE govt_welfare;
2. Import CSV: LOAD DATA INFILE 'path/citizens.csv' INTO TABLE citizens FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
PostgreSQL:
1. Create database: CREATE DATABASE govt_welfare;
2. Import CSV: \COPY citizens FROM 'path/citizens.csv' WITH (FORMAT csv, HEADER true);
Note: Create tables first with appropriate column types before importing!