IT TRAINING

Microsoft 20461 – Querying Microsoft SQL Server 2012

In this course you will learn how to write basic Transact-SQL queries for Microsoft SQL Server 2012 and 2014. Other topics discussed in this course include SQL Server Management Studio, T-SQL, SELECT statements in querying multiple tables, data types, data sorting and filtering, data manipulation language, grouping and aggregating data, table expressions, set operators, window functions, T-SQL programming, error handlers and transaction management in SQL Server.

Who needs to attend

Who needs to attend?
This course is aimed at database administrators, database developers, business intelligence professionals and SQL power users (report writers, business analysts, client application developers).

what you will learn

What you will learn

Upon completion you will know how to:

  • SELECT query writing
  • Query multiple tables
  • Sort and filter data
  • Data types in SQL Server
  • Data modification using T-SQL
  • Built-in functions
  • Group and aggregate data
  • Set operators
  • Window functions: ranking, offset, and aggregate
  • Pivot and group sets
  • T-SQL programming
  • Error handling and transaction implementation
Prerequisites

Prerequisites

Students need to have:

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

Course Outline

1. 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. Execute 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. Improve Query Performance
Factors in Query Performance
Display Query Performance Data

20. Query SQL Server Metadata
Query System Catalog Views and Functions
Execute System Stored Procedures
Query Dynamic Management Objects

Follow on

  • Administering Microsoft SQL Server Databases (M20462)
  • Implementing a Data Warehouse with Microsoft SQL Server (M20463)
  • Developing Microsoft SQL Server Databases (M20464)
  • Designing a Data Solution with Microsoft SQL Server (M20465)

Certification programs

  • MCSA: SQL Server 2012