SQLize Online / PHPize Online  /  SQLtest Online

CREATE TYPE contact_type AS ENUM ('worker', 'person'); CREATE TABLE contact ( id serial unique, contact_type contact_type not null, name varchar not null, number varchar not null, email varchar ); CREATE TABLE location ( id serial unique, location varchar ); CREATE TYPE client_type AS ENUM ('entity', 'individual'); CREATE TABLE client ( id serial unique, client_type client_type not null, client_name varchar, contact_id int references contact (id) not null ); CREATE TABLE hotel ( id serial unique, hotel_name varchar(200) not null, location_id int references location (id) not null, hotel_number varchar not null, worker_id int references contact (id) not null, hotel_description varchar(500) not null ); CREATE TABLE tour ( id serial unique, tour_name varchar not null , hotel_id int references hotel (id) not null, entry_date date not null, departure_date date not null, food varchar not null , price numeric(10, 2), tour_description varchar(500) not null ); CREATE TYPE payment_type AS ENUM ('credit', 'prepayment'); CREATE TABLE booking ( id serial unique, client_id int references client (id) not null, payment payment_type not null, tour_id int references tour (id) not null, tour_price numeric(10, 2) not null, amount int not null, price numeric(10, 2) not null ); CREATE TABLE sale ( id serial unique, client_id int references booking (id) not null ); SELECT client.id, client.client_type, client.client_name, json_agg( json_build_object( 'contact_type',contact.contact_type, 'contact_name', contact.name, 'contact_number', contact.number ) ) contacts, json_object_agg('tour_name', tour_name) tours FROM booking b INNER JOIN client on client.id = b.client_id INNER JOIN contact on client.contact_id = contact.id JOIN tour t ON t.id = b.tour_id GROUP BY client.id, client.client_type, client.client_name;