DROP TABLE IF EXISTS PilotSkills;
DROP TABLE IF EXISTS Hangar;

CREATE TABLE PilotSkills
(pilot_name CHAR(15) NOT NULL,
plane_name CHAR(15) NOT NULL,
PRIMARY KEY (pilot_name, plane_name));

INSERT into PilotSkills VALUES ('Celko'  ,  'Piper Cub');
INSERT into PilotSkills VALUES ('Higgins', 'B-52 Bomber');
INSERT into PilotSkills VALUES ('Higgins', 'F-14 Fighter');
INSERT into PilotSkills VALUES ('Higgins', 'Piper Cub');
INSERT into PilotSkills VALUES ('Jones'  , 'B-52 Bomber');
INSERT into PilotSkills VALUES ('Jones'  , 'F-14 Fighter');
INSERT into PilotSkills VALUES ('Smith'  , 'B-1 Bomber');
INSERT into PilotSkills VALUES ('Smith'  , 'B-52 Bomber');
INSERT into PilotSkills VALUES ('Smith'  , 'F-14 Fighter');
INSERT into PilotSkills VALUES ('Wilson' , 'B-1 Bomber');
INSERT into PilotSkills VALUES ('Wilson' , 'B-52 Bomber');
INSERT into PilotSkills VALUES ('Wilson' , 'F-14 Fighter');
INSERT into PilotSkills VALUES ('Wilson' , 'F-17 Fighter');

CREATE TABLE Hangar
(plane_name CHAR(15) NOT NULL PRIMARY KEY);

INSERT INTO Hangar VALUES ('B-1 Bomber');
INSERT INTO Hangar VALUES ('B-52 Bomber');
INSERT INTO Hangar VALUES ('F-14 Fighter');

/*
PilotSkills DIVIDED BY Hangar
pilot_name
=============================
'Smith'
'Wilson'
*/

/* Method of relational division by Chris Date:
The quickest way to explain what is happening in this query 
is to imagine a World War II movie where a cocky pilot has 
just walked into the hangar, looked over the fleet, and announced, 
"There ain't no planes in this hangar that I can't fly!"  
We want to find pilots for whom no plane exists in the hangar 
for which they have no skills.  You might want to read that double 
negative again – it is ugly English, but good logic.
*/

SELECT DISTINCT pilot_name
  FROM PilotSkills AS PS1 
  WHERE NOT EXISTS
       (SELECT *
          FROM Hangar
         WHERE NOT EXISTS
               (SELECT *
                  FROM PilotSkills AS PS2
                 WHERE (PS1.pilot_name = PS2.pilot_name)
                   AND (PS2.plane_name = Hangar.plane_name)));

/* 
This example, via Joe Celko: 
"There is a serious difference in the two methods.  
Burn down the hangar, so that the divisor is empty.  
Because of the NOT EXISTS() predicates in Date's query, 
all pilots are returned from a division by an empty set.  
Because of the COUNT() functions in my query, no pilots 
are returned from a division by an empty set. "

*/
SELECT PS1.pilot_name
   FROM PilotSkills AS PS1, Hangar AS H1
  WHERE PS1.plane_name = H1.plane_name
  GROUP BY PS1.pilot_name 
  HAVING COUNT(PS1.plane_name) = (SELECT COUNT(plane_name) FROM Hangar);
  
