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.

Upcoming Classes:

Register
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