CREATE TABLE voo(
	nvoo	INTEGER		PRIMARY KEY, 
	origem  VARCHAR(30)	NOT NULL, 
	destino VARCHAR(30)	NOT NULL,
	partida	TIME		NOT NULL,
	chegada TIME		NOT NULL);


INSERT INTO voo VALUES (105, 'Chicago', 'Pittsburgh', '8:00', '9:15');
INSERT INTO voo VALUES (104, 'Chicago', 'Detroit', '8:50', '9:30');
INSERT INTO voo VALUES (107, 'Detroit', 'New York', '11:00', '12:30');
INSERT INTO voo VALUES (109, 'Pittsburgh', 'New York', '10:00', '12:00');
INSERT INTO voo VALUES (205, 'Chicago', 'Las Vegas', '14:00', '17:00');
INSERT INTO voo VALUES (101, 'Los Angeles', 'Chicago', '5:30', '7:30');
INSERT INTO voo VALUES (201, 'Las Vegas', 'Tucson', '17:40', '19:00');
INSERT INTO voo VALUES (210, 'Tucson', 'Albuquerque', '19:30', '20:30');
INSERT INTO voo VALUES (310, 'Dallas', 'Albuquerque', '9:30', '11:00');
INSERT INTO voo VALUES (325, 'Los Angeles', 'Dallas', '6:15', '8:15');
INSERT INTO voo VALUES (425, 'Albuquerque', 'Los Angeles', '21:30', '23:00');


/******************************************************
Exemplos de consultas recursivas sobre a tabela Voo
*******************************************************/

-- (a) Escreva uma consulta SQL que retorne os pares de cidade (x,y) tais que é possível chegar em y a partir de x, por meio de um ou mais  voos.

WITH RECURSIVE Conectadas(origem,destino,partida,chegada) AS (
	(SELECT origem, destino, partida, chegada FROM Voo)
	UNION
	(SELECT C.origem, V.destino, C.partida, V.chegada 
		FROM Conectadas as C, Voo as V 
		WHERE C.destino = V.origem and C.chegada < V.partida) )
SELECT origem, destino FROM Conectadas;
	

-- (b) Encontre o menor tempo de viagem entre Los Angeles e New York, por meio de nenhuma ou mais conexões realizadas num mesmo dia.


WITH RECURSIVE ConectadasLA(cidade,partida,chegada) AS (
	(SELECT destino, partida, chegada FROM Voo WHERE origem = 'Los Angeles')
	UNION
	(SELECT V.destino, C.partida, V.chegada 
		FROM ConectadasLA as C, Voo as V 
		WHERE C.cidade = V.origem and C.chegada < V.partida) )
SELECT MIN(chegada - partida) FROM ConectadasLA WHERE cidade = 'New York';
	
-- (c) Escreva uma consulta SQL que retorne os pares de cidade (x,y) tais que é possível chegar em y a partir de x  numa mesma data, mas não existe um voo direto de  x para y.

WITH RECURSIVE Conectadas(origem,destino,partida,chegada) AS (
	(SELECT origem, destino, partida, chegada FROM Voo)
	UNION
	(SELECT C.origem, V.destino, C.partida, V.chegada 
		FROM Conectadas as C, Voo as V 
		WHERE C.destino = V.origem and C.chegada < V.partida) )
(SELECT origem, destino FROM Conectadas)
EXCEPT
(SELECT origem, destino FROM Voo);

--ou

WITH RECURSIVE Conectadas(origem,destino,partida,chegada) AS (
	(SELECT V1.origem, V2.destino, V1.partida, V2.chegada 
		FROM Voo as V1, Voo as V2
		WHERE V1.destino = V2.origem AND V1.chegada < V2.partida)
	UNION
	(SELECT C.origem, V.destino, C.partida, V.chegada 
		FROM Conectadas as C, Voo as V 
		WHERE C.destino = V.origem and C.chegada < V.partida) )
SELECT distinct origem, destino FROM Conectadas;