Class Central is learner-supported. When you buy through links on our site, we may earn an affiliate commission.

LinkedIn Learning

Advanced SQL for Data Scientists

via LinkedIn Learning

Overview

Learn advanced techniques for analyzing large data sets with SQL. Find out how to build sophisticated data models, optimize queries, extend SQL with user-defined functions, and more.

Syllabus

Introduction
  • Advanced SQL techniques for data science
  • What you should know
1. Data Modeling: Tables
  • Rules of normalization
  • Denormalization
  • Partitioning data
  • Materialized views
  • Read replicas
  • Challenge: Design a data model for analytics
  • Solution: Design a data model for analytics
2. Data Modeling: Indexes
  • B-tree indexes
  • Bitmap indexes
  • Hash indexes
  • GiST and SP-GiST indexes
  • GIN and BRIN indexes
  • Challenge: Choosing an optimal indexing strategy
  • Solution: Choosing an optimal indexing strategy
3. Query Optimization
  • EXPLAIN and ANALYZE commands
  • Generating data with generate_sequence
  • Generating time series data
  • Analyzing a query with WHERE clauses and indexes
  • Analyzing a query with a join
  • Challenge: Optimize a query using an explain plan
  • Solution: Optimize a query using an explain plan
4. User-Defined Functions
  • Extending SQL with user-defined functions
  • SQL query functions
  • Function overloading
  • Function volatility
  • PL/Python functions
  • Challenge: Write a user-defined function
  • Solution: Write a user-defined function
5. Special-Purpose Functionality
  • Federated queries
  • Bloom filters
  • Hstore for key-value pairs
  • JSON for semi-structured data
  • Hierarchical data and ltrees
  • Challenge: Design a table to support unstructured data
  • Solution: Design a table to support unstructured data
Conclusion
  • Next steps

Taught by

Dan Sullivan

Reviews

4.7 rating at LinkedIn Learning based on 480 ratings

Start your review of Advanced SQL for Data Scientists

Never Stop Learning.

Get personalized course recommendations, track subjects and courses with reminders, and more.

Someone learning on their laptop while sitting on the floor.