Common Table Expressions (CTEs) in SQL: SQL 30 Day Challenge Day 19
Learn how to create temporary tables that can limit the use of SQL subqueries (and make your life easier) This is just an intro feel free to practice more CTEs online (they are very important)
Questions:
/* Use a CTE to calculate the average track length
per album */
with tracklength as (
select AlbumId, AVG(Milliseconds) as AvgTrackLength
from tracks
group by AlbumId
)
select al.Title, tracklength.AvgTrackLength
from albums al
join tracklength
on al.AlbumId=tracklength.AlbumId;
/* Use two CTEs to get total sales
for each USA customer */
WITH TotalSales AS (
SELECT CustomerId, SUM(Total) AS TotalAmount
FROM Invoices
GROUP BY CustomerId
), TopCustomers AS (
SELECT CustomerId, FirstName, LastName
FROM Customers
WHERE Country = 'USA'
)
SELECT TopCustomers.FirstName, TopCustomers.LastName, TotalSales.TotalAmount
FROM TopCustomers
JOIN TotalSales ON TopCustomers.CustomerId = TotalSales.CustomerId
ORDER BY TotalSales.TotalAmount DESC;
-----------------------------------------------------------------
Download SQLite Studio Here: https://sqlitestudio.pl/
Download the Chinook Database: https://www.sqlitetutorial.net/sqlite...
Link to Topic List: https://docs.google.com/document/d/1f...
Link to Facebook Support Group: / 767988395118964
Link to my Data Etsy Shop (support me to keep making content): https://jellysgeekygoods.etsy.com
Buy me a coffee to show support: https://buymeacoffee.com/learningwith...
#sql #sqltutorial #sqlforbeginners
