task3.sql

DROP DATABASE IF EXISTS flights;
CREATE DATABASE flights;
USE flights;

DROP TABLE IF EXISTS flights;
CREATE TABLE flights (
  id SERIAL PRIMARY KEY,
  `from` VARCHAR(255),
  `to` VARCHAR(255)
);

INSERT INTO flights (`from`,`to`) VALUES
  ('moscow', 'omsk'),
  ('novgorod', 'kazan'),
  ('irkutsk', 'moscow'),
  ('omsk', 'irkutsk'),
  ('moscow', 'kazan');

DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
  label VARCHAR(255),
  name VARCHAR(255)
);

INSERT INTO cities (label, name) VALUES
  ('moscow', 'Ìîñêâà'),
  ('irkutsk', 'Èðêóòñê'),
  ('novgorod', 'Íîâãîðîä'),
  ('kazan', 'Êàçàíü'),
  ('omsk', 'Îìñê');
 
 select f.id, c1.name as `from`, c2.name as `to` 
 from flights as f
 	join 
 	  cities as c1 on f.`from` = c1.label
 	join
 	  cities as c2 on f.`to` = c2.label
 order by f.id