From the course: Practice It: Advanced SQL

Unlock this course with a free trial

Join today to access over 25,500 courses taught by industry experts.

Check it: Implement CTE

Check it: Implement CTE - SQL Tutorial

From the course: Practice It: Advanced SQL

Start my 1-month free trial Buy for my team

Check it: Implement CTE

“

(light music) - [Instructor] On line 70, we will build our CTE by labeling it as high value suppliers. On line 74, in the from clause, we will pull data from the supplier's table and alias this as s. Then to pull information about products, we will left join onto the products table where a supplier ID from the supplier's table equals the supplier ID from the products table. Up to the select statement in line 71, we'll pull supplier name and create a count for the number of products by each supplier and alias this as high value count. Now in our where clause, we will want to filter products where the unit price is greater than the average unit price of all products. So we will build a subquery by selecting the average unit price from products. We finish off our CTE by grouping our results by each supplier with supplier name. Now onto the main query where we'll be selecting data from the CTE that we just built, so from high value suppliers as HVS to shorthand. Then from our CTE, we will…

Contents