Sample Database SQL

-- The database is composed of three tables that
-- form a hierarchical set. The relation of
-- COUNTRY to STATE is one to many, as is the
-- relationship of STATE to CITY. (The many is
-- actually 0, 1, or many.
--
-- The values for COUNTRY.COUNTRY and STATE.STATE
-- are code values. In this case, the state codes
-- are only valid for Canada and the USA because these
-- are the state/province codes used by the post offices.
--
CREATE TABLE COUNTRY (
COUNTRY VARCHAR(2) NOT NULL,
NAME VARCHAR(40),
PRIMARY KEY (COUNTRY)
);
--
--
CREATE TABLE STATE (
STATE VARCHAR(2) NOT NULL,
NAME VARCHAR(40),
COUNTRY VARCHAR(2) NOT NULL,
PRIMARY KEY (COUNTRY, STATE),
FOREIGN KEY (COUNTRY) REFERENCES COUNTRY(COUNTRY)
);
--
--
CREATE TABLE CITY (
CITY VARCHAR(40) NOT NULL,
COUNTRY VARCHAR(2) NOT NULL,
STATE VARCHAR(2) NOT NULL,
PRIMARY KEY (COUNTRY, STATE, CITY),
FOREIGN KEY (COUNTRY, STATE) REFERENCES STATE(COUNTRY, STATE)
);
--
-- This SQL statement populates the COUNTRY table.
--
INSERT INTO COUNTRY (COUNTRY, NAME) VALUES
('CA', 'Canada'),
('US', 'United States of America');
--
-- This SQL statement populates the STATE table with names
-- and postal codes of American states and Canadian provinces.
--
INSERT INTO STATE (STATE, NAME, COUNTRY) VALUES
('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', 'US'),
('AB', 'Alberta', 'CA'),
('BC', 'British Columbia', 'CA'),
('MB', 'Manitoba', 'CA'),
('NB', 'New Brunswick', 'CA'),
('NL', 'Newfoundland and Labrador', 'CA'),
('NT', 'Northwest Territories', 'CA'),
('NS', 'Nova Scotia', 'CA'),
('NU', 'Nunavut', 'CA'),
('ON', 'Ontario', 'CA'),
('PE', 'Prince Edward Island', 'CA'),
('QC', 'Quebec', 'CA'),
('SK', 'Saskatchewan', 'CA'),
('YT', 'Yukon', 'CA');
--
-- This is what I refer to as a sanity check
-- transaction. The inner SQL statement
-- returns the number of states in the STATE
-- table for each value of COUNTRY.
--
SELECT MULT, COUNT(MULT) AS FREQ
FROM
( SELECT COUNT(COUNTRY) AS MULT, COUNTRY
FROM STATE GROUP BY COUNTRY ) FIRST
GROUP BY MULT
ORDER BY MULT;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s