data_setup for country, states, orders

data_setup.sql

drop table country;
drop table states;
drop table sales;

CREATE TABLE
    IF NOT EXISTS country
(
    country_id    INTEGER IDENTITY(1,1) ,
    _name VARCHAR(22) NOT NULL,
    UNIQUE ( _name)
);


CREATE TABLE
    IF NOT EXISTS states
(
    state_id         INTEGER IDENTITY,
    country_id INTEGER NOT NULL,
    _name      VARCHAR(22) NOT NULL
);


CREATE TABLE
    IF NOT EXISTS sales
(
    sales_id         INTEGER IDENTITY,
    country_id INTEGER NOT NULL,
    state_id   INTEGER NOT NULL,
    unit       INTEGER NOT NULL
);

insert into country ( _name) values ('USA');
insert into country ( _name) values ('Canada');
insert into country ( _name) values ('Mexico');
insert into country ( _name) values ('Brazil');

insert into states (country_id,  _name)values ((select country_id from country where  _name='USA'),'Alaska');
insert into states (country_id,  _name)values ((select country_id from country where  _name='USA'),'Arizona');
insert into states (country_id,  _name)values ((select country_id from country where  _name='USA'),'California');
insert into states (country_id,  _name)values ((select country_id from country where  _name='USA'),'Florida');
insert into states (country_id,  _name)values ((select country_id from country where  _name='Mexico'),'Chiapas');
insert into states (country_id,  _name)values ((select country_id from country where  _name='Mexico'),'Hidalgo');
insert into states (country_id,  _name)values ((select country_id from country where  _name='Mexico'),'Nayarit');
insert into states (country_id,  _name)values ((select country_id from country where  _name='Canada'),'Ontario');
insert into states (country_id,  _name)values ((select country_id from country where  _name='Canada'),'Quebac');
insert into states (country_id,  _name)values ((select country_id from country where  _name='Canada'),'Manitoba');
insert into states (country_id,  _name)values ((select country_id from country where  _name='Canada'),'Alberta');
insert into states (country_id,  _name)values ((select country_id from country where  _name='Canada'),'British Columbia');
insert into states (country_id,  _name)values ((select country_id from country where  _name='Brazil'),'Golas');
insert into states (country_id,  _name)values ((select country_id from country where  _name='Brazil'),'Acre');


insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='USA'), (select state_id from states where _name='Alaska'),10);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='USA'), (select state_id from states where _name='Arizona'),100);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='USA'), (select state_id from states where _name='California'),310);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='USA'), (select state_id from states where _name='Florida'),120);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='Mexico'), (select state_id from states where _name='Chiapas'),410);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='Mexico'), (select state_id from states where _name='Hidalgo'),180);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='Mexico'), (select state_id from states where _name='Nayarit'),104);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='Canada'), (select state_id from states where _name='Ontario'),150);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='Canada'), (select state_id from states where _name='Quebac'),130);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='Canada'), (select state_id from states where _name='Manitoba'),610);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='Canada'), (select state_id from states where _name='Alberta'),154);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='Canada'), (select state_id from states where _name='British Columbia'),354);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='Brazil'), (select state_id from states where _name='Golas'),105);
insert into sales (country_id, state_id, unit)values ((select country_id from country where _name='Brazil'), (select state_id from states where _name='Acre'),108);

select * from country;
select * from states;
select * from sales;