Contact Us info@learnquest.com

??WelcomeName??
??WelcomeName??
photo

Thank you for your interest in LearnQuest.

Your request is being processed and LearnQuest or a LearnQuest-Authorized Training Provider will be in touch with you shortly.

photo

Thank you for your interest in Private Training.

We look forward to helping you develop the perfect training solution to help you meet your company's goals.

For immediate assistance, speak with one of our representatives using the chat module below. Otherwise, LearnQuest or a LearnQuest-Authorized Training Provider will be in touch with you shortly.

photo

Thank you for your interest in LearnQuest!

Now, you will be able to stay up-to-date on our latest course offerings, promotions, and training discounts. Watch your inbox for upcoming special offers.

title

Date: xxx

Location: xxx

Time: xxx

Price: xxx

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

Course content updated by LearnQuest
Price
2,295 USD
3 Days
MSSQ-215
Classroom Training, Online Training
Microsoft
Prices reflect a 22.5% discount for IBM employees.
Prices shown are the special AWS Partner Prices.
Prices reflect the Capgemini employee discount.
Prices reflect the UPS employee discount.
Prices reflect the ??democompanyname?? employee discount.
GSA Private/Onsite Price: ??gsa-private-price??
For GSA pricing, please go to GSA Advantage.
This course is eligible for the IBM Full Access Training Pass
Enroll today and save 10% on this course. Use promo code CLOUD10 when registering.
Working on a laptop
Gain access to IBM’s library of digital, on-demand courses for one low annual subscription fee
IBM Full Access Training Pass
$500 off any IBM Full Access Training Pass Option
See Offer
Get a 30% Discount on IBM Self-Paced Courses
See Offer

Class Schedule

Delivery Formats

Sort results

Filter Classes

Guaranteed to Run

Modality

Location

Language

Date

    Sorry, there are no public classes currently scheduled in your country.

    Please complete this form, and a Training Advisor will be in touch with you shortly to address your training needs.

View Global Schedule

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
    • 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
  • The Basics of SQL
    • 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
    • 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
  • Distinct, Group By and TOP
    • 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
  • Join Functions
    • 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
  • Date Functions
    • 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
  • Temporary Tables
    • 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
  • Sub-query Functions
    • 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
  • Window Functions OLAP
    • 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))
  • Working with Strings
    • 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
  • Interrogating the Data
    • 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
  • Table Create and Data Types
    • 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
  • View Functions
    • 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
  • Data Manipulation Language (DML)
    • 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)
  • Set Operators Functions
    • 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
  • Stored Procedures
    • 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
  • Statistical Aggregate Functions
    • The Stats Table
    • VAR
    • VARP
    • The STDEV and STDEVP Functions
  • Nexus
    • 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
  • 2020 Top 20 Training Industry Company - IT Training

    Need Help?

    Call us toll free at 877-206-0106 or e-mail us at info@learnquest.com

    Personalized Solutions

    Need a personalized solution for your training? Contact us, and one of our advisors will help you find the best solution to your training needs.

    Contact us

    Need Help?

    Do you have a question about the courses, instruction, or materials covered? Do you need help finding which course is best for you?

    Talk to us

    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.
    Nothing yet
    here's the message from the cart

    To view the cart, you can click "View Cart" on the right side of the heading on each page
    Add to cart clicker.

    Purchase Information

    ??elearning-coursenumber?? ??coursename??
    View Cart

    Need more Information?

    Speak with our training specialists to continue your learning journey.

     

    Delivery Formats

    Close

    By submitting this form, I agree to LearnQuest's Terms and Conditions

    heres the new schedule
    This website uses third-party profiling cookies to provide services in line with the preferences you reveal while browsing the Website. By continuing to browse this Website, you consent to the use of these cookies. If you wish to object such processing, please read the instructions described in our Privacy Policy.
    Your use of this LearnQuest site affirms your consent to our use of session and persistent cookies to track how you use our website.