Complete Intro to SQL & PostgreSQL

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.

• 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