PostgreSQL Data Manipulation Playbook

Data modifications are a critical aspect of application design. In this course, you will learn about challenges associated with multi-user data access and the tools that PostgreSQL provides to tackle them, so you can avoid data consistency issues.
Course info
Level
Intermediate
Updated
Apr 25, 2019
Duration
2h 35m
Table of contents
Course Overview
Understanding SQL Sublanguages
What Are Concurrency Challenges?
Understanding ANSI Concurrency Phenomena
How ANSI Isolation levels Solve Concurrency Challenges
Using Transaction Control Language
Inserting Data with the INSERT Statement
Using the UPDATE Statement
Deleting Data Using the DELETE Statement
Performing Bulk Deletes
Course Review and Wrap Up
Description
Course info
Level
Intermediate
Updated
Apr 25, 2019
Duration
2h 35m
Description

Multi-user database applications involve many challenges. While many designers focus on performance, scalability, and other very important aspects, data modifications and consistency challenges are sometimes overlooked. In this course, PostgreSQL Data Manipulation Playbook, you will learn about the various concurrency phenomena that may occur when multiple users access shared resources. First, you will cover how to predict, prepare, and plan for these challenges. Next, you will discover how to use PostgreSQL rich DML syntax to tackle data modifications, and how to use advanced techniques to solve harder challenges, associated with large scale data modifications. Finally, you will explore how to avoid many common pitfalls along the way. After completing this course, you will have both the fundamental theoretical knowledge needed to understand concurrency challenges, and the practical tools and techniques you will need to tackle these efficiently for your database applications.

About the author
About the author

Ami is a senior instructor and mentor with over 25 years of experience in data tier architecture, data modeling, database design, and SQL development.

More from the author
Querying Data Using T-SQL
Beginner
2h 7m
Sep 19, 2019
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
(Music) Welcome to the PostgreSQL Data Manipulation Language course. My name is Ami Levin, and I've been living, breathing, working, speaking and teaching data and databases for longer than I would like to admit. SQL is one of only a few select technologies that have survived for five decades and still is one of the most widely used programming languages in the world, and there's a good reason for it. SQL is magic, and in this course, I will try to enchant you with it. This course is intended for people with some basic experience with SQL in general and PostgreSQL in particular. However, even if you're not 100% confident with your SQL skills, in this course, we will walk together and cover both the theoretical aspects of the challenges associated with data modifications and multi-user environments, and the DML syntax from the grounds up. The sky and time will be our only limits. If somehow you manage to endure this course, you will receive the most valuable gift I can offer: the knowledge and understanding of how to design and write applications that manage and safeguard your organization's most valuable asset, its data. We will learn about fascinating topics such as concurrency challenges with our friends, Jim and Ruth, how the ANSI SQL standard helps us deal with these challenges, how to define and control transactions, and of course, how to write efficient insert, update, and delete statements. For dessert, we will dive deeper into advanced techniques that you will be able to use to tackle the very common and extremely painful real-world challenge of large-scale data modifications. Are you up for the challenge? If so, let's get started on our journey down another SQL rabbit hole!