Classes :: class description :

SQL Server

Overview

This course provides delegates with technical skills required to implement a database solution with the Microsoft SQL Server relational database management. The system course provides extensive or hands on practical experience covering all the main topics including creating databases, creating indexes, Performing advanced queries and summarizing data.

Prerequisites

Students should be familiar with Windows NT.

Outline

Introduction 

• SQL language components

• Databases and database models

• Overview and history of SQL

Getting started  

• SQL objects and tools

• Data definition language

• Data control language

• Data manipulation language

Data manipulation language - Simple SELECT  

• SELECT syntax

• Result set

• The ORDER BY clause

Data types -- Numeric, character, temporal

• Exact vs. approximate numeric

• Numeric rounding and truncation

• Math functions, precedence, and extensions

• Numeric conversions

• Character data considerations

• String functions and manipulation

• String conversions

• Datetime conversions and styles

• Date functions and date parts

• General conversion rules

Expressions  

• Types of expressions -- Numeric, character, date

• SELECT statements with expressions

• Constants and columns

• Naming SELECT columns and aliases

• Conditional and aggregate expressions

Row filters  

• SELECT DISTINCT

• The WHERE clause

• Logical operators and logical predicates

• Boolean operators and logic

Joins -- Relational operators 

• Database design and normalization

• Primary and foreign keys

• Joining tables

Subqueries

• Query within a query

• Subqueries with IN and NOT IN

• Scalar and correlated subqueries

• The EXISTS predicate

Grouping data  

• The GROUP BY clause

• Filtering groups -- The HAVING clause

Advanced select  

• Outer joins --  Left, right, full

• Importance of table order

• Generalized placement of SELECT

• Advanced queries

• Thinking in sets

• Rankings

• Cross tabs

Programming constructs (Transact SQL)  

• Statement batches

• Stored procedures

• Global and local variables

• Temporary tables

• Cursors

• Flow of control - BEGIN ... END, WHILE, IF