Advanced SQL for Application Development

Course details

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.

Topics include:
Creating tables and loading data
Parameterizing SELECT statements
Indexing tables
Object relational mapping
Partitioning
Error trapping
Monitoring and logging
Automated schema migration

Skills covered

Mobile Device ManagementSQLDatabase AdministrationFull-Stack Web DevelopmentAdvancedDatabase DevelopmentPythonDatabase ManagementData AnalysisWeb DevelopmentProgramming LanguagesNetwork and System AdministrationData ScienceBusiness Analysis and StrategyBusiness Software and ToolsOpen SourceSoftware Development

Concepts

0. Introduction

01 - Getting started with SQL for application developers

1. Advanced Querying with Select

02 - Designing an ecommerce application database
03 - Creating tables and loading data
04 - Using environment variables for connection parameters
05 - Connecting to a database
06 - Parameterizing SELECT statements
07 - Avoiding N+1 queries
08 - Challenge - Write a parameterized query in Python
09 - Solution - How to write a parameterized query in Python

2. Indexing Tables

10 - Overview of index types
11 - Using B-tree indexes and range scans
12 - Using hash indexes and equality operations
13 - Covering indexes
14 - Indexes and bulk data loading
15 - Avoiding index locks
16 - Challenge - Define a B-tree and a hash index
17 - Solution - Defining B-tree and hash indexes

3. Object-Relational Mapping (ORM)

18 - Overview of object-relational mapping
19 - Introduction to SQLAlchemy
20 - Installing SQLAlchemy
21 - Query with SQLAlchemy
22 - Limitations of ORM and cautionary advice
23 - Challenge - Create an ORM model
24 - Solution - Create an ORM model

4. Designing for Scalability

25 - Overview of partitioning
26 - Range partitioning
27 - List partitioning
28 - Hash partitioning
29 - Querying partitioned tables
30 - Using read-only replicas
31 - Challenge - Define a partitioned table
32 - Solution - Defining a partitioned table

5. Designing for Reliability

33 - Database errors
34 - Error trapping
35 - Database not responding, timeouts, and exponential backoff
36 - Connection pooling and bulk heads
37 - Monitoring and logging best practices
38 - Challenge - When your Python application fails
39 - Solution - When your Python application fails

6. Designing for Maintainability

40 - Understanding schema changes
41 - Ad hoc scripts for schema changes
42 - Automated schema migration with Flyway
43 - Automated schema migration with Liquibase
44 - Challenge
45 - Solution

Conclusion

46 - Key considerations