Querying Data with Transact SQL – Intermediate Course

Start Dates: TBC

Duration: 5 Days

CPD Hours: 35 (5 Days)

Full Fee: €1000

Network Members Fee: €700

https://www.ifsskillnet.ie Book Now

Programme overview

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.

Learning outcomes

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.

Modules

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

Introducing T-SQL

Understanding Sets

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

Understanding Joins

Querying with Inner Joins

Querying with Outer Joins

Querying with Cross Joins and Self Joins

Lab: Querying Multiple Tables

5 – Sorting and Filtering Data

Sorting 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

Inserting 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 Views

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

Using APPLY

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

Controlling transactions

Lab: Implementing Transactions

Trainer Profile

Please contact info@ifsskillnet.ie for more information on Training Providers.