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.
SQL Server T-SQL
Duration
3 Days
Course
MSSQ-215
Available Formats
Classroom Training, Online Training
Course Description
Overview
This SQL Server T-SQL course is designed to provide students with a deeper knowledge and understanding of the SQL Server T-SQL and how to write it. Students will learn the SQL Server T-SQL starting at the most basic level and going to the most advanced level with many examples.Objectives
Upon completion of the SQL Server T-SQL course, students will be able to:
- Identify and apply basic SQL functions
- Understand the WHERE clause
- Contrast Distinct vs. Group by
- Recognize and apply functions, such as:
- Aggregation, Join, Date, OLAP, View, Sub-query and Statistical Aggregate
- Recognize and produce Temporary Tables
- Define and distinguish Strings
- Interrogate the Data
- Set Operators
- Demonstrate knowledge of the Data Manipulation Language (DML)
Audience
- Anyone who has a desire to learn SQL Server T-SQL from beginners to an advanced audience
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
- Place your Commas in front for better Debugging Capabilities
- Sort the Data with the ORDER BY Keyword
- Changing the ORDER BY to Descending Order
- NULL Values sort First in Ascending Mode (Default)
- Major Sort vs. Minor Sorts
- Multiple Sort Keys using Names vs. Numbers
- Using A CASE Statement to Sort Logically
- How to ALIAS a Column Name
- Aliasing a Column Name with Spaces or Reserved Words
- Comments using Double Dashes are Single Line Comments
- Comments for Multi-Lines
- 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 and ORDER BY Clause
- 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
- Using Greater Than or Equal To (>=)
- AND in the WHERE Clause
- OR in the WHERE Clause
- Troubleshooting Character Data
- Using Different Columns in an AND Statement
- Using Parentheses to change the Order of Precedence
- Using an IN List in place of OR
- IN List vs. OR brings the same Results
- Using a NOT IN List
- Null Values in a NOT IN List Bring Back No Rows
- BETWEEN and NOT BETWEEN are Inclusive
- LIKE command
- Introducing the RTRIM Command
- Numbers are Right Justified and Character Data is Left
- 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
- The Distinct Command
- Distinct vs. GROUP BY
- TOP Command
- Troubleshooting Aggregates
- GROUP BY when Aggregates and Normal Columns Mix
- 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 and Rollup
- Creating a Cube
- 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
- A two-table join using ANSI Syntax
- Both Queries have the same Results and Performance
- LEFT OUTER JOIN
- RIGHT OUTER JOIN
- FULL OUTER JOIN
- INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional AND and WHERE Clause
- OUTER JOIN with Additional WHERE and AND Clause
- Evaluation Order for Outer Queries
- The DREADED Product Join
- The Horrifying Cartesian product Join
- The ANSI Cartesian Join will ERROR
- The CROSS JOIN
- The Self Join
- How would you join these two tables?
- An Associative Table is a Bridge that Joins Two Tables
- The 5-Table Join – Logical Insurance Model
- Current_Timestamp
- Getdate
- Date and Time Keywords
- SYSDATETIMEOFFSET Provides the Timezone Offset
- Using both CAST and CONVERT in Literal Values
- The DATEADD and DATEDIFF Function
- A Real World Example for DateAdd Using the Order Table
- DATEPART Function
- YEAR, MONTH, and DAY Functions
- DATENAME Function
- Date and Time Formatting
- ISDATE Function
- There are three types of Temporary Tables in TEMPDB
- Tables in TEMPDB are not your only Temporary Storage
- 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
- The Three Steps to Use a Global Temporary Table
- Creating a Direct Temporary Table
- The Three Steps to Use a Direct Temporary Table
- CREATING A Derived Table
- Aliasing the Column Names in the 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
- 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, Query, and Definition
- WITH RECURSIVE Derived Table Seeding and Looping
- RECURSIVE Derived Table Looping
- RECURSIVE Derived Table Definition
- RECURSIVE Derived Table Answer Set
- Using a Table Variable
- An IN List Never has Duplicates – Just like a Subquery
- The Three Steps of How a Basic Subquery Works
- These are Equivalent Queries
- The Final Answer Set from the Subquery
- Should you use a Subquery of a Join?
- 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
- 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 Row_Number Command
- 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
- 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
- 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
- Moving Average
- Moving Average every 3-rows Vs a Continuous Average
- Partition by Resets an ANSI OLAP
- Moving Difference using ANSI Syntax
- COUNT OVER for a Sequential Number
- 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
- NTILE
- FIRST_VALUE
- LAST_VALUE
- LEAD
- LAG
- CUME_DIST
- 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
- Concatenation and SUBSTRING
- 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
- PATINDEX Function
- DIFFERENCE Function to Quantile a Sound
- The REPLACE Function
- LEN and REPLACE Functions for Number of Occurrences
- REPLICATE Function
- STUFF Function
- UPPER and lower Functions
- The NULLIF Command
- The COALESCE Command – Fill In the Answers
- COALESCE is Equivalent to This CASE Statement
- CAST (Convert and Store)
- A Rounding Example
- 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
- 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
- Clustered Index Page
- 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
- 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
- Another Way to Alias Columns in a View CREATE
- Resolving Aliasing Problems in a View CREATE
- Aggregates on View Aggregates
- Altering a Table
- 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 Syntax #2
- INSERT/SELECT Command
- The UPDATE Command Basic Syntax
- Subquery UPDATE Command Syntax
- Join UPDATE Command Syntax
- 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
- User Defined Functions (UDFs)
- Replace
- User Defined Types (UDTs)
- Rules of Set Operators
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- EXCEPT Explained Logically
- 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
- Displaying and 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
- VAR
- VARP
- The STDEV and STDEVP Functions
- Nexus is Now Available on the Microsoft Azure Cloud
- Nexus Queries Every Major System
- Setup of Nexus is as easy as pie
- 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
- Columns Tab 2- Removing Columns from the Report in the Columns Tab
- Sorting Tab 3
- Joins Tab 4
- Where Tab 5- Using the WHERE Tab For Additional WHERE or AND
- SQL Tab 6
- Answer Set Tab 7
- Analytics Tab 9
- Analytics Tab
- 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
Related Courses
-
Microsoft SQL Server T-SQL
MSSQ-200- Duration: 3 Days
- Delivery Format: Classroom Training, Online Training
- Price: 2,295.00 USD
-
Actian Matrix Architecture and SQL
ARAM-100- Duration: 3 Days
- Delivery Format: Classroom Training, Online Training
- Price: 2,295.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??
??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