Question about the answer of SQL fundamental: 7. Practice Integrating A Subquery With The Outer Query

Q&A DQ Courses
, , ,
#1

Screen Link: https://app.dataquest.io/m/543/subqueries/7/practice-integrating-a-subquery-with-the-outer-query

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: https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html
From this website, it said Standard SQL disallows references to column aliases in a WHERE clause. So my question is why the answer includes “WHERE ratio > (SELECT AVG(CAST(Sample_size AS FLOAT)/Total) AS avg_ratio
FROM recent_grads);” ? Thanks a lot!

What actually happened:

Replace this line with the output/error
1 Like
#2

Hi @NelsonNg you code is okay have just ran it and it has worked;

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);

here is the result.

Capture

#3

Hi @NelsonNg,

Standard SQL doesn’t allow it, that’s correct, but SQLite (used on this platform) does allow, even though it isn’t a standard usage. There is a related StackOverflow discussion, you can refer to it.

#4

Thank you, Elena. :smiley:

1 Like