This page has 0 threads | Add post

Courseware

Fast-track 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 Fast-track manual, or review how to license our courseware.

Chapter 1 - DESIGNING DATABASES▼

Section	Title	Subsections	Page
1.1	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	11
1.2	Many-to-Many Relationships		15

Chapter 2 - SQL SERVER MANAGEMENT STUDIO▼

Section	Title	Subsections	Page
2.1	Starting to Use Management Studio		16
2.2	Object Explorer	Useful Start-up Options	17

Chapter 3 - CREATING A DATABASE AND TABLES▼

Section	Title	Subsections	Page
3.1	Creating the Database	The Files Created	18
3.2	Creating Tables		19
3.3	Setting an Identity Primary Key		20
3.4	Creating Columns	Data Types Explained in this Chapter Other Data Types in SQL Server	21
3.5	Whole Numbers	Integer Field Types Logical Field Types	22
3.6	Other Numerical Fields	Decimal and Numeric Field Types Float and Real Data Types	23
3.7	Character Data Types	Types of Character Storage Variable Length Data Types Fixed Length Data Types	24
3.8	Date/Time Data Types	Entering Dates into a Table	25
3.9	Default and Null Values	Allowing Nulls Default Values	26
3.10	Database Diagrams	Creating a Database Diagram Creating Relationships Database Diagram Support Objects Error	27

Chapter 4 - QUERIES▼

Section	Title	Subsections	Page
4.1	Basic SELECT Statements	Where to Put your Commas	29
4.2	Creating Queries	Starting a New Query Choosing the Right Database	30
4.3	Running Queries	Parsing a Query Executing a Query Viewing Information on a Query’s Execution Cancelling a Running Query Redirecting Query Output	31
4.4	Dealing with Errors	Displaying Line Numbers	33
4.5	Using IntelliSense	Refreshing IntelliSense	34
4.6	Multiple SQL Commands		35
4.7	Saving, Opening and Closing Queries	Saving Queries Opening Queries Closing Queries	36

Chapter 5 - LAYING OUT QUERIES▼

Section	Title	Subsections	Page
5.1	Using Case		38
5.2	Indentation and Word Wrap	Changing Tab Settings Word Wrap	39
5.3	Comments	Commenting Out Blocks of Code	40
5.4	Colours in SQL	Changing the Default Colours	41
5.5	Auto-formatting SQL		42

Chapter 6 - THE SELECT STATEMENT▼

Section	Title	Subsections	Page
6.1	SELECT Statement Syntax	Mnemonic for Order of Commands	43
6.2	Qualified Tables and Columns	Dragging Tables/Columns onto a Query Specifying the DBO Schema and Database	44
6.3	Table Aliases	Reason 1 for Aliases – Easier to Refer to Field Names Reason 2 for Aliases – Joins Changing a Table Alias	44
6.4	Column Aliases	Basic Column Aliases Other Ways to Create Column Aliases Aliases in WHERE and ORDER BY Clauses	47
6.5	Ordering Rows	Simple Sorting Sorting by Multiple Columns	49
6.6	Miscellaneous SELECT Tricks	Selecting All Columns Using * Selecting Unique Rows Showing Top and Bottom Rows Including Ties	50
6.7	Using UNION to Combine Results		52

Chapter 7 - QUERY DESIGNER▼

Section	Title	Subsections	Page
7.1	Starting Query Designer	What Query Designer is and does Starting Query Designer	53
7.2	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	54
7.3	Editing Generated SQL		57
7.4	Advanced Features	Inner and Outer Joins Grouping	58

Chapter 8 - CRITERIA USING WHERE▼

Section	Title	Subsections	Page
8.1	The WHERE Clause	Relational Operators	59
8.2	Criteria with Numbers	Using Comparisons Finding Numbers in a Given Range	60
8.3	Criteria using Text	Exact Matches Wildcard Matches using LIKE Using Special Characters with LIKE Ranges and Wildcards Using Relational Operators with Text Case Sensitivity	61
8.4	Criteria for Dates	Using Dates in Criteria Using Dates with Wildcards	64
8.5	Combining Criteria		65
8.6	Nulls	An Example of Testing for Nulls Entering Nulls into a Table	66

Chapter 9 - EXPORTING TO EXCEL▼

Section	Title	Subsections	Page
9.1	Copying and Pasting	Copying Column Headers by Default	67
9.2	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	68

Chapter 10 - CALCULATIONS▼

Section	Title	Subsections	Page
10.1	Creating Calculated Columns	Giving Calculated Columns Aliases Using Column Aliases in ORDER BY Clauses Column Aliases Don’t Work in WHERE Criteria	72
10.2	Using SQL Functions	Typing an SQL Function Getting the Full List of Functions	74
10.3	Casting Data Types	The Need for Casting Data Type Precedence The CAST Function The CONVERT Function	75
10.4	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	78
10.5	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 3	81
10.6	Dealing with Nulls	The ISNULL Function The COALESCE Function	87
10.7	Testing Conditions using IIF		89

Chapter 11 - THE CASE EXPRESSION▼

Section	Title	Subsections	Page
11.1	The Searched Case Expression	Example: Film Bands Example: Film Era Using CASE in WHERE Criteria	90
11.2	The Simple Case Statement		92
11.3	Nested CASE Statements		93

Chapter 12 - DATE CALCULATIONS▼

Section	Title	Subsections	Page
12.1	How Dates and Times Work	How SQL Server Stores Dates and Times Displaying Dates/Times GETDATE – the Current Date/Time Dates Prefer American Format	94
12.2	Formatting Dates using FORMAT	The Available Codes Using the Culture Argument How Slow is the Format Function?	96
12.3	Formatting Dates using CONVERT		98
12.4	Parts of a Date: DATEPART and DATENAME	Displaying a Day Suffix	99
12.5	Getting the Difference between Dates	Subtracting One Date from Another The DATEDIFF Function	100
12.6	Calculating Ages Correctly	Using DateDiff Dividing Someone’s Age in Days by 365 Getting the Exact Age	102
12.7	Adding Dates using DATEADD		104

Chapter 13 - JOINS▼

Section	Title	Subsections	Page
13.1	Overview of Joins	What is a Join? The Types of Join	105
13.2	Understanding your Database	How Relationships Work (Reminder)	106
13.3	Easy Joins, using Query Designer		107
13.4	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	108
13.5	Outer Joins	Outer Joins using Query Designer Outer Joins in SQL Left and Right Outer Joins Picking Out Unmatched Rows Full Outer Joins	112
13.6	Cross Joins	A Practical Example of Cross Joins	115
13.7	Self-Joins		116

Chapter 14 - SUMMARISING DATA▼

Section	Title	Subsections	Page
14.1	Simple Summarising	Syntax of a Simple Summary	117
14.2	Counting	Counting All of a Table’s Rows Counting Non-Null Columns Counting Unique Values	118
14.3	Grouping	Why you Need GROUP BY The GROUP BY Clause Grouping by Multiple Columns Grouping by Expressions Grouping without Aggregating	120
14.4	Filtering Results using HAVING		123
14.5	Casting Data for (eg) Averages		124
14.6	Dealing with Nulls	The Default Treatment of Nulls Forcing SQL to Include Nulls	125
14.7	Additional Options when Grouping	Using ALL to Show Missing Rows Using CUBE to Show All Combinations Using GROUPING to Show Levels	126

Chapter 15 - VIEWS▼

Section	Title	Subsections	Page
15.1	Why Views are Useful	Use 1: Pre-Joining Tables Use 2: Virtually Renaming Columns	128
15.2	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	130
15.3	Scripting Views	Creating a New View Changing an Open View in Script Changing a View’s Script from Object Explorer	135
15.4	Switching between the Designer and Scripting		137

Chapter 16 - CTES AND DERIVED TABLES▼

Section	Title	Subsections	Page
16.1	Multi-Stage Queries		138
16.2	Derived Tables		139
16.3	Single CTEs (Common Table Expressions)	Syntax of Single CTEs The CTE for our Example	140
16.4	Multiple CTEs	Syntax of Multiple CTEs Example of a Multiple CTE	141

Chapter 17 - SUBQUERIES▼

Section	Title	Subsections	Page
17.1	Single-Value Subqueries	Example: Showing the Name of the Longest Film	143
17.2	ANY, ALL, IN and NOT IN		144
17.3	Correlated Subqueries	Correlated Subqueries: Definition and Example Alternatives to Correlated Subqueries Considering Speed Using EXISTS to Check whether Rows are Returned	145

Chapter 18 - RANKING AND PERCENTILES▼

Section	Title	Subsections	Page
18.1	Ranking and Numbering	Simple Row Numbering	147
18.2	Leading and Lagging	Example of LAG: Actors Born One Week Apart	148
18.3	Percentiles	Percentile Rankings	149

Chapter 19 - STORED PROCEDURES▼

Section	Title	Subsections	Page
19.1	Overview	What is a Stored Procedure? Advantages and Disadvantages	150
19.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	151
19.3	Altering a Stored Procedure	Altering an Open Stored Procedure Altering a Procedure in a Database	153
19.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	154
19.5	Renaming and Deleting Stored Procedures	Renaming/Deleting a Procedure with the Menu Deleting a Procedure in Script Renaming a Procedure in Script	156
19.6	System Stored Procedures	Listing System Stored Procedures Useful System Stored Procedures	157
19.7	Getting Help on SQL	Context-Sensitive Help Tips on Googling	159

Chapter 20 - VARIABLES▼

Section	Title	Subsections	Page
20.1	Declaring Variables	Syntax for Declaring a Variable	160
20.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	161
20.3	Using Variables with Subqueries	An Alternative Approach: Aggregate Functions	164
20.4	Storing Column Values in Variables	Storing a Single Row’s Values Accumulating Numbers Accumulating Text	165
20.5	Global Variables	Special Considerations when using @@ROWCOUNT	167

Chapter 21 - VARIABLE AND PARAMETER DATA TYPES▼

Section	Title	Subsections	Page
21.1	Numeric Data Types	Integer Variable/Parameter Types Decimal and Numeric Types	168
21.2	Character Data Types	Types of Character Storage Variable Length Data Types Fixed Length Data Types	169
21.3	Date/Time Data Types		170

Chapter 22 - STORED PROCEDURE PARAMETERS▼

Section	Title	Subsections	Page
22.1	Overview	Syntax of Parameters	171
22.2	Simple Parameters	Step 1 – Specifying the Parameters Step 2 – Coding the Parameters Step 3 – Referencing the Parameters Using Text Wildcards as Parameters	172
22.3	Running Procedures using Parameters	Positional Arguments Named Arguments Right-clicking to Execute a Procedure	174
22.4	Default Parameter Values	Setting Default Values to Null The Perfect Stored Procedure?	176
22.5	The RETURN Statement		178
22.6	Output Parameters		179

Chapter 23 - CONDITIONS AND LOOPS▼

Section	Title	Subsections	Page
23.1	IF Conditions	Simple Conditions Using BEGIN … END Using ELSE Nesting Conditions and Indentation Using CASE to Avoid IF	180
23.2	Looping using WHILE	The Syntax of WHILE Loops Breaking out of Loops	183

Chapter 24 - SCALAR FUNCTIONS▼

Section	Title	Subsections	Page
24.1	Overview	Syntax of a Scalar Function	185
24.2	Writing a Scalar Function	Specifying Input Parameters and Return Types Writing the Function Itself	186
24.3	Running a Function	Calling a Function on its Own Calling a Function within a SELECT Statement	187
24.4	Worked Examples	Example One – Returning a Person’s Status Example Two – Profitability Example Three – Categorisation (by Oscar Type)	188
24.5	Limitations of Functions	Assessing Function Speed	191

Chapter 25 - ERROR TRAPPING▼

Section	Title	Subsections	Page
25.1	About Errors		192
25.2	TRY / CATCH	Syntax of TRY / CATCH Example of a Simple Error Trap Nesting TRY Statements	193
25.3	Error Functions	T-SQL Error Functions Error Severity Levels Showing Errors within a TRY / CATCH Block	195
25.4	Customising Error Messages	Viewing the Full List of Error Messages Creating your Own Errors Customising your own Error Messages	197

Chapter 26 - DELETING DATA▼

Section	Title	Subsections	Page
26.1	Deleting (Dropping) Tables	Dropping a Table if it Exists Using Error Trapping to Check Existence Using SYS.OBJECTS and OBJECT_ID	199
26.2	Deleting Rows	Differences between TRUNCATE and DELETE FROM	201

Chapter 27 - UPDATING DATA▼

Section	Title	Subsections	Page
27.1	The UPDATE Command	An Example – Changing Genres for Films	202
27.2	Updating using JOIN	The Obvious Answer doesn’t Work The Correct Syntax	203

Chapter 28 - INSERTING DATA▼

Section	Title	Subsections	Page
28.1	Three Possible Ways to Insert		205
28.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	206
28.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	208
28.4	Inserting Single Rows	Syntax of INSERT INTO … VALUES Example Code to Insert a New Row Inserting a Batch of Single Rows	210
28.5	INSERT INTO – More Possibilities	Missing out Columns Using a Stored Procedure’s Output Outputting Inserted Rows Getting Inserted Row Numbers with @@IDENTITY	212

Chapter 29 - CREATING TABLES▼

Section	Title	Subsections	Page
29.1	Setting Up our Example	The Example Used in this Chapter Creating and Dropping Databases	214
29.2	Creating Tables		215
29.3	Setting Primary Keys	Creating a Primary Key when Creating Tables Creating a Primary Key Afterwards	216
29.4	Setting a Default Value for a Column		217
29.5	Preventing Null Values in a Column		218
29.6	Putting Checks or Constraints on a Column		219
29.7	Foreign Keys and Relationships	Our Example Foreign Keys Creating a Foreign Key Constraint	220
29.8	Two Reasons/Ways to Index a Column	Creating an Index to Speed Up Queries Enforcing Uniqueness with an Index	222
29.9	A Complete Example		223

Chapter 30 - TRANSACTIONS▼

Section	Title	Subsections	Page
30.1	The Concept	Syntax of a Transaction	224
30.2	A Simple Example		225
30.3	Case Study – Recategorising Films	The Problem The Algorithm The Procedure	226
30.4	Errors and Transactions		228

Chapter 31 - TEMPORARY TABLES▼

Section	Title	Subsections	Page
31.1	Overview of Temporary Tables	Local and Global Temporary Tables How Temporary Tables are Stored	229
31.2	Creating and Deleting Temporary Tables	Creating Temporary Tables Deleting Temporary Tables	230
31.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	231
31.4	Case Study – Successful People	Step 1 – Busy Actors (Creating the Table) Step 2 – Busy Directors (Inserting Rows) Final Answer with Problems Solved	234

Chapter 32 - TABLE VARIABLES▼

Section	Title	Subsections	Page
32.1	About Table Variables		237
32.2	Case Study Revisited		238

Chapter 33 - COMPARING TABLE TYPES▼

Section	Title	Subsections	Page
33.1	Differences between Table Variables and Temporary Tables	Speed Limitations of Table Variables Limitations of Temporary Tables	239

Chapter 34 - TABLE-VALUED FUNCTIONS▼

Section	Title	Subsections	Page
34.1	The Two Types of Table-Valued Functions	Types of Table-Valued Functions Where to Find Them	241
34.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	242
34.3	Multi-Statement Table-Valued Functions	Syntax of an MSTVF Example of an MSTVF	244

Chapter 35 - DYNAMIC SQL▼

Section	Title	Subsections	Page
35.1	The EXEC Command and Dynamic SQL	Why not to Use Dynamic SQL	246
35.2	Example –Parameterising Row Selection		247

Chapter 36 - PIVOTING DATA▼

Section	Title	Subsections	Page
36.1	Overview		248
36.2	The Two Stages of Creating a Pivot Query	Step 1 – Assembling the Data Step 2 – Pivoting the Assembled Data	249
36.3	Varying the Number of Row Fields	Pivot Queries with no Row Headings Pivot Queries with Multiple Row Headings	251
36.4	Queries Based on Pivot Queries		253
36.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	254

Chapter 37 - TRIGGERS▼

Section	Title	Subsections	Page
37.1	Overview of Triggers	Syntax of a Trigger	256
37.2	Working with Triggers	Creating a Trigger Viewing Triggers Enabling and Disabling Triggers Deleting Triggers	257
37.3	More Sophisticated Triggers	Tables Created by Triggers	259
37.4	A Case Study: Transactions in Triggers		260

Chapter 38 - WRITING SQL IN AI TOOLS▼

Section	Title	Subsections	Page
38.1	Choosing an AI Tool		261
38.2	Blind Queries		262
38.3	Querying a Specific Database	Method 1 – Getting Schema Information Method 2 – Uploading a Database Diagram Method 3 – Generating a Schema	263
38.4	Formatting and Refactoring	Comments and Indentation Rewriting Generated Queries Turning Views into Readable SQL	266
38.5	Optimising Queries		269
38.6	Debugging SQL		270

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