Learn SQL: The Hands-on Guide with interactive exercises

As the first video of the course, this video introduces the lecturer of the course and provides a list of topics that will be explored and studied throughout this course.

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.

Test the student's knowledge of information related to this section.

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 quiz contains questions that can be answered with the knowledge provided in the lectures in this chapter.

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 quiz focuses on the topics that were explored in this section - the ability to modify SQL tables by adding, removing or updating rows.

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.

Test the student's knowledge of the different SQL functions, CASE clauses, and characteristics about the NULL value.