This page has 0 threads | Add post

Courseware

Advanced course manual

Here are the chapters included in this course manual (we use exactly the same manuals for our classroom and online SQL courses).

Click here to download a sample of the first two chapters of this Advanced manual, or review how to license our courseware.

Chapter 1 - THE MOVIES DATABASE▼

Section	Title	Subsections	Page
1.1	Our Example Database		7

Chapter 2 - STORED PROCEDURES▼

Section	Title	Subsections	Page
2.1	Overview	What is a Stored Procedure? Advantages and Disadvantages	8
2.2	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	9
2.3	Altering a Stored Procedure	Altering an Open Stored Procedure Altering a Procedure in a Database	11
2.4	Executing Stored Procedures	Refreshing your Local Cache Executing a Procedure Altering and Executing a Stored Procedure Together Selecting a Stored Procedure Name to Run It	12
2.5	Renaming and Deleting Stored Procedures	Renaming/Deleting a Procedure with the Menu Deleting a Procedure in Script Renaming a Procedure in Script	14
2.6	System Stored Procedures	Listing System Stored Procedures Useful System Stored Procedures	15
2.7	Getting Help on SQL	Context-Sensitive Help Tips on Googling	17

Chapter 3 - VARIABLES▼

Section	Title	Subsections	Page
3.1	Declaring Variables	Syntax for Declaring a Variable	18
3.2	Using Variables	Setting the Value of a Variable Showing the Values of Variables Scope of Variables Incrementing and Concatenating Variables The Importance of Casting	19
3.3	Using Variables with Subqueries	An Alternative Approach: Aggregate Functions	22
3.4	Storing Column Values in Variables	Storing a Single Row’s Values Accumulating Numbers Accumulating Text	23
3.5	Global Variables	Special Considerations when using @@ROWCOUNT	25

Chapter 4 - VARIABLE AND PARAMETER DATA TYPES▼

Section	Title	Subsections	Page
4.1	Numeric Data Types	Integer Variable/Parameter Types Decimal and Numeric Types	26
4.2	Character Data Types	Types of Character Storage Variable Length Data Types Fixed Length Data Types	27
4.3	Date/Time Data Types		28

Chapter 5 - STORED PROCEDURE PARAMETERS▼

Section	Title	Subsections	Page
5.1	Overview	Syntax of Parameters	29
5.2	Simple Parameters	Step 1 – Specifying the Parameters Step 2 – Coding the Parameters Step 3 – Referencing the Parameters Using Text Wildcards as Parameters	30
5.3	Running Procedures using Parameters	Positional Arguments Named Arguments Right-clicking to Execute a Procedure	32
5.4	Default Parameter Values	Setting Default Values to Null The Perfect Stored Procedure?	34
5.5	The RETURN Statement		36
5.6	Output Parameters		37

Chapter 6 - CONDITIONS AND LOOPS▼

Section	Title	Subsections	Page
6.1	IF Conditions	Simple Conditions Using BEGIN … END Using ELSE Nesting Conditions and Indentation Using CASE to Avoid IF	38
6.2	Looping using WHILE	The Syntax of WHILE Loops Breaking out of Loops	41

Chapter 7 - SCALAR FUNCTIONS▼

Section	Title	Subsections	Page
7.1	Overview	Syntax of a Scalar Function	43
7.2	Writing a Scalar Function	Specifying Input Parameters and Return Types Writing the Function Itself	44
7.3	Running a Function	Calling a Function on its Own Calling a Function within a SELECT Statement	45
7.4	Worked Examples	Example One – Returning a Person’s Status Example Two – Profitability Example Three – Categorisation (by Oscar Type)	46
7.5	Limitations of Functions	Assessing Function Speed	49

Chapter 8 - ERROR TRAPPING▼

Section	Title	Subsections	Page
8.1	About Errors		50
8.2	TRY / CATCH	Syntax of TRY / CATCH Example of a Simple Error Trap Nesting TRY Statements	51
8.3	Error Functions	T-SQL Error Functions Error Severity Levels Showing Errors within a TRY / CATCH Block	53
8.4	Customising Error Messages	Viewing the Full List of Error Messages Creating your Own Errors Customising your own Error Messages	55

Chapter 9 - DELETING DATA▼

Section	Title	Subsections	Page
9.1	Deleting (Dropping) Tables	Dropping a Table if it Exists Using Error Trapping to Check Existence Using SYS.OBJECTS and OBJECT_ID	57
9.2	Deleting Rows	Differences between TRUNCATE and DELETE FROM	59

Chapter 10 - UPDATING DATA▼

Section	Title	Subsections	Page
10.1	The UPDATE Command	An Example – Changing Genres for Films	60
10.2	Updating using JOIN	The Obvious Answer doesn’t Work The Correct Syntax	61

Chapter 11 - INSERTING DATA▼

Section	Title	Subsections	Page
11.1	Three Possible Ways to Insert		63
11.2	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	64
11.3	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	66
11.4	Inserting Single Rows	Syntax of INSERT INTO … VALUES Example Code to Insert a New Row Inserting a Batch of Single Rows	68
11.5	INSERT INTO – More Possibilities	Missing out Columns Using a Stored Procedure’s Output Outputting Inserted Rows Getting Inserted Row Numbers with @@IDENTITY	70

Chapter 12 - CREATING TABLES▼

Section	Title	Subsections	Page
12.1	Setting Up our Example	The Example Used in this Chapter Creating and Dropping Databases	72
12.2	Creating Tables		73
12.3	Setting Primary Keys	Creating a Primary Key when Creating Tables Creating a Primary Key Afterwards	74
12.4	Setting a Default Value for a Column		75
12.5	Preventing Null Values in a Column		76
12.6	Putting Checks or Constraints on a Column		77
12.7	Foreign Keys and Relationships	Our Example Foreign Keys Creating a Foreign Key Constraint	78
12.8	Two Reasons/Ways to Index a Column	Creating an Index to Speed Up Queries Enforcing Uniqueness with an Index	80
12.9	A Complete Example		81

Chapter 13 - TRANSACTIONS▼

Section	Title	Subsections	Page
13.1	The Concept	Syntax of a Transaction	82
13.2	A Simple Example		83
13.3	Case Study – Recategorising Films	The Problem The Algorithm The Procedure	84
13.4	Errors and Transactions		86

Chapter 14 - TEMPORARY TABLES▼

Section	Title	Subsections	Page
14.1	Overview of Temporary Tables	Local and Global Temporary Tables How Temporary Tables are Stored	87
14.2	Creating and Deleting Temporary Tables	Creating Temporary Tables Deleting Temporary Tables	88
14.3	Scope of Temporary Tables	Temporary Tables are Tied to the Queries Creating Them Visibility of Temporary Tables Scope of Temporary Tables in Stored Procedures	89
14.4	Case Study – Successful People	Step 1 – Busy Actors (Creating the Table) Step 2 – Busy Directors (Inserting Rows) Final Answer with Problems Solved	92

Chapter 15 - TABLE VARIABLES▼

Section	Title	Subsections	Page
15.1	About Table Variables		95
15.2	Case Study Revisited		96

Chapter 16 - COMPARING TABLE TYPES▼

Section	Title	Subsections	Page
16.1	Differences between Table Variables and Temporary Tables	Speed Limitations of Table Variables Limitations of Temporary Tables	97

Chapter 17 - TABLE-VALUED FUNCTIONS▼

Section	Title	Subsections	Page
17.1	The Two Types of Table-Valued Functions	Types of Table-Valued Functions Where to Find Them	99
17.2	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	100
17.3	Multi-Statement Table-Valued Functions	Syntax of an MSTVF Example of an MSTVF	102

Chapter 18 - CURSORS▼

Section	Title	Subsections	Page
18.1	About Cursors	Reasons to Use Cursors The Syntax of a Basic Cursor	104
18.2	Example of a Cursor		105

Chapter 19 - DEBUGGING IN SQL▼

Section	Title	Subsections	Page
19.1	Example Used		106
19.2	Debugging	Starting and Stopping Debugging Stepping Through Code Setting and Unsetting Breakpoints Viewing Variable Values by Hovering Viewing Variables in the Locals Window	107

Chapter 20 - DYNAMIC SQL▼

Section	Title	Subsections	Page
20.1	The EXEC Command and Dynamic SQL	Why not to Use Dynamic SQL	110
20.2	Example –Parameterising Row Selection		111

Chapter 21 - CTES AND DERIVED TABLES▼

Section	Title	Subsections	Page
21.1	Multi-Stage Queries		112
21.2	Derived Tables		113
21.3	Single CTEs (Common Table Expressions)	Syntax of Single CTEs The CTE for our Example	114
21.4	Multiple CTEs	Syntax of Multiple CTEs Example of a Multiple CTE	115

Chapter 22 - SUBQUERIES▼

Section	Title	Subsections	Page
22.1	Single-Value Subqueries	Example: Showing the Name of the Longest Film	117
22.2	ANY, ALL, IN and NOT IN		118
22.3	Correlated Subqueries	Correlated Subqueries: Definition and Example Alternatives to Correlated Subqueries Considering Speed Using EXISTS to Check whether Rows are Returned	119

Chapter 23 - PIVOTING DATA▼

Section	Title	Subsections	Page
23.1	Overview		121
23.2	The Two Stages of Creating a Pivot Query	Step 1 – Assembling the Data Step 2 – Pivoting the Assembled Data	122
23.3	Varying the Number of Row Fields	Pivot Queries with no Row Headings Pivot Queries with Multiple Row Headings	124
23.4	Queries Based on Pivot Queries		126
23.5	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	127

Chapter 24 - TRIGGERS▼

Section	Title	Subsections	Page
24.1	Overview of Triggers	Syntax of a Trigger	129
24.2	Working with Triggers	Creating a Trigger Viewing Triggers Enabling and Disabling Triggers Deleting Triggers	130
24.3	More Sophisticated Triggers	Tables Created by Triggers	132
24.4	A Case Study: Transactions in Triggers		133

If you book a place on one of our courses you will automatically get access to the relevant courseware manual online, and also have the option to receive a paper copy of it.

This page has 0 threads

Add a new post

Choose software

SQL course manuals