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


![rep_1.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/e9706669-5187-a4ec-1ad7-1be719c2dd7a.png)


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

**I have checked that it works with PostgreSQL**

# tables and data
```SQL: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

![rep_2.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/2d327d77-bb3c-73a1-7448-5ed7a4027529.png)


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:SQL
SELECT PS1.*, H1.*
FROM PilotSkills AS PS1 
  LEFT JOIN Hangar AS H1 ON PS1.plane = H1.plane
```

▼ Output

![rep_3.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/9fb2b895-53a5-b558-1976-c20e8c54607c.png)



# Count the number of planes 1st
```SQL: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

![rep_4.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/a0857ad5-a749-bf6a-6480-7f4cb578986f.png)

# The same number of planes 1st
```SQL: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`


![rep_5.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/515a9eba-148b-825d-b00c-b79065f94480.png)


# Count the number of planes 2nd
```SQL: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



![rep_6.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/03076c77-cbb3-20e3-16b1-e02550f61941.png)


# The same number of planes 2nd
```SQL: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


![rep_7.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1242736/d27ad86e-3886-a3a6-4241-27a05dd4dcfb.png)

# Answer SQL
```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](https://www.amazon.com/Puzzles-Answers-Kaufmann-Management-Systems/dp/0123735963)