Microsoft SQL Server Programming

Microsoft SQL Server Programming

Audience

This course is intended for those who are new to querying with Microsoft SQL Server products and need a total immersion in the subject.

This class can be delivered at any recent release of SQL Server, from 2008 to 2017.  Please specify the version applicable to your workplace when booking your class.

Prerequisites

Students should ideally be familiar with basic programming concepts and understand the fundamental design of relational databases and the concept of data normalization.

Duration

4 days. Hands on.

Course Objectives

This is a comprehensive, intensive course with plenty of illustrated examples and augmented with practical hands-on exercises.

Please note that the optional modules detailed in the course content below are not delivered as part of the standard course, due to time constraints. However, as this course is fully modularised, when delivered as an on-site course, optional modules can be included on request.

Upon successful completion of this course, delegates will be able to:

  • Use SQL Server Management Studio
  • Understand the Syntax of Transact-SQL
  • Retrieve, Filter and Sort Data
  • Query Data from Multiple Tables by Using Joins
  • Summarize and Rank Grouped Data
  • Combine and Limit Result Sets
  • Work with Subqueries
  • Understand Query Performance Issues
  • Use Crosstab Queries
  • Use Common Table Expressions
  • Modify Data with INSERT, DELETE and UPDATE
  • Understand and Implement Transactions
  • Understand SQL Server Locks
  • Work with the SQL Server Date and Time Data Types
  • Query and Work with XML Data in SQL Server
  • Create and Manage Views
  • Create and Manage Stored Procedures
  • Handle Errors in Transact-SQL Code
  • Create and Manage User Defined Functions
  • Create and Manage Triggers

Course Content

Introduction to SQL Server Databases and Management Studio
Identifying the Elements of SQL Server Management Studio
Objects in a SQL Server Database
Database Diagrams
Components of a SQL Server Database Table
Native SQL Server Data Types
Executing a Query in SQL Server Management Studio
Introduction to SQLCMD

Introduction to SQL and Transact-SQL
ANSI SQL vs. T-SQL (Transact-SQL)
Categories of SQL Statements
T-SQL Scripts
Using Batches
Adding Comments to T-SQL Code
Working with Variables
Conditional Statements

Using the SELECT Statement to Retrieve Data
Examine the Basic Syntax of the SELECT Statement
Retrieving Columns of Data from a Table
Using Aliases for Column Names
Using Aliases for Table Names

Retrieving Specific Rows of Data Using 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
Working with NULL Values
Introducing Native SQL Server Functions
Functions Relating to NULL Values

Formatting and Sorting Result Sets
Sorting Data Using the ORDER BY Clause
Eliminating Duplicate Rows
Using String Literals
Using Expressions

Querying Data from Multiple Tables by Using Joins
Introduction to Joins
Using Inner Joins
Using Outer Joins
Using Cross Joins
Joining More than Two Tables
Joining a Table to Itself
Using Non-Equi Joins
How to Join a Table to a User-Defined Function

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 the COMPUTE Clause
How to Implement Custom Aggregate Functions

Ranking Grouped Data
Ranking Data Using Ranking Functions
Using ROW_NUMBER
Using RANK
Using DENSE_RANK
Using NTILE
Summary of Ranking Functions Based on Their Functionality

Combining and Limiting Result Sets
Combining Result Sets Using the UNION Operator
Limiting Result Sets Using the EXCEPT and INTERSECT Operators
Identifying the Order of Precedence of UNION, EXCEPT, and INTERSECT
Limiting Result Sets Using the TOP Operator
Limiting Result Sets Using the TABLESAMPLE Operator

Working with Subqueries
Introduction to Subqueries
Using Subqueries as Expressions and Derived Tables
Using the ANY, ALL, and SOME Operators
Scalar versus Tabular Subqueries
Using Correlated Subqueries
Using the EXISTS Clause with Correlated Subqueries
Using the APPLY Operator
Subqueries versus Joins Subqueries versus Temporary Tables

Performance Considerations for Writing Queries
How SQL Server Processes T-SQL Queries
How the Query Optimizer Processes Search Arguments
Writing Efficient Search Arguments

Crosstab Queries
Using the PIVOT Operator
Using the UNPIVOT Operator

Common Table Expressions
Introduction to Common Table Expressions
Using Common Table Expressions Recursive Queries
Using Common Table Expressions Techniques for Querying Hierarchical Data

Inserting Data into Tables
Inserting a Single Row into a Table
Inserting Multiple Rows into a Table
Inserting Values into Identity Columns
Differentiating Various INSERT Statements
Using the OUTPUT Clause with the INSERT Statement

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 the DELETE Statement

Updating Data in Tables
Updating Rows in a Table
Updating Rows Based on Data in Other Tables
Using the OUTPUT Clause with the UPDATE Statement

Transactions and Locking
What Is a Transaction?
How SQL Server Modifies Data in Tables
Managing Transactions
Nested Transactions
Transaction Isolation Levels
SQL Server Locking Architecture
Managing Locks

Working with SQL Server Date and Time Data
Data Type Precedence
Implicit Data Type Conversions
Querying and Modifying Date and Time Data
Using the SQL Server Date and Time Data Types

Querying XML Data
Introduction to XML
How SQL Server Implements XML
Generating XML Based Reports with FOR XML
Querying XML by Using OpenXML
Introduction to XQuery
Querying XML by Using XQuery
Querying Relational Data Combined with XML Data
XML Indexes
XML Schemas

Implementing Views
Overview of Views
Creating and Modifying a View
Considerations When Creating Views
Examining the Impact of Using SELECT * in Views
Restrictions for Modifying Data by Using Views
Using Views to Optimise Performance
Indexed Views
Partitioned Views

Implementing Stored Procedures
Overview of Stored Procedures
How Stored Procedures Are Executed by SQL Server
Creating and Using a Simple Stored Procedure
Parameterising Stored Procedures

Error Handling
Using @@ERROR
Using RAISERROR
Using TRY…CATCH

Implementing User Defined Functions
Overview of User Defined Functions (UDFs)
Creating and Modifying UDFs
Restrictions When Creating UDFs
Implementing Different Types of UDFs
Performance Consideration for Using User-Defined Functions
Controlling Execution Context

Implementing Triggers
Overview of Triggers
How Triggers Work
AFTER Triggers
INSTEAD OF Triggers
Overview of DDL Triggers

Optional Module: Querying Metadata
Different Categories of Data Grouping
Concepts Related to Different Categories of Data
Understanding Metadata
Querying Metadata by Using SQL Server Views
Querying Metadata by Using SQL Server Commands

Optional Module: Distributed Queries
Overview of Distributed Queries
Writing Ad Hoc Distributed Queries
Creating a Linked Server
Creating a Distributed Query
Using a Linked Server

Public Courses

On-Site Courses

Can't attend one of our public classes? Booking for multiple people?

All our courses are available on your site! Delivered for your staff, at your premises.

Contact us to find out more...