Этот материал находится в платной подписке. Оформи премиум подписку и смотри или слушай SQL and PostgreSQL: The Complete Developer's Guide, а также все другие курсы, прямо сейчас!
Премиум
  1. Урок 1. 00:04:13
    What is PostgreSQL All About?
  2. Урок 2. 00:05:12
    Database Design
  3. Урок 3. 00:04:43
    Creating Tables
  4. Урок 4. 00:03:31
    Analyzing CREATE TABLE
  5. Урок 5. 00:04:50
    Inserting Data Into a Table
  6. Урок 6. 00:02:08
    Retrieving Data with Select
  7. Урок 7. 00:04:38
    Calculated Columns
  8. Урок 8. 00:00:59
    Calculating Phone Revenue
  9. Урок 9. 00:01:04
    Exercise Solution
  10. Урок 10. 00:06:18
    String Operators and Functions
  11. Урок 11. 00:03:50
    Filtering Rows with "Where"
  12. Урок 12. 00:02:31
    More on the "Where" Keyword
  13. Урок 13. 00:05:25
    Compound "Where" Clauses
  14. Урок 14. 00:00:38
    A "Where" Exercise Overview
  15. Урок 15. 00:00:57
    A "Where" Solution
  16. Урок 16. 00:00:38
    "Where" With Lists
  17. Урок 17. 00:01:43
    A "Where" With Lists Solution
  18. Урок 18. 00:02:06
    Calculations in "Where" Clauses
  19. Урок 19. 00:01:52
    Solving Calculations
  20. Урок 20. 00:04:17
    Updating Rows
  21. Урок 21. 00:03:17
    Deleting Rows
  22. Урок 22. 00:01:30
    A Solution for Updating Rows
  23. Урок 23. 00:01:18
    Solution for Deleting Rows
  24. Урок 24. 00:01:44
    The Plan Moving Forward
  25. Урок 25. 00:07:29
    Approaching Database Design
  26. Урок 26. 00:06:34
    One-to-Many and Many-to-One Relationships
  27. Урок 27. 00:05:42
    One-to-One and Many-to-Many Relationships
  28. Урок 28. 00:05:30
    Primary Keys and Foreign Keys
  29. Урок 29. 00:08:20
    Understanding Foreign Keys
  30. Урок 30. 00:05:48
    Auto-Generated ID's
  31. Урок 31. 00:04:39
    Creating Foreign Key Columns
  32. Урок 32. 00:06:49
    Running Queries on Associated Data
  33. Урок 33. 00:02:08
    Exercise Overview
  34. Урок 34. 00:02:10
    Foreign Key Creation Solution
  35. Урок 35. 00:05:36
    Foreign Key Constraints Around Insertion
  36. Урок 36. 00:05:01
    Constraints Around Deletion
  37. Урок 37. 00:04:38
    Testing Deletion Constraints
  38. Урок 38. 00:02:59
    Setting Foreign Keys to Null on Delete
  39. Урок 39. 00:02:57
    Adding Some Complexity
  40. Урок 40. 00:03:24
    Queries with Joins and Aggregations
  41. Урок 41. 00:07:40
    Joining Data from Different Tables
  42. Урок 42. 00:02:23
    Another Quick Join
  43. Урок 43. 00:01:14
    Exercise Overview
  44. Урок 44. 00:01:44
    A Joinful Solution
  45. Урок 45. 00:05:52
    Alternate Forms of Syntax
  46. Урок 46. 00:06:17
    Missing Data in Joins
  47. Урок 47. 00:02:23
    Why Wasn't It Included
  48. Урок 48. 00:09:26
    Four Kinds of Joins
  49. Урок 49. 00:04:15
    Each Join in Practice
  50. Урок 50. 00:04:12
    Does Order Matter?
  51. Урок 51. 00:00:56
    Exercise Overview
  52. Урок 52. 00:03:00
    Two Possible Solutions
  53. Урок 53. 00:06:46
    Where with Join
  54. Урок 54. 00:08:30
    Three Way Joins
  55. Урок 55. 00:04:59
    A Bit of Practice
  56. Урок 56. 00:02:53
    Exercise Solution
  57. Урок 57. 00:02:11
    Aggregating and Grouping
  58. Урок 58. 00:05:30
    Picturing Group By
  59. Урок 59. 00:03:00
    Aggregate Functions
  60. Урок 60. 00:04:48
    Combining Group By and Aggregates
  61. Урок 61. 00:02:37
    A Gotcha with Count
  62. Урок 62. 00:03:11
    Visualizing More Grouping
  63. Урок 63. 00:00:40
    Exercise Overview
  64. Урок 64. 00:01:22
    Grouping Solution
  65. Урок 65. 00:00:43
    Adding a Layer of Difficulty
  66. Урок 66. 00:01:28
    Solution
  67. Урок 67. 00:04:44
    Filtering Groups with Having
  68. Урок 68. 00:02:37
    Having In Action
  69. Урок 69. 00:06:48
    More on Having!
  70. Урок 70. 00:03:37
    A Having Exercise Overview
  71. Урок 71. 00:02:33
    A Quick Solution
  72. Урок 72. 00:02:35
    Investigating This Dataset
  73. Урок 73. 00:01:21
    Some Group By Practice
  74. Урок 74. 00:02:26
    Group By Review Solution
  75. Урок 75. 00:02:14
    Remember Joins?
  76. Урок 76. 00:01:26
    Of Course You Remember!
  77. Урок 77. 00:02:15
    The Basics of Sorting
  78. Урок 78. 00:02:41
    Two Variations on Sorting
  79. Урок 79. 00:06:39
    Offset and Limit
  80. Урок 80. 00:00:26
    Exercise Overview
  81. Урок 81. 00:01:21
    Exercise Solution
  82. Урок 82. 00:05:44
    Handling Sets with Union
  83. Урок 83. 00:03:46
    A Few Notes on Union
  84. Урок 84. 00:03:24
    Commonalities with Intersect
  85. Урок 85. 00:05:50
    Removing Commonalities with Except
  86. Урок 86. 00:01:27
    Union Exercise Overview
  87. Урок 87. 00:01:28
    Exercise Solution
  88. Урок 88. 00:05:49
    What's a Subquery?
  89. Урок 89. 00:04:47
    Thinking About the Structure of Data
  90. Урок 90. 00:05:42
    Subqueries in a Select
  91. Урок 91. 00:01:06
    Exercise Overview
  92. Урок 92. 00:01:19
    Select Solution
  93. Урок 93. 00:07:59
    Subqueries in a From
  94. Урок 94. 00:02:46
    From Subqueries that Return a Value
  95. Урок 95. 00:08:57
    Example of a Subquery in a From
  96. Урок 96. 00:01:16
    Exercise Overview
  97. Урок 97. 00:02:00
    Exercise Solution
  98. Урок 98. 00:04:53
    Subqueries in a Join Clause
  99. Урок 99. 00:08:13
    More Useful - Subqueries with Where
  100. Урок 100. 00:05:36
    Data Structure with Where Subqueries
  101. Урок 101. 00:00:58
    Exercise Overview
  102. Урок 102. 00:02:06
    Exercise Solution
  103. Урок 103. 00:04:10
    The Not In Operator with a List
  104. Урок 104. 00:06:21
    A New Where Operator
  105. Урок 105. 00:05:22
    Finally Some!
  106. Урок 106. 00:00:28
    Exercise Overview
  107. Урок 107. 00:01:34
    A Quick Solution
  108. Урок 108. 00:16:40
    Probably Too Much About Correlated Subqueries
  109. Урок 109. 00:08:54
    More on Correlated Subqueries
  110. Урок 110. 00:03:29
    A Select Without a From?
  111. Урок 111. 00:00:46
    Exercise Overview
  112. Урок 112. 00:01:20
    Exercise Solution
  113. Урок 113. 00:04:15
    Selecting Distinct Values
  114. Урок 114. 00:00:30
    Exercise Overview
  115. Урок 115. 00:00:41
    A Distinct Solution
  116. Урок 116. 00:03:21
    The Greatest Value in a List
  117. Урок 117. 00:02:02
    And the Least Value in a List!
  118. Урок 118. 00:04:14
    The Case Keyword
  119. Урок 119. 00:05:48
    PostgreSQL Installation on macOS
  120. Урок 120. 00:04:57
    pgAdmin Setup on macOS
  121. Урок 121. 00:03:56
    Postgres installation on Windows
  122. Урок 122. 00:04:03
    What'd We Just Do?
  123. Урок 123. 00:04:10
    Data Types
  124. Урок 124. 00:02:02
    Fast Rules on Numeric Data Types
  125. Урок 125. 00:09:20
    More on Number Data Types
  126. Урок 126. 00:04:43
    Reminder on Character Types
  127. Урок 127. 00:02:29
    Boolean Data Types
  128. Урок 128. 00:05:31
    Times, Dates, and Timestamps
  129. Урок 129. 00:05:45
    Really Awesome Intervals
  130. Урок 130. 00:04:13
    Thinking About Validation
  131. Урок 131. 00:07:20
    Creating and Viewing Tables in PGAdmin
  132. Урок 132. 00:06:26
    Applying a Null Constraint
  133. Урок 133. 00:04:10
    Solving a Gotcha with Null Constraints
  134. Урок 134. 00:03:14
    Default Column Values
  135. Урок 135. 00:05:36
    Applying a Unique Constraint to One column
  136. Урок 136. 00:03:41
    Multi-Column Uniqueness
  137. Урок 137. 00:04:00
    Adding a Validation Check
  138. Урок 138. 00:05:05
    Checks Over Multiple Columns
  139. Урок 139. 00:05:47
    So Where Are We Applying Validation?
  140. Урок 140. 00:03:14
    Approaching More Complicated Designs
  141. Урок 141. 00:06:07
    Using a SQL Design Tool
  142. Урок 142. 00:07:03
    A Config-based Schema Designer
  143. Урок 143. 00:02:16
    Here's the Plan
  144. Урок 144. 00:06:55
    Rebuilding Some Schema
  145. Урок 145. 00:04:36
    Requirements of a Like System
  146. Урок 146. 00:02:59
    How Not to Design a Like System
  147. Урок 147. 00:05:05
    Designing a Like System
  148. Урок 148. 00:01:36
    Making a Reaction System Instead
  149. Урок 149. 00:05:59
    Polymorphic Associations
  150. Урок 150. 00:06:58
    Polymorphic Association Alternative Implementation
  151. Урок 151. 00:03:40
    The Simplest Alternative
  152. Урок 152. 00:04:22
    So Which Approach?
  153. Урок 153. 00:06:11
    Additional Features Around Posts
  154. Урок 154. 00:01:40
    Adding Captions and Locations
  155. Урок 155. 00:07:28
    Photo Mentions vs Caption Mentions
  156. Урок 156. 00:07:09
    Considerations on Photo Tags vs Caption Tags
  157. Урок 157. 00:05:02
    Update For Tags
  158. Урок 158. 00:07:34
    Designing a Hashtag System
  159. Урок 159. 00:03:37
    Tables for Hashtags
  160. Урок 160. 00:04:07
    Including the Hashtag Table
  161. Урок 161. 00:06:02
    A Few More User Columns
  162. Урок 162. 00:03:45
    Why No Number of Followers or Posts?
  163. Урок 163. 00:05:51
    Designing a Follower System
  164. Урок 164. 00:01:46
    Back to Postgres
  165. Урок 165. 00:13:01
    Creating Tables with Checks
  166. Урок 166. 00:08:33
    Posts Creation
  167. Урок 167. 00:02:17
    Comments Creation
  168. Урок 168. 00:06:53
    Likes Creation
  169. Урок 169. 00:05:29
    Photo Tags and Caption Tags
  170. Урок 170. 00:06:28
    Creating Hashtags, Hashtag Posts, and Followers
  171. Урок 171. 00:04:07
    Adding Some Data
  172. Урок 172. 00:04:25
    Restoring from Scratch
  173. Урок 173. 00:01:42
    Highest User ID's Exercise
  174. Урок 174. 00:01:10
    Solution for User ID's
  175. Урок 175. 00:01:06
    Posts by a Particular User
  176. Урок 176. 00:01:57
    Solving for Posts by User
  177. Урок 177. 00:00:51
    Likes Per User
  178. Урок 178. 00:01:42
    Solution for Likes Per User
  179. Урок 179. 00:02:04
    Thinking About Performance
  180. Урок 180. 00:05:52
    Where Does Postgres Store Data?
  181. Урок 181. 00:03:37
    Heaps, Blocks, and Tuples
  182. Урок 182. 00:04:21
    Block Data Layout
  183. Урок 183. 00:31:53
    Heap File Layout
  184. Урок 184. 00:04:09
    Full Table Scans
  185. Урок 185. 00:02:24
    What's an Index
  186. Урок 186. 00:07:59
    How an Index Works
  187. Урок 187. 00:03:59
    Creating an Index
  188. Урок 188. 00:05:28
    Benchmarking Queries
  189. Урок 189. 00:05:10
    Downsides of Indexes
  190. Урок 190. 00:01:36
    Index Types
  191. Урок 191. 00:03:38
    Automatically Generated Indexes
  192. Урок 192. 00:31:56
    Behind the Scenes of Indexes
  193. Урок 193. 00:04:58
    The Query Processing Pipeline
  194. Урок 194. 00:05:26
    Explain and Explain Analyze
  195. Урок 195. 00:09:00
    Solving an Explain Mystery
  196. Урок 196. 00:11:58
    Developing an Intuitive Understanding of Cost
  197. Урок 197. 00:06:22
    Calculating Cost by Hand
  198. Урок 198. 00:07:18
    A Touch More on Costs
  199. Урок 199. 00:05:35
    Startup vs Total Costs
  200. Урок 200. 00:01:49
    Costs Flow Up
  201. Урок 201. 00:07:56
    Use My Index!
  202. Урок 202. 00:01:53
    Common Table Expressions
  203. Урок 203. 00:03:53
    A Quick Solution
  204. Урок 204. 00:03:51
    So What's a CTE?
  205. Урок 205. 00:03:19
    Recursive CTE's
  206. Урок 206. 00:10:22
    Recursive CTE's Step by Step
  207. Урок 207. 00:04:34
    Why Use Recursive CTE's?
  208. Урок 208. 00:08:03
    Writing the Query
  209. Урок 209. 00:09:48
    Walking Through Recursion
  210. Урок 210. 00:06:17
    Most Popular Users
  211. Урок 211. 00:03:52
    A Possible Solution for Merging Tables
  212. Урок 212. 00:05:37
    Creating a View
  213. Урок 213. 00:03:20
    When to Use a View?
  214. Урок 214. 00:02:27
    Deleting and Changing Views
  215. Урок 215. 00:02:19
    Materialized Views
  216. Урок 216. 00:04:09
    Grouping by Week
  217. Урок 217. 00:04:31
    Reminder on Left Joins
  218. Урок 218. 00:09:19
    Writing a Slow Query
  219. Урок 219. 00:06:38
    Creating and Refreshing Materialized Views
  220. Урок 220. 00:04:04
    What are Transactions Used For?
  221. Урок 221. 00:02:00
    Some Sample Data
  222. Урок 222. 00:09:45
    Opening and Closing Transactions
  223. Урок 223. 00:04:04
    Transaction Cleanup on Crash
  224. Урок 224. 00:01:46
    Closing Aborted Transactions
  225. Урок 225. 00:17:14
    A Story on Migrations
  226. Урок 226. 00:05:01
    Migration Files
  227. Урок 227. 00:02:52
    Issues Solved by Migrations
  228. Урок 228. 00:04:39
    A Few Notes on Migrations Libraries
  229. Урок 229. 00:02:53
    Project Creation
  230. Урок 230. 00:07:12
    Generating and Writing Migrations
  231. Урок 231. 00:07:16
    Applying and Reverting Migrations
  232. Урок 232. 00:03:39
    Generating and Applying a Second Migration
  233. Урок 233. 00:04:09
    Schema vs Data Migrations
  234. Урок 234. 00:09:07
    Dangers Around Data Migrations
  235. Урок 235. 00:05:29
    Properly Running Data and Schema Migrations
  236. Урок 236. 00:04:40
    Creating a Posts Table
  237. Урок 237. 00:14:53
    A Small Web Server
  238. Урок 238. 00:04:11
    Adding the Loc Column
  239. Урок 239. 00:04:13
    Writing Values to Both Columns
  240. Урок 240. 00:13:40
    Transaction Locks
  241. Урок 241. 00:04:20
    Updating Values
  242. Урок 242. 00:04:14
    Updating the App Server
  243. Урок 243. 00:03:02
    Dropping the Last Columns
  244. Урок 244. 00:00:53
    Section Goal
  245. Урок 245. 00:01:35
    Initial Setup
  246. Урок 246. 00:05:02
    One Fast Migration
  247. Урок 247. 00:04:54
    Building the Users Router
  248. Урок 248. 00:06:32
    Understanding Connection Pools
  249. Урок 249. 00:06:57
    Validating Connection Credentials
  250. Урок 250. 00:01:58
    Query and Close
  251. Урок 251. 00:03:39
    The Repository Pattern
  252. Урок 252. 00:08:21
    Creating a Repository
  253. Урок 253. 00:03:52
    Accessing the API
  254. Урок 254. 00:04:22
    Casing Issues
  255. Урок 255. 00:06:34
    Fixing Casing
  256. Урок 256. 00:05:27
    Finding Particular Users
  257. Урок 257. 00:06:41
    SQL Injection Exploits
  258. Урок 258. 00:07:49
    Handling SQL Injection with Prepared Statements
  259. Урок 259. 00:07:33
    Preventing SQL Injection
  260. Урок 260. 00:02:52
    Reminder on Post Requests
  261. Урок 261. 00:04:14
    Inserting Users
  262. Урок 262. 00:04:37
    Handling Updates
  263. Урок 263. 00:04:06
    And, Finally, Delete
  264. Урок 264. 00:02:48
    A Note on Testing
  265. Урок 265. 00:07:05
    Assertions Around User Count
  266. Урок 266. 00:05:02
    Connecting to a DB For Tests
  267. Урок 267. 00:02:46
    Disconnecting After Tests
  268. Урок 268. 00:04:41
    Multi-DB Setup
  269. Урок 269. 00:04:45
    Assumptions Around Content
  270. Урок 270. 00:04:49
    Issues with Parallel Tests
  271. Урок 271. 00:04:10
    Isolation with Schemas
  272. Урок 272. 00:05:22
    Creating and Accessing Schemas
  273. Урок 273. 00:05:48
    Controlling Schema Access with Search Paths
  274. Урок 274. 00:04:03
    Routing Schema Access
  275. Урок 275. 00:04:44
    Strategy for Isolation
  276. Урок 276. 00:11:45
    Programmatic Schema Creation
  277. Урок 277. 00:04:21
    Escaping Identifiers
  278. Урок 278. 00:05:19
    Test Helpers
  279. Урок 279. 00:05:57
    Cleaning up Schemas and Roles
  280. Урок 280. 00:03:33
    Finally... Parallel Tests! (Final Lecture)