Get familiar with mastering SQL and PostgreSQL for efficient data handling and application integration.
2.
Introduction to PostgreSQL
3 Lessons
Look at PostgreSQL's robust features, essential commands, and extensibility for modern applications.
3.
Part II: Introduction to Structured Query Language
10 Lessons
Examine SQL principles, security measures, data processing, window functions, and style guidelines.
4.
Part III: Writing SQL Queries
1 Lesson
Grasp the fundamentals of writing, debugging, and maintaining SQL queries in application code.
Recognize the importance of strategically placing business logic in PostgreSQL for efficiency.
6.
A Small Application
5 Lessons
Focus on building a small PostgreSQL application, querying data, and handling SQL in Python.
7.
The SQL REPL: An Interactive Setup
8 Lessons
Master the steps to effectively use psql for interactive SQL testing and regression testing in PostgreSQL.
8.
Indexing in PostgreSQL
4 Lessons
Learn how to use effective indexing strategies and access methods to optimize PostgreSQL.
9.
Part IV: SQL Toolbox
1 Lesson
Get started with enhancing your SQL skills through practical examples and advanced PostgreSQL concepts.
Break apart SQL sublanguages, SELECT statement anatomy, data sources, and advanced joins.
11.
Managing Output in SQL
9 Lessons
Grasp the fundamentals of optimizing query output, sorting, pagination, and aggregate functions in SQL.
12.
Understanding Nulls
6 Lessons
Take a closer look at handling null values in SQL, their implications, and practical applications.
13.
Window Functions
3 Lessons
Investigate PostgreSQL’s window functions, their frames, partitioning, and analytical capabilities.
14.
Relations and Joins
3 Lessons
Piece together the parts of relations and various join types in PostgreSQL.
15.
Part V: Data Types
1 Lesson
Get familiar with data types in PostgreSQL crucial for improving correctness and performance.
16.
Serialization and Deserialization
6 Lessons
Unpack the core of serialization, relational theory, data types, and handling date-specific queries in PostgreSQL.
17.
PostgreSQL Data Types 101
12 Lessons
Examine PostgreSQL's diverse data types, effective data handling, encoding, numeric precision, temporal management, and network analysis.
18.
Denormalized Data Types
9 Lessons
Grasp the fundamentals of PostgreSQL's denormalized data types and their practical applications.
19.
Part VI: Data Modeling
1 Lesson
Take a look at the importance of database schema modeling to simplify operations.
20.
Object Relational Mapping and Tooling for Database Modeling
5 Lessons
Simplify complex topics on database modeling, tooling, and schema refinement with PostgreSQL.
Build on normalization principles to ensure data consistency, integrity, and optimal design.
22.
Practical Use Case: Geonames
9 Lessons
Get familiar with normalizing and querying GeoNames data in PostgreSQL effectively.
23.
Modelization Anti-Patterns
4 Lessons
Unpack the core of PostgreSQL modelization anti-patterns, including EAV models, multiple values per column, and UUIDs.
24.
Denormalization
10 Lessons
Examine denormalization to optimize database performance, manage data efficiently, and address practical coding challenges.
Grasp the fundamentals of PostgreSQL's flexibility in handling NoSQL features, durability, and scaling out.
26.
Part VII: Data Manipulation and Concurrency Control
1 Lesson
Dig deeper into data manipulation, transaction processing, and ensuring data integrity in PostgreSQL.
27.
Another Small Application
6 Lessons
Tackle data manipulation and concurrency control, featuring PostgreSQL’s efficiency in handling inserts, updates, and deletes.
28.
Isolation and Locking
5 Lessons
Build on PostgreSQL's isolation and locking for effective concurrency management in transactions.
29.
Computing and Caching in SQL
6 Lessons
Step through computing and caching techniques in SQL, including views and materialized views.
Get started with PostgreSQL triggers, their transactional processing, and addressing concurrency issues.
31.
Listen and Notify
4 Lessons
Examine PostgreSQL's listen and notify system for asynchronous communication between servers and clients.
32.
Batch Update, MoMA Collection
4 Lessons
Apply your skills to load, update, and manage the MoMA Collection in PostgreSQL.
33.
Part VIII: PostgreSQL Extensions
1 Lesson
Explore PostgreSQL's extensible data types and user-defined extensions, rooted in its original design.
34.
What’s a PostgreSQL Extension?
3 Lessons
Investigate how PostgreSQL extensions enhance functionality through SQL objects and efficient installation.
35.
Auditing Changes with hstore
5 Lessons
Master hstore for auditing changes with triggers, testing, and reverting to regular records.
36.
Million Song Dataset
2 Lessons
Learn how to use the Million Song Dataset for importing, querying, and exploring music data.
37.
Using Trigrams for Typos
6 Lessons
Get started with improving fuzzy searches, typo correction, and efficient querying using trigrams in PostgreSQL.
38.
Denormalizing Tags with intarray
3 Lessons
Work your way through efficient tag indexing and advanced searches using PostgreSQL's intarray extension.
39.
The Most Popular Pub Names
5 Lessons
Apply your skills to load, geolocate, query, and analyze pub name data in PostgreSQL.
40.
Geolocation with PostgreSQL
6 Lessons
Solve problems in geolocation data processing and querying IP addresses with PostgreSQL.
41.
Counting Distinct Users with HyperLogLog
6 Lessons
Simplify complex topics like HyperLogLog for estimating unique counts with minimal storage.
Build on setting up PostgreSQL, utilizing extensions, and optimizing with noteworthy tools.