Queries

----------Query №1----------
SELECT TRAIN_ID, DELAY
  FROM DELAYS INNER JOIN TIMETABLE ON DELAYS.TRIP_ID = TIMETABLE.TRIP_ID AND DELAYS.STATION_NUMBER = TIMETABLE.STATION_NUMBER
              INNER JOIN TRIPS ON TIMETABLE.TRIP_ID = TRIPS.ID
WHERE TRAIN_ID = 1 AND TIMETABLE.ARRIVAL_TIME < TIMESTAMP '2017-12-01 12:00:00.00'
ORDER BY ARRIVAL_TIME DESC
FETCH FIRST ROW ONLY;

----------Query №2----------
SELECT *
  FROM (SELECT TRAINS.*
    FROM TRAINS INNER JOIN ROUTES ON TRAINS.ROUTE_ID = ROUTES.ID
    WHERE ROUTES.STATION_ID = 10)
WHERE TRAIN_ID = (SELECT TRAINS.TRAIN_ID
    FROM TRAINS INNER JOIN ROUTES ON TRAINS.ROUTE_ID = ROUTES.ID
    WHERE ROUTES.STATION_ID = 16);

----------Query №3----------
SELECT DISTINCT TRANSIT_ID, FIN
FROM(SELECT TRANSITS.ID AS TRANSIT_ID, FINISH_STATION AS FIN, TRANSITS.ORDER_NUMBER, COUNT (TRANSITS.ID) OVER (PARTITION BY TRANSITS.ID) AS CNT
  FROM TRANSITS INNER JOIN ROUTES ON ROUTES.ID = TRANSITS.ROUTE_ID
WHERE STATION_ID = 2 OR STATION_ID = 22) INNER JOIN TRANSITS ON TRANSIT_ID = TRANSITS.ID
WHERE CNT = 2;

--2 22

----------Query №4----------
SELECT MIN(FREE_TICKETS) AS FREE_PLACES
  FROM (SELECT *
  FROM (SELECT TRAINS.TRAIN_ID, TRAINS.ROUTE_ID, TIMETABLE.STATION_NUMBER, ROUTES.ORDER_NUMBER AS LOW, TICKETS_QUANTITY - SUM(PASSENGERS_CHANGE+TRANSIT_PASSENGERS_CHANGE) OVER (PARTITION BY TRIP_ID ORDER BY STATION_NUMBER) AS FREE_TICKETS
    FROM TRAINS INNER JOIN ROUTES ON TRAINS.ROUTE_ID = ROUTES.ID
      INNER JOIN TRIPS ON TRAINS.TRAIN_ID = TRIPS.TRAIN_ID
      INNER JOIN TIMETABLE ON TRIPS.ID = TIMETABLE.TRIP_ID
    WHERE ROUTES.STATION_ID = 10 AND ARRIVAL_TIME BETWEEN TIMESTAMP '2017-12-01 00:00:00.00' AND TIMESTAMP '2017-12-01 20:00:00.00')
  WHERE TRAIN_ID = (SELECT TRAINS.TRAIN_ID
    FROM TRAINS INNER JOIN ROUTES ON TRAINS.ROUTE_ID = ROUTES.ID
    WHERE ROUTES.STATION_ID = 16))
WHERE STATION_NUMBER BETWEEN LOW AND (SELECT ORDER_NUMBER
    FROM TRAINS INNER JOIN ROUTES ON TRAINS.ROUTE_ID = ROUTES.ID
    WHERE ROUTES.STATION_ID = 16);

----------Query №5----------
SELECT TRIPS.TRAIN_ID, ROUTE_ID, TYPE, DEPARTURE_TIME
  FROM (SELECT ROUTES.ID
   FROM ROUTES
  WHERE STATION_ID = 24) INNER JOIN TRAINS ON ID = TRAINS.ROUTE_ID
  INNER JOIN TRIPS ON TRAINS.TRAIN_ID = TRIPS.TRAIN_ID
  INNER JOIN TIMETABLE ON TRIPS.ID = TIMETABLE.TRIP_ID
WHERE DEPARTURE_TIME BETWEEN TIMESTAMP '2017-12-01 17:00:00.00' AND TIMESTAMP '2017-12-01 20:00:00.00';