July 25, 2008 at 9:04 pm
Hi
I need a sql script for Countries, State and Cities.:)
July 26, 2008 at 10:16 am
What is the question here? Please write a question that better explains what you are looking for.
July 26, 2008 at 11:00 am
That is kind a challenging and if anybody will provide you a script might be Half baked or someting..
Try to Google around.
But here is a list of countries to start with:: Ofcourse this is not a SQL Script...
http://www.iso.org/iso/list-en1-semic-2.txt
http://www.ajaxdr.com/code/json-encoded-list-of-countries-and-their-iso-3166-1-alpha-3-code/
http://www.ajaxdr.com/code/html-list-of-countries-with-iso-3166-1-alpha-3-and-name/
Maninder
www.dbanation.com
July 26, 2008 at 12:39 pm
Select Country, State, City From Cities
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 28, 2008 at 12:06 am
mirza_aurangzeb_ashraf (7/25/2008)
HiI need a sql script for Countries, State and Cities.:)
Me Too need the same thing. Please help me if you found any thing regarding the same....
July 28, 2008 at 3:12 am
mirza_aurangzeb_ashraf (7/25/2008)
HiI need a sql script for Countries, State and Cities.:)
Hi Mirza,
Whether you need a script for list of all Contries,States, and cities by using joins
or
you need a list of Contries,States, and cities to store in Tables?
March 25, 2009 at 5:11 am
I also need a script file for us states and their cites
March 26, 2009 at 9:18 am
Try checking out the US Postal Service website.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 28, 2009 at 8:37 am
What you need is to create 3 distinct tables.
CREATE TABLE County
(
pk_ CountryID CHAR(3) NOT NULL PRIMARY KEY,
Country Name varchar(50)
)
CREATE Table State
(
pk_StateID CHAR(3) NOT NULL PRIMARY KEY,
fk_CountryID CHAR(3)
State_Name varchar(50)
)
CREATE TABLE City
(
pk_ID int NOT NULL PRIMARY KEY IDENTITY,
fk_StateID CHAR(3) NOT NULL PRIMARY KEY,
fk_CountryID CHAR(3)
State_Name varchar(50)
)
For countries, you can use the ISO 3-character codes (such as GBR for Great Britain).
I have seen list of state codes for the US somewhere, such as FLA for Florida.
In the case of cities, I doubt very much that the concept of a city code exists. So you would have to get the user to either select from the list of cities within a specific state (this is why a foreign key fk_StateID must be present in you City table, as you could easily have the same city name in two different states.
- or -
validate user input against your City table to guard against typos.
And while you are at it, you could also consider a Currency table, again using the ISO code for currencies, such as USD for US dollars (first two letters are from the country code and the last letter is the first letter the currency name, such as GBP for British pound). This table should also have a foreign key pointing the CountryID of the Country table.
Why use a separate Currency table instead of just sticking a CurreencyID and a Currency_Name column in the Country table?
Well you could be faced with the following situation: the CountryID of a supplier might be HKG (Hong Kong) but instead of paying in HKD (Hong Kong dollars), your supplier may prefer payment in USD.
Regards
June 29, 2011 at 12:52 am
Whatever script you end up using with your database, consider using the data from the database of world cities by WorldCitiesDatabase.Info.
Best part about that database is that it contains enough entries to provide sufficient detail in most situations. Yet it is not overly bloated, and so it won't take forever to populate the selectors, as is the case with free databases that contain over 2 million entries.
All details about the database can be seen in freely downloadable samples. The full version of the database is offered in both Excel and MySQL formats for only $4.99.
January 14, 2012 at 11:58 pm
hi....
i want to redymade database having tables country, state,city
January 15, 2012 at 2:35 am
1) Posted Saturday, July 26, 2008 9:34 AM
2) Are you ready to pay for it?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply