The more work I do in this industry, the more I have to utilize SQL almost daily to extract the information I need from CRM databases, website CMS platforms, and CDPs. I’d encourage any marketer to learn SQL. SQL isn’t just for pulling the information you need; it’s also for storing it.
For one client, I built an online store location platform customized to their site. Interestingly, I had difficulty finding the state and province data online as I began to build out the tables for querying country and state or province (located in the United States and Canada only). I thought this data might be useful for other developers or marketers who needed to populate some foundational tables for the country and state, so I’m publishing them here.
Table of Contents
SQL Explanations
Here are some brief explanations of the SQL functions utilized in the code below:
- create table: Defines a new table with specified columns, data types, and constraints. Ensures the table structure is appropriate for data storage and relationships.
- drop table: Removes an existing table from the database, including all its data and structure, if it exists.
- foreign key: Establishes a relationship between two tables by linking a column in one table to the primary key in another. Enforces referential integrity, such as cascading deletions.
- insert into: Adds new rows of data into a specified table, filling the defined columns with provided values.
- lower: Converts a string to lowercase, ensuring case-insensitive comparisons or searches in queries.
- primary key: Specifies a unique identifier for rows in a table, ensuring no duplicate entries for the designated column(s).
- references: Defines a foreign key relationship between a column in one table and the primary key in another, enabling relational data integrity.
- select: Retrieves data from one or more tables based on specified conditions, allowing for targeted queries of the dataset.
- where: Filters query results to include only rows that meet specific conditions, refining the scope of the data returned.
Create Your Countries Table in SQL
The first step is to create a table that stores country information, including a two-letter abbreviation (country_id) as the primary key and the full name of the country (country_name). This table provides a reference for storing and retrieving country-level data.
drop table if exists tbl_countries;
create table tbl_countries (
country_id varchar(2) primary key,
country_name varchar(100) not null
);Insert Your Countries in Your Countries Table in SQL
Insert records into the countries table for Canada (ca) and the United States (us), ensuring each country has a unique abbreviation and name.
insert into tbl_countries (country_id, country_name) values
('ca', 'Canada'),
('us', 'United States');Create Your State / Province Table in SQL
The states table is designed to store details about states or provinces. It uses a composite primary key consisting of the two-letter state abbreviation (state_id) and the country abbreviation (country_id). A foreign key constraint links each state to its corresponding country in the countries table. This ensures relational integrity.
drop table if exists tbl_states;
create table tbl_states (
state_id varchar(2) not null,
country_id varchar(2) not null,
state_name varchar(100) not null,
primary key (state_id, country_id),
foreign key (country_id) references tbl_countries(country_id) on delete cascade
);Insert Your States and Provinces in Your State or Province Table in SQL
Insert records for states and provinces in both Canada and the United States. Each record includes the two-letter country ID, the two-letter state/province abbreviation, and the full name of the state or province.
insert into tbl_states (country_id, state_id, state_name) values
('ca', 'ab', 'Alberta'),
('ca', 'bc', 'British Columbia'),
('ca', 'mb', 'Manitoba'),
('ca', 'nb', 'New Brunswick'),
('ca', 'nl', 'Newfoundland and Labrador'),
('ca', 'ns', 'Nova Scotia'),
('ca', 'nt', 'Northwest Territories'),
('ca', 'nu', 'Nunavut'),
('ca', 'on', 'Ontario'),
('ca', 'pe', 'Prince Edward Island'),
('ca', 'qc', 'Quebec'),
('ca', 'sk', 'Saskatchewan'),
('ca', 'yt', 'Yukon'),
('us', 'ak', 'Alaska'),
('us', 'al', 'Alabama'),
('us', 'ar', 'Arkansas'),
('us', 'az', 'Arizona'),
('us', 'ca', 'California'),
('us', 'co', 'Colorado'),
('us', 'ct', 'Connecticut'),
('us', 'dc', 'District of Columbia'),
('us', 'de', 'Delaware'),
('us', 'fl', 'Florida'),
('us', 'ga', 'Georgia'),
('us', 'hi', 'Hawaii'),
('us', 'ia', 'Iowa'),
('us', 'id', 'Idaho'),
('us', 'il', 'Illinois'),
('us', 'in', 'Indiana'),
('us', 'ks', 'Kansas'),
('us', 'ky', 'Kentucky'),
('us', 'la', 'Louisiana'),
('us', 'ma', 'Massachusetts'),
('us', 'md', 'Maryland'),
('us', 'me', 'Maine'),
('us', 'mi', 'Michigan'),
('us', 'mn', 'Minnesota'),
('us', 'mo', 'Missouri'),
('us', 'ms', 'Mississippi'),
('us', 'mt', 'Montana'),
('us', 'nc', 'North Carolina'),
('us', 'nd', 'North Dakota'),
('us', 'ne', 'Nebraska'),
('us', 'nh', 'New Hampshire'),
('us', 'nj', 'New Jersey'),
('us', 'nm', 'New Mexico'),
('us', 'nv', 'Nevada'),
('us', 'ny', 'New York'),
('us', 'oh', 'Ohio'),
('us', 'ok', 'Oklahoma'),
('us', 'or', 'Oregon'),
('us', 'pa', 'Pennsylvania'),
('us', 'ri', 'Rhode Island'),
('us', 'sc', 'South Carolina'),
('us', 'sd', 'South Dakota'),
('us', 'tn', 'Tennessee'),
('us', 'tx', 'Texas'),
('us', 'ut', 'Utah'),
('us', 'va', 'Virginia'),
('us', 'vt', 'Vermont'),
('us', 'wa', 'Washington'),
('us', 'wi', 'Wisconsin'),
('us', 'wv', 'West Virginia'),
('us', 'wy', 'Wyoming');SQL Query For a Country
This query retrieves all details for a specific country using its two-letter abbreviation. By using lower() in the where clause, the query is case-insensitive, allowing users to input the country ID in any case.
select *
from tbl_countries
where lower(country_id) = lower('ca');SQL Query For The States for The Specific Country
This query fetches all states or provinces belonging to a specific country. The lower() function ensures that the country ID can be matched regardless of the input’s case, providing user flexibility.
select *
from tbl_states
where lower(country_id) = lower('us');SQL Query For A State
This query retrieves details for a specific state or province. It uses the state ID identify the state uniquely. The ID is case-insensitive, ensuring a robust and user-friendly lookup.
select *
from tbl_states
where lower(state_id) = lower('ny');Related Martech Zone Content
