Declaring datasets

Q&A DQ Courses
,
#1

Below is from the code challenge for the Joining Data in SQL mission:

f.name country, 
c.urban_pop,
f.population total_pop, 
(c.urban_pop / CAST(f.population AS FLOAT)) urban_pct
FROM facts f
INNER JOIN (SELECT facts_id, SUM(population) urban_pop FROM cities
            GROUP BY facts_id) c ON c.facts_id = f.id
WHERE urban_pct > 0.5
ORDER BY 4 ASC

What I expected to happen:
I would have expected to get an error because nowhere have I defined my short forms or cities.

My guess is because the sub query is always executed first, as denoted by the (SELECT facts_id, SUM etc, etc) ends up defining urban_pop? But we still did not define (anywhere for that matter) cities as c?

I am having a very hard time trying to break these SQL queries down due to the “run-on sentence” nature of the language. It’s like I have to figure out the answer first, then work backgrounds. Versus applying deductive reasoning (and trial and error) to arrive at my answer if that makes sense.

Thanks.

#2

I’m having a hard time understanding your question. I leave you with a couple observations that I hope are useful:

  • urban_pop is the name attributed to the column SUM(population).
  • c is the result of the subquery SELECT facts_id, SUM(population) urban_pop FROM cities GROUP BY facts_id.
#3

But that is it - nowhere do we say “cities as c” in the code above.

When I actually put in SELECT facts_id, SUM(population) urban_pop FROM cities as c GROUP BY facts_id. The code works.

But it still runs when I do not define “cities as c”. Is the whole sub query denoted as c?

#4

Yes, that is what I said.

#5

Yes, I realized after I hit submit.

I am really struggling with the syntax of SQL. I find it illogical and cumbersome, despite being consistently told it is the easiest language ever and even easier than Python. :sob:

#6

I unfortunately never really had an issue with SQL, I have trouble connecting to that hardship. Hopefully someone else can weigh in.

One thing I’ll say is that you can try thinking in terms of tables. I see you mentioning the language a lot, it seems like you’re focusing on the syntax. SQL is about table, think about the tables and what you want to do with them using SQL’s tools. Only then figure out how to actually write the code.

1 Like
#7

hey @AWM007

if it’s something like you are working with SQL for the first time, you can supplement your learnings (along with the DQ courses) from websites like w3schools.com and/or sqlzoo.