Skip to content

Instantly share code, notes, and snippets.

Created May 14, 2015 19:12
Show Gist options
  • Save anonymous/8d25f5ed7fa65a77a853 to your computer and use it in GitHub Desktop.
Save anonymous/8d25f5ed7fa65a77a853 to your computer and use it in GitHub Desktop.
CREATE TABLE film (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
duration TINYINT UNSIGNED NOT NULL DEFAULT 120,
PRIMARY KEY (id)
);
CREATE TABLE seance (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
`date` DATETIME NOT NULL,
film_id INT UNSIGNED NOT NULL,
price FLOAT NOT NULL,
FOREIGN KEY (film_id) REFERENCES film(id),
PRIMARY KEY (id)
);
CREATE TABLE ticket (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
seance_id INT UNSIGNED NOT NULL,
FOREIGN KEY(seance_id) REFERENCES seance(id),
PRIMARY KEY (id)
);
/***************************************************/
INSERT INTO film (name, duration)
VALUES
('Геи-ниггеры из далекого космоса', 26),
('Зубы', 98),
('Свадебная ваза', 81),
('Зеленый слоник', 86),
('Отвращение', 106),
('Пять бутылок водки', 90),
('Человеческая многоножка', 92),
('Хостел', 94),
('Сало, или 120 дней Содома', 145),
('Пила', 103),
('Оно живое!', 92),
('Молоко скорби', 94),
('Бомж с дробовиком', 86),
('Мгла', 127),
('Паршивая овца', 95),
('Ад каннибалов', 86),
('Молчание ягнят', 114),
('Голова-ластик', 109);
/***************************************************/
SELECT s1.id AS 'Сеанс 1', f1.name AS 'Название 1',
s1.date AS 'Начало 1', f1.duration AS 'Длительность 1',
s2.id AS 'Сеанс 1', f2.name AS 'Название 2',
s2.date AS 'Начало 2', f2.duration AS 'Длительность 2'
FROM seance s1
INNER JOIN seance s2
INNER JOIN film f1 ON s1.film_id = f1.id
INNER JOIN film f2 ON s2.film_id = f2.id
WHERE s2.date BETWEEN s1.date + 1 AND s1.date + INTERVAL (f1.duration) MINUTE;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment