--a
SELECT P.AGE
FROM PASSENGERS P, TICKETS T
WHERE P.PID=T.PID AND P.PID NOT IN
	(SELECT T1.PID 
		FROM TICKETS T1, FLIGHTS F1
	        WHERE T1.FID=F1.FID AND F1.AIRCRAFT='B787');

--b
SELECT P.PNAME
FROM PASSENGERS P
WHERE NOT EXISTS(
	        SELECT F.AIRCRAFT FROM FLIGHTS F
		MINUS
		SELECT F1.AIRCRAFT 
		FROM FLIGHTS F1, TICKETS T 
		WHERE F1.FID=T.FID AND T.PID=P.PID
	);


--c
SELECT T.FID, AVG(T.PRICE)
FROM PASSENGERS P, TICKETS T
WHERE P.PID=T.PID AND P.AGE > 30
GROUP BY T.FID
HAVING 100 <= (
	SELECT COUNT(*) 
	FROM TICKETS T1
	WHERE T1.FID=T.FID);


--d
SELECT SUM(T.PRICE) AS REVENUE
FROM TICKETS T, FLIGHTS F
WHERE T.FID = F.FID AND F.MILES = (SELECT MAX(F1.MILES) FROM FLIGHTS F1);


--e
SELECT F."from", COUNT(T.PID) AS TOTAL_PASSENGERS, SUM(T.PRICE) AS DOLLAR_AMOUNT, AVG(MILES) AS AVG_DIST
FROM TICKETS T, FLIGHTS F
WHERE F.FID=T.FID 
GROUP BY F."from";

--f
SELECT TEMP.AGE
FROM(
        SELECT P.PID, P.AGE, SUM(F.MILES) AS DIST_TRAVELED
        FROM PASSENGERS P, TICKETS T, FLIGHTS F
        WHERE P.PID=T.PID AND F.FID=T.FID
	GROUP BY P.PID, P.AGE) TEMP
WHERE TEMP.DIST_TRAVELED = (SELECT MAX (TEMP.DIST_TRAVELED)
	FROM(
		 SELECT P.PID, P.AGE, SUM(F.MILES) AS DIST_TRAVELED
		 FROM PASSENGERS P, TICKETS T, FLIGHTS F
		 WHERE P.PID=T.PID AND F.FID=T.FID
		 GROUP BY P.PID, P.AGE
	)
	TEMP);




