Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

Advertisements