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
· 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
· 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.
|Capacity||Max 12 Persons|
|Training Type||Classroom / Virtual Classroom|