Divided We Stand: The SQL of Relational Division -- excerpted SQL statements from article (https://www.simple-talk.com/sql/t-sql-programming/divided-we-stand-the-sql-of-relational-division/), so that I can refer back to the SQL with syntax highlighting ;-) Queries have been edited only for the purpose of removing html entities that exist in orig…

division with a remainder.sql

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)));

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);

division with joins.sql

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');

division with set operators.sql

SELECT DISTINCT Pilot_name FROM PilotSkills AS P1
WHERE (SELECT plane_name FROM Hangar
        EXCEPT
        SELECT plane_name
          FROM PilotSkills AS P2
         WHERE P1.pilot_name = P2.pilot_name) IS NULL;

exact division.sql

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);

romley's division.sql

SELECT M1.mgr_name, P1.dept_name
  FROM MgrProjects AS M1
       CROSS JOIN
       Personnel AS P1
WHERE M1.project_id = P1.project_id
GROUP BY M1.mgr_name, P1.dept_name
HAVING COUNT(*) <> (SELECT COUNT(emp_id)
                      FROM Personnel AS P2
                     WHERE P2.dept_name = P1.dept_name);

SELECT DISTINCT M1.mgr_name, P1.dept_name
  FROM (MgrProjects AS M1
        INNER JOIN
        Personnel AS P1
        ON M1.project_id = P1.project_id)
         INNER JOIN
         Personnel AS P2
         ON P1.dept_name = P2.dept_name
GROUP BY M1.mgr_name, P1.dept_name, P2.project_id
HAVING MAX (CASE WHEN M1.project_id = P2.project_id
                THEN 1 ELSE 0 END) = 0;

SELECT T1.mgr_name, T1.dept_name,
       CASE SUM(T1.authority)
       WHEN 1 THEN 'None'
       WHEN 2 THEN 'All'
       WHEN 3 THEN 'Some'
       ELSE NULL END AS POWER
  FROM (SELECT DISTINCT M1.mgr_name, P1.dept_name,
               MAX (CASE WHEN M1.project_id = P1.project_id
                        THEN 2 ELSE 1 END) AS authority
          FROM MgrProjects AS M1
               CROSS JOIN
               Personnel AS P1
         GROUP BY m.mgr_name, P1.dept_name, P1.project_id) AS T1
GROUP BY T1.mgr_name, T1.dept_name;


SELECT PS1.pilot_name,
       CASE WHEN COUNT(PS1.plane_name) 
                > (SELECT COUNT(plane_name) FROM Hanger)
                 AND COUNT(H1.plane_name) = (SELECT COUNT(plane_name)FROM Hanger)
            THEN 'more than all'
            WHEN COUNT(PS1.plane_name) 
                  = (SELECT COUNT(plane_name) FROM Hanger)
                 AND COUNT(H1.plane_name) ) 
                     = (SELECT COUNT(plane_name) FROM Hanger)
            THEN 'exactly all '
            WHEN MIN(H1.plane_name) IS NULL
            THEN 'none'
            ELSE 'some' END AS skill_level
  FROM PilotSkills AS PS1
       LEFT OUTER JOIN
       Hanger AS H1
       ON PS1.plane_name = H1.plane_name
  GROUP BY PS1.pilot_name;

structure.sql

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'
*/

CREATE TABLE MgrProjects
(mgr_name CHAR(10) NOT NULL,
project_id CHAR(2) NOT NULL,
PRIMARY KEY(mgr_name, project_id));
INSERT INTO MgrProject 
VALUES ('M1', 'P1'), ('M1', 'P3'),
       ('M2', 'P2'), ('M2', 'P3'),
       ('M3', 'P2'),
       ('M4', 'P1'), ('M4', 'P2'), ('M4', 'P3');
CREATE TABLE Personnel
(emp_id CHAR(10) NOT NULL,
dept_id CHAR(2) NOT NULL,
project_id CHAR(2) NOT NULL,
UNIQUE (emp_id, project_id),
UNIQUE (emp_id, dept),
PRIMARY KEY (emp_id, dept_id, project_id));
-- load department #1 data
INSERT INTO Personnel 
VALUES ('Albert', 'D1', 'P1'),
       ('Bob', 'D1', 'P1'),
       ('Carl', 'D1', 'P1'),
       ('Don', 'D1', 'P2'),
       ('Ed', 'D1', 'P2'),
       ('Frank', 'D1', 'P2'),
       ('George', 'D1', 'P2');
-- load department #2 data
INSERT INTO Personnel
VALUES ('Harry', 'D2', 'P2'),
       ('Jack', 'D2', 'P2'),
       ('Larry', 'D2', 'P2'),
       ('Mike', 'D2', 'P2'),
       ('Nat', 'D2', 'P2');
-- load department #3 data
INSERT INTO Personnel 
VALUES ('Oscar', 'D3', 'P2'),
       ('Pat', 'D3', 'P2'),
       ('Rich', 'D3', 'P3');

todd's division.sql

SELECT DISTINCT JP1.job_nbr, SP1.sup_nbr_nbr
  FROM JobParts AS JP1, SupParts AS SP1
WHERE NOT EXISTS
        (SELECT *
           FROM JobParts AS JP2
          WHERE JP2.job_nbr = JP1.job_nbr
            AND JP2.part_nbr
                NOT IN (SELECT SP2.part_nbr
                          FROM SupParts AS SP2
                         WHERE SP2.sup_nbr_nbr = SP1.sup_nbr_nbr));

SELECT S1.sup_nbr, S2.sup_nbr
  FROM SupParts AS S1, SupParts AS S2
WHERE S1.sup_nbr < S2.sup_nbr -- different suppliers
   AND S1.part_nbr = S2.part_nbr -- same parts
GROUP BY S1.sup_nbr, S2.sup_nbr
HAVING COUNT(*) = (SELECT COUNT (*)  -- same count of parts
                     FROM SupParts AS S3
             WHERE S3.sup_nbr = S1.sup_nbr)
   AND COUNT(*) = (SELECT COUNT (*)
                     FROM SupParts AS S4
                    WHERE S4.sup_nbr = S2.sup_nbr);