Master SQL - Simple to advanced 200+ queries

Explore the database model for an employee management system by linking the employees and departments tables through department_id, and prepare them in Pgadmin to run basic to advanced queries.

Explore hierarchical queries and parent-child relationships by testing self joins on an employees table to display each employee with their manager, using concatenation and subqueries.

Build a recursive query to generate an employee tree from a parent-child table. Start with roots (manager_id is null) and use union all to reveal hierarchy to any depth.

Identify each row's position in a parent-child hierarchy by classifying employees as leaf, branch, or root using scalar subqueries, counts, and a sign method in SQL.

Learn to compute a three-point moving average in SQL by combining current sales with two prior values using lag functions and a window ordered by transaction date.

Learn how to calculate the mode in a data set using SQL, counting how often each salary appears, applying dense_rank, and selecting the most frequent value.

Calculate a median using percentile_cont within group by salary, filtered by department 20, and compute averages without highs and lows by excluding max and min salaries.

Learn date arithmetic in PostgreSQL by adding and subtracting days, months, and years with interval, and calculate the number of days between dates using simple queries.

Apply window functions with dense_rank to return the top salaries, handling ties correctly, using inline views and ranking rather than limit for accurate top end results.

Explore how the order by clause in a window function's over clause shapes results within a partition, computing running salaries by department and comparing partitioned totals.

Generate dynamic SQL from meta queries to count rows across thousands of base tables in the public schema, building strings into a SQL statement for maintenance tasks.

Prepare and upload World Bank depth data to a PostgreSQL database, create the international depth table with country and indicator fields, insert data, and preview records for global data stories.