MS-20461 Querying Microsoft SQL Server

KLUCZOWE ZAGADNIENIA

• podstawy T-SQL – od prostych zapytań do bardziej zaawansowanych
• modyfikacje w danych – dodaj, zamień, usuń
• wykorzystanie funkcji okna OVER()
• konstrukcje zaawansowane – PIVOT I UNPIVOT, CTE, GROUPING SETS
• kontrola przepływu danych i obsługa błędów
• poziomy izolacji i obsługa transakcji

Bartosz Ratajczyk, Microsoft Certified Trainer

Tematyka szkolenia pokrywa zagadnienia wymagane na ścieżce MCSA: SQL Server 2012
Dowiedz się więcej o certyfikacjach Microsoft

TEMATYKA ZAJĘĆ

• Module 1: Introduction to Microsoft SQL Server 2014
  The Basic Architecture of SQL Server
  SQL Server Editions and Versions
  Getting Started with SQL Server Management Studio
  Lab: Working with SQL Server 2014 Tools

• Module 2: Introduction to T-SQL Querying
  Introducing T-SQL
  Understanding Sets
  Understanding Predicate Logic
  Understanding the Logical Order of Operations in SELECT statements
  Lab: Introduction to Transact-SQL Querying

• Module 3: Writing SELECT Queries
  Writing Simple SELECT Statements
  Eliminate Duplicates with DISTINCT
  Using Column and Table Aliases
  Write Simple CASE Expressions
  Lab: Writing Basic SELECT Statements

• Module 4: Querying Multiple Tables
  Understanding Joins
  Querying with Inner Joins
  Querying with Outer Joins
  Querying with Cross Joins and Self Joins
  Lab: Querying Multiple Tables

• Module 5: Sorting and Filtering Data
  Sorting Data
  Filtering Data with Predicates
  Filtering with the TOP and OFFSET-FETCH
  Working with Unknown Values
  Lab: Sorting and Filtering Data

• Module 6: Working with SQL Server 2014 Data Types
  Introducing SQL Server 2014 Data Types
  Working with Character Data
  Working with Date and Time Data
  Lab: Working with SQL Server 2014 Data Types

• Module 7: Using DML to Modify Data
  Inserting Data
  Modifying and Deleting Data
  Lab: Using DML to Modify Data

• Module 8: Using Built-In Functions
  Writing Queries with Built-In Functions
  Using Conversion Functions
  Using Logical Functions
  Using Functions to Work with NULL
  Lab: Using Built-In Functions

• Module 9: Grouping and Aggregating Data
  Using Aggregate Functions
  Using the GROUP BY Clause
  Filtering Groups with HAVING
  Lab: Grouping and Aggregating Data

• Module 10: Using Subqueries
  Writing Self-Contained Subqueries
  Writing Correlated Subqueries
  Using the EXISTS Predicate with Subqueries
  Lab: Using Subqueries

• Module 11: Using Table Expressions
  Using Views
  Using Inline Table-Valued Functions
  Using Derived Tables
  Using Common Table Expressions
  Lab: Using Table Expressions

• Module 12: Using Set Operators
  Writing Queries with the UNION Operator
  Using EXCEPT and INTERSECT
  Using APPLY
  Lab: Using Set Operators

• Module 13: Using Window Ranking, Offset, and Aggregate Functions
  Creating Windows with OVER
  Exploring Window Functions
  Lab: Using Window Ranking, Offset and Aggregate Functions

• Module 14: Pivoting and Grouping Sets
  Writing Queries with PIVOT and UNPIVOT
  Working with Grouping Sets
  Lab: Pivoting and Grouping Sets

• Module 15: Querying data with Stored Procedures
  Writing Queries with PIVOT and UNPIVOT
  Passing Parameters to Stored Procedures
  Creating Simple Stored Procedures
  Working with Dynamic SQL
  Lab: Executing Stored Procedures

• Module 16: Programming with T-SQL
  T-SQL Programming Elements
  Controlling Program Flow
  Lab: Programming with T-SQL

• Module 17: Implementing Error Handling
  Using TRY / CATCH Blocks
  Working with Error Information
  Lab: Implementing Error Handling

• Module 18: Implementing Transactions
  Transactions and the Database Engine
  Controlling Transactions
  Lab: Implementing Transactions

• Module 19: Appendix 1: Improving Query Performance
  Factors in Query Performance
  Displaying Query Performance Data
  Lab: Improving Query Performance

• Module 20: Appendix 2: Querying SQL Server Metadata
  Querying System Catalog Views and Functions
  Executing System Stored Procedures
  Querying Dynamic Management Objects
  Lab: Querying SQL Server Metadata