002 diagrams (7.72 MB)
003 What is PostgreSQL All About (15.94 MB)
004 Database Design (49.41 MB)
005 Creating Tables (12.9 MB)
006 Analyzing CREATE TABLE (17.67 MB)
007 Inserting Data Into a Table (18.07 MB)
008 Retrieving Data with Select (6.02 MB)
009 Calculated Columns (15.22 MB)
010 Calculating Phone Revenue (3.36 MB)
011 Exercise Solution (3.15 MB)
012 String Operators and Functions (16.34 MB)
001 Filtering Rows with Where (12.26 MB)
002 More on the Where Keyword (9.45 MB)
003 Compound Where Clauses (18.98 MB)
004 A Where Exercise Overview (1.72 MB)
005 A Where Solution (2.54 MB)
006 Where With Lists (1.59 MB)
007 A Where With Lists Solution (4.96 MB)
008 Calculations in Where Clauses (4.68 MB)
009 Solving Calculations (5.91 MB)
010 Updating Rows (17.3 MB)
011 Deleting Rows (11.18 MB)
012 A Solution for Updating Rows (4.94 MB)
013 Solution for Deleting Rows (4 MB)
001 The Plan Moving Forward (10.75 MB)
002 Approaching Database Design (50.16 MB)
003 One-to-Many and Many-to-One Relationships (41.31 MB)
004 One-to-One and Many-to-Many Relationships (24.58 MB)
005 Primary Keys and Foreign Keys (29.57 MB)
006 Understanding Foreign Keys (63.35 MB)
007 Auto-Generated ID's (22.97 MB)
008 Creating Foreign Key Columns (18.32 MB)
009 Running Queries on Associated Data (38.22 MB)
010 Exercise Overview (8.25 MB)
011 Foreign Key Creation Solution (8.35 MB)
012 Foreign Key Constraints Around Insertion (22.15 MB)
013 Constraints Around Deletion (40.54 MB)
015 Testing Deletion Constraints (19.27 MB)
016 Setting Foreign Keys to Null on Delete (12.6 MB)
017 Adding Some Complexity (9.96 MB)
002 Queries with Joins and Aggregations (27.86 MB)
003 Joining Data from Different Tables (36.8 MB)
004 Another Quick Join (8.62 MB)
005 Exercise Overview (4.85 MB)
006 A Joinful Solution (5.27 MB)
007 Alternate Forms of Syntax (24.96 MB)
008 Missing Data in Joins (29.27 MB)
009 Why Wasn't It Included (24.58 MB)
010 Four Kinds of Joins (124.86 MB)
011 Each Join in Practice (40.6 MB)
012 Does Order Matter (61.33 MB)
013 Exercise Overview (2.23 MB)
014 Two Possible Solutions (16.51 MB)
015 Where with Join (31.03 MB)
016 Three Way Joins (49.71 MB)
017 A Bit of Practice (17.87 MB)
018 Exercise Solution (7.67 MB)
001 Aggregating and Grouping (7.9 MB)
002 Picturing Group By (29.43 MB)
003 Aggregate Functions (11.58 MB)
004 Combining Group By and Aggregates (15.13 MB)
005 A Gotcha with Count (5.46 MB)
006 Visualizing More Grouping (9.72 MB)
007 Exercise Overview (1.66 MB)
008 Grouping Solution (3.15 MB)
009 Adding a Layer of Difficulty (1.79 MB)
010 Solution (3.68 MB)
011 Filtering Groups with Having (36.97 MB)
012 Having In Action (6.86 MB)
013 More on Having! (28.46 MB)
014 A Having Exercise Overview (12.79 MB)
015 A Quick Solution (7.3 MB)
002 Investigating This Dataset (8 MB)
003 Some Group By Practice (3.1 MB)
004 Group By Review Solution (7.92 MB)
005 Remember Joins (9.3 MB)
006 Of Course You Remember! (4.42 MB)
001 The Basics of Sorting (6.73 MB)
002 Two Variations on Sorting (9.46 MB)
003 Offset and Limit (19.43 MB)
004 Exercise Overview (1.08 MB)
005 Exercise Solution (3.33 MB)
001 Handling Sets with Union (19.51 MB)
002 A Few Notes on Union (16.54 MB)
003 Commonalities with Intersect (15.42 MB)
004 Removing Commonalities with Except (20.44 MB)
005 Union Exercise Overview (4.23 MB)
006 Exercise Solution (4.72 MB)
001 What's a Subquery (30.54 MB)
002 Thinking About the Structure of Data (23.64 MB)
003 Subqueries in a Select (22.54 MB)
004 Exercise Overview (3.05 MB)
005 Select Solution (3.8 MB)
006 Subqueries in a From (44.45 MB)
007 From Subqueries that Return a Value (9.08 MB)
008 Example of a Subquery in a From (53.51 MB)
009 Exercise Overview (3.4 MB)
010 Exercise Solution (10.37 MB)
011 Subqueries in a Join Clause (16.55 MB)
012 More Useful - Subqueries with Where (36.66 MB)
013 Data Structure with Where Subqueries (44.04 MB)
014 Exercise Overview (2.71 MB)
015 Exercise Solution (6.25 MB)
016 The Not In Operator with a List (38.59 MB)
017 A New Where Operator (38.71 MB)
018 Finally Some! (31.96 MB)
019 Exercise Overview (1.23 MB)
020 A Quick Solution (4.37 MB)
021 Probably Too Much About Correlated Subqueries (107 MB)
022 More on Correlated Subqueries (44.16 MB)
023 A Select Without a From (9.09 MB)
024 Exercise Overview (1.95 MB)
025 Exercise Solution (3.57 MB)
001 Selecting Distinct Values (12.46 MB)
002 Exercise Overview (1.3 MB)
003 A Distinct Solution (1.9 MB)
001 The Greatest Value in a List (11.77 MB)
002 And the Least Value in a List! (5.55 MB)
003 The Case Keyword (13.32 MB)
001 PostgreSQL Installation on macOS (21.13 MB)
002 PGAdmin Setup on macOS (36.92 MB)
003 Postgres installation on Windows (23.31 MB)
001 What'd We Just Do (19.32 MB)
002 Data Types (30.53 MB)
003 Fast Rules on Numeric Data Types (18.9 MB)
004 More on Number Data Types (48.13 MB)
005 Reminder on Character Types (29.9 MB)
006 Boolean Data Types (8.2 MB)
007 Times, Dates, and Timestamps (21.27 MB)
008 Really Awesome Intervals (19.5 MB)
001 Thinking About Validation (34.64 MB)
002 Creating and Viewing Tables in PGAdmin (28.09 MB)
003 Applying a Null Constraint (21.08 MB)
004 Solving a Gotcha with Null Constraints (13.72 MB)
005 Default Column Values (22.47 MB)
006 Applying a Unique Constraint to One column (43.13 MB)
007 Multi-Column Uniqueness (30.06 MB)
008 Adding a Validation Check (18.21 MB)
009 Checks Over Multiple Columns (17.21 MB)
010 So Where Are We Applying Validation (22.04 MB)
001 Approaching More Complicated Designs (11.21 MB)
002 Using a SQL Design Tool (47.29 MB)
003 A Config-based Schema Designer (28.69 MB)
004 Here's the Plan (9.63 MB)
005 Rebuilding Some Schema (20.94 MB)
001 Requirements of a Like System (26.11 MB)
002 How Not to Design a Like System (16.22 MB)
003 Designing a Like System (26.14 MB)
004 Making a Reaction System Instead (6.39 MB)
005 Polymorphic Associations (52.6 MB)
006 Polymorphic Association Alternative Implementation (63.84 MB)
007 The Simplest Alternative (26.94 MB)
008 So Which Approach (16.02 MB)
001 Additional Features Around Posts (95.79 MB)
002 Adding Captions and Locations (8.53 MB)
003 Photo Mentions vs Caption Mentions (73.41 MB)
004 Considerations on Photo Tags vs Caption Tags (67.08 MB)
005 Update For Tags (23.42 MB)
001 Designing a Hashtag System (98.12 MB)
002 Tables for Hashtags (14.45 MB)
003 Including the Hashtag Table (18.04 MB)
004 A Few More User Columns (27.66 MB)
005 Why No Number of Followers or Posts (17.1 MB)
001 Designing a Follower System (26.49 MB)
001 Back to Postgres (6.84 MB)
002 Creating Tables with Checks (60.56 MB)
003 Posts Creation (36.16 MB)
004 Comments Creation (7.17 MB)
005 Likes Creation (23.87 MB)
006 Photo Tags and Caption Tags (41.35 MB)
007 Creating Hashtags, Hashtag Posts, and Followers (19.85 MB)
002 Adding Some Data (16.95 MB)
003 Restoring from Scratch (21.23 MB)
004 Highest User ID's Exercise (7.14 MB)
005 Solution for User ID's (4.64 MB)
006 Posts by a Particular User (4.81 MB)
007 Solving for Posts by User (9.39 MB)
008 Likes Per User (3.59 MB)
009 Solution for Likes Per User (6.6 MB)
001 Thinking About Performance (9.24 MB)
002 Where Does Postgres Store Data (37.02 MB)
003 Heaps, Blocks, and Tuples (22.73 MB)
004 Block Data Layout (17.24 MB)
005 Heap File Layout (597.17 MB)
001 Full Table Scans (37.18 MB)
002 What's an Index (10.1 MB)
003 How an Index Works (69.31 MB)
004 Creating an Index (13.11 MB)
005 Benchmarking Queries (22.58 MB)
006 Downsides of Indexes (31.82 MB)
007 Index Types (6.46 MB)
008 Automatically Generated Indexes (19.74 MB)
009 Behind the Scenes of Indexes (398.75 MB)
001 The Query Processing Pipeline (25.73 MB)
002 Explain and Explain Analyze (42.83 MB)
003 Solving an Explain Mystery (79.79 MB)
001 Developing an Intuitive Understanding of Cost (102.89 MB)
002 Calculating Cost by Hand (51.25 MB)
003 A Touch More on Costs (95.4 MB)
004 Startup vs Total Costs (44.7 MB)
005 Costs Flow Up (10.1 MB)
006 Use My Index! (39.4 MB)
001 Common Table Expressions (11.19 MB)
002 A Quick Solution (13.16 MB)
003 So What's a CTE (33.5 MB)
001 Recursive CTE's (11.9 MB)
002 Recursive CTE's Step by Step (62.89 MB)
003 Why Use Recursive CTE's (43.14 MB)
004 Writing the Query (36.56 MB)
005 Walking Through Recursion (109.41 MB)
001 Most Popular Users (24.81 MB)
002 A Possible Solution for Merging Tables (35.67 MB)
003 Creating a View (33.33 MB)
004 When to Use a View (21.21 MB)
005 Deleting and Changing Views (12.51 MB)
001 Materialized Views (8.38 MB)
002 Grouping by Week (34.4 MB)
003 Reminder on Left Joins (15.51 MB)
004 Writing a Slow Query (55.08 MB)
005 Creating and Refreshing Materialized Views (36.1 MB)
001 What are Transactions Used For (22.54 MB)
002 Some Sample Data (5.44 MB)
003 Opening and Closing Transactions (65.33 MB)
004 Transaction Cleanup on Crash (32.89 MB)
005 Closing Aborted Transactions (7.47 MB)
001 A Story on Migrations (139.7 MB)
002 Migration Files (44.16 MB)
003 Issues Solved by Migrations (10 MB)
004 A Few Notes on Migrations Libraries (34.19 MB)
006 Project Creation (9.91 MB)
007 Generating and Writing Migrations (45.64 MB)
008 Applying and Reverting Migrations (68.14 MB)
009 Generating and Applying a Second Migration (16.63 MB)
001 Schema vs Data Migrations (20.28 MB)
002 Dangers Around Data Migrations (70 MB)
003 Properly Running Data and Schema Migrations (19.73 MB)
004 Creating a Posts Table (21.59 MB)
005 A Small Web Server (62.26 MB)
007 Adding the Loc Column (21.55 MB)
008 Writing Values to Both Columns (16.26 MB)
009 Transaction Locks (96.42 MB)
010 Updating Values (15.27 MB)
012 Updating the App Server (13.23 MB)
013 Dropping the Last Columns (11.61 MB)
001 Section Goal (2.62 MB)
002 Initial Setup (2.23 MB)
003 One Fast Migration (24.43 MB)
004 005-access-social-repo (61.31 KB)
004 Building the Users Router (29.61 MB)
005 Understanding Connection Pools (22.93 MB)
006 Validating Connection Credentials (44.74 MB)
007 008-access (62 KB)
007 Query and Close (5.9 MB)
001 The Repository Pattern (17.88 MB)
002 010-access (62.58 KB)
002 Creating a Repository (24.78 MB)
003 Accessing the API (23.55 MB)
004 Casing Issues (18.55 MB)
005 Fixing Casing (49.74 MB)
006 Finding Particular Users (18.16 MB)
001 SQL Injection Exploits (30.46 MB)
002 Handling SQL Injection with Prepared Statements (38.15 MB)
003 Preventing SQL Injection (34.29 MB)
004 Reminder on Post Requests (14.4 MB)
005 019-access (63.56 KB)
005 Inserting Users (15.25 MB)
006 Handling Updates (16.66 MB)
007 021-access (63.66 KB)
007 And, Finally, Delete (18.24 MB)
001 A Note on Testing (7.64 MB)
002 Assertions Around User Count (42.34 MB)
003 024-access (64.5 KB)
003 Connecting to a DB For Tests (54.23 MB)
004 025-access (64.51 KB)
004 Disconnecting After Tests (12.86 MB)
005 026-access (64.52 KB)
005 Multi-DB Setup (21.85 MB)
006 027-access (64.52 KB)
006 Assumptions Around Content (33.12 MB)
007 028-access (65.77 KB)
007 Issues with Parallel Tests (35.76 MB)
008 Isolation with Schemas (27.4 MB)
009 Creating and Accessing Schemas (19.38 MB)
010 Controlling Schema Access with Search Paths (30.95 MB)
011 Routing Schema Access (41.12 MB)
012 Strategy for Isolation (17.57 MB)
013 034-access (66.14 KB)
013 Programmatic Schema Creation (65.75 MB)
014 035-access (66.14 KB)
014 Escaping Identifiers (25.53 MB)
015 036-access (66.48 KB)
015 Test Helpers (43.34 MB)
016 037-access (66.59 KB)
016 Cleaning up Schemas and Roles (31.28 MB)
017 Finally Parallel Tests! (Final Lecture) (18.57 MB)