SQL Mastery for Data Analytics and Business Intelligence

Explore how to query data with SQL, using the SELECT and FROM clauses to retrieve data from tables, while understanding that queries read but do not modify the database.

Explore the core elements of SQL, including select, from, and where clauses, keywords, built-in functions, identifiers, operators, and static values in practical queries.

Learn how SQL executes queries using select and from, retrieve all columns with select star, and specify tables, columns, and simple comments in practical examples.

Learn how to filter data using the where clause by applying conditions to rows, with examples like score > 500 and country = 'Germany', and selecting specific columns.

Learn how to use the order by clause to sort data, choose ascending or descending, and apply nested sorting by multiple columns like country and score.

Group data by country to aggregate scores using sum and aliases, then observe how non-aggregated columns must appear in the group by and how counting ID affects results.

Explore how to use having after group by to filter aggregated results, compare where versus having, and apply conditions on sums, averages, and country-level scores.

Learn how to use distinct in sql to remove duplicates and return unique values, such as a country list, by applying select and from with proper syntax.

Learn how to use top (limit) in SQL to cap rows and pair it with order by for top or bottom n records, such as most recent orders.

Compare the coding order of SQL queries—select, distinct, top, from, where, group by, having, order by—with the database execution sequence.

Master executing multiple SQL queries in one window with semicolon separators and separate result grids, and learn to include static values in selects while highlighting queries for partial execution.

Learn how data filtering starts with cleaning and preparing messy data using SQL string, number, and date functions, handling nulls, and building standards with case statements and comparison operators.

Explore SQL operators, including the comparison, logical, range (between), membership (in / not in), and search (like) operators, and learn to build conditions using columns, values, functions, and subqueries.

Unlock how the or operator keeps rows when at least one condition is true, shown with customers from USA or a score greater than 500, and contrast it with SQL.

Learn the not operator as a reverse condition in SQL, excluding matching values, using a single condition like country = USA or score not less than 500.

Learn to apply the between operator in SQL to filter scores within an inclusive range, using lower and upper boundaries or via equivalent comparisons with the and operator.

Explore the in and not in operators in SQL, using a value list to filter customers by country, compare with or, and improve readability and performance.

Learn how to use the like operator to search text patterns in SQL, using % and _ wildcards, with examples starting with M, ending with N, or containing R.

Learn why data transformation and cleaning are essential before analysis, then use SQL string, number, and date functions and case statements to prepare data for reliable analytics.

Master SQL functions to transform data through single row, multi row operations. Learn nesting techniques and apply string, numeric, date, null handling, and window analytic functions for engineering and analytics.

Master string manipulation in SQL by learning concat to combine multiple values into one. See how to join first name and country into a single column with a space separator.

Master SQL string case handling with the upper and lower functions, transforming a customer's first name to upper or lower case, and previewing the trim function.

Master the trim function to remove leading and trailing spaces and normalize data. Learn to detect hidden spaces in names using where clauses and length checks for effective data cleansing.

Apply the sql replace function to swap old values with new ones, such as dashes to slashes or .txt to .csv in data and filenames.

Learn how the length function (LEN) counts characters in strings, numbers, and dates, apply it to the first name, and explore extracting substrings with left and right.

Learn how to use the left and right functions in SQL to extract the first or last characters from a string, with trim to remove spaces.

Extract substrings in SQL by specifying a starting position and length. Combine length, trim, and substring to create dynamic, robust text extractions for names and other fields.

Explore sql number functions by applying round to multiple decimal places and using abs (eps) to convert negatives to positives, with practical examples on 3.516 and -10.

Learn to extract date parts, format and cast dates, perform date calculations, and validate dates in SQL, with a four-category approach covering 13 date and time functions.

Extract date parts in SQL with the day, month, and year functions to obtain the corresponding day, month, and year from a date, using straightforward syntax.

Learn how the SQL date name function returns human-readable date parts, such as the full month name or weekday, as strings, unlike date part which yields numbers.

Explore how DateRank truncates date/time to specific parts, redefining granularity from seconds to years, and enabling fast aggregation by month or year with group by.

Master end of month function that returns last day of a month by adjusting day, and cast to date while deriving start of the month with date trunk.

Learn how to extract date parts to power data aggregations and reports, grouping by year, quarter, or month and filtering by month for faster queries.

Explore extracting date parts in SQL with date part, date name, and date trunk, and understand outputs like integers, strings, and dates. Apply the decision process to choose the function.

Discover how SQL formats dates with Y, MM, DD and the case-sensitive M vs m, then use format, cast, and convert to view data in different styles.

Master the SQL format function to customize date, time, and number displays. Use two required parameters, an optional culture, and create USA or Europe formats for reports and aggregations.

Master the SQL convert function to cast data types and format values, using syntax like convert(data_type, value, style) and practical examples from string, date, and date time conversions.

Master date add to manipulate dates by adding or subtracting years, months, and days, using a three-part syntax: part, interval, and date.

Showcases how DateDiff computes differences between two dates in years, months, or days with examples using order and shipping dates. Demonstrates calculating age from birth dates against the current date.

Master date and time handling in SQL using 13 functions to extract date parts, format dates, compute date differences, perform date arithmetic, and validate dates for analytics, reporting, and filtering.

Discover what nulls mean in SQL and how to replace them with a value using coalesce, null if, and to check with is null or is not null.

Analyze how null values impact sql aggregations and learn to handle them before calculations using coalesce or is null, ensuring accurate averages, sums, counts, min, max, and windowed insights.

Learn to handle nulls before arithmetic with coalesce and is null, then concatenate first and last names into a full name and add a 10 point bonus to scores.

Learn how to handle nulls in join keys with coalesce or is null to prevent missing records during inner joins, ensuring accurate cross-table analytics.

Master the SQL NULLIF function: compare two values or columns, return null when equal, else return the first value; use it to flag data issues and prevent divide by zero.

Master isNull and isNotNull to filter nulls and non-nulls, use them in where clauses to find missing data, and create left anti-joins by combining left joins with isNull.

Explore the differences between null, empty string, and blank spaces in SQL, including representations, data types, storage, and querying with is null versus the equality operator and length checks.

Learn to build conditional logic in SQL with case statements, using when, then, and end, evaluating conditions top to bottom and applying an optional else default.

Use case statements to transform data, create a category column, and categorize sales into high, medium, and low, then aggregate total sales by category.

Master mapping values with SQL case statements, transforming codes to readable labels like active/inactive and female/male, and comparing full versus quick form syntax for country abbreviations.

Learn to handle nulls in SQL with case statements, replacing nulls with zero, and compute conditional aggregations like counting orders with sales above 30 for accurate analytics.

Leverage case statements to evaluate conditions and return the first match, enabling data transformations, categorization, value mapping, null handling, and conditional aggregations for analytics.

Explore how to combine data from multiple tables using sql joins and set operators to answer business questions and prepare datasets for analysis.

Explore how to combine data from two tables using SQL joins and set operators. Learn inner, left, right, and full joins, plus union and intersect.

learn to query two tables without joining by running separate select statements for customers and orders, returning two independent results rather than a combined dataset.

Master inner join techniques to combine data from two tables by matching keys, returning only overlapping rows, with tips on join conditions, table order, and table aliases.

Understand how left join returns all rows from the left table and only matching rows from the right table, preserving left data while showing nulls for non-matches.

Learn how a right join returns all rows from the right table and matching data from the left. Compare it with left joins.

Implement the left anti join by performing a left join and filtering where the right key is null to show left rows with no matches, such as customers without orders.

Master the right anti join: return rows from the right table with no match in the left, using a right join and a null check, shown with orders without customers.

Discover how cross join, a cartesian join, creates every possible combination by multiplying rows from left and right tables with no on condition, yielding five customers and four orders.

Explore how to combine rows from two tables using set operators, including union, union all, accept, and intersects, and build proficiency in row-wise data combination.

Learn how union combines data from two queries by returning all distinct rows and removing duplicates, as shown when merging employee and customer first and last names.

Use EXCEPT, also called minus, to return distinct rows from the first query not found in the second, emphasizing the order of queries and set-based results in SQL Server.

Use union to combine similar tables into one dataset before analysis. Add a source column to identify origins and avoid duplicates with union.

Master set operators in sql using union, union all, except, and intersect to combine results. Ensure same column count, data types, and order; the first query controls aliases.

Learn how aggregate functions in SQL reveal insights by counting rows, summing sales, averaging values, and identifying the highest and lowest figures, with group by for drill-down.

Explore how sql window functions preserve row-level detail while performing aggregations, unlike group by. Learn when to use window functions for advanced analytics with over and partition by.

Explore the syntax of SQL window functions, including the window function and the over clause with partition by, order by, and frame, plus aggregate, ranking, and value or analytics functions.

Use window functions with the over clause and partition by to create windows. Sum data across all orders or by month, product, and order status.

Explore window definitions and the role of order by in window functions. Rank data with the rank function by month using partition by month and order by sales descending.

Master the window frame in SQL window functions. Define frame boundaries using rows and range, including current row, preceding, following, and unbounded options to scope calculations within partitions.

Explore the rules of window functions in SQL, including where they can be used (select and order by), nesting limits, interaction with filtering and group by, and practical ranking examples.

Explore SQL window functions and the over clause, including partition by, order by, and frame, compare them with group by, and learn how to apply window functions in select.