Код выполняется последовательно, по пунктам.
Код закоментирован /* */, для исполнения его нужно вставить, например в PgAdmin, выделить мышкой или раскоментировать нужный фрагмент и нажать "F5".
Таблицы идентичны примерам в книге.
-- M.Gruber "Understanding SQL" /М.Грабер "Понимание SQL"
-- Создание примера базы данных по книге на базе Postgres-9.4
-- 1. Создаем таблицу salespeople
/*
create table salespeople (
snum bigint primary key,
sname varchar(50),
city varchar(50),
comm numeric)
*/
-- 1.1. Наполняем данными salespeople
/*
insert into salespeople
select 1001::bigint as snum, 'Peel'::varchar(50) as sname, 'London'::varchar(50) as city, 0.12::numeric as comm
union all
select 1002::bigint as snum, 'Serres'::varchar(50) as sname, 'San Jose'::varchar(50) as city, 0.13::numeric as comm
union all
select 1004::bigint as snum, 'Motika'::varchar(50) as sname, 'London'::varchar(50) as city, 0.11::numeric as comm
union all
select 1007::bigint as snum, 'Rifkin'::varchar(50) as sname, 'Barcelona'::varchar(50) as city, 0.15::numeric as comm
union all
select 1003::bigint as snum, 'Axelrod'::varchar(50) as sname, 'New York'::varchar(50) as city, 0.10::numeric as comm
*/
-- 2. Создаем таблицу customers
/*
create table customers (
cnum bigint primary key,
cname varchar(50),
city varchar(50),
rating numeric,
snum bigint)
*/
-- 2.1. Наполняем данными customers
/*
insert into customers
select 2001::bigint as сnum, 'Hoffman'::varchar(50) as сname, 'London'::varchar(50) as city, 100::numeric as rating, 1001::bigint as snum
union all
select 2002::bigint as сnum, 'Giovanni'::varchar(50) as сname, 'Rome'::varchar(50) as city, 200::numeric as rating, 1003::bigint as snum
union all
select 2003::bigint as сnum, 'Liu'::varchar(50) as сname, 'San Jose'::varchar(50) as city, 200::numeric as rating, 1002::bigint as snum
union all
select 2004::bigint as сnum, 'Grass'::varchar(50) as сname, 'Berlin'::varchar(50) as city, 300::numeric as rating, 1002::bigint as snum
union all
select 2006::bigint as сnum, 'Clemens'::varchar(50) as сname, 'London'::varchar(50) as city, 100::numeric as rating, 1001::bigint as snum
union all
select 2008::bigint as сnum, 'Cisneros'::varchar(50) as сname, 'San Jose'::varchar(50) as city, 300::numeric as rating, 1007::bigint as snum
union all
select 2007::bigint as сnum, 'Pereira'::varchar(50) as сname, 'Rome'::varchar(50) as city, 100::numeric as rating, 1004::bigint as snum
*/
-- 3. Создаем таблицу orders
/*
create table orders (
onum bigint primary key,
amt numeric,
odate date,
cnum bigint,
snum bigint)
*/
-- 3.1. Наполняем данными orders
/*
insert into orders
select 3001::bigint as onum, 18.69::numeric as amt, '10/03/1990'::date as odate, 2008::bigint as cnum, 1007::bigint as snum
union all
select 3003::bigint as onum, 767.19::numeric as amt, '10/03/1990'::date as odate, 2001::bigint as cnum, 1001::bigint as snum
union all
select 3002::bigint as onum, 1900.10::numeric as amt, '10/03/1990'::date as odate, 2007::bigint as cnum, 1004::bigint as snum
union all
select 3005::bigint as onum, 5160.45::numeric as amt, '10/03/1990'::date as odate, 2003::bigint as cnum, 1002::bigint as snum
union all
select 3006::bigint as onum, 1098.16::numeric as amt, '10/03/1990'::date as odate, 2008::bigint as cnum, 1007::bigint as snum
union all
select 3009::bigint as onum, 1713.23::numeric as amt, '10/04/1990'::date as odate, 2002::bigint as cnum, 1003::bigint as snum
union all
select 3007::bigint as onum, 75.75::numeric as amt, '10/04/1990'::date as odate, 2004::bigint as cnum, 1002::bigint as snum
union all
select 3008::bigint as onum, 4723.00::numeric as amt, '10/05/1990'::date as odate, 2006::bigint as cnum, 1001::bigint as snum
union all
select 3010::bigint as onum, 1309.95::numeric as amt, '10/06/1990'::date as odate, 2004::bigint as cnum, 1002::bigint as snum
union all
select 3011::bigint as onum, 9891.88::numeric as amt, '10/06/1990'::date as odate, 2006::bigint as cnum, 1001::bigint as snum
*/
-- 4. Удаление базы данных
/*
DROP TABLE salespeople
DROP TABLE customers
DROP TABLE orders
*/
