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.
Who will the Course Benefit?
Anyone who needs to access and work with a Microsoft SQL Server Database.
Requirements
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.
Querying Microsoft SQL Server with Transact-SQL Training Course
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
Querying Microsoft SQL Server with Transact-SQL Training Course
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
Querying Microsoft SQL Server with Transact-SQL Training Course
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.
Who will the Course Benefit?
Anyone who needs to access and work with a Microsoft SQL Server Database.
Requirements
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.
Querying Microsoft SQL Server with Transact-SQL Training Course
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
Querying Microsoft SQL Server with Transact-SQL Training Course
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
Querying Microsoft SQL Server with Transact-SQL Training Course
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