Tags
Complex SQL Challenges, Complex TSQL Challenge, Interesting Interview Questions, Interview Qs.SQL SERVER Questions, Interview questions on Joins, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Joins, Joins Interview questions, Joins Puzzle, Learn complex SQL, Learn SQL, Learn T-SQL, Objective Puzzle, PL/SQL Challenges, puzzle sql developer, Puzzles, Queries for SQL Interview, SELECT Puzzle, SQL, SQL 2012, SQL 2014, SQL 2014 Interview Questions, SQL Challenge, SQL Challenges, SQL Interview Questions, SQL Joins, SQL pl/sql puzzles, SQL Puzzles, SQL Queries, SQL Quiz, SQL Server Database, SQL SERVER Interview questions, SQL Skills, SQL Sudoku, SQL Top clause, SQL Trikcy question, sql/database interview for puzzle sql developer, SQLSERVER, T SQL Puzzles, T-SQL Challenge, TOP Clause, Tough SQL Challenges, Tough SQL Puzzles, Tricky Questions, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Improving my SQL BI Skills 
select pilot
from #PilotSkills
where plane in ( select plane from #Hangar)
group by pilot
having COUNT(distinct plane)= (select COUNT(*) from #Hangar)
LikeLike
Excellent stuff Sanjay !!
LikeLike
https://polldaddy.com/js/rating/rating.jsHello Mate,
Smokin hot stuff! You’ve trimmed my dim. I feel as bright and fresh as your prolific website and blogs!
Module Module1
Sub Main()
Dim City As String ‘City to look up in array.’
Dim CityNames() As String ‘Initialized array of Michigan cities.’
Dim FoundIt As Boolean ‘Flag variable.’
Dim X As Long ‘Loop control variable.’
CityNames = Class System.Array(“Acme”, “Albion”, “Detroit”, “Watervliet”, “Coloma”, “Saginaw”, “Richland”, “Glenn”, “Midland”, “Brooklyn”)
City = InputBox$(“Enter city name: “) ‘Get user input.’
For X = LBound(CityNames) To UBound(CityNames)
If LCase(City) = LCase(CityNames(X)) Then
FoundIt = True
Exit For
End If
Next X
If FoundIt Then
System.Console.WriteLine(“Is a city in Michigan.” & City)
Else
System.Console.WriteLine(“Is NOT a city in Michigan.” & City)
End If
Console.ReadKey()
End Sub ‘ End of Main() procedure
End Module
Error
I receive is BC30201 Error (local variable) City As String I am unsure what to do to fix this
This error is on my Class System.Array
I am so grateful for your blog. Really looking forward to read more.
Ciao,
Dinesh
LikeLike
with cte as (
select
pilot,
(select count(1) from (select plane from Hangar intersect select plane from PilotSkills where pilot = ps.pilot) as tbl) as counted
from PilotSkills ps
group by pilot
)
select * from cte
where counted = (select COUNT(1) from Hangar)
LikeLiked by 1 person
;with cte as (
select a.*, row_number() over (partition by a.pilot order by a.pilot) as c from pilotskills as a
join hangar as b on a.plane=b.plane)
select pilot from cte where c=(select count(*) from hangar)
LikeLike