SQL Advanced

Duration: 2 days

Overview:

Building upon the skills mastered in Aspect Training's Introduction to SQL and Database Design course, Advanced SQL and Database

Programming teaches the development techniques needed to take full advantage of powerful and flexible data-driven applications. We'll turn from the prior course's concern with data modelling and database design to how to use data in extensive, enterprise-class databases. We will be using SQL Server as the basis for the examples and exercises.

Prerequisites:

Attendance of Aspect's SQL Introduction course or equivalent knowledge.

Topics:

1 - Basic SQL Recap

Writing Select Statements

Joining Tables

2 - Subqueries

Self-Contained Subqueries

Self-Contained Multivalued Subquery Examples

Correlated Subqueries

The EXISTS Predicate

3 - Table Expressions

Derived Tables

Common Table Expressions

Views

Inline Table-Valued Functions

The APPLY Operator

4 - Set Operators

The UNION Operator

The INTERSECT Operator

The EXCEPT Operator

5 - Data Modification

Inserting Data

The Identity Property and the Sequence Object

Deleting Data

Updating Data

Merging Data

The OUTPUT Clause

6 - Transactions and Concurrency

Transactions

Locks and Blocking

Isolation Levels

Deadlocks

7 - Introduction To Stored Procedures

Creating Stored Procedures

Altering Stored Procedures

Deleting A Stored Procedure

Passing In Parameterss

Outputting Values

The RETURN Statement

8 - Introduction To User Defined Functions

What is a User-Defined Function?

Usage of User-Defined Functions

Creating a User-Defined Scalar Function

Using a User-Defined Scalar Function

Modifying and Dropping User-Defined Functions