Before we can begin working with SQL, we need 'something' that utilizes SQL and uses it when managing databases. While there are many options, this video explains why we will be working with PostgreSQL and its benefits over most of the other alternatives.

Now that we know why we will be working with PostgreSQL, it is time to install it.

To be able to use a database management system such as PostgreSQL, we need a way to connect to it. There are a couple of options and we explore one of them in this video - connecting through the usage of pgAdmin 4.

A very important distinction needs to be made between a database management system (DBMS) and a database. However, a 'database' can often be mentioned interchangeably for both. This video provides some clarification on the topic and shows us how to create a database.

To finish off this section, we finally explore how to import actual data into the database we created in the last lecture. We also learn about some of the terminology used when describing this data.

This video introduces the first and one of the most important keywords in SQL - the SELECT keyword, which is used to specify what columns should be returned by a query. The lecture also explains some important concepts, including the case insensitivity for keywords and the case sensitivity for columns, and their best practices.

While it is important to get the data from a database, it is just as important to get only the data that we need from a database. In this video, we take a look at the WHERE keyword and some of its operations. This keyword allows us to return only the data that we care about from a query. Here we see how to filter based on numbers and strings, with more complex filtering coming in the next lectures.

This lecture explains how we can write multiple SQL statements that run at once and how to use comments in specific parts of our queries. While fetching data will often be achieved with a single query, other queries such as ones to modify records, create or delete tables and more complex operations will need multiple queries. Comments are also very important for documenting complex queries or commenting out queries to test alternatives.

In this video, we take our first look at SQL aggregate functions - functionality that allows us to gather some type of aggregate information from our tables. The most basic functions include finding the maximum or minimum value of a column, counting the number of rows that satisfy a query, and finding the average value of a column. The video also explains how to use these functions properly. 

We have already seen how to query, filter, and aggregate information from a table. In this lecture, we look at the next SQL feature, the capability to return unique rows after a query using the DISTINCT keyword. This means that if we filter by let's say a name, if there are 2 people with the same name, only one of them will be returned. We also look at how to combine DISTINCT with aggregate functions.

In the video that introduced the WHERE keyword, we saw how to filter rows by exact strings of data. In this video, we explore more complex ways to filter string data by using the LIKE keyword together with its 2 operators: % and _, which give us a wide range of possibilities for filtering data.

Continuing with extending our knowledge of filtering, in this video we learn how to filter data based on ranges of the data and whether the data is included in a list of values. While both of these things can be achieved without the BETWEEN and IN clauses, the query would become very big and redundant. These 2 keywords shorten and ease our queries. We are also shown the difference between the two and how to combine them.

When data from a query is returned, it is usually returned in a random order. This video shows us how to use the ORDER BY clause to sort the data we will receive based on specific columns in either descending or ascending order.

This lecture teaches us how to limit the amount of rows returned by a query as well as how to offset, or skip a couple of rows and fetch the rows after them. We are given a real-life example of where this can be used - to page data, fetching the first N rows, then fetching the next N rows, then the next, and so on. We also explore an alternative syntax to skipping/limiting rows as some SQL databases do not support the standard way of doing it.

This lecture explains how and when single and double quotes need to be used in SQL. We also understand the difference between them and why one type should not be used in place of another. We are also given a tip when it comes down to copying/pasting SQL queries from different platforms - quotes that may be used on one platform, such as PowerPoint or Word may end up being the incorrect font of quotes that SQL databases understand.

In the last lecture, we saw how quotes in SQL are to be used 'officially', unfortunately, some database systems have different quote functionalities than the standard ones. This lecture tells us about how MySQL and MariaDB's quotes differ from the standard.

This is the first lecture of this section, and it introduces us to what we will be learning in the next couple of lectures. The last section covered the SELECT clause, and this one will introduce us to the INSERT, UPDATE, and DELETE clauses.

In this video, we explore the INSERT clause, which allows us to add new data to our database. We will demonstrate how to insert a single element as well as multiple elements into a table. This video provides practical examples to help you understand how to use the INSERT clause effectively.

Updating data is an essential part of maintaining a good database. SQL gives us the UPDATE clause to achieve this. This video shows us the different ways we can use it, including combining it with the WHERE clause. We also learn the correct way to increment a counter column in cases where multiple simultaneous connections to the database may request a value to be updated.

The last lecture of this section introduces us to the DELETE clause, which allows us to remove table entries we no longer need. We learn that when using DELETE, it is crucial to include a WHERE clause to properly filter rows. Otherwise, we risk deleting all the entries in the table.

The very first lecture of this section introduces us to everything that we will be learning in the next couple of videos. This will include information about NULL, some of its quirks, and how to deal with them. We will study how to manipulate string and numeric values as well as how to write conditional statements in SQL, similar to an if-else statement in most programming languages.

In this session, we will explore NULL, covering its basics and challenges in programming and data handling. We'll examine how NULL affects data types, especially strings and numbers, and discuss practical ways to handle them to keep our code bug-free. We'll also cover using if-else statements to manage NULLs and common mistakes to avoid for smoother, more reliable programs.

This lecture discusses how aggregate functions like COUNT, MIN, and MAX handle null values in queries. It explains that nulls are ignored by these functions, making additional filtering unnecessary and enabling more efficient query writing. The lecture also clarifies the behavior of counting functions when dealing with null values.

Previously, we learned about NULL. Now, we’ll explore how to work with string functions, allowing us to modify string data before it's returned from our queries. We’ll cover different functions, such as getting the length of a string, or making all characters uppercase.

We'll now explore how to replace a string, or how to concatenate a string.

While NULL can sometimes be annoying to work with, this lecture introduces us to the COALESCE function, which can detect a NULL and replace it with a specified value. This gives us the ability to return specific values in our results, even when NULL would otherwise be the answer.

Most of the time, working with the default data types of the columns in a table is perfectly fine, however, on special occasions such as using the COALESCE function or concatenating columns, we need the columns we are using to be of the same value. The CAST function helps us achieve this by turning one data type into another.

After learning how to work with functions for NULL and string values, we now move on to working with numeric values. This lecture introduces us to addition, subtraction, multiplication, and division in SQL. We also explore some of the most used functions for numbers :: CEIL, which rounds a value up; FLOOR, which rounds a value down; and ROUND, which rounds a value to the nearest integer. We also learn how to generate a random numeric value with the RANDOM function and how to get the absolute value of a number with the ABS function.

This video introduces a very powerful clause that allows us to return values using conditional statements. This means that one column with one set of values could easily be modified to return a completely new set based on some condition. We are shown how to apply the CASE WHEN clause in a SELECT statement and a WHERE clause.

Just like the other section overviews, this one explores what we will be learning: how to create tables, how to modify their columns, and what data types we can work with. Both in the lecture and exercises, we will be building a table step-by-step, with each video introducing an important new concept.

This lecture introduces the first concept for this section: how to create tables. We are shown how to use the CREATE TABLE statement in its simplest form by providing just one column.

Then, we insert some data and query the table to retrieve the data we inserted.

Once a table has been created, we often need to update it as our needs change. This video explores some of the most common operations, including how to add a column, rename a column, change the data type of a column, and remove a column.

We now begin exploring some of the most important SQL data types, starting with VARCHAR, CHAR, and TEXT. In this section, we learn the differences between these three types, when and how to use each one. We are shown real-life scenarios where we might choose one over the other.

Another very common data type that is nearly always used is the integer type. Here we explore the three integer types provided by the SQL standard: SMALLINT, INT, and BIGINT. We look at a couple of use cases for each type and see how to set them as a column data type.

Continuing with our exploration of SQL data types, this lecture covers different types for working with numbers that have a decimal point (such as 3.65) instead of integers (whole numbers, such as 3). We look at the FLOAT and DOUBLE precision types, which are used when rounding errors are acceptable and high precision is needed. We also learn about the DECIMAL data type, which allows us to store numbers within specific ranges and prevent any rounding errors.

There may be cases when the columns in our table cannot be NULL because they are important for our applications to work.

One way to enforce this is with the NOT NULL constraint, which we explore in this video. We also learn that there may be situations where we cannot add this constraint without first fixing some of the column’s existing rows.

Sometimes we need a column to have either a true or false state. This can be achieved with the BOOLEAN data type, as explained in this lecture. We are also shown how to apply it to a column and set a value in it.

By default, when we don’t insert a value in a column, the database sets it to NULL. While this can sometimes be useful, a column being NULL is not the same as a column having an empty value, as we can see in this lecture. Here, you will learn how to set a default value for a column that is different from NULL.

A primary key allows us to identify columns in our table as important keys that benefit from a few additional constraints, which are explored in this lecture. Moreover, there are other crucial concepts related to primary keys that we will explore in future lectures. For now, this lecture provides the basics, allowing us to use primary keys in our tables

To prevent collisions from manually inserting ids, this video shows us how to utilize id generation strategies, allowing the database to automatically generate ids for us. We are shown two main generation strategies, with the primary difference being that the second one allows us to change the column’s value if needed.

In this bonus lecture, we take a look at a slightly controversial data type—the BLOB. This data type allows us to store data as raw binary, including images, videos, text files, and more. However, it comes with certain caveats, which the lecturer explains, along with an interesting alternative.

When we no longer need a table or want to start using a table from a clean slate, we can use the DROP and TRUNCATE commands, respectively. This lecture shows us how to utilize these commands and provides more information about them.

For the last lecture of this section, we will see how to perform most of the tasks we covered using SQL through the PostgreSQL-provided interface, pgAdmin4. We will learn how to create tables, add different columns with various data types, and modify them once the table has been created.

This video provides an overview of what this section will include—a hands-on approach to analyzing a real-world project and properly modeling its data. We will explore the different types of relationships between tables and how to reduce data redundancy if needed.

In this video, we create the first table for this section—the artists table. We focus on using the pgAdmin4 interface instead of writing our own SQL, as we did plenty of that in the last section. Additionally, we have the benefit of being able to see the actual SQL query that will be used for the table creation.

This lecture explores the first of three primary SQL table relationship types: one-to-many. We are given a real-world example of how this relationship works, as well as why and where we may use it.

Watching is good and all, but we also have to be doing! This is a video-type exercise, where you are given some requirements that you need to fulfill on your own, preferably through pgAdmin4’s interface.

This video provides a possible solution to the problem posed in the previous lecture.

Another relationship type we will explore now is the one-to-one relationship. In our example, this means an artist can have only one favorite track, and that track can be favorited by a single artist. We also explore how a one-to-one relationship might be a bit tricky and discuss some relatively simple solutions to address this issue.

The last relationship type we explore is the many-to-many relationship. This video provides an example using playlists, where one track can be in many playlists and many playlists can contain one track. We also see the usage of an intermediate table, also known as a junction table, which is necessary for many-to-many relationships.

Until now, we have always had our primary key limited to a single column with the name of id. However, this lecture introduces the concept of a compound primary key - something that allows us to make multiple columns a single primary key. We also see some of the potential benefits of using such keys.

In the last lecture of this section, we try to more compactly look at what this section has been exploring. A lot of what we learned is summarized in best practices.

At the end of the video, we are also shown additional topics related to data modeling that we may want to explore, as this is a complex and extensive field.

The first lecture of this section introduces a new concept that this entire section will focus on: subselects, also known as subqueries. We start with a quick explanation and then immediately jump into a practical example to show us how subqueries work.

Another practical example shows us how to work with subqueries. This time, however, we will be taking a look at the correlated subquery, a type of subquery that uses information from the outer query to evaluate its results. We see how we can combine information from multiple tables in our results.

To pave the way for the next lecture, we are introduced to a method for renaming tables within a query. This allows us to reference two instances of the same table (with different names) and shorten our queries by using shorter table names.

This video presents  how to write self-correlated subqueries - subqueries that work with the same table as the outer query.

In the video, we can see how this concept can be used to compile a form of statistic within the result. One of the many uses that self-correlated subqueries have.

This video explores another highly useful feature that subqueries unlock for us: filtering data based on information from other tables. We are shown two ways to achieve this, each offering different levels of efficiency and usability in specific situations.

Continuing the exploration of different subqueries in the WHERE clause from the last lecture, we now take a look at another possibility using the EXISTS clause. This builds on the use case presented in the last video.

We finish off this section with the introduction of the UNION clause. While it is not a subquery, it can still provide a useful way to combine two or more tables. It can even allow us to provide statistical data from multiple columns at once, as shown in the video.

In this section, we revisit how to combine data from two or more tables using JOINs, a widely used concept in the SQL world. This quick overview will introduce the types of JOINs we'll cover.

The first JOIN we will explore in this section is the CROSS JOIN, which is the Cartesian product of two tables. The lecturer shows us how to CROSS JOIN the students table with the courses table. An example of how to filter the results is also provided.

To optimize our somewhat inefficient join query from the last lecture, we are introduced to the INNER JOIN in this video.

The INNER JOIN allows us to combine the records of two tables only where rows have a matching condition between them, resulting in significantly smaller queries compared to the CROSS JOIN

To finish our exploration of different types of JOINs, this video introduces the last three: LEFT JOIN, RIGHT JOIN, and FULL JOIN. We can see the similarities between the LEFT JOIN and RIGHT JOIN, as well as the usefulness of all three.

At the end of the video, five diagrams showcasing all the types of JOINs we have learned so far are provided.

To finish off this section, we are provided with a few tips on choosing whether to use a subquery or a join, and in which situations one may be more beneficial than the other.

The first video of this section begins by introducing a very powerful clause used to aggregate data from our tables. The video shows a real-life example where we count how many students are enrolled in a course. While this can be achieved with a complex query, the GROUP BY clause makes everything much easier.

In the last video, we learned how to group records based on a single column. Now, we further this knowledge by grouping records based on two or more columns and using built-in functions that provide us with a whole new set of ways to group data.

Now that we have learned how to group data based on specific columns, we move on to learning how to filter the created groups based on either an aggregation function or one of the columns present in the HAVING clause.

Another concept of key importance is the execution order of an SQL query, which this video will explain. Why is that important?

By knowing how SQL orders different clauses, we can more easily visualize how a query will work and identify syntax errors before they occur.