In the third lesson of this SQL Intermediate course, you'll focus on how to build and organize more complex queries to increase your knowledge of SQL and add more tools to your toolbox. Often times when you'll find yourself having to write complex queries and you'll have to keep them format them to make the query readable to other people besides yourself!
You'll learn about the WITH clause to create CTEs along with CREATE VIEW to organize your queries. You will also learn how to use set operations such as UNION, UNION ALL, INTERSECT, and EXCEPT to combine results from two tables that have no column relation. In addition, you will have practice using each of these concepts along with writing advanced joins so that you can feel confident in your SQL ability so you can be on your way to becoming proficient in writing queries to solve any problem or answer any question by writing a query.
To facilitate your building and organizing complex queries, you'll be working with a the Chinook database, a music collection involving track name, album name, artist name, genre, and number of tracks purchased.
2. Writing Readable Queries
3. The With Clause
4. Creating Views
5. Combining Rows With Union
6. Combining Rows Using Intersect and Except
7. Multiple Named Subqueries
8. Challenge: Each Country's Best Customer
9. Next Steps