The Complete SQL Bootcamp for Data Analysis – Level 2

Discover how to combine data from multiple tables in a relational database using SQL operators such as union and intersect, with a later focus on the join operator.

Learn how to combine SQL results using union, intersect, and except, ensuring same column count, order, and compatible data types across queries.

Master how join operators merge data from multiple queries or tables, including inner, outer, and cost join, and understand the cartesian join's potential to produce a large number of rows.

Learn how inner join links two tables with a condition to return records with matching values, using keys, primary keys, and aliases for clarity.

Understand outer joins in SQL, including left, right, and full outer joins, to preserve unmatched rows from one table while enriching with related data from another.

Explore how subqueries unlock insights by nesting queries in SQL, enabling you to combine data from multiple related tables and build more complex, powerful analyses.

Explore what a subquery is, including inner and outer queries, how inner results feed the outer query, and how subqueries enable multi-table data extraction in select statements.

Use single-row subqueries to filter data with conditional logic, employing aggregate functions (average, maximum) to compare product weights and prices across tables.

Learn how to filter data with multiple-row subqueries using in, any, and all operators, including inner joins, with examples on books, ratings, and users.

Learn to use subqueries as inline views in the from clause to create a temporary, alias-named data source that simplifies complex queries, with an example using average book ratings.

Transform data with a simple case statement in SQL by mapping input expressions using when-then-else, demonstrated with gender values M and F to male and female.

Learn how to use a searched case expression to classify rows with flexible conditions, creating age groups and weight categories, then summarize results with group by and counts.

Discover window functions in SQL, learning to apply aggregates to groups while keeping each row, such as listing users with their city’s average age.

Assign sequential numbers within city partitions using the window function row_number over (partition by city order by age desc), highlighting oldest users per city and optionally top five.

Use the rank window function to assign equal ranks to rows with the same value inside a partition, replacing sequential numbers and noting that ties cause skipped ranks.

Explore two simple SQL tools to simplify complex queries: create virtual tables with views and use common table expressions (CTEs) to improve readability and troubleshooting.

Learn how views create virtual, dynamic tables to simplify complex queries and restrict sensitive data, with practical examples using case statements, unions, and masking columns.

Recap level two covers creating database instances and schemas, loading csv data, and analyzing data across tables with union, joins, sub queries, case statement, window functions, and common table expressions.