Intermediate SQL Server
| Course Details | ||||||||
|---|---|---|---|---|---|---|---|---|
|
||||||||
| Languages | ||||||||
|
||||||||
| Outcomes | ||||||||
|
||||||||
| Price & Terms | ||||||||
|
||||||||
| Technical | ||||||||
|
||||||||
| Specification | ||||||||
|
||||||||
Use T-SQL in Microsoft SQL Server to clean missing data, work with dates and numbers, and build CTEs and window functions for analysis.
T-SQL, the dialect of SQL that Microsoft SQL Server runs, is the tool here for analyzing and cleaning data inside the database that holds it. You fill in or remove missing values, calculate with dates and numbers, and write more advanced queries that return summary statistics, so questions about a table can be answered quickly.
What You Will Achieve
- Clean a table by removing or imputing missing values and sort its records into labeled bins
- Measure the days between two dates and round, truncate or total numeric columns
- Hold values in variables, repeat a step with a loop, or stage a long query in a CTE
- Number rows, track running totals and find the most frequent value of a numeric column
Program Overview
Aggregations, Missing Data and Math Functions
Grouped aggregations and CASE bins give a first look at a table, followed by T-SQL math and date functions for counting, totals and rounding.
- Grouped aggregations and missing-value imputation
- Binning data with CASE statements
- Date functions for day counts and adding days
- Rounding, truncating, absolute values, squares and square roots
Processing Data with Variables, Loops and CTEs
Variables and WHILE loops process data step by step, while derived tables and common table expressions organize more complex queries.
- Creating variables and WHILE loops
- Queries built on derived tables
- Common table expression syntax
Window Functions for Summary Statistics
Partitions and window functions attach summary statistics to each row, including running totals and the mode of numeric columns.
- Window functions with aggregations over partitions
- First, previous and next values in a window
- Running totals and row numbers
- Standard deviation and mode
Certificate Awarded (Paid)
Job Outlook
These skills apply wherever data sits in Microsoft SQL Server and has to be cleaned, summarized and queried for analysis and reporting, directly against the tables that store it.
Good for: Analysts who work with Microsoft SQL Server data and want T-SQL methods for cleaning tables and calculating statistics within their queries.
Keep in mind: Everything is taught in T-SQL syntax, so some functions and query forms will differ in PostgreSQL, MySQL or other database systems.
Bottom line: Practical T-SQL work on cleaning, date math, CTEs and window statistics, all carried out inside SQL Server queries.
Frequently Asked Questions
How are missing values handled in the lessons?
Exercises practice two approaches with T-SQL functions: removing rows that have missing values and imputing replacements for them. CASE statements then sort the cleaned records into groups as a base for later analysis.
Are WHILE loops and variables part of the T-SQL taught?
Yes. One chapter has you create variables and write WHILE loops to process data, then build more complex queries with derived tables and common table expressions in SQL Server.
Which statistics do the window function lessons calculate?
The final chapter applies aggregations over partitions, pulls first, previous and next values, builds running totals, assigns row numbers, and calculates standard deviation and the mode of numeric columns.
User Reviews
Write your review
You must be logged in to post a review.
Reviews can only be posted by registered learners. Log in or create a free account, then you will return here to post.
There are no reviews yet.