Querying Microsoft SQL Server with Transact-SQL

The Querying Microsoft SQL Server with Transact-SQL course covers the basics of the SQL language as implemented by Microsoft SQL Server. The course is designed to give delegates practical experience in writing Transact-SQL statements using the SQL Server Management Studio (SSMS) development environment. The basic Transact-SQL statements, including the use of many built-in Transact-SQL functions are covered on this course. This course covers Microsoft SQL Server up to version 2016.

The delegate will learn and acquire skills as follows:

• Writing SELECT queries
• Sorting and filtering data
• Using SQL aggregate functions
• Using SET operators
• Writing joins to query multiple tables
• Using built-in functions
• Using Subqueries
• Inserting, updating, deleting and merging rows
• Transaction control

Anyone who needs to access and work with a Microsoft SQL Server Database.

To provide the Transact-SQL (T-SQL) coding skills needed to query and modify data held within a Microsoft SQL Server database.

There are no formal pre-requisites for the Querying Microsoft SQL Server with Transact-SQL course, although an understanding of databases and exposure to information technology in general would be useful. This knowledge can be gained by attendance on the Relational Databases & Data Modelling Overview course.

• Advanced Querying Microsoft SQL Server with Transact-SQL
• Programming Microsoft SQL Server with Transact-SQL

• Course technical content is subject to change without notice.
• Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions.

Course Contents - DAY 1

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: DATABASE CONCEPTS

• What is a database
• Database management systems
• Databases, Schemas, Tables, rows and columns
• Indexes, primary keys, unique constraints and foreign keys
• Supported data types
• System Tables

Session 2: USING SQL SERVER MANAGEMENT STUDIO

• Getting started with SQL Server Management Studio (SSMS)
• Selecting a database
• Entering and executing SQL statements
• Creating, editing and executing SQL files

Session 3: WRITING SELECT QUERIES

• Writing simple SQL SELECT statement
• Using DISTINCT to remove duplicate data
• Filtering Data with Predicates
• Logical operators
• The ORDER BY clause
• Filtering with the TOP and OFFSET-FETCH
• Using Column aliases
• Arithmetic expressions
• Precedence of operators

Session 4: USING SET OPERATORS

• Writing Queries using the UNION operator
• Using the INTERSECT operator
• Using the EXCEPT operator
• Introducing the APPLY operator

Session 5: GROUPING AND AGGREGATING DATA

• Using aggregate functions
• Using the GROUP BY clause
• Using Rollup with GROUP BY
• Filtering grouped data with the HAVING clause

Course Contents - DAY 2

Session 6: QUERYING MULTIPLE TABLES

• Understanding JOINS
• CROSS Joins
• Using INNER Joins
• Using Table Aliases
• Using OUTER Joins: LEFT, RIGHT and FULL
• Using SELF JOINS

Session 7: NUMERIC AND CHARACTER FUNCTIONS

• Function types
• Testing functions
• Numeric functions
• Character functions

Session 8: DATE AND TIME FUNCTIONS

• Date and time column types
• Date and time formats
• Functions to return Today's date and time
• Functions to extract components of a date (Day, Month, Year)
• Date time arithmetic
• Displaying Dates in specific Date formats

Course Contents - DAY 3

Session 9: CONVERSION AND MISCELLANEOUS FUNCTIONS

• Converting a string to a date
• Converting a string to a number
• Converting a number to a string
• Using Functions to work with NULL
• Using CASE expressions

Session 10: USING SUBQUERIES

• Using self contained subqueries
• Using correlated subqueries
• Using the EXISTS predicate with subqueries

Session 11: MANAGING DATA

• Inserting rows
• Updating rows
• Deleting rows
• Merging rows
• The truncate statement
• Transaction control

The Querying Microsoft SQL Server with Transact-SQL course covers the basics of the SQL language as implemented by Microsoft SQL Server. The course is designed to give delegates practical experience in writing Transact-SQL statements using the SQL Server Management Studio (SSMS) development environment. The basic Transact-SQL statements, including the use of many built-in Transact-SQL functions are covered on this course. This course covers Microsoft SQL Server up to version 2016.

The delegate will learn and acquire skills as follows:

• Writing SELECT queries
• Sorting and filtering data
• Using SQL aggregate functions
• Using SET operators
• Writing joins to query multiple tables
• Using built-in functions
• Using Subqueries
• Inserting, updating, deleting and merging rows
• Transaction control

Anyone who needs to access and work with a Microsoft SQL Server Database.

To provide the Transact-SQL (T-SQL) coding skills needed to query and modify data held within a Microsoft SQL Server database.

There are no formal pre-requisites for the Querying Microsoft SQL Server with Transact-SQL course, although an understanding of databases and exposure to information technology in general would be useful. This knowledge can be gained by attendance on the Relational Databases & Data Modelling Overview course.

• Advanced Querying Microsoft SQL Server with Transact-SQL
• Programming Microsoft SQL Server with Transact-SQL

• Course technical content is subject to change without notice.
• Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions.

Course Contents - DAY 1

Course Introduction

• Administration and Course Materials
• Course Structure and Agenda
• Delegate and Trainer Introductions

Session 1: DATABASE CONCEPTS

• What is a database
• Database management systems
• Databases, Schemas, Tables, rows and columns
• Indexes, primary keys, unique constraints and foreign keys
• Supported data types
• System Tables

Session 2: USING SQL SERVER MANAGEMENT STUDIO

• Getting started with SQL Server Management Studio (SSMS)
• Selecting a database
• Entering and executing SQL statements
• Creating, editing and executing SQL files

Session 3: WRITING SELECT QUERIES

• Writing simple SQL SELECT statement
• Using DISTINCT to remove duplicate data
• Filtering Data with Predicates
• Logical operators
• The ORDER BY clause
• Filtering with the TOP and OFFSET-FETCH
• Using Column aliases
• Arithmetic expressions
• Precedence of operators

Session 4: USING SET OPERATORS

• Writing Queries using the UNION operator
• Using the INTERSECT operator
• Using the EXCEPT operator
• Introducing the APPLY operator

Session 5: GROUPING AND AGGREGATING DATA

• Using aggregate functions
• Using the GROUP BY clause
• Using Rollup with GROUP BY
• Filtering grouped data with the HAVING clause

Course Contents - DAY 2

Session 6: QUERYING MULTIPLE TABLES

• Understanding JOINS
• CROSS Joins
• Using INNER Joins
• Using Table Aliases
• Using OUTER Joins: LEFT, RIGHT and FULL
• Using SELF JOINS

Session 7: NUMERIC AND CHARACTER FUNCTIONS

• Function types
• Testing functions
• Numeric functions
• Character functions

Session 8: DATE AND TIME FUNCTIONS

• Date and time column types
• Date and time formats
• Functions to return Today's date and time
• Functions to extract components of a date (Day, Month, Year)
• Date time arithmetic
• Displaying Dates in specific Date formats

Course Contents - DAY 3

Session 9: CONVERSION AND MISCELLANEOUS FUNCTIONS

• Converting a string to a date
• Converting a string to a number
• Converting a number to a string
• Using Functions to work with NULL
• Using CASE expressions

Session 10: USING SUBQUERIES

• Using self contained subqueries
• Using correlated subqueries
• Using the EXISTS predicate with subqueries

Session 11: MANAGING DATA

• Inserting rows
• Updating rows
• Deleting rows
• Merging rows
• The truncate statement
• Transaction control