WLKJZ4iE

1/-- tables--/
2
3create table session (
4  id    int auto_increment,
5  movie int      not null,
6  hall  int      not null,
7  start datetime not null,
8  primary key (id),
9  foreign key (movie) references movie (id)
10    on delete cascade,
11  foreign key (hall) references dictionary (id)
12    on delete cascade
13);
14
15create table movie (
16  id          int auto_increment,
17  description text,
18  name        varchar(100) not null,
19  poster      varchar(255),
20  duration    int          not null,
21  price       int          not null,
22  primary key (id)
23);
24
25create table hall
26(
27  id            int auto_increment,
28  name          varchar(255) not null,
29  primary key (id)
30);
31
32/--queries--/
33drop table if exists sessions_start_and_end;
34
35create temporary table sessions_start_and_end (
36  session int,
37  start datetime,
38  end datetime
39);
40
41insert into sessions_start_and_end (
42  select s.id, s.start, s.start + interval m.duration minute from session s join movie m on s.movie = m.id where s.hall = 3
43);
44
45select * from sessions_start_and_end ssae where '2018-06-21 12:15:00' between ssae.start and ssae.end
46                                                or '2018-06-21 12:15:00' + interval 100 minute between ssae.start and ssae.end
47                                                or ssae.start between '2018-06-21 12:15:00' and '2018-06-21 12:15:00' + interval 100 minute