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.

Master SQL syntax and conventions for Postgres by learning end-of-statement semicolons, double-dash comments, and the rule to uppercase keywords while keeping table and column names lowercase.

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 and design your first postgres table using a serial id, varchar and text columns, and include createdat and updatedat as timestamp columns with defaults to now for data tracking.

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.

Execute delete statements carefully to remove a single record using delete from pet where id is seven, ensuring a precise where clause to avoid data loss.

Wrap sql statements in a transaction with begin, then verify results and rollback if needed or commit to save changes. The pet table example demonstrates updates, rollbacks, and commits.

Explore like and ilike in Postgres to filter records using % wildcards, matching breeds like lab and labradoodle, with ilike offering case-insensitive comparisons.

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.

Define a primary key with a serial id, ensure uniqueness, and use indexes to speed id lookups; then alter tables to add primary keys to pet and appointment.

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.

Design a practical database for a fictional online plant store, modeling products (plants, soil, accessories), stock status, user reviews, and orders with multiple items shipped to a user.

Model an online store database by defining products with categories and stock. Use a review join table and an order product join table to capture products, users, and quantities.

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.

Learn how Postgres aggregate functions such as sum, average, count, max, and min work with group by to compute per-user totals by summing price times quantity.

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

Learn to use nested queries to calculate total quantity sold per product, join product and category data, and alias the subquery to simplify complex aggregate queries.

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.

Create a separate shipping schema and a tracking table to manage order status (placed, shipped, arrived), with an order_id reference to public.order_id, and practice transactions with begin, commit, and rollback.

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.

Learn to create a calculate discounted price function in PostgreSQL, validate input with an if, raise exceptions for negative discount percentages, and return the numeric price rounded to two decimals.

Call a function in public schema to calculate discounted price by passing price and discount percent, observe results, and note the 0–100 validation error; preview triggers in the next lecture.

Automate tracking updates by using a trigger that runs after an order insert to create a shipping tracking entry, returning new for each row.

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.

Explore how Postgres uses jsonb for unstructured json data, add a jsonb metadata column, insert and update json, and query by nested keys with json operators.

Identify long-running queries using Postgres built-in views, such as pg_stat_activity. Terminate problematic queries with pg_terminate_backend to free up CPU resources.

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.

Explore DataGrip as a powerful, advanced editor for Postgres and other SQL databases, with out-of-the-box intellisense, query consoles, CSV export, automatic entity relationship diagrams, and AI-assisted code suggestions.

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