/*
 * From "Divided we stand: The SQL of Relational Division
 * Joe Celko
 * https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/
 *
 */

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

/*
PilotSkills
pilot_name    plane_name
=========================
'Celko'    'Piper Cub'
'Higgins'  'B-52 Bomber'
'Higgins'  'F-14 Fighter'
'Higgins'  'Piper Cub'
'Jones'    'B-52 Bomber'
'Jones'    'F-14 Fighter'
'Smith'    'B-1 Bomber'
'Smith'    'B-52 Bomber'
'Smith'    'F-14 Fighter'
'Wilson'   'B-1 Bomber'
'Wilson'   'B-52 Bomber'
'Wilson'   'F-14 Fighter'
'Wilson'   'F-17 Fighter' */
CREATE TABLE Hangar
(plane_name CHAR(15) NOT NULL PRIMARY KEY);
/*
Hangar
plane_name
=============
'B-1 Bomber'
'B-52 Bomber'
'F-14 Fighter'

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

/*** Division with a remainder */
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)));
                   
/* alternatively */
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);

/* Exact division */
SELECT PS1.pilot_name
  FROM PilotSkills AS PS1
       LEFT OUTER JOIN
       Hangar AS H1
       ON PS1.plane_name = H1.plane_name 
  GROUP BY PS1.pilot_name
HAVING COUNT(PS1.plane_name) = (SELECT COUNT(plane_name) FROM Hangar)
   AND COUNT(H1.plane_name) = (SELECT COUNT(plane_name) FROM Hangar);

/* Division with JOINS */
SELECT SP1.Pilot_name
  FROM (((SELECT plane_name FROM Hangar) AS H1
         INNER JOIN
         (SELECT pilot_name, plane_name FROM PilotSkills) AS SP1
         ON H1.plane_name = SP1.plane_name)
       INNER JOIN (SELECT *
                     FROM PilotSkills
                    WHERE pilot_name = 'Higgins') AS H2
       ON H2.plane_name = H1.plane_name)
       GROUP BY SP1.Pilot_name
HAVING COUNT(*) >= (SELECT COUNT(*)
                      FROM PilotSkills
                     WHERE pilot_name = 'Higgins');
                     
