Created
May 14, 2015 19:12
-
-
Save anonymous/8d25f5ed7fa65a77a853 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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