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.
- The solution should be should use “SELECT” statement or “CTE”.
- Add your solution(s) in the comments section or send you solution(s) to firstname.lastname@example.org
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.
sanjay singh said:
where plane in ( select plane from #Hangar)
group by pilot
having COUNT(distinct plane)= (select COUNT(*) from #Hangar)
Pawan Kumar Khowal said:
Excellent stuff Sanjay !!
Jean Bell said:
Smokin hot stuff! You’ve trimmed my dim. I feel as bright and fresh as your prolific website and blogs!
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
If FoundIt Then
System.Console.WriteLine(“Is a city in Michigan.” & City)
System.Console.WriteLine(“Is NOT a city in Michigan.” & City)
End Sub ‘ End of Main() procedure
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.
Gregor Kralj said:
with cte as (
(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)