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.

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.

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.

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

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.

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 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.

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.

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.

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.

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.

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.

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.

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.

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.

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 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.