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.

Identify where to write SQL code across open source and proprietary rdbms, then explore editors like MySQL Workbench, Pgadmin, and the option of online editors.

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.

Install MySQL Workbench on mac by downloading the DMG archive, selecting macOS and the correct OS version (ARM for M1/M3, x86 for Intel), then drag to Applications and open.

download the latest MySQL community server for Windows (9.1.0), install via the MSI with typical settings, run the configurator with a memorable password, and finish to use MySQL Workbench.

Install MySQL workbench on Windows by downloading the MSI installer, running the setup wizard, and completing the installation. Launch the application to verify that MySQL workbench is installed.

Connect MySQL workbench to MySQL by entering the root password and double-clicking the connection box, then explore the Mac and PC interfaces to start writing SQL.

Load course datasets into your SQL editor using either the SQL script route or CSV files, with MySQL-specific create statements and cross-RDBMS compatibility for Postgres, SQLite, and SQL Server.

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.

Load data into any RDBMS by running the create statements SQL file in SQLite. Use DBeaver to query the nine tables and verify results with a select from students.

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.

Explore the big six SQL clauses: select, from, where, group by, having, and order by, and learn their required order, aided by a mnemonic and a forthcoming demo.

Explore common SQL keywords in the select and where clauses, including distinct and count distinct, aggregate functions, case statements, and aliases like GPA range.

Join a hands-on demo reviewing sql basics, covering the big six clauses (select, from, where, group by, having, order by), along with aggregates, distinct, alias, and case statements.

Explore multi table analysis in SQL by mastering joins and unions, including inner, left, right, and outer joins, as well as self joins, with practical use cases and clear syntax.

Explore working with multiple tables in SQL by using joins on common columns to combine data and unions to stack rows, illustrated with happiness scores and country stats.

Learn how to join two tables on multiple columns and choose a join type, using aliases and on conditions to combine happiness scores with country stats and refine output.

Explore the four basic SQL join types—inner, left, right, and full outer—and how they combine tables, their outputs, common usage, tool support differences, and practical examples.

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.

Master joining multiple tables with left joins, starting from a leftmost table, joining on year and country to combine happiness score, continent, and inflation rate.

Learn how to join a table with itself to compare rows, filter results, and reveal relationships such as employees with the same or greater salaries, and managers linked to employees.

Develop a self join query to identify candy product price pairs within 0.25 of each other using the products table.

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.

Explore cross joins and how they generate all combinations of rows from two tables, with caution about large outputs and performance, and learn to write clear self-join examples.

Explore union vs union all in SQL, learn how duplicates are handled and when union all runs faster, with practical demos using tops, outerwear, and happiness scores.

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.

Explore subqueries and common table expressions, learn their syntax, compare pros and cons, and apply guidance on when to use each in practice, including temporary tables and views.

Learn how subqueries nest inside main queries to perform multi-step filtering, such as selecting countries with above average happiness by computing the average first in the where clause.

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.

Explore using a subquery in the from clause to join yearly happiness scores with each country's average, using aliases and left joins to handle different granularities and improve performance.

Combine two subqueries with a union all and join by country to compute yearly happiness scores with country averages. Then filter where the score is one point above the average.

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.

Learn to build subqueries in the from clause to show each factory with its product names and the total number of products, using joins, grouping, and aliases.

Explain subqueries in the where and having clauses to filter data after grouping. Compare regional happiness scores to the overall average using subqueries in both where and having.

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.

Identify products with a unit price lower than all Wicked Chalkis products using subqueries in the where clause, and report the producing factories.

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.

Learn how common table expressions (ctes) create named temporary outputs you can reference in another query, such as showing each country's happiness score with its overall average.

Compare subqueries and CTEs to improve readability, defining named subqueries at the top using the with keyword and referencing them in the main select for cleaner logic.

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.

Join orders to products, compute total amount spent per order, filter orders over 200, then use a cte to count these orders.

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.

Rewrite a solution by using the with keyword to define two ctes subqueries for factories and product names and for number of products, then left join and sort for readability.

Explore recursive CTEs, including anchor and recursive members, union all connectors, to generate sequences and handle hierarchical data, with practical MySQL demos and emphasis on syntax variations.

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.

Master subqueries and common table expressions to build modular, reusable queries; learn alias rules, where and having usage, any/all/exists, and when to prefer joins, ctes, temp tables, and views.

Discover window functions in SQL by breaking down their components, comparing them with aggregates, and exploring practical applications for group data and data analysis.

Explore window functions that apply calculations within non-collapsed windows of data while preserving row granularity. See how partition by country with a row number differs from traditional group by.

Break down the components of a window function—function, over, partition by, and order by—and see how row number uses them to partition by country and order by happiness score.

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.

Discover how to add a per-customer transaction number with a window function (row_number), partitioned by customer and ordered by transaction ID, yielding a clean final output.

Explore window functions beyond row number, including rank, dense_rank, first value, last value, nth value, lead and lag, and use average and ntile within windows for data analysis.

Explore window functions to number rows with row_number, rank, and dense_rank. Compare how each handles ties using a baby girl names example, and learn when to apply explicit ordering.

Master row numbering to create a product rank field that returns 1 for the most popular product in each order, handles ties, and aligns with the results preview.

Use a window function for row numbering to rank most popular products per order. Partition by order id, order by units desc, and apply rank and dense_rank to handle ties.

Explore window functions to extract first, last, and nth values within partitions, using a baby names example to show top names by gender and practical alternatives like row_number.

Return a list of the second most popular product within each order for the assignment, to help the sales team bundle items and increase units sold.

Identify the second most popular product for each order by using nth value, compare with dense_rank, and return the second product id per order via a subquery or a cte.

Learn to use window functions with lead and lag, partitioned by country and ordered by year, to compute year-over-year happiness score changes via a common table expression.

Analyze customer orders over time in SQL for data analysis to display each customer's orders with total units and the change in units from the current to the previous order.

Compute total units per order per customer, order by transaction id, and use window functions to derive prior units and the difference in units over time.

Learn how ntile uses window functions to divide each region's rows into percentiles, enabling top 25% happiness scores by region, with guidance on partitioning, ordering, and CTE usage.

Pull the top 1% of customers by how much they've spent to support a rewards program, and review the sample results showing who spent the most.

Identify the top 1% of customers by calculating total spend per customer from orders and products, joining on product id, summing unit price times units, and using a cte with entile.

Harness window functions to compute moving averages and running totals, including a three year moving average example, while learning duplicates removal and min max filtering for rolling calculations.

Master SQL window functions: apply calculations across data windows with over, partition, and order, producing per-row outputs, compare with aggregates, and explore lead or lag values and moving averages.

Explore sql functions by data type, covering numeric, date/time, and string functions, plus general functions. Learn how functions target specific data types and why you don't need to memorize syntax.

Define SQL functions by parentheses, and note distinct is a keyword, not a function. Explore aggregate, window, and general functions with examples like count, round, and upper.

Apply numeric functions to transform and bin data, using log, rounding, nesting, and floor for binning, plus least and greatest for row and column comparisons, with null handling via coalesce.

Learn how to cast and convert non-numeric text to numeric types in sql to perform calculations, compare cast versus convert, and handle differences across rdbms.

Use numeric functions to bucket customer spend into $0 to $10, $10 to $20, and each $10 range, and generate the requested table for Mandy.

Join orders with products, compute each customer's total spend by multiplying units by unit price, then bin the spend into ten-dollar ranges and count customers per bin.

Explore MySQL date time functions to extract year, month, and day of week, compute current dates, and perform interval calculations with datediff and date add for recency calculation.

Master date time functions by pulling the Q2 2024 orders data and adding a ship date two days after each order date, as shown in the results preview.

Filter orders to q2 2024, extract year and month from order dates, then create a ship date by using date add function to add two days to each order date.

Explore how to clean and transform text data in sql using string functions such as trim, replace, length, upper, lower, and concat, with practical examples on event data.

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.

Explore pattern matching in SQL using substring and instring to extract the first word, with a case statement, like keyword usage, and regex for text patterns.

Master pattern matching in SQL by extracting the first word with substring and instring, then filtering with like and regex to reveal hyphenated terms and patterns.

Engage in a pattern matching assignment to remove 'Wonka Bar' from product names, illustrating current versus new names in the results preview.

Learn to clean product names by removing the Wonka Bar text with replace or substring methods in MySQL, including using instring and case statements to generalize hyphen-driven extractions.

Explore null functions to replace missing values with alternatives using if null and coalesce, compare simple and complex checks, and see practical demo across RDBMS.

Master null functions in sql by completing the assignment that fills missing divisions for two new products, first with other and then with the most common division per factory.

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.

Master how aggregate, window, and general SQL functions transform data, using over, partition by, cast or convert, and explore numeric, date time, string, and null functions.

Master sql for analysis by handling duplicates, min max value filtering, and pivoting with conditional aggregations, then rolling calculations and imputing missing values with joins, unions, subqueries, and window functions.

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.

Use the row_number window function to identify duplicates by student name, keep the most recent by id, and output a clean id, name, and email report.

Explore min/max value filtering by returning each sales rep's most recent date and amount, using group by with join or a window function.

Apply min/max value filtering to generate a report of each student with their highest semester grade and the corresponding class, pulling data from students table and the student grades table.

Identify each student's top grade and its class by joining the students and grades tables, then use group by or window functions to rank grades and select rank one.

Pivot data in SQL to transform rows into columns and build a summary table, using case statements for one hot encoding of crust types, then group by category and sum.

Pivot data to present the average grade by department and grade level. Align rows to departments and columns to grade levels, with each cell showing the corresponding average grade.

Pivot student and student grades data to build a department-by-grade-level summary table, calculating average final grades and using case statements to create grade columns.

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.

Explore rolling calculations in sql by deriving subtotals with rollup, computing a cumulative sum with window functions, and deriving a three-year moving average of happiness scores per country.

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

Compute monthly total sales by joining orders and products, then add a cumulative sum and a six-month moving average using window functions and a cte.

Impute null values in a stock prices table using four methods: hard-coded, average via subquery, lag-based prior value, and smoothed using lead, showcasing four advanced SQL techniques.

Summarizes advanced sql querying techniques: duplicates handling with having and distinct, min max filtering, pivoting with case or pivot, rolling totals, window sums and averages, and null imputation.

Analyze how player stats evolve over time and across teams using advanced SQL techniques. Load final project scripts and CSV data to answer 12 questions and review hints and solutions.

Explore advanced SQL techniques to analyze school data by decade, using round to tens, distinct counts, joins, and window functions to rank top three producers per decade.

Analyze the salaries table to compute each team's average annual spend, identify the top 20% using ntile, then track yearly cumulative spend to find the first year hitting 1 billion.

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.