Microsoft SQL Server 2016 Querying Essentials
This 4 day course is intended for students who already have some experience with SQL and need to fast-track their querying skills to an advanced level. Effectively, this is the “Microsoft SQL Server 2016 Introduction to Querying” and “Microsoft SQL Server 2016 Advanced Querying” courses combined. This course may also prove beneficial to delegates wanting to refresh their existing querying skills.
You will find this to be a very comprehensive course, with plenty of illustrated examples and augmented with practical hands-on exercises. Additionally, with the course structure being fully modularised, customised versions of this course can also be devised and delivered to suit individual requirements. In fact, specific content from this course could also be combined with material from other related courses in order to produce a bespoke training package – just ask for details.
Students should ideally be familiar with basic programming concepts. They should also understand the fundamental design of relational databases including, but not restricted to, the purpose of primary and foreign keys, entity relationships, and data normalization. These prerequisites can be met by taking the 2 day “Microsoft SQL Server 2016 Relational Database Design and Querying Fundamentals” course.
Moreover, students should ideally possess some experience of querying data with SQL in a Microsoft SQL Server environment. This is a fast paced course, and delegates will need the aptitude to quickly assimilate the material presented, and whether novice or experienced, should feel comfortable absorbing new ideas quickly.
Upon successful completion of the full course, students will be able to:
1. Introduction to SQL Server Management Studio
Identifying the Elements of SSMS
Objects in a SQL Server Database
Database Diagrams
Components of a SQL Server Database Table
The Querying Environment
Creating a Script
Adding Comments to your Code
Categories of SQL Statements
ANSI-SQL and Transact-SQL (T-SQL)
2. Using the SELECT Statement to Retrieve Data
Examine the Basic Syntax of the SELECT Statement
Using String Literals and Expressions
Retrieving Columns of Data from a Table
Using Aliases for Column Names
3. Filtering Rows of Data with the WHERE Clause
How to Use the WHERE Clause
Overview of Operators
Filter Data by Using Comparison Operators
Filter Data by Using String Comparisons
Filter Data by Using Logical Operators
Filter Data Using a Range of Values
Filter Data Using a List of Values
Filter Data with NULL Values
Filtering Date and Time Data
4. Formatting and Sorting Result Sets
Sorting Data Using the ORDER BY Clause
Eliminating Duplicate Rows with DISTINCT
Introducing Native SQL Server Functions
Getting Help on Functions
Functions Relating to NULL Values
Using the CASE Expression
5. Working with SQL Server Data Types
Native SQL Server Data Types
Data Type Precedence
Implicit Data Type Conversions
Explicit Data Type Conversion Functions
Working with Variables
Using Batches
6. Querying Data from Multiple Tables Using Joins
Introduction to Joins
Using Inner Joins
Using Outer Joins
Using Cross Joins
Using Aliases for Table Names
Joining More than Two Tables
Joining a Table to Itself (Self Join)
Using Non-Equi Joins
7. Summarizing Data Using Aggregate Functions
Using Aggregate Functions Native to SQL Server
Using Aggregate Functions with NULL Values
Grouping Summarized Data with GROUP BY
Filtering Grouped Data Using the HAVING Clause
Using the ROLLUP and CUBE Operators
Using GROUPING SETS
8. Ranking Grouped Data
Using ROW_NUMBER
Using RANK
Using DENSE_RANK
Using NTILE
9. Analysing Data with Window Functions
Aggregate Window Functions
Analytic Window Functions
10. Combining and Limiting Result Sets
Combining Result Sets Using the UNION Operator
Using the EXCEPT and INTERSECT Operators
Limiting Result Sets Using the TOP Operator
Using OFFSET/FETCH NEXT Filters
11. Working with Subqueries
Using Subqueries as Expressions and Derived Tables
Using Correlated Subqueries
Using EXISTS with Correlated Subqueries
Using the APPLY Operator
12. Temporary Storage
Table Variables
Temporary Tables
13. Crosstab Queries
Using the PIVOT Operator
Using the UNPIVOT Operator
14. Common Table Expressions
Using Common Table Expressions
Recursive Queries Using Common Table Expressions
15. Inserting Data into Tables
Inserting a Single Row into a Table
Inserting Multiple Rows into a Table
Inserting Values into Identity Columns
The SEQUENCE Object
Differentiating Various INSERT Statements
Using the OUTPUT Clause with INSERT
16. Deleting Data from Tables
Deleting Rows from a Table
Truncating a Table
Deleting Rows Based on Data in Other Tables
Using the OUTPUT Clause with DELETE
17. Updating Data in Tables
Updating Rows in a Table
Updating Rows Based on Data in Other Tables
Using the OUTPUT Clause with UPDATE
18. Merging Data
The MERGE Statement
Practical Examples of Using MERGE
19. SQL Server Date and Time Data Types
Understanding SQL Server Date and Time Data Types
Querying and Modifying Date and Time Data
Native SQL Server Date and Time Functions
Guidelines and Techniques for Querying Dates
Group discount | ||
1 place | £ 3000 | n/a |
2 places | £ 3600 | n/a |
3 places | £ 9000 | £ 4200 |
4 places | £ 12000 | £ 4800 |
5 places | £ 15000 | £ 5400 |
6 places | £ 18000 | £ 6000 |
7 places | £ 21000 | £ 6600 |
8 places | £ 24000 | £ 7200 |