CYBER WEEK - EXTRA SAVINGS EVENT
TRY A FREE LESSON

Integrating a subquery with the outer Query

Screen Link: Learn data science with Python and R projects

My Code: SELECT Major, Major_category,
CAST(Sample_size AS FLOAT)/Total AS ratio
FROM recent_grads
WHERE ratio > (SELECT AVG(CAST(Sample_size AS FLOAT)/Total) AS avg_ratio
FROM recent_grads

Replace this line with your code

What I expected to happen:
As per SQL runs the high-level structure
“WHERE” runs 1 and then “SELECT” then how does select query ratio used in Where in this Query

What actually happened:

Replace this line with the output/error

Hi @shubhamkhetle98 and welcome to the community!

I’m not quite sure exactly what your question is but I do see an issue with your subquery:

Here you have stated a SELECT clause but your FROM clause for your subquery is outside. If you put the FROM clause inside the brackets you should be okay.

EDIT: Reading a bit deeper, I think I might understand your question now. Are you confused about how the WHERE clause is working here because in the SQL order of execution, WHERE happens before SELECT? If so, understand that the WHERE clause here is an entire (sub)query and so SQL evaluates this subquery entirely before it evaluates SELECT Major, Major_category, ... Hope this helps!

I think the question is how come the code works whereas in the WHERE clause one column is called “ratio” but as not been created yet(as it is created in the SELECT clause). I don’t get it either.

AS a quick reminder is a code proposed in the answer in Dataquest :slight_smile:
SELECT Major, Major_category,
CAST(Sample_size AS FLOAT)/Total AS ratio
FROM recent_grads
WHERE ratio > (SELECT AVG(CAST(Sample_size AS FLOAT)/Total) AS avg_ratio
FROM recent_grads
);

Below is what I thought would be the best answer:
SELECT Major, Major_category
FROM recent_grads
WHERE CAST(Sample_size AS FLOAT)/Total >
(SELECT AVG(CAST(Sample_size AS FLOAT)/Total) AS avg_ratio FROM recent_grads );

Hi @alais and welcome to the community!

Yes, I agree with you! This is definitely the better answer because it will work with other flavours of SQL. The flavour we are using on DataQuest is SQLite which behaves a little differently than most others in that it allows the use of aliases in WHERE clauses. This post on SO clearly indicates that it will throw an error if we use aliases in other versions of SQL.