From the course: Top Five Things to Know in Advanced SQL
Join today to access over 25,500 courses taught by industry experts.
Solution: Create a CTE - SQL Tutorial
From the course: Top Five Things to Know in Advanced SQL
Solution: Create a CTE
(upbeat music) - [Instructor] Here, the resulting dataset shows only the products that have lower than average quantities in stock. Again, you should have 47 rows in your output. Same as what we had when we wrote this as a subquery. In case you didn't get the correct results or want an explanation of how I arrived at the solution, we'll head over to the database to walk through the solution together. To arrive at this answer, we'll first want to construct a CTE expression which I'll call low stock that starts with the with clause and it extracts a column called stock. The CTE will contain what we use as a subquery in the last example where we'll select the average in stock as stock from the Red30Tech inventory table. This is what we use to calculate the average inventory levels. In the last portion of the query, we'll select only the product category, product number, product name and in stock from the…
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.