Choose software▼
- .NET manuals
- Access manuals
- Business Intelligence manuals
- C# manuals
- Excel manuals
- Microsoft Office manuals
- Power Apps manuals
- Power Automate manuals
- Power BI manuals
- Programming manuals
- Python manuals
- Report Builder manuals
- SQL manuals
- SSAS manuals
- SSIS manuals
- SSRS manuals
- VBA manuals
- Visual Basic manuals
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 (18 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 ; Data Type Precedence ; The CAST Function ; The CONVERT Function | |
Numerical Calculations | Mathematical Symbols and BODMAS ; The Modulus Operator (%) ; Mathematical Functions ; The Importance of Casting Numbers for Calculations ; A Short-Cut to Forcing the Right Number Type | |
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 | |
Testing Conditions using IIF | ||
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:
- 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.
- 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