SQL Mastery for Data Analytics and Business Intelligence

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.

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

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.

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.

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.

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.

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

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.

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.

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.

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

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.

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.