The SQL Saga: Unveiling the Secret Sequence of Query Execution

In today's episode, we're going back to basics. SQL is a language you must not only speak but also comprehend. You can craft a beautiful query, knowing exactly what you need from your data. You can even communicate it, yet somehow all you see is nonsense.

Like in any relationship, communication works both ways, so let's ensure you're getting your message across clearly.

It is important to know that there is an order in which SQL will execute your query. This is how it rolls:

In this awesome graphic above, which I made myself, you'll see how SQL executes.

First, SQL identifies the table you need to pull from. If you use a JOIN, it will be processed in this crucial step. Tell it what to SELECT.

Then, with the WHERE clause, you get to choose your conditions, allowing SQL to filter the rows accordingly.

GROUP BY and HAVING are especially helpful when using aggregate functions and applying conditions to those groupings.

Only then will SQL select the fields you've chosen, ORDER them BY the column of your choice, and LIMIT the results to the number of rows you specified.

The result is a table displaying your queried data.

Let's go over an example.

I have a database containing two tables: movies and critic_reviews.

I wanted to answer the question of: what are the top 10 publications starting with the letter "A", that have the most reviews for movies with audience score above 90%.

SELECT TOP 10		
COUNT(m.movieTitle) AS number_of_movies,
cr.publicationName AS publication_name
FROM movies as m
JOIN critic_reviews as cr
ON m.movieId = cr.movieId
WHERE m.audience_score > '90%'
GROUP BY cr.publicationName
HAVING cr.publicationName LIKE 'A%'
ORDER BY number_of_movies DESC ;

The way SQL reads it, I imagine, would be something along:

• I am joining movies and critic_reviews tables. The common column is movieID, so I will match it and take those that have common lines.

• Then I will take movies that will have audience score above 90% from the column aduaince_score in movies part of the table. From all of those, I will group them by publication name and take only those that starts with the letter "A".

• I am asked to give count how many movieTitiles were considered by those publications and associate it to filtered before publication name.

Ta-da!

I hope this gives you an idea how SQL process the query.

NOTE! In SSMS to limit you would use

SELECT TOP 10 

Or how many you need to see.