1. Advanced Common Table Expressions
Get ready; we're tuning up the heat on those common table expressions!
2. Defining multiple Common Table Expressions
Believe it or not, we can define more than one common table expression with a single `WITH` clause. In fact, we can define, really, as many common table expressions as we'd like!
To do this, we'll start with the same syntax we used for a single common table expression. However, following the parenthesis after the first query, we'll add a comma, the name of the second CTE, and its query in parenthesis.
This allows us to do a couple of neat things, such as joining the result sets of CTEs.
Remember, we can do just about anything we'd do in a normal query in a CTE, like filtering, aggregating, and joining data.
We could even use the results of one CTE in another!
3. Top-performing courses
Let's say we want to pull together a list of top-performing courses. This requires data to be curated from two tables. To make this a bit easier, we'll actually go ahead and use two CTEs. First, we create `active_courses` by `SELECT`'ing records from the `courses` table where the course is active.
Next, we'll define another CTE; `course_avgs`. Note the syntax. A comma is added after the parenthesis, followed by another query. This query aggregates data from `student_courses` to find the average grade.
Once we've defined these CTEs, we can `JOIN` them together, just like we would with two tables. However, using these two common table expressions, we've made the final query significantly easier to understand and manipulate as needed.
4. Top-performing courses
Here's a good way to visualize the results. Our two CTE's basically created these results on the left. Then, those two result sets were joined together to produce the final result. This is much easier to understand than lumping this into one query!
5. Tenured teachers
Let's say we're interested in finding the tenure and highest average grade for each teacher. To do this, we'll again use two common table expressions. First, we'll retrieve records from the `JOIN`'d courses and teachers table. This gives us granular data about each course's teacher.
Like before, the `course_avgs` CTE is defined after the `active_courses`.
Now, we can `JOIN` these two CTE's together to find the metrics for each teacher, by tenure.
In a single query, we've joined three tables together. But, it doesn't feel complex at all. CTE's allow for us to design, build, and test queries in an organized manner.
6. Tenured teachers
Just for good measure, here's the result. We've taken three disparate tables and brought them together to create a single, easy-to-understand output.
7. Let's practice!
Alright, let's see what you got! Time to try your hand at building more advanced common table expressions!