YouTube2Text

The Ultimate Database Roadmap (SQL to NoSQL and Beyond) — Transcript

by Tech With Tim · 3,095 words · 463 segments · language en · Watch on YouTube

Full transcript

  1. 0:00This video will give you a complete road
  2. 0:02map on how you can master databases.
  3. 0:04It's going to cover topics which are
  4. 0:06beginner level, intermediate level, and
  5. 0:08advanced and go over everything that you
  6. 0:09need to learn in what order if you want
  7. 0:12to be comfortable working with
  8. 0:13databases. Now, quickly, I want to clear
  9. 0:15up a misconception. Databases are not
  10. 0:17just important to understand if you're a
  11. 0:19back-end developer or a data scientist
  12. 0:20or, you know, a data engineer. They're
  13. 0:22also important for front-end developers,
  14. 0:24full stack developers, really any type
  15. 0:26of developer to at least get a solid
  16. 0:28grasp of because you're going to use
  17. 0:30them in every single type of application
  18. 0:32that you write. And after all, if you
  19. 0:34think of what an application is, it's
  20. 0:36really just a flow of data. Saving data,
  21. 0:38updating data, deleting data, presenting
  22. 0:40data, that is really what software does.
  23. 0:43So, you really need to dive into the
  24. 0:44core here and understand what I would
  25. 0:46say is the most important part of any
  26. 0:47application, which is the data. So, with
  27. 0:49that said, let's get into it. Again,
  28. 0:51I've broken this video into three sets
  29. 0:53of skills: beginner, intermediate, and
  30. 0:55advanced. And if you want a list of all
  31. 0:56of these skills in a written format,
  32. 0:58simply click the link in the
  33. 0:59description. You can sign up for my
  34. 1:00newsletter, and I'll give it to you for
  35. 1:02free. Okay, let's get into it with the
  36. 1:04beginner skills. So, at the beginner
  37. 1:05level here, this is really all about
  38. 1:07understanding the foundations of
  39. 1:09databases. So, that starts with what a
  40. 1:11database actually is. What is a
  41. 1:14structured data store or data storage?
  42. 1:16And why would you use this compared to
  43. 1:18something like a spreadsheet, you know,
  44. 1:19or an Excel document, etc. Right? Now,
  45. 1:22after that, you want to look at the
  46. 1:23difference between relational and non-
  47. 1:25relational databases. So, what is a
  48. 1:27relational database? What does that
  49. 1:29mean? And quickly have a look at SQL
  50. 1:31versus NoSQL. Now, you don't need to be
  51. 1:33an expert here, but you should
  52. 1:34understand what SQL stands for. So,
  53. 1:36structured query language and what a
  54. 1:39database that uses SQL can kind of do
  55. 1:41versus one that doesn't. For example,
  56. 1:43you have very popular databases like
  57. 1:44MySQL or Postcris SQL or SQLite for
  58. 1:48example. And then you have ones like
  59. 1:49MongoDB or maybe Firebase for example,
  60. 1:52right? So what is kind of the core
  61. 1:54differences between those? You should
  62. 1:55have a bit of an understanding before
  63. 1:57you dive too far into the syntax and get
  64. 1:59into the next section here which is SQL
  65. 2:02fundamentals. Now I always suggest that
  66. 2:04people start by mastering SQL databases.
  67. 2:07If you understand SQL databases, it's a
  68. 2:09lot easier to get into NoSQL and to see
  69. 2:11the differences and the advantages. So,
  70. 2:13you want to start by understanding SQL,
  71. 2:15right, structured query language, and
  72. 2:17going over the common syntax there. So,
  73. 2:19what is a select statement? What is an
  74. 2:21insert statement? What is an update
  75. 2:22statement? What is a delete statement?
  76. 2:24And I would highly recommend that while
  77. 2:26you're doing this, you use one of two
  78. 2:27databases. Either a Postgress SQL
  79. 2:30database or a MySQL database. You can
  80. 2:32use SQLite as well, but these are the
  81. 2:34three most common databases. They're all
  82. 2:36very similar. So I would suggest just
  83. 2:38picking one. Most people go with
  84. 2:39Postgress SQL and do some experiments,
  85. 2:42write some data, update some delay data,
  86. 2:44sorry, and understand the basic syntax.
  87. 2:47Now once you have a little bit of
  88. 2:48understanding of what SQL is and some of
  89. 2:50the SQL syntax, you want to start
  90. 2:52looking at tables and schemas. Now a
  91. 2:54table is really just a structured
  92. 2:56collection of data. Should understand
  93. 2:58what that is. And a schema is
  94. 2:59essentially a blueprint that defines the
  95. 3:01table structure. Now this involves
  96. 3:03looking at things like data types. So
  97. 3:05like an integer versus a string versus
  98. 3:07text versus a date versus a boolean. You
  99. 3:10want to know what those different types
  100. 3:11of data are and how you can structure a
  101. 3:14schema for a table. Now after that you
  102. 3:16want to start looking at constraints.
  103. 3:18Now this involves primary keys, foreign
  104. 3:21keys, the unique constraint, things like
  105. 3:23not having a nullable value inside of
  106. 3:25the database. This is where you'll start
  107. 3:27to understand how to make your schemas a
  108. 3:28little bit more strict. And you get into
  109. 3:30again the basics of SQL where you're
  110. 3:32creating a schema, setting up some
  111. 3:34tables, and then we move into the next
  112. 3:35step which is the CRUD operations. CRUD
  113. 3:38stands for create, read, update, and
  114. 3:41delete. You need to know how to do that
  115. 3:43in SQL syntax. So for example, using the
  116. 3:45insert into syntax. If you're reading
  117. 3:48something using the select syntax, if
  118. 3:50you're updating something using, you
  119. 3:51know, update set, for example, if you're
  120. 3:53deleting something using delete from.
  121. 3:55Okay. Now once you look at those CRUD
  122. 3:57operations, you want to start getting
  123. 3:59into some filtering. So using things
  124. 4:01like the wear clause or the order by
  125. 4:03clause to limit the number of results
  126. 4:05and look at again a little bit more
  127. 4:06advanced SQL syntax. At this point you
  128. 4:09should understand what a SQL database
  129. 4:10is, how to create tables and schemas,
  130. 4:12how to insert data, read data and update
  131. 4:15data at a basic level. Now after that
  132. 4:17you want to start looking at
  133. 4:18relationships and keys. So SQL is really
  134. 4:21useful because you can have a lot of
  135. 4:23related data that's kind of connected to
  136. 4:25one another. You can have for example
  137. 4:27onetoone relationships, one to many
  138. 4:30relationships, many to many
  139. 4:31relationships. You need to understand
  140. 4:33what those are and how you can relate
  141. 4:35data together and start to do a little
  142. 4:37bit of database design. Now this is
  143. 4:39going to involve using primary keys,
  144. 4:41foreign keys, and again understanding
  145. 4:43when to use these different types of
  146. 4:44relationships in terms of how you kind
  147. 4:47of group data together. Now, along those
  148. 4:49same lines, you're going to want to look
  149. 4:50at cascading and things like updates and
  150. 4:52deletes with that related data. Now, I
  151. 4:54know that sounds like a lot, but that's
  152. 4:55really the bare minimum at the beginner
  153. 4:57level that everyone should understand.
  154. 4:59And that is the SQL fundamentals,
  155. 5:01understanding relational databases, and
  156. 5:03again, really understanding
  157. 5:05relationships, which are very, very
  158. 5:06important. Now, I suggest to close this
  159. 5:08section out that you work on some sample
  160. 5:10projects where you just work with a good
  161. 5:12amount of data in a SQL database and you
  162. 5:14do an exercise of, for example,
  163. 5:16designing your own database where you
  164. 5:18have maybe some users organized or
  165. 5:20connected with some orders, maybe you
  166. 5:21have some transactions or some payments.
  167. 5:23You can go to something like chat GBT
  168. 5:25ask it you know what's a simple SQL
  169. 5:27project I could practice with but you
  170. 5:28need to do a little bit of hands-on
  171. 5:30learning here and try to design
  172. 5:31something on your own so you can make
  173. 5:33all of those important decisions on you
  174. 5:35know what data type should I use what
  175. 5:37relationship should I have where should
  176. 5:38the foreign key live etc now let's keep
  177. 5:40going here into the intermediate level
  178. 5:43but you're going to realize that as you
  179. 5:44start working with databases it becomes
  180. 5:46important to use some tools to manage
  181. 5:48the databases right to visualize them
  182. 5:50work with them etc that's exactly where
  183. 5:52today's sponsor answer data grip from
  184. 5:55jet brains comes in. Data grip is an IDE
  185. 5:57built specifically for working with
  186. 5:59multiple databases all in one place. You
  187. 6:02can connect to almost anything like
  188. 6:04MySQL, Postgress SQL, SQLite, MongoDB,
  189. 6:08and dozens more and get an editor that's
  190. 6:10actually smart about your schemas and
  191. 6:12queries. It gives you schema
  192. 6:14autocomplete, instant error detection, a
  193. 6:17powerful data editor, and schema AI that
  194. 6:20lets you turn natural language into SQL.
  195. 6:23It can also suggest fixes for your
  196. 6:25queries, and do much more, all while
  197. 6:27understanding your actual database
  198. 6:29structure. You can browse data, edit
  199. 6:31rows directly, refactor SQL, and switch
  200. 6:34between different database engines
  201. 6:36without ever leaving the same window.
  202. 6:38Now, if you're learning databases or
  203. 6:40building apps that rely on more than one
  204. 6:42backend, mastering a tool like this can
  205. 6:44massively boost your productivity
  206. 6:46because it visualizes relationships,
  207. 6:48integrates with tools like Git and
  208. 6:50brings all of the productivity features
  209. 6:52you'd expect from a modern Jetrain IDE,
  210. 6:55and the best part is that it's now free
  211. 6:57for non-commercial use, which means it's
  212. 6:59perfect for students, hobby projects, or
  213. 7:02anyone who wants to get better at
  214. 7:03working with databases. Jetrains just
  215. 7:06announced this and it's awesome that you
  216. 7:07can use such a powerful tool completely
  217. 7:10for free. Try it out today by
  218. 7:12downloading data grip for free from the
  219. 7:14link in the description and see how much
  220. 7:16faster you can work across all of your
  221. 7:18databases. Thanks to Jetrains. Now let's
  222. 7:20get back into it. So let's continue here
  223. 7:22with the intermediate level which is
  224. 7:24really about designing databases for
  225. 7:26performance and reliability. This is
  226. 7:28where we get out of the absolute basics
  227. 7:29and we move into some more advanced
  228. 7:31topics. Now, the first thing that you're
  229. 7:32going to want to look at here is
  230. 7:33something called database joins and
  231. 7:35subqueries. Now, some might argue this
  232. 7:37is in the beginner level, but I think it
  233. 7:39can be a little bit more complex, which
  234. 7:40is why I put it here. Now, this allows
  235. 7:42you to essentially connect multiple
  236. 7:44tables together and to query information
  237. 7:46all at once and aggregate data in the
  238. 7:49same response. So, you can use things
  239. 7:50like the inner syntax, left join, right
  240. 7:53join, full joins. You need to understand
  241. 7:55what those are, how they work, and how
  242. 7:57you can make them the most efficient.
  243. 7:59Now you also want to start looking into
  244. 8:00subqueries for example using nested
  245. 8:02select statements and getting into using
  246. 8:04aliases for readability of the database.
  247. 8:07Now after that you want to start looking
  248. 8:08at indexes and query optimization. It's
  249. 8:11one thing to query the database. It's
  250. 8:12another to do this in an efficient
  251. 8:14manner and that's really where indexes
  252. 8:16come in in terms of how quickly you can
  253. 8:18look information up in the database. You
  254. 8:20want to also start looking at trade-offs
  255. 8:22like write speed versus read performance
  256. 8:25and the way in which you design your
  257. 8:26database so that you know am I going to
  258. 8:28do a lot of writes am I going to do a
  259. 8:30lot of reads and what kind of operation
  260. 8:32am I optimizing for. Now you also want
  261. 8:34to understand things like the explain
  262. 8:36query and how you can analyze queries
  263. 8:38for performance. Next you want to start
  264. 8:40looking at transactions. Now, this
  265. 8:42involves using things like the commit
  266. 8:44and roll back syntax and having
  267. 8:46different isolation levels and
  268. 8:48concurrency controls based on how you're
  269. 8:50setting up your database. Now, you
  270. 8:52should understand common transaction use
  271. 8:53cases, things like bank transfers,
  272. 8:55inventory updates, etc. Next, we move to
  273. 8:59data normalization. Now, normalization
  274. 9:01helps prevent redundancy in the database
  275. 9:04and you have different normal forms in
  276. 9:06your database. You have 1 NF, 3 NF, and
  277. 9:08you need to understand what those are.
  278. 9:10Now, you also need to know when to
  279. 9:12denormalize for performance in your
  280. 9:14database. And I know a lot of this
  281. 9:15sounds vague. I'm not trying to teach
  282. 9:16you these concepts. I'm just giving you
  283. 9:18a list of things you can essentially
  284. 9:19look up to figure out what I'm talking
  285. 9:21about. Right. Next on my list, we have
  286. 9:23referential integrity and constraints.
  287. 9:25So, essentially enforcing different
  288. 9:27relationships through various foreign
  289. 9:29keys, things like cascading updates and
  290. 9:31deletes, but looking at it on a deeper
  291. 9:33level that we did before, and handling
  292. 9:35constraint violations. Nearing the end
  293. 9:37of this intermediate section, I have
  294. 9:39things like query optimization
  295. 9:40techniques. So, how to use indexes and
  296. 9:42multiple indexes effectively, avoiding,
  297. 9:45you know, selecting asterisks from the
  298. 9:47database, like why is that a bad thing
  299. 9:48to do, limiting the results sets, using
  300. 9:51proper wear clauses, caching and
  301. 9:53pagionation considerations, and how you
  302. 9:55can make your queries as optimal and as
  303. 9:57fast as possible. In the beginner level,
  304. 9:59you're just trying to get the data that
  305. 10:01you need. In the intermediate level, you
  306. 10:03really need to think about how can I
  307. 10:04make sure this is as optimal as possible
  308. 10:06and I'm not wasting resources or doing
  309. 10:08something that you know takes an
  310. 10:09unnecessarily long amount of time. Now
  311. 10:12lastly, we get into a kind of a real
  312. 10:13world example here. So I always suggest
  313. 10:15again building something working on a
  314. 10:18project to learn these types of topics.
  315. 10:19So if you could design for example a
  316. 10:21small e-commerce schema where you have
  317. 10:23like products, orders, users, reviews,
  318. 10:26maybe some kind of inventory and then
  319. 10:28you write you know complex queries to
  320. 10:30join and aggregate all of this data
  321. 10:32together. You have the correct
  322. 10:33constraints. You add a bunch of data.
  323. 10:35You actually connect to it and maybe
  324. 10:36present it in a project. That's a great
  325. 10:38way to learn some of these more advanced
  326. 10:40topics. Anyways, that wraps up the
  327. 10:42intermediate section. Again, I know it's
  328. 10:44a lot of stuff. It might sound
  329. 10:45complicated, but now we're going to move
  330. 10:47on to the advanced level. Now the
  331. 10:49advanced level is really about scaling
  332. 10:51and modern data architectures. So before
  333. 10:54we're talking more about kind of query
  334. 10:55optimizations and stuff on just the data
  335. 10:58level. Now we're looking at really how
  336. 11:00do we scale the system, right? How do we
  337. 11:01scale databases? And this is more
  338. 11:03architectural where we get into more
  339. 11:05senior level skills. Full disclosure,
  340. 11:07I'm not a complete expert in this area,
  341. 11:09but I just am aware of a lot of these
  342. 11:11skills and I've seen them pop up with
  343. 11:12people that I work with or places that I
  344. 11:14worked at before. So that's why I'm
  345. 11:16mentioning them here. Okay. So first is
  346. 11:18scaling databases and vertical scaling
  347. 11:21versus horizontal scaling. So when do
  348. 11:23you add more compute versus when do you
  349. 11:25add more resources and start doing
  350. 11:27things like sharding, partitioning and
  351. 11:29replicating your databases, right? This
  352. 11:31is things like read replicas versus
  353. 11:33write masters and getting into again
  354. 11:36more complex architecture where you have
  355. 11:38multiple machines in this distributed
  356. 11:40system. Next we have replication and
  357. 11:42backups. So when do you back up the
  358. 11:44database? what data needs to be backed
  359. 11:46up and how do you make sure that
  360. 11:47everything you have is always available
  361. 11:49and fault tolerant. Now this also leads
  362. 11:52into various backup strategies. So like
  363. 11:54full backups, incremental backups, point
  364. 11:56in time recovery. You need to look at
  365. 11:58all of those strategies. You also need
  366. 12:00to look at failover and again high
  367. 12:02availability patterns so that if one
  368. 12:03database goes down your data is still
  369. 12:05accessible. Next we look at transactions
  370. 12:08and scale. So essentially distributed
  371. 12:10transactions and two-phase commits. This
  372. 12:13means eventual consistency versus strong
  373. 12:16consistency. Looking at things like the
  374. 12:18cap theorem. So consistency,
  375. 12:20availability, and partition tolerance.
  376. 12:22Again, I'm not an expert in this field,
  377. 12:24but these are some things that you
  378. 12:25should have a look at. Next, we get into
  379. 12:27NoSQL concepts. So notice that almost
  380. 12:29everything I mentioned so far relates to
  381. 12:31SQL databases. That's intentional
  382. 12:33because that's really where you're going
  383. 12:34to learn the most about databases and
  384. 12:36architecture. And then I suggest again
  385. 12:38once we've learned all of that to start
  386. 12:40looking more into NoSQL databases. So to
  387. 12:43understand why NoSQL database exists to
  388. 12:45understand the flexibility and the
  389. 12:47scaling uh kind of problems or
  390. 12:49potentially advantages of using a NoSQL
  391. 12:51database and then starting to look at
  392. 12:53things like key value stores so things
  393. 12:55like a ready's database right which
  394. 12:57maybe you would refer to as NoSQL but
  395. 12:59really it's a key value database and
  396. 13:00when that can kind of fill in or come
  397. 13:02into your system also looking at
  398. 13:04document databases again no SQL
  399. 13:06databases like MongoDB or things like
  400. 13:09Cassandra where you have you know column
  401. 13:10or stores right even things like vector
  402. 13:12databases or graph databases. This is
  403. 13:15where you start to kind of spread out a
  404. 13:17little bit and look at all of the
  405. 13:18different offerings and particularly
  406. 13:20those strengths and weaknesses and being
  407. 13:22able to decide which one you actually
  408. 13:24want to use. Now, that leads us into the
  409. 13:26next section here, which is hybrid data
  410. 13:28models. So, understanding when you want
  411. 13:30to mix multiple databases together and
  412. 13:33use a SQL database plus a NoSQL
  413. 13:35database, for example. When do we have
  414. 13:37readies for caching? when do we have a
  415. 13:39NoSQL database for you know data that's
  416. 13:41constantly changing when do we have
  417. 13:42strict relational databases and what's
  418. 13:44the uses for those right also how do we
  419. 13:47log all of this information and what
  420. 13:49database is just the best for what
  421. 13:50purpose so you need to understand the
  422. 13:52trade-offs between the consistency and
  423. 13:54the flexibility of these services and be
  424. 13:57able to argue why you should use one
  425. 13:59over the other. Next we get into
  426. 14:01performance and observability, right? So
  427. 14:03things like query profiling, being able
  428. 14:05to view all of the logs. You have things
  429. 14:07like monitoring performance. So being
  430. 14:09able to look at the CPU, the input
  431. 14:10output, the various connections, the
  432. 14:12network uh usage for your different
  433. 14:14machines and resources. This also leads
  434. 14:16into caching strategies. So having
  435. 14:18in-memory caches versus distributed
  436. 14:20caches. And then we look at things like
  437. 14:22schema migration management, especially
  438. 14:24in production environments. How do we
  439. 14:25make sure that if we adjust the
  440. 14:27database, we don't break everything? and
  441. 14:28how do we migrate large portions of data
  442. 14:31and set ourselves up for success when we
  443. 14:33want to do that in the future. Now,
  444. 14:35there is a lot of stuff here and again
  445. 14:36as we get further into this list, I
  446. 14:38become less and less of an expert, but
  447. 14:40these are things that you need to look
  448. 14:41at if you really want to get into more
  449. 14:43of a backend heavy role, a data
  450. 14:44engineering role. And a lot of what
  451. 14:46you're doing is working with databases.
  452. 14:48For most people, up until kind of the
  453. 14:50middle of that intermediate section is
  454. 14:52where I would suggest being. If you can
  455. 14:54learn more, obviously, that's fantastic.
  456. 14:56But the skills in this section are
  457. 14:58really if you want to go above and
  458. 14:59beyond. So with that said, that's going
  459. 15:00to wrap up this video. I hope you
  460. 15:02enjoyed. If you did, make sure to leave
  461. 15:03a like, subscribe to the channel, and I
  462. 15:05will see you in the next one.
  463. 15:11[Music]

About this transcript

This page contains the full transcript of The Ultimate Database Roadmap (SQL to NoSQL and Beyond) by Tech With Tim, generated from the public captions YouTube serves with the video. The transcript has 3,095 words across 463 segments, with the original timestamps preserved so you can click any line to jump to that moment in the embedded player.

What you can do with it

Use the transcript to take notes, quote the speaker, build a study guide, generate a summary with ChatGPT or Claude via the YouTube Summary tool, or export it as a timed subtitle file with YouTube to SRT. You can also re-open it in the transcriber to choose a caption track available on the video.

Free YouTube transcript tool

YouTube2Text is a free YouTube transcript generator — no signup, no daily limit. Paste any YouTube link and get the full transcript instantly, with timestamps, click-to-jump, caption language selection, AI prompts for ChatGPT, Claude, and Gemini, and exports to TXT, SRT, VTT, or Markdown.