Data Analysis: Advanced SQL, 1000x Faster Python & Visualize

Install PostgreSQL on macOS by downloading the macOS 16.1 dmg from postgresql.org and running the interactive installer; include server, pgadmin four, and stack builder, then set the superuser password.

Install Postgres SQL and Pgadmin on Windows by running the installer, setting a password, and optionally using Stack Builder to add tools and plugins.

Download a dvd rental backup, create the dvd rental database in pgadmin, restore the backup, and explore 15 tables, views, and sample data via a simple actor query.

Restore a Postgres database from dot sql backup files by creating a new database, running schema.sql to establish the structure, and loading data via insert statements for the Pagila database.

Learn how to backup and restore the Northwind PostgreSQL database using the Northwind dot SQL file, create the database in pgadmin, run queries, and verify 14 tables.

Create base tables product, customer, and orders using the provided SQL script, insert records, and verify row counts in the environment.

Learn how to add new records to a database using the insert statement, including insert into syntax, column lists, value order, and multi-row inserts with a product table example.

Create an employee table in SQL with an integer primary key, a name field, and a ten-digit phone, then insert single or multiple records and verify row counts.

Learn how the select statement retrieves specific data from a product table, choosing exact columns like name and category, improving efficiency and controlling result order.

Discover how databases install on a server, organize data into tables of rows and columns, and use select queries in PostgreSQL via Pgadmin to retrieve specific columns or whole tables.

Explore how the sql distinct keyword removes duplicates to return unique values, such as categories and unique name and is_active combinations, and supports single columns, multiple columns, and calculated expressions.

Explore aggregate functions in SQL with practical queries on a product table, calculating count, min, max, and average of price, and using single queries to combine results in Postgres.

Explore SQL filters through the where clause, using comparison, logical, range, membership, and search operators to narrow results and boost query performance with indexes.

Learn SQL comparison operators as the building blocks of data filtering, including equal to, not equal to, greater than, and less than, with numeric, text, and date examples.

Explore sql logical operators and, or, and not to combine multiple conditions into precise or broader filters, using intersection and union concepts to refine product filters.

Master numeric filtering in sql by using the where clause to refine results on stock and price with equals, not equals, greater than, less than, and/or conditions.

Filter string columns with the sql where clause to refine results using equals, not equals, and combine conditions across product name and category with and or.

Master range operators in SQL with between, an inclusive filter for numbers, dates, times, and text. Write clear queries by applying proper bounds and using between for inclusive ranges.

Filter date columns with the where clause to retrieve orders on a specific date, using equals, not equals, greater than, less than, and/or across order date and product id.

Filter boolean data directly in SQL with the where clause to show active products, using is active equals true, 1, or not equals to true, and with category filters.

Learn to filter a single column with the in keyword to retrieve multiple values, such as stock values 15 or 0, or product names like laptop and monitor.

Discover how to filter using between on price ranges and order date ranges, with inclusive endpoints, and apply multiple value filters instead of or conditions on a single column.

Learn to filter string columns with sql like and wildcards, using starts with, ends with, contains, underscores, and percent signs on product names.

Learn how inner joins link the product and orders tables using a common key, such as product_id, to retrieve combined records while preserving data integrity.

Explore left join fundamentals by combining product and orders tables, showing all records from the product table with matched orders via on condition, and nulls for unmatched rows.

Understand the right join in SQL: retrieves all records from the right table and matching records from the left, filling nulls where no match, illustrated with product and orders tables.

Explore SQL views as virtual tables that simplify queries and secure data by showing only selected columns or rows. Create and use views to query subsets without altering the table.

learn how to create and use sql views like employee_view and employee_filtered_view, query them as base tables, and see updates when the underlying employee table changes.

Learn how the SQL union operator combines the result sets of two select statements, removes duplicates to keep unique rows, and merges employee records across sources.

Explore the SQL union all operator to combine results from two tables, showing how union all returns duplicates while union removes them, with multi-column examples.

Explore the upper and lower string functions in SQL to convert text to uppercase or lowercase, standardizing user input for case-insensitive searches in reports and product data.

Learn how split_part and replace in SQL transform text data by splitting strings on a delimiter and replacing characters, with examples parsing product names and cleaning names for structured data.

Explore the SQL concat function to merge text from multiple columns, control separators, and create readable outputs for product names, categories, and prices in reports.

Explore default values in sql by creating an employees table, using serial ids and a default name of unknown, and inserting records with and without names to observe defaults.

Learn to handle null in sql by creating an employee table with not null and null columns, inserting null values, enforcing constraints, and querying nulls and salary sums.

Explore case statements in SQL to apply conditional logic and categorize products by price and active status using simple and advanced cases, including or conditions.

Use the coalesce function to handle null values in SQL. See the employee table replacing null emails with 'no email' and null salaries with 0; it does not alter data.

Learn how the sql null if function replaces specific salaries with null to handle conditional null values and exclude them from calculations and reports, demonstrated with an employee table.

Create a sales table with sale_id as serial primary key, sale_date as date, product_name as varchar(100), and amount as decimal(10,2), then insert records and verify via select * from sales.

Explore casting and formatting date values in SQL by converting the order date and today's date with to_char, now, and cast to produce date-only or text representations in multiple formats.

Explore extracting day, month, year, day of week, and quarter from date fields using the extract function, and format dates with the two underscore character function for readable reports.

Explore date truncate to group data by month or year, calculate end of month with interval arithmetic, and use extract and interval operations for time series analysis.

Master inner queries in sql, using subqueries to filter data dynamically and locate specific products and orders by id, name, price, and sold status.

Explore inner queries and subqueries to filter data with a where clause, using inner results to learn when to use equals to or in, and ensure a single column mapping.

Explore common table expressions (ctes) in sql, enabling readable, reusable queries using temporary result sets referenced within select, insert, update, or delete statements.

Explore common table expressions (ctes) with filters in sql, using an active products example to show how to apply conditions inside the cte and query the results.

Explore common table expressions (ctes) and unions in sql with a product category example, comparing union and union all, and learn to define a cte using the with clause.

Explore recursive queries with a common table expression to generate numbers from 1 to 10, using a base case, union all, and a stop condition.

Discover recursive common table expressions in SQL by generating even numbers from 2 to 10 with a base case and union all, then query the results.

Generate the Fibonacci sequence in SQL with a recursive CTE, starting from n, a, b, and iterating via union all to reach the 10th position.

Explore recursive cte queries to build an employee hierarchy, retrieving direct and indirect reports with union all, tracking level, and identifying managers from hierarchy data.

Learn how to store multiple values in a single column using arrays in PostgreSQL, create array columns with square brackets, and retrieve values via array indexing.

Learn to apply aggregate functions to arrays in SQL, using array length to count values and unnest to sum marks for each student.

Explore how to use arrays in SQL with the all and any operators to filter on multi-valued columns, including combined conditions with and/or, as shown with a students table.

Discover how temporary tables boost performance in data-heavy systems by creating, populating, querying, and dropping session-based temp tables like temp_employee and temp_product.

Learn how to create and use temporary tables, such as updated_product, to store session data, run aggregates like max(stock), and filter with where clauses, noting they vanish after the session.

Learn to copy and back up SQL tables with create table as select and insert into, and create blank or electronics category subset tables from an existing product table.

Explore range partitioning to create date-based partitions for a sales table, routing data automatically to yearly partitions and boosting query performance on time-based data.

Insert data into a range partition table and rely on automatic routing by the partition key to the 2023 and 2024 partitions, validating with queries.

Learn how range partitioning uses partition pruning to scan only the relevant partition when filtering by the partition key, such as sale date.

Explore list partitioning in databases by region, creating a partitioned customers table and partitions for North and South to improve region-specific queries.

Insert data into the list partitioned customers table and observe automatic routing by the region column to the North and South partitions, with queries verifying partition placement.

Explore list partitioning and partition pruning by filtering on the region partition key to scan only relevant partitions, boosting SQL query performance.

Apply hash partitioning to evenly distribute data across partitions using a hash function on the customer_id column and modulus four, enabling load balancing and parallel queries.

Insert into a hash-partitioned orders table automatically routes records by customer_id hash, distributing data across partitions and mapping all hash values to partitions.

Utilize a default partition to catch data that falls outside existing partitions, ensuring resilient inserts and preserving data quality when unexpected regions appear.