Course Outline

Introduction to Microsoft SQL Server 2014

• Basic Architecture of SQL Server
• SQL Server versions and editions
• SQL Server Management Studio

Introduction to T-SQL Querying

• T-SQL overview
• Understand sets
• Understand base logic and logical order of operations in SELECT statements

Writing SELECT Queries

• How to write simple SELECT statements
• Remove duplicates with DISTINCT
• Use table aliases and columns
• Write simple CASE expressions

Querying Multiple Tables

• Joins overview
• Query with Inner and outer joins
• Query with self-joins and cross joins

Sorting and Filtering Data

• Sort data
• Using WHERE clause filter data
• With TOP and OFFSET-FETCH options filter data
• Work with missing and unknown values

Working with SQL Server 2014 Data Types

• SQL Server 2014 data types
• Work with character data
• Work with time and date data

Use DML to Change Data

• Add data
• Modify and delete data

Using Built-in Functions

• With Built-in functions write queries
• Use conversion and logical functions
• Use functions to work with NULL

Grouping and Aggregating data

• Use aggregate functions
• Use GROUP BY clause
• Filter groups with HAVING

Using Subqueries

• Write Self-contained and correlated subqueries
• Use EXISTS predicate with subqueries

Using Table Expressions

• Use derived tables and common table expressions
• Use views and Inline Table-Valued Functions

Using Set Operators

• With UNION operator write queries
• Use INTERSECT, EXCEPT and APPLY

Using Window Ranking, Offset, and Aggregate Functions

• With OVER create windows
• Explore Window Functions

Pivoting and Grouping Sets

• Write queries with PIVOT and UNPIVOT
• Work with grouping sets

Executing Stored Procedures

• Query data with stored procedures
• Pass parameters to stored procedures
• Create simple stored procedures
• Work with Dynamic SQL

Programming with T-SQL

• Programming elements of T-SQL
• Controlling program flow

Implementing Error Handling

• Use TRY/CATCH block
• Work with error information

Implementing Transactions

• Transactions and Database engine
• Control transactions
• Isolation Levels