SQLize | PHPize | SQLtest

CREATE TABLE employee( id_employee INTEGER not null primary key AUTO_INCREMENT, fio_employee varchar(100) not null, age_employee integer not null, experience_employee int 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, age_trainer integer not null, experience_trainer integer 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, id_pool integer, id_gym integer, money_subscription integer not null, validity_subscription integer not null, FOREIGN KEY (id_sayna) REFERENCES sayna (id_sayna), FOREIGN KEY (id_pool) REFERENCES pool (id_pool), FOREIGN KEY (id_gym) REFERENCES gym (id_gym) ); CREATE TABLE medicine( id_medicine INTEGER not null primary key AUTO_INCREMENT, id_visitor int, id_employee int, time_medicine INT not null, square_medicine int not null, FOREIGN KEY (id_visitor) REFERENCES visitor (id_visitor), FOREIGN KEY (id_employee) REFERENCES employee (id_employee) ); 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), FOREIGN KEY (id_employee) REFERENCES employee (id_employee) ); CREATE TABLE visitor_to_subscription( id_visitor integer not null, id_subscription integer not null, FOREIGN KEY (id_visitor) REFERENCES visitor (id_visitor), FOREIGN KEY (id_subscription) REFERENCES subscription (id_subscription), 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), FOREIGN KEY (id_employee) REFERENCES employee (id_employee), 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), FOREIGN KEY (id_employee) REFERENCES employee (id_employee), 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), FOREIGN KEY (id_pool) REFERENCES pool (id_pool), 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), FOREIGN KEY (id_gym) REFERENCES gym (id_gym), PRIMARY key (id_trainer, id_gym) ); INSERT into employee (fio_employee, age_employee, experience_employee) values (" Овсянников Т.А.",32,5), (" Калашников Т.М.",28,3), (" Исаева Н.К.",30,2), (" Климова М.И.",24,3), (" Степанов М.З.",46,7), (" Федоров И.И.",31,8), (" Пономарев В. А.",23,1), (" Шестаков В. М.",19,3), (" Иванова А. А.",20,7), (" Новиков А. К.",27,5); 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), (80, 6), (75, 6), (60, 7), (80, 8), (70, 7), (90, 6), (80, 8), (90, 8); INSERT into gym (time_gym, square_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, age_trainer, experience_trainer) values (" Антонов А.П.",30,4), (" Семенов А.Р.",35,7), (" Казанцев Е.Д",32,8), (" Попов В. Д.",23,2), (" Королева М. В.",48,6), (" Казакова В. А.",28,2), (" Зайцева В. П.",27,2), (" Никифоров Л. Д.",22,1), (" Михайлова В. Я.",56,6), (" Емельянова У. А.",29,3); ; 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, id_sayna, id_pool, id_gym) values (2500, 12,1,2,1), (3000, 30,2,1,2), (7000, 90,3,3,3), (10000, 14,4,3,3), (4500, 30,3,2,3), (5000, 90,3,3,3), (3500, 90,3,3,4), (8000, 14,2,3,3), (4590, 30,3,3,3), (4799, 14,4,4,4); INSERT into medicine (time_medicine, square_medicine, id_visitor, id_employee) values (8, 20,1,4), (8, 15,1,3), (7, 20,2,1), (8, 30,1,2), (8, 20,1,1), (7, 30,1,5), (7, 25,3,1), (7, 20,1,1), (8, 25,2,1), (9, 25,2,3); INSERT into swimm (name_swimm, time_swimm, id_visitor, id_employee) values ("SwinStyle",8,1,1), ("Salow",9,1,2), ("Акер",8,3,1), ("Saloy",8,1,4), ("Convenience",8,4,2), ("Salon",8,4,1), ("Gymnastics",8,1,4), ("Blue",9,2,3), ("Digitalswim",9,5,4), ("Искра",8,2,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); SELECT visitor.id_visitor, visitor.fio_visitor, visitor.age_visitor FROM visitor CROSS JOIN medicine ON visitor.id_visitor=medicine.id_visitor;