SQL Programming for Data Analysis: Ultimate Guide

Explore databases and data modeling, including tables, views, schemas, primary and foreign keys, and relationships, and compare star and snowflake schemas for data warehousing and bi.

Master MySQL data types—numeric, string, and date and timestamp—and SQL syntax by using select, from, where, and order by, plus UTC timestamp storage and case-insensitive keywords.

Learn to create a MySQL database for an e-commerce dataset, load data from CSV, define tables with primary and foreign keys, and generate an ERD model through reverse engineering.

Create a MySQL ecommerce database, load calendar, product, seller, and fact order data from CSV files with local infile, verify row counts, and define composite primary keys and foreign keys.

Learn to create an erd data model by reverse engineering an ecommerce database, importing tables, and mapping one-to-many relationships among seller, order, and product tables.

Learn to use select statements to pull columns or rows, apply distinct values, limit results, handle nulls, order data, and filter records with where and between.

Explore lab 2A to master select statements in the e-commerce data set. Inspect tables with describe, use distinct and null handling, and order results with order by, limit and offset.

Filter order data with where predicates to target product IDs and payment types. Isolate 2019 orders using date filtering, wildcards, null checks, and between ranges.

Explore join operations in SQL, including inner, outer (left and right), cross, self, and anti joins, plus conditional temporal joins using primary and foreign keys across multiple tables.

Explain inner joins of fact orders with dim product to get categories, and left outer/anti-joins to list sellers with or without orders, including CAD price and purchase date.

Learn how to perform a cross join between product categories and provinces using a derived table and cartesian product, then execute a self-join to reveal each employee's manager.

Explore conditional join operations with temporal data in sql, using self joins with aliasing (e1, e2) and the between operator to determine the managers who approved course invoices across expenses.

Explore set operators in SQL for data analysis, comparing union and union all, and learn how to simulate intersect and except in MySQL with inner and left joins, with examples.

Explore set operators in SQL, focusing on union and union all, as they combine results from the e-commerce seller updates table, demonstrate deduplication, duplicates, and performance trade-offs.

Understand how intersect works as an inner join that returns distinct records, and implement a MySQL workaround using select distinct with an inner join between two tables.

Explore the except set operator as an anti join workaround in MySQL using a left join and null checks to identify records present only in the update table.

Explore functions and aggregations in sql programming for data analysis, covering date and time functions, string functions, logical and conditional operations, window functions, and data aggregation and grouping.

Learn window functions in sql, using over, partition by, and order by to compute running totals and moving averages; view ranking and partitioned aggregations with group by and having.

Master scalar and string functions for data analysis in an e-commerce database, including year, month, weekday extraction, timestamp diff, and concat, mid, and substring index.

Master logical functions in SQL by using regular expressions to filter the payment installments and create calculated columns with if and case when statements.

Explore window functions by ranking products by price using rank over, with optional partition by product category name in English, and joining fact_order and product tables.

Learn to use aggregate functions with group by and having to compute key indicators such as number of sellers, orders, unique products, and the average price from e-commerce data.

explore scalar and multi-value subqueries, correlated subqueries, and derived tables in sql, using examples like average value comparisons and product category filtering across order and product tables.

Master scalar subqueries in where clauses by computing the average freight value from the e-commerce fact order table and filtering to return orders and products.

Explore correlated subqueries in SQL using the e-commerce fact order table to identify each customer's latest purchase date, leveraging max(order_purchase_date) and outer–inner query logic.

Explore derived queries and derived tables to list each customer's latest order date using inner joins and group by, then count products per category with a derived table.

Learn how views create virtual, non-physical results from joined data for reporting and access control. Understand temp tables as transient, session-scoped structures and common table expressions as reusable query components.

Create a virtual view from joined tables in the e-commerce database to simplify queries. Query the view to compute total revenue by city and province using common table expressions.

learn to create and use a temporary table in mysql, inserting unique english product categories into product_category for session-based testing and data validation, with notes on table variables and alternatives.

Create and use common table expressions to build temporary query results, count products by category, and explore recursive CTEs for hierarchical employee data.

Explore rollup for subtotal and grand total in group by queries, and build pivot and unpivot tables using case when, cross join, and union all to analyze data.

Learn how grouping sets with rollup generate subtotals and a grand total in product counts by category, and explore creating pivot tables with e-commerce data.

Learn to create pivot-like tables in MySQL using sum with case when to count order statuses (delivered, shipped, canceled, unknown), grouped by product category name in English.

Learn to create a temporary pivot table and unpivot its results using case when and cross join in MySQL, since MySQL lacks an unpivot function.

Learn to modify data in SQL by creating backup tables and inserts, updates, and deletes. Explore explicit versus implicit inserts, where clauses, and practical examples with a fact order table.

Create a backup table named fact_order_bcp from the e-commerce.fact_order using create table and select, illustrating best practice before data manipulation and testing.

learn how to insert records in sql with explicit column lists and implicit inserts, manage foreign key checks, and handle nulls, defaults, and multiple rows in e-commerce orders with integrity.

Practice updating and deleting records in SQL by using update statements with set clauses, inspecting before and after states, handling multiple columns, and ensuring proper semicolon separation.

Delete records by order id and by date range using date_sub(now() interval 1 year). Verify results and demonstrate truncating a table, restoring data from backup, and dropping the backup table.