Querying Data
with Transact-SQL

Gain the technical skills and best practices required to write basic Transact-SQL queries for Microsoft SQL Server (2008-2017.) The course is intended for Database Administrators, Database Developers, and Business Intelligence professionals.

No prior product knowledge needed.

Course length: 5 days, Delivery: Online or In-class.
Please call (475) 328-2227 with questions.

Course Overview

1.  SELECT

• Column and Table Aliased

• Predicate Logic and Order of Operations

• Result Set Manipulation
   

2.  Filtering Data

• Deduplication and DISTINCT

• TOP     

• CASE

• Offset-Fetch
   

3.  Sorting Data

• Ordering and Sorting

• Use of aliases and object names

• Best Practices

4.  JOINS and Multi-Table Querying

• INNER Join

• OUTER Join

• Cross and Self Joining

• Best Practices

5.  SQL Server Data Types

• Numeric Types

• Character Types

• System Data Types

• Date and Time

• Null Data and Best Practices

6.  Built-In Functions

• String Functions

• Date/Time Functions

• Programmatic

• Best Practices

7.  INSERT, UPDATE, DELETE

• Inserted, Deleted

• Data Manipulation Techniques

• Identity and Sequence

8.  Grouping and Aggregation

• Group By

• Aggregation Functions

• Having Clause

• Best Practices

9.  Derived Tables

• Do’s and Don’ts

• Query Techniques

• Best Practices

10.  Sub Queries

• Atomic Sub Queries

• Correlated Queries

• EXISTS and CROSS APPLY

• Best Practices

11.  Common Table Expressions

• CTE Fundamentals

• Query Techniques

• CTE vs Standard Select

12.  Set Operators

• UNION

• Except, Intersect

• Cross Apply

13.  Ranking

• Windowing

• Over Clause

• Ranking, Tiling and Numbering

14.  Aggregation and Offset functions

• Reporting Uses

• LEAD

• LAG

• FIRST_VALUE, LAST_VALUE

• Best Practices

15.  Creating Functions

• Scalar Functions

• Table Valued

• Best Practices

16.  Stored Procs

• Stored Proc Fundamentals

• Variable Usage

• Parameters IN, OUT

• Best Practices

17.  Error Handling

• Error Parts and Types

• Begin_try/Catch

• @@error and error notifications

• Best Practices

18.  Transactions

• Transaction Theory

• Isolation Levels

• Commit & RollBack

• Best Practices

19.  Programmatic Features

• Variable Usages

• Programmatic Statements

• Logic Conditions

• Program Flow

• Dynamic SQL issues

20.  Best Practices

• Overall review of Best Practices and Techniques