SQL Server 2005 Writing Queries - SQ5Q


Description

Course Outline
The SQL Server 2005 Writing Queries course from ATS is the ultimate course for database administrators. Beginning with object-oriented databases, you'll build upon basic query concepts, move through data manipulation language, master common table expressions and create stored procedures. By the end of this course, you'll understand how to write SQL Server 2005 queries efficiently and accurately.


Module 1

Section A: Relational Databases
· Introduction
· About Databases and SQL Server
· Data Types and Nulls
· Table Relationships

Section B: Starting with SQL
· Client/Server Architecture
· SQL Server Management Studio
· SQL Language
· Batches
· OSQL and SQLCMD

Section C: Introduction to Transact-SQL
· Statement Types
· Language Elements

Section D: SELECT Statements
· Basic Elements
· Column Aliases
· Four-Part Name

Section E: Operators and Expressions
· Arithmetic
· Operator Precedence
· Unary and Bitwise Operators
· String Concatenation
· Table Alias


Module 2
Section A: Filtering with Comparison Operators
· WHERE Clause
· Comparison Operators

Section B: Filtering with Logical Operators
· Logical Operators
· Operator Precedence

Section C: Filtering with Range of Values
· BETWEEN Operator
· BETWEEN Syntax

Section D: Filtering with Pattern Matching
· LIKE Clause
· Percent and Wildcard
· Other Wildcards

Section E: Writing Good WHERE Clauses
· What Is a SARG?
· Optimizer Plans
· Query Plans

Section F: Handling Missing Values
· Defining Null Values
· Null Basics
· Null Functions

Section G: Scalar Functions
· Function Types
· Date/Time Functions
· Metadata Functions


Module 3
Section A: Result Sets
· Sorting
· Eliminating Duplicates

Section B: Using Variables
· Local Variables
· Scalar Variables
· EXEC Command
· Value in SELECT
· Variable Examples

Section C: Control of Flow
· Control of Flow Commands
· IF and CASE Statements
· WHILE Statement
· Simple CASE Expression

Section D: CASE and Ranking Functions
· Converting Codes
· Searched CASE
· Ranking Functions

Section E: Aggregate Functions
· Defining Aggregates
· Aggregate Examples
· Nulls and Aggregates
· CHECKSUM

Section F: Grouping and Summarizing Data
· GROUP BY
· HAVING

Section G: Advanced Grouping and Summarizing
· ROLLUP and CUBE
· COMPUTE and COMPUTE BY
· PIVOT and UNPIVOT

Section H: Retrieving Data with INNER JOINS
· Defining INNER JOINS
· Understanding Joins
· Why Use Joins?

Section I: Retrieving Data with OUTER/CROSS JOINS
· Defining OUTER and CROSS JOINS
· Using OUTER JOINS
· Join Tables with RANGE


Module 4

Section A: Combining and Limiting Result Sets
· Unions
· UNION Operator UNION ALL
· TOPn
· INTERSECT and EXCEPT
· TABLESAMPLE

Section B: Basic Subqueries
· Subqueries
· Using a Subquery

Section C: Correlated Subqueries
· Using a Correlated Subquery
· EXISTS/NOT EXISTS Function

Section D: Common Table Expressions
· Non-Recursive CTEs
· Using Non-Recursive CTEs

Section E: Recursive (Queries) CTEs
· Traversing Hierarchies

Section F: Modifying Data with INSERT
· Defining an INSERT
· INSERT SELECT and INSERT EXEC

Section G: Modifying Data with SELECT INTO
· SELECT INTO

Section H: Modifying Data with DELETE
· Deleting Rows

Section I: Modifying Data with UPDATE
· Updating Rows


Module 5

Section A: OUTPUT Clause
· OUTPUT and INSERTED Clauses
· Output to Tables
· DELETED Clause

Section B: Querying Full-Text Indexes
· Full-Text Indexes
· CONTAINS
· Table Versions and FREETEXT

Section C: Using XML
· XML-Defined
· Storing XML Data
· Querying XML Data
· XML Indexes
· Selecting

Section D: Programming Objects for Data Retrieval
· Views
· Selecting from Views
· Updatable Views
· WITH CHECK OPTION

Section E: User Defined Functions
· Scalar UDFs
· Using Scalar UDFs

Section F: Inline and Multi-Statement Functions
· Table Value Functions
· Inline Table Values
· Multi-Statement Table Values

Section G: Stored Procedures
· Stored Procedures Defined
· Creating a Stored Procedure
· Stored Procedure Parameters

Section H: Advanced Query Techniques
· Using CONVERT
· Heterogeneous Queries


Price £ 980 (Bundle of 5)

Complementary Courses
SQL Server 2005 Maintaining a Database
SQL Server 2005 Implementing a Database

<<Back <<Contact Us