Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Development Tools Database Foundations SQL Calculating, Aggregating and Other Functions Grouping, Joining and Cleaning Up

Posted by William J. Terrell
William J. Terrell
17,403 Points

So, how might I put the final SQL statement into plain English?

I'm keeping some notes throughout this course, and with all of the SQL statements that we put together, I'm adding "plain English" counterparts, to better understand what each one does.

Thus far, this is the first "skyscraper" statement that I've come across in the course, so I'm trying to think on how to word it in plain English, for reference.

The code is as follows:

SELECT title
MIN(score) AS minimum_score, 
MAX(score) AS maximum_score, 
IFNULL (AVG(score), 0) AS average 
FROM movies LEFT OUTER JOIN reviews
ON movies.id = reviews.movie_id
WHERE year_released > 2000
GROUP BY movie_id HAVING average > 3;

Any insight would be greatly appreciated; or might it be too complex to bother with putting together a "plain English" counterpart?

Thanks!

Max Hirsh
Max Hirsh
16,773 Points
Max Hirsh
Max Hirsh
16,773 Points

Hi there, I was just thinking about this. I think what makes SQL statements dense/confusing, is that the syntax is like set theory notation with words where mathematical operators would normally be: https://en.wikipedia.org/wiki/Union_(set_theory)

This is a bit different than the syntax used in normal conversation. For example, you might want to find "the intersection of set A and set B." In set notation, that would be "A ∩ B. " In mySQL, this is something like "A INNER JOIN B." This is slightly harder to read, but can be very difficult to keep parse several statements are strung one after the other.

So, with that in mind, a translation of the above statement would be something like this:

Collect the titles from this set* Report the minimum score as "minimum_score", Report the maximum score as "maximum_score", When calculating the average review, null values should count as "0", report the average score as "average" From the left outer join of movies and reviews (joined at the movie id) Return movies made after the year 2000 *(group by: return only the movie ID's with average review better than 3);

Simplified even more, this statement is saying this: Return titles of movies made after the year 2000 that have an average review better than 3. Report the min review score, the max review score and the average score for these movies.

Hope this helps!