Skip to content

Latest commit

 

History

History
75 lines (56 loc) · 2.15 KB

README.md

File metadata and controls

75 lines (56 loc) · 2.15 KB

Good Old Style SQL for countries, states and cities information. Standard SQL syntax is used. It should work for all relational databases.

Usage

Run countries.sql, states.sql and cities.sql with your favorite SQL interpreter.

Ex (MySQL command line interpreter):

mysql -u USERNAME -pPASSWORD -D DATABASENAME < countries.sql 
mysql -u USERNAME -pPASSWORD -D DATABASENAME < states.sql 
mysql -u USERNAME -pPASSWORD -D DATABASENAME < cities.sql

Scenario 1

Assume your customer is from Boston.

  1. Normally you should show all countries to her to find United states.
select *
	from countries;
  1. From her choice it should be 231 as the country id. And you can bring the states of United States with following SQL:
select *
	from states
	where country_id = 231;
  1. Now, she'll navigate to the state of Massachussets. And you'll show the cities of Massachussets with following SQL:
select *
	from cities
	where state_id = 3943;
  1. And if she choose Boston, you'll have 44918 as your city id. Cheers!

Scenario 2

Assume your customer is from city Artvin of Turkey.

Normally you should show all countries to her to choose Turkey.

select *
	from countries;

From her choice it should be 223 as the country id. And you can bring the cities with following SQL:

select *
	from states
	where country_id = 223;

Now, she'll navigate to the city of Artvin. And you'll show the districts of Artvin with following SQL:

select *
	from cities
	where state_id = 3672;

Info

The difference between scenario 1 and 2 is, USA has states but Turkey don't. So for Turkey "states" data becomes "cities" and "cities" data become "district". So while labeling, it is better to choose wording accordingly.

Credit

Initial script source was here: https://github.com/hiiamrohit/Countries-States-Cities-database

Disclimer

These scripts are provided as - is. Use them with your own responsibilities. The implementer does not accept any responsibility under any condition.

Warning

Although commented out, scripts contain drop table statements. If you want to uncomment, have your own responsibility!