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);
-- функции
-- 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;
Telegram: DB developers channel