SQL for Data Analysis: Advanced SQL Querying Techniques

Explore where to write SQL code, install MySQL and MySQL workbench, and load data for this course using either provided SQL scripts or CSV uploads.

Install MySQL on Mac by downloading MySQL Community Server, selecting the correct ARM or x86 macOS version, and running DMG installer with a secure password to begin using MySQL Workbench.

Learn to load data into MySQL Workbench using the provided SQL scripts and CSV files, create and populate the Maven advanced SQL schema with nine tables ready for querying.

Review the basics of SQL, focusing on the SELECT statement and basic queries, and introduce the big six clauses and common SQL keywords to provide foundational knowledge for advanced querying.

practice basic joins by identifying products that exist in the orders or products tables but not in the other, in the Candi database, using a join-based approach.

Use left and right joins to find products present in the products table but missing from the orders table. Learn to refine to a clean left join query.

Learn how to join tables on multiple columns like year and country using and in the join condition, with examples from happiness scores and inflation rates, including aliases.

Self join the products table to pair each product with another. Filter price differences under 0.25 with absolute value, keeping only where first product name is less than the second.

Learn how to use inner, left, right, and full outer joins to combine data across tables within the from clause, plus self, cross joins, and unions with union all.

Execute a MySQL query using a subquery in the select clause to compute the average unit price, derive the price difference, and sort by unit price descending.

Learn to write subqueries in the from clause to list each factory with the products it produces and the number of products, using inventory management examples.

Explore how any and all filter data via subqueries in where and having clauses, comparing them with exists, using happiness scores across 2019 to 2024.

Explore exists and correlated subqueries by filtering happiness scores to only countries in the inflation rates table, then compare readability and speed with an inner join.

Learn to filter products by using a subquery in the where clause and the all keyword, returning items with unit prices below all Wicked Talkies prices.

Reference a cte multiple times within a query to improve readability and efficiency, using a 2023 data example that compares happiness scores by region with a self-join.

Explore using common table expressions to build a results table of orders over $200 and a follow-up query that counts those rows for the sales analysis assignment.

Use multiple ctes to compare 2023 and 2024 happiness scores, joining on country. Compare readability by mixing ctes with subqueries or using ctes only to filter where 2024 exceeds 2023.

Rewrite your sql code to use multiple common table expressions (ctes) instead of subqueries, ensuring the output matches the previous assignment.

Compare subqueries, CTEs, temp tables, and views to store and reuse query results. Understand how temp tables exist per session while views persist across sessions for data analysis in SQL.

Learn to apply window functions to an orders report, adding a per-customer transaction number column that numbers each customer's first, second, and subsequent transactions.

Learn to use string functions in SQL to create a new column by combining factory name and product ID, as shown in the results preview.

Learn to clean and combine data by removing apostrophes and spaces from factory names, replacing them with hyphens, and concatenating with product id using a common table expression.

Replace null divisions with 'other' using coalesce, then identify each factory's top division with window functions, CTEs, and joins to fill missing values and showcase advanced SQL techniques.

Identify and handle duplicate values in SQL using group by with count and having, distinct for fully duplicated rows, and window functions for partial duplicates, with practical demos.

Identify and report duplicate values in student records by generating a report of students and their emails from the students table, while excluding duplicate records.

Master rolling calculations in SQL by computing subtotals with window rollup, cumulative sums across rows, and moving averages with window functions to analyze data across rows.

Create a report showing total sales by year and month, with a cumulative sum and a six-month moving average, using orders and products.

Analyze the players table to compute age at debut, age at final, and career length, then determine starting and ending teams by year with salaries table joins.

Explore advanced sql techniques to compare players by birthday, batting arms, and debut-era trends, employing groupconcat, pivot with case statements, and lag window calculations.