Training

Querying Microsoft SQL Server 2014 (M20461)

Description: 

Prerequisites

  • Working knowledge of relational databases
  • Basic knowledge of Microsoft Windows operation system and its core functionality

Related Certifications

Course Brief

In this course, you will learn the technical skills required to write basic Transact-SQL (T-SQL) queries for Microsoft SQL Server 2014. This is the foundational course for all SQL server related disciplines: database administration, database development and business intelligence. Tools and skills you will learn include: SQL Server Management Studio, T-SQL, SELECT statements in querying multiple tables, data types, data sorting and filtering, data manipulation language (DML), grouping and aggregating data, table expressions, set operators, window functions, T-SQL programming, error handlers, and transaction management in SQL Server.

 Course Outline

. Microsoft SQL Server 2014

  • SQL Server Architecture
  • SQL Server Editions and Versions
  • SQL Server Management Studio

2. Transact-SQL Querying

  • Transact-SQL
  • Sets
  • Predicate Logic
  • Logical Order of Operations in SELECT Statements

3. Write SELECT Queries

  • Write Simple SELECT Statements
  • Eliminate Duplicates with DISTINCT
  • Column and Table Aliases
  • Write Simple CASE Expressions

4. Querying Multiple Tables

  • Joins
  • Query with Inner Joins and Outer Joins
  • Query with Cross Joins and Self Joins

5. Sorting and Filtering Data

  • Sort Data
  • Filter Data with a WHERE Clause
  • Filter with the TOP and OFFSET-FETCH Options
  • Work with Unknown and Missing Values

6. SQL Server 2014 Data Types

  • SQL Server 2014 Data Types
  • Work with Character Data
  • Work with Date and Time Data

7. DML to Modify Data

  • Insert Data
  • Modify and Delete Data

8. Built-In Functions

  • Write Queries with Built-In Functions
  • Conversion Functions
  • Logical Functions
  • Use Functions to Work with NULL

9. Grouping and Aggregating Data

  • Use Aggregate Functions
  • Use the GROUP BY Clause
  • Filter Groups with HAVING

10. Sub-queries

  • Write Self-Contained Sub-queries
  • Write Correlated Sub-queries
  • Use the EXISTS Predicate with Sub-queries

11. Table Expressions

  • Use Derived Tables
  • Use Common Table Expressions
  • Use Views
  • Use Inline Table-Valued Functions

12. Set Operators

  • Write Queries with the UNION Operator
  • Use EXCEPT and INTERSECT
  • Use APPLY

13. Window Ranking, Offset, and Aggregate Functions

  • Create Windows with OVER
  • Explore Window Functions including Ranking, Aggregate and Offset Functions

14. Pivoting and Grouping Sets

  • Write Queries with PIVOT and UNPIVOT
  • Work with Grouping Sets

15. Query Data with Stored Procedures

  • Query Data with Stored Procedures
  • Pass Parameters to Store Procedures
  • Create Simple Stored Procedures
  • Work with Dynamic SQL

16. Programming with T-SQL

17. Implement Error Handling

  • Use TRY/CATCH Blocks
  • Work with Error Information

18. Implement Transactions

  • Transactions and the Database Engine
  • Control Transactions
  • Isolation Levels

19. Appendix 1: Improve Query Performance

  • Factors in Query Performance
  • Display Query Performance Data

20. Appendix 2: Query SQL Server Metadata

  • Query System Catalog Views and Functions
  • Execute System Stored Procedures
  • Query Dynamic Management Objects

 

 

Specification: 

Training Type: 
Microsoft

  Subsidiaries of Totheburn Trading