-- List the names of crew members who are assigned to every flight
-- that leaves MIAmi on October 31st.

INSERT INTO Departure VALUES ('Oct 31', 395);
INSERT INTO Assigned_To VALUES (1003, 'Oct 31', 335);
INSERT INTO Assigned_To VALUES (1003, 'Oct 31', 395);

SELECT P.Name
FROM (SELECT Person.Name, COUNT(Flight.Num)
      FROM Person, Employee, Assigned_To, Flight, Departure
      WHERE Employee.Name = Person.Name
        AND Employee.Emp_No = Assigned_To.Emp_No
        AND Flight.Num = Assigned_To.Num
        AND Flight.Origin = 'MIA'
	AND Departure.Dep_Date = 'Oct 31'
        GROUP BY Person.Name
        HAVING (COUNT(Flight.Num) = 
	     (SELECT COUNT(*)
	      FROM Flight, Departure
	      WHERE Flight.Origin = 'MIA' AND
	            Flight.Num = Departure.Num
		    AND Departure.Dep_Date = 'Oct 31'))) P;


