Advanced SQL: Logical Query Processing, Part 1
1h 39mAdvanced2020-03-30
Authors
Ami Levin
Data Educator, Speaker, and Practitioner, SQL and RDM enthusiast
Course details
SQL has been the dominant data processing language for the past five decades. This course takes you beyond the syntax fundamentals, and into a new world of understanding how relational database management systems process SQL queries, and how that impacts your coding practices. Learn about logical query processing, and avoid most common pitfalls and processing limitations. Discover advanced JOIN techniques and how to deal with missing data. Understand the subtleties of ternary logic, SELECT expression evaluation, grouping logic, and how to implement efficient paging and ordering. By the end of the course, you’ll be able to take advantage of the nuances of logical query processing to easily troubleshoot and solve daunting SQL challenges elegantly and efficiently.
Topics include:
- Query logical processing order
- Advanced join processing
- Filtering rows with ternary logic predicates
- Grouping and aggregating data efficiently
- Advanced group filtering
- Ordering and paging result cursors
Topics include:
- Query logical processing order
- Advanced join processing
- Filtering rows with ternary logic predicates
- Grouping and aggregating data efficiently
- Advanced group filtering
- Ordering and paging result cursors
Skills covered
Desktop DatabasesSQLDatabase AdministrationAdvancedDatabase DevelopmentDatabase ManagementData AnalysisProgramming LanguagesData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware Development
Concepts
0. Introduction
- 01 - Course introduction
- 02 - Course agenda
- 03 - Tooling
- 04 - Introducing the demo database
- 05 - Using the code files
1. Constructing Query Source Data Sets
- 06 - Single data source queries
- 07 - Dual source query processing
- 08 - Joining multiple source data sets
- 09 - Challenge - Hybrid multi-table join
- 10 - Solution - Hybrid multi-table join
2. Row Filters
- 11 - Filtering source rows
- 12 - Missing information and ternary logic
- 13 - Dealing with ternary logic in SQL
3. Grouping
- 14 - Grouping
- 15 - Dealing with NULLs and elimination duplicates
- 16 - Group filters
- 17 - Challenge - Filtering and grouped query
- 18 - Solution - Grouped query with Distinct
4. Ordering and Paging
- 19 - Presentation ordering in multitier architecture
- 20 - Ordering result sets
- 21 - Paging result sets
Conclusion
- 22 - Course review and takeaway
- 23 - Feedback and additional resources