Tag: Transact SQL

Duration

14 hours (usually 2 days including breaks)

Overview

Delegates will gain an understanding of the basic principles of Structured Query Language as well as being able to do each of the following:

• Construct queries to extract and filter data from an SQL Server database
• Create summarised results
• Change, derive and format data to suit the required output
• Change data and maintain database components and definitions

This course is for anybody who needs information from a Microsoft SQL Server database. It is suitable for either system developers or people from other areas who need to use SQL to extract and analyse data.

Course Outline

Basics

• Selection of all columns/fields
• Selection of certain columns/fields
• Use of distinct/unique
• Selection of certain rows/records
• Selection of values in a range
• Selection of values matching a pattern mask
• Selection of values within a list
• Treatment of null values
• How to sort and order data
• Selection of calculated and derived values
• How to control column headings in query results
• How to send query results to external files

Joining Tables

• Principles of joining tables:
  • Use of cartesian join
  • Use of inner join
  • Use of non-equi join
  • Use of outer join

Joining Queries

• Union operator
• Intersect operator
• Except operator

Simple Functions

• Conversion functions
• Date functions
• Number functions
• Text functions
• Group/summary/aggregate functions

Sub-Queries

• Principles of sub-queries
• How to filter rows from main query
• Use of nested sub-query
• Use of multi-column sub-query
• Use of correlated sub-query
• Use of sub-query as an inline view and common table expression
• Use of sub-query as a column in main query

Case Statements

• Principles of case statements
• Use of case statement to derive column values
• Use of nested case statements
• Use of case statements to produce pivot tables
• Use of case statement with sub-queries

Data Manipulation

• How to insert values into a table
• How to copy values between tables
• How to update values
• How to delete records
• How to change data via views
• Use of transactions
• How to lock rows and tables

Data Definition

• Principles of a relational database and data normalisation
• Use of primary key and foreign key relationships and constraints
• How to create tables
• How to alter tables
• How to create views
• Use of synonyms
• How to remove tables and views

Duration

7 hours (usually 1 day including breaks)

Overview

Delegates will gain an understanding of some of the more advanced features of Transact SQL as well as being able to do each of the following:

• Use queries to return complex result sets
• Manage database objects to aid query performance
• Tune queries to perform more efficiently

This course is for anyone who currently uses Transact SQL to extract data from a Microsoft SQL Server database and wishes to expand their knowledge particularly in the areas of data analysis and improving query speed.

Course Outline

Analytical Functions

• Use of advanced summary functions
• Use of hierarchical queries
• Use of analytical summary functions, e.g. moving averages, running totals
• Use of ranking functions

Useful Database Objects

• Principles of using indexes
• How to create and maintain an index
• Use of clustered tables
• Use of partitioned tables
• Use of metadata in the master database

Query Performance Tracing

• Principles of query execution and optimisation
• Use of Execution Plan
• Use of table & index statistics
• Use of hints

Basic Data Warehouse Techniques

• Use of Indexed Views
• Use of Dimension & fact tables
• Use of Star & Snowflake designs