SQLize | PHPize | SQLtest

CREATE TABLE employee( id_employee INTEGER not null primary key AUTO_INCREMENT, fio_employee varchar(100) not null, data_employee date not null, data_work_employee date not null); CREATE TABLE pool( id_pool INTEGER not null primary key AUTO_INCREMENT, appointment_pool varchar(100) not null, time_pool int not null); CREATE TABLE sayna( id_sayna INTEGER not null primary key AUTO_INCREMENT, temper_sayna integer not null, time_sayna integer not null); CREATE TABLE gym( id_gym INTEGER not null primary key AUTO_INCREMENT, time_gym integer not null, square_gym integer not null); CREATE TABLE trainer( id_trainer INTEGER not null primary key AUTO_INCREMENT, fio_trainer varchar(100) not null, data_trainer date not null, data_work_trainer date not null); CREATE TABLE visitor( id_visitor INTEGER not null primary key AUTO_INCREMENT, fio_visitor varchar(100) not null, age_visitor integer not null, money_visitor integer not null); CREATE TABLE subscription( ID_subscription INTEGER not null primary key AUTO_INCREMENT, id_sayna integer default null, id_pool integer default null, id_gym integer default null, money_subscription integer not null, validity_subscription integer not null, data_buy date not null, FOREIGN KEY (id_sayna) REFERENCES sayna (id_sayna) ON DELETE CASCADE, FOREIGN KEY (id_pool) REFERENCES pool (id_pool) ON DELETE CASCADE, FOREIGN KEY (id_gym) REFERENCES gym (id_gym) ON DELETE CASCADE ); CREATE TABLE medicine( id_medicine INTEGER not null primary key AUTO_INCREMENT, id_employee int, time_medicine INT not null, square_medicine int not null, FOREIGN KEY (id_employee) REFERENCES employee (id_employee) ON DELETE CASCADE ); CREATE TABLE swimm( id_swimm INTEGER not null primary key AUTO_INCREMENT, id_visitor integer, id_employee integer, name_swimm varchar(100) not null, time_swimm integer not null, FOREIGN KEY (id_visitor) REFERENCES visitor (id_visitor) ON DELETE CASCADE, FOREIGN KEY (id_employee) REFERENCES employee (id_employee) ON DELETE CASCADE ); CREATE TABLE visitor_to_subscription( id_visitor integer not null, id_subscription integer not null, FOREIGN KEY (id_visitor) REFERENCES visitor (id_visitor) ON DELETE CASCADE, FOREIGN KEY (id_subscription) REFERENCES subscription (id_subscription) ON DELETE CASCADE, PRIMARY key (id_visitor, id_subscription) ); CREATE TABLE medicine_to_employee( id_medicine integer not null, id_employee integer not null, FOREIGN KEY (id_medicine) REFERENCES medicine (id_medicine) ON DELETE CASCADE, FOREIGN KEY (id_employee) REFERENCES employee (id_employee) ON DELETE CASCADE, PRIMARY key (id_medicine, id_employee) ); CREATE TABLE swimm_to_employee( id_swimm integer not null, id_employee integer not null, FOREIGN KEY (id_swimm) REFERENCES swimm (id_swimm), FOREIGN KEY (id_employee) REFERENCES employee (id_employee), PRIMARY key (id_swimm, id_employee) ); CREATE TABLE sayna_to_employee( id_sayna int not null, id_employee int not null, FOREIGN KEY (id_sayna) REFERENCES sayna (id_sayna) ON DELETE CASCADE, FOREIGN KEY (id_employee) REFERENCES employee (id_employee) ON DELETE CASCADE, PRIMARY key (id_sayna, id_employee) ); CREATE TABLE trainer_to_pool( id_trainer integer not null, id_pool integer not null, FOREIGN KEY (id_trainer) REFERENCES trainer (id_trainer) ON DELETE CASCADE, FOREIGN KEY (id_pool) REFERENCES pool (id_pool) ON DELETE CASCADE, PRIMARY key (id_trainer, id_pool) ); CREATE TABLE trainer_to_gym( id_trainer integer not null, id_gym integer not null, FOREIGN KEY (id_trainer) REFERENCES trainer (id_trainer) ON DELETE CASCADE, FOREIGN KEY (id_gym) REFERENCES gym (id_gym) ON DELETE CASCADE, PRIMARY key (id_trainer, id_gym) ); CREATE TABLE visitor_to_medicine( id_visitor integer not null, id_medicine integer not null, FOREIGN KEY (id_visitor) REFERENCES visitor (id_visitor) ON DELETE CASCADE, FOREIGN KEY (id_medicine) REFERENCES medicine (id_medicine) ON DELETE CASCADE, PRIMARY key (id_visitor, id_medicine), data_visit date not null ); CREATE TABLE visitor_to_swimm( id_visitor integer not null, id_swimm integer not null, FOREIGN KEY (id_visitor) REFERENCES visitor (id_visitor) ON DELETE CASCADE, FOREIGN KEY (id_swimm) REFERENCES swimm (id_swimm) ON DELETE CASCADE, PRIMARY key (id_visitor, id_swimm), summa_buy int not null ); INSERT into employee (fio_employee, data_employee, data_work_employee) values ("Овсянников Т.А.","2002-05-15","2002-05-15"), ("Калашников Т.М.","2000-10-11","2002-05-15"), ("Исаева Н.К.","2005-06-20","2002-05-15"), ("Климова М.И.","2007-09-28","2002-05-15"), ("Степанов М.З.","2005-05-17","2002-05-15"), ("Федоров И.И.","2010-07-13","2002-05-15"), ("Пономарев В. А.","1996-12-31","2002-05-15"), ("Шестаков В. М.","1998-11-30","2002-05-15"), ("Иванова А. А.","1992-09-27","2002-05-15"), ("Новиков А. К.","2002-10-28","2002-05-15"); INSERT into pool (appointment_pool, time_pool) values ("спортивный",10), ("купальный",8), ("спортивный",7), ("тренировочный",8), ("купальный",9), ("купальный",7), ("тренировочный",9), ("купальный",8), ("спортивный",7), ("тренировочный",10); INSERT into sayna (temper_sayna, time_sayna) values (70, 8), (60, 7), (82, 6), (95, 6), (60, 7), (80, 8), (90, 7), (95, 6), (80, 8), (90, 8); INSERT into gym (square_gym, time_gym) values (220, 9), (240, 8), (270, 7), (190, 9), (230, 8), (210, 8), (190, 7), (250, 9), (215, 7), (215, 9); INSERT into trainer (fio_trainer, data_trainer, data_work_trainer) values ("Антонов А.П.","2000-06-30","2002-05-15"), ("Семенов А.Р.","1990-12-16","2002-05-15"), ("Казанцев Е.Д","2000-08-18","2002-05-15"), ("Попов В. Д.","2001-12-07","2002-05-15"), ("Королева М. В.","1996-11-15","2002-05-15"), ("Казакова В. А.","1990-10-15","2002-05-15"), ("Зайцева В. П.","1989-09-15","2002-05-15"), ("Никифоров Л. Д.","2001-08-15","2002-05-15"), ("Михайлова В. Я.","2000-04-15","2002-05-15"), ("Емельянова У. А.","1998-01-15","2002-05-15"); INSERT into visitor (fio_visitor, age_visitor, money_visitor) values ("Петров А.Д.",26,6000), ("Коняхин В.Д. ",32,5000), ("Золотов К.Г.",48,5000), ("Фомина Д. Р.",34,4500), ("Белкин М. К.",53,7000), ("Акимов Е. А.",17,8000), ("Черных А. М.",68,10000), ("Серова А. М.",55,7500), ("Вдовин Ф. Г.",23,9000), ("Виднов М.П.",42,2000); INSERT into subscription (money_subscription, validity_subscription, data_buy, id_sayna, id_pool, id_gym) values (2500, 12,"2020-02-16",1,2,1), (3000, 30,"2022-05-14",2,NULL,2), (7000, 90,"2021-04-02",3,3,3), (10000, 14,"2022-08-03",4,3,3), (4500, 30,"2023-12-31",NULL,2,3), (5000, 90,"2021-11-27",3,3,NULL), (3500, 90,"2020-10-28",3,NULL,4), (8000, 14,"2022-07-19",2,NULL,NULL), (4590, 30,"2022-09-17",3,3,3), (4799, 14,"2021-01-25",NULL,4,4); INSERT into medicine (time_medicine, square_medicine, id_employee) values (8, 20,4), (8, 15,3), (7, 20,1), (8, 30,2), (8, 20,1), (7, 30,5), (7, 25,1), (7, 20,1), (8, 25,1), (9, 25,3); INSERT into swimm (name_swimm, time_swimm, id_employee) values ("SwinStyle",8,1), ("Salow",9,2), ("Акер",8,1), ("Saloy",8,4), ("Convenience",8,2), ("Salon",8,1), ("Gymnastics",8,4), ("Blue",9,3), ("Digitalswim",9,4), ("Искра",8,1); INSERT into visitor_to_subscription (id_visitor, id_subscription) values (3,5), (5,2), (2,4), (3,2), (1,3), (4,2), (1,4), (2,5), (3,4), (2,3); INSERT into medicine_to_employee (id_medicine, id_employee) values (1,2), (5,2), (5,7), (1,3), (2,5), (1,4), (3,4), (1,1), (5,4), (2,4); INSERT into swimm_to_employee (id_swimm, id_employee) values (1,7), (2,4), (1,2), (1,3), (4,5), (3,5), (1,4), (2,5), (2,3), (5,4); INSERT into sayna_to_employee (id_sayna, id_employee) values (1,2), (3,4), (1,4), (2,3), (5,4), (2,4), (1,5), (1,3), (2,8), (2,5); INSERT into trainer_to_pool (id_trainer, id_pool) values (1,2), (2,5), (1,4), (2,3), (5,4), (2,1), (1,5), (7,3), (2,4), (6,5); INSERT into trainer_to_gym (id_trainer, id_gym) values (1,2), (2,7), (1,4), (9,3), (5,2), (2,8), (1,5), (7,3), (5,4), (6,5); INSERT into visitor_to_medicine (id_visitor, id_medicine, data_visit) values (3,5,"2021-07-16"), (9,4,"2022-08-15"), (2,8,"2023-05-05"), (9,2,"2022-12-12"), (2,3,"2020-07-14"), (6,2,"2021-04-18"), (1,10,"2023-06-27"), (7,5,"2021-05-29"), (3,4,"2022-10-31"), (9,8,"2020-12-30"); INSERT into visitor_to_swimm (id_visitor, id_swimm, summa_buy) values (4,5,2500), (5,2,300), (5,7,8054), (3,4,1354), (1,3,5435), (4,8,2550), (10,4,485), (2,5,360), (3,9,780), (8,3,1226); -- функции IF, IFNULL, -- 1 SELECT id_sayna, IF(temper_sayna >= 90, 'Высокая температура', 'Невысокая температура') as high_temper_sayna FROM sayna ORDER BY temper_sayna DESC; -- 2 SELECT id_subscription, IFNULL(id_sayna, 'не выбрано') AS NULL_id_sayna FROM subscription ORDER BY id_subscription ASC; -- 3 SELECT id_subscription, CASE WHEN validity_subscription <= 7 THEN 'Неделя и меньше' WHEN validity_subscription <= 14 AND validity_subscription > 7 THEN 'Две недели и меньше' WHEN validity_subscription <= 30 AND validity_subscription > 14 THEN 'Месяц и меньше' WHEN validity_subscription <= 92 AND validity_subscription > 30 THEN 'Три месяца и меньше' ELSE 'Более трех месяцев' END AS Date FROM subscription; -- процедуры -- 1 DELIMITER $$ CREATE PROCEDURE GetVisitorToSubscription() BEGIN SELECT v.id_visitor, v.fio_visitor, s.id_subscription from visitor v join visitor_to_subscription vts ON v.id_visitor=vts.id_visitor join subscription s ON s.id_subscription=vts.id_subscription; END;$$ DELIMITER ; -- 2 DELIMITER $$ CREATE PROCEDURE GetVisitorToSubscription_1() BEGIN DECLARE visitor_fio_count INT DEFAULT 0; SELECT COUNT(*) INTO visitor_fio_count FROM visitor; SELECT visitor_fio_count; END;$$ DELIMITER ;