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.

Learn to filter data with the where clause, using and/or conditions, and retrieve specific columns with aliases. Explore not null and commission examples in SQL queries.

Explore using a case expression to apply conditional logic in a select query, labeling salaries as underpaid or overpaid in the employees table.

Master SQL pattern searching by using in for set matching, combining with and/or, and applying like with percent wildcards to filter employees by department and name or job title.

Learn how to stack result sets from multiple tables using union and union all, create temporary tables, and apply distinct filtering with inner and outer 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.

Compute the average salaries across all employees and by department using avg, min, and max. Group by department, order by salary, and uncover data stories with SQL.

Compute total salaries using the sum function and apply the average function. Group by department id to show department totals and counts, and order by highest totals.

Generate a running total in SQL using a window function over salary, ordered by salary, and ensure uniqueness with employee ID to handle duplicates.

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.

Learn to compute running date differences in SQL by using lead window function to find the next hire date within department 10, then subtract hire dates to get day counts.

Skip rows in SQL by using row_number over order by to generate a sequence, then filter with mod to return every other (odd) employee from the employee table.

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.

Utilize the partition by clause as a moving group by in window functions to compute per-department counts without a standard group by.

Learn how to handle nulls in queries using group by and partition by. See how nulls are treated as a single group and how count(*) differs from count(column).

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.

List and inspect postgres table constraints using information_schema views, identify primary keys and other constraints, and combine table_constraint with key_column_usage for a complete view of a table.

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.

Import sample data by downloading and unzipping four csv files, then use the SQL copy command in PgAdmin to load sectors, symbols, latest prices, and historical prices.

Count total sectors in the stock market database using count and alias as total sectors. Use where with in and not in to include or exclude specific sector IDs.