2. Non-Correlated Subqueries


#1

So I passed this lesson but am not understanding this. How does the flight table know which airport the flight is leaving from? Shouldn't there be a primary and foreign key to join the two tables together?


https://www.codecademy.com/en/courses/sql-table-transformation/lessons/subqueries/exercises/non-correlated-i?action=resume



Replace this line with your code.


#2

@jaydacoder
what was the FULL code you used....

In the these exercises we are using SQLite

You could also read the meaning in an Oracle-SQL environment
google search
sql non correlated query site:oracle.com


#3

Please re-read the question I'm asking. The problem is not with my code. My code is correct and I passed the lesson. The problem is my understanding of the lesson.

I am confused as to how the flight table knows which airport the flight is leaving from? Shouldn't there be a primary and foreign key to join the two tables together?

The link to the exercise is in the original post.


#4

@jaydacoder
As we are using SQLite we could analyze the used Database
+++ show all table defintions
select * from sqlite_master;

If you use the Query

SELECT * 
FROM flights 
WHERE origin in (
    SELECT code 
    FROM airports 
    WHERE elevation > 2000);

the Database-engine will create a list of airport code's
( each Airport has its own unique code )
SELECT code
FROM airports
WHERE elevation > 2000

In the flights table they use these unique-airport-code's
in the columns origin and destination

And this case we use
SELECT *
FROM flights
WHERE origin in (created list of airport codes)

=============================================

And if we read through the definitions

Correlated subquery is an inner query referenced by main query (outer query) such that inner query considered as being excuted repeatedly.

non-correlated subquery is a sub query that is an independent of the outer query and it can executed on it's own without relying on main outer query.

plain subquery is not dependent on the outer query

we can conclude we are using a non-correlated query


#5

Thanks for the explanation


#6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.