SQLize | PHPize | SQLtest

-- Таблица адресов CREATE TABLE addresses ( id SERIAL PRIMARY KEY, country VARCHAR(100), region VARCHAR(100), district VARCHAR(100), city VARCHAR(100), street VARCHAR(100), house VARCHAR(20), building VARCHAR(20), appartment VARCHAR(20) ); -- Таблица мест работы CREATE TABLE works ( id SERIAL PRIMARY KEY, work_place INT REFERENCES addresses(id), unp VARCHAR(20), name VARCHAR(200), ueo VARCHAR(100), position VARCHAR(100), position_status VARCHAR(100), social_status VARCHAR(100), phone_number VARCHAR(20) ); -- Таблица персональных данных CREATE TABLE criminal_personal_data ( id SERIAL PRIMARY KEY, is_citizen BOOLEAN, citizenship VARCHAR(100), sex VARCHAR(1), birth_date DATE, last_name VARCHAR(100), first_name VARCHAR(100), surname VARCHAR(100), education VARCHAR(100), phone_number VARCHAR(20) ); -- Таблица документов CREATE TABLE criminal_documents ( id SERIAL PRIMARY KEY, doc_type VARCHAR(100), doc_num VARCHAR(50), doc_id VARCHAR(50), issuing_date DATE, issued_by VARCHAR(200), validity_data VARCHAR(100) ); -- Таблица лиц, связанных с преступлениями CREATE TABLE criminal_persons ( id SERIAL PRIMARY KEY, document INT REFERENCES criminal_documents(id), personal_data INT REFERENCES criminal_personal_data(id), birth_place INT REFERENCES addresses(id), residence_place INT REFERENCES addresses(id), work INT REFERENCES works(id) ); -- Таблица уголовных дел CREATE TABLE criminal_cases ( id SERIAL PRIMARY KEY, authority VARCHAR(200), department VARCHAR(200), delo_reg_num VARCHAR(50), delo_reg_date DATE, offender_characteristics TEXT, criminal_applicant INT REFERENCES criminal_persons(id), criminal_suspect INT REFERENCES criminal_persons(id), result_date DATE ); -- Заполняем таблицу адресов (20+ записей) INSERT INTO addresses (country, region, district, city, street, house, building, appartment) VALUES ('Беларусь', 'Минская область', 'Минский район', 'Минск', 'ул. Ленина', '10', NULL, '15'), ('Беларусь', 'Минская область', 'Минский район', 'Минск', 'пр. Независимости', '25', '2', '34'), ('Беларусь', 'Гомельская область', 'Гомельский район', 'Гомель', 'ул. Советская', '45', NULL, '12'), ('Беларусь', 'Брестская область', 'Брестский район', 'Брест', 'ул. Гоголя', '8', NULL, '5'), ('Беларусь', 'Витебская область', 'Витебский район', 'Витебск', 'ул. Пушкина', '17', '1', '23'), ('Беларусь', 'Гродненская область', 'Гродненский район', 'Гродно', 'ул. Ожешко', '3', NULL, '7'), ('Беларусь', 'Могилёвская область', 'Могилёвский район', 'Могилёв', 'ул. Первомайская', '33', NULL, '41'), ('Россия', 'Московская область', NULL, 'Москва', 'ул. Тверская', '15', '3', '10'), ('Россия', 'Ленинградская область', NULL, 'Санкт-Петербург', 'Невский пр.', '22', NULL, '18'), ('Украина', 'Киевская область', NULL, 'Киев', 'ул. Крещатик', '25', NULL, '3'), ('Беларусь', 'Минская область', 'Минский район', 'Минск', 'ул. Козлова', '7', NULL, '32'), ('Беларусь', 'Минская область', 'Минский район', 'Минск', 'ул. Притыцкого', '62', '4', '56'), ('Беларусь', 'Гомельская область', 'Гомельский район', 'Гомель', 'ул. Ильича', '13', NULL, '9'), ('Беларусь', 'Брестская область', 'Брестский район', 'Брест', 'ул. Мицкевича', '5', '2', '14'), ('Беларусь', 'Витебская область', 'Витебский район', 'Витебск', 'ул. Ленинградская', '28', NULL, '17'), ('Беларусь', 'Гродненская область', 'Гродненский район', 'Гродно', 'ул. Дзержинского', '11', NULL, '6'), ('Беларусь', 'Могилёвская область', 'Могилёвский район', 'Могилёв', 'ул. Пионерская', '9', '1', '22'), ('Россия', 'Московская область', NULL, 'Москва', 'ул. Арбат', '12', NULL, '8'), ('Россия', 'Ленинградская область', NULL, 'Санкт-Петербург', 'ул. Марата', '34', '3', '25'), ('Украина', 'Киевская область', NULL, 'Киев', 'ул. Богдана Хмельницкого', '17', NULL, '11'); -- Заполняем таблицу мест работы (20+ записей) INSERT INTO works (work_place, unp, name, ueo, position, position_status, social_status, phone_number) VALUES (1, 'unp1', 'name1', 'ueo1', 'pos1', 'pos_stat1', 'soc_stat1', '+375291111111'), (2, 'unp2', 'name2', 'ueo2', 'pos2', 'pos_stat2', 'soc_stat2', '+375291111112'), (3, 'unp3', 'name3', 'ueo3', 'pos3', 'pos_stat3', 'soc_stat3', '+375291111113'), (4, 'unp4', 'name4', 'ueo4', 'pos4', 'pos_stat4', 'soc_stat4', '+375291111114'), (5, 'unp5', 'name5', 'ueo5', 'pos5', 'pos_stat5', 'soc_stat5', '+375291111115'), (6, 'unp6', 'name6', 'ueo6', 'pos6', 'pos_stat6', 'soc_stat6', '+375291111116'), (7, 'unp7', 'name7', 'ueo7', 'pos7', 'pos_stat7', 'soc_stat7', '+375291111117'), (8, 'unp8', 'name8', 'ueo8', 'pos8', 'pos_stat8', 'soc_stat8', '+375291111118'), (9, 'unp9', 'name9', 'ueo9', 'pos9', 'pos_stat9', 'soc_stat9', '+375291111119'); INSERT INTO criminal_personal_data (is_citizen, citizenship, sex, birth_date, last_name, first_name, surname, education, phone_number) VALUES ('True', 'c1', 'M', '2022-12-31', 'Петровский', 'Иннокентий', 'Александрович', 'БГУИР', '+375292222222'), ('True', 'c1', 'M', '2022-12-30', 'Иннокентьевский', 'Петр', 'Александрович', 'БГУИР', '+375293333333'); INSERT INTO criminal_documents (doc_type, doc_num, doc_id, issuing_date, issued_by, validity_data) VALUES ('паспорт', 'АВ1234567', '11', '2012-12-31', 'iss1', 'Просрочен'), ('паспорт', 'АВ7654321', '11', '2012-12-30', 'iss2', 'Просрочен'); INSERT INTO criminal_persons (document, personal_data, birth_place, residence_place, work) VALUES (1, 1, 1, 1, 1), (2, 2, 1, 1, 1); INSERT INTO criminal_cases (authority, department, delo_reg_num, delo_reg_date, offender_characteristics, criminal_applicant, criminal_suspect, result_date) VALUES ('Комитет отладки', 'Отдел борьбы с багами', 'Номер 1', '2025-01-30', 'Норм чел', 1, 2, '2025-06-20'); SELECT cc.delo_reg_num AS "Номер дела", cc.delo_reg_date AS "Дата регистрации", cc.department AS "Отдел", -- Информация о заявителе cpd_app.last_name || ' ' || cpd_app.first_name || ' ' || cpd_app.surname AS "Заявитель", cd_app.doc_type || ' ' || cd_app.doc_num AS "Документ заявителя", -- Информация о подозреваемом cpd_sus.last_name || ' ' || cpd_sus.first_name || ' ' || cpd_sus.surname AS "Подозреваемый", cd_sus.doc_type || ' ' || cd_sus.doc_num AS "Документ подозреваемого", cc.offender_characteristics AS "Характеристика", cc.result_date AS "Дата решения" FROM criminal_cases cc JOIN criminal_persons cp_app ON cc.criminal_applicant = cp_app.id JOIN criminal_personal_data cpd_app ON cp_app.personal_data = cpd_app.id JOIN criminal_documents cd_app ON cp_app.document = cd_app.id JOIN criminal_persons cp_sus ON cc.criminal_suspect = cp_sus.id JOIN criminal_personal_data cpd_sus ON cp_sus.personal_data = cpd_sus.id JOIN criminal_documents cd_sus ON cp_sus.document = cd_sus.id ORDER BY cc.delo_reg_date DESC; SELECT cpd.last_name || ' ' || cpd.first_name || ' ' || cpd.surname AS "Подозреваемый", cd.doc_type AS "Тип документа", cd.doc_num AS "Номер документа", cd.issuing_date AS "Дата выдачи", cd.validity_data AS "Статус документа", cc.delo_reg_num AS "Номер дела", cc.delo_reg_date AS "Дата регистрации дела" FROM criminal_cases cc JOIN criminal_persons cp ON cc.criminal_suspect = cp.id JOIN criminal_personal_data cpd ON cp.personal_data = cpd.id JOIN criminal_documents cd ON cp.document = cd.id WHERE cd.validity_data = 'Просрочен' ORDER BY cpd.last_name; SELECT department AS "Отдел", COUNT(*) AS "Количество дел", STRING_AGG(delo_reg_num, ', ') AS "Номера дел" FROM criminal_cases GROUP BY department ORDER BY COUNT(*) DESC; SELECT cc.delo_reg_num AS "Номер дела", cc.delo_reg_date AS "Дата регистрации", cpd.last_name || ' ' || cpd.first_name || ' ' || cpd.surname AS "Подозреваемый", a.city || ', ' || a.street || ', ' || a.house AS "Адрес проживания", w.name AS "Место работы", cc.offender_characteristics AS "Характеристика" FROM criminal_cases cc JOIN criminal_persons cp ON cc.criminal_suspect = cp.id JOIN criminal_personal_data cpd ON cp.personal_data = cpd.id JOIN addresses a ON cp.residence_place = a.id LEFT JOIN works w ON cp.work = w.id WHERE cpd.last_name LIKE 'Иннокентьевский%' ORDER BY cc.delo_reg_date DESC;