Advanced SQL Bootcamp

Master subqueries in sql by using inner queries inside outer ones to perform select, update, create, and delete operations, including basic from clause subqueries and with clauses.

Practice writing subqueries in from and join clauses using PG Admin to nest selects, alias subqueries, and perform multi-step data retrieval.

Master common table expressions in Postgres, or with clauses, to build temporary, reusable result sets that simplify complex multistep queries and work across create, select, update, and delete statements.

Learn how to use subqueries for comparisons in where and having clauses, including dynamic values and aggregates, with examples on surgical costs and vital signs.

Master using in and not in with subqueries in Postgres to filter data by dynamic lists, compare with joins for performance, and implement with patient and surgery examples.

Use any and all in sql subqueries to compare values with single-column subquery. In where or having clauses, sum equals any, in equals any, not in equals not all; nulls.

Construct recursive common table expressions in Postgres using with recursive, base terms, and union all to model hierarchies like employees to managers or orders with parent IDs.

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.

Explore window functions in Postgres to aggregate by group and perform dynamic calculations within groups, using examples like movie lengths and regional sales to rank and compare values.

Master windowed calculations in Postgres using over, partition by, and order by to compute within-group metrics while preserving granularity.

Explore the window clause and window functions in SQL, defining reusable windows with partition by and order by. Apply avg, sum, rank, row_number, lag, and lead within those windows.

Practice window functions in Postgres with exercises: identify surgeries within 30 days of prior surgery, rank three physicians by department admissions, and find resources exceeding 50% of a surgery's cost.

Explore SQL window functions with common table expressions and lag to compute days between surgeries, identify top admitting providers by department, and flag resources over 50% of total costs.

Master self-joins in SQL to analyze hierarchical and network-like data, using descriptive aliases to join a table to itself, with examples like employees and managers and patient readmissions.

Discover how cross joins generate all possible combinations of rows from two tables, forming a Cartesian product, and compare explicit cross join syntax with the comma method, noting performance cautions.

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.

Learn how using keyword and natural joins work in PostgreSQL, leveraging common column names for joins and avoiding cross-join pitfalls and audit columns, with guidance to prefer using keyword.

Apply advanced join techniques in Postgres to pair physicians with practices, compute average systolic and diastolic blood pressure by admitting provider, and count unmatched surgeries in the surgical cost table.

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.

The intersect operator returns only rows common to two queries, requires matching columns and data types, and removes duplicates, with examples using surgery IDs and patients with both encounters.

Master the except set operator in SQL, returning rows from the first query not in the second, with union and intersect-like syntax and same number of columns.

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 grouping sets in PostgreSQL to perform multiple group by aggregations in a single query, including state, county, and overall totals, with practical examples.

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 roll up in sql to generate ordered, hierarchical grouping sets and compare it with cube, then apply roll up to hospital and patient data to summarize encounters and ages.

Master sql grouping techniques with cube, roll up, and grouping sets through hands-on exercises: compute averages by weight and height, track surgical admissions by date parts, and analyze patient demographics.

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.

Apply the Postgres comment command to annotate tables and columns, view comments with object_description and column_description, and use PG admin to manage database object documentation.

Add and drop constraints in Postgres to enforce data quality, using alter table to add check, unique, or not null constraints, and drop constraint or drop not null as needed.

Learn how to add and drop foreign key constraints in PostgreSQL using alter table, linking child and parent tables. See practical examples with physician IDs in patient visits and encounters.

Practice schema structure exercises by adding a comment for ICD admission, implementing constraints to ensure discharge date time is after admission date time or empty, and verifying with information schema.

Apply schema changes by adding column comments, enforcing not null on surgical admission date, and implementing a check constraint for discharge after admission, then drop constraints to verify results.

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.

Begin and end transactions in Postgres with begin, begin transaction, commit, end, and rollback to manage atomic SQL blocks.

Learn how to use save points within Postgres transactions to rollback to a point without aborting the entire transaction, then release or commit.

Understand how database locks control data access by locking tables within transactions and how Postgres handles automatic locking, plus how to manually lock tables with the lock table command.

Practice advanced transactions by updating balances, rolling back, using savepoints, reverting a physicians table update via log table, dropping a table within a transaction, and verifying changes.

Master transactional sql techniques, including begin transaction, lock tables, update accounts, and commit. Learn roll back and savepoint strategies to revert drops and preserve data integrity.

Explore range partitioning in Postgres to improve query performance by dividing a base table into non-overlapping date ranges, creating partitions and a default catchall.

Learn to partition Postgres tables by lists, creating explicit list partitions (e.g., by hospital id or region) and a default partition. Build base tables, define partitions, and verify with inserts.

Learn how to partition tables by hashes using modular arithmetic, defining a modulus and remainder to create hash partitions like hash zero, hash one, and hash two.

Explore table inheritance in Postgres, where a child table inherits columns from a parent. See how data is visible through both tables, and how inserts, updates, and deletes behave.

Practice creating partitioned tables in Postgres, using ranges, lists, hashes, and inheritance; build an encounters table partitioned by hospital id and a vitals table by a date time field.

Create and populate partitioned tables for encounters and vitals by hospital ID and collection date time, define primary and foreign keys, and build partitions with indexes.

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 to modify and delete views in Postgres using create or replace view, drop view if exists, and alter view to rename, rename columns, or set defaults, with examples.

Learn how Postgres updatable views map insert, update, and delete to the underlying table, and how to use with check option to enforce view constraints and protect data integrity.

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

Explore how recursive views in Postgres encapsulate recursive common table expressions inside a view. Learn through Fibonacci sequence and orders hierarchy examples using create recursive view and union all.

Create and modify sql views and a unpopulated MV Hospital Encounters; build a primary care patients view excluding sensitive location data, then rename to MHB Hospital Encounters Statistics.

Create and refresh views for patients’ primary care data, join with physicians, and build a materialized view of hospital encounters by year and month with defaults and renaming.