Practical Postgres: Master The Most Important Parts of SQL

Learn how Postgres uses tables to organize data into rows and columns, enforce data types, and distinguish records with a unique id, plus common createdat and updatedat timestamps for auditing.

Explore how to structure data in Postgres using key data types like varchar, integer, decimal, text, date, time, and time stamp, with a practical pet example and table design insights.

Create an appointments table alongside the pets table, with fields for id, pet name, visit date and time, doctor's note, charges, and createdat and updatedat.

Explore how entity relationship diagrams help read and plan database structures, showing table columns, primary and foreign keys, and one-to-one, one-to-many, and many-to-many relationships, with practical pet system examples.

Implement a relational database design by creating the Leaf Land database, building category and product tables, and wiring a foreign key from product.category_id to category.id.

Apply inner joins and table aliasing to pull user names with reviews and ratings, join categories, products, orders, and addresses, and filter by price to identify shipped items.

Walks through building a user insights query in Postgres: compute total spend per user via joins and sums, then identify each user's favorite category with a cte and nested select.

Update the shipping tracking table by using a select query to filter orders created after 2024-07-01, then set status to pending within a transaction.

This exercise fixes two issues: automatically syncing the product table's total stock and correcting updated columns, starting with the updated app solution for the product table and then stock solution.