title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.
Microsoft SQL Server T-SQL
Duration
3 Days
Course
MSSQ-200
Available Formats
Classroom Training, Online Training
Course Description
Overview
This Microsoft SQL Server T-SQL course is designed to provide students with knowledge of SQL. This course starts at a basic level and continues to an advanced level, providing students with many examples along the way. Students will gain a deeper knowledge and understanding of Microsoft’s SQL Server T-SQL and how to write it. In addition, students will gain practical experience with hands-on exercises.Objectives
Upon completion of the Microsoft SQL Server T-SQL course, students will be able to:
- Understand Basic SQL Functions
- Use the WHERE Clause
- Demonstrate Distinct Vs. Group By
- Utilize the Aggregation Function
- Apply Join, Date, Format, and OLAP Functions
- Understand Temporary Tables
- Use Sub-query Functions
- Apply Substrings and Positioning Functions
- Interrogate Data
- Use View Functions
- Set Operators Functions
- Create Tables
- Summarize Data Manipulation Language (DML)
Audience
- IT Professionals
Prerequisites
- None
Topics
- Introduction to the Family of SQL Server Products
- Introduction to the Family Continued
- Microsoft Azure SQL Data Warehouse
- Nexus is Now Available on the Microsoft Azure Cloud
- Symmetric Multi-Processing (SMP)
- Naming of Objects
- Introduction
- Setting Your Default Database
- SELECT * (All Columns) in a Table
- Fully Qualifying a Database, Schema and Table
- SELECT Specific Columns in a Table
- Commas in the Front or Back?
- Place your Commas in front for better Debugging Capabilities
- Sort the Data with the ORDER BY Keyword
- ORDER BY Defaults to Ascending
- Use the Name or the Number in your ORDER BY Statement
- Two Examples of ORDER BY using Different Techniques
- Changing the ORDER BY to Descending Order
- NULL Values sort First in Ascending Mode (Default)
- NULL Values sort Last in Descending Mode (DESC
- Major Sort vs. Minor Sorts
- Multiple Sort Keys using Names vs. Numbers
- Sorts are Alphabetical, NOT Logical
- Using A CASE Statement to Sort Logically
- An Order By That Uses an Expression
- How to ALIAS a Column Name
- Aliasing a Column Name with Spaces or Reserved Words
- A Missing Comma can by Mistake become an Alias
- Comments using Double Dashes are Single Line Comments
- Comments for Multi-Lines
- Comments for Multi-Lines as Double Dashes Per Line
- A Great Technique for Comments to Look for SQL Errors
- sp_help at the Database Level
- sp_help at the Object Level
- Getting System Information
- Getting Additional System Information
- The WHERE Clause limits Returning Rows
- Double Quoted Aliases are for Reserved Words and Spaces
- Using A Column ALIAS In A WHERE Clause
- Using A Column ALIAS In An ORDER BY Clause
- In What Order Does SQL Server Process A Query?
- Character Data needs Single Quotes in the WHERE Clause
- Character Data needs Single Quotes, but Numbers Don’t
- Declaring a Variable
- Comparisons against a Null Value
- NULL means UNKNOWN DATA so Equal (=) won’t Work
- Use IS NULL or IS NOT NULL when dealing with NULLs
- NULL is UNKNOWN DATA so NOT Equal won’t Work
- Use IS NULL or IS NOT NULL when dealing with NULLs
- Using Greater Than or Equal To (>=)
- AND in the WHERE Clause
- Troubleshooting AND
- OR in the WHERE Clause
- Troubleshooting Or
- Troubleshooting Character Data
- Using Different Columns in an AND Statement
- Quiz – How many rows will return?
- What is the Order of Precedence?
- Using Parentheses to change the Order of Precedence
- Using an IN List in place of OR
- The IN List is an Excellent Technique
- IN List vs. OR brings the same Results
- The IN List Can Use Character Data
- Using a NOT IN List
- Null Values in a NOT IN List Bring Back No Rows
- A Technique for Handling Nulls with a NOT IN List
- BETWEEN is Inclusive
- NOT BETWEEN is Also Inclusive
- LIKE command Underscore is Wildcard for one Character
- LIKE command using a Range of Values
- LIKE command Using a NOT Range of Values
- LIKE Command Works Differently on Char Vs Varchar
- Troubleshooting LIKE Command on Character Data
- Introducing the RTRIM Command
- Quiz – What Data is Left Justified and What is Right?
- Numbers are Right Justified and Character Data is Left
- An Example of Data with Left and Right Justification
- A Visual of CHARACTER Data vs. VARCHAR Data
- RTRIM command Removes Trailing spaces on CHAR Data
- Using Like with an AND Clause to Find Multiple Letters
- Using Like with an OR Clause to Find Either Letters
- Declaring a Variable and Using it with the LIKE Command
- Escape Character in the LIKE Command changes Wildcards
- Escape Characters Turn off Wildcards in the LIKE Command
- Quiz – Turn off that Wildcard
- The Distinct Command
- Distinct vs. GROUP BY
- Quiz – How many rows come back from the Distinct?
- TOP Command
- TOP Command is brilliant when ORDER BY is used!
- TOP Command with PERCENT
- TOP Command with PERCENT
- The TOP Command WITH TIES
- The TOP Command Using a Variable
- The TOP 1 Command for a Random Sample
- Quiz – You calculate the Answer Set in your own Mind
- The 3 Rules of Aggregation
- There are Five Aggregates
- Quiz – How many rows come back?
- Troubleshooting Aggregates
- GROUP BY when Aggregates and Normal Columns Mix
- GROUP BY delivers one row per Group
- Count_Big
- Limiting Rows and Improving Performance with WHERE
- WHERE Clause in Aggregation limits unneeded Calculations
- Keyword HAVING tests Aggregates after they are Totaled
- CHECKSUM_AGG to Check If a Table Has Changed
- Using Distinct Within the Aggregate
- Group By Grouping Sets
- Group By Rollup
- Answer Set for Group By Rollup Query
- Creating a Cube
- Answer Set for Cube Query
- An Easy Example of Creating a Cube
- Quiz - GROUP BY GROUPING SETS Challenge
- Getting the Average Values Per Column
- Average Values per Column for all Columns in a Table
- A Two-Table Join Using Traditional Syntax
- A two-table join using Non-ANSI Syntax with Table Alias
- You Can Fully Qualify All Columns
- A two-table join using ANSI Syntax
- Both Queries have the same Results and Performance
- Quiz – Can You Finish the Join Syntax?
- Quiz – Can You Find the Error?
- Super Quiz – Can You Find the Difficult Error?
- Quiz – Which rows from both tables won’t Return?
- LEFT OUTER JOIN
- LEFT OUTER JOIN Results
- RIGHT OUTER JOIN
- RIGHT OUTER JOIN Example and Results
- FULL OUTER JOIN
- FULL OUTER JOIN Results
- Which Tables are the Left and which Tables are Right?
- INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional WHERE Clause
- OUTER JOIN with Additional WHERE Clause
- OUTER JOIN with Additional AND Clause
- OUTER JOIN with Additional AND Clause Results
- Quiz – Why is this considered an INNER JOIN?
- Evaluation Order for Outer Queries
- The DREADED Product Join
- The DREADED Product Join Results
- The Horrifying Cartesian Product Join
- The ANSI Cartesian Join will ERROR
- Quiz – Do these Joins Return the Same Answer Set?
- The CROSS JOIN
- The CROSS JOIN Answer Set
- The Self Join
- The Self Join with ANSI Syntax
- Quiz – Will both queries bring back the same Answer Set?
- Quiz – Will both queries bring back the same Answer Set?
- How would you Join these two tables?
- An Associative Table is a Bridge that Joins Two Tables
- Quiz – Can you write the 3-Table Join?
- Quiz – Can you write the 3-Table Join to ANSI Syntax?
- Quiz – Can you Place the ON Clauses at the End?
- The 5-Table Join – Logical Insurance Model
- Quiz - Write a Five Table Join Using ANSI Syntax
- Quiz - Write a Five Table Join Using Non-ANSI Syntax
- Quiz –Re-Write this putting the ON clauses at the END
- Current_Timestamp
- Getdate
- Date and Time Keywords
- SYSDATETIMEOFFSET Provides the Timezone Offset
- Using Both CAST and CONVERT in Literal Values
- The DATEADD Function
- The DATEDIFF Function
- DATEADD Function
- A Real World Example for DateAdd Using the Order Table
- DATEPART Function
- DATEPART Function Examples
- YEAR, MONTH, and DAY Functions
- A Better Technique for YEAR, MONTH, and DAY Functions
- DATENAME Function
- Date Formatting
- Time Formatting
- ISDATE Function
- There are three types of Temporary Tables in TEMPDB
- Tables in TEMPDB are not your only Temporary Storage
- What is TEMPDB?
- Creating a Private Temporary Table
- You Populate a Private Temporary Table with an INSERT/SELECT
- The Three Steps to Use a Private Temporary Table
- Creating a Global Temporary Table
- You Populate a Global Temporary Table with an INSERT/SELECT
- The Three Steps to Use a Global Temporary Table
- Creating a Direct Temporary Table
- You Populate a Direct Temporary Table with an INSERT/SELECT
- The Three Steps to Use a Direct Temporary Table
- CREATING A Derived Table
- Naming the Derived Table
- Aliasing the Column Names in the Derived Table
- Multiple Ways to Alias the Columns in a Derived Table
- CREATING a Derived Table using the WITH Command
- The Same Derived Query shown Three Different Ways
- MULTIPLE Derived Tables using the WITH Command
- Column Alias Can Default For Normal Columns
- Most Derived Tables Are Used To Join To Other Tables
- A Join Example Showing Different Column Alias Styles
- The Three Components of a Derived Table
- Visualize This Derived Table
- Our Join Example With The WITH Syntax
- Quiz - Answer the Questions
- Clever Tricks on Aliasing Columns in a Derived Table
- A Derived Table lives only for the lifetime of a single query
- An Example of Two Derived Tables in a Single Query
- RECURSIVE Derived Table Hierarchy
- RECURSIVE Derived Table Query
- RECURSIVE Derived Table Definition
- WITH RECURSIVE Derived Table Seeding
- WITH RECURSIVE Derived Table Looping
- RECURSIVE Derived Table Looping in Slow Motion
- RECURSIVE Derived Table Looping Continued
- RECURSIVE Derived Table Ends the Looping
- RECURSIVE Derived Table Definition
- RECURSIVE Derived Table Answer Set
- Using a Table Variable
- An IN List is much like a Subquery
- An IN List Never has Duplicates – Just like a Subquery
- An IN List Ignores Duplicates
- The Subquery
- The Three Steps of How a Basic Subquery Works
- These are Equivalent Queries
- The Final Answer Set from the Subquery
- Quiz- Answer the Difficult Question
- Should you use a Subquery of a Join?
- Quiz- Write the Subquery
- Quiz- Write the More Difficult Subquery
- Quiz – Write the Extreme Subquery
- Quiz- Write the Subquery with an Aggregate
- Quiz- Write the Correlated Subquery
- The Basics of a Correlated Subquery
- The Top Query always runs first in a Correlated Subquery
- Correlated Subquery Example vs. a Join with a Derived Table
- Quiz- A Second Chance to Write a Correlated Subquery
- Quiz- A Third Chance to Write a Correlated Subquery
- Quiz- Last Chance To Write a Correlated Subquery
- Quiz – Write the Extreme Correlated Subquery
- Quiz- Write the NOT Subquery
- Quiz- Write the Subquery using a WHERE Clause
- Quiz – Write the Triple Subquery
- Quiz – How many rows return on a NOT IN with a NULL?
- How to handle a NOT IN with Potential NULL Values
- Using a Correlated Exists
- How a Correlated Exists matches up
- The Correlated NOT Exists
- The Correlated NOT Exists Answer Set
- Quiz – How many rows come back from this NOT Exists?
- The Row_Number Command
- Quiz – How did the Row_Number Reset?
- Using a Derived Table and Row_Number
- Ordered Analytics OVER
- RANK and DENSE RANK
- RANK Defaults to Ascending Order
- Getting RANK to Sort in DESC Order
- RANK() OVER and PARTITION BY
- CSUM – Rows Unbounded Preceding Explained
- CSUM – Making Sense of the Data
- CSUM – Making Even More Sense of the Data
- CSUM – The Major and Minor Sort Key(s)
- The ANSI CSUM – Getting a Sequential Number
- Troubleshooting The ANSI OLAP on a GROUP BY
- Reset with a PARTITION BY Statement
- PARTITION BY only Resets a Single OLAP not ALL of them
- CURRENT ROW AND UNBOUNDED FOLLOWING
- Different Windowing Options
- Moving Sum has a Moving Window
- How ANSI Moving SUM Handles the Sort
- Quiz – How is that Total Calculated?
- Moving SUM every 3-rows Vs a Continuous Average
- Partition By Resets an ANSI OLAP
- The Moving Window is Current Row and Preceding
- How Moving Average Handles the Sort
- Moving Average
- Quiz – How is that Total Calculated?
- Quiz – How is that 4th Row Calculated?
- Moving Average every 3-rows Vs a Continuous Average
- Partition By Resets an ANSI OLAP
- Moving Difference using ANSI Syntax
- Moving Difference using ANSI Syntax with Partition By
- COUNT OVER for a Sequential Number
- COUNT OVER without Rows Unbounded Preceding
- Quiz – What caused the COUNT OVER to Reset?
- The MAX OVER Command
- MAX OVER with PARTITION BY Reset
- MAX OVER Without Rows Unbounded Preceding
- The MIN OVER Command
- Troubleshooting MIN OVER
- Finding a Value of a Column in the Next Row with MIN
- The CSUM For Each Product_Id and the Next Start Date
- Quiz – Fill in the Blank
- How Ntile Works
- Ntile
- Ntile Continued
- Ntile Percentile
- Another Ntile Example
- Using Tertiles (Partitions of Four)
- NTILE
- NTILE Using a Value of 10
- NTILE With a Partition
- Using FIRST_VALUE
- FIRST_VALUE
- FIRST_VALUE After Sorting by the Highest Value
- FIRST_VALUE with Partitioning
- Using LAST_VALUE
- LAST_VALUE
- Using LAG and LEAD
- Using LEAD
- Using LEAD With and Offset of 2
- LEAD
- LEAD With Partitioning
- Using LAG
- Using LAG With an Offset of 2
- LAG
- LAG with Partitioning
- CUME_DIST
- CUME_DIST With a Partition
- SUM(SUM(n))
- The ASCII Function
- The CHAR Function
- The UNICODE Function
- The NCHAR Function
- The LEN Function
- The DATALENGTH Function
- Concatenation
- The RTRIM and LTRIM Command trims Spaces
- The SUBSTRING Command
- Using SUBSTRING to move Backwards
- How SUBSTRING Works with a Starting Position of -1
- How SUBSTRING Works with an Ending Position of 0
- Concatenation and SUBSTRING
- SUBSTRING and Different Aliasing
- The LEFT and RIGHT Functions
- Four Concatenations Together
- The DATALENGTH Function and RTRIM
- A Visual of the TRIM Command Using Concatenation
- CHARINDEX Function Finds a Letter(s) Position in a String
- The CHARINDEX Command is brilliant with SUBSTRING
- The CHARINDEX Command Using a Literal
- PATINDEX Function
- PATINDEX Function to Find a Character Pattern
- SOUNDEX Function to Find a Sound
- DIFFERENCE Function to Quantile a Sound
- The REPLACE Function
- LEN and REPLACE Functions for Number of Occurrences
- REPLICATE Function
- STUFF Function
- STUFF without Deleting Function
- UPPER and lower Functions
- Quiz – What would the Answer be?
- The NULLIF Command
- Quiz – Fill in the Answers for the NULLIF Command
- The COALESCE Command – Fill In the Answers
- COALESCE is Equivalent to This CASE Statement
- The Basics of CAST (Convert and Store)
- Some Great CAST (Convert and Store) Examples
- A Rounding Example
- Quiz - CAST Examples
- Quiz - The Basics of the CASE Statements
- Using an ELSE in the Case Statement
- Using an ELSE as a Safety Net
- Rules For a Valued Case Statement
- Rules for a Searched Case Statement
- Valued Case Vs. A Searched Case
- Quiz - Valued Case Statement
- Quiz - Searched Case Statement
- Quiz - When NO ELSE is present in CASE Statement
- Quiz -When an Alias is NOT used in a CASE Statement
- Combining Searched Case and Valued Case
- A Trick for getting a Horizontal Case
- Nested Case
- Put a CASE in the ORDER BY
- Creating a Table that is a Heap
- Heap Page
- Extents
- Creating a Table That Has a Clustered Index
- Clustered Index Page
- When Do I Create a Clustered Index?
- B-Trees
- The Building of a B-Tree For a Clustered Index
- The Row Offset Array is the Guidance System For Every Row
- The Row Offset Array Provides Two Search Options
- The Row Offset Array Helps With Inserts
- Adding an Index
- When Do I Create a Non Clustered Index?
- B-Tree For Non Clustered Index on a Clustered Table
- Adding A Non Clustered Index To A Heap
- B-Tree For Non Clustered Index on a Heap Table
- SELECT INTO
- A Primary Key Constraint
- The Difference between a Primary Key vs. Unique Constraint
- Primary Key Foreign Key Constraints
- More Information about Foreign Key Constraints
- Check Constraint
- Default Values
- Identity Columns
- Computed Columns
- Compression
- ROWVERSION
- The Fundamentals of Views
- Creating a Simple View to Restrict Sensitive Columns
- Creating a Simple View to Restrict Rows
- Basic Rules for Views
- How to Modify a View
- Why Bother To ALTER A View
- Two Exceptions to the ORDER BY Rule inside a View
- How to Get HELP with a View
- Views sometimes CREATED for Formatting or Row Security
- Creating a View to Join Tables Together
- You Select From a View
- Another Way to Alias Columns in a View CREATE
- The Standard Way Most Aliasing is done
- What Happens When Both Aliasing Options Are Present
- Resolving Aliasing Problems in a View CREATE
- Aggregates on View Aggregates
- Altering A Table
- Altering a Table after a View has been Created
- A View that Errors After An ALTER
- Troubleshooting a View
- Updating Data in a Table through a View
- Loading Data through a View
- Maintenance Restrictions on a Table through a View
- INSERT Syntax # 1
- INSERT Example with Syntax 1
- INSERT Syntax #2
- INSERT Example with Syntax 2
- INSERT Example with Syntax 3
- INSERT/SELECT Command
- INSERT/SELECT Example using All Columns (*)
- INSERT/SELECT Example with Less Columns
- The UPDATE Command Basic Syntax
- Two UPDATE Examples
- Subquery UPDATE Command Syntax
- Example of Subquery UPDATE Command
- Join UPDATE Command Syntax
- Example of an UPDATE Join Command
- Fast UPDATE
- The DELETE Command Basic Syntax
- Two DELETE Examples to DELETE ALL Rows in a Table
- To DELETE or to TRUNCATE
- TRUNCATE is Different from DELETE
- A DELETE Example Deleting only Some of the Rows
- Want to know How Many Rows were just changed
- Subquery and Join DELETE Command Syntax
- Example of Subquery DELETE Command
- Example of Join DELETE Command
- MERGE INTO
- MERGE INTO Example that Matches
- MERGE INTO Example that does NOT Match
- User Defined Functions (UDFs)
- User Defined Function Example
- Replace
- User Defined Types (UDTs)
- Rules of Set Operators
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- EXCEPT Explained Logically
- Another EXCEPT Example
- EXCEPT Explained Logically in Reverse Order
- An Equal Amount of Columns in both SELECT List
- Columns in the SELECT list should be from the same Domain
- The Top Query handles all Aliases
- The Bottom Query does the ORDER BY
- Great Trick: Place your Set Operator in a Derived Table
- UNION Vs UNION ALL
- Using UNION ALL and Literals
- A Great Example of how EXCEPT works
- USING Multiple SET Operators in a Single Request
- Changing the Order of Precedence with Parentheses
- Building Grouping Sets Using UNION
- Three Grouping Sets Using a UNION
- Creating a Stored Procedure
- Executing a Stored Procedure
- There are Three Ways to Execute a Stored Procedure
- Dropping a Stored Procedure
- Passing an Input Parameter to a Stored Procedure
- Executing With Positional Parameters vs. Named Parameters
- Passing an Output Parameter to a Stored Procedure
- Changing a Stored Procedure with an ALTER
- Answer Set for the Altered Stored Procedure
- Displaying a Stored Procedure’s Definition
- Encrypting a Stored Procedure’s Definition
- Using a Stored Procedure to Delete a Row
- A Different Method to Delete a Row
- Deleting a Row Using an Input Parameter
- Using Loops in Stored Procedures
- Stored Procedure Workshop
- Looping with a WHILE Statement
- The Stats Table
- Above is the Stats_Table data in which we will use in our statistical examples
- The VAR and VARP Functions
- A VAR Example
- A VARP Example
- The STDEV and STDEVP Functions
- A STDEV Example
- A STDEVP Example
- Nexus is Now Available on the Microsoft Azure Cloud
- Nexus Queries Every Major System
- Setup of Nexus is as easy as pie
- Setup of Nexus is a Easy as 1, 2, 3
- Nexus Data Visualization
- Nexus Data Visualization Shows What Tables Can Be Joined
- Nexus is doing a Five-Table Join
- Nexus Generates the SQL Automatically
- Nexus Delivers the Report
- Cross-System Joins from Teradata, Oracle and SQL Server
- The Tab of the Super Join Builder
- The 9 Tabs of the Super Join Builder – Objects Tab 1
- Selecting Columns in the Objects Tab
- The 9 Tabs of the Super Join Builder – Columns Tab 2
- Removing Columns from the Report in the Columns Tab
- The 9 Tabs of the Super Join Builder – Sorting Tab 3
- The 9 Tabs of the Super Join Builder – Joins Tab 4
- The 9 Tabs of the Super Join Builder – Where Tab 5
- Using the WHERE Tab For Additional WHERE or AND
- The 9 Tabs of the Super Join Builder – SQL Tab 6
- The 9 Tabs of the Super Join Builder – Answer Set Tab 7
- The 9 Tabs of the Super Join Builder – Analytics Tab 9
- Analytics Tab
- Analytics Tab – OLAP Example
- Analytics Tab – OLAP Example of SQL Generated
- Analytics Tab – Grouping Sets Example
- Analytics Tab – Grouping Sets Answer Set
- Nexus Data Movement
- Moving a Single Table To a Different System
- The Single Table Data Movement Screen
- Moving an Entire Database To a Different System
- The Database Mover Screen
- The Database Mover Options Tab
- Converting DDL Table Structures
- Hound Dog Compression
- Hound Dog Compression on Teradata
Related Courses
-
Querying Data with Microsoft Transact-SQL
MOC-DP-080T00- Duration: 2 Days
- Delivery Format: Classroom Training, Online Training
- Price: 1,190.00 USD
-
Getting Started with Cosmos DB NoSQL Development
MOC-DP-3015- Duration: 1
- Delivery Format: Classroom Training, Online Training
- Price: 595.00 USD
Self-Paced Training Info
Learn at your own pace with anytime, anywhere training
- Same in-demand topics as instructor-led public and private classes.
- Standalone learning or supplemental reinforcement.
- e-Learning content varies by course and technology.
- View the Self-Paced version of this outline and what is included in the SPVC course.
- Learn more about e-Learning
Course Added To Shopping Cart
bla
bla
bla
bla
bla
bla
Self-Paced Training Terms & Conditions
??spvc-wbt-warning??
Exam Terms & Conditions
??exam-warning??
??group-training-form-area??
??how-can-we-help-you-area??
??personalized-form-area??
??request-quote-area??
Sorry, there are no classes that meet your criteria.
Please contact us to schedule a class.
STOP! Before You Leave
Save 0% on this course!
Take advantage of our online-only offer & save 0% on any course !
Promo Code skip0 will be applied to your registration
Nothing yet
Purchase Information
??elearning-coursenumber?? ??coursename??
View Cart
title
Please take a moment to fill out this form. We will get back to you as soon as possible.
All fields marked with an asterisk (*) are mandatory.