SQL and PostgreSQL

Section 1

Simple SQL Statements

1.

What is SQL and PostgreSQL All About?
2.

Database Design
3.

Creating Tables with CREATE TABLE
4.

Inserting Data Into a Table with INSERT INTO and VALUES
5.

Retrieving Data with SELECT and FROM
6.

Calculated Columns and Renaming Selected Columns with AS
7.

String Operators/Functions for Manipulating Columns That Holds Values of String Type

Section 2

Filtering Records

1.

Filtering Rows with WHERE
2.

Compound WHERE Clauses with AND or OR
3.

Calculations in WHERE Clauses
4.

Updating Rows with UPDATE and SET
5.

Deleting Rows with DELETE FROM

Section 3

Working with Tables

1.

One-to-Many and Many-to-One Relationships
2.

One-to-One and Many-to-Many Relationships
3.

Primary Keys and Foreign Keys
4.

Auto-Generated IDs with SERIAL Data Type
5.

Data Consistency and Foreign Key Constraints Around Insertion/Deletion
6.

How To Delete a Table: DROP TABLE

Section 4

Relating Records with Joins

1.

Query Relating Records with Joins and Aggregations
2.

Joining Data from Different Tables with JOIN and ON
3.

Missing Data in Joins? Four Kinds of Joins
4.

Does The Order of Tables in FROM and JOIN make a difference?
5.

WHERE with JOIN
6.

Three Ways Joins

Section 5

Aggregation of Records

1.

Grouping Rows with GROUP BY and Aggregates
2.

Selecting Columns After Grouping
3.

Aggregate Functions
4.

A Gotcha with COUNT
5.

Filtering Groups with HAVING
6.

Keywords Order

Section 6

Sorting Records

1.

The Basics of Sorting: ORDER BY
2.

OFFSET and LIMIT Keywords

Section 7

Unions and Intersections with Sets

1.

Handling Sets with UNION and UNION ALL
2.

Commonalities with INTERSECT and INTERSECT ALL
3.

Removing Commonalities with EXCEPT and EXCEPT ALL
4.

Summary

Section 8

Assembling Queries with SubQueries

1.

What is a Subquery?
2.

Thinking About the Structure of Data
3.

Subqueries in a SELECT
4.

Subqueries in a FROM
5.

Subqueries in a JOIN
6.

Subqueries with WHERE
7.

Correlated Subqueries
8.

A SELECT without a FROM?

Section 9

Selecting Distinct Records

1.

Selecting Distinct Values with DISTINCT

Section 10

Utility Operators, Keywords, and Functions

1.

The Greatest Value in a List with GREATEST Function
2.

The Least Value in a List with LEAST Function
3.

The CASE Keyword

Section 11

PostgreSQL Complex Datatypes

1.

Data Types
2.

Rules on Numeric Data Types
3.

Reminder on Character Types
4.

Boolean Data Types
5.

Dates, Times and Timestamps
6.

Intervals

Section 12

Database-Side Validation and Constraints

1.

Thinking About Validation
2.

Applying a NULL Constraint
3.

Default Column Values
4.

Applying a Unique Constraint to One column
5.

Multi-Column Uniqueness
6.

Adding a Validation Check
7.

Checks Over Multiple Columns

Section 13

Polymorphic Associations

1.

Polymorphic Associations and Alternative Approaches

Section 14

A Look at Indexes for Performance

1.

Full Table Scans
2.

What is an Index?
3.

Creating an Index
4.

Composite Indexes
5.

Downsides of Indexes
6.

Maintaining Indexes
7.

Choosing Appropriate Index Types
8.

Automatically Generated Indexes

Section 15

Basic Query Tuning

1.

EXPLAIN and EXPLAIN ANALYZE

Section 16

Common Table Expressions (CTEs)

1.

Non-Recursive CTEs
2.

Recursive CTEs

Section 17

Simplifying Queries with Views

1.

Merging Tables: Creating a View
2.

Managing Data Through Views
3.

Security Advisors in Views (Supabase)

Section 18

Optimizing Queries with Materialized Views

1.

Materialized Views

Section 19

Handling Concurrency and Reversibility with Transactions

1.

What are Transactions Used For?
2.

Transaction Cleanup on Crash
3.

Closing Aborted Transactions

Section 20

Managing Database Design with Schema Migrations

1.

Schema Migration Files
2.

Issues Solved by Migrations
3.

Writing, Applying and Reverting Migrations | A Few Notes on Migrations Libraries

Section 21

Schema vs Data Migrations

1.

Schema vs Data Migrations
2.

Dangers Around Data Migrations | Properly Running Data and Schema Migrations
3.

Transaction Locks

Section 22

Accessing PostgreSQL From APIs

1.

Understanding Connection Pools | Query and Close

Section 23

Data Access Pattern - Repositories

1.

The Repository Pattern
2.

Creating a Repository

Section 24

Security Around PostgreSQL

1.

SQL Injection Exploits
2.

Handling SQL Injection with Prepared Statements: PREPARE & EXECUTE

Section 25

Fast Parallel Testing

1.

A Note on Testing
2.

Assertions Around User Count
3.

Connecting to a DB For Tests
4.

Disconnecting After Tests
5.

Multi-DB Setup
6.

Assumptions Around Content
7.

Issues with Parallel Tests
8.

Isolation with Schemas
9.

Creating and Accessing Schemas
10.

Controlling Schema Access with Search Paths
11.

Routing Schema Access
12.

Strategy for Isolation
13.

Programmatic Schema Creation
14.

Escaping Identifiers
15.

Test Helpers
16.

Cleaning up Schemas and Roles

SQL and PostgreSQL

Section 1 Simple SQL Statements

Section 2 Filtering Records

Section 3 Working with Tables

Section 4 Relating Records with Joins

Section 5 Aggregation of Records

Section 6 Sorting Records

Section 7 Unions and Intersections with Sets

Section 8 Assembling Queries with SubQueries

Section 9 Selecting Distinct Records

Section 10 Utility Operators, Keywords, and Functions

Section 11 PostgreSQL Complex Datatypes

Section 12 Database-Side Validation and Constraints

Section 13 Polymorphic Associations

Section 14 A Look at Indexes for Performance

Section 15 Basic Query Tuning

Section 16 Common Table Expressions (CTEs)

Section 17 Simplifying Queries with Views

Section 18 Optimizing Queries with Materialized Views

Section 19 Handling Concurrency and Reversibility with Transactions

Section 20 Managing Database Design with Schema Migrations

Section 21 Schema vs Data Migrations

Section 22 Accessing PostgreSQL From APIs

Section 23 Data Access Pattern - Repositories

Section 24 Security Around PostgreSQL

Section 25 Fast Parallel Testing

Section 1

Simple SQL Statements

Section 2

Filtering Records

Section 3

Working with Tables

Section 4

Relating Records with Joins

Section 5

Aggregation of Records

Section 6

Sorting Records

Section 7

Unions and Intersections with Sets

Section 8

Assembling Queries with SubQueries

Section 9

Selecting Distinct Records

Section 10

Utility Operators, Keywords, and Functions

Section 11

PostgreSQL Complex Datatypes

Section 12

Database-Side Validation and Constraints

Section 13

Polymorphic Associations

Section 14

A Look at Indexes for Performance

Section 15

Basic Query Tuning

Section 16

Common Table Expressions (CTEs)

Section 17

Simplifying Queries with Views

Section 18

Optimizing Queries with Materialized Views

Section 19

Handling Concurrency and Reversibility with Transactions

Section 20

Managing Database Design with Schema Migrations

Section 21

Schema vs Data Migrations

Section 22

Accessing PostgreSQL From APIs

Section 23

Data Access Pattern - Repositories

Section 24

Security Around PostgreSQL

Section 25

Fast Parallel Testing