Код выполняется последовательно, по пунктам.

Код закоментирован /* */, для исполнения его нужно вставить, например в 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

*/