Visualize SQL | Let's explain 『Exact Relational Division』

Beginning

This technique is based on SQL Puzzle and Answers # 21 AIRPLANES AND PILOTS
It is better with 『SQL Puzzle and Answers』 to read this article.

Because it says the idea of algorithm and
You can check what each quiz is intended for ・・・

In this article ・・・
I would like to explain about 『Exact Relational Division』

～～～～～～～～～～～～～～～～～～～～～～～～～～
Quoted from the book

Another kind of relational division is exact relational division
The dividend table must match exactly to the values of
the divisor without any extra values
～～～～～～～～～～～～～～～～～～～～～～～～～～

the meaning of 『exact』 is ・・・
the number of planes that a pilot can fly and
the number of planes that a pilot can fly in the Hangar are exactly the same

In the previous article 「 Let's explain 『Relational Division』in detail 」
Smith and Willson are the pilots but it is not exactly correct
　⇒　Wilson can also fly F-17 Fighter

we are going to see SQL that can take out only Smith

I have checked that it works with PostgreSQL

tables and data

SQL

CREATE TABLE PilotSkills (
	pilot CHAR(15) NOT NULL,
	plane CHAR(15) NOT NULL,
	PRIMARY KEY (pilot, plane)
);

CREATE TABLE Hangar (
	plane CHAR(15) PRIMARY KEY
);

INSERT INTO PilotSkills
VALUES ('Celko',   'Piper Cub'),
       ('Higgins', 'B-52 Bomber'),
       ('Higgins', 'F-14 Fighter'),
       ('Higgins', 'Piper Cub'),
       ('Jones',   'B-52 Bomber'),
       ('Jones',   'F-14 Bomber'),
       ('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');

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

▼ Output

We are going to take out Smith
because he can fly three planes that is exactly same as in the Hangar

Making the original data using LEFT JOIN

SQL

SELECT PS1.*, H1.*
FROM PilotSkills AS PS1 
  LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane

▼ Output

Count the number of planes 1st

SQL

SELECT 
   PS1.pilot,
   array_agg(PS1.plane), --a list that a pilot can fly
   COUNT(PS1.plane),     --number of planes that a pilot can fly
   (SELECT array_agg(plane) FROM Hangar), --a list of planes in the Hangar
   (SELECT COUNT(plane) FROM Hangar)      --number of planes in the Hangar
FROM PilotSkills AS PS1 
 LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane
GROUP BY PS1.pilot

▼ Output

The same number of planes 1st

SQL

SELECT 
   PS1.pilot,
   array_agg(PS1.plane), --a list that a pilot can fly
   COUNT(PS1.plane),     --number of planes that a pilot can fly
   (SELECT array_agg(plane) FROM Hangar), --a list of planes in the Hangar
   (SELECT COUNT(plane) FROM Hangar)      --number of planes in the Hangar
FROM PilotSkills AS PS1 
 LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane
GROUP BY PS1.pilot

HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)

▼ Output

Add this Having condition
　⇒　HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)

the number of planes a pilot can fly
and the number of planes in the Hangar are the same
　⇒　both are three

but this is not good enough because Higgins can not fly all planes in the Hangar
he just can fly three planes

Count the number of planes 2nd

SQL

SELECT 
   PS1.pilot,
   array_agg(PS1.plane), --a list that a pilot can fly
   COUNT(PS1.plane),     --number of planes that a pilot can fly
   (SELECT array_agg(plane) FROM Hangar), --a list of planes in the Hangar
   (SELECT COUNT(plane) FROM Hangar),     --number of planes in the Hangar
   array_agg(H1.plane), --a list that a pilot can fly in the Hangar
   COUNT(H1.plane)      --number of planes that a pilot can fly in the Hangar
FROM PilotSkills AS PS1 
 LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane
GROUP BY PS1.pilot

▼ Output

Add a list that a pilot can fly in the Hangar and
number of planes that a pilot can fly in the Hangar in the SELECT statement
　⇒　array_agg(H1.plane)
　⇒　COUNT(H1.plane)

these planes' data are joined(LEFT JOIN) to the PilotSkills table
so we can see as 『planes that a pilot can fly in the Hangar』
　⇒　the right side of below image

we need to find a row that this number and
number of planes in the Hangar are the same

The same number of planes 2nd

SQL

SELECT 
   PS1.pilot,
   array_agg(PS1.plane), --a list that a pilot can fly
   COUNT(PS1.plane),     --number of planes that a pilot can fly
   (SELECT array_agg(plane) FROM Hangar), --a list of planes in the Hangar
   (SELECT COUNT(plane) FROM Hangar),     --number of planes in the Hangar
   array_agg(H1.plane), --a list that a pilot can fly in the Hangar
   COUNT(H1.plane)      --number of planes that a pilot can fly in the Hangar
FROM PilotSkills AS PS1 
 LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane
GROUP BY PS1.pilot

HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
 AND COUNT(H1.plane)  = (SELECT COUNT(plane) FROM Hangar)

▼ Output

Add below condition on HAVING ・・・
　⇒　COUNT(H1.plane) = (SELECT COUNT(plane) FROM Hangar)
　⇒　only Smith is left

Answer SQL

SQL

SELECT PS1.pilot
FROM PilotSkills AS PS1 
LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane
GROUP BY PS1.pilot
HAVING COUNT(PS1.plane) = (SELECT COUNT(plane) FROM Hangar)
   AND COUNT(H1.plane)  = (SELECT COUNT(plane) FROM Hangar)

▼ Output

pilot
Smith

References

Joe Celko's SQL Puzzles and Answers