SQL Server Querying and Programming

Learn to query and programme using SQL Server
Overview

The course will allow delegates to understand and use Transact SQL for both querying and programming in a Microsoft SQL Server environment. This course uses SQL Server 2017 but is equally suitable for previous versions.

Course Code
SQL4QAP
Duration
4 Days
Delivery Style
Classroom
Course Type
Public or Private
Max Delegates
12
Available as Nutshell
No

Book your team training at your premises

  • Date
  • Venue
  • Cost
  • More info

Book your place

  • Date
  • Venue
  • Cost
  • Availability
  •  
  • 05 Feb 2019
  • VenueWorcester
  • Cost£1,596
  • Availability3
  • Book Now
  • 10 Jun 2019
  • VenueWorcester
  • Cost£1,596
  • AvailabilityAvailable
  • Book Now
Purpose of this course

This course covers important Transact SQL features such as data types, joins, sequences, subqueries, indexes and views. It includes creating stored procedures, user defined functions and triggers. Exercises are included to consolidate the skills taught.

Who is this course for

Database developers and support personnel who need to be able to query and programme SQL Server databases using Transact SQL. The course is also suitable for application developers (Visual Basic, C#, etc.) who need to query SQL Server databases.

You will learn how to
  • Write Transact SQL queries
  • Develop stored procedures
  • Use inner, outer and cross joins
  • Use many advanced Transact SQL syntaxes
  • Build indexes to improve query performance
  • Control locking and consistency through transactions
Benefits for your organisation

SQL Server is becoming an increasingly popular choice for enterprise wide applications. This course will give your organisation the ability to develop both Transact SQL queries and programmes. These queries and programmes will often be used as the basis of corporate reports.

Benefits for you as an individual

Many roles now require knowledge of SQL Server. This course will equip you with the skills to understand and use Transact SQL. You will have confidence to develop queries and programmes in Transact SQL.

Introduction to Databases

Database Basics
Data Types
Using SQL Server Management Studio
SQL Batches and the GO Command

Querying and Updating the Database

Selecting Data
Selecting Columns from a Table
Inner, Outer and Cross Joins
Overview of Graph Database
Union, Intersect and Except
Updating, Inserting and Deleting Data
Merging Data
Recording Changes

Tables

Creating & Altering Tables
Creating and Updating a Table with Transact SQL
Dropping Tables
Temporal Tables

Using Variables and Functions

Variables
Functions
Scalar Functions
Aggregate Functions
Splitting, Aggregating and Trimming Strings

Programming with Transact SQL

Stored Procedures
Creating Stored Procedures
Executing Stored Procedures
Controlling Flow of Code
User Defined Functions

Advanced Querying

Subqueries
Grouping and Summaries
Views
Common Table Expressions
Pivot Tables
Ranking Functions
Window Functions

Optimisation

Designing Indexes
Creating an Index
Creating an Index on a View
Viewing the Execution Plan
Putting the Performance Tools to Use

Advanced Programming with Transact SQL

Temporary Tables
Declaring a Variable with the Table Datatype
Dynamic SQL
Cursors

Introduction to SQL Server Support for XML

Introduction to XML
XML Casting
FOR XML
Overview of SQL Server Support for JSON

Data Integrity

Error Handling
Try... Catch Block
Transactions and Locks
Triggers
Triggers in Management Studio

Real World Development

Writing SQL for Reporting Tools
Troubleshooting
Other SQL Server Technologies
Best Practices

Prerequisites

Delegates must have a good understanding of relational databases. This can be obtained by attending our SQL Server Essentials course. Delegates must also have a basic knowledge of Microsoft Windows.

Need help with this course?

About the Lead Trainer

Stephen Morton

Stephen has 20 years’ experience in the IT industry. A member of the Institute of IT Training and British Computer Society with a degree in business computing systems, Stephen has a solid foundation upon which his ITIL® Expert qualification sits.