T-SQL Query | [AIRPLANES AND PILOTS Puzzle ]

NOTE – This puzzle is from Joe Celko’s SQL Puzzles and Answers book.

The puzzle is very simple. We have a table of pilots and the planes they can fly and a table of planes in the hangar. We want the names of the pilots who can fly every plane in the hangar. Please check out the sample input and expected output for details.

Sample Input

PilotSkills

 pilot plane 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

Hangar

 plane B-1 Bomber B-52 Bomber F-14 Fighter

Expected output

 Pilot Smith Wilson

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

 ```-- CREATE TABLE PilotSkills ( pilot CHAR(15) NOT NULL, plane CHAR(15) NOT NULL, PRIMARY KEY (pilot, plane) ); 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 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 CHAR(15) PRIMARY KEY ); INSERT INTO Hangar VALUES ('B-1 Bomber'), ('B-52 Bomber'), ('F-14 Fighter'); -- ```

UPDATE – 10-Apr-2015 – Solution 1

 ``` -- Solution 1 -- SELECT p.Pilot FROM PilotSkills p INNER JOIN Hangar h on h.plane = p.plane GROUP BY p.pilot HAVING COUNT(*) = ( SELECT COUNT(*) FROM Hangar h ) -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com