SQL courseware and training manuals

We've got courseware at two levels of details for SQL - basic courseware, and manuals covering advanced SQL programming topics:

You can see details of how to license our SQL courseware (including volume requirements) here.

Basic SQL courseware

Here is a list of all of our Basic SQL manual chapters (you can download the ones marked):

Chapter Sections Subsections
Designing Databases (5 pages) The Four Stages of Database Design Stage 1 – Deciding what to Include ; Stage 2 – Dividing Data into Tables ; Stage 3 – Choosing a Primary Key for each Table ; Stage 4 – Creating Relationships and a Database Diagram
Many-to-Many Relationships
SQL Server Management Studio (2 pages) Starting to Use Management Studio
Object Explorer Useful Start-up Options
Creating a Database and Tables (11 pages) Creating the Database The Files Created
Creating Tables
Setting an Identity Primary Key
Creating Columns Data Types Explained in this Chapter ; Other Data Types in SQL Server
Whole Numbers Integer Field Types ; Logical Field Types
Other Numerical Fields Decimal and Numeric Field Types ; Float and Real Data Types
Character Data Types Types of Character Storage ; Variable Length Data Types ; Fixed Length Data Types
Date/Time Data Types Entering Dates into a Table
Default and Null Values Allowing Nulls ; Default Values
Database Diagrams Creating a Database Diagram ; Creating Relationships ; Database Diagram Support Objects Error
Queries (9 pages) Basic SELECT Statements Where to Put your Commas
Creating Queries Starting a New Query ; Choosing the Right Database
Running Queries Parsing a Query ; Executing a Query ; Viewing Information on a Query’s Execution ; Cancelling a Running Query ; Redirecting Query Output
Dealing with Errors Displaying Line Numbers
Using IntelliSense Refreshing IntelliSense
Multiple SQL Commands
Saving, Opening and Closing Queries Saving Queries ; Opening Queries ; Closing Queries
Laying Out Queries (5 pages)

Download this chapter 		Using Case
Indentation and Word Wrap Changing Tab Settings ; Word Wrap
Comments Commenting Out Blocks of Code
Colours in SQL Changing the Default Colours
Auto-formatting SQL
The SELECT Statement (10 pages) SELECT Statement Syntax Mnemonic for Order of Commands
Qualified Tables and Columns Dragging Tables/Columns onto a Query ; Specifying the DBO Schema and Database
Table Aliases Reason 1 for Aliases – Easier to Refer to Field Names ; Reason 2 for Aliases – Joins ; Changing a Table Alias
Column Aliases Basic Column Aliases ; Other Ways to Create Column Aliases ; Aliases in WHERE and ORDER BY Clauses
Ordering Rows Simple Sorting ; Sorting by Multiple Columns
Miscellaneous SELECT Tricks Selecting All Columns Using * ; Selecting Unique Rows ; Showing Top and Bottom Rows ; Including Ties
Using UNION to Combine Results
Query Designer (6 pages) Starting Query Designer What Query Designer is and does ; Starting Query Designer
Using Query Designer Choosing Tables ; Adding/Removing Tables ; The Parts of Query Designer ; The Non-Existent Results Pane/Execute Button ; Working with Columns ; Finishing Work in Query Designer
Editing Generated SQL
Advanced Features Inner and Outer Joins ; Grouping
Criteria using WHERE (8 pages) The WHERE Clause Relational Operators
Criteria with Numbers Using Comparisons ; Finding Numbers in a Given Range
Criteria using Text Exact Matches ; Wildcard Matches using LIKE ; Using Special Characters with LIKE ; Ranges and Wildcards ; Using Relational Operators with Text ; Case Sensitivity
Criteria for Dates Using Dates in Criteria ; Using Dates with Wildcards
Combining Criteria
Nulls An Example of Testing for Nulls ; Entering Nulls into a Table
Exporting to Excel (5 pages) Copying and Pasting Copying Column Headers by Default
Exporting Data Step 1 – Getting the Query ; Step 2 – Starting to Export Data ; Step 3 – Choosing the Source ; Step 4 – Choosing the Destination ; Step 5 – Specifying the Data to Export ; Step 6 – Specifying how to Export ; Step 7 – Finishing the Export
Calculations (15 pages) Creating Calculated Columns Giving Calculated Columns Aliases ; Using Column Aliases in ORDER BY Clauses ; Column Aliases Don’t Work in WHERE Criteria
Using SQL Functions Typing an SQL Function ; Getting the Full List of Functions
Casting Data Types The Need for Casting ; The CAST Function ; The CONVERT Function
Numerical Calculations Mathematical Symbols and BODMAS ; The Modulus Operator (%) ; Mathematical Functions ; The Importance of Casting Numbers for Calculations
Text Calculations Concatenating Text using the CONCAT Function ; Concatenating Text Using the Plus Sign (with Data Conversion) ; Functions to Turn Numbers into Text ; Functions to Search for and Replace Text ; Functions for Extracting Text ; Changing the Case of Text ; Functions for Trimming Text ; Other Text Functions ; Worked Example – 1 ; Worked Example – 2 ; Worked Example – 3
Dealing with Nulls The ISNULL Function ; The COALESCE Function
The CASE Statement (4 pages) The Searched Case Expression Example: Film Bands ; Example: Film Era ; Using CASE in WHERE Criteria
The Simple Case Statement
Nested CASE Statements
Date Calculations (11 pages) How Dates and Times Work How SQL Server Stores Dates and Times ; Displaying Dates/Times ; GETDATE – the Current Date/Time ; Dates Prefer American Format
Formatting Dates using FORMAT The Available Codes ; Using the Culture Argument ; How Slow is the Format Function?
Formatting Dates using CONVERT
Parts of a Date: DATEPART and DATENAME Displaying a Day Suffix
Getting the Difference between Dates Subtracting One Date from Another ; The DATEDIFF Function
Calculating Ages Correctly Using DateDiff ; Dividing Someone’s Age in Days by 365 ; Getting the Exact Age
Adding Dates using DATEADD
Joins (12 pages) Overview of Joins What is a Join? ; The Types of Join
Understanding your Database How Relationships Work (Reminder)
Easy Joins, using Query Designer
Inner Joins The Syntax of an Inner Join ; Our Example – Joining the Film and Director Tables ; Joining more than One Table ; Variations on Inner Join Syntax ; Composite Joins ; Joining by Expressions
Outer Joins Outer Joins using Query Designer ; Outer Joins in SQL ; Left and Right Outer Joins ; Picking Out Unmatched Rows ; Full Outer Joins
Cross Joins A Practical Example of Cross Joins
Self-Joins
Summarising Data (11 pages) Simple Summarising Syntax of a Simple Summary
Counting Counting All of a Table’s Rows ; Counting Non-Null Columns ; Counting Unique Values
Grouping Why you Need GROUP BY ; The GROUP BY Clause ; Grouping by Multiple Columns ; Grouping by Expressions ; Grouping without Aggregating
Filtering Results using HAVING
Casting Data for (eg) Averages
Dealing with Nulls The Default Treatment of Nulls ; Forcing SQL to Include Nulls
Additional Options when Grouping Using ALL to Show Missing Rows ; Using CUBE to Show All Combinations ; Using GROUPING to Show Levels
Views (10 pages) Why Views are Useful Use 1: Pre-Joining Tables ; Use 2: Virtually Renaming Columns
Views using the Designer Starting the Designer ; Choosing Columns ; Sorting and Filtering ; Adding Grouping ; Executing a View ; Saving and Closing Views ; Seeing your View in Object Explorer ; Running a View ; Changing a View
Scripting Views Creating a New View ; Changing an Open View in Script ; Changing a View’s Script from Object Explorer
Switching between the Designer and Scripting
CTEs and Derived Tables (5 pages) Multi-Stage Queries
Derived Tables
Single CTEs (Common Table Expressions) Syntax of Single CTEs ; The CTE for our Example
Multiple CTEs Syntax of Multiple CTEs ; Example of a Multiple CTE
Subqueries (4 pages) Single-Value Subqueries Example: Showing the Name of the Longest Film
ANY, ALL, IN and NOT IN
Correlated Subqueries Correlated Subqueries: Definition and Example ; Alternatives to Correlated Subqueries ; Considering Speed ; Using EXISTS to Check whether Rows are Returned
Ranking and Percentiles (3 pages)

Download this chapter 		Ranking and Numbering Simple Row Numbering
Leading and Lagging Example of LAG: Actors Born One Week Apart
Percentiles Percentile Rankings

Advanced SQL courseware

Here is a list of all of our Advanced SQL manual chapters (you can download the ones marked):

Chapter Sections Subsections
The Movies Database (1 page) Our Example Database
Stored Procedures (10 pages) Overview What is a Stored Procedure? ; Advantages and Disadvantages
Creating Stored Procedures Typing in a Stored Procedure ; Creating a Stored Procedure using a Template ; Executing the Query to Create your Stored Procedure ; Viewing your Stored Procedure
Altering a Stored Procedure Altering an Open Stored Procedure ; Altering a Procedure in a Database
Executing Stored Procedures Refreshing your Local Cache ; Altering and Executing a Stored Procedure Together ; Selecting a Stored Procedure Name to Run It
Renaming and Deleting Stored Procedures Renaming/Deleting a Procedure with the Menu ; Deleting a Procedure in Script ; Renaming a Procedure in Script
System Stored Procedures Listing System Stored Procedures ; Useful System Stored Procedures
Getting Help on SQL Context-Sensitive Help ; Tips on Googling
Variables (8 pages)

Download this chapter 		Declaring Variables Syntax for Declaring a Variable
Using Variables Setting the Value of a Variable ; Showing the Values of Variables ; Scope of Variables ; Incrementing and Concatenating Variables ; The Importance of Casting
Using Variables with Subqueries An Alternative Approach: Aggregate Functions
Storing Column Values in Variables Storing a Single Row’s Values ; Accumulating Numbers ; Accumulating Text
Global Variables Special Considerations when using @@ROWCOUNT
Variable and Parameter Data Types (3 pages) Numeric Data Types Integer Variable/Parameter Types ; Decimal and Numeric Types
Character Data Types Types of Character Storage ; Variable Length Data Types ; Fixed Length Data Types
Date/Time Data Types
Stored Procedure Parameters (9 pages) Overview Syntax of Parameters
Simple Parameters Step 1 – Specifying the Parameters ; Step 2 – Coding the Parameters ; Step 3 – Referencing the Parameters ; Using Text Wildcards as Parameters
Running Procedures using Parameters Positional Arguments ; Named Arguments ; Right-clicking to Execute a Procedure
Default Parameter Values Setting Default Values to Null ; The Perfect Stored Procedure?
The RETURN Statement
Output Parameters
Conditions and Loops (5 pages) IF Conditions Simple Conditions ; Using BEGIN … END ; Using ELSE ; Nesting Conditions and Indentation ; Using CASE to Avoid IF
Looping using WHILE The Syntax of WHILE Loops ; Breaking out of Loops
Scalar Functions (7 pages) Overview Syntax of a Scalar Function
Writing a Scalar Function Specifying Input Parameters and Return Types ; Writing the Function Itself
Running a Function Calling a Function on its Own ; Calling a Function within a SELECT Statement
Worked Examples Example One – Returning a Person’s Status ; Example Two – Profitability ; Example Three – Categorisation (by Oscar Type)
Limitations of Functions Assessing Function Speed
Error Handling (7 pages) About Errors
TRY / CATCH Syntax of TRY / CATCH ; Example of a Simple Error Trap ; Nesting TRY Statements
Error Functions T-SQL Error Functions ; Error Severity Levels ; Showing Errors within a TRY / CATCH Block
Customising Error Messages Viewing the Full List of Error Messages ; Creating your Own Errors ; Customising your own Error Messages
Deleting Data (3 pages) Deleting (Dropping) Tables Dropping a Table if it Exists ; Using Error Trapping to Check Existence ; Using SYS.OBJECTS and OBJECT_ID
Deleting Rows Differences between TRUNCATE and DELETE FROM
Updating Data (3 pages) The UPDATE Command An Example – Changing Genres for Films
Updating using JOIN The Obvious Answer doesn’t Work ; The Correct Syntax
Inserting Data (9 pages) Three Possible Ways to Insert
Creating Tables from Existing Data (SELECT INTO) Step 1 – Getting the Data for your New Table ; Step 2 – Making a New Table ; Step 3 – Checking the Table Created
Inserting Multiple Rows into an Existing Table Step 1 – Understanding the Syntax ; Step 2 – Working out what to do ; Step 3 – Mapping the Columns ; Step 4 – Creating the Query
Inserting Single Rows Syntax of INSERT INTO … VALUES ; Example Code to Insert a New Row ; Inserting a Batch of Single Rows
INSERT INTO – More Possibilities Missing out Columns ; Using a Stored Procedure’s Output ; Outputting Inserted Rows ; Getting Inserted Row Numbers with @@IDENTITY
Creating Tables (10 pages) Setting Up our Example The Example Used in this Chapter ; Creating and Dropping Databases
Creating Tables
Setting Primary Keys Creating a Primary Key when Creating Tables ; Creating a Primary Key Afterwards
Setting a Default Value for a Column
Preventing Null Values in a Column
Putting Checks or Constraints on a Column
Foreign Keys and Relationships Our Example ; Foreign Keys ; Creating a Foreign Key Constraint
Two Reasons/Ways to Index a Column Creating an Index to Speed Up Queries ; Enforcing Uniqueness with an Index
A Complete Example
Transactions (5 pages) The Concept Syntax of a Transaction
A Simple Example
Case Study – Recategorising Films The Problem ; The Algorithm ; The Procedure
Errors and Transactions
Temporary Tables (8 pages) Overview of Temporary Tables Local and Global Temporary Tables ; How Temporary Tables are Stored
Creating and Deleting Temporary Tables Creating Temporary Tables ; Deleting Temporary Tables
Scope of Temporary Tables Temporary Tables are Tied to the Queries Creating Them ; Visibility of Temporary Tables ; Scope of Temporary Tables in Stored Procedures
Case Study – Successful People Step 1 – Busy Actors (Creating the Table) ; Step 2 – Busy Directors (Inserting Rows) ; Final Answer with Problems Solved
Table Variables (2 pages) About Table Variables
Case Study Revisited
Comparing Table Types (2 pages) Differences between Table Variables and Temporary Tables Speed ; Limitations of Table Variables ; Limitations of Temporary Tables
Table-Valued Functions (5 pages) The Two Types of Table-Valued Functions Types of Table-Valued Functions ; Where to Find Them
In-line Table-Valued Functions Syntax of In-Line TVFs ; Where Stored Procedures Fall Short ; The In-Line TVF Solution ; Joins with Table-Valued Functions
Multi-Statement Table-Valued Functions Syntax of an MSTVF ; Example of an MSTVF
Cursors (2 pages) About Cursors Reasons to Use Cursors ; The Syntax of a Basic Cursor
Example of a Cursor
Debugging in SQL (4 pages) Example Used
Debugging Starting and Stopping Debugging ; Stepping Through Code ; Setting and Unsetting Breakpoints ; Viewing Variable Values by Hovering ; Viewing Variables in the Locals Window
Dynamic SQL (2 pages)

Download this chapter 		The EXEC Command and Dynamic SQL Why not to Use Dynamic SQL
Example –Parameterising Row Selection
Pivoting Data (8 pages) Overview
The Two Stages of Creating a Pivot Query Step 1 – Assembling the Data ; Step 2 – Pivoting the Assembled Data
Varying the Number of Row Fields Pivot Queries with no Row Headings ; Pivot Queries with Multiple Row Headings
Queries Based on Pivot Queries
Getting and Using Dynamic Columns Step 1 – Get a Comma-Delimited List ; Step 2 – Build up the SQL Statement ; Step 3 – Test the SQL ; Step 4 – Execute the SQL
Triggers (5 pages) Overview of Triggers Syntax of a Trigger
Working with Triggers Creating a Trigger ; Viewing Triggers ; Enabling and Disabling Triggers ; Deleting Triggers
More Sophisticated Triggers Tables Created by Triggers
A Case Study: Transactions in Triggers

Notes:

  1. Since Wise Owl print courseware double-sided to save trees, the number of physical pages in each chapter is roughly half of the number of sides quoted.
  2. Downloads are provided as examples only, and should not be used or distributed in any way or form without the prior written permission of Wise Owl.
This page has 0 threads Add post