YoVDO

Advanced SQL for Data Scientists

Offered By: LinkedIn Learning

Tags

SQL Courses Data Analysis Courses Relational Databases Courses JSON Courses Data Modeling Courses Bloom Filters Courses User-Defined Functions Courses

Course Description

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

Related Courses

Algorithms for Big Data
Indian Institute of Technology Madras via Swayam
Graph Search, Shortest Paths, and Data Structures
Stanford University via Coursera
Algorithms for Searching, Sorting, and Indexing
University of Colorado Boulder via Coursera
Cassandra Data Modeling Essential Training
LinkedIn Learning
Algorithms with Prediction
Simons Institute via YouTube