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

Provider Logo

Advanced SQL for Application Development

via LinkedIn Learning

Overview

Build scalable and resilient databases to support any application. Learn how to write optimized SQL, use indexes, partition data, and use established design patterns.

Many applications require a relational database. But poorly designed data architecture and poorly written SQL can result in subpar performance, unreliable services, and difficulty scaling. This course includes hands-on examples and lessons that show how to build scalable and resilient databases to support any application. Learn how to write optimized SQL for transaction processing, use indexes to reduce read latency, partition data to improve scalability, and use established design patterns. Instructor Dan Sullivan also explores object relational mapping and shows how to respond to database errors such as query timeouts and refused connections. After completing this course, you will be able to design robust database applications that can scale to meet increasingly demanding workloads.

Syllabus

Introduction
  • Getting started with SQL for application developers
1. Advanced Querying with Select
  • Designing an ecommerce application database
  • Creating tables and loading data
  • Using environment variables for connection parameters
  • Connecting to a database
  • Parameterizing SELECT statements
  • Avoiding N+1 queries
  • Challenge: Write a parameterized query in Python
  • Solution: How to write a parameterized query in Python
2. Indexing Tables
  • Overview of index types
  • Using B-tree indexes and range scans
  • Using hash indexes and equality operations
  • Covering indexes
  • Indexes and bulk data loading
  • Avoiding index locks
  • Challenge: Define a B-tree and a hash index
  • Solution: Defining B-tree and hash indexes
3. Object-Relational Mapping (ORM)
  • Overview of object-relational mapping
  • Introduction to SQLAlchemy
  • Installing SQLAlchemy
  • Query with SQLAlchemy
  • Limitations of ORM and cautionary advice
  • Challenge: Create an ORM model
  • Solution: Create an ORM model
4. Designing for Scalability
  • Overview of partitioning
  • Range partitioning
  • List partitioning
  • Hash partitioning
  • Querying partitioned tables
  • Using read-only replicas
  • Challenge: Define a partitioned table
  • Solution: Defining a partitioned table
5. Designing for Reliability
  • Database errors
  • Error trapping
  • Database not responding, timeouts, and exponential backoff
  • Connection pooling and bulk heads
  • Monitoring and logging best practices
  • Challenge: When your Python application fails
  • Solution: When your Python application fails
6. Designing for Maintainability
  • Understanding schema changes
  • Ad hoc scripts for schema changes
  • Automated schema migration with Flyway
  • Automated schema migration with Liquibase
  • Challenge
  • Solution
Conclusion
  • Key considerations

Taught by

Dan Sullivan

Related Courses

Reviews

0.0 rating, based on 0 reviews

Start your review of Advanced SQL for Application Development

Never stop learning Never Stop Learning!

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

Sign up for free