Transact SQL Basic

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.

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