Master composing SQL Query
I have come across many professionals including data engineers who struggle in formulating SQL queries to achieve desired results. SQL query format is neither as simple nor as straight forward as what engineers in general are used to in the top-down coding approach. This stumps many in composing the query correctly.
In this article, I am going to help you master on how to `think` SQL. I will be using T-SQL as example, which is one of the dialects of SQL mainly used in Microsoft Technologies.
‘Think’-SQL
How to ‘think’ SQL?
The best way to start formulating the query is by understanding how the SQL query is processed by the SQL Query Optimizer engine and following the same approach. This also helps in writing performance optimized queries.
When a SELECT query is received by the Query Optimizer, it is broken down into logical units before being processed. Below are the different logical units a SELECT query is broken into. It is eventually processed in the logical order below by the Query Optimizer:
1. FROM, where to fetch data
2. JOIN, to what other datasets
3. WHERE, the conditions for the data
4. GROUP BY, the classifications
5. HAVING, conditions for the aggregated data
6. SELECT, attributes to display
7. ORDER BY, the selected attributes
Explanation Logical Order
Building the base data for processing
The table in FROM clause is the first dataset fetched by the engine. Every other table is JOINed to the main table by ON clause conditions, think of one big extended table being constructed.
Now the WHERE clause predicates are used to define the data contextually and narrows down the scope of the data to the needs.
Data wrangling
Once the working dataset is compiled, the data can be manipulated to achieve the desired results, like, creating calculated columns from existing attributes, creating conditional columns, GROUPing and aggregating data, and using HAVING to further filter out the aggregated data.
Desired Output
After performing manipulations, all or specific attributes are SELECTed and ORDERed to provide the desired output.
Exercise:
Now let's walk through some simple example requirements, to understand and formulate the SELECT queries, using the Query Optimizer approach.
I will take ‘School’ as the example dataset to practice constructing SQL queries.
Requirement 1:
‘I would like to know names of the students in 1st grade’
Let's see how to dissect this requirement per ‘Think-SQL’:
1. FROM — fetch data from Students
2. WHERE — students in 1st grade
3. SELECT — display their names
SELECT Name AS NamesOfStudentsInFirstGrade
FROM Student
WHERE Grade = 1
The above query returns the names of students who are in first grade.
Get datadoodler’s stories in your inbox
Join Medium for free to get updates from this writer.
Requirement 2:
‘I would like to know the classes that students in 10th grade have enrolled in’
Let’s see how to dissect this requirement per ‘Think-SQL’:
1. FROM — fetch data from Students
2. JOIN — need to lookup Class details for each Student
3. WHERE — students in 10th grade
4. SELECT — display Student name and Class name
LEFT JOIN type is used here to bring all students (left dataset) and only the classes (right dataset) they attend, that means bringing all data from Student dataset and only related data from Class dataset
SELECT S.Name AS NamesOfStudentsInTenthGrade
, C.Name AS ClassNamesOfStudentsInTenthGrade
FROM Student AS S
LEFT JOIN Class AS C
ON C.StudentID = S.StudentID
WHERE S.Grade = 10
Aliasing dataset, as ‘S’ and ‘C’ is to provide a short reference name to the dataset. This helps to select the attributes from each dataset. In the above scenario, ‘Name’ attribute exists in both datasets, instead of specifying ‘Student.Name’ and ‘Class.Name’ when we reference the dataset, it's simpler to specify ‘S.Name’ and ‘C.Name’.
The above query returns names of each student in 10th grade and only the classes they have enrolled in.
Requirement 3:
‘I would like to know the height of tallest boy and girl of the students in 1st grade’
Let’s see how to dissect this requirement per ‘Think-SQL’:
1. FROM — fetch data from Students
2. WHERE — students in 1st grade
3.GROUP BY — classify Students into Gender groups
4. SELECT — display max height by gender
SELECT Gender AS GenderOfTheStudentInFirstGrade
, MAX(Height) AS TallestHeightByGenderInFirstGrade
FROM Students
WHERE Grade = 1
GROUP BY Gender
The above query groups the students in 1st grade by their gender and then displays the max height found in each gender group.
Requirement 4:
‘I would like to know the subjects of each student in 8th grade where the average score is less than 80% on their tests’
Let’s see how to dissect this requirement per ‘Think-SQL’:
1. FROM — fetch data from Students
2. WHERE — Students in 8th grade and type of assessment is ‘Test’
3.GROUP BY — classify by each Student and by Subject groups
4. HAVING — average score less than 80%
5. SELECT — display Student’s Name, Subject, cumulative Score
SELECT S.Name AS NameOfStudentsInEightGrade
, G.Subject AS SubjectOfStudentsInEightGrade
, SUM(G.Score)/COUNT(*) AS AvgScoreOfStudentsInEightGradeInGivenSubject
FROM Students AS S
JOIN Grades AS G
ON G.SudentID = S.StudentID
WHERE S.Grade = 8 AND G.AssessmentType = 'Test'
GROUP BY S.Name, G.Subject
HAVING SUM(G.Score)/COUNT(*) <0.8
The above query groups 8th grade students’ test data by their name and subject, then adds the scores (SUM) and count occurrences of tests. Divide the total score by number of occurrences to find true average score for each student in each subject. The HAVING statement then filters out the scores that are 80% and above. The SELECT displays names of students and the subjects the students are struggling with, along with the average score.
WHERE and HAVING clauses, both filter out data. The difference lies in the scope of data, the stage where they can be invoked in. WHERE clause is used in defining the scope of the data for the query, while HAVING clause is used in defining the scope on the aggregated data.
In the above example, the WHERE clause restricted the scope of the data to 8th grade. Until the data is aggregated by name and subject, it is not possible to know which subjects' students scored less than 80%. The HAVING clause filters out the aggregated data to return the desired results.
Requirement 5:
‘I would like to see the number of students who are living in each mile radius from the school by each mile radius from school’
Let’s see how to dissect this requirement per ‘Think-SQL’:
1. FROM — fetch data from Students
2. JOIN— address to get how far from school they live
3. GROUP BY — classify by each Student and how far they live
4. SELECT — number of students and mile radius
5. ORDER BY — mile radius
SELECT A.MileFromSchool, COUNT(DISTINCT S.StudentID) AS NumberOfStudents
FROM Students AS S
JOIN Address AS A
ON A.StudentID = S.StudentID
GROUP BY A.MileFromSchool
ORDER BY A.MileFromSchool
The above query, fetches ‘MileFromSchool’ from Address table for each student in Students table and counts how many students (by distinct Ids) are found for each mile. The output is ordered by ‘MileFromSchool’ in ascending order (default, as not specified in the query).
The above query does DISTINCT COUNT on StudentID, rather than student Name. This is because multiple students may exist with same full name. To avoid duplicates and miscounting the students the query counts the students by the unique StudentIDs .
I hope this article helps in understanding how SELECT queries are processed by SQL, which in turn helps in better formulating them.
References:
Query Processing Architecture Guide — SQL Server | Microsoft Learn
Written by Chelvy Sun
