-- -- -- create Table Flights and populate with data 

DROP TABLE Flights;

CREATE TABLE Flights(
  airline CHAR(2),
  cityfrom VARCHAR2(10),
  cityto VARCHAR2(10),
  fee NUMBER,
  departs NUMBER,
  arrives NUMBER);

INSERT INTO Flights VALUES('UA', 'SF','DEN', 1000, 930,1230);
INSERT INTO Flights VALUES('AA', 'SF','DAL', 10000, 900,1430);
INSERT INTO Flights VALUES('UA','DEN','CHI', 500, 1500,1800);
INSERT INTO Flights VALUES('AA','DEN','DAL', 2000, 1400,1700);
INSERT INTO Flights VALUES('AA','DAL','CHI', 600, 1530,1730);
INSERT INTO Flights VALUES('AA','DAL', 'NY', 2000, 1500,1930);
INSERT INTO Flights VALUES('AA','CHI', 'NY', 3000, 1900,2200);
INSERT INTO Flights VALUES('UA','CHI', 'NY', 2000, 1830,2130);
INSERT INTO Flights VALUES('LH', 'CHI', 'DEN', 2000, 1900, 2100);

COMMIT;

SELECT * FROM Flights;


-- -- -- There is no solution in Relational Algebra (or basic SELECT) 

select distinct cityfrom, cityto
from Flights
union
select j1.cityfrom, j2.cityto
from Flights j1, Flights j2
where j1.cityto=j2.cityfrom
union
select j1.cityfrom, j3.cityto
from Flights j1, Flights j2, Flights j3
where j1.cityto=j2.cityfrom
and j2.cityto=j3.cityfrom
--- union etc... 

-- paper excercises SQL-1999 WITH RECURSIVE 


            WITH RECURSIVE Reaches AS 
                  (SELECT cityfrom, cityto FROM Flights
             UNION
                  SELECT Reaches.cityfrom, Flights.cityto 
                  FROM Reaches, Flights 
                  WHERE Reaches.cityto = Flights.cityfrom)
            SELECT cityto FROM Reaches WHERE cityfrom='DAL';


-- in Oracle WITH  
 
           with Reaches (cityfrom, cityto) as
                   (select cityfrom, cityto from Flights
               union all
                    select Flights.cityfrom, Reaches.cityto 
                    from Flights, Reaches 
                    where Flights.cityto=Reaches.cityfrom
                    )
                SEARCH DEPTH FIRST BY cityfrom SET SORTING
                CYCLE cityfrom SET is_cycle TO 1 DEFAULT 0
            select distinct cityfrom, cityto from Reaches order by cityfrom;


-- in PL/SQL 

           DROP TABLE Reaches;
            CREATE TABLE Reaches(
                             cityfrom VARCHAR2(10), 
                             cityto VARCHAR2(10));


   DECLARE 
    OldCounter INTEGER:= 0;
    NewCounter INTEGER:= 0;
  
  BEGIN
  
-- Initial values
   
  delete from Reaches; 
  OldCounter:= 0;
  insert into Reaches 
    (select distinct cityfrom, cityto from Flights);
  select count(*) into NewCounter from Reaches;

-- in LOOP 

  LOOP 
  insert into Reaches 
    (select distinct Reaches.cityfrom,Flights.cityto 
     from Reaches, Flights   
     where Reaches.cityto = Flights.cityfrom
     and (Reaches.cityfrom,Flights.cityto) 
          NOT IN (select * from Reaches));
  select count(*) into NewCounter from Reaches;
  EXIT WHEN NewCounter = OldCounter; 
  OldCounter := NewCounter; 
  END LOOP;
  END;
  /
    
  


   -- VER2: Reaches2(cityfrom, cityto, transfersNum, fee)
     
    WITH RECURSIVE Reaches(cityfrom, cityto, transfersNum, fee) AS 
            (SELECT cityfrom, cityto, 0 transfersNum, fee FROM Flights
        UNION
             SELECT Reaches.cityfrom cityfrom, Flights.cityto cityto, Reaches.transfersNum+1 transfersNum,  
                 Reaches.fee+Flights.fee fee FROM Reaches, Flights 
                 WHERE Reaches.y = Flights.cityfrom)
    SELECT DISTINCT cityto FROM Reaches WHERE cityfrom='DAL' 
                  and transfersNum<=3 and fee<5000;
    
    -- Oracle WITH 
    
    WITH Reaches(cityfrom, cityto, transfersNum, fee) AS 
            (SELECT cityfrom, cityto, 0 transfersNum, fee FROM Flights
        UNION ALL
             SELECT Reaches.cityfrom cityfrom, Flights.cityto cityto, Reaches.transfersNum+1 transfersNum,  
                 Reaches.fee+Flights.fee fee FROM Reaches, Flights 
                 WHERE Reaches.cityto = Flights.cityfrom)
        SEARCH DEPTH FIRST BY cityfrom SET SORTING
        CYCLE cityfrom SET is_cycle TO 1 DEFAULT 0
     SELECT DISTINCT cityto FROM Reaches 
     WHERE cityfrom='DAL' and transfersNum<=3 and fee<5000;