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.

Create the appointment table with id serial, pet name, visit date and time, doctor note, and amount charged in cents. Define nullable columns and default created_at and updated_at timestamps.

Learn CRUD—create, read, update, and delete—by adding and modifying rows in a table, and see how these core operations underpin everyday apps like posting, reading feeds, and deleting content.

Learn how to create data with insert statements by specifying target columns, using single quotes, honoring default columns (id, createdat, updatedat), and inserting multiple rows into the pet table.

Master core select statements in Postgres by learning how to fetch specific columns, filter results with where conditions using and, and limit output for precise data.

Use update statements with update, set, and where to modify specific records, typically by id; learn incrementing or decrementing a column and validating with select.

Master the or operator and parentheses in SQL where clauses to combine conditions, returning dogs that are labs or boxers and cats that are Birman.

Aliases let you rename columns using as, for example pet name to pet_name, and alias tables like pet to p for clearer references when working with multiple tables.

Learn how to use the sql order by clause to sort results by age or createdat, using ascending and descending to list oldest to youngest or newest to oldest.

Master robust Postgres queries by detecting invalid emails, handling nulls, filtering by date ranges, and combining conditions with and/or to produce ordered results in practical scenarios.

Explore why database relationships matter by using IDs and foreign keys to link pets and appointments, illustrating one-to-many relationships and why storing IDs beats name-based links.

Explore one-to-many, one-to-one, and many-to-many relationships with pets, doctors, and appointments. Birth certificates illustrate a 1-to-1 pattern; foreign keys and join tables model connections and guide column bloat considerations.

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.

Explore joins in postgres by building inner, left, right, and full outer joins, using aliases and on clauses to relate products to categories and handle optional data.

Explore how schemas group tables into namespaces in Postgres, with public as default, and how to use a separate reporting schema by prefixing table names, like reporting.metrics, in queries.

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.

Compute total revenue by summing price times quantity, then apply date_trunc to group by month from order created at.

Master common table expressions using the with keyword to replace nested queries and improve readability. Alias cte results, join them as tables, and chain multiple ctes.

Learn to build a Postgres product revenue query: use a cte for sales, left joins, and aggregates for revenue in dollars, distinct order count, and average rating.

Write a practice Postgres query to reveal user insights: list each customer's name, total spend on the platform, and their favorite category by most orders, with a guided walkthrough.

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.

Write a postgres sql query that breaks data by month and returns the top-selling product for each month, its revenue, and the month's total revenue, formatted in dollars.

Write and refine sql queries to compute monthly revenue, revenue by product, and top selling products using joins, date truncation, grouping, and formatting.

Backfill data by chaining inserts from selects into the shipping tracking table, pulling order IDs and adding a hard-coded status via a select statement.

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.

Learn how Postgres views save complex queries for repeat reporting, create a dedicated reporting schema, and quickly pull monthly revenue metrics for investors.

Discover how materialized views optimize analytics by storing query results at a point in time, refreshing periodically to reflect new data, and differ from regular views in Postgres.

Discover the basic structure of a Postgres function and implement a discount function with parameters for original price and discount percentage, using declare, begin, and plpgsql.

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.

Learn to implement triggers in Postgres that auto-update the updated_at timestamp on products and keep total stock in sync by reacting to order_product insertions.

Learn how Amazon RDS hosts Postgres, with automated backups, snapshots, read replicas, and security, plus a free tier; consider Heroku Postgres for easy plug-and-play.

Thank you for taking the practical Postgres course; I hope the concepts learned will help your career and future, and please share feedback.