Course Outline

Table Structure

• Objects under the Customer Orders Database
• Creating a Table from the Object Explorer
• The Customer Table with Customer Id as Primary Key
• Click Refresh under Tables
• The Supplier Table
• The Orders Table
  • The Orders Table Foreign Key Relationships
• The Product Table
• The Order Details Table
  • The Orders Detail Table with Multi-Column Primary Key
  • The Order Details and Product Table Foreign Key Relationship
• The TABLES in the CUSTOMER ORDER Database
  • DATA for CUSTOMER Table
  • Data for SUPPLIER Table
  • Data for Product Table
  • Data for ORDERS table
  • Data for Order Details

Subqueries

• Understand Subqueries
• Using ALL, ANY, and IN
• Correlated Subqueries
• Using EXISTS

DCL

• What is DCL?
• DCL Commands
  • GRANT Command
  • REVOKE Command
• Privileges and Roles

TCL

• Basic Transactions
  • Properties of Transactions
  • Transaction Control
  • The COMMIT Command
  • The ROLLBACK Command
  • The SAVEPOINT Command
  • The SET TRANSACTION Command
• Rolling Back

Variables

• Declaring Variables
• SET versus SELECT
• Tricks with Variables and Row sets
• Global Variables

Testing Conditions

• IF/ELSE Statement
• Using CASE Where Possible

Looping

• Syntax of WHILE
• Objects
• Dropping Objects

Rules

• What are Rules
• Creating Syntax Rules

Stored Procedure Basics

• Creating Stored Procedures
• Three Ways to Execute
  • Using Object Explorer
  • Using the query window
  • Calling from another Application
• Stored Procedures with Parameters
• Pros and Cons of Stored Procedures
• System Stored Procedures

Parameters and Return Values

• Default Values
• Output Parameters
• Using RETURN

Cursors

• Understand Cursors
  • Types of Cursor
• Syntax of Fetching Rows

Triggers

• Understand the Trigger
• Two Types of Triggers

Avoiding Scalar Functions

• Scalar Functions
• Disadvantages
• Advantages

Temporary Tables and Table Variable

• Using Temporary Tables
• Creating Table Variables
• Pros and Cons of Each Approach

Table-Valued Functions

• In-line Table-Valued Functions
• Multi-Statement Table-Valued Functions
• Limitations of User-Defined Functions

Derived Tables and CTEs

• Using Derived Tables
• Common Table Expressions (CTEs)

Error-Handling

• Using TRY/CATCH
• System Error Functions
• Custom Error Messages
• The Obsolete @@Error Function

Debugging

• The SQL Server Debugger
  • Stepping Through Transact-SQL Code
  • Viewing Debugger Information