Master Microsoft SQL for Data Analysis: Beginner to Advanced

Create a database and five tables: accounts, orders, sales reps, region, and web events—and learn create table syntax, data types such as char and varchar, and primary and foreign keys.

Create tables in a database, define region and sales rep tables with primary and foreign keys, and import data via csv or insert statements.

Import data into SQL Server via the CSV method with the import data wizard and flat file source, map data types, and preview the region, sales rep, and accounts tables.

Learn insert into and values syntax to add single or multiple records to orders and web event tables, then use a database diagram to explore primary and foreign key relationships.

Learn to write the select statement to retrieve data from a table, choosing specific columns or all columns with a from clause, plus examples of semicolon usage.

Learn how the top clause retrieves a subset of rows to quickly preview data, using select top n from table syntax.

Apply the where clause to filter data after a from statement, using conditions like account ID to retrieve only matching rows and respect clause order.

Explore how the where clause combines with comparison operators to filter data precisely in sql, using greater than, less than, and equal to conditions to answer business questions.

Apply the where clause with logical operators like like, in, not, and between to filter data, illustrated by referrer URLs, account IDs, and date ranges.

Practice using the where clause with logical operator 2 to query the accounts table for Exxon Mobil, returning the company name, website, and primary point of contact.

Learn to build complex where clauses using in, and, or, not with bracketed groupings to filter accounts and orders. Practice with real examples on names, channels, and dates.

Use the order by clause to sort results, optionally including a where clause, and specify ascending or descending on one or more columns, such as the most recent orders.

Learn how arithmetic in SQL derives new columns from existing data using derived or calculated columns, with aliases and operators like multiplication, addition, subtraction, and division, following order of operations.

Review the basic SQL commands, including select, from, top, order by, where, like, in, not, and, all, and between, as you prepare to explore joins and deeper data insights.

Explore how SQL joins connect multiple tables using common identifiers to merge data, organize information, and solve complex data analysis problems, highlighting relational databases and the power of joins.

Explore why the orders table stores only account IDs, not customer names, and how normalization separates historical orders from account data to boost efficiency and fast data access.

Explore the join clause and its syntax to combine data from multiple tables using inner, left, right, and full outer joins on a common column such as orders.account_id equals accounts.id.

Learn how inner joins combine web events and accounts to show Walmart events with primary contact and channel, then join regions, sales reps, and accounts to compute unit price.

Explore left join and right join concepts in SQL by comparing how orders and accounts tables merge, filtering on gloss and poster quantities to reveal matched results.

Learn how sql aggregate functions summarize data by column, including count, sum, avg, min, and max. Explore grouping results with group by and filtering with having.

Explore SQL nulls by understanding null as the absence of data, distinguishing it from zeros or empty strings, and using is null and is not null in joins and aggregations.

Master counting rows in sql with count(*), count(column), and meaningful aliases like total orders. Explore counting all rows versus non-null values in a column to support dashboards and reporting.

Explore min and max SQL aggregation functions, retrieving smallest and largest values per category, ignoring nulls, with examples on orders by paper type; apply to numbers, dates, and text.

Learn to compute the total quantity sold with the sum function on the orders table for inventory planning. Sum adds numeric values and ignores nulls, unlike count.

Master the full outer join and its syntax, including null handling for unmatched rows in accounts and sales reps. Apply it to Midwest region, channels, and top orders in 2016.

Explore how the SQL avg function computes mean order quantities, comparing standard, gloss, and poster cuts while noting that it requires numerical columns, ignores nulls, and is affected by outliers.

Explore the group by clause to obtain granular insights from aggregation, using sum, average, and counts by account, and apply joins and correct order by after where clause.

Explore the having clause with group by to filter aggregated results, using sum and total amount USD to identify top accounts and sales reps.

learn how to group data by date in SQL Server, extracting year, month, or day with the date name function and the format function to produce meaningful aggregates.

Explore the sql case statement as a conditional expression to create derived columns, group data, and avoid division by zero with practical examples of when-then logic.

Explore the SQL iif function, its equivalence to case when, and how to nest iif to create categorical data and replace case when expressions.

Learn to use subqueries and table expressions, including common table expressions and temp tables, for derived results, cleaner logic, and faster analysis of questions like top regions and monthly sales.

Explore subqueries in SQL, where a query runs first and feeds results. Use them in select, from, and where, alias the subquery, and compute average events per day by channel.

Explore subqueries in other clauses by treating them as tables, scalars, or single cells, and apply them in where, join, or case statements with appropriate operators and aliases.

Learn how common table expressions create temporary virtual tables using the with clause, enabling readable, modular queries that replace subqueries and improve analysis workflows.

Learn to use temp tables to speed up slow subqueries by creating a session-scoped temporary table (drop table if exists, into), then query from it for dashboards and reporting.

Create a sql view from a multi-table query to power dashboards by joining accounts, sales reps, regions, and orders; learn aliases, top totals, and querying the view.

Build a view by combining account and WebEvents data, use a left join, select and alias columns, and sort by occurred at to analyze user activity.

Master data cleaning and reshaping, addressing inconsistent formats, null values, wrong data types, and messy text fields, using SQL functions like left, right, length, substring, and cast to derive insights.

Use left, right, and length functions to extract substrings and count characters, with practical examples like pulling the first letter of company names and group by that letter.

Learn to manipulate text using character index and substring, nested in left and right, to dynamically split a full name into first and last names by the space delimiter.

Learn how to use sql concat to merge first name, last name, and company domain into a work email, using a common table expression and replace to remove spaces.

Explore the cast function in SQL to convert data types, with logical examples turning strings into date types or integers and integers into boolean values, plus cautions about invalid formats.

Master window functions in SQL to compute running totals, compare rows, and classify trends without joins or subqueries, using row_number, rank, lag, and lead for daily sales analysis and insights.

Discover how window functions enable running totals across rows without grouping or joining, using over, order by, and partition by to restart monthly totals.

Learn how to use window functions row_number, rank, and dense_rank to number rows and rank items within groups, with partition by account_id and order by occurred at and month.

Master window functions in SQL by applying sum, count, average, min, and max with over to create running totals, counts, and averages without using groupby.

Explore SQL window functions lag and lead to compare current and previous rows, analyze changes over time, detect trends, and calculate gaps in order data.

Develop a problem-solving mindset to use sql as building blocks for answering business questions, from basic filtering and aggregation to window functions, ranking, and row comparison, leveraging resources.

Demonstrate using AI tools to draft and explain SQL queries for Microsoft SQL Server, comparing Gemini, deep sig, and copilot while guarding sensitive data.