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