Course
Microsoft SQL Server Programming (2012 - 2019)
Private Courses
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 to produce a bespoke training package – just ask for details.
This course is available as a private event. A private event can be held at one of our venues, your location, or somewhere else if you prefer.
Course Duration
5 Days Overview
This 5 day course is intended for students new to querying and programming with Microsoft SQL Server products who need a total immersion in the subject on a SQL Server 2016 or 2017 platform. This is a very comprehensive and intensive course with plenty of illustrated examples and augmented with practical hands-on exercises to enhance the learning experience. 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 to produce a bespoke training package – just ask for details.
Prerequisites
-
- Delegates should be familiar with basic programming concepts and 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, such as can be found in the SQL Server Relational Database Design and Querying Fundamentals course.
Objectives
-
- Identify the Primary Components of a Typical SQL Server 2016 or 2017 Installation
- Identify the Tools Available in SQL Server 2016 or 2017
- Use SQL Server Management Studio (SSMS)
- Understand the Components of Transact-SQL (T-SQL)
- Retrieve, Filter and Sort Data
- Work with the Native SQL Server Data Types
- Query Data from Multiple Tables Using Joins
- Summarize and Group Data
- Use Window Functions for Ranking and Aggregating Data
- Combine and Limit Result Sets
- Work with Subqueries
- Use Crosstab Queries
- Use Common Table Expressions
- Work with SQL Server Date and Time Data
- Modify Data with INSERT, DELETE and UPDATE
- Merge Data with the MERGE Statement
- Understand and Implement Transactions
- Understand SQL Server Locks
- Understand SQL Server Indexes
- Understand Query Performance Issues
- Create and Manage Views
- Control the Flow of Program Execution
- Create and Implement Stored Procedures
- Handle Errors and Exceptions
- Create and Implement User Defined Functions
- Create and Implement Triggers
What's included
-
- Comprehensive course materials and exercises
Course Content
-
Introduction to SQL Server Management StudioIdentify the Tools and Components of SQL Server
Identifying the Elements of SSMS
Objects in a SQL Server Database Synonyms
Database Diagrams
Components of a SQL Server Database Table The Querying Environment
Creating a Script
Adding Comments to your Code
-
Introduction to SQL and Transact-SQLANSI-SQL and Transact-SQL (T-SQL)
Categories of SQL Statements -
Using the SELECT Statement to Retrieve DataExamine the Basic Syntax of the SELECT Statement
Using String Literals and Expressions
Retrieving Columns of Data from a Table
Using Aliases for Column Names
-
Filtering Rows of Data with the WHERE ClauseHow 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
-
Formatting and Sorting Result SetsSorting 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
-
Working with SQL Server Data TypesNative SQL Server Data Types
Data Type Precedence
Implicit Data Type Conversions
Explicit Data Type Conversion Functions
Working with Variables
Using Batches -
Querying Data from Multiple Tables Using JoinsIntroduction 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
-
Summarizing Data Using Aggregate FunctionsUsing 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
-
Window FunctionsAggregate Window Functions
Ranking Functions
Analytic Window Functions -
Combining and Limiting Result SetsCombining Result Sets Using the UNION Operator
Using the EXCEPT and INTERSECT Operators
Using UNION, EXCEPT, and INTERSECT Together
Limiting Result Sets Using the TOP Operator
Using OFFSET/FETCH NEXT Filters
-
Working with SubqueriesIntroduction to Subqueries
Using Subqueries as Expressions and Derived Tables
Using Correlated Subqueries
Using EXISTS with Correlated Subqueries
Using the APPLY Operator
-
Temporary StorageTable Variables
Temporary Tables -
Crosstab QueriesUsing the PIVOT Operator
Using the UNPIVOT Operator
-
Common Table ExpressionsIntroduction to Common Table Expressions
Using Common Table Expressions
Recursive Queries Using Common Table Expressions
-
SQL Server Date and Time Data TypesUnderstanding 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
-
Inserting Data into TablesInserting 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
-
Deleting Data from TablesDeleting Rows from a Table
Truncating a Table
Deleting Rows Based on Data in Other Tables
Using the OUTPUT Clause with DELETE -
Updating Data in TablesUpdating Rows in a Table
Updating Rows Based on Data in Other Tables
Using the OUTPUT Clause with UPDATE -
Merging DataThe MERGE Statement
Practical Examples of Using MERGE -
Transactions and LockingWhat Is a Transaction?
How SQL Server Modifies Data in Tables
Managing Transactions
Nested Transactions
Transaction Isolation Levels
Delayed Durability
SQL Server Locking Architecture
Managing Locks
-
SQL Server IndexesUnderstanding SQL Server Index Architecture
Creating and Using Nonclustered Indexes
Creating and Using Clustered Indexes
Filtered Indexes -
Query Performance ConsiderationsHow SQL Server Processes T-SQL Queries
Examining Execution Plans
Dynamically Generating T-SQL Code
Writing Efficient Search Arguments -
Implementing ViewsOverview of Views
Creating and Modifying a View
Guidelines for Creating Views
Examining the Impact of Using SELECT * in Views
Restrictions for Modifying Data by Using Views
Indexed Views
Partitioned Views -
Controlling Program ExecutionControl-of-Flow Language Statements
Logical Functions -
Implementing Stored ProceduresOverview of Stored Procedures
How Stored Procedures Are Executed by SQL Server
Creating and Using a Stored Procedure
Parameterising Stored Procedures
Returning Values from a Stored Procedure
Using Table Valued Parameters (TVPs)
Controlling Execution Context
Parameter Sniffing -
Error HandlingUsing @@ERROR
Using RAISERROR
Using TRY…CATCH
Using the THROW Statement -
Implementing User Defined FunctionsOverview of User Defined Functions (UDFs)
Creating and Modifying Scalar UDFs
Creating and Modifying Table-Valued UDFs
Guidelines for Implementing UDFs
-
Implementing TriggersOverview of Triggers
How Triggers Work
AFTER Triggers
INSTEAD OF Triggers
DDL Triggers -
Appendix 1. Querying MetadataUnderstanding Metadata
Querying Metadata by Using Views
Querying Metadata by Using Stored Procedures
Querying Metadata by Using Functions
Metadata Discovery in SQL Server -
Appendix 2. Distributed QueriesAd Hoc Distributed Queries
Linked Servers
Distributed Queries Against Linked Servers
You can choose your learning path and delivery style. Courses can be delivered at your office or at one of our training venues, it's your choice. Small class sizes. Course materials included. Flexible course content. Expert trainers and tried & tested training methods. Course materials available online using your personal training account.
You can master SQL Server, please contact us to book a place or request a quote.
-
Course Options
Please contact us to discuss options for this courseBespokeBespokeBespoke private training can be based on an existing course or a number of lessons taken from several courses, we can also write bespoke courses for you based on your in-house systems or development program.PrivatePrivateFlexible content, flexible date, flexible location, variable length, private to your organisation - Courses can be held at one of our training venues or at another location convenient to you. Training can be set over a number of days or weeks to allow for practice time between training sessions.
- Virtual Training
IT Training Solutions Ltd
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:3/28/2024 1:50:23 PM
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:3/28/2024 1:50:23 PM