Advanced SQL Bootcamp

Stretch your SQL skills with sub queries by calculating average number of orders per encounter by provider or physician, and identify encounters with top ten order codes and ICU encounters.

Explore SQL problem solving with common table expressions, subqueries, and window functions to calculate physician averages, top ten most common order codes, ICU encounters, high balances, and age-based stay measures.

Full joins combine left and right joins to return all matching and nonmatching rows. Use cases include finding orders without visits, accounts without encounters, or departments without employees.

Master advanced sql joins using cross joins for physician and practice pairs, compute average blood pressure by admitting provider with vitals and encounters, and identify missing surgeries with full joins.

Explore the union operator in Postgres to combine query results, ensure matching column counts and data types, and understand when duplicates are removed versus using union all.

Practice set operations—union, intersect, and except—in sql—with a hands-on exercise. List physicians from encounters, identify primary care admitting providers, and check surgeons in surgical encounters not in physician table.

Apply a common table expression and set operations to query encounters, physicians, and patient data. Build solutions using union, intersect, and except to verify data quality and provider roles.

Explore cube operations in SQL to generate all grouping subsets with grouping sets, roll up, and group by, using real Postgres examples like state, county, and surgeon profits.

Explore how to use the Postgres information schema to inspect database structure, including tables, columns, and constraints, with practical queries across the general hospital schema.

Begin a transaction to group sql statements, then end the transaction to ensure all statements succeed or none apply, preserving data integrity under concurrency with atomicity, consistency, isolation, and durability.

Learn to update data in Postgres using the update and set commands, applying a where clause to target specific rows and update multiple columns.

Learn how views in Postgres store a select query without storing data and simplify complex queries. Restrict data access and name them with v_ or view_ prefixes.

Learn how materialized views in Postgres store query results, create and refresh them with or without data, and manage them with concurrent refresh.