Spaces:
Sleeping
Sleeping
File size: 8,673 Bytes
530a106 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 | schema = """
TABLE states (
state_id INTEGER PRIMARY KEY,
lgd_state_id INTEGER NOT NULL,
state_name TEXT NOT NULL,
census_state INTEGER NOT NULL
)
TABLE districts (
district_id INTEGER PRIMARY KEY,
lgd_district_id INTEGER NOT NULL,
district_name TEXT NOT NULL,
census_district INTEGER NOT NULL
)
TABLE blocks (
block_id INTEGER PRIMARY KEY,
lgd_block_id INTEGER NOT NULL,
block_name TEXT NOT NULL
)
TABLE panchayats (
panchayat_id INTEGER PRIMARY KEY,
lgd_panchayat_id INTEGER NOT NULL,
panchayat_name TEXT NOT NULL
)
TABLE divisions (
division_id INTEGER PRIMARY KEY,
division_name TEXT NOT NULL
)
TABLE villages (
village_id INTEGER PRIMARY KEY,
lgd_village_id INTEGER NOT NULL,
village_name TEXT NOT NULL,
census_village TEXT NOT NULL,
village_type TEXT NOT NULL,
village_status TEXT NOT NULL,
vap_status TEXT NOT NULL,
vwsc_formed INTEGER NOT NULL,
village_certificate INTEGER NOT NULL,
gp_resolution INTEGER NOT NULL,
declaration_video INTEGER NOT NULL,
total_no_households INTEGER NOT NULL,
total_no_house_connection INTEGER NOT NULL,
no_of_ftk_trained_women INTEGER NOT NULL,
no_of_school INTEGER NOT NULL,
school_with_tap_connection INTEGER NOT NULL,
no_of_aws INTEGER NOT NULL,
no_of_aws_with_tap_connection INTEGER NOT NULL,
total_pop INTEGER NOT NULL,
gen_pop INTEGER NOT NULL,
sc_pop INTEGER NOT NULL,
st_pop INTEGER NOT NULL,
sanctioned_approved_status INTEGER,
work_order_updated_status INTEGER,
scheme_is_work_started_status INTEGER
)
TABLE habitations (
habitation_id INTEGER PRIMARY KEY,
habitation_name TEXT NOT NULL,
is_pvtg INTEGER NOT NULL,
community_access_planned INTEGER NOT NULL,
pvtg_fully_partial INTEGER NOT NULL,
pvtg_households INTEGER NOT NULL,
total_no_households INTEGER NOT NULL,
total_no_house_connection INTEGER NOT NULL,
is_pvtg_given_by_mota INTEGER NOT NULL
)
TABLE source_type_categories (
source_type_category_id INTEGER PRIMARY KEY,
description TEXT NOT NULL
)
TABLE source_types (
source_type_id INTEGER PRIMARY KEY,
description TEXT NOT NULL
)
TABLE storage_structure_types (
storage_structure_type_id INTEGER PRIMARY KEY,
description TEXT NOT NULL
)
TABLE categories (
category_id INTEGER PRIMARY KEY,
description TEXT NOT NULL
)
TABLE water_sources (
source_id INTEGER PRIMARY KEY,
location TEXT,
source_type_category_id INTEGER,
source_type_id INTEGER,
response_on TEXT,
scheme_id INTEGER,
latitude TEXT,
longitude TEXT,
pws_fhtc_status INTEGER
)
TABLE schemes (
scheme_id INTEGER PRIMARY KEY,
scheme_name TEXT,
category TEXT,
no_of_villages INTEGER,
household_planned INTEGER,
fhtc_provided INTEGER,
is_pws INTEGER,
fhtc_scheme TEXT,
is_jjm INTEGER,
sanction_year TEXT,
type TEXT,
work_order_date TEXT,
status TEXT,
physical_progress_in_percentage REAL,
handed_over_community_status TEXT,
handed_over_community_date TEXT,
estimated_cost REAL,
csr_donation REAL,
om_cost REAL,
expenditure REAL,
total_central_expenditure REAL,
central_expenditure_sc REAL,
central_expenditure_st REAL,
central_expenditure_gen REAL,
total_state_expenditure REAL,
state_expenditure_sc REAL,
state_expenditure_st REAL,
state_expenditure_gen REAL,
total_world_bank_expenditure REAL,
total_community_expenditure REAL,
total_csr_expenditure REAL,
total_other_expenditure REAL,
total_expenditure_during_JJM REAL,
latitude REAL NOT NULL,
longitude REAL NOT NULL,
location TEXT NOT NULL
)
TABLE scheme_assets (
id INTEGER PRIMARY KEY,
habitation_id INTEGER,
scheme_id INTEGER,
scheme_name TEXT,
latitude REAL,
longitude REAL,
location TEXT,
category_id INTEGER,
FOREIGN KEY (habitation_id) REFERENCES habitations(habitation_id),
FOREIGN KEY (scheme_id) REFERENCES schemes(scheme_id),
FOREIGN KEY (category_id) REFERENCES categories(category_id)
)
TABLE district_state_mapping (
district_id INTEGER PRIMARY KEY,
state_id INTEGER,
FOREIGN KEY (district_id) REFERENCES districts(district_id),
FOREIGN KEY (state_id) REFERENCES states(state_id)
)
TABLE block_district_mapping (
block_id INTEGER PRIMARY KEY,
district_id INTEGER,
FOREIGN KEY (block_id) REFERENCES blocks(block_id),
FOREIGN KEY (district_id) REFERENCES districts(district_id)
)
TABLE block_division_mapping (
block_id INTEGER PRIMARY KEY,
division_id INTEGER,
FOREIGN KEY (block_id) REFERENCES blocks(block_id),
FOREIGN KEY (division_id) REFERENCES divisions(division_id)
)
TABLE panchayat_block_mapping (
panchayat_id INTEGER PRIMARY KEY,
block_id INTEGER,
FOREIGN KEY (panchayat_id) REFERENCES panchayats(panchayat_id),
FOREIGN KEY (block_id) REFERENCES blocks(block_id)
)
TABLE village_panchayat_mapping (
village_id INTEGER PRIMARY KEY,
panchayat_id INTEGER,
FOREIGN KEY (village_id) REFERENCES villages(village_id),
FOREIGN KEY (panchayat_id) REFERENCES panchayats(panchayat_id)
)
TABLE habitation_village_mapping (
habitation_id INTEGER PRIMARY KEY,
village_id INTEGER,
FOREIGN KEY (habitation_id) REFERENCES habitations(habitation_id),
FOREIGN KEY (village_id) REFERENCES villages(village_id)
)
TABLE source_habitation_mapping (
source_id INTEGER PRIMARY KEY,
habitation_id INTEGER,
FOREIGN KEY (source_id) REFERENCES water_sources(source_id),
FOREIGN KEY (habitation_id) REFERENCES habitations(habitation_id)
)
TABLE scheme_village_mapping (
scheme_id INTEGER,
village_id INTEGER,
PRIMARY KEY (scheme_id, village_id),
FOREIGN KEY (scheme_id) REFERENCES schemes(scheme_id),
FOREIGN KEY (village_id) REFERENCES villages(village_id)
)
TABLE scheme_division_mapping (
scheme_id INTEGER PRIMARY KEY,
division_id INTEGER,
FOREIGN KEY (scheme_id) REFERENCES schemes(scheme_id),
FOREIGN KEY (division_id) REFERENCES divisions(division_id)
)
TABLE source_type_source_type_category_mapping (
source_type_id INTEGER PRIMARY KEY,
source_type_category_id INTEGER,
FOREIGN KEY (source_type_id) REFERENCES source_types(source_type_id),
FOREIGN KEY (source_type_category_id) REFERENCES source_type_categories(source_type_category_id)
)
TABLE wtps (
wtp_id INTEGER PRIMARY KEY,
wtp_name INTEGER NOT NULL
)
TABLE labs (
lab_id INTEGER PRIMARY KEY,
lab_name TEXT NOT NULL,
lab_type TEXT NOT NULL,
lab_group TEXT NOT NULL,
latitude REAL,
longitude REAL,
wtp_id INTEGER NOT NULL,
is_in_house INTEGER NOT NULL,
FOREIGN KEY (wtp_id) REFERENCES wtps(wtp_id)
)
TABLE parameters (
parameterid INTEGER PRIMARY KEY,
parameter_name TEXT NOT NULL,
measurement_unit TEXT NOT NULL,
acceptable_limit REAL NOT NULL,
permissible_limit TEXT NOT NULL,
value_type TEXT NOT NULL,
value_type_description TEXT NOT NULL,
public_rate INTEGER NOT NULL,
department_rate INTEGER NOT NULL,
commercial_rate INTEGER NOT NULL,
test_parameter_type TEXT NOT NULL
)
TABLE types (
type_id INTEGER PRIMARY KEY,
type_name TEXT NOT NULL,
description TEXT NOT NULL
)
TABLE wtp_village_mapping (
wtp_id INTEGER PRIMARY KEY,
village_id INTEGER,
FOREIGN KEY (wtp_id) REFERENCES wtps(wtp_id),
FOREIGN KEY (village_id) REFERENCES villages(village_id)
)
TABLE lab_village_mapping (
lab_id INTEGER,
village_id INTEGER,
FOREIGN KEY (lab_id) REFERENCES labs(lab_id),
FOREIGN KEY (village_id) REFERENCES villages(village_id)
)
"""
system_prompt = f"""
You are a precise SQL query generator assistant working with the database schema below.
Only use the tables and columns explicitly provided in the schema when generating SQL.
Schema definition:
{schema}
Guidelines:
- Use the correct primary and foreign key relationships.
- Do not invent tables or columns not listed in the schema.
- If the natural language question is ambiguous, make a reasonable assumption about the intent.
- Output only the final SQL query. Do not add any explanations or commentary.
Instructions: The user question will be provided after this prompt. Write the SQL query that answers it.
"""
|