VTEC Training
Course Code: 1258
20461 Querying Microsoft SQL Server® 2014
VTEC Training
Portland,
ME
Class Dates:
10/21/2019
11/18/2019
12/16/2019
Length:
5 Days
Cost:
$2495.00
Class Time:
Technology:
Database
Delivery:
Overview
- Course Overview
- This course provides students with the technical skills required to write Transact-SQL queries for MS SQL Server 2014. This course is the foundation for all SQL Server-related disciplines; Database Administration, Database Development & Business Intelligence.
At Course Completion- Describe the basic architecture and concepts of Microsoft SQL Server 2014.
- Understand the similarities & differences between Transact-SQL & other computer languages.
- Write SELECT queries
- Query multiple tables
- Sort & filter data
- Describe the use of data types in SQL Server
- Modify data using Transact-SQL
- Use built-in functions
- Group & aggregate data
- Use subqueries
- Use table expressions
- Use set operators
- Use window ranking, offset & aggregate functions
- Implement pivoting and grouping sets
- Execute stored procedures
- Program with T-SQL
- Implement error handling
- Implement transactions.
- Audience
- This course is intended for Database Administrators, Database Developers, and Business Intelligence professionals.
The course will very likely be well attended by SQL power users who aren’t necessarily database-focused or plan on taking the exam; namely, report writers, business analysts & client app developers..
Prerequisites
- This course requires that you meet the following prerequisites:
- Basic knowledge of the Microsoft Windows operating system and its core functionality.
- Working knowledge of Transact-SQL.
- Working knowledge of relational databases.
- Some experience with database design.
Recommended Courses:
- 55119 - SQL Server 2012 Reporting Services
Course Details
- Module 1: Introduction to SQL Server 2014 Database Administration
- 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/b>
- 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