SQLize | PHPize | SQLtest

DROP TABLE IF EXISTS db.user; DROP TABLE IF EXISTS db.review; DROP TABLE IF EXISTS db.order; DROP TABLE IF EXISTS db.request; DROP TABLE IF EXISTS db.user_map_review; DROP TABLE IF EXISTS db.user_map_order; DROP TABLE IF EXISTS db.user_map_request; DROP TABLE IF EXISTS db.request_map_order; DROP SCHEMA IF EXISTS db; CREATE SCHEMA db; CREATE TABLE IF NOT EXISTS db.user( user_id INTEGER NOT NULL, password CHARACTER VARYING(200) NOT NULL, name CHARACTER VARYING(200) NOT NULL, surname CHARACTER VARYING(200) NOT NULL, telephone CHARACTER VARYING(20) NOT NULL, rating NUMERIC NOT NULL, join_date TIMESTAMP NOT NULL, last_active_date TIMESTAMP NOT NULL, CONSTRAINT user_pk PRIMARY KEY (user_id) ); CREATE TABLE IF NOT EXISTS db.review( review_id INTEGER NOT NULL, creation_date TIMESTAMP NOT NULL, grade NUMERIC NOT NULL, content CHARACTER VARYING(5000) NOT NULL, CONSTRAINT review_pk PRIMARY KEY (review_id) ); CREATE TABLE IF NOT EXISTS db.order( order_id INTEGER NOT NULL, creation_date TIMESTAMP NOT NULL, description CHARACTER VARYING(5000) NOT NULL, status CHARACTER VARYING(50) NOT NULL, CONSTRAINT order_pk PRIMARY KEY (order_id) ); CREATE TABLE IF NOT EXISTS db.request( request_id INTEGER NOT NULL, creation_date TIMESTAMP NOT NULL, description CHARACTER VARYING(5000) NOT NULL, status CHARACTER VARYING(50) NOT NULL, CONSTRAINT request_pk PRIMARY KEY (request_id) ); CREATE TABLE IF NOT EXISTS db.user_map_review( review_id INTEGER NOT NULL, author_id INTEGER NOT NULL, rated_person_id INTEGER NOT NULL, CONSTRAINT fk_review FOREIGN KEY (review_id) REFERENCES db.review(review_id) ON DELETE NO ACTION, CONSTRAINT fk_author FOREIGN KEY (author_id) REFERENCES db.user(user_id) ON DELETE NO ACTION, CONSTRAINT fk_rated_person FOREIGN KEY (rated_person_id) REFERENCES db.user(user_id) ON DELETE NO ACTION ); CREATE TABLE IF NOT EXISTS db.user_map_order( order_id INTEGER NOT NULL, customer_id INTEGER NOT NULL, CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES db.order(order_id) ON DELETE NO ACTION, CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES db.user(user_id) ON DELETE NO ACTION ); CREATE TABLE IF NOT EXISTS db.user_map_request( request_id INTEGER NOT NULL, freelancer_id INTEGER NOT NULL, CONSTRAINT fk_request FOREIGN KEY (request_id) REFERENCES db.request(request_id) ON DELETE NO ACTION, CONSTRAINT fk_freelancer FOREIGN KEY (freelancer_id) REFERENCES db.user(user_id) ON DELETE NO ACTION ); CREATE TABLE IF NOT EXISTS db.request_map_order( request_id INTEGER NOT NULL, order_id INTEGER NOT NULL, CONSTRAINT fk_request FOREIGN KEY (request_id) REFERENCES db.request(request_id) ON DELETE NO ACTION, CONSTRAINT fk_order FOREIGN KEY (order_id) REFERENCES db.order(order_id) ON DELETE NO ACTION ); INSERT INTO db.user (user_id, password, name, surname, telephone, rating, join_date, last_active_date) VALUES (1, 'password1', 'Gandalf', 'Stormcrow', '+1-202-555-0163', 4.5, '2023-05-08 12:00:00', '2023-05-09 13:00:00'), (2, 'password2', 'Leia', 'Organa', '+44-20-5555-0192', 4.2, '2023-05-07 12:00:00', '2023-05-08 13:00:00'), (3, 'password3', 'Neo', 'Anderson', '+33-1-5555-0115', 3.8, '2023-05-06 12:00:00', '2023-05-07 13:00:00'), (4, 'password4', 'Ellen', 'Ripley', '+81-3-5555-0146', 4.1, '2023-05-05 12:00:00', '2023-05-06 13:00:00'), (5, 'password5', 'Harry', 'Potter', '+86-10-5555-0177', 4.3, '2023-05-04 12:00:00', '2023-05-05 13:00:00'), (6, 'password6', 'Marty', 'McFly', '+61-2-5555-0208', 4.6, '2023-05-03 12:00:00', '2023-05-04 13:00:00'), (7, 'password7', 'Tony', 'Stark', '+55-21-5555-0239', 4.0, '2023-05-02 12:00:00', '2023-05-03 13:00:00'), (8, 'password8', 'Hermione', 'Granger', '+49-30-5555-0270', 4.4, '2023-05-01 12:00:00', '2023-05-02 13:00:00'), (9, 'password9', 'Luke', 'Skywalker', '+61-3-5555-0301', 3.9, '2023-04-30 12:00:00', '2023-05-01 13:00:00'), (10, 'password10', 'Katniss', 'Everdeen', '+91-22-5555-0332', 4.7, '2023-04-29 12:00:00', '2023-04-30 13:00:00'); INSERT INTO db.review (review_id, creation_date, grade, content) VALUES (1, '2023-05-08 12:00:00', 4.5, 'Отличный продукт, очень доволен!'), (2, '2023-05-07 12:00:00', 4.2, 'Хороший продукт, но есть некоторые проблемы.'), (3, '2023-05-06 12:00:00', 3.8, 'Средний продукт, ничего особенного.'), (4, '2023-05-05 12:00:00', 4.1, 'Хороший продукт, но немного дорогой.'), (5, '2023-05-04 12:00:00', 4.3, 'Отличный продукт, рекомендую!'), (6, '2023-05-03 12:00:00', 4.6, 'Превосходный продукт, очень доволен!'), (7, '2023-05-02 12:00:00', 4.0, 'Хороший продукт, но есть некоторые недостатки.'), (8, '2023-05-01 12:00:00', 4.4, 'Отличный продукт, стоит своих денег.'), (9, '2023-04-30 12:00:00', 3.9, 'Средний продукт, но можно найти лучше.'), (10, '2023-04-29 12:00:00', 4.7, 'Превосходный продукт, рекомендую всем!'); INSERT INTO db.order (order_id, creation_date, description, status) VALUES (1, '2023-05-08 12:00:00', 'Разработка веб-сайта для компании X', 'В процессе'), (2, '2023-05-07 12:00:00', 'Разработка мобильного приложения для компании Y', 'Завершен'), (3, '2023-05-06 12:00:00', 'Разработка программного обеспечения для компании Z', 'Отменен'), (4, '2023-05-05 12:00:00', 'Разработка игры для компании A', 'В процессе'), (5, '2023-05-04 12:00:00', 'Разработка приложения для управления проектами', 'Завершен'), (6, '2023-05-03 12:00:00', 'Разработка системы управления складом для компании B', 'В процессе'), (7, '2023-05-02 12:00:00', 'Разработка программного обеспечения для автоматизации бизнес-процессов', 'Завершен'), (8, '2023-05-01 12:00:00', 'Разработка веб-приложения для онлайн-магазина', 'В процессе'), (9, '2023-04-30 12:00:00', 'Разработка приложения для управления финансами', 'Отменен'), (10, '2023-04-29 12:00:00', 'Разработка программного обеспечения для управления производственными процессами', 'В процессе'); INSERT INTO db.request (request_id, creation_date, description, status) VALUES (1, '2023-05-08 12:00:00', 'Требуется разработчик для создания веб-сайта', 'Открыт'), (2, '2023-05-07 12:00:00', 'Требуется разработчик для создания мобильного приложения', 'Закрыт'), (3, '2023-05-06 12:00:00', 'Требуется разработчик для создания программного обеспечения', 'Открыт'), (4, '2023-05-05 12:00:00', 'Требуется разработчик для создания игры', 'Закрыт'), (5, '2023-05-04 12:00:00', 'Требуется разработчик для создания приложения для управления проектами', 'Открыт'), (6, '2023-05-03 12:00:00', 'Требуется разработчик для создания системы управления складом', 'Закрыт'), (7, '2023-05-02 12:00:00', 'Требуется разработчик для создания программного обеспечения для автоматизации бизнес-процессов', 'Открыт'), (8, '2023-05-01 12:00:00', 'Требуется разработчик для создания веб-приложения для онлайн-магазина', 'Закрыт'), (9, '2023-04-30 12:00:00', 'Требуется разработчик для создания приложения для управления финансами', 'Открыт'), (10, '2023-04-29 12:00:00', 'Требуется разработчик для создания программного обеспечения для управления производственными процессами', 'Закрыт'); INSERT INTO db.user_map_review (review_id, author_id, rated_person_id) VALUES (1, 1, 2), (2, 3, 4), (3, 5, 6), (4, 7, 8), (5, 9, 10), (6, 2, 1), (7, 4, 3), (8, 6, 5), (9, 8, 7), (10, 9, 9); INSERT INTO db.user_map_order (order_id, customer_id) VALUES (1, 2), (2, 4), (3, 6), (4, 8), (5, 10), (6, 1), (7, 3), (8, 5), (9, 7), (8, 9); INSERT INTO db.user_map_request (request_id, freelancer_id) VALUES (1, 1), (2, 3), (3, 5), (4, 7), (5, 9), (6, 2), (7, 4), (8, 6), (9, 8), (10, 10); INSERT INTO db.request_map_order (request_id, order_id) VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10); -- Пользователи которые написали отзывы о заказах, но не создавали заказов SELECT DISTINCT u.user_id, u.name FROM db.user u INNER JOIN db.user_map_review umr ON u.user_id = umr.author_id LEFT JOIN db.user_map_order umo ON u.user_id = umo.customer_id WHERE umo.customer_id IS NULL; -- Заказы, которые созданы более 5 дней назад и еще не завершены SELECT * FROM db.order WHERE status != 'Завершен' AND creation_date < CURRENT_DATE - 5; -- Обновление описания запроса UPDATE db.request SET description = 'Новое описание запроса' WHERE request_id = 5; -- Пользователи, которые делали заказы, но не написали отзывов SELECT DISTINCT u.user_id, u.name FROM db.user u INNER JOIN db.user_map_order umo ON u.user_id = umo.customer_id LEFT JOIN db.user_map_review umrev ON u.user_id = umrev.author_id WHERE umrev.review_id IS NULL; -- Новая запись INSERT INTO db.order (order_id, creation_date, description, status) VALUES (11, '2023-05-08 12:00:00', 'Разработка веб-сайта для компании X', 'В процессе'); -- Удаляем маппинг DELETE FROM db.user_map_review WHERE review_id = 3; -- Удаляем отзыв DELETE FROM db.review WHERE review_id = 3; -- Все отзывы SELECT * FROM db.review; -- Возвращаем обратно отзыв INSERT INTO db.review (review_id, creation_date, grade, content) VALUES (3, '2023-05-06 12:00:00', 3.8, 'Средний продукт, ничего особенного.'); -- Возвращаем обратно маппинг INSERT INTO db.user_map_review (review_id, author_id, rated_person_id) VALUES (3, 5, 6); -- Пользователей, которые соз дали >= 1 заказа SELECT customer_id, COUNT(*) AS num_orders FROM db.user_map_order GROUP BY customer_id HAVING COUNT(*) >= 1 ORDER BY num_orders DESC, customer_id; -- Пользователи, отсортированные по количеству отзывов SELECT author_id, COUNT(*) AS num_reviews FROM db.user_map_review GROUP BY author_id ORDER BY num_reviews DESC; -- Количество откликов на каждый из заказов SELECT order_id, COUNT(*) OVER (PARTITION BY order_id) AS num_responses FROM db.request_map_order ORDER BY num_responses DESC; -- Заказы, отсортированные по дате и статусу SELECT order_id, creation_date, status, ROW_NUMBER() OVER (ORDER BY creation_date, status) AS row_num FROM db.order; -- заказы, отсортированные по дате создания, группировка по статусу заказа SELECT umo.order_id, umo.customer_id, o.creation_date, o.status, ROW_NUMBER() OVER (PARTITION BY o.status ORDER BY o.creation_date) AS row_num FROM db.user_map_order umo INNER JOIN db.order o ON o.order_id = umo.order_id; -- пользователи, отсортированне по кол-ву заказов SELECT DISTINCT umo.customer_id, COUNT(*) OVER (PARTITION BY umo.customer_id) AS num_orders, FIRST_VALUE(name) OVER (PARTITION BY umo.customer_id) AS customer_name FROM db.user_map_order umo INNER JOIN db.user u ON umo.customer_id = u.user_id ORDER BY num_orders DESC; -- CREATE OR REPLACE VIEW get_customers_and_orders AS SELECT umo.customer_id, umo.order_id, o.creation_date, o.status FROM db.user_map_order umo INNER JOIN db.order o ON o.order_id = umo.order_id ORDER BY umo.customer_id, umo.order_id; SELECT * FROM get_customers_and_orders; -- CREATE OR REPLACE VIEW get_customers_and_requests AS SELECT umr.freelancer_id, umr.request_id, r.creation_date, r.status FROM db.user_map_request umr INNER JOIN db.request r ON r.request_id = umr.request_id ORDER BY umr.freelancer_id, umr.request_id; SELECT * FROM get_customers_and_requests; -- CREATE OR REPLACE VIEW get_orders_and_requests AS SELECT rmo.order_id, rmo.request_id, o.creation_date as order_creation_date, o.status as order_status FROM db.request_map_order rmo INNER JOIN db.order o ON o.order_id = rmo.order_id ORDER BY rmo.order_id, rmo.request_id; SELECT * FROM get_orders_and_requests; -- CREATE OR REPLACE VIEW get_users_and_reviews AS SELECT umr.rated_person_id, umr.author_id, umr.review_id, r.creation_date, r.grade, r.content FROM db.user_map_review umr INNER JOIN db.review r ON r.review_id = umr.review_id ORDER BY umr.rated_person_id, umr.author_id, umr.review_id; SELECT * FROM get_users_and_reviews; -- Представления с сокрытием полей с персональными данными CREATE OR REPLACE VIEW get_users_info AS SELECT name, surname, CONCAT(SUBSTRING(telephone, 0, LENGTH(telephone) - 9), '-****-****') AS telephone, rating, CONCAT(EXTRACT(YEAR FROM join_date), '-**-**') AS join_date, CONCAT(EXTRACT(YEAR FROM last_active_date), '-**-**') AS last_active_date FROM db.user ORDER BY name, surname; SELECT * FROM get_users_info; -- Представления с сокрытием полей с персональными данными CREATE OR REPLACE VIEW get_reviews_info AS SELECT u.name, u.surname, '***' as author_id, CONCAT(EXTRACT(YEAR FROM r.creation_date), '-**-**') AS creation_date, r.grade, r.content FROM db.user_map_review umr INNER JOIN db.review r ON r.review_id = umr.review_id INNER JOIN db.user u ON u.user_id = umr.rated_person_id ORDER BY u.name, u.surname, umr.author_id, umr.review_id; SELECT * FROM get_reviews_info; -- Добавляем новый заказ, связываем его с заказчиком CREATE OR REPLACE PROCEDURE add_order_and_link_customer( customer_id INTEGER, description CHARACTER VARYING(5000), status CHARACTER VARYING(50) ) AS $$ DECLARE order_id_ INTEGER := 0; BEGIN SELECT MAX(o.order_id) INTO order_id_ FROM db.order o; order_id_ = order_id_ + 1; INSERT INTO db.order (order_id, creation_date, description, status) VALUES (order_id_, NOW(), description, status); INSERT INTO db.user_map_order (order_id, customer_id) VALUES (order_id_, customer_id); END; $$ LANGUAGE plpgsql; CALL add_order_and_link_customer(1, 'Новый заказ', 'В процессе'); CREATE OR REPLACE FUNCTION get_average_user_rating(user_id INTEGER) RETURNS NUMERIC AS $$ DECLARE avg_rating NUMERIC := 0; BEGIN SELECT AVG(r.grade) INTO avg_rating FROM db.review r INNER JOIN db.user_map_review umr ON r.review_id = umr.review_id WHERE umr.rated_person_id = user_id; RETURN avg_rating; END; $$ LANGUAGE plpgsql; SELECT get_average_user_rating(1); CREATE OR REPLACE FUNCTION update_user_last_active_date() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN NEW.last_active_date = NOW(); RETURN NEW; END; $$; CREATE TRIGGER user_update_trigger BEFORE UPDATE ON db.user FOR EACH ROW EXECUTE FUNCTION update_user_last_active_date(); INSERT INTO db.user (user_id, password, name, surname, telephone, rating, join_date, last_active_date) VALUES (100, 'password1', 'Gandalf', 'Stormcrow', '+1-202-555-0163', 4.5, '2023-05-08 12:00:00', '2023-05-09 13:00:00');