Geek Answers Handbook

Help me understand this specific use of nested SELECT statements

From this site:

Tables:

CREATE TABLE PilotSkills
(pilot_name CHAR(15) NOT NULL,
plane_name CHAR(15) NOT NULL,
PRIMARY KEY (pilot_name, plane_name));

CREATE TABLE Hangar
(plane_name CHAR(15) NOT NULL PRIMARY KEY);

Query:

SELECT DISTINCT pilot_name
  FROM PilotSkills AS PS1
  WHERE NOT EXISTS
       (SELECT *
          FROM Hangar
         WHERE NOT EXISTS
               (SELECT *
                  FROM PilotSkills AS PS2
                 WHERE (PS1.pilot_name = PS2.pilot_name)
                   AND (PS2.plane_name = Hangar.plane_name)));

I understand the problem he used for (set division), including the analogy that describes it as "There are no planes in this hangar that I cannot fly!". What I do not understand is exactly what works here, and how he joins together to do what he says.

You are having trouble indicating the features of my difficulty at the moment ...

Edit: Let me first ask what it is, namely:

SELECT DISTINCT pilot_name
  FROM PilotSkills
  WHERE NOT EXISTS
       (SELECT *
          FROM Hangar)

I think that there is no fundamental understanding ...

Edit: not suitable, and it would not be meaningful without a third nested SELECT, right?

+5
3

, . , , , . , , , . ( ), . , , .

, , (), .

" ..."

Select Distinct pilot_name
From PilotSkills As PS1
...

"... , ()..."

Select Distinct pilot_name
From PilotSkills As PS1
Where Not Exists    (
                    Select 1
                    From Hanger

"... , ".

Select Distinct pilot_name
From PilotSkills As PS1
Where Not Exists    (
                    Select 1
                    From Hanger As H
                    Where Not Exists    (
                                        Select 1
                                        From PilotSkills As PS2
                                        Where PS2.pilot_name = PS1.pilot_name
                                            And PS2.plane_name = H.plane_name
                                        )
                    )
+1

, Select * . , , , . , :

  • Pilot_Name From PilotSkills. .
  • ( * ). , .
  • ( * PilotSkills) - , .

( ) - . , .

+1

.

, .

, , ? .

, ( PilotSkills ).

SELECT pilot_name
  FROM Pilots
  WHERE NOT EXISTS
       (SELECT *
          FROM Hangar
         WHERE NOT EXISTS
               (SELECT *
                  FROM PilotSkills 
                 WHERE (Pilots.pilot_name = PilotSkills.pilot_name)
                   AND (PilotSkills.plane_name = Hangar.plane_name)));


pilot_name 
===========
'Celko'    
'Higgins'  


plane_name
=============
'B-1 Bomber'
'F-14 Fighter'

PilotSkills

pilot_name    plane_name
=========================
'Celko'    'F-14 Fighter'
'Higgins'  'B-1 Bomber'
'Higgins'  'F-14 Fighter'

, ,

  • Pilots.pilot_name
  • Hangar.plane_name
  • , PilotSkills pilot_name,plane_name

3 , , , , Pilots . 3 , 2 . , PilotSkills, , .

, , , ( ), PilotSkills.

0

More articles:


All Articles