This page has 0 threads | Add post

Courseware

Introduction to PostgreSQL course manual

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

Click here to download a sample of the first two chapters of this Introduction to PostgreSQL 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 - GETTING STARTED WITH POSTGRESQL▼

Section	Title	Subsections	Page
2.1	Getting Started with PostgreSQL	The Command Line Interface The GUI User Interface	12
2.2	Object Explorer	Listing Databases Listing Schemas Listing Tables	13
2.3	Setting Preferences	Change 1: Setting the Default Font Size Change 2: Changing the New Query Short-Cut Change 3: Enabling Intellisense Automatically	15

Chapter 3 - CREATING TABLES▼

Section	Title	Subsections	Page
3.1	Creating our Sample Database		17
3.2	Creating a Table		18
3.3	Creating Columns	Adding Columns to a Table while Creating It Adding Columns to an Existing Table Setting the Primary Key Setting Identity Columns Setting Default Values Allowing or Disallowing Nulls	19
3.4	Postgres Data Types	Data Types Explained in this Chapter Integer Field Types Boolean Field Type Decimal and Numeric Field Types Double Precision and Real Data Types String Data Types Date/Time Data Types	23
3.5	Entering Data in Tables		26

Chapter 4 - RELATIONSHIPS BETWEEN TABLES▼

Section	Title	Subsections	Page
4.1	Understanding Relationships		27
4.2	Creating a Relationship		28
4.3	ERD Diagrams	Creating an ERD Diagram from a Database Creating an ERD Diagram from Scratch Adding Relationships Other Options Available	29

Chapter 5 - CREATING QUERIES▼

Section	Title	Subsections	Page
5.1	Creating Queries	Viewing a Query’s Database Managing Query Windows	33
5.2	Basic SELECT Statements	Where to Put your Commas Using Autocomplete	35
5.3	Running Queries	Executing a Query Cancelling a Running Query Explaining a Query The Data Output Window Running Multiple SQL Commands in the Same Query Dealing with Errors Copying the Rows Returned from a Query	37
5.4	Saving, Opening and Closing Queries	Saving Queries Opening Queries	41
5.5	Using Upper and Lower Case	Changing the Default Case of SQL Keywords	42
5.6	Indentation and Word Wrap	Changing Indentation Word Wrap	43
5.7	Comments	Commenting Out Blocks of Code	44
5.8	Colours in SQL		45
5.9	Auto-formatting SQL		46

Chapter 6 - THE SELECT STATEMENT▼

Section	Title	Subsections	Page
6.1	SELECT Statement Syntax	Mnemonic for Order of Commands	47
6.2	Qualified Tables and Columns	Dragging Tables/Columns onto a Query Schemas	48
6.3	Table Aliases	Reason 1 for Aliases – Easier to Refer to Field Names Reason 2 for Aliases – Joins Changing a Table Alias	48
6.4	Column Aliases	Basic Column Aliases The Word AS is Optional Aliases in WHERE and ORDER BY Clauses	51
6.5	Ordering Rows	Simple Sorting Sorting by Multiple Columns	53
6.6	Miscellaneous SELECT Tricks	Selecting All Columns Using the Asterisk Selecting Unique Rows Showing Top and Bottom Rows Including Ties	54
6.7	Using UNION to Combine Results		56

Chapter 7 - CRITERIA USING WHERE▼

Section	Title	Subsections	Page
7.1	The WHERE Clause	Relational Operators	57
7.2	Criteria with Numbers	Using Comparisons Finding Numbers in a Given Range	58
7.3	Criteria using Text	Exact Matches Wildcard Matches using LIKE and ILIKE Using Special Characters with LIKE Ranges and Regular Expressions Using Similar To	59
7.4	Criteria for Dates	Using Dates in Criteria	62
7.5	Combining Criteria	Example using AND Example using OR Criteria Explained using Venn Diagrams Using Parentheses	63
7.6	Nulls	An Example of Testing for Nulls	65

Chapter 8 - IMPORTING AND EXPORTING DATA▼

Section	Title	Subsections	Page
8.1	Copying and Pasting		66
8.2	Exporting Rows for Tables	Starting the Export Process Choosing to Include Columns Choose Columns to Include Finishing the Export Viewing Errors	67
8.3	Exporting Queries	Exporting a Query Via a Table Exporting a Query by Pasting its SQL	70
8.4	Loading Data into Excel or Power BI	Installing the Driver Add an ODBC Data Source Loading Data into Excel	72

Chapter 9 - CALCULATIONS▼

Section	Title	Subsections	Page
9.1	Creating Calculated Columns	Giving Calculated Columns Aliases Using Column Aliases in ORDER BY Clauses Column Aliases Don’t Work in WHERE Criteria	74
9.2	Using SQL Functions	Typing an SQL Function Getting Help on PostgreSQL Functions	76
9.3	Casting Data Types	The Need for Casting Casting Using the CAST Function Casting using the Cast Operator	77
9.4	Numerical Calculations	Mathematical Symbols and BODMAS The Modulus Operator (%) Powers and Roots Rounding Other Useful Mathematical Functions The Importance of Casting Numbers for Calculations A Short-Cut to Forcing the Right Number Type	79
9.5	Text Calculations	Concatenating Text Changing the Case of Text Getting Numerical Information Extracting Text Manipulating Text	82
9.6	Examples of Text Functions	Example: Counting Words Example: Getting the Last Word (1) Example: Getting the Last Word (2)	85
9.7	Dealing with Nulls – the COALESCE Function		87

Chapter 10 - THE CASE EXPRESSION▼

Section	Title	Subsections	Page
10.1	The Searched Case Expression	Example: Film Bands Example: Film Era Using CASE Expressions in WHERE Criteria	88
10.2	The Simple Case Statement		91
10.3	Nested CASE Statements		92

Chapter 11 - DATE CALCULATIONS▼

Section	Title	Subsections	Page
11.1	How Dates and Times Work	How PostgreSQL Stores Dates How PostgreSQL Stores Times	93
11.2	Basic Dates and Times	Date and Time Types Getting the Current Date/Time	93
11.3	Representing Dates (and Times)	The Main Components of a Date The FM Modifier Ordinal Suffices Putting Everything Together Time Format Codes	95
11.4	Extracting Parts of Dates	The Extract Function Keywords for Extract The Date_Part Function Truncating Dates/Times	98
11.5	Getting the Interval Between Dates	Simple Date Subtraction Introducing Intervals Date/Time Subtraction Returning Intervals Getting True Ages Using Extract with Intervals	101
11.6	Adding and Subtracting Dates	Adding and Subtracting Days Adding and Subtracting Other Time Intervals Using Date_Add and Date_Subtract Generating Intervals from Numbers	104

Chapter 12 - JOINS▼

Section	Title	Subsections	Page
12.1	Overview of Joins	What is a Join? The Types of Join	107
12.2	Understanding your Database	How Relationships Work (Reminder)	108
12.3	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	109
12.4	Outer Joins	Creating Outer Joins in SQL Right Outer Joins Picking Out Unmatched Rows Full Outer Joins	114
12.5	Cross Joins	A Practical Example of Cross Joins	116
12.6	Self-Joins		117

Chapter 13 - SUMMARISING DATA▼

Section	Title	Subsections	Page
13.1	Simple Summarising	Syntax of a Simple Summary	118
13.2	Counting	Counting All of a Table’s Rows Counting Non-Null Columns Counting Unique Values Using Filter to Count a Subset of Rows	119
13.3	Grouping	Why you Need GROUP BY The GROUP BY Clause Grouping by Multiple Columns Grouping by Expressions Grouping without Aggregating	121
13.4	Filtering Results using HAVING		124
13.5	Dealing with Nulls	The Default Treatment of Nulls Forcing SQL to Include Nulls	125
13.6	The CUBE and ROLLUP Operators	Using CUBE to Show All Combinations Using ROLLUP to Show Most Combinations	126
13.7	String Aggregation		128

Chapter 14 - VIEWS▼

Section	Title	Subsections	Page
14.1	What Views Are		129
14.2	Why Views are Useful	Use 1: Pre-Joining Tables Use 2: Virtually Renaming Columns	130
14.3	Creating Views		132
14.4	Changing Views	Scripting Changes Why Changes Rarely Work Solution 1 – Delete and Recreate the View Solution 2 – Amend the Existing View	133
14.5	Sorting in Views		135
14.6	Joining to Views		136

Chapter 15 - CTES AND DERIVED TABLES▼

Section	Title	Subsections	Page
15.1	Multi-Stage Queries		137
15.2	Derived Tables	Single Derived Tables Multiple Derived Tables	138
15.3	Single CTEs (Common Table Expressions)	Syntax of Single CTEs The CTE for our Example	140
15.4	Multiple CTEs	Syntax of Multiple CTEs Example of a Multiple CTE	141

Chapter 16 - SUBQUERIES▼

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

Chapter 17 - POSTGRES AND AI▼

Section	Title	Subsections	Page
17.1	Choosing an AI Tool		147
17.2	Blind Queries		148
17.3	Querying a Specific Database	Creating a Backup of a Schema Uploading your Schema File Requesting a Query	149
17.4	Tidying Up Queries		151
17.5	Optimising Queries		152
17.6	Debugging SQL		153

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

PostgreSQL course manuals