The Ultimate Database Roadmap (SQL to NoSQL and Beyond) — Transcript
Full transcript
- 0:00This video will give you a complete road
- 0:02map on how you can master databases.
- 0:04It's going to cover topics which are
- 0:06beginner level, intermediate level, and
- 0:08advanced and go over everything that you
- 0:09need to learn in what order if you want
- 0:12to be comfortable working with
- 0:13databases. Now, quickly, I want to clear
- 0:15up a misconception. Databases are not
- 0:17just important to understand if you're a
- 0:19back-end developer or a data scientist
- 0:20or, you know, a data engineer. They're
- 0:22also important for front-end developers,
- 0:24full stack developers, really any type
- 0:26of developer to at least get a solid
- 0:28grasp of because you're going to use
- 0:30them in every single type of application
- 0:32that you write. And after all, if you
- 0:34think of what an application is, it's
- 0:36really just a flow of data. Saving data,
- 0:38updating data, deleting data, presenting
- 0:40data, that is really what software does.
- 0:43So, you really need to dive into the
- 0:44core here and understand what I would
- 0:46say is the most important part of any
- 0:47application, which is the data. So, with
- 0:49that said, let's get into it. Again,
- 0:51I've broken this video into three sets
- 0:53of skills: beginner, intermediate, and
- 0:55advanced. And if you want a list of all
- 0:56of these skills in a written format,
- 0:58simply click the link in the
- 0:59description. You can sign up for my
- 1:00newsletter, and I'll give it to you for
- 1:02free. Okay, let's get into it with the
- 1:04beginner skills. So, at the beginner
- 1:05level here, this is really all about
- 1:07understanding the foundations of
- 1:09databases. So, that starts with what a
- 1:11database actually is. What is a
- 1:14structured data store or data storage?
- 1:16And why would you use this compared to
- 1:18something like a spreadsheet, you know,
- 1:19or an Excel document, etc. Right? Now,
- 1:22after that, you want to look at the
- 1:23difference between relational and non-
- 1:25relational databases. So, what is a
- 1:27relational database? What does that
- 1:29mean? And quickly have a look at SQL
- 1:31versus NoSQL. Now, you don't need to be
- 1:33an expert here, but you should
- 1:34understand what SQL stands for. So,
- 1:36structured query language and what a
- 1:39database that uses SQL can kind of do
- 1:41versus one that doesn't. For example,
- 1:43you have very popular databases like
- 1:44MySQL or Postcris SQL or SQLite for
- 1:48example. And then you have ones like
- 1:49MongoDB or maybe Firebase for example,
- 1:52right? So what is kind of the core
- 1:54differences between those? You should
- 1:55have a bit of an understanding before
- 1:57you dive too far into the syntax and get
- 1:59into the next section here which is SQL
- 2:02fundamentals. Now I always suggest that
- 2:04people start by mastering SQL databases.
- 2:07If you understand SQL databases, it's a
- 2:09lot easier to get into NoSQL and to see
- 2:11the differences and the advantages. So,
- 2:13you want to start by understanding SQL,
- 2:15right, structured query language, and
- 2:17going over the common syntax there. So,
- 2:19what is a select statement? What is an
- 2:21insert statement? What is an update
- 2:22statement? What is a delete statement?
- 2:24And I would highly recommend that while
- 2:26you're doing this, you use one of two
- 2:27databases. Either a Postgress SQL
- 2:30database or a MySQL database. You can
- 2:32use SQLite as well, but these are the
- 2:34three most common databases. They're all
- 2:36very similar. So I would suggest just
- 2:38picking one. Most people go with
- 2:39Postgress SQL and do some experiments,
- 2:42write some data, update some delay data,
- 2:44sorry, and understand the basic syntax.
- 2:47Now once you have a little bit of
- 2:48understanding of what SQL is and some of
- 2:50the SQL syntax, you want to start
- 2:52looking at tables and schemas. Now a
- 2:54table is really just a structured
- 2:56collection of data. Should understand
- 2:58what that is. And a schema is
- 2:59essentially a blueprint that defines the
- 3:01table structure. Now this involves
- 3:03looking at things like data types. So
- 3:05like an integer versus a string versus
- 3:07text versus a date versus a boolean. You
- 3:10want to know what those different types
- 3:11of data are and how you can structure a
- 3:14schema for a table. Now after that you
- 3:16want to start looking at constraints.
- 3:18Now this involves primary keys, foreign
- 3:21keys, the unique constraint, things like
- 3:23not having a nullable value inside of
- 3:25the database. This is where you'll start
- 3:27to understand how to make your schemas a
- 3:28little bit more strict. And you get into
- 3:30again the basics of SQL where you're
- 3:32creating a schema, setting up some
- 3:34tables, and then we move into the next
- 3:35step which is the CRUD operations. CRUD
- 3:38stands for create, read, update, and
- 3:41delete. You need to know how to do that
- 3:43in SQL syntax. So for example, using the
- 3:45insert into syntax. If you're reading
- 3:48something using the select syntax, if
- 3:50you're updating something using, you
- 3:51know, update set, for example, if you're
- 3:53deleting something using delete from.
- 3:55Okay. Now once you look at those CRUD
- 3:57operations, you want to start getting
- 3:59into some filtering. So using things
- 4:01like the wear clause or the order by
- 4:03clause to limit the number of results
- 4:05and look at again a little bit more
- 4:06advanced SQL syntax. At this point you
- 4:09should understand what a SQL database
- 4:10is, how to create tables and schemas,
- 4:12how to insert data, read data and update
- 4:15data at a basic level. Now after that
- 4:17you want to start looking at
- 4:18relationships and keys. So SQL is really
- 4:21useful because you can have a lot of
- 4:23related data that's kind of connected to
- 4:25one another. You can have for example
- 4:27onetoone relationships, one to many
- 4:30relationships, many to many
- 4:31relationships. You need to understand
- 4:33what those are and how you can relate
- 4:35data together and start to do a little
- 4:37bit of database design. Now this is
- 4:39going to involve using primary keys,
- 4:41foreign keys, and again understanding
- 4:43when to use these different types of
- 4:44relationships in terms of how you kind
- 4:47of group data together. Now, along those
- 4:49same lines, you're going to want to look
- 4:50at cascading and things like updates and
- 4:52deletes with that related data. Now, I
- 4:54know that sounds like a lot, but that's
- 4:55really the bare minimum at the beginner
- 4:57level that everyone should understand.
- 4:59And that is the SQL fundamentals,
- 5:01understanding relational databases, and
- 5:03again, really understanding
- 5:05relationships, which are very, very
- 5:06important. Now, I suggest to close this
- 5:08section out that you work on some sample
- 5:10projects where you just work with a good
- 5:12amount of data in a SQL database and you
- 5:14do an exercise of, for example,
- 5:16designing your own database where you
- 5:18have maybe some users organized or
- 5:20connected with some orders, maybe you
- 5:21have some transactions or some payments.
- 5:23You can go to something like chat GBT
- 5:25ask it you know what's a simple SQL
- 5:27project I could practice with but you
- 5:28need to do a little bit of hands-on
- 5:30learning here and try to design
- 5:31something on your own so you can make
- 5:33all of those important decisions on you
- 5:35know what data type should I use what
- 5:37relationship should I have where should
- 5:38the foreign key live etc now let's keep
- 5:40going here into the intermediate level
- 5:43but you're going to realize that as you
- 5:44start working with databases it becomes
- 5:46important to use some tools to manage
- 5:48the databases right to visualize them
- 5:50work with them etc that's exactly where
- 5:52today's sponsor answer data grip from
- 5:55jet brains comes in. Data grip is an IDE
- 5:57built specifically for working with
- 5:59multiple databases all in one place. You
- 6:02can connect to almost anything like
- 6:04MySQL, Postgress SQL, SQLite, MongoDB,
- 6:08and dozens more and get an editor that's
- 6:10actually smart about your schemas and
- 6:12queries. It gives you schema
- 6:14autocomplete, instant error detection, a
- 6:17powerful data editor, and schema AI that
- 6:20lets you turn natural language into SQL.
- 6:23It can also suggest fixes for your
- 6:25queries, and do much more, all while
- 6:27understanding your actual database
- 6:29structure. You can browse data, edit
- 6:31rows directly, refactor SQL, and switch
- 6:34between different database engines
- 6:36without ever leaving the same window.
- 6:38Now, if you're learning databases or
- 6:40building apps that rely on more than one
- 6:42backend, mastering a tool like this can
- 6:44massively boost your productivity
- 6:46because it visualizes relationships,
- 6:48integrates with tools like Git and
- 6:50brings all of the productivity features
- 6:52you'd expect from a modern Jetrain IDE,
- 6:55and the best part is that it's now free
- 6:57for non-commercial use, which means it's
- 6:59perfect for students, hobby projects, or
- 7:02anyone who wants to get better at
- 7:03working with databases. Jetrains just
- 7:06announced this and it's awesome that you
- 7:07can use such a powerful tool completely
- 7:10for free. Try it out today by
- 7:12downloading data grip for free from the
- 7:14link in the description and see how much
- 7:16faster you can work across all of your
- 7:18databases. Thanks to Jetrains. Now let's
- 7:20get back into it. So let's continue here
- 7:22with the intermediate level which is
- 7:24really about designing databases for
- 7:26performance and reliability. This is
- 7:28where we get out of the absolute basics
- 7:29and we move into some more advanced
- 7:31topics. Now, the first thing that you're
- 7:32going to want to look at here is
- 7:33something called database joins and
- 7:35subqueries. Now, some might argue this
- 7:37is in the beginner level, but I think it
- 7:39can be a little bit more complex, which
- 7:40is why I put it here. Now, this allows
- 7:42you to essentially connect multiple
- 7:44tables together and to query information
- 7:46all at once and aggregate data in the
- 7:49same response. So, you can use things
- 7:50like the inner syntax, left join, right
- 7:53join, full joins. You need to understand
- 7:55what those are, how they work, and how
- 7:57you can make them the most efficient.
- 7:59Now you also want to start looking into
- 8:00subqueries for example using nested
- 8:02select statements and getting into using
- 8:04aliases for readability of the database.
- 8:07Now after that you want to start looking
- 8:08at indexes and query optimization. It's
- 8:11one thing to query the database. It's
- 8:12another to do this in an efficient
- 8:14manner and that's really where indexes
- 8:16come in in terms of how quickly you can
- 8:18look information up in the database. You
- 8:20want to also start looking at trade-offs
- 8:22like write speed versus read performance
- 8:25and the way in which you design your
- 8:26database so that you know am I going to
- 8:28do a lot of writes am I going to do a
- 8:30lot of reads and what kind of operation
- 8:32am I optimizing for. Now you also want
- 8:34to understand things like the explain
- 8:36query and how you can analyze queries
- 8:38for performance. Next you want to start
- 8:40looking at transactions. Now, this
- 8:42involves using things like the commit
- 8:44and roll back syntax and having
- 8:46different isolation levels and
- 8:48concurrency controls based on how you're
- 8:50setting up your database. Now, you
- 8:52should understand common transaction use
- 8:53cases, things like bank transfers,
- 8:55inventory updates, etc. Next, we move to
- 8:59data normalization. Now, normalization
- 9:01helps prevent redundancy in the database
- 9:04and you have different normal forms in
- 9:06your database. You have 1 NF, 3 NF, and
- 9:08you need to understand what those are.
- 9:10Now, you also need to know when to
- 9:12denormalize for performance in your
- 9:14database. And I know a lot of this
- 9:15sounds vague. I'm not trying to teach
- 9:16you these concepts. I'm just giving you
- 9:18a list of things you can essentially
- 9:19look up to figure out what I'm talking
- 9:21about. Right. Next on my list, we have
- 9:23referential integrity and constraints.
- 9:25So, essentially enforcing different
- 9:27relationships through various foreign
- 9:29keys, things like cascading updates and
- 9:31deletes, but looking at it on a deeper
- 9:33level that we did before, and handling
- 9:35constraint violations. Nearing the end
- 9:37of this intermediate section, I have
- 9:39things like query optimization
- 9:40techniques. So, how to use indexes and
- 9:42multiple indexes effectively, avoiding,
- 9:45you know, selecting asterisks from the
- 9:47database, like why is that a bad thing
- 9:48to do, limiting the results sets, using
- 9:51proper wear clauses, caching and
- 9:53pagionation considerations, and how you
- 9:55can make your queries as optimal and as
- 9:57fast as possible. In the beginner level,
- 9:59you're just trying to get the data that
- 10:01you need. In the intermediate level, you
- 10:03really need to think about how can I
- 10:04make sure this is as optimal as possible
- 10:06and I'm not wasting resources or doing
- 10:08something that you know takes an
- 10:09unnecessarily long amount of time. Now
- 10:12lastly, we get into a kind of a real
- 10:13world example here. So I always suggest
- 10:15again building something working on a
- 10:18project to learn these types of topics.
- 10:19So if you could design for example a
- 10:21small e-commerce schema where you have
- 10:23like products, orders, users, reviews,
- 10:26maybe some kind of inventory and then
- 10:28you write you know complex queries to
- 10:30join and aggregate all of this data
- 10:32together. You have the correct
- 10:33constraints. You add a bunch of data.
- 10:35You actually connect to it and maybe
- 10:36present it in a project. That's a great
- 10:38way to learn some of these more advanced
- 10:40topics. Anyways, that wraps up the
- 10:42intermediate section. Again, I know it's
- 10:44a lot of stuff. It might sound
- 10:45complicated, but now we're going to move
- 10:47on to the advanced level. Now the
- 10:49advanced level is really about scaling
- 10:51and modern data architectures. So before
- 10:54we're talking more about kind of query
- 10:55optimizations and stuff on just the data
- 10:58level. Now we're looking at really how
- 11:00do we scale the system, right? How do we
- 11:01scale databases? And this is more
- 11:03architectural where we get into more
- 11:05senior level skills. Full disclosure,
- 11:07I'm not a complete expert in this area,
- 11:09but I just am aware of a lot of these
- 11:11skills and I've seen them pop up with
- 11:12people that I work with or places that I
- 11:14worked at before. So that's why I'm
- 11:16mentioning them here. Okay. So first is
- 11:18scaling databases and vertical scaling
- 11:21versus horizontal scaling. So when do
- 11:23you add more compute versus when do you
- 11:25add more resources and start doing
- 11:27things like sharding, partitioning and
- 11:29replicating your databases, right? This
- 11:31is things like read replicas versus
- 11:33write masters and getting into again
- 11:36more complex architecture where you have
- 11:38multiple machines in this distributed
- 11:40system. Next we have replication and
- 11:42backups. So when do you back up the
- 11:44database? what data needs to be backed
- 11:46up and how do you make sure that
- 11:47everything you have is always available
- 11:49and fault tolerant. Now this also leads
- 11:52into various backup strategies. So like
- 11:54full backups, incremental backups, point
- 11:56in time recovery. You need to look at
- 11:58all of those strategies. You also need
- 12:00to look at failover and again high
- 12:02availability patterns so that if one
- 12:03database goes down your data is still
- 12:05accessible. Next we look at transactions
- 12:08and scale. So essentially distributed
- 12:10transactions and two-phase commits. This
- 12:13means eventual consistency versus strong
- 12:16consistency. Looking at things like the
- 12:18cap theorem. So consistency,
- 12:20availability, and partition tolerance.
- 12:22Again, I'm not an expert in this field,
- 12:24but these are some things that you
- 12:25should have a look at. Next, we get into
- 12:27NoSQL concepts. So notice that almost
- 12:29everything I mentioned so far relates to
- 12:31SQL databases. That's intentional
- 12:33because that's really where you're going
- 12:34to learn the most about databases and
- 12:36architecture. And then I suggest again
- 12:38once we've learned all of that to start
- 12:40looking more into NoSQL databases. So to
- 12:43understand why NoSQL database exists to
- 12:45understand the flexibility and the
- 12:47scaling uh kind of problems or
- 12:49potentially advantages of using a NoSQL
- 12:51database and then starting to look at
- 12:53things like key value stores so things
- 12:55like a ready's database right which
- 12:57maybe you would refer to as NoSQL but
- 12:59really it's a key value database and
- 13:00when that can kind of fill in or come
- 13:02into your system also looking at
- 13:04document databases again no SQL
- 13:06databases like MongoDB or things like
- 13:09Cassandra where you have you know column
- 13:10or stores right even things like vector
- 13:12databases or graph databases. This is
- 13:15where you start to kind of spread out a
- 13:17little bit and look at all of the
- 13:18different offerings and particularly
- 13:20those strengths and weaknesses and being
- 13:22able to decide which one you actually
- 13:24want to use. Now, that leads us into the
- 13:26next section here, which is hybrid data
- 13:28models. So, understanding when you want
- 13:30to mix multiple databases together and
- 13:33use a SQL database plus a NoSQL
- 13:35database, for example. When do we have
- 13:37readies for caching? when do we have a
- 13:39NoSQL database for you know data that's
- 13:41constantly changing when do we have
- 13:42strict relational databases and what's
- 13:44the uses for those right also how do we
- 13:47log all of this information and what
- 13:49database is just the best for what
- 13:50purpose so you need to understand the
- 13:52trade-offs between the consistency and
- 13:54the flexibility of these services and be
- 13:57able to argue why you should use one
- 13:59over the other. Next we get into
- 14:01performance and observability, right? So
- 14:03things like query profiling, being able
- 14:05to view all of the logs. You have things
- 14:07like monitoring performance. So being
- 14:09able to look at the CPU, the input
- 14:10output, the various connections, the
- 14:12network uh usage for your different
- 14:14machines and resources. This also leads
- 14:16into caching strategies. So having
- 14:18in-memory caches versus distributed
- 14:20caches. And then we look at things like
- 14:22schema migration management, especially
- 14:24in production environments. How do we
- 14:25make sure that if we adjust the
- 14:27database, we don't break everything? and
- 14:28how do we migrate large portions of data
- 14:31and set ourselves up for success when we
- 14:33want to do that in the future. Now,
- 14:35there is a lot of stuff here and again
- 14:36as we get further into this list, I
- 14:38become less and less of an expert, but
- 14:40these are things that you need to look
- 14:41at if you really want to get into more
- 14:43of a backend heavy role, a data
- 14:44engineering role. And a lot of what
- 14:46you're doing is working with databases.
- 14:48For most people, up until kind of the
- 14:50middle of that intermediate section is
- 14:52where I would suggest being. If you can
- 14:54learn more, obviously, that's fantastic.
- 14:56But the skills in this section are
- 14:58really if you want to go above and
- 14:59beyond. So with that said, that's going
- 15:00to wrap up this video. I hope you
- 15:02enjoyed. If you did, make sure to leave
- 15:03a like, subscribe to the channel, and I
- 15:05will see you in the next one.
- 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.