Querying Data with Transact-SQL

This course is designed to introduce students to Transact-SQL. You will learn about: T-SQL elements, writing SQL queries, querying JOINs, sorting and filtering data, SQL Server 2016 data types, T-SQL DML, built-in functions, grouping and aggregating data, subqueries, and set operators.

Delegates will learn how to

·       Describe key capabilities and components of SQL Server 2016.

·       Describe T-SQL, sets, and predicate logic.

·       Write a single table SELECT statement.

·       Write a multi-table SELECT statement.

·       Write SELECT statements with filtering and sorting.

·       Describe how SQL Server uses data types.

·       Write DML statements.

·       Write queries that use built-in functions.

·       Write queries that aggregate data.

·       Write subqueries.

·       Create and implement views and table-valued functions.

·       Use set operators to combine query results.

·       Write queries that use window ranking, offset, and aggregate functions.

·       Transform data by implementing pivot, unpivot, rollup and cube.

·       Create and implement stored procedures.

·       Add programming constructs such as variables, conditions, and loops to T-SQL code.

1. Introduction to Microsoft SQL Server 2016

·       The Basic Architecture of SQL Server

·       SQL Server Editions and Versions

·       Getting Started with SQL Server Management Studio

2. Introduction to T-SQL Querying

·       Introducing T-SQL

·       Sets

·       Predicate Logic

·       Logical Order of Operations in SELECT statements

3. Writing SELECT Queries

·       Writing Simple SELECT Statements

·       Eliminating Duplicates with DISTINCT

·       Using Column and Table Aliases

·       Writing Simple CASE Expressions

4. Querying Multiple Tables

·       Joins

·       Querying with Inner Joins

·       Querying with Outer Joins

·       Querying with Cross Joins and Self Joins

5. Sorting and Filtering Data

·       Sorting Data

·       Filtering Data with Predicates

·       Filtering with the TOP and OFFSET-FETCH Options

·       Working with Unknown Values

6. Working with SQL Server 2016 Data Types

·       Introducing SQL Server 2016 Data Types

·       Working with Character Data

·       Working with Date and Time Data

7. Using DML to Modify Data

·       Adding Data to Tables

·       Modifying and Removing Data

·       Generating automatic column values

8. Using Built-In Functions

·       Writing Queries with Built-In Functions

·       Using Conversion Functions

·       Using Logical Functions

·       Using Functions to Work with NULL

9. Grouping and Aggregating Data

·       Using Aggregate Functions

·       Using the GROUP BY Clause

·       Filtering Groups with HAVING

10. Using Subqueries

·       Writing Self-Contained Subqueries

·       Writing Correlated Subqueries

·       Using the EXISTS Predicate with Subqueries

11. Using Table Expressions

·       Using Views

·       Using Inline Table-Valued Functions

·       Using Derived Tables

·       Using Common Table Expressions

12. Using Set Operators

·       Writing Queries with the UNION Operator

·       Using EXCEPT and INTERSECT

·       Using APPLY

13. Using Windows Ranking, Offset, and Aggregate Functions

·       Creating Windows with OVER

·       Exploring Window Functions

14. Pivoting and Grouping Sets

·       Writing Queries with PIVOT and UNPIVOT

·       Working with Grouping Sets

15. Executing Stored Procedures

·       Querying Data with Stored Procedures

·       Passing Parameters to Stored procedures

·       Creating Simple Stored Procedures

·       Working with Dynamic SQL

16. Programming with T-SQL

·       T-SQL Programming Elements

·       Controlling Program Flow

17. Implementing Error Handling

·       Implementing T-SQL Error Handling

·       Implementing Structured Exception Handling

18. Implementing Transactions

·       Transactions and the Database Engines

·       Controlling Transactions

There are no prerequisites for this course.

Program Details
Duration 5 Days
Capacity Max 12 Persons
Training Type Classroom / Virtual Classroom

Can't find what you're looking for? Let us know if you have a query or cannot find what you are looking for.