Start Dates: TBC
Duration: 5 Days
CPD Hours: 35 (5 Days)
Full Fee: €1000
Network Members Fee: €700
This course is designed to introduce students to Transact-SQL. It is designed in such a way that the first three days can be taught as a course to students requiring the knowledge for other courses in the SQL Server curriculum. Days 4 & 5 teach the remaining skills required to take the exam.
After completing this course, students will be able to:
– Describe key capabilities and components of SQL Server.
– 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.
Who is the course for?
The main purpose of the course is to give students a good understanding of the Transact-SQL language which is used by all SQL Server-related disciplines; namely, Database Administration, Database Development and Business Intelligence. As such, the primary target audience for this course is: Database Administrators, Database Developers and BI professionals.
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
Lab: Working with SQL Server 2016 Tools
2 – Introduction to T-SQL Querying
Understanding Predicate Logic
Understanding the Logical Order of Operations in SELECT statements
Lab: Introduction to Transact-SQL Querying
3 – Writing SELECT Queries
Writing Simple SELECT Statements
Eliminating Duplicates with DISTINCT
Using Column and Table Aliases
Writing Simple CASE Expressions
Lab: Writing Basic SELECT Statements
4 – Querying Multiple Tables
Querying with Inner Joins
Querying with Outer Joins
Querying with Cross Joins and Self Joins
Lab: Querying Multiple Tables
5 – Sorting and Filtering Data
Filtering Data with Predicates
Filtering with TOP and OFFSET-FETCH
Working with Unknown Values
Lab: Sorting and Filtering Data
6 – Working with SQL Server 2016 Data Types
Introducing SQL Server 2016 Data Types
Working with Character Data
Working with Date and Time Data
Lab: Working with SQL Server 2016 Data Types
7 – Using DML to Modify Data
Modifying and Deleting Data
Lab: Using DML to Modify Data
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
9 – Grouping and Aggregating Data
Using Aggregate Functions
Using the GROUP BY Clause
Filtering Groups with HAVING
Lab: Grouping and Aggregating Data
10 – Using Subqueries
Writing Self-Contained Subqueries
Writing Correlated Subqueries
Using the EXISTS Predicate with Subqueries
Lab: Using Subqueries
11 – Using Table Expressions
Using Inline Table-Valued Functions
Using Derived Tables
Using Common Table Expressions
Lab: Using Table Expressions
12 – Using Set Operators
Writing Queries with the UNION Operator
Using EXCEPT and INTERSECT
Lab: Using Set Operators
13 – Using Windows Ranking, Offset, and Aggregate Functions
Creating Windows with OVER
Exploring Window Functions
Lab: Using Windows Ranking, Offset, and Aggregate Functions
14 – Pivoting and Grouping Sets
Writing Queries with PIVOT and UNPIVOT
Working with Grouping Sets
Lab: Pivoting and Grouping Sets
15 – Executing Stored Procedures
Querying Data with Stored Procedures
Passing Parameters to Stored procedures
Creating Simple Stored Procedures
Working with Dynamic SQL
Lab: Executing Stored Procedures
16 – Programming with T-SQL
T-SQL Programming Elements
Controlling Program Flow
Lab: Programming with T-SQL
17 – Implementing Error Handling
Implementing T-SQL error handling
Implementing structured exception handling
Lab: Implementing Error Handling
18 – Implementing Transactions
Transactions and the database engines
Lab: Implementing Transactions
Please contact firstname.lastname@example.org for more information on Training Providers.