AI Features
Log In
0% completed
All Lessons
Free Lessons (10)
Part I: Preface
Introduction to the Course
Course Overview
Architecture Required
Introduction to PostgreSQL
PostgreSQL
Some Useful Commands
Quiz: Introduction to PostgreSQL
Part II: Introduction to Structured Query Language
Introduction to SQL
Utilizing SQL
The First Use Case
A Word About SQL Injection
PostgreSQL Protocol: Server-Side Prepared Statements
Back to Discovering SQL
Computing Weekly Changes
SQL Style Guidelines
Challenge: Compute Fortnightly Changes
Solution Review: Compute Fortnightly Changes
Part III: Writing SQL Queries
Introduction to Writing SQL Queries
Business Logic
Business Logic in the Database
Business Logic in Application Code
Correctness
Efficiency
Stored Procedures: A Data Access API
Quiz: SQL and Business Logic
Challenge: Implement Business Logic
Solution Review: Implement Business Logic
A Small Application
Introduction to a Small Application
Loading and Querying the Database
Python Application for Chinook Database
Challenge: Top-N Factbook Entries
Solution Review: Top-N Factbook Entries
The SQL REPL: An Interactive Setup
Introduction to SQL REPL
Getting Started with psql
Query Rewriting and Validating
Challenge: Testing in PostgreSQL
Solution Review: Testing in PostgreSQL
Regression Testing
Challenge: Regression Testing in PostgreSQL
Solution Review: Regression Testing in PostgreSQL
Indexing in PostgreSQL
Indexing Strategy
Index Access Methods
Advanced Indexing
Quiz: Loading Database, REPL, Testing, and Indexing
Part IV: SQL Toolbox
Introduction to SQL Toolbox
SQL Tools
Sub-languages of SQL
Anatomy of a Select Statement
Processing Function and Data Sources
Joins and Restrictions
Managing Output in SQL
Ordering with the Order By
Top-N Sorts: Limit and Pagination
Aggregates
Restrict Selected Groups: Having
Grouping Sets
Common Table Expressions
Result Sets Operations
Challenge: Managing the Output in SQL
Solution Review: Managing the Output in SQL
Understanding Nulls
Three-Valued Logic
Null in Applications
Outer Joins Introduce Nulls
Quiz: Queries, Output of Queries, and Nulls in SQL
Challenge: Understanding Nulls
Solution Review: Understanding Nulls
Window Functions
Windows and Frames
Partitioning into Different Frames
Using Window Functions
Relations and Joins
Understanding Relations
SQL Join Types
Quiz: Window Functions, Relations, and Joins
Part V: Data Types
Introduction to Data Types
Serialization and Deserialization
Serialization in SQL
Some Relational Theory
Attribute Values, Data Domains, and Data Types
Consistency and Data Type Behavior
Challenge: Generate Specific Numbers
Solution Review: Generate Specific Numbers
PostgreSQL Data Types 101
Introduction to PostgreSQL Data Types
Boolean
Character and Text
Server Encoding and Client Encoding
Numbers
Sequences and the Serial Pseudo Data Type
UUID, Bytea, and Bitstring
Date/Time and Time Zones
Time Intervals
Date/Time Processing and Querying
Network Address Types
Ranges
Denormalized Data Types
Introduction to Denormalized Data Types
Arrays
GIN Indexing
Composite Types
XML
JSON
Enum
Challenge: JSON Data Type
Solution Review: JSON Data Type
Part VI: Data Modeling
Introduction to Data Modeling
Object Relational Mapping and Tooling for Database Modeling
Object Relational Mapping
Tooling for Database Modeling
Writing a Database Model
Modeling Example
Quiz: Data Types and Data Modeling
Normalization
Data Structures and Algorithms
Normal Forms and Anomalies
Normalization Example
Primary and Surrogate Keys
Constraints
Practical Use Case: Geonames
Loading the GeoNames
Features
Countries
Administrative Zoning
Geolocation
Indexing and Lookup
A Sampling of Countries
Challenge: Create a Histogram
Solution Review: Create a Histogram
Modelization Anti-Patterns
Entity Attribute Values
Multiple Values per Column
UUIDs
Quiz: Normalization, GeoNames Database, and Anti-Patterns
Denormalization
Introduction to Denormalization
Premature Optimization and Functional Dependency Trade-Offs
Denormalization with PostgreSQL
Materialized Views
History Tables and Audit Trails
More About Denormalization
Partitioning
Denormalize with Care
Challenge: Generating Series
Solution Review: Generating Series
Not Only SQL
Introduction to Not Only SQL
Schemaless Design in PostgreSQL
Durability Trade-Offs
Scaling Out
Quiz: Denormalization and NOSQL
Part VII: Data Manipulation and Concurrency Control
Introduction to Data Manipulation
Another Small Application
Introduction to Data Manipulation and Concurrency Control
Data Manipulation and Concurrency Control: Insert
Data Manipulation and Concurrency Control: Update
Data Manipulation and Concurrency Control: Delete
Challenge: Data Manipulation
Solution Review: Data Manipulation
Isolation and Locking
Introduction to Isolation and Locking
Transactions and Isolation
Concurrent Updates and Isolation
Modeling for Concurrency
Putting Concurrency to the Test
Computing and Caching in SQL
Introduction to Computing and Caching in SQL
Views
Materialized Views
Quiz: Data Manipulation, Concurrency, Isolation, and Caching
Challenge: Creating a View
Solution Review: Creating a View
Triggers
Introduction to Triggers
Transactional Event-Driven Processing
Trigger and Counters Anti-Pattern
Fixing the Behavior
Listen and Notify
Introduction to Listen and Notify
PostgreSQL Event Publication System
Notifications and Cache Maintenance
Limitations and Support of Listen and Notify
Batch Update, MoMA Collection
Loading the Data
Updating the Data
Concurrency Patterns and Conflict
Quiz: Triggers, Notifications Feature, and Batch Update
Part VIII: PostgreSQL Extensions
Introduction to PostgreSQL Extensions
What’s a PostgreSQL Extension?
PostgreSQL Extensions
Inside PostgreSQL Extensions
Installing and Using PostgreSQL Extensions
Auditing Changes with hstore
Introduction to hstore
Auditing Changes with a Trigger
Testing the Audit Trigger
From hstore Back to a Regular Record
Quiz: Extensions and Auditing Changes
Million Song Dataset
Loading the Data
Discover the Data Model
Using Trigrams for Typos
The pg_trgm Extension
Trigrams, Similarity, and Searches
Complete and Suggest Song Titles
Trigram Indexing
Challenge: Find the Words Similarity Score
Solution Review: Find the Words Similarity Score
Denormalizing Tags with intarray
Advanced Tag Indexing
Searches and User-Defined Tags
Quiz: Trigrams and Denormalizing Tags
The Most Popular Pub Names
Loading the Data
Geolocating the Nearest Pub
Finding the Nearest Pub
Pubs and Cities
The Most Popular Pub Names
Geolocation with PostgreSQL
Geolocation Data Loading
Finding an IP Address in the Ranges
Geolocation Metadata
Emergency Pub
Challenge: Search for the Nearest Circuits
Solution Review: Search for the Nearest Circuits
Counting Distinct Users with HyperLogLog
Introduction to HyperLogLog
Counting Unique Tweet Visitors
Lossy Unique Count with hll
Getting the Visits into Unique Counts
Scheduling Estimates Computations
Quiz: Geolocation and HyperLogLog
Appendix
PostgreSQL: Setup on the Local Machine
The PostgreSQL extension: How to Find It
A Short List of Noteworthy Extensions
Home
Courses
The Art of PostgreSQL
Introduction to Denormalized Data Types
Learn about denormalized data types.
We'll cover the following...
Extensibility
Extensibility
...
Ask