SQLize Online / PHPize Online  /  SQLtest Online

create table providers (id serial primary key, name varchar(100)); create table manufacturers (id serial primary key, name varchar(100)); create table countries (id serial primary key, name varchar(100)); create table products (id serial primary key, type varchar(100), name varchar(100), year_of_issue date, date_of_sale date, providers_id int references providers(id), manufacturers_id int references manufacturers(id), country_id int references countries(id), price int, target_audience varchar(100)); create table customers (id serial primary key, lastname varchar(100), firstname varchar(100), city varchar(100), products_id int references products(id)); insert into providers (name) values ('ООО "ТехноМаркет"'), ('ЗАО "Энергия Плюс"'), ('ООО "Глобал Трейд"'), ('ПАО "ТрансСнаб"'), ('ЗАО "Альфа Логистик"'), ('ООО "ИнтерСнаб"'), ('ПАО "ЮниТранс"'), ('ООО "Мегаполис-Сервис"'), ('ЗАО "СеверТорг"'), ('ПАО "Логистик-Системс"'); insert into manufacturers (name) values ('ОАО "АвтоМаш"'), ('ЗАО "Электроника-Про"'), ('ООО "Кварц-Тех"'), ('ПАО "ПромСтрой"'), ('ОАО "ГидроСистемы"'), ('ООО "МеталлИндустрия"'), ('ЗАО "ТехноТранс"'), ('ПАО "Энергетика-М"'), ('ООО "Кристалл-Инжиниринг"'), ('ОАО "СтройПроект"'); insert into countries (name) values ('Россия'), ('США'), ('Германия'), ('Франция'), ('Италия'), ('Китай'), ('Япония'), ('Бразилия'), ('Индия'), ('Австралия'); insert into products (id, type, name, year_of_issue, date_of_sale, providers_id, manufacturers_id, country_id, price, target_audience) VALUES (1, 'духи', 'Chanel No. 5', '1921-01-01', '2024-12-13', 1, 1, 1, 15000, 'женщины'), (2, 'духи', 'Dior Sauvage', '2015-01-01', '2020-11-13', 2, 2, 2, 12000, 'мужчины'), (3, 'туалетная вода', 'Acqua di Gio', '1996-01-01', '2021-10-01', 3, 3, 3, 8000, 'мужчины'), (4, 'парфюмированная вода', 'Guerlain La Petite Robe Noire', '2012-01-01', '2002-05-20', 4, 4, 4, 9500, 'женщины'), (5, 'духи', 'Yves Saint Laurent Black Opium', '2014-01-01', '2018-03-14', 5, 5, 5, 11000, 'женщины'), (6, 'туалетная вода', 'Hugo Boss Bottled', '1998-01-01', '2000-11-13', 6, 6, 6, 9000, 'мужчины'), (7, 'парфюмированная вода', 'Tom Ford Black Orchid', '2006-01-01', '2024-01-17', 7, 7, 7, 14000, 'женщины'), (8, 'духи', 'Chanel Bleu de Chanel', '2010-01-01', '2024-12-13', 1, 1, 1, 13500, 'мужчины'), (9, 'туалетная вода', 'CK One', '1994-01-01', '2023-08-15', 8, 8, 8, 5000, 'дети'), (10, 'парфюмированная вода', 'Lancome La Vie Est Belle', '2012-01-01', '2013-08-30', 9, 9, 9, 10500, 'женщины'), (11, 'духи', 'Creed Aventus', '2010-01-01', '2019-12-19', 10, 10, 10, 25000, 'мужчины'), (12, 'туалетная вода', 'Davidoff Cool Water', '1988-01-01', '2024-12-13', 1, 2, 3, 4500, 'мужчины'), (13, 'парфюмированная вода', 'Chloe Eau de Parfum', '2008-01-01', '2012-09-29', 4, 3, 4, 11500, 'женщины'), (14, 'духи', 'Tom Ford Tobacco Vanille', '2007-01-01', '2007-12-31', 5, 6, 7, 22000, 'дети'), (15, 'туалетная вода', 'Viktor and Rolf Flowerbomb', '2005-01-01', '2024-12-20', 6, 8, 8, 13000, 'женщины'), (16, 'парфюмированная вода', 'Bvlgari Omnia Crystalline', '2005-01-01', '2007-11-22', 7, 9, 9, 9500, 'женщины'), (17, 'духи', 'Hermes Terre', '2006-01-01', '2009-01-25', 8, 10, 1, 17000, 'мужчины'), (18, 'туалетная вода', 'Jean Paul Gaultier Le Male', '1995-01-01', '2000-02-27', 9, 1, 2, 8000, 'мужчины'), (19, 'парфюмированная вода', 'Prada Candy', '2011-01-01', '2024-06-06', 10, 4, 6, 12000, 'женщины'), (20, 'духи', 'Baby Dior', '2013-01-01', '2023-04-11', 2, 5, 3, 5500, 'дети'); insert into customers (id, lastname, firstname, city, products_id) VALUES (1, 'Иванов', 'Алексей', 'Москва', 1), (2, 'Петров', 'Иван', 'Санкт-Петербург', 2), (3, 'Смирнова', 'Мария', 'Новосибирск', 3), (4, 'Кузнецова', 'Екатерина', 'Екатеринбург', 4), (5, 'Попова', 'Анна', 'Нижний Новгород', 5), (6, 'Васильев', 'Михаил', 'Краснодар', 6), (7, 'Михайлова', 'Ольга', 'Ростов-на-Дону', 7), (8, 'Новиков', 'Дмитрий', 'Казань', 8), (9, 'Ковалев', 'Юрий', 'Челябинск', 9), (10, 'Соловьева', 'Ирина', 'Воронеж', 10), (11, 'Григорьев', 'Петр', 'Уфа', 11), (12, 'Дмитриева', 'Светлана', 'Пермь', 12), (13, 'Федорова', 'Татьяна', 'Красноярск', 13), (14, 'Сергеев', 'Андрей', 'Тюмень', 14), (15, 'Шевченко', 'Наталья', 'Омск', 15), (16, 'Беляев', 'Владимир', 'Саратов', 16), (17, 'Захарова', 'Елена', 'Волгоград', 17), (18, 'Яковлев', 'Константин', 'Тверь', 18), (19, 'Крылова', 'Марина', 'Ярославль', 19), (20, 'Романов', 'Григорий', 'Арзамас', 20); -----------1--------------- create function products_info() returns table (id int, type varchar(100), name varchar(100), year_of_issue date, date_of_sale date, provider_name varchar(100), manufacturer_name varchar(100), country_name varchar(100),price int, target_audience varchar(100)) as $$ begin return query select p.id, p.type, p.name, p.year_of_issue, p.date_of_sale, pr.name as provider_name, m.name as manufacturer_name, c.name as country_name, p.price, p.target_audience from products p join providers pr on p.providers_id = pr.id join manufacturers m on p.manufacturers_id = m.id join countries c on p.country_id = c.id order by p.type; end; $$ language plpgsql; -----------2--------------- create or replace function sort_year_price(selectmode text) returns table (id int, type varchar(100), name varchar(100), year_of_issue date, date_of_sale date, provider_name varchar(100), manufacturer_name varchar(100), country_name varchar(100),price int, target_audience varchar(100)) as $$ begin if(selectmode = 'year') then return query(select p.id, p.type, p.name, p.year_of_issue, p.date_of_sale, pr.name as provider_name, m.name as manufacturer_name, c.name as country_name, p.price, p.target_audience from products p join providers pr on p.providers_id = pr.id join manufacturers m on p.manufacturers_id = m.id join countries c on p.country_id = c.id order by date_of_sale); end if; if(selectmode='price') then return query(select p.id, p.type, p.name, p.year_of_issue, p.date_of_sale, pr.name as provider_name, m.name as manufacturer_name, c.name as country_name, p.price, p.target_audience from products p join providers pr on p.providers_id = pr.id join manufacturers m on p.manufacturers_id = m.id join countries c on p.country_id = c.id order by price); end if; end; $$ language plpgsql; -----------3--------------- create function product_price_stats(product_type varchar(100) = null) returns table(min_price int, max_price int, avg_price numeric) as $$ begin if product_type is null then return query select min(price), max(price), avg(price) from products; else return query select min(price), max(price), avg(price) from products where type = product_type; end if; end; $$ language plpgsql; -----------4--------------- create function search_price(pricestart float, priceend float, product_type varchar(100) = null) returns setof products as $$ begin if product_type is null then return query (select* from products where products.price between pricestart and priceend); else return query (select* from products where (products.price between pricestart and priceend) and type = product_type); end if; end; $$ language plpgsql; -----------5--------------- create function search_on_manuf(select_manuf varchar(100)) returns table (id int, type varchar(100), name varchar(100), year_of_issue date, date_of_sale date, providers_id int, manufacturer_name varchar(100), country_id int, price int, target_audience varchar(100)) as $$ begin return query select p.id, p.type, p.name, p.year_of_issue, p.date_of_sale, p.providers_id, m.name as manufacturer_name, p.country_id, p.price, p.target_audience from products p join manufacturers m on p.manufacturers_id = m.id where m.name = select_manuf; end; $$ language plpgsql; -----------6--------------- create function search_products_price_manuf(manuf text) returns table(id int, "type" varchar(100), "name" varchar(100), year_of_issue date, providers_id int, manufacturers_id int, country_id int, price int, avgprice numeric, "Manufacturername" varchar(100)) as $$ declare avgprice numeric; begin avgprice := avg(products.price) from products join manufacturers on products.manufacturers_id = manufacturers.id where manufacturers."name" = manuf; return query (select products.id, products."type", products."name", products.year_of_issue, products.providers_id, products.manufacturers_id, products.country_id, products.price, (select avgprice) as avgprice, manufacturers."name" from products join manufacturers on products.manufacturers_id = manufacturers.id where products.price > avgprice); end; $$ language plpgsql; select * from search_products_price_manuf('ОАО "АвтоМаш"');