Загрузка данных

drop table if exists orders cascade; drop table if exists book_writer cascade; drop table if exists contract cascade; drop table if exists book cascade; drop table if exists customer cascade; drop table if exists writer cascade; create table writer (passport_no varchar(20) primary key, last_name varchar(50) not null, first_name varchar(50) not null, middle_name varchar(50), address text not null, phone varchar(20) not null); create table contract (contract_id serial primary key, writer_passport varchar(20) not null unique, sign_date date not null default current_date, term_years integer not null check (term_years > 0), restaurant_flag boolean not null default false, restaurant_date date, foreign key (writer_passport) references writer(passport_no) on delete cascade); create table book (book_code varchar(20) primary key, title varchar(200) not null, publisher varchar(100) not null, release_date date not null, cost_price decimal(10,2) not null check (cost_price >= 0), sale_price decimal(10,2) not null check (sale_price >= 0), royalty_total decimal(10,2) not null check (royalty_total >= 0)); create table book_writer (book_code varchar(20) not null, writer_passport varchar(20) not null, primary key (book_code, writer_passport), foreign key (book_code) references book(book_code) on delete cascade, foreign key (writer_passport) references writer(passport_no) on delete cascade); create table customer (customer_id serial primary key, name varchar(100) not null, address text not null, phone varchar(20) not null, contact_person varchar(100) not null); create table orders (order_id serial primary key, book_code varchar(20) not null, customer_id integer not null, order_date date not null default current_date, execute_date date, quantity integer not null check (quantity > 0), foreign key (book_code) references book(book_code) on delete restrict, foreign key (customer_id) references customer(customer_id) on delete cascade); insert into writer (passport_no, last_name, first_name, middle_name, address, phone) values ('1234-567890', 'иванов', 'иван', 'иванович', 'г. москва, ул. ленина, д. 1, кв. 5', '+79001234567'); insert into writer (passport_no, last_name, first_name, middle_name, address, phone) values ('2345-678901', 'петров', 'пётр', 'петрович', 'г. москва, ул. пушкина, д. 2, кв. 10', '+79007654321'); insert into writer (passport_no, last_name, first_name, middle_name, address, phone) values ('3456-789012', 'сидоров', 'сергей', 'сергеевич', 'г. санкт-петербург, невский пр., д. 5, кв. 12', '+78123456789'); insert into writer (passport_no, last_name, first_name, middle_name, address, phone) values ('4567-890123', 'козлова', 'елена', 'ивановна', 'г. казань, ул. баумана, д. 3, кв. 8', '+78431234567'); insert into contract (writer_passport, sign_date, term_years, restaurant_flag, restaurant_date) values ('1234-567890', '2025-01-01', 3, false, null); insert into contract (writer_passport, sign_date, term_years, restaurant_flag, restaurant_date) values ('2345-678901', '2025-02-01', 2, true, '2025-02-01'); insert into contract (writer_passport, sign_date, term_years, restaurant_flag, restaurant_date) values ('3456-789012', '2025-03-01', 1, false, null); insert into contract (writer_passport, sign_date, term_years, restaurant_flag, restaurant_date) values ('4567-890123', '2025-01-15', 2, false, null); insert into book (book_code, title, publisher, release_date, cost_price, sale_price, royalty_total) values ('b001', 'война и мир', 'издательство наука', '2025-03-10', 500.00, 1200.00, 300.00); insert into book (book_code, title, publisher, release_date, cost_price, sale_price, royalty_total) values ('b002', 'анна каренина', 'издательство художественная литература', '2025-04-05', 400.00, 1100.00, 250.00); insert into book (book_code, title, publisher, release_date, cost_price, sale_price, royalty_total) values ('b003', 'преступление и наказание', 'издательство эксмо', '2025-05-15', 350.00, 950.00, 200.00); insert into book (book_code, title, publisher, release_date, cost_price, sale_price, royalty_total) values ('b004', 'мастер и маргарита', 'издательство аст', '2025-06-20', 450.00, 1300.00, 280.00); insert into book (book_code, title, publisher, release_date, cost_price, sale_price, royalty_total) values ('b005', 'тихий дон', 'издательство дрофа', '2025-07-01', 380.00, 1000.00, 220.00); insert into book_writer (book_code, writer_passport) values ('b001', '1234-567890'); insert into book_writer (book_code, writer_passport) values ('b001', '2345-678901'); insert into book_writer (book_code, writer_passport) values ('b002', '1234-567890'); insert into book_writer (book_code, writer_passport) values ('b003', '2345-678901'); insert into book_writer (book_code, writer_passport) values ('b004', '3456-789012'); insert into book_writer (book_code, writer_passport) values ('b005', '4567-890123'); insert into customer (name, address, phone, contact_person) values ('ооо книжный мир', 'г. москва, ул. тверская, д. 15', '+74951234567', 'сидорова анна алексеевна'); insert into customer (name, address, phone, contact_person) values ('гбук библиотека №1', 'г. санкт-петербург, невский пр., д. 10', '+78121234567', 'кузнецов борис борисович'); insert into customer (name, address, phone, contact_person) values ('ип иванова е.м.', 'г. казань, ул. кремлевская, д. 8', '+78431234568', 'иванова елена михайловна'); insert into customer (name, address, phone, contact_person) values ('ооо книги-маркет', 'г. новосибирск, пр. красный, д. 20', '+73831234567', 'петров сергей николаевич'); insert into customer (name, address, phone, contact_person) values ('мбук дом книги', 'г. екатеринбург, ул. ленина, д. 5', '+73431234567', 'смирнов александр петрович'); insert into orders (book_code, customer_id, order_date, execute_date, quantity) values ('b001', 1, '2025-03-12', '2025-03-25', 100); insert into orders (book_code, customer_id, order_date, execute_date, quantity) values ('b001', 2, '2025-03-15', '2025-03-28', 50); insert into orders (book_code, customer_id, order_date, execute_date, quantity) values ('b002', 1, '2025-04-08', '2025-04-20', 200); insert into orders (book_code, customer_id, order_date, execute_date, quantity) values ('b003', 3, '2025-05-18', '2025-05-31', 75); insert into orders (book_code, customer_id, order_date, execute_date, quantity) values ('b003', 4, '2025-05-20', '2025-06-01', 120); insert into orders (book_code, customer_id, order_date, execute_date, quantity) values ('b004', 2, '2025-06-22', null, 30); insert into orders (book_code, customer_id, order_date, execute_date, quantity) values ('b004', 5, '2025-06-25', null, 45); insert into orders (book_code, customer_id, order_date, execute_date, quantity) values ('b005', 1, '2025-07-05', '2025-07-15', 60); insert into orders (book_code, customer_id, order_date, execute_date, quantity) values ('b005', 3, '2025-07-08', null, 80); select 'писатели' as таблица, count(*) as количество from writer union all select 'контракты', count(*) from contract union all select 'книги', count(*) from book union all select 'связи книг и писателей', count(*) from book_writer union all select 'заказчики', count(*) from customer union all select 'заказы', count(*) from orders; select o.order_id as номер_заказа, c.name as заказчик, b.title as книга, o.quantity as количество, o.order_date as дата_заказа, o.execute_date as дата_выполнения from orders o join book b on o.book_code = b.book_code join customer c on o.customer_id = c.customer_id order by o.order_date desc;