CREATE TABLE raion 
(raion TEXT NOT NULL UNIQUE,
ID CHAR(5) PRIMARY KEY);

CREATE TABLE typehouse 
(type TEXT NOT NULL UNIQUE,
ID CHAR(5) PRIMARY KEY);


CREATE TABLE klient
(FIRST_NAME TEXT  NOT NULL,
LAST_NAME TEXT  NOT NULL,
ID CHAR(5) PRIMARY KEY,
id_raion CHAR(5) NOT NULL,
adress_klient text,
CONSTRAINT fk  FOREIGN KEY (id_raion)
REFERENCES raion (ID)ON UPDATE NO ACTION ON DELETE CASCADE,
CONSTRAINT UN UNIQUE(LAST_NAME, FIRST_NAME));

  CREATE TABLE house(
ID CHAR(5) PRIMARY KEY,
ID_raion CHAR(5) NOT NULL,
id_typehouse char(5) not null,
adress_house text,
rajmer integer not null,
CONSTRAINT FK3 FOREIGN KEY (ID_raion) REFERENCES raion(ID) 
ON UPDATE NO ACTION ON DELETE CASCADE,
FOREIGN KEY (ID_typehouse) REFERENCES typehouse(ID)
ON UPDATE NO ACTION ON DELETE CASCADE);


create table obmen(
id char(5) primary key,
id_klient char(5) not null,
id_house char(5) not null,
date_obmen date not null,
CONSTRAINT FK5 FOREIGN KEY (ID_klient) REFERENCES klient(ID)
ON UPDATE NO ACTION ON DELETE CASCADE,
FOREIGN KEY (ID_house) REFERENCES house(id) 
ON UPDATE NO ACTION ON DELETE CASCADE,
 obmen boolean) ;

select first_name,last_name,raion,adress_klient,adress_house,rajmer,count(obmen.id_house)
from klient join raion on klient.id_raion=raion.id join house on raion.id=house.id_raion join obmen on house.id=obmen.id_house
     join typehouse on house.id_typehouse=typehouse.id
     group by last_name,first_name,raion,adress_klient,adress_house,rajmer,raion
     having  count(obmen.id_house) >3;

Тема: учет обмена жилплощади.
Объекты: районы, клиенты, жилье, обмены жилья, тип жилья.
Для моделирования задачи необходимо хранить следующую информацию:
* ФИО клиента
* Адрес места работы клиента с указанием района
* Характеристика жилья (адрес, район, размер жилья)
* Тип жилья (1, 2, 3, -комнатная квартира)
* Обмен жилплощади (было, стало)
* Дата обмена
Дополнительные условия:
* один клиент может произвести несколько обменов жилья.

select first_name,last_name,adress_klient,adress_house,rajmer,count(obmen.id_klient)
from klient join obmen on klient.id=obmen.id_klient  join house  on house.id=obmen.id_house
     join typehouse on house.id_typehouse=typehouse.id
     group by last_name,first_name,adress_klient,adress_house,rajmer
     having  count( obmen.id_house) >=2;