SQL is a timeless skillset you'll find in nearly every modern application! Using the popular PostgreSQL database, you'll learn to set up, model, and query your data through real-world projects. You'll also understand how to model complex relationships in your data and query data from large datasets.
Overview
Syllabus
- Introduction
- SQL Setup with Docker
- SQL Overview & Creating a Database
- Creating & Populating a Table
- Altering a Table & Postgres Data Types
- Inserting Data and Managing Conflicts
- Updating & Deleting Data
- Selecting, Paginating, & Using Where Clauses
- Using LIKE, ILIKE, & SQL Functions
- node-postgres & SQL Injection
- Ingredients API Exercise
- Ingredients API Solution
- Understanding Relationships & Joins
- Left, Right, & Outer Joins
- Natural & Cross Joins
- Foreign Keys & Managing References
- Many-to-Many Relationships
- Using the CHECK Constraint
- Using the DISTINCT Statement
- Recipes Exercise
- Recipes Solution
- JSON vs. JSONB
- Adding JSONB to Recipes Table
- When to Use JSONB
- Understanding Aggregation
- Filtering Aggregates with HAVING
- Storing Queries with Functions
- Functions vs. Procedures
- Calling Functions with Triggers
- The Movie Database Setup
- The Movie Database Exercise
- The Movie Database Solution: Most Money
- The Movie Database Solution: Keanu Reeves
- The Movie Database Solution: Most Revenue
- The Movie Database Solution: Most Keywords
- The Movie Database Solution: Most Movies
- Setup pgAdmin
- Analyzing Queries with EXPLAIN
- Identifying what to Index
- Creating an Index
- Using a GIN Index
- Creating a Partial Index
- Indexing a Derivative Value
- Creating Views & Inserting Data
- Joining Between Views
- Optimizing Queries with Materialized Views
- Indexing Materialized Views
- Using Subqueries
- Creating an Array from a Subquery
- Transactions
- Window Functions
- Self Join
- Wrapping Up
Taught by
Brian Holt
