This page has 0 threads | Add post

Courseware

Introduction 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 Introduction 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	7
1.2	Many-to-Many Relationships		11

Chapter 2 - SQL SERVER MANAGEMENT STUDIO▼

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

Chapter 3 - CREATING A DATABASE AND TABLES▼

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

Chapter 4 - QUERIES▼

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

Chapter 5 - LAYING OUT QUERIES▼

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

Chapter 6 - THE SELECT STATEMENT▼

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

Chapter 7 - QUERY DESIGNER▼

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

Chapter 8 - CRITERIA USING WHERE▼

Section	Title	Subsections	Page
8.1	The WHERE Clause	Relational Operators	55
8.2	Criteria with Numbers	Using Comparisons Finding Numbers in a Given Range	56
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	57
8.4	Criteria for Dates	Using Dates in Criteria Using Dates with Wildcards	60
8.5	Combining Criteria		61
8.6	Nulls	An Example of Testing for Nulls Entering Nulls into a Table	62

Chapter 9 - EXPORTING TO EXCEL▼

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

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	68
10.2	Using SQL Functions	Typing an SQL Function Getting the Full List of Functions	70
10.3	Casting Data Types	The Need for Casting Data Type Precedence The CAST Function The CONVERT Function	71
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	74
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	77
10.6	Dealing with Nulls	The ISNULL Function The COALESCE Function	83
10.7	Testing Conditions using IIF		85

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	86
11.2	The Simple Case Statement		88
11.3	Nested CASE Statements		89

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	90
12.2	Formatting Dates using FORMAT	The Available Codes Using the Culture Argument How Slow is the Format Function?	92
12.3	Formatting Dates using CONVERT		94
12.4	Parts of a Date: DATEPART and DATENAME	Displaying a Day Suffix	95
12.5	Getting the Difference between Dates	Subtracting One Date from Another The DATEDIFF Function	96
12.6	Calculating Ages Correctly	Using DateDiff Dividing Someone’s Age in Days by 365 Getting the Exact Age	98
12.7	Adding Dates using DATEADD		100

Chapter 13 - JOINS▼

Section	Title	Subsections	Page
13.1	Overview of Joins	What is a Join? The Types of Join	101
13.2	Understanding your Database	How Relationships Work (Reminder)	102
13.3	Easy Joins, using Query Designer		103
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	104
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	108
13.6	Cross Joins	A Practical Example of Cross Joins	111
13.7	Self-Joins		112

Chapter 14 - SUMMARISING DATA▼

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

Chapter 15 - VIEWS▼

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

Chapter 16 - CTES AND DERIVED TABLES▼

Section	Title	Subsections	Page
16.1	Multi-Stage Queries		134
16.2	Derived Tables		135
16.3	Single CTEs (Common Table Expressions)	Syntax of Single CTEs The CTE for our Example	136
16.4	Multiple CTEs	Syntax of Multiple CTEs Example of a Multiple CTE	137

Chapter 17 - SUBQUERIES▼

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

Chapter 18 - RANKING AND PERCENTILES▼

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

Chapter 19 - WRITING SQL IN AI TOOLS▼

Section	Title	Subsections	Page
19.1	Choosing an AI Tool		146
19.2	Blind Queries		147
19.3	Querying a Specific Database	Method 1 – Getting Schema Information Method 2 – Uploading a Database Diagram Method 3 – Generating a Schema	148
19.4	Formatting and Refactoring	Comments and Indentation Rewriting Generated Queries Turning Views into Readable SQL	151
19.5	Optimising Queries		154
19.6	Debugging SQL		155

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