Querying Microsoft SQL Server 2014 (M20461)

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. 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

• 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

Lab 1: Work with SQL Server 2014 Tools

• SQL Server Management Studio
• Create and Organize T-SQL Scripts
• Books Online

Lab 2: Transact-SQL Querying

• Execute Basic SELECT Statements
• Execute Queries which filter data using predicates and sort data using ORDER BY

Lab 3: Write Basic SELECT Statements

• Write Simple SELECT Statements
• Eliminate Duplicates using Distinct
• Table and Column Aliases
• Simple CASE Expression

Lab 4: Query Multiple Tables

• Write Queries that use Inner Joins, Multiple-Table Inner Join, Self Joins, Outer Joins, and Cross Joins

Lab 5: Sort and Filter Data

• Write Queries that filter data using a WHERE Clause, ORDER BY Clause, TOP Option and OFFSET-FETCH Clause

Lab 6: SQL Server 2014 Data Types

• Write Queries that return date and time data and character data
• Write Queries that use date, time, and character functions

Lab 7: Modify Data using DML

• Insert, Update and Delete Data

Lab 8: Built-In Functions

• Write Queries which use conversion and logical functions
• Write Queries which test for nullability

Lab 9: Group and Aggregate Data

• Write Queries which use the GROUP BY Clause
• Write Queries which use aggregate and distinct aggregate functions
• Write Queries which filter group with the HAVING Clause

Lab 10: Sub-Queries

• Write Queries which use self-contained, scalar and multi-result sub-queries
• Write Queries which use correlated sub-queries and EXISTS predicate

Lab 11: Table Expressions

• Write Queries which use Views, Derived Tables and Common Table Expressions
• Write Queries which use Inline Table-Valued Functions

Lab 12: Set Operators

• Write Queries which use UNION set operators and UNION ALL multi-set operators
• Write Queries which use CROSS APPLY and OUTER APPLY operators
• Write Queries which use EXCEPT and INTERSECT operators

Lab 13: Windows Ranking, Offset and Aggregate Functions

Lab 14: Pivoting and Grouping Sets

Lab 15: Execute Stored Procedures

• Invoke stored procedures using the EXECUTE statement
• Pass parameters to stored procedures
• Execute system stored procedures

Lab 16: Programming with T-SQL

• Declare Variables and Delimiting Batches
• Control-of-Flow Elements
• Generate Dynamic SQL
• Synonyms

Lab 17: Implement Error Handling

• Redirect Errors with TRY/CATCH
• Pass an Error Message Back to a Client using THROW

Lab 18: Implement Transactions

• Control transactions with BEGIN, COMMIT and ROLLBACK
• Add error handling to a CATCH block

Lab 19: Improve Query Performance

• View Query Execution Plans
• View Index Usage and Using SET STASTICS Statements

Lab 20: Query SQL Server Metadata

• Query System Catalog Views
• Query System Functions
• Query System Dynamic Management Views

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

• Database administrators
• Database developers
• Business intelligence professionals
• SQL power-users such as:
  • Report writers
  • Business analysts
  • Client application developers