Customised SQL Training

 Closed In-House Course

Synopsis

This course is designed for developers wishing to exploit SQL in a Microsoft SQL Server environment. The course most often lasts for 2 days, with a reduced one-day version giving more of an overview and a 3 day version allowing coverage of more advanced aspects such as triggers and functions. Versions of SQL server from 2000 onwards can be supported. The course is highly practical including many exercises which can be adapted to follow your real-world requirements.

Publicly scheduled dates, locations, and prices

Outline Course Contents

Introduction

• Relational Databases
• IDs and Keys
• Primary and Foreign Keys
• Entity-Relationship Modelling - ERM
• T-SQL
• How SQL Server Works
• System Databases
• SQL Server Management Studio
• SQLCMD
• SQL Server Configuration Manager
• Other Tools

Tables and Data Types

• Naming Objects
• Designing a Table
• Creating a Database
• Database Files
• Storing Scripts
• Data Types - Integers
• Non-Integer Numeric Types
• Characters and Text
• Other Main Data Types
• Identity Columns
• Setting the Primary Key
• Nulls
• Altering a Table
• Dropping Columns and Tables
• User Defined Data Types

Selecting Data

• The Select Statement
• Selecting Columns
• Ordering
• Aliases
• Combining Columns
• Distinct
• Filtering Using Where
• Using Not, And and Or
• Matching using Like
• Between and In

Joins and Constraints

• Relationships
• Using Foreign Keys
• Inner Joins
• Outer Joins
• Disambiguation and abbreviation
• Foreign Key Constraints
• Database Diagrams
• Relationship Properties
• Cascading on Update or Delete
• Default, Unique and Check Constraints
• Disabling Constraints

Calculation

• Aggregate Functions
• Compute and Compute By
• Group By and Having
• Having and Where
• Dates and Times
• The Convert Function
• String Functions

Action Queries

• Insert Into
• Delete
• Update
• Updating Multiple Rows
• Sub-queries
• Nested Queries
• Insert...Select
• Select Into

Views and Triggers

• Creating and Using Views
• Views Involving Multiple Tables
• Inserting via a View
• Triggers
• The Trigger SQL
• Temporary Tables

Stored Procedures and Functions

• What is a Stored Procedure?
• Creating a Stored Procedure
• Calling the Procedure
• Passing Parameters
• Returning a Value
• Using an Output Parameter
• Scalar-Valued Functions
• Table-Valued Function
• A Function as a Parameterized View
• Multi-Statement Table-Valued Functions
• Error Handling
• T-SQL as a Programming Language

Indexing and Performance Tuning

• Index Structure
• Clustered and Non-Clustered Indexes
• Query Analysis
• Indexes and Primary Keys
• Covering a Query
• Performance Tuning
• Tracing
• The Database Engine Tuning Advisor

Transactions and Locks

• Using Transactions
• Savepoints
• Nested Transactions
• Transaction Isolation Levels

Integrating with .Net

• Advantages of .Net Integration
• A User-Defined Function in C#
• Uploading the Assembly
• Allowing CLR Integration
• Using a Command in a Stored Procedure
• A Table-Valued Function
• Using System Features and Permitting Resource Access
• Aggregate Functions
• C# User Defined Types

Using XML

• XML and SQL Server
• A Simple Query
• For XML Raw, Auto and Path
• Storing XML
• Selecting Data
• Using an XML Schema
• Creating a Table Using Typed XML Data
• Using .query
• HTTP Endpoints

Introducing Security

• Login Options
• Principals
• Server Roles
• Granting Privileges
• Users and Roles
• Schemas

Using Multiple Data Sources

• Using Multiple Servers
• Ad Hoc Queries
• OpenDataSource and OpenRecordset
• Adding a Linked Server