Learn Basic SQL in 3.5 hrs | Complete SQL Beginner Course

This video is sponsored by Brilliant. Hello everyone! If you want to learn SQL from scratch, and you want to start using SQL at your job, you want to clear SQL interviews, and you want to be able to solve basic SQL queries, then this is the only video that you will need to watch.

A quick overview of everything that we'll be doing in this video: We are first going to install a database and the tools that are required. The database that I'm going to be choosing is PostgreSQL database because it is one of my favorites. You can choose any other database of your choice; any other relational database. Everything that you'll be learning in this video will be applicable to any other relational database. Once we have the database and the tools set up, we'll create a new database. We'll then talk about tables and data types. We'll create some sample tables, and then we will see how to load, read, modify, and remove data. Once we have this basic understanding, we'll then talk about constraints, create a table with constraints, see how relationships are formed between tables using constraints, and understand all the basic things that you need before you dive into writing SQL queries.

Now, once you have all of this basic understanding, then you're all set to start solving the case study that I have, which is the sales order data set. We are going to be solving 20 basic SQL queries. Now, these basic SQL queries will give you complete understanding of all the important basic SQL concepts, and once you are able to solve these queries, you can then straight away get into any other platforms like LeetCode or StrataScratch or some other to solve basic SQL problems.

Now, once you have this understanding, then I also going to mention you the next steps; the next concepts that you basically need to learn, kind of like the intermediate concepts that you need to learn in order to kind of solidify your SQL knowledge. Okay, having said that, straight away let's get into installing our database.

Okay, to install PostgreSQL database, just go to the postgresql.org website. This is the official PostgreSQL website. Then click on the download button. You will see all the different operating systems where you can install PostgreSQL. Now I am using macOS, but let's say if you're using Windows, the installation method between Windows and Mac is going to be identical. For example, if you click on Windows, you see the option to install it using the EDB method, the interactive installer by EDB. Here you have this first link: download the installer. Just click on that, and it should download the .exe file. You can then click on the .exe file to install the database. Okay, and this is the only way you can install it in Windows. But if I go back and if I click on macOS, I have a few different options. So I have the same interactive installer by EDB method to basically download and install the PostgreSQL, but I also have some other methods like PostgreSQL app, Homebrew, and a few others. Okay. Now, all of these methods will basically install PostgreSQL, and you can start using the database. It does not matter which method you use, but I will be using the EDB method because it's going to be identical between Windows and macOS, so that you know how the installation works.

Now I'll just click the download the installer link, and it should show me all the different versions of PostgreSQL that is currently supported. Now I'll choose the latest version, 16.4. It is supported on macOS as well as on Windows, so I'll just click on the macOS option here, and it should start downloading the .dmg file. Once it is downloaded, just click on this file; it should, I think, open something like this, and here you can just double click on this, and it should basically pop up a window. But before that, you need to, I think, open this right, and then you need to pass enter your computer password. So I'm just going to provide that, and then I think it should open a popup for your installation, right? And this is exactly how you will see this in Windows as well, right? So I'll just enter next, and I'm not going to be changing any default values here. It's going to be exactly the same. The only change that I'll do is when installing; it's basically asking me that what are the different things or tools that you want to install. So PostgreSQL Server basically means it's the database that we need. PGAdmin is basically the tool, so we don't need to install the tool separately; it comes bundled together with the same EDB package. Then I have Stack Builder. Now I don't need Stack Builder; I'll not be using it, so I'll uncheck that. Okay, and then I have the command line tools. I might use it in the future, so I'll just keep it. Okay, so I'll just say next, and everything stays the same. And this is important: the password that you mention here is going to be the password that you will need to enter when you log into PGAdmin tool when you when you're trying to connect to the PostgreSQL Server, right? So this password you need to remember. So I'm just going to give a simple password here, and then I'll just click next, next. Don't change the port; everything can stay as it is; just click next, and it should start then installation, and this should basically take a few seconds. Okay, so the installation is complete. Click on finish, and that's all.

Now, in order to verify if the installation is done or not, you can just try to open the PGAdmin tool. I'm on Mac, so I'm just going to say command space to search for the application. I'll say PGAdmin. Now you can see that it's already installed. I'll just open it, and you can see that it's trying to open. Okay, this might take a few seconds. Okay, so the PGAdmin tool is open. On the left-hand side, you see that we have servers. I have two different servers; I actually installed 16, but 15 is also there. I think it's basically remaining from my previous installation; I probably did not remove it properly. But anyways, now let me open the 16. I'll just click on the 16, and you will see that by default there is one database that is already there. Okay, so if I click on database, if I see here, I can see that there is something called postgres. Right now, this postgres is a default database that comes with your PostgreSQL database installation. Okay, if you're using Oracle or MySQL, I think the default database is this, and every database will come with some default database. Okay. Right-click on this postgres database name and just click on the query tool. So it will open a session or a page where you can start writing your SQL scripts to perform certain activities. Okay. Now, just to show you what this database already comes with, I'll just write a query like `SELECT * FROM information_schema.tables`. Now, here `SELECT * FROM` is the SQL command that will tell the database to read something, read the data from the database, and then the `information_schema` is kind of like the database or the schema where all the default objects are stored, which is used to run the database or maintain the database, right? And then `tables` is basically the name of the table which will provide you the list of all the tables that is available in this database. Okay, so if I just run this command, you can see that I have 209 rows; that means there are 209 objects that are already created in this database. Okay, some of them are tables; base table basically means table; some of them are views, and then you have the database name, the schema name, and the table name here, right? Now, this is not important for us; I just wanted to show you that even when you install a database, when you think it is empty, it's actually not empty; it comes with some pre-installed objects which are required to maintain your database. Okay. Now, what we actually want to do is we want to create a new database, and we are going to be using that database to basically do all the learning. Okay, so in order to create a new database, I'm just going to run the command like `CREATE DATABASE demo;`. I'm going to give it the database name `demo`. I'll run this command. You can see that the `CREATE DATABASE` is successful. Now just go to your database on the left, right-click, click refresh, and you will see that new database that you created. You can give any name of your choice. Okay. Now right-click on `demo` and just click on query tool. It should again open another page or another session which is basically connected to the `demo` database. So whatever you do here, it will basically impact your `demo` database. Okay, and that's exactly what we want.

Now, in the beginning, I told you that if you want to learn basic SQL, this is the only video that you will need to watch. But what if you want to learn something else, maybe statistics or data analysis or math or science? Right? That is where the sponsor of today's video comes in, which is Brilliant. Now Brilliant is an online learning platform unlike any other you can find online. Now, in my opinion, the learning experience that you get on Brilliant is kind of unmatched compared to any other platform, and I have a very good reason on why I'm saying that. So recently I did this Statistics Fundamentals course on Brilliant, and I was kind of amazed by the sheer quality and the user interface and the whole storytelling part of this course. It felt like I was actually not doing a course on statistics, but I was kind of like playing a game using statistics. So all the different concepts that we generally use, like mean, median, standard deviations, all the different graphs like histogram, bar chart, line chart, etc., were explained so neatly, so clearly, using precise examples and some realistic data sets, and the whole storytelling part throughout the course was so much engaging that you kind of felt involved in the course, and you were interested, and you just wanted to complete the current session and move on to the next session and just finish the whole thing. The whole time doing this course, it just felt that all these concepts that I was learning were so simple, and I just wanted to move to the next step, learn the next concept, and just finish it. And I've never experienced this kind of intuitive learning and this kind of engaging learning doing any courses online, right? So now if you think that I'm exaggerating this one and you do not really trust me, you can check it out yourself. You will find a link in the video description, and by using that link, you will get a 30-day free access to Brilliant. So you can click on the link, get access to Brilliant 30 days for free, do any course of your choice, and try to check it yourself on how amazing the learning experience on Brilliant is, right? And after the trial period, if you want to purchase any courses, you can do that using my link; you will get a 20% off. You will find it in the video description, or you can scan the QR code that is shown here. Thank you, Brilliant, for sponsoring this video.

Now let's get back into our tutorial. Now we have installed the PostgreSQL database; we have the PGAdmin tool; we have created the new database by the name `demo`, and we have connected to it, right? Now next we can start learning all the different concepts, but the very first thing that we want to learn is what is a database, right? Now I've been already mentioning database a few times, but what exactly is a database? Database is basically a container that stores data. So consider milk; if you want to store milk, you can basically store it in a vessel, in a bottle, etc. If you want to store, let's say, books, you can store it in a box or in some shelf, etc. If you want to store clothes, you could probably store it in a cupboard or some other location at your home, right? Same way, if we want to store data, then data can be stored in a database, and that is why you can refer to it like a container that stores data. That's it. Okay, let's not use any other complex terms here.

Now there are two types of, mainly two types of database: relational database as well as non-relational database. We will mainly be focusing on relational database in this video because SQL is used only in relational database. Okay, now SQL is also used, kind of SQL is also used in non-relational database; we'll not get into that, but mainly SQL is used in relational database. So basically this means that the database that we are working on here is called as relational database, right? Now why exactly is this database called as relational? The reason is the data that is stored, data that is stored in this database is related to one another, related to one another. The only reason why this data is called as relational is because data is related to one another; that means there is a relation that has been formed between the data that is stored here. Okay. Now how exactly data is actually stored here? Data is stored in tables. Okay. Now what exactly is a table? Table is something like you can consider table to be something like an Excel file. If you have opened an Excel file, you would see that you have a combination of rows and columns, and tables are something like that. Okay, we will create a table and we'll see some examples, but in short, just remember that we are working with relational database; relational databases use SQL; and relational database can store data, and the data that is stored will be related to one another. Okay, and all the data that we have is actually stored in tables. These tables are created inside the relational database. Okay.

Now how do we create a table? What it is, everything we'll learn in the next session. Now let's look at a table in a relational database and understand all the different functionalities that come along with the table. So the first thing is I'll open a new session here so that I can create a sample table. It's going to be a very simple table, and I'm going to store information related to different products. So in order to create a table, the command is `CREATE TABLE`, and then the table name. In this case, my table name is going to be `products`. You can give any table name of your choice, depending on what data you want to store. There are, of course, a few rules and regulations like you cannot start the table name with a special character; you cannot have spaces; and a few other things, but I'll not be talking about all of those things; you can easily Google it. We'll straight away create a simple table. Okay. Now once you enter the table name, you need to open the parenthesis, and within this open and close parenthesis, this is where you need to mention the different columns the table is going to store. Now I told you previously that table is basically a combination of columns and rows. Now you'll better understand this once we start inserting data into this table, but first let's create some columns. Now whenever you think of a product, what is it that generally, let's say if you want to store information related to a product in in a store, what is the information that you would like to store? So ideally you would basically want a name, right? You would basically want a product name, right? So let's say I will just say `product_name`, and then you would probably need its price, right? What is the price of this product? And then you would probably need, let's say, when was this product released, right? So I'll say `released_date`. Okay. Now the thing is you have the `product_name` here, but you could have a lot of different products that you would be storing in this table. So ideally it's always good that you also have a unique identifier which represents each of these products. Okay. So initially I'm also going to have something like a `product_code`. Okay, and this `product_code` should basically be like a unique identifier which is going to identify each product. Okay, so each product would have its own code, something like that. Okay. Now these are the four information that I would like to store in my `products` table. So this is fine. So you create a table; you provide all the different columns, or when you say columns, it's basically the information that you want to store in that table. But when it comes to a database, it's not just about providing a column name or a column, but you also need to tell to the database what type of data will each of this column hold. For example, when I say `product_code`, let's say I'm just going to make it like an integer. Okay, so it's going to start from one and go uh until whatever number of products you have. Okay, but each product would have a unique value, something like that. When it comes to a `product_name`, what what what do you mean by a product name? Let's say in this `product` table we're going to store information about all the different Apple products. Okay, so iPhone, MacBook Pros, AirPods, etc. So these names are basically alphabets, right? Because they have English alphabets in them. Okay, and they could have some numeric values as well, like if I say iPhone 15, then you have a numeric value and you have alphabets; you have spaces, etc., right? So whenever you want to store this kind of a value which is a combination of alphanumeric value with some special characters, etc., then the data type we generally use is something called as a `VARCHAR`. Now within `VARCHAR`, you also need to provide what is the maximum length of that particular value. So if I mention `VARCHAR(50)`, that means your `product_name` cannot exceed 50 characters. Okay, you can of course give more than this, but I know that I do not have any product which is going to have a name more than 50 characters, so I think this is more than enough, right? So this is my data type. So whenever I want to store some string values or any alphanumeric values with special characters, spaces, etc., then we generally use `VARCHAR`. Whenever I want to store like a numeric value, only numbers or digits, right, then we use the data type like `INT`, which basically stands for integer. Okay, and then when it comes to price, now price could be a whole number, or you could have some decimal values, right? Like the price could be, let's say, 999.9, right? So in that case, I cannot not use the data type like integer, but it needs to be a data type that can store decimal values. Okay, and in PostgreSQL we can generally use `FLOAT` as a data type for that. Okay, now, and finally we have date. Now `released_date`, as you know, it's it's going to basically be a date, right? So whenever you want to store a value that is in the form of a date, then we generally use the data type called as `DATE`. Okay. Now you might be asking what does actually data type mean? Now data type, you can simply understand it to be like a rule or a data rule that is specific to each column. So whenever you create a column in a table, each column has to be associated with a data type; that is kind of like a data rule that this column can only store values which are satisfied by this data type. So if you make a column like `INT`, then it means this column can only hold integer values. If you make a column like `VARCHAR`, that means this column can store string values. If you make `FLOAT`, that means this column can store float values or decimal values, right? So something like that. I hope you understand. The last thing that we need to do here is between each column you need to separate it by a comma. So after the data type here, put a comma here, put a comma at the end here, and put a comma at the end here. You don't need a comma in the last column because after this there is no other column. Right? End the whole statement with a semicolon, and now we can just run it, and now you can see that the table is created. Now what we have done is we had created the database; we log, log into this `demo` database; and then we created the `products` table. Now in order to, so we have created the table; it's just an empty table; we have not actually loaded any data here. But in order to see how this table actually looks like, what we can do is we can read the data from that table. Okay, so I can just tell `SELECT * FROM products;`. Now `SELECT * FROM` basically tells SQL or tells the database to go to the database, look for this table, and return whatever data is present in that table, all the columns, all the rows, etc. So if I run it, you can see that I am getting the list of columns here, the four columns that I created, but

There is no data here. If data was present, it would be showing up here right, because we just created an empty table. There is no data that is inserted yet, right? So we have created the table; we now know how to read the data from our table, right. The next thing is, how do we start loading data into this table in SQL?

In order to load data into the dat database—that means, to load data into a table—we generally use the command called as `insert into`. Okay, so we say `insert into` the table name. That is, in our case, the table name is `products`. Then you provide the keyword `like values`, and then you provide the value. Okay, but here, before the values, you can also provide the column names. This is optional, but I'll provide it here. So I need to provide all the different columns to which I want to load the data. So I have `product code`, I have `product name`, I have `price`, and finally, I have the `release date`, right? These four columns, and then I need to provide the values. I'll just put it into the next line. I need to mention value into each of these columns. So let's say, for `product code`, I'm just going to say `one`, because I know it's an integer; `product name`, let's say I'm going to say it like `iPhone 15`; okay, and then the `price`, let's say it's going to be $9.99, okay, and let's say [Music] 9995 okay, or something like that, okay, because it's a float; and then the `release date`, let's say this was released on 22nd August 2023.

The one thing that you need to remember is, whenever you are providing a value which is integer or a float, you don't need to provide it with inside the single codes, but if the value is a string, then we generally provide it inside the single codes. Okay, so this is very important. If I did not put single codes here, then I would get an error. Okay, whereas a date, I'm just providing it like this, like a string, because you see the format here is in the form of a date. Let's see if this is—if this gets accepted by SQL or not. Okay, so if I run it, you can see that I'm getting an error, and I think the error is because here I have given the name as `released date`, whereas I actually wanted it to be named like `release`. It's a typo, but let me go with the typo for now. Okay, so I'll just run this command now, and I'm still getting an error, uh, because it's telling that this value is out of range. Now this is strange, right? This—how does this become out of range? 28th, 22nd August should be valid, right? The reason is because I have just provided this value, but I did not tell SQL in which format should it treat it. I think the default format in this database here is month, date, and year. Okay, so it's looking at 22 as a month, but we do not have any month like 22, right? So in order to fix that, ideally what we do is we can, whenever we are providing a date, it's always better to provide it using some function, okay, inbuilt function. Now `to_date` is an inbuilt function. What it—what basically a function is, in the simplest term, is a function is like, let's say it's a collection of code, okay, it's a block of code that whenever you basically need—whenever you execute it, it's going to return you something. Okay, so I'll tell you in detail. So let's say I'm going to give a format here. I'm just going to say this is going to be DD, MM, and YYYY. Okay, and let's say I'll close this parenthesis now. What this means is, this is my function. This function is accepting two input values. Okay, whatever—whenever you see a comma here, it basically is like a separator for different values, or in this case, we call it like an argument or a parameter. Okay, and I'm passing two values here. So the first value or the first argument here is basically the date itself, and the second argument here is basically the format. Okay, so whenever we provide this two arguments into this function `to_date`, it's going to take this value and it's going to convert this in this format, and it's going to return a value that will be in the form of a date. Okay, that is what the `to_date` function does. Now there are many other functions in SQL. Now I'll not be talking about all the inbuilt functions, but going forward, we will be looking at a couple of inbuilt functions. Okay, but in the simplest terms, remember, a function is a block of code that when you execute it will return something. In this case, when I execute this, it's going to accept this date value, okay, this value, convert it into this format, and it's going to return a value that will be in the form of a date that SQL understands. Okay, so now let me try to run it. If we'll see if it works, and you can see that it is actually working. Okay, so I have inserted one record. Now let me insert a couple of more records, just so that we have some data.

Okay, so I have iPhone 15. Let's say the next thing that I want to insert is MacBook Pro 16, okay, uh, and then let's say the next one is going to be AirPods, okay, and okay. So for MacBook Pro, let's say I'm just going to make it 2,000, okay, and then for AirPods, let's say I'll make it 400, and the date could be, let's say 25th, 7, 2021, and let's say this could be 02, 02, and 2021, okay, or let's say 2022, and let me run these two in records as well. So it is inserted. So now I have inserted one record here, the second one here, and the third one here, right? So we have the table created in the database. I have loaded some data into the database. Now, in order to read that data, I can just run this command: `select * from products`. So when I run it now, you can see that I have that data which I inserted here, which it is present here, right? So this is basically how we load data into the table.

A couple of things I want to mention here is: you see, we have 999.9. It's because, since it's a float data type, decimal values are accepted here, but if I had provided 999.9 in the integer column, it would—it would basically not accept decimal values; it would convert it into a whole number. Okay, you can try it out yourself. And here you see that, even though this looks like a string, this is actually a date data type. Okay, it's in the form of a—the format here is of a date. Now we have loaded data into this table, sample records. Now let's look at how do we read different sets of data from this particular table. So when we do `select * from products`, I told you that `*` basically tells to read all the columns in the table, and here I do not have any filter conditions or anything, so it basically returns and reads all the data. Okay, so all the columns and all the records, right. Now let's say I did not want to read all the records; I only wanted to read the records where, let's say, say the price is above $1,000, uh, dollar. Okay, let's say this currency is dollars. In that case, what I can do is I can just write the statement like `select * from products`, and I can put a filter condition saying that `where`, uh, let's say `price` is greater than 1,000. Okay. Now what this statement basically tells is: `select` is basically—it's a keyword which tells which columns the SQL statement should return. Okay, and if I—if I put `*`, it basically tells SQL to read all the columns here. Instead of `*`, I could also specify the column names, but we will get to that later. `from` basically tells SQL, okay, I need to read all the columns from which table, and that is what I need to mention here. Okay, so `from` is basically used to specify the table from where you want to read the data. Here you mention the table name, and then, in order to apply filter conditions, we use this `where` keyword. Okay, so this `where` basically is kind of like a filter that you apply, saying that fetch only those records from the `products` table wherever the `price` is greater than 1,000. Okay, so if I run this, and if you look at this data here, there is only one record which is having price greater than 1,000, so hopefully that should get returned, right? So if I run it, you can see that only that is getting returned. So this is one way of just a simple filter. Okay. Now we'll see a few different queries just to understand how do we read basic data from one table.

Now let's say I did not want to fetch all the different, uh, columns from this table. So in that case, what I could do is I can just copy the same query here; I can put it here. In of `*`, I can just tell which column do I want—want to read. So I can just tell `product name`, and if I run it, I'm still getting the same record, but it's only returning me one column, that is a `product name`. Now, same way, let's say I had this `select * from products`, and I told you that this kind of reads all the columns from the table, so there are no filter conditions; every record is written, but if I only wanted to read, let's say one column, so that is the `product code`, okay, then I could run this, and it will only return me one column. Okay, no filters; every record is returned—returned, but just the column that I mentioned here.

Now let's look at another scenario where I want to read all the data from this table, but I only want to fetch products which were released during a particular year, let's say 2023. Okay, so ideally what I want is, I want my filter condition to say `where` the `released date` is equal to 2023. Okay, so this is basically what I want. Now, before I run this, let me look at the whole data in this table. You can see that, out of three records, there's only one product which were released in 2023, so ideally this record should get returned, right? So if I run this SQL query, it's going to throw me an error, because the problem is with the type cast. Now what this basically means is, this `release date` belongs to the data type `date`, whereas this 2023, for SQL, it's an integer, because I just passed the value here; I did not put it inside the single codes. Now the same value, if I put it inside single codes, then SQL is going to treat it like a `varchar`, okay, or a string, but here what's happening is: left-hand side is a date; right-hand side is an integer; it's basically not compatible, and that is why it's throwing me an error. Okay. Now how I can fix this is: so whenever you're trying to compare values, the data type has to be the same when you're comparing two values. Okay, so that SQL can understand it. Now in this case, what I want to do is, from this `release date`, which kind of looks like this, right, so there is DD, MM, and YYYY, I only want to fetch the year. So I can use another inbuilt function, which is going to be called as `to_char`. Okay. Now `to_char`, as the name suggests, is basically trying to convert from any value—in this case, from a date—to a character. Okay, so that is a `varchar`. So here I need to pass two arguments: the first argument is basically the date value itself; the second argument is actually in what format I want it to return. So here I'll just tell YYYY. So `to_char` is an inbuilt function; it accepts a date value, and it will return a character value in whatever format I have given. So the format here I'm given is only YYYY, because I'm only interested in the year, right? So this, when I run this, it's going—going to return me only these four characters, the year from each of that value that is present in this particular column, and I want it to match with the 2023. I'm going to pass it inside the single codes, so that SQL is going to treat it like a `varchar`. So here this is a `varchar`, and this is also `varchar` or a string, whatever you call it, and now if I run it, it's going to work. Okay, so now this is fetching me the record where the `release date` is of 2023. Okay, so this is one way. Whenever you are working with date, uh, columns, it's—I think in most cases you would generally be using some of the other inbuilt functions. So if you want to convert a string into a date, then you use the `to_date` function; if you want to convert, uh, date into a `varchar` or a string, then we use a `to_char` function. Now these two functions are compatible in PostgreSQL and in Oracle database. If you're using Microsoft SQL Server or MySQL or some other, there will be alternative functions to this. Okay, you can just Google like, let's say if you are in using SQL Server, then just Google the alternate to `to_date` function in SQL Server, and you would basically get the result. Okay, so this is how we can filter the data based on date column. Now there is another function which is specific to PostgreSQL which I can use to extract or basically filter based on the date column, and how I can do that is I can just tell the same `select * from products`, and I can filter, but instead of using the `to_char` function, I can use another function called as `extract`, and in `extract`, I can say here from this particular column, okay, `released`, okay, now, and then I can do the same kind of matching that is. Now what this function does is: now `extract` is also another inbuilt function, so when you execute this `extract` function, it will accept some values, and it will return some result. Okay, in this case what it is accepting is: it is accepting this `released date` value, and from this date it's actually fetching the year, so it's fetching the year from this date, and it will return that year. Okay, and I'm matching it with 2023. So now if I just run this, you can see that I'm still getting the same output. Okay, so two different functions, but they kind of are performing the same task here. Okay, so this is something that could be useful for you whenever you're working with date functions in the future.

Now quickly, I also want to talk about aliases. So let's say I'm going to copy this same query that I have written here: fetch the products, uh, which are greater than 1,000 in price, and if I run this query, I'm getting this result: MacBook Pro, and the column name is `product name`, right? But let's say, instead of `product name`, I wanted to display this column name as something else, let's say I just want it to be displayed like `product`. And if I run it, it—you can now see the column is displayed like `product`. Now what you see here is basically called as an alias. Okay. Now alias, you can simply treat it like a nickname. So this is the actual column name, but you can provide a different name for the purpose of displaying. Okay, so if you want this column to be displayed something else, then you can use the `as` keyword and just mention it. So instead of `product`, if I can—let's say if I just mention XYZ, now you can see that the column will be called like XYZ. Okay. Now this is called as a column alias. We can also have an alias for a table, something like, let's say after the table name, just mention some other keyword, and this also works. Okay. Now there is a greater purpose of actually using an alias in your queries, specifically when you have big tables, and if you want to reference this table multiple times in your query, generally we use alias. This will not be clear to you now, but in—when we are solving the case study in a short time, that time you will realize, uh, why this alias becomes very important. Okay, but just remember, whenever you see after the column, if you see an `as`, or after a space you have some other values, that means this is an alias. So this column can also be referred by this name. Okay. In the same way, this table can also be referred by this name. Okay. That's what an alias stands for.

Lastly, I also want to cover about some important inbuilt aggregate functions. Okay, so let's say I have this table that is `select * from products`, right, and this one, and if I run it, you can see I have some data, but now this is a small table, but let's say you have some large table with thousands of records, and you want to know how many records are actually present in the table, then we can write a function or basically use a function which is called as `count`. Now `count` is part of an aggregate function. What this basically does is: so this is my inbuilt function here. I pass one argument, that is `*`. `*` basically means like all sets of columns, right? So when I pass this, it's going to count the total number of records that is present in this table. So if I run it, you can see that it's returning three. It's not counting the total number of columns; it's counting the total number of records. So if I just, uh, put it here again, and if I just run it, so this table has three records, right? Now let's say, when I'm doing this, if I put another filter condition here, let's say the same—same filter condition `where` the `price` is greater than 1,000, and now if I run this, now I'm getting the count as one, because there is only one record in the `products` table whose `price` is greater than 1,000, right? So this is about the `count` function. If you want to count the total number of records that is returned from your, uh, query, then we use the `count` function. Same way, let's say if I want to find the total price of all of the products in this table, then I can just—I'll just copy this here; I'll put it here. Instead of `count`, I can use something like a `sum`, but here I need to specify the column on which that it should basically add up the values. So the column is `price`, right? If I do `sum` of `price`, it's telling me the total price from this table from all the records is 3399.5, right? The same way, there are some other functions as well, like in of `sum`, we have something called as an `average`, which basically will tell me the average of this value of this column `price`. So if I run it, it's telling me the average is 1133. Okay. Now what exactly is an average? It's basically summing up all these three values and dividing it by the total number of records, that is three in this case, and that's why I'm getting around 1,133. Okay, so these are few important basic things that you need to understand: so aggregate functions like `sum`, aggregate `count`, and how do we filter data based on some values, how do we filter data based on the date columns, uh, etc., okay, and also about aliases. Now I think you have some basic understanding about all the—this basic SQL queries that we can write. Now let's go back to this table and try to modify some data that is present in this table and see how it works in SQL.

Whenever we have to modify the data in an existing table, the command that we use is `update`. So we can use the `update` command to change any existing data. Now we know that in the table that we have, that is `products`, these are the three records that we have, right? Now let's say I want to update the price of this particular record. So for iPhone 15, I want to make the price to be something else. Okay, so if I have to make this change, how I can do that is: I say the `update` command, then I mention the table name, that is `products`, then I say what column do I want to change, so I use the `set` command, and I mention the column which will—which basically needs to be changed, so I'll say `price` equal to 1,000. Okay, and I need to also say which particular record I want to modify. So in this, I only want to modify where the product is iPhone 15, right? So what I can do is I'll just tell `product name` equal to—that is, I'll just copy this. Okay, so I'll copy this; I'll put it here, and I'll just run this. So if I run this, you can see that the `update` statement is successful, and now if I query the data from that table, so `select * from products`, you could

See that the data has been changed, so iPhone 15 now is showing the price has 1,000 because that's what we made here. Now this is pretty straightforward, right?

Similarly, I can also modify multiple columns if I wanted to. So let's say I want to again modify the same record, that is wherever it is iPhone 15, and I want to make the price, let's say 1,200, and I also want to update one more column, let's say the release date. So I'm just going to say released date equal to some new date. So I'm just going to say to date, and I need to provide the new date. And okay, so currently it is this one, so I'll just copy it here. Since of telling 22nd, I'll just make it like 30th of August. Okay, and I need to provide the format, so this is YYYY-MM-DD.

Okay. Now you can see that I have physically asked SQL to update the price column to 1,200 and the release date column to some other value, that is 30th August, right? So I have mentioned these two values using a comma-separated. Now if I just run this, you can see that the update statement is successful. And if I look at this table data now, you can see that the price is 1,200 and the date has been changed to 30th August. So multiple columns also can be updated using the similar statement; you just need to provide the multiple columns comma-separated. Okay.

Now these are fine, but when you're using update, it's very important that you take caution because sometimes if you do not apply the proper filters, you might end up updating the wrong records. Okay. So, for example, let's say my intention was that I wanted to update iPhone 15 back to 999. Okay. So now you can see that the price is 1,200; I want to update it back to 999. So let's say I'm just going to run, create a new update statement. So I'm just going to say update the table name products, and I'll say set price equal to let's say $9.99. Okay. And if I just run this, now you can see that the update statement was successful, but it's telling updated three records. Right. Now ideally, this is not what I wanted because if I look at my products table, all the records, the price has been updated to $9.99. Why? Because when I ran this update statement, I did not provide a filter condition. Since I did not provide a filter condition, this statement basically fetched every record from the products table, and for every record it made the price to be $9.99. Ideally, what I wanted is I wanted to put a filter such that I only fetched the record where the product name was iPhone 15, right? So that's very important when you're using an update; also always make sure that you have the proper filter, okay, else you might end up screwing up or messing up your whole data. Okay. Now we only have three records, but imagine if you had a million records. Okay. Ideally, it's not even recommended to use update if you have to update million records due to performance, but of course, I'll not be talking about performance uh in this uh video.

Now I'll tell you one more important thing that you need to remember when you're using update. So let's say I want to update this record back to the proper value. So I think MacBook Pro 16 was th 2,000 and airpods was 400, right? So what I want to do is I'll just run the same uh command. Okay. I'll say update uh products, and I'll say set price equal to uh I want magbook Pro to be 1,000 2,000, right, 2,000. Okay. And now I'll put a filter condition based on the ID okay, or the product code. So here I'll just tell product code is equal to one because the product code for this record is one, right? Now if I run this record, ideally what I wanted is I want only this record, the price to be updated to 2,000. Now if I run it, you can see that again it's telling update three; that means three records have been updated. If I query the data from this table, you can see that all the records have been updated, the price to 2,000, right? Now why did this happen? I'm sure you guys already know that it's because of this product code, right? Now if you have, if you noticed when I initially did the insert, I gave the same product code to all the three records. In a real scenario, this is not the right way of doing it, right? Because I told you that this should be the unique identifier, so this ID should be unique for each record, but since my table did not have any constraints, I was able to um insert duplicate records into that table, and I just kept it as it is because I'm going to be using this to explain constraint when I come to that topic. Okay.

So now what happened when I said product code equal to one? This statement basically fetched all the three records because all the three record, the product code is one, and hence the price of all the three records is updated. Okay. So this is why when you're doing update, you need to be careful; you need to specify; you need to find out which is a unique identifier, and based on that you need to do the update, okay, so that you don't end up updating records which you did not intend to update. Okay. So that's very important when modifying data. Now let me just try to correct the data uh in this table, so try to add the proper uh price for each of these uh products. So I'm just going to say update products; I'll say set price equal to 2,000 where the product name. So I want it MacBook, right? So what I'll do is instead of providing the entire name, just like a kind of like a shortcut or a simple way of matching some values, I'm just going to use the like keyword, okay, and I'll say I know in this three records wherever I have MacBook Pro, MacBook Pro 16, I know it, it basically has something like MacBook, right? So instead of giving the entire name, I'll just provide MacBook, and then I'll provide a wild character, that is this one, percentage symbol. Okay. And what this basically does is if I use equal to symbol, this is actually an operator in SQL; if I use this operator, that is equal to, it will exactly try to match the value on the left-hand side with the value on the right-hand side, but when I use this like operator, it will try to match kind of like a a partial match where if I say product name like MacBook, it basically will look for any record that starts with this value MacBook. Okay. And after that value, whenever it sees this percentage symbol, this can basically match with any character. Okay. So after MacBook, I have space then Pro then space then 16; it does not matter because I put a percentage here. Okay. Now let's say if I run this, you can see that it only updated one record because it only matched with the record where the product name is starting with MacBook. Okay. Now similarly, let's say I want to update the other record. So now before that, I'll just show you the data in this table. You can see that the MacBook Pro has been updated to 2; in fact, it was 2,000 previously as well, but now let's try to update for iPhone 15. Okay. Now what I'll do is I know that for this particular record, the product name wherever it is 15, it is my iPhone, right? So I'll just do one thing; I'll just put 15, and I'll put percentage on both the sides, on the beginning and as well as end. Okay. Now, and I want the price to be let's say um 99.5. Okay. Now what this basically does is it will look for any product name that kind of can start with any character, okay; it can match with any character, but somewhere it needs to have a 15, right? So here it, there's only one record where the product name has 15, right? And at the end again it will match for anything; in fact, after 15 there is nothing, but it does not matter. Okay. So in this case, it will again only fetch one record, that is where it is iPhone 15, and now if I look at the data in that table, I think only that record is updated, as you can see here. So this is a very important keyword; whenever you do not want to match the exact value, you can use the like keyword, and you can use this percentage symbol in order to match values based on a subset of values that you might know. Okay. So, and finally, I just want to update for airpods pro as well. So for that I'll just say airpods, and here I'll say airpods. Okay. And even this one will actually work, but since I'm not using any percentage symbol, it will kind of match with all the values that is given here. Okay. And here I just want to make it uh 400, so I'll just run this, and and it and it did not update anything. Now let me go back to this table and see if I have given the proper case, airports and okay, so I think the p is in uppercase here, so now let me give that, and if I run it, now you can see that it updated one record, and if I go and query this table again, now I think I'm getting all the old value that I had. Okay. So airpods, I ideally wanted it to be not 4,000 but 400, so let me update it again. Okay. So this is basically how the update statement works in SQL.

Now let's look at how do we remove data from the database. Now in SQL, whenever you have to remove the data from the database, the command that we use is delete. Okay. So let's say I have my table, so let me just put that statement here, select start from products, right? And I have these three records. Now let's say I wanted to delete the record where it is iPhone 15. So what I can do is I can just tell delete from the product table, that is a table name, where now I need to provide the filter such that I only fetch or try to delete the record which I actually want to delete. So here I can just tell where the product name is like, okay, and I'll just tell I know it is iPhone, right? Wherever it is iPhone, I want to delete that record. So if I run it, you can see that one record is deleted. So it's very simple; whenever you want to delete a record, just use this simple command, provide the filter condition. Now again, if you do not provide this filter condition, and if I only run this Command, right? So let's say if I if I only ran this one, okay, so I'll just put a semicolon just so that you know where it ends. Now if I run this command, that is delete from products, you you might have guessed it will basically delete everything. Okay. It's telling delete two here because already one record was deleted; the table now had only two records, and both of them is deleted here. So if I look at the table now, now you can see that it is empty, right? So even when you're using delete, make sure that you have the proper filter, so you don't end up deleting unwanted records or the records that you did not intend to delete.

Now let's do one thing; I'll go back to my insert statement, and I'll just run this insert statement so I get all the three records. So if I go back and look at my select table, okay, now I have the three records. Now I want to show you one more thing with delete. Let's say I will again copy the same uh record. Okay. So I want to delete data from this table, but I I want to basically keep iPhone 15, but everything else I want to delete. In that case, what I can do is instead of matching with iPhone, I can just tell not like. Now what not like does is it kind of does the reversal of what like would do. So if I say product name like iPhone, it would match with the record wherever the name was having iPhone. Now when I say not like, it will basically match every record where the product name is not having iPhone, so that means it will match with the second record and the third record, so it will delete these two records, and it will only keep the first record. Okay. So if I run this now, now you can see that the two records are deleted, okay, and there is still one record remaining in this table. Okay. Now I'll again go back, and I'll insert the two records because I want to show you one more thing, and now if I look at my table, I have the three records. Now there is one more command that you can use to remove data from the table, okay, and that command is called as truncate. Okay. So it's truncate table; you provide the table name. Okay. Now if I run this command, this command also will empty the table; the only difference, in fact, not the only, the major difference between the truncate command and the delete command is when I use delete command, I have the option of providing filters, so I can either delete; if I just run this whole command, that is delete from the table, it will delete everything from the table, all the records, but if I want to delete just some specific records, I can put a filter condition, and I can use it with delete, but when it comes to truncate, I cannot use a filter condition; if I use a filter Condition, it's going to throw an error. With truncate, the only concept is that whenever you want to empty the whole table, it is always better to use truncate because it is much much faster. Okay. So let's say if I run this, you can see that it has deleted the entire table, and if I query the data from this table, there is nothing. Okay. So so whenever you want to delete the entire entire table without like any specific records, use truncate because it is much faster, but if you want to delete let's say some records, not everything, then you can use delete and provide the filters. Now the difference between delete and truncate is a very common question that is asked during interviews. Now I'm not going to be explaining the difference in detail here; there is a Blog where I have explained this detail; I'm going to leave a link to that in the video description so you can check that out.

Now so far we have done certain operations; so we have created a table, we modified data, and then we saw how to remove the data. Now all of these things that I'm doing, they are all part of this whole window called as SQL commands. So in order to perform different actions using SQL, they are all categorized into different SQL commands. For example, when we used select, it was basically to read the data from the table. Now this select comes under the DQL command, Data Query Language, okay, whereas the create command that we used, it is part of the DDL command; it is whenever you want to create a table or any other objects, we use create, right? And if I want to load data into the table, we use insert; if I want to modify the data, it is update; and if I want to remove the data from the table, then we use delete; we can also use truncate for removing the data, right? Now as you can see here, there are few other commands that we will be learning, but I'll not be covering all of this; for example, merge, Grant, revoke, and all of this, it's not that important to learn right now. Okay. But we will be understanding what is drop and alter. Okay. So now let's try to learn that concept. So let's say I have my products table, and currently I think it is blank because I truncated it, so I'll again run this insert command so that all the data gets inserted, and now if I look into my products table, and I'll just put it here again so that I can run it easily. Now I have my products table. Now let's say I have some requirement where I want to take a backup of this table. Okay. So if I want to take a backup of this table, or let's say I want to create another products table with the same data. Okay. Now I can create again; I can run the same create table command, give a different products uh table name, and then again do the same insert, but there is another shortcut to doing that. Okay. And that shortcut is I can just tell create table, let's say products_BKP, that stands for backup, and I'll say as, and then I'll just give a SQL statement, okay, so select start from the products table. Now what this basically does is it's going to create a table by the name products_BKP, and it's going to create this table based on the query that I have mentioned here. So this query here will return me three records, as you can see here, three records and four columns, so this table is going to get created with the same data and with the same column list, okay, and with the same data types. So if I run it, you can see that the create table is successful, and now let's say I'm just going to query that data from the backup table that I just created. So select start from products_BKP; you can see that it is exactly the same; it's kind of like I created the backup table using the products table, so it's kind of like I created a duplicate of products table. Okay. So this is a very useful command that we generally use whenever we have to create a backup.

Now one more very important concept that I think it's important that you know is let's say you might have a requirement where you want to create another table which should be exactly a copy of some existing table, but you do not want to copy the data from that table; you only want to copy the structure from table. In that case, what I can do is I can copy this same command here; I can put it here. So let's say I'll call it this like BKP2, but this time I do not want it to have any data. So what I can do is I can put a filter condition something like 1 equal to 2. Okay. Now what happens? It's going to create this products_backup_BKP2 table, backup two table; it's going to create it using this select statement. Okay. This select statement, what is this select statement? It's fetching all the records from the product table and then applying this filter condition. Now what is a filter condition? The filter condition is 1 equal to 2. Now if you know, and I'm sure everyone knows, one is never equal to two, right? Because as per maths, you one is not equal to two; 1 is equal to 1 and two is equal to two, but this is basically not true, right? So whenever you try to match 1 equal to 2, this is going to return false. So if this returns false, that means your filter condition is basically returning false, that means it will not basically fetch any record. So for example, if I just run select star from products, it's fetching me three records, but if I fetch if I run this whole query with this filter 1 equal to 2, it's going to fetch me Zero Records. Okay. So whenever we have a requirement that we want to copy a structure from a table and we do not want to copy the data, this is generally what we do. Okay. So we put this filter condition, and we run this whole command. Now the products_backup_two table is created; if I just copy it and if I just query the data from this table, you can see that the table structure is the same; the column names are the same; data types are the same, but no data. Okay. So this is a shortcut way of creating a duplicate table or a backup from an existing table.

Now let's talk about how do we remove the table from the database. Now let's say if I want to remove a table or any other object from the database, the command we generally use is drop. Okay. So we say drop table and the table name, that is let's say I want to drop the products table. Okay. So if I just run this, you can see that the drop table is successful, and now if I try to query the products table, so I'll say select star from products, it will show that the table does not exist; in fact, here it is telling relation does not exist because in post SQL, if you see error messages, in most of the cases they refer to table like a relation, so you can call a table to be a relation as well. Okay. But in all the other databases, generally they just refer to it like a

Table. Okay, so do not get confused that it's calling relation; it's basically means a table. Okay, so I have dropped the table, and this is a command when you want to drop the table. Now, what is the difference between drop and delete? Now, drop basically removes the entire table from the database, so the table, its structure, its data—everything is removed from the database. Whereas whenever I use, let's say, delete or truncate, it's only removing the data from the table; the table structure will still be there, but it kind of just makes it empty. Okay, the data is removed, but drop will kind of remove the entire table from the database. Okay, so this is very important that you should know.

Now, one more thing I want to mention here is, let's say I already dropped the table, and if I if I run this command again, now you can see that it's throwing me an error saying that the table does not exist. Okay, so generally when we are using this kind of a command, that is drop table, we can also provide a keyword like if exists. So if I can say drop table if exists and then provide a table name, and if I run it now, it's basically telling successful; it's not throwing an error. You can see that there is a notice. Notice is basically kind of like a warning. Okay, so in other databases you might see warning, but in Postgres they call it like notice, and here they are telling the product table does not exist, so it is skipping this command. So this command was not even executed. So generally when you are writing some scripts, you might see some if exists after the drop table. In fact, you can use it with the create table as well. So this basically means that only drop this table only if it exists; so if this table exists, then only delete it, then only drop it; otherwise, just skip the command. Okay, that's what this is doing. I hope this is clear. Now, this is basically how we can remove the table itself from the database—not only table but any other object. Okay, there are some few other objects in the database as well, like table, view, and procedures, functions, and few others. So whenever you want to drop something, remove the whole structure or the whole object from the database, then we use the drop command.

Now, let's say I want to modify the structure of a table. Okay. Okay, so we have seen how to modify the data in the table using the update command, but let's say if you want to modify the structure of the table. So, for example, let's say I have I think the two tables that are remaining currently is BKP and the BKP 2 table, right? BKP and BKP 2, and let's I'll also copy this. Okay, so these were the three tables that I created, but products table I already dropped. BKP table, the product backup is present, and product backup two is also present, right? Right now what I want to do is I this table does not exist, but I want to rename the backup table into the products table. So what I can do is instead of like trying to recreate the products table and insert the data, I I can just rename this table into the original table. So if whenever you have a requirement to rename a table, how you can do that is a simple command that is using alter. So we say alter table table name, that is products_BKP, rename to products. Okay, so this is the command. If I run it, you can see that alter table is successful. What this basically does is it's using the alter command to change the table name of products_BKP to the products table. So now let's say if I query the data from the select star from products, you can see that this table now exists because I renamed the backup table into the product table. But if I query from the backup table, you can see that this table does not exist. Similarly, alter table is not just used for renaming the table name, but there are many other modifications in the table structure you can do by using the alter command. For example, let's say in the products table I have this column, let's say product name; I want to rename it to let's say, or let's say product code; I want to rename it to ID. So what I can do is I can say alter table products, and then I can say rename column product code to ID. If I run this, you can see the alter is successful. If I query the data from the products now, you can see the column name has been renamed to ID. Similarly, if if I want to modify the data type of an existing column, I can do that using the alter command as well. So I can just tell alter table products, alter column the column name, that is ID in this case, then I say the keyword type, and then I say the new data type. So let's say I want to make it float. If I run it, you can see that alter is successful, and if I query the data from the table now, you can see that it's telling double precision. Now, double precision basically means float. Okay, so even the varchar basically it means varchar, uh, character varying, and if you get confused why is it not showing float and varchar, because it's just two different names for the same data type. Okay, so here it is displayed like this. So this is basically how we can modify the data type of an existing column; we can rename a column, or we can rename a table. Okay, so this whenever you want to change the structure, like change the table name, data type, or column name, etc., then we can use the alter command. I hope this is clear.

Next, let's look at a couple of very important constraints that you should definitely be aware of. Now let's talk about constraints in SQL. Now, what exactly is a constraint? So if you remember initially in this video, I created this products table, right, and I created the products table just with the column name and the data type; I did not use any constraint. Now, the problem what happened was when I inserted data into the products table, you could see that the product code is basically the same for all the three records. Now, if you remember I told you that the intention of having this product code is that it's going to be unique for each record, but maybe it was a human error or typo or whatever; I inserted the same value for all the three records, and SQL allowed me to insert it, right? Now, this is actually not right, because when you are working on real projects, real SQL data sets, if you have a unique identifier, it needs to have unique values; it cannot have null values; it needs to have unique values; you cannot have a duplicate ID, right? Now, now this can be avoided by having a primary key constraint. What exactly is a constraint? Now, as the name suggests, constraint is kind of like you add some restrictions to some of the columns in your table. Okay, now the restrictions are added so that you can have non-redundant data and meaningful data and correct data inserted into your tables. So constraints kind of helps the database to kind of maintain data quality, for example. And what I'm going to do is I'm going to recreate this table, so I'll just copy this whole table. In fact, yeah, so I'll just copy this whole thing. Okay, the create table and the insert, and I'll just put it here, and I'm going to recreate this table. But first of all, since the table is already present, I will need to drop it, so I'm going to use a drop command: drop table if exists, and I will just run this. So the table is dropped. Next, I want to create this table. Now, when I I'm creating this table, I'll say product code; I want product code to be my primary key constraint. So one way of declaring a column as a primary key is after the data type, leave a space, and then just mention primary key, that's all. Okay, so if you just mention this, then SQL is going to treat this particular column to be the primary key. Now, if I run it, the table is created, and if I query the data from the products products table, you can see that there is a PK that is mentioned here, primary key. Now, how this basically helps me is now this was a same insert command or the insert script that I used previously, and it inserted properly, but now let me try to do that. So I'll insert the first record; it is done successfully. Now let me insert the second record, and now it is failing. Why? Because you see the error message; it's telling product code one already exists, right? Because in this table already one record was inserted with the product code one. Now I'm trying to insert another record here, this one, and it's also having the same product code, and that is not allowed because this is basically a violation of the primary key constraint. Okay, so because in the primary key column you cannot have duplicate values, that means if a value is already present, you cannot insert the same value again, and you cannot have null values. Okay. Now, what exactly is a null value? It's kind of like an empty value. Okay, so what I need to do here is I need to give a different value, so I can give two. Okay, and now if I run this, now it will work. And again in the third record, I cannot execute it because I cannot make it work because it's one, so I need to make the product code as unique, so I made it three, and now it works. And now if I look at my products table, you can see that I have the unique ID in my primary key column, that is product code. Okay, so this is a very, very important constraint; it's a pretty basic constraint to understand, because if you want a column to have unique values and non-null values, then you just when you're creating the table, you can just declare the table as that particular column as a primary key column. Okay. Now, you can also use the alter command to basically make a column as primary key, but of course I'll not be showing all that; you can Google the syntax for that. So this is one of the syntax of how you can make a column as primary key; there's another syntax as well. So, for example, I'll just copy this, and I'll just paste it in the bottom here. So either you can just after the data type of that column you can mention the column as primary key, or you don't have to do this here, and the end after the last column put a comma, and then mention constraint, give a constraint name, let's say pk_or_product, okay, and you say primary key, and then inside the parenthesis mention which column you want to make primary key, that is product code in my case, right? And now if I run this, first of all let me drop it because the table is present. Now let me recreate it, and this one also works. So if I query the data now, you can see that it is still the same; it's primary key, right? So you can either use this syntax, that is after the last column just mention the keyword constraint, give any name—this is just the name of the constraint; you can give whatever name you you want—then mention the primary key, because this is the constraint that you want to use; primary key is the constraint name is basically the type of constraint that you're using, and then on which column you want to make primary key, right? That is what you need to mention inside the parenthesis. So this syntax also works, and this syntax also works. Now you might be thinking this syntax is so simple, right? So why would anyone want to use this? There's a very good reason on why you have two different syntax for this. For example, there could be scenarios where you want to make a column as primary key, but not just one column, but let's say two columns. So I want a combination of product code and product name to be primary key. Okay. Now I cannot use this syntax to make that happen; the reason is in SQL a table can only have one primary key. Okay, so let's say if I just copy the same thing again, and I'll go here and let's say, okay, I will drop the table, and I want both the columns to be primary key, right? So product code and product name; I mean, I cannot make two columns to be I cannot have two primary keys, but I want a combination of these two column values to be the primary key. So that means when I say unique value, the combination of values in these two fields should become unique. Okay, so if I run this command, it's going to throw me an error saying that you cannot have multiple primary keys. Okay, that's the error. So that is the reason why you have this syntax. If you want to create a primary key using more than one column, then this is the syntax that you need to use. So how you can do that is I'm just going to copy this again here. Okay, and I'll just tell I want product code and I want the product name. So here inside primary key, list of columns, I'm mentioning two columns, and now if I run it, now it works. Okay, so here the primary key, if I query the data from the table, the primary key, both these columns are primary key, and you can see that PK is mentioned under both these columns. So this is a very important uh concept that you should remember, and this concept of where you're creating a primary key using more than one column is generally called as a composite primary key. So sometimes in interviews you might be asked, what is a composite, what is the difference between a composite primary key and a primary key? This is the answer. Primary key you can say using one column; composite primary key is when you're using multiple columns to create the primary key. Okay, so I hope this is clear. Now let's do one thing; I'm just going to insert data into this table using the same insert command, and if I run this, I have three records. Okay. Now when I told you combination of two columns, so the primary key can be combination of two columns; in that case, I'll show you an example. So let's do one thing; I'll just tell you how that differs. So, for example, I'll have this insert statement, and I'll have one, one, and one here. Okay, so I'm just going to drop the table again, and I'll recreate this table here. My primary key is a combination of product code and product name. Now, if I try to insert these three records, can you guess if it will work or not? Just yes for a second. The answer is it will actually work. Why? Because now the primary key is not just product code, because if you look at only product code, then one, one, one, and they are repeated, so they will be duplicated, but this is not only the primary key; the primary key is combination of these two, so it's one concatenated with this value, one concatenated with this value, one concatenated with this value. So when you take the first two columns together, then all these three records are having unique values, right? So that is why if I run it, it's still working. Okay, so that is what is a difference when you create a primary key with one column and with multiple columns. Okay, so I hope this is clear. Now if I just query the data from this uh table, just to show you how the data looks, this is how the data looks. I hope this is clear. Now, one more thing I want to mention here is about the identity column. So in this case, now what I want is I do not want my primary key to be the combination of two columns; I just want it to be a combination of one column column. So I'm going to recreate this table um using Okay, so this one. Okay, so just with one column, I'll recreate it, and I'll insert the same records, and now if I just query the data from the table, it's the same data, but the primary key is only product code. Okay, this is how I want it. Now let's say, let's imagine that okay, here I only have three records, but let's say you wanted to insert let's say 1000 records, and like every day you have a requirement where you might be inserting someday 100 records, or continuous you'll be inserting some records, right? So in that case, it is very difficult for you to manually enter a unique identifier. Okay, you could enter it; there are certain functions that you can use to enter unique values, but I think there is a better way of handling this unique values. Okay, and that is by using the identity column. So, for example, what I'm going to do is I'm going to recreate this same table, so I will just copy this table and I'll just put it here, and what I'm going to do is here the same syntax after this data type int, I'm just going to mention keyword like generated always as identity. Okay, that's it; just this one line I have added extra. Now I'm going to recreate this table, and I'm going to now if you see the table is empty, right? Now what you see here is basically also another type of constraint, which is generally referred to as an identity constraint. What this does is whenever you have a unique identifier in our table, you know that there is a there's a column which is always needs to have unique values, then it is always better to create that table as an identity column. What this basically does is you don't have to manually enter a value into this column; SQL by default or automatically will assign a unique value into this column. Okay, so let's see how that works. So what I'll do is I'm just going to uh copy okay this insert script, and I'll try to run it here. Okay, so I'll just put it here, and now let me just try to insert the record, the first record. You can see that I'm getting an error. Okay, and the error basically tells the column product code is an identity column defined as generated always, so I cannot insert a value myself because I created this column as an identity column; I cannot insert a value into it; SQL will by default add a value, so I should not be providing a value here. So instead of providing a value, what I can do is I can just provide a keyword called as default. Okay, so I'll provide default, default, default, and now if I run it, you can see that insert is successful, and if I query the data from this table, select star from products, and if I see it, you can see that I'm getting unique values now. No matter how many records are inserted, SQL will take care of handling unique values into this table. Okay, so this is one very good I think uh concept that you should always try to use whenever you're creating a table. If you know that it's going to be a primary key column, if it's going to have unique values, then try to create that column as an identity column. Every database supports it; maybe the syntax in other databases slightly might be different; you don't need to provide this keyword; you just need to mention identity, but this is supported everywhere in every database. Okay. Now, here by default the value always starts with one, but we can also add some additional uh key like arguments here to tell SQL if you want to the value to start from 100 or whatever you can do that. Okay, I'll not be showing it here; you can easily Google that, but I hope you understand the concept about primary key and the concept about identity. So I hope the primary key constraint concept is clear; it's I think it's very simple to understand. The next constraint that we are going to talk about is foreign key constraint, and that one might be a little confusing, and I cannot explain that constraint just by using one table, so we will need to create another table. So for that, I'm going to create another table which is going to be my sales order table. So what I'll do is I'll just tell create table sales order; I'll open and close the parenthesis. My first column is going to be let's say order ID, and this is going to be int, and let's say I'm going to make it like generated always as identity, and I want this to be my primary key. Okay, and let let me put a comma here, and then it's going to be order date. Okay, and this can can be let's say a date column, and then I'm going to have quantity. Okay, how much items were purchased in that order, and this can again be an integer. Then I'm just going to say my product ID. Okay, this is basically the product that was purchased; it has to be one among the product

That is present in my product table, and this can be, let's say, an integer again. And finally, I'll say the status of the order, okay, and this can be, let's say, a varchar, okay. And I'll just call it like, let's say, 30, okay. So I think this is my create, create uh, table, and if I just run this, the table is created.

Now I want to insert some data into this table, so I'll just quickly do that, okay. So I have quickly written a sample insert script, so I'm just inserting values into all of these columns in the sales order table. So my first column is an identity column, so I just, I'm just putting default, then my order date, and then the quantity I'm saying two, then the product ID I'm just saying one, and then the status completed, pending, and completed, okay. So now what, what I'll do is I'll just run this, okay. This works fine, and I'll, this one also works fine, and I'll insert the third record. This one also works fine, okay.

Now if I query the data from this table, so select star from the sales order table, if I run it, you can see that I'm having three records. And just to show you the products table as well, so I'll have products table here. So in the sales order table, I have one, two, three. This is the order ID, then I have the order date, and then I have some quantity, and then I have the product ID, okay. What this basically means is on 1st January 2024, two items were purchased and of this product, that is product one. So what is product one? Product one, I think is iPhone, right? So I have product one, two, and three. So first one is iPhone 15, then MacBook Pro, and then AirPods, right. But in the status order, I think the first two records are fine because the order ID is unique, the quantity does not matter, the product ID is 1 and 2, and 1 and 2 both are valid product IDs as per the product table, and then the status is completed and pending, that is fine. The problem is with the third record. If you look at the third record, the product ID is mentioned as 15, right, but in my products table, I do not have something like 15, right. So the data that you see in the sales order table, this looks incorrect. How can you have an invoice for a product that does not exist? That is not right, right. So this is basically where a foreign key constraint comes in, okay.

Now whenever you want to have like a parent-child relationship between two tables such that, let's say, in this particular case, my products table should become the parent table because if you want to make a sale, you can only sell the products that is available in the store, right. So this kind of whatever is mentioned in the product, those are the only available products in the store. So if you are making some sale, then you can only sell the products that is present in the products table, right. So products becomes my parent table, and sales order will kind of become my child table, right. So I basically need to create a parent and child relation such that for the column product ID, this column should always refer to the product ID that is present in the product table, okay. Now this is the kind of constraint that we can add by using the foreign key constraint. So what I'm going to do is I'm going to copy the same table again, okay, with the insert script, and this time I'll just put it here. And now what I'll do is first of all, I'll create a drop command here, so I'll say drop table if exists, okay. And here what I'll do is when I'm creating this product ID, I want to create this product ID like a foreign key column. So how I can do that is here I can just mention references, references the other table name. So this is my child table, right, and this table should refer to the products table, so products table, and within the products table, it should refer to the product code column, right. So if you see here in the product table, I have the product code. Whatever product code is mentioned here, only these should be allowed to be inserted into the sales order table here under the product ID. The column names are different in the two tables, but you can still form a relation between a column from the sales order table and the column from a product table. How I'm doing that? When I, when I'm creating the child table, that is a sales order table, in in the product ID column, I'm telling it should always refer to the products table, and within the products table, it should refer to the product code column, okay. So now if I just recreate this table with the foreign key constraint, and let's say now I'm going to query in the sales order table, I do not have any record. So now I need to insert the record, right. So in order to insert the record, I'm just going to run the same insert scripts which I actually was able to insert previously. So the first record is done successfully, the second record is done successfully. Now I'm trying to insert the third record, and it fails. Now the error message is the product ID 15 is not present in the products table. You might actually think so I'm trying to insert data into the sales order table, but SQL is telling that the value that I'm inserting here is not present in the products table. Now this is the magic of a foreign key constraint. Why? What happened here was 15, whatever value when you are trying to insert into a foreign key column, it will first look if this value is present in the master table or in the parent table. The parent table is the products table, the table to which it is referencing, right? In the products table, if I had 15, then this insert script would work fine, but since I do not have 15, because I only have products one, two, and three, this value is basically out of scope, so it's not allowed, right, and that is why it's failing. So instead of 15, let's say if I insert three, and now if I run it, it will work fine because three is one of the products in the products table, as you can see here, right? I have this three, right. So now in the sales order table, I have all the three records. In fact, the order ID did not become three; it became four. This is because this value is auto-generated using the identity column, right. So previously when I ran the insert, it failed once, so that sequence 3 was already used, so that when you execute it again, it kind of goes to the next sequential number, that is four, okay.

So I hope you understood the concept of primary key and foreign key constraint and also about this identity uh constraint that we can use. Next what we are going to do is I'm going to create a data set of the sales order data set. I have a list of four tables with some sample data. I'm going to create them, and then we will use that data set to kind of build our case study. So in order to create that new data set, I'm just going to open a new window here, and I'll just paste the script that I have already written. Now if I have to quickly explain you this data set, so I have four tables. The first one is products; it has the same four fields: the product ID, the product name, price, and release date. I have some five different products that I'm trying to insert into this table. Then have the customer table, so the product is basically the products that are available in the store. Customer table is so that I can store the information about the customers who are actually going to purchase some of the products from the store. So I will have information about the customer ID, their name, and email, okay, and some sample customers I'll be inserting. Then I have the employee table; these are the employees who are working for the store who would basically be responsible for some of the sale that will happen, right. So the employee ID and the name is stored with some sample employees. Then finally, I have the sales order table, which is basically having information about all the sales that has happened. So the order ID, order date, the quantity in each order, how many products were purchased, that is basically the quantity, then the product ID, which particular product was actually purchased in each order, the status of that particular order, if it was completed successful or pending or failed or something, right, then the customer who purchased that order, his customer ID will be there, then the employee who was responsible for that sale, their ID also will be there, right, and then I'll be inserting some sample sales uh order data, okay. And when I'm creating this table, I have all of this foreign key uh identity column and primary key column used properly, okay. Now all of the script you will find it in my blog, so you will be able to download it and use it on your own, okay. You don't have to try to pause the video and copy it; you will find everything in my blog. So I will do one thing; I'll just create all of this entire data set. I just ran it; I'm getting this error because I already had created this sales order and products table. So what I'll do is first let me just see drop this table, so I'll drop the sales order table, and I also had the products table; I'll drop it. Now I'll run the whole script, and it is all completed, okay. So all the four tables are created. So just let me just quickly show you. So this is my products table; five products: iPhone 15, MacBook, Apple watch, iPad, AirPods. This is my customers table; four different customers: Rosa Chan, uh, Megan Harley Logan Short, and Zaria Duke. And then I have the employee table; three employees: Nina Kumari, Abrar Khan, and Irene Costa. And then I have my final table, that is sales order. I have I think 10 different orders; that information is present here, okay. There is an order date, the quantity, the product which was purchased, and the status. You can see that the status is completed, pending, on hold, rejected, okay, and also cancelled, right, and then the customer who purchased the order and the employee who was responsible for the sale, okay.

So we have now created the sales order data set; we have the four tables that holds this entire data set. I hope you understand the data set; I hope you understand these four tables; I hope you understand the data that is stored in these four tables, because you will need this understanding in order to solve the case study. Now as I told you, I have this sales order case study that I have created. As part of this case study, you will have 20 SQL problems that you will be able to solve or answer using the salesorder data set. The reason why I'm having this case study is because I did not wanted to explain all the different basic SQL concepts theoretically; rather, I wanted to explain it by solving SQL queries so that you can get hands-on experience in not just learning the concept but also seeing how that concept is used to basically solve a problem. And each of these 20 problems that I have mentioned here is basically designed such that each problem will kind of help you to learn and understand at least some of the basic SQL concepts, and by the time you finish all these 20 problems, you would have probably completed or covered most of the basic SQL concept that is actually required for you to start writing and solving SQL queries confidently. Now once you have completed this 20 problems, you're all set to go into any of the online SQL practice platforms like LeetCode or HackerRank and start solving the easy level problems. Now straight away, let's start solving the problem one by one. First of all, I'm going to take the first problem. In order to solve it, I'll create a new session here; I'll just paste the question here. So this is my question number one, and I also want these four tables, so I'm just going to copy this, and I'll just mention it at the top because we will be referring this four tables. So the problem number one states: identify the total number of products sold. Now this is a problem where you need to properly understand the problem statement. Here they're asking us to find the total number of products, but not just products, but they are asking us to find the total number of products that has been sold. Now some of you might get confused by this term number of products because when you see that, okay, you are asked to find the total number of products, you could just go into the products table, and you can see that, okay, this store has five different products, right. So you could probably write a query using the products table, right, but that would not be correct because in this problem, they're not asking us to find the number of products, but they are asking us to find the number of products that have been sold. So if I want to find the information about something that has been been sold, then the only table that has this information is the sales order table. Now here I know that every record in this table represents a sale that happened. For the sake of this problem, we will treat that everything that is present in this table is basically sold, right. Now when it comes to the number of products sold, you can see that I have this column quantity, right, because in each order, it's not mandatory that only one product is sold; more than one product could be sold, like you can see here; here the quantity is two, that means under this order there were two products sold, and under this order three products were sold. So here also three were sold, and here I have somewhere five products sold; here somewhere I have one product sold, right. So if I want to find the total number of products that have been sold, then I just need to sum all the values that is present under this column in the sales order table, right. If I just do a sum, I should basically get the total number of products that have been sold. So in order to do that, I'm just going to copy this query here; I'll just put it here, and what I'm going to to do is in order to basically add the all the values within the quantity column, I'm going to use the sum function, okay, and I'm just going to mention the column name here saying that quantity, right. And if I just run this, you can see that I'm getting 24, right. So 24 are the total number of products that have been sold, as simple as that, okay. Now just to make this query more meaningful, you can see that the column displayed here is sum, that is not very meaningful value to get displayed, right, because if someone looks at it, they want to know what is this 24. So just to make it more meaningful, I'm going to add an alias here saying that, let's say, total sold products, okay. So if I run this now, now it's still the same value 24, but the column name here is kind of more meaningful for anyone to understand what exactly this 24 is, right. So this is the solution to my problem number one. Now let's look at the problem number two. So I'm just going to copy this, let's say, and I'll just put it here, okay. So the problem number two states: other than completed, display the available delivery status, right. So they are asking us to display the delivery status, but ignoring completed, right. So now if I go into the delivery status, in which table can I find the delivery status? That the table that I can find it is only sales order because in sales order I have this column status. Now I have a feeling that some of you might struggle to see all the text here, so I'm just going to do a zoom. Right now let's go back. So in the sales order table, I have the status field, and here I can see the status are completed, pending, on hold, rejected, cancelled, etc., right. I want to find other than completed all the other delivery status, right. So that means first of all, whenever I'm writing a query, one of the best ways or a good way of practicing writing SQL queries when you're getting started is just to write the three keywords one line at a time: select, from, where, right. Now what this basically means is select is where you mention all the mention all the columns, right, which you want to display; from is the the table which has the data, right; and then where is the filter condition, right. So if you can just remember these three things, then you will be able to easily write most of the queries, okay. So we will try to practice this, okay. Whenever we are having a problem to solve, we'll write these three keywords and then see what are the values that needs to be filled corresponding to each of these keywords, and that's exactly what I will be doing here. I'm just going to write it again: select, from, where, okay. And what I want to do here is I want to find the all the available delivery status, that means the final column that I want to display is basically the state field, right. So I'll just mention that, and I know that this particular column is the only column I need, and this is present in my I think the table name is sales order, right. So inside the from, the table from where the data needs to be fetched, it's going to be sales order, so I'll mention that, right. Now do I have any filter condition, right? So they're basically telling me that I need to display all the delivery status other than completed, that means this is something that I need to enter in the filter condition, right. So here I need to say where the state status is not equal to completed, right, because what basically they're telling is fetch all the records other than completed, other than the record where the status is completed, right. Now generally in all the queries that we have written, we have seen this operator equal to. When I put equal to, it basically kind of matches left-hand side value with the right-hand side value, but if I want to do the reverse, that means if I want to if I do not want to match a particular value, then I can put the not equal to symbol. So this is basically not equal to symbol; you can also represent it like uh put it like this. So this is also a valid not equal to symbol in SQL, and this is also a valid not equal to symbol, so you can use either of them. Now I'll be using this. So what this basically means is SQL will go into the sales order table; it will fetch the status column, and when it is fetching the status column, this table totally has 10 records, but here I have a filter condition saying that where the status is not equal to completed. So when SQL is processing one record at a time, it starts by processing the first record; it sees that, okay, the status here is completed, so this basically fails our filter condition, right, because completed is something that I I am basically looking for record where the value here is not completed, right. So that means this cannot be part of my final output, so this will be ignored. Same way these three will be ignored, and then I think everything else will get displayed, right. So now if I just run this, you can see that I'm getting pending; I'm getting some completed; then I'm getting on hold, rejected, and cancelled. Now this is fine except that there is one particular problem, and the problem is I can see that I still have a completed status here, right. Now I had put a filter condition that I should not fetch wherever the status is completed, but still this record is showing up here. Why is it happening? The reason why this is happening is because the case is different here; in completed, C is in upper case, whereas here it is in lower case, right. Now whatever values that you mention or you try to compare, it is case sensitive, so I cannot expect SQL to automatically understand that this completed and this completed are the same. We might understand, but SQL does not understand; we have to explicitly tell it, right. Now there are a couple of ways I can solve it. One way is whenever I'm doing this filter, I can provide both the values together. So what I'll do is I'll just copy this whole query here; I'll put it here, okay, and now instead of using the not equal to symbol, I'll just put a command or I'll put an operator like not in, and then here I'm just going to put a parenthesis, right, and then I'll just tell the second value that is completed, right.

What am I doing here? Is whenever I want to just have one value on the right-hand side, I can basically do equal to or not equal to here, right, to match just one record. But if I want to match this left-hand side value with multiple values on the right-hand side, then I can either use the in keyword or the not in keyword, right? Not in keyword—what it will do is it will look for all the records where the status should not be equal to this and it should also not be equal to this, right? So instead of not in, if I had put in, then it would basically do—it will basically look for every record where the status is either completed or it has to be this completed, right? But now here I'm interested in not in because that is as per my problem statement. And now if I run it, you can see that I'm getting four records. So these are the four statuses that are basically other than the completed, pending, on hold, rejected, and cancelled. Okay.

Now there is one more thing that I want to mention: there could be a possibility that I might have multiple records where the status was pending, or there were multiple records where the status was cancelled, etc. Right? In that case, if I just write this query, then for those statuses where there are multiple records, it would get repeated, right? So if I had repeated data, then I could use a distinct keyword to kind of display the unique values. Okay.

Now maybe this is not very clear here, so what I'll do is imagine that I will write the same query down here, and this time I want to fetch everything where the status is completed. Okay. So instead of not in, I'll just put an in, and if I run it, you can see that I have all the records where the status is completed. So four of the places it is completed, and in fact, five of the places it is like the completed with C in uppercase, and the last one is basically the one where the C is in lowercase, right? Now here I'm getting the same data repeated multiple times. If you want to avoid repeating multiple records with the same data, one way of doing that is by using the distinct keyword. Okay. If I run it now, now you can see that only unique values will get displayed. So what does the distinct keyword do? As it means, it basically always displays unique values, right? If I put multiple columns here, then it will kind of display the unique values for the combination of the multiple columns. So you can try it out yourself. Okay.

Now this is something that you need to know about the distinct keyword. You should also know about not in and the in operator, okay, and also the not equal to operator. Okay. Now there is one more thing that I want to show you here. So I had this query, and I'm just going to copy it and I'll just try to put it here, right? And how did I solve this query so that I could basically ignore both the completed in uppercase and in lowercase? Is by using the not in operator and presenting multiple values, right? Now there is another way of solving this problem: whenever you have a value that you want to match irrespective of its case, then a good way of doing this is by using some inbuilt functions. So, for example, let's say I have—I'll just show you one example—so let's say my name, okay, Toik. So this is my name, and if I run it, you can see that it is displaying Toik, right? But let's say if I just use a an inbuilt function called as upper, and now if I run it, now you can see that it is displaying all the letters in uppercase, right? Same way, I have another function, so I'll just put a comma and mention it here, and instead of upper, I'll mention lower. So if I run it now, now you can see that when I in the lower the second column here, everything is in lowercase, right? Now I can use this same inbuilt function in this above query to basically tell SQL to treat the status completed irrespective of its case. Okay.

So how I can do that is I'm just going to comment out this one, and here for the status I'm just going to say, let's say lower, okay, and I'll put the status column within the lower function. So what this happen—what this means is whatever the status might be in that table, okay, whether it's an uppercase or lowercase, all of the values will be converted into lowercase, and then when I'm doing this comparison, I can just make sure that this right-hand side value will all be in lowercase. So whether it's an uppercase completed or a lowercase completed, both will be converted into lowercase and it will match with this lowercase completed value, right? So now if I run this, you can see that I'm still getting the final output. So this query here and this query here—that is this query here—is basically returning me the same output, but my queries are different. Here I use the not in keyword and I passed in both the values, but here I used the lower inbuilt function to basically ignore the case. So I hope you understand this. Now same way, you might also imagine how about solving it using upper. So if I'm using upper here, then what happens is this status whatever is present in the table will always be converted into uppercase. So when I'm matching it with the value, I should match it with the uppercase value, and now if I run it, I'm still getting the same output. Okay. So this is basically my solution to the problem number two. Now let's try to solve the problem number three, and let's go back to our problem statement. So let me just copy this and I'll put it here. Now the problem statement—let's try to read it—so we are asked to display the order ID, the order date, and the product name of all the completed orders, right? So first of all, let me just copy this four tables and I'll just put it here so that I can easily check the data. So what do I have? I need to display the order ID, order date, and product name. So first and foremost, as I told you, just to follow a good practice when getting started with SQL, I'm just going to write select from where. Okay. So this thing I'll straight away write, and then I'll try to fill in the values here, right? So what do I want to display? I want to display the order ID, I want to display the order date, and I want to display the product name, right? So I'll just put it here: so order underscore ID, order date, and I think product name, right? Now if I look at the product name, I think products is the only table where I have the product name, but the column name is actually not product name, but it is name, right? And then order date and Order ID, I think it is present in the sales order table. That means the values that I need to display in as part of this problem statement are basically coming from two different tables: the product name is coming from the products table, and order date and Order ID is coming from the sales order table, right? So that means in my from clause, I need to mention both of these tables. So I'm going to start by writing the sales order table, so I'll just mention it here, and then I also need to have another table that is the products table, right? I'll mention it here. This is a very important concept: whenever you have a requirement in SQL where you need to fetch the data from more than one table, that means you will need to join the tables, right? So in this case, I have two tables where I need—because I need to fetch the data from two tables—so I need to join these two tables. Now there are a few different ways of how you can join multiple tables. One of the most common ways of doing that is something called as an inner join, okay? So just mention the first table, then mention this keyword inner join, and then mention products, the second table, and then what you need to do is it's not just about telling that, hey, SQL, join sales order table with products; you also need to tell SQL how will that join happen, right? Now when I'm saying about join, what this basically means is I need to kind of see if there is a relationship between the two tables. Now in my case, let's start looking at both the tables. So in the products, I have the product ID, name, price, and release date, whereas in the sales order table, I have the order ID, date, quantity, product ID. Okay, so I have the product ID here, right? Now this product ID here in the sales order table and the ID column in the product table should be the same. So that means I can form a relationship between the products and the sales order table using the product ID column, right? Whenever you know that if you want to join two tables and there is a common column in these two tables whose data is basically going to be interconnected or linked or the same, then you can basically join the two tables using that column. Okay. The column name—it's it's not mandatory that the column names in the two tables has to be the same—it does not matter what the column name is; it's about the data stored in these columns. So the ID column in the products table has the ID of each product, the unique ID of each product, and the same order table, the product ID column has again the ID of each product, right? So we know that they are the same—the product ID here and the ID in the product table are basically the same—so in order to join the two tables, I'm going to be using the product ID column, and how you can mention this is so you mention the table, inner join keyword, and then the second table, and then you use the on keyword, okay, and then you mention the join. But a better way—whenever you're using multiple tables in queries—is a better way of writing a query is to always give an alias to each of the table. So for my sales order, I'm going to give an alias like so, okay, sales order, and then from products table, I'm going to give an alias like P, okay, and then when I'm joining, I'm just going to say from the P table, fetch the ID column and then try to match it with the so table, from the so table, the prod ID column. Now you might be wondering how does the match exactly happen. For example, let's say in the products table I have the ID 1, 2, 3, 4, and 5, right? So when I try to join this ID column with the product ID in the sales order table, you can see that again I have 1, 2, 3, and then 5, right? So it will basically match for all of these IDs. So I don't see the ID 4 here, but 1, 2, 3, and 5 is present, right? So wherever there is this common values in both the tables, those records will get returned from this query, okay? That is what is basically happening with the join.

Now just to explain you the concept of inner join and how it actually works, let me explain you this quickly using a couple of other tables. So I have these two tables created: European countries and Asian countries. In the European countries, you can see that I have the column country and continent. So I have mentioned five different countries here: Turkey, Germany, Poland, Russia, and France, and the continent they all belong in Europe, right? Then I have another table Asia, which again has the same columns country and continent, and I have four different countries mentioned. So countries are India, Turkey, Malaysia, and Russia, and their continent is Asia, right? Now let's say I have a requirement where I want to find—find the countries present in both Asia and Europe. So using the above two tables, we need to find the countries which are present in both Asia and Europe, right? Now the first thing that we generally do when we have to write a query, as I told you, is write the three keywords, that is Select, from, and where, right? And then we will try to fill in the blanks here, like what should come under select, what should come under from, and where, right? Now what I want to find is the country name, right? And I want to find probably in which continent they are. For now, let me just skip this; I'll not enter the select now, but from which table should I fetch this? Of course, I should be fetching it from both of these tables, so I'm just going to say European countries. I'll give an alias to this like EC, and I also want to use the Asian countries because the Asian countries information is present in this table; European countries information is present in this table. I am looking for countries which are present kind of in both the tables, right? Because one table represents Europe countries and other table represents Asian countries. If I want to find countries which are present in both the tables, I need to use both the tables, so I'll be using both the tables here, so I'll say Asian countries and I'll give an alias like AC. How do I join these two tables? If you see in both the tables, I have this country column, right? And and if you can easily see that there are some countries which are kind of present in both these tables, like Russia and I think Turkey, right? So I can join it using the country column, so that's what I'll do. And here I'm just going to say inner join. Okay. Now there are other types of joins; we'll not be covering it in this video. If you understand inner join, then understanding the remaining type of joins will be easy. I have made a very detailed video explaining all the other type of joins; you can definitely refer that; you will find a link in the video description. Okay. Now inner join with the Asian countries table, and I need to join it with some column, right? The join condition. So I'm just going to say AC.country equal to EC.country, right? And I don't need any filter condition here because I do not have any filter condition as per my requirement, so I'll remove the where clause, okay? And in the select, I can enter some column, but for now I'll just keep it star. When I say star, it's going to fetch all the columns from all the tables that is mentioned in your from clause, right? So it's going to mention all the columns from here and all the columns from here. So if I run it, you can see that I'm getting two records, right? Now this might be confusing because you're getting four columns because two columns are from the Europe table and two are from the Asian table, but if you look carefully, the country is the same: Turkey and Russia here, and here also it's Turkey and Russia, just that in the first table it's mentioned that it's coming from Europe, and in the second table it's mentioned that it's coming from Asia, right? Now what exactly happened was inner join—what it does is it will go into both the tables, it will try to join the table using the column that you have mentioned here, and any record that is matching—only the matching records will get returned, right? In this table, in European countries, I have Turkey and I have Russia, right? These two, and in the Asian table again I have Turkey and again I have Russia, right? So these are the only two values which will match with each other between the two tables, and that is why this query here is returning me two records. So basically, that is what inner join does. Okay.

Now just to give you another example here, let me do one thing: I'm going to delete some records from the table. So let's say I'll say delete from European countries. I'll just copy this, okay, and I'm just going to delete where the country is equal to let's say Russia, and I'll just select this and I'll run it. You can see that now in the European countries I only have four countries: Turkey, Germany, Poland, and France. Asian country stays the same. Now can you imagine when I run this query how many records will I get? Just think—it's going to find the matching record between these two tables. In the Asian countries I have Turkey, Malaysia, Russia, India, but in the European countries I have Turkey, Germany, Poland, and France. So the only matching country between these two tables is Turkey, right? So this query here should only fetch me one record, and that's exactly what it is doing, right? Now if I delete Turkey as well from the European countries table, then it's going to fetch me no records. But just to give you another final example, I'm going to delete a record from the Asian countries, okay, and from the Asian countries I'll delete let's say India, okay? So I'll delete the country India. Now in the Asian countries I have three records: Turkey, Malaysia, and Russia. Now can you imagine what will happen with the inner join? As you can guess, there will be no change, why? Because Turkey is still present here and Turkey is present here as well, right? So if I run, I'll still get one record. Now just to give you a final example, let me delete Turkey from Asia. So I'll delete the country Turkey from Asia, and you can see one record is deleted. Now in the Asian country table there is only two records: Malaysia and Russia, and in the European country table I have four records: Turkey, Germany, Poland, and France, and now if I run it, can you guess how many records will dispatch? It is looking for matching records, but there are no matching records in these two countries—in these two tables—hence you're not getting any record. Okay. So this is basically what an inner join does: it goes to both the tables, it will join based on this column. When I say join based on this column, it will try to link the two tables using this column. If there are any matching records, it will return it; if there are no matching records, it will return no records. Okay. So that's all an inner join does. I hope this is clear now. Now let's go back to joining our sales order and products table using the prod ID field and see what happens. So now just for the sake of showing you, I'll just run this, and you can see that I'm getting 10 records, okay? Because in the sales order table there are 10 records, and whatever product ID is present in this column or in this table is all present in my products table, so all the records are getting matched from the sales order, so I'm getting 10 records here, okay? Now this is fine, but as per my problem statement, I want to find the order ID, date, and product name for all the completed orders. Now if I want to find completed orders, it's basically I need to look at the status column from the sales order table, so I need to filter the data based on the status. So I'm just going to say so.status is equal to completed, right? So I just need to mention completed. Now the trick here is that in some of the records the completed is in lowercase, somewhere it is in uppercase. To handle the different cases, I can just use the lower function as I already explained in the previous query, so I'll say lower of status is equal to completed, and now if you read through this data, this is one record, 2, 3, 4, 5, and then 6. So there are totally six orders which are actually completed. So when I run this query, hopefully I should get six records, and you can see that I am getting six records. Okay. So this is basically the solution to my problem number three. Now let's move on to problem number four, and the problem number four basically is this one. So I'm just going to copy the data and query and put it here, and let me just move into this to the next line so you can read it. Let's read the problem statement. So we have been asked to sort the above query to show the earliest orders at the top. Now when they say above query, it's basically the query number three, right? So what they told—from the above query—this is the output that we got; we need to sort this data such that the earliest orders—so the orders that happened first—should be at the top, right? And also display the customer who purchased these orders. So we are now displaying order date, order ID, and the product name, but along with this we also need to display the customer name, right? So this is basically what we need.

To do as part of this query now, in order to solve this query, because this query is kind of like an extension from the previous query, what I'll do is I'll just copy the query that I have already written here, okay, and I'll put it here. So, in order to sort data, so in SQL, whenever you need to sort the data, the keyword that we use is `ORDER BY`. Okay, so you say the keyword `ORDER BY`, and you mention the column on which the sorting should happen. So, in this case, the sorting should happen on the earliest orders. So, when you say earliest order, it's basically depending on the date, right? So that means I'm going to sort the data based on the order date, right, and the earliest should be at the top, so that means in ascending order. Okay, `ASC` basically stands for ascending; that means the lowest date will be at the top, and then the dates which are of higher values will be later on. Okay. So, if I run it now, we can see that uh this is the 1st, 2nd, 3rd, 4th, and 6th, right? So this data is now sorted as per the earliest orders. Ascending is one way of sorting; that is the ascending order, but if I wanted to do the opposite, that is, I wanted to sort it in descending order, then I can just mention `DESC`, and if I run it now, you can see that the data is reversed; so the earliest date is at the bottom, and the last date is at the top. Okay.

Now, as part of this query, I still need ascending, but one thing to remember is, by default, if I do not mention whether it is ascending or descending, by default, SQL will always sort the data in ascending order. So, if I run it, you can see that I have the data in ascending order. Okay. So this is basically one part of my solution to this problem. The second part of this problem that I need to solve is, along with these three columns that is being displayed, I also now need to display the customer who purchased these orders. Now, if I look at my sales order table, I do have the customer information, but it's just the customer ID, right? I want to display the customer name. The customer name is not present in the sales order table; the only table where the customer name is present is the customer table. That means now, for this query, I also need to join this query with the customers table so that I can fetch the customer name, right? So, in order to do that, you can see that I have the sales order table already joined with products; so just after that, again, you need to now join it with the customer table, right, and I'll give an alias `C`. Now, in order to do this join, I'll again use the `INNER JOIN` concept, so that every matching record between this above result set and the customer table will get returned. Okay. So, again, I need to find a join condition; on which column should I do the join? So I know that in my sales order table, I have the customer ID field, right? We just saw that. So, if I run it, I have the customer ID field in the `C` in the sales order table. Same way, in the customers table, I have the ID field, right? So this ID in the customer table and the customer ID in the sales order table are basically the same; so I can use it for joining. So here, I'm just going to say `=` it is `C.ID`, right? So now I should be able to join it with the customer table, and here I, what I want to display, is `C.name`, right?

Now, this is a very important thing that I want to show. So let's say this `name` that I mentioned here is actually the product name, whereas this `name` that I mentioned is the customer name. So, if I run this query now, it's going to throw me an error saying that the column name is ambiguous. Okay, the reason for that is I just mentioned `name`, but there are two tables in my query, `products` and `customer`, who both have the column name. So, if I look at the table here, in the customer table also I have the column name; it's actually the customer name, and in the product table also I have the column name, which is actually the product name, right? So the same column names are the same, but they are of two different purposes, right? So, when I'm writing the query, I need to tell SQL. So SQL is trying to retrieve the `name` field, okay, and now it is confused whether it should retrieve this name from the products table or it should retrieve it from the customer table, and that is why it is very important that you mention the alias here, okay? So that means I'm just going to say `p.` because `p` is the alias for products table. So now SQL will know that, okay, I need to fetch this name from the `P` table, and I need to fetch this second name from the `C` table, right? And now, if I run this, now you see that it is actually working, and I'm getting the customer name here and the product name here. Okay. The only problem that I see here is both of this last column are having the value as `name`; the column name is `name`, right? It's not meaningful; so let's give a more meaningful alias here. So I'm just going to say the first one is going to be the product name, and the second one is going to be the customer name. Okay. So if I run it now, now you can see that the output is still the same, but the column names are more meaningful, right? Now, one important thing that you need to remember here is that you saw that I added the alias to the last two columns, but I did not add the alias to the first two columns. The reason why I did not add it is because I can add it; I can put it as `so.orderID` and `so.orderDate`, but I did not add it because these columns are only present in the sales order table; so SQL will not get confused because if SQL needs to fetch the `orderID` among all of the tables that is mentioned here, SQL knows that this column is only present in this table, so it will directly fetch it from there. But let's say there was another table that we used which also had the field `orderID`, then I still need to mention the alias. Okay. So I hope you understand the concept; I hope you understand why aliases are important, and I hope you understand how these joins are working.

Now let's try to solve the fifth problem in our problem in our case study, and the fifth problem is basically this one. So I'll just copy it here; I'll put the problem statement here, and let's try to understand this. So we need to display the total number of orders corresponding to each delivery status. All the information related to orders is present in our sales order table, so I'm just going to query this table here. So in the sales order, you can see that I have totally 10 orders as shown here; every record in the sales order represents one order, right? What we need to do is we need to find the total number of orders corresponding to each status. Now let's ignore the second part for now; imagine if you only had to find the total number of orders, how would you do that? I can easily do that by just using a function, that is the aggregate function `COUNT`. I could say `COUNT(*)`, and if I just do `SELECT COUNT(*) FROM sales_order`, I will get the total number of orders, right? And that is, it is telling 10, which is actually correct, but we are not looking for finding the total number of orders; we are looking to find the total number of orders corresponding to each delivery status. Now, what is a delivery status here? It can either be completed, pending, on hold, rejected, cancelled, etc., right? So we need to write a query which is going to count the number of records specific to the unique values present in this particular column, right? Now, whenever you have this kind of a requirement based on certain value in a column, you want to do some aggregation, whether counting or sum or some other kind of aggregation, then this is a good place where you should be using the concept of `GROUP BY`. Now I'm going to quickly explain you the concept of `GROUP BY`. I'll use, I try to use the same table, the sales order; so I'm just going to query the table again here, but what I'll do is, instead of querying all the columns, I'll just mention one column, that is `status`. Okay. So when I do `SELECT status FROM sales_order`, I'm getting the 10 records as you can see here, right? Let me just move this ahead. So I have totally 10 records, right? Now there is one concept in SQL which is called as `GROUP BY`, and after `GROUP BY`, you need to mention the column on which you want to do the group by. Okay, I'll explain you what this does internally, but let me first run this query. So I have done the `SELECT status FROM sales_order`, which, you know, has written me 10 records. Now I'm just adding one line, `GROUP BY status`. Okay. Now when I run the same query, you can see that I'm only getting six records, right? Why is that? So, and if you look carefully about the six records, these are all the unique six records: pending, rejected, on hold, cancelled, and I have two records with completed, why they are separated here because their case is different; so SQL treats them to be separate values or different values, right? So now you could imagine that, okay, but something similar to this can also be done by using the `DISTINCT` clause, right? So if I had used `SELECT DISTINCT status FROM sales_order`, and I'm still getting the same output; so that means this query here and this query here returns the same output, right? And that is true until this stage; this is true because one of the common questions during interviews is the difference between a `GROUP BY` and a `DISTINCT` clause. Okay, people might ask you that why are you using `GROUP BY`; why don't you just do it using `DISTINCT`? Yes, if you only had to find unique values, then you could use `DISTINCT`, but here we are not only trying to find unique values; we are trying to find the total number of records corresponding to each of these values, right? So let me just comment out this `DISTINCT`; I don't need this; I just wanted to show you, okay, and go back to this `GROUP BY`.

Now, what happened when I just executed `SELECT status FROM sales_order`? I got 10 records, but when I added this `GROUP BY status`, I got six records. Now what basically happened was, what SQL is doing when you say `GROUP BY` is, when you say `GROUP BY` and when you say a specific column, SQL will go into this column and it will see in this column how many unique values are present, and for each unique value, SQL will create a group internally. Okay, you will not be able to see that group, but internally SQL creates a group. Okay. So a group is like, you can, so let's say you have a group of friends, and you can name, uh, you can give a name to your group, right? Let's say there are multiple such groups; so each group of friends can have one name, right? Something like that. So you can imagine that I have 10 statuses here, 10 records with different statuses; when I do `GROUP BY`, SQL will find the unique values, and for each unique value it's going to create a group, and so when I run it, you can see that I'm getting six values, but internally what you see here are these six is actually each value here represents one group. Okay. So there is one group for pending, completed, rejected, on hold, cancelled, right? Now this is fine. Now once you have created the groups, then you can basically apply any aggregate function on the grouped data. Okay. So when it comes to aggregate functions, aggregate functions, there are mainly five aggregate functions. So we have already seen some of them: `COUNT`; we have then we have seen `SUM` as well, and I think we have already seen `AVERAGE`, and then there are a couple of others: `MIN` and `MAX`. Okay. Now what this basically does is, `COUNT` will count the number of records; `SUM` will find the, it will basically add up all the values; `AVERAGE` will find the average of all the multiple values; `MIN` will basically find the minimum value within a list of values; and `MAX` will kind of find the highest value within a list of values. Now, whenever we are using `GROUP BY`, SQL allows us to use any of these aggregate functions with it. Okay. So I have this query here, and I'm just going to keep this as it is; I'll just rewrite it down, and if I just run it, I'm getting the six groups that is present as I told you. Now, for each of these groups, I can apply any of these aggregate functions. So let's say if I apply `COUNT` of, and I need to provide a column name here, or I can just provide `*`. Okay. So let's say if I provide `COUNT(*)`, and I'll just move the `FROM` clause down, and if I run it now, what happens is, I'll still get the same six records, but there'll be an additional column added here. Okay. So if I run it, you can see that I'm still getting the same six records, but there is a second column added, that is the count, and here if you see the value in `count` for `pending`, it's telling one; for the `completed` in lowercase, it's telling one; for the `completed` in uppercase, it's telling five; and then for all the three other statuses, it's telling one, right? Now what exactly happened? If you can, if I just, let's say I'll just copy this value, I'll put it here. Okay, I just want to explain you; so this is the value that I have got from my below `GROUP BY` table, and if I just do a `SELECT * FROM sales_order`, you can see that for `pending`, there's only one record where it is pending, and similarly for rejected, on hold, and cancelled, there are only one record; so rejected, on hold, cancelled, everything, there's just one record, and that is why what SQL is doing is, it is grouping the data based on each unique value in `status`, and for that each unique value, it's going to count how many records this unique value is present, right? For pending, on hold, rejected, cancelled, there is only one record each where this unique value is present, right? But when it comes to the lowercase `completed`, again, there is also only one record where this lowercase `completed` is mentioned, and that is why this lowercase `completed` is again has one, but when I look at all the other data that is for all the other records where I have this uppercase `completed`, so uppercase 1, 2, 3, 4, and I think these five, so these are the five records where that value `completed` is mentioned in different rows, right? So totally five rows, and that is why I'm getting five here. Okay. So this is basically what a `GROUP BY` does. When you do a `GROUP BY` any column, it will group the database on that column; that means for each unique value in that column, it's going to create one group, and within that group, whatever function you mention, it's going to basically run that function separately for each of that group. Okay. So for each of the group, it's going to run the `COUNT` function, and it's going to return the total number of records for each of that group. Okay, and that is why I'm getting from 10, I'm only getting six records because there were six unique values, and getting the total number of count, and this is basically the solution to my problem as well. Okay. So if you look at my problem statement, find the total number of orders corresponding to each delivery status, and that's exactly what I have got, right? So here I can just give a better alias like `total_orders`, right, and, and if I run it, this makes more sense, right? Each status, how many orders are present, I'm getting it here. Okay. So this is the solution to my problem, and this is also about `GROUP BY`. I hope you understand the concept and how internally this is basically executed.

Now let's go back into our problem statement, and I think we finished five. Now let's look at the sixth problem, and I'll just put it here, and this one is sixth problem, problem, right? So the sixth problem statement that we have basically states: For orders purchasing more than one item, how many are still not completed? So in order to solve this problem, the first thing that we need to focus on is we need to find the orders where there has been more than one item purchased, right? So we need to look at the tables where we can get information related to the orders, and there is only one table that has this information in our data set, which is the sales order data set. So if I run it, you can see that I have all of these orders, and if I want to know how many items were purchased in each order, I can look at this column `quantity`, right? They're asking us to find basically look at the orders where there has been more than one item purchased; that means wherever the quantity is more than one, that is the data that we need to look at, right? So we need to find for orders purchasing more than one item, how many are still not completed, right? So we need to find the count, but we need to find a count of based on the status which are not completed, right? So let's try to look at that. So the very first thing that we will do is, I'm just going to, okay, I'll just keep this query as it is, and I'm just going to write `SELECT FROM WHERE`. Okay. So now what do I want to display in my `SELECT`? Okay, I will come to this at the end. Okay, first let's see from which table should I fetch the data. I know that I am only looking at the sales order data, so everything comes from the single table `sales_order`. Right, I'll give an alias like `so`. Then I want to know my first filter condition will be based on the quantity because I'm only interested in where there was more than one quantity of items purchased, right? So here I can just tell `quantity > 1`. So this is going to be my first filter condition, right? And once I have that, so let me do one thing, I'll just do a `SELECT *`, okay, and you can see that from 10, now I only have six records. This is kind of like the subset of records that I'm interested in. Now, even within that, I'm only interested in how many are still not completed; so anything that is completed, I'm not interested in that; I'm only interested in the status where it is not yet completed, right? So I need to put another filter condition. So when you want to add multiple filter conditions, you can use this operator `AND`. So if you want both the conditions to be satisfied, then we use `AND`. Okay. So here I'm just going to say `AND status != 'completed'`. Right. Now the only problem here is some of the completed statuses are in uppercase and, okay, in this particular case, I think all of these statuses are in uppercase, okay, but just to be on the safer side, I can add this function `LOWER`, so that whether it is in uppercase or lowercase, it does not matter because I will always be converting this status into lowercase and comparing it with the lowercase value, right? So now if I run it, you can see that I'm getting two records, right? So I have pending and rejected, but I need to find out how many are still complete, how many are still not completed; that means I need to find a count. So I'm just going to say `COUNT(*)`, and given alias like `not_completed_count`, okay, something like that, and if I run this, this is basically the solution to my problem number six. I hope this is clear. Now quickly let's move on to our problem number seven, that is this one. So I'm just going to paste it here, and let me just move this to the next line, and let's keep it here. Okay. So let me just read the problem statement. So we need to find the total number of orders corresponding to each delivery status. I think we already solved a problem which basically did exactly this, right? And that was our problem number five. If

You remember this was the problem, right? And we have this is the solution that we got, right? So, in fact, I'm just going to copy this query that we already used for as our solution in query number five, and I'll paste it here. Okay, so we need to find a total number of orders corresponding to each delivery status, which is already being done by this query. But here they are asking us to do a couple of additional things. Number one is by ignoring the case in delivery status. So if I run this query here, you can see that my delivery status, I have a separate record for lowercase completed and a separate record for uppercase completed. Here they're asking us to ignore the case, right? So that means both of these records should be displayed as a single record because we know they both mean the same, right? And I think the count here should be six because 5 + 1, right? That is what we need to do.

Then they're saying the status with the highest number of orders should be at the top. So we need to sort the data such that I think completed is having the highest orders; it should be at the top, and others should come after that, right? So let's see how to do that now. First and foremost, how do I basically modify this query such that I can treat both of these records like the same record? So I'm going to give you two different solutions to this problem. The first solution is going to be slightly lengthy; I'll be using a subquery for that, and the second one is going to be very simple using an inbuilt function. Okay, so let's first look at how I can solve it using a subquery. Okay. Now, what exactly is a subquery? Whenever you write a query inside a query, that inside query is basically called as a subquery, as simple as that. Okay, you will understand when I come to that solution, but first and foremost, if I query the data from my sales order table, soel, let's start from sales order, and if I see here, I can see that some of the records are in uppercase completed, and there is one record with lowercase completed, right? Now, if I want to rewrite this column, right, so instead of having this status, let's say I'm going to create a new status field, so I'll write status, and I'll just execute that. I'm getting all the different statuses, but now I'm going to create another column here, okay, and I'll I'll call it like, let's say, new status, okay, new status, okay, or I'll say updated status, something like that. Okay. Now, I have not given anything here; this is basically a blank value. If I run it, you see that it's basically returning me blank or null. Okay, so if you if you ever hear of what is null, this is what null is—a blank value. Now, what I want to do here is I want to basically modify whenever I have this lowercase completed; I want to change it back to this uppercase completed. So I can do that by using something called as a case statement, right? Now, what I'm going to do is I'll explain to you what this case statement does. So I'll say case when, okay, if you can treat a case statement to be something like an IF/ELSE statement, which you might have seen in other programming languages. So I say case when the status is equal to completed, okay, that is when I have this particular status. I'm only trying to—whenever SQL is processing this record, whenever it finds lowercase completed, I want to change its name or value to uppercase completed. So when the status is equal to completed, then, and I'll just move this here, that name, okay, and I'm just going to say then I want to display it as completed. Okay, so here the C is in uppercase; that's the only difference, right? Else, if it is not completed, then I want to display whatever the value is coming from the table. Okay, so I'll just say end as, and this is my column alas. Okay.

Now, this is the statement that I have written. Let me explain to you what is happening. So SQL, it's going to process every record one by one. Initially, it will basically process this record, right? So when it is processing the first record completed, it sees that the status—it is basically checking if this value is equal to this value. Okay, this is lowercase completed. Now, until it comes to the sixth record, none of these records will get matched, right? For the first five records, the values are—it is not exactly this one, lowercase completed, right? So this condition will never be satisfied. So if this condition is not satisfied, it will not go inside here; okay, it will directly come into the else part, and it will display whatever is there in the status. So for all of these five records, it will just execute this else part, and it will just copy whatever is there and it'll put it here. Okay, but when it comes to the sixth record, this will actually match because the status will be equal to this lowercase completed, and then it will come inside this when part, and it will display whatever I mentioned in the then, and that means it will become this uppercase completed, right? So this is the only record where this status will match to the value that I have given, and this is the only record where the transformation will happen. So from this lowercase completed, it will become uppercase completed. For everything else, whatever is present in the status value, that will get displayed here. Okay. So if I run it now, you can see that everything is the same, except where I have lowercase completed; it has become uppercase completed. Then everything else is is the same. Okay. So this is what I have achieved using the case statement. I hope you understand how this case statement works. Okay. Now, what I want to do is I will use this result set in my group by clause, right? So what I'll do is I'll just copy this query. Okay, this is a query that we used in our problem number five, right? And because I'm using it here again because the requirement and the problem statement is the same, right? So what I'll do here is instead of fetching the data in the group by from the sales order table, I will basically provide that query that I had written previously, right? So I'll just move this slightly down, and I'll put that query here. Okay, and I'll just move this right, and I'll give an alias for this, let's say subquery SQ. Okay. Now it's the same query that I had—fetching all the data from sales order, group the data based on status, and count the total number of records for each status. The only difference that I did here was instead of fetching all the data from the sales order table, I fetched it from a query. So this means in your from clause, you can not only mention tables but you can also mention another SQL query, and you can also mention some other objects like views, okay, which we will not be covering today. But now I just used the SQL query here, but always remember to put the SQL query inside the parenthesis and to give an alias for that entire SQL query. Okay, so I gave SQ. Now, if I run now, what happens is when SQL executes this, so from the from clause, what SQL basically wants is it wants a result set. When I say result set, it's basically the combination of rows and columns. So this whole thing here is a result set of 10 rows and I think what, seven columns, right? Now, this query here is—whatever the data it gets returned—it is also a result set; it's a result set of 10 rows and two columns, right? Now, what happens is I'm fetching—when SQL needs to process this group by, it's going to refetch the data from the result of this query. Okay, since this is a query inside our main query, this is called as a subquery. Okay, so that's all what a subquery is—a query inside a query. Now, I'm not going to be changing anything else in the outer query; I'll just run it and see what happens, and I'm basically not getting the correct output. Ideally, I did not want two different records for completed; I want this to be treated as one record. And can you imagine why it's not working? The reason why it's not working is because I'm still using the statSt field, and the status field has two different completed values, right? I should basically be using the updated status column, so that is what I'll use here. I'll say updated status in the after column, and I'll group the database on updated status. Now, if I run it now, you can see that I'm getting only five records, and there is only one record for completed, and its total number of records is six. Okay, so this is basically what we wanted. The last thing that we wanted in our solution is to sort the data based on the highest number of orders. So here I'll just tell—in order to sort data, I'll say order by the count; I'll provide this alias and in the descending order, right? And now if I run it, this is basically the solution to my problem number seven. Okay, so I think yeah, it's problem number seven. I hope this is clear, and I hope you're able to understand this. Now, I told you that I'm going to give you another solution to the same problem—a simple solution. Now, for the simple solution, what I'm going to be doing is again I'll copy that same group by clause; I will put it here, right? And what I'm going to do is if I execute this query, I will get two records for completed because of the different case. So I can ignore the case by just using the lower or the upper function. So I'll just tell lower, okay, and I will again call this as, let's say, status, okay, and I'll use this lower status again in the group by. Okay, that's all; nothing else. And if I run it now, now you can see that I'm automatically getting five records, okay, and the completed is basically merged into one, one, and it is showing totally six, right? And again, if I have to sort the data, I can again order the data based on total orders, and this will basically be my final output, right? So this query here and this query here is kind of providing the solution to this particular problem. Okay, I hope this is clear. Now let's move on to our problem number eight, and I'm just going to copy this and I'll go back here and I'll say eight.

Okay, so the problem statement basically reads: Write a query to identify the total products purchased by each customer. So here we want to find the total products purchased by each customer, right? That means, of course, we need to use the customers table because we need to probably display the customer name. So this is one table that we need, and the second table is basically we want the table which can help us to identify how many purchases this customer has made, and there's only one table which can return us that data, and that is the sales order table because only in this table we know what purchases have happened, right? So I need to use these two tables. So here I have the customer ID, but this is not very clear who the customer is; ideally, it would be good to display the customer name, and I want to know how many purchases each customer has done, right? So first and foremost, what I need to do is I need to join the two tables. So I'm just going to say select from where, and in the from clause, I know that I'll be using these two tables, so I'll say sales order SO and I'll do an inner join. Now, one thing to remember is you can mention inner join or you could just mention join; they both are the same; the inner keyword is actually optional, so I will remove that. Okay, so I'll just tell join with the customers table C, and I'm just going to say how do I join it? I know that I have this ID column in the customers, which is actually the customer ID; I'll join it with the customer ID table in the sales order table, so customer ID, right? And here let's say I'm just going to say SO.star. Okay, so this will return me—so we know that when I say star, it returns all the columns in the table, but when I say SO.star, SO is the alias for sales order, that means it will return all the columns from the SO table, okay? And when it comes to the C table, the customer table, I only want to display the name of the customer, okay, and I'm going to give an alias for it like customer, okay, so, and let's say I will give it like my customer name, okay, and I don't have any filter condition, so I'll remove it here. Okay, so now if I run it, I'm still getting the same 10 records; all of the first I think seven columns are coming from the sales order table; the last column is coming from the customer table, right? Now, what I want to do is I want to find the total purchase done by each customer. So if I wanted to find the total products that have been purchased, I could just count, right? Or basically, I could do a sum of this quantity, and I would know how many products have been sold, which we have already done in one of our queries, right? But what we want to do is we not just want to find the total products that are sold, but we want to find the total products that are sold corresponding to each customer, right? Now, I think this—you you know what we should be using it here, right? So if we want to find the aggregated value specific to a value within a column, then this is an—this is where we should be using Group by, right? And now the group by that we should basically be doing is the customer on the customer name, right? So we need to find something for each customer. So for each unique value in the customer, we need to do something. So here I'm just going to say C. group by C.name, okay, and let's say I'm just going to—okay, instead of printing all of these columns here, let me comment out this for a moment; I'll just print let's say Quantity, okay, so I'll say Quantity, and I need the name, right? And I'll do one thing; I'll just move this to the left so it becomes more meaningful, okay, and I'll just run this. Okay, I did not make any change; I just—to displaying two columns here. Okay, so here I have all my customers, and here I have the total products they have purchased in each order, right? Now, what I want to do is I want to find for each customer how many products they have totally purchased, right? So I need to do a sum. So for example, let's say Logan Short; if I have two records here, totally 3 + 3 = 6 products he or she has purchased. With that said, if I do a group by, let's say the customer name, okay, and let's say for now I'll just comment out the quantity, and if I run it, you can see that I have 10 records in under the under this query, under customers, but when I do a group by the customer name, I'm only getting three. That means there are only three unique customer names present in the sales order data set, right? Now, in the customers table, I have four customers, but looks like only three customer information is present in the sales order, which actually means that only three customers have actually done some purchase, right? So there is someone else—I think it is AR Duke—who has not done any purchase yet. Okay, so now let me go back to this query and run it. So when I do group by this customer, I'm getting only three records. Now I know what I need to do; I need to find basically add together all the quantity values corresponding to all the records which has this customer name, right? So I got this three records because there were three unique values in the customer name; for each unique value, one group was created. Now, within that group, I can apply any aggregate function. Here I'm just going to say sum of this quantity, and I'll say I'll give an alias for this like total purchased uh products, okay? And if I just run this, you can see that Megan Harley has purchased 12 items, Logan Short has purchased seven, and Rosa Chan has purchased five items. I hope this is clear, and this is basically the solution to our problem number eight. Now let's go and try to solve the problem number nine. So I'll just take this, and I'll go here, and I'll put this. Okay.

Okay, so the problem number nine basically states: Display the total sales and the average sales done for each day. Again, what we need to do here is we need to find the sales that have happened for each day. If I want information related to the sales, then the only table that has this information is the sales order, right? So we will use that. If I run it, I have this. Now I want to know how many total sales have happened, but the problem is if you look at the sales order, I have all the information about the sale, but I do not have the information about the price, right? I don't know—so let's say for this order number, 12 quantity of products were purchased, product one, but I don't know what was the total cost of that order; that is not mentioned here, right? Now, the price is basically only mentioned in one table, and that one table is the products table. So if you remember the data set in the products table, for each of the product, I know what is the price here; that is mentioned here. That means if I want to know the price so that I could find the total sales and average sales for each day, then I need to join these two tables, and that is exactly what I'm going to do. So I'll say select from, and probably I don't need any filter, so I'll not add that, and I'll say sales order SO. I'll join it with the products table, and I'll give an alias like P, and you know by now on which we can join it, so I'll say P.ID equal to SO.prodID, right? And here let's say if I just do a star, and if I run it, I'm getting all the data joined which are matching in these two tables, right? Now, what I'm actually interested in is I want to know the order date because here I want to find the total sales and average sales for each day, right? That means I need the order date, right? And then I need the price, right? So the price is coming from the products table. I'll not give an alias here because I can give—no problem—but I'm not giving because this is kind of—these column names are present only in specific tables, so I think SQL should be able to understand from where it should fetch it, right? So if I just run it now, you can see that for all the different order dates, I am getting all the information related to the price. The only thing that I want to do now is I want to find total sales, right? And average sales for each day, right? Now, here you can see that there are some of the records with the same date, right? So I need to group the data based on each day, right? So Group by this particular column. So for each unique value, I'll get one group, and within that group, let's say if you consider the first two records within this group, I want to do the aggregation. So I—in order to find the total sales, I'll do the sum of this; in order to find the average sales, I'll do the average of this, right? So here I can just tell Group by order date, and I'll keep that as it is, and here I'm just going to say sum of price, and this is going to be my total sales, and for the next average sales, I'll again say average of price, and this is going to be my average sales. Okay, so if I just run this, you can see that I'm getting the data that I wanted. So for order date, and just to make it more meaningful, I'll sort the data by the order date, so order by order date, and if I just run it now, you can see that I have the first of Jan 2024, 2nd, 3rd, 4th, 5th, and 6th of Jan, and I have the total sales for each day and the average sales for each day. Okay, so this is basically my solution to the problem number nine. Now let's go back and solve the next problem, that is my problem number 10, so I'm just going to

paste it here. This is my problem number 10. Let me just move this to the next line. Okay. Now let's see how to solve this. Okay, so the problem statement basically states: display the customer name, the employee name, and the total sale amount of all the orders which are either on hold or pending. Let me straight away write the three commands or the three clauses: select, from, and where.

Now, can you guess what are the different tables I will need to solve this problem? So I need to display the customer name, so definitely I need to use my customers table, so I'll give an alias C. Then I need to fetch the employee name; the employee name is only present in the employee table, so I need to mention the employee table, right, E. Then I need to find the total sale. Now, the total sale, the sale information or the price information, is only present in the products table, so I'm going to say join with the products table as well, okay, P.

And then I need to basically, okay, do something, do some filters, so which are either on hold or pending. So that means the status, right? They are basically asking me only to basically do whatever this calculations I need to do to find a total sales for each customer name only when the status is either on hold or pending, only for these, only for orders which are in these two status. So that means the status. Now, the status is not present in any of these three tables; the status is only present in the sales order table, right? So let me just mention join sales order, okay, so, and then here I can add a filter saying that S. So do status is in because I want it to match with on hold and pending, right? So if you remember in the sales order table, just to show you, select star from sales order, I have, I think, on hold status, then I have pending status, right? So I'm only interested in the record where the status is either on hold or pending. So if I want to match a particular field with two values, you know that I can use the in operator. I cannot use equal to; with equal to I can only put one value, right? So here inside the in, I can just mention the two values. So the first one is on hold, so I'll just copy this and I'll put it here. The second one is pending, so I'll put it here. Okay, so this is fine.

Now there is a major problem for me to join these columns. Can you imagine what? What I have done here is first I mentioned a customers table. Now, just to show you, okay, I'll just do one thing; I'll just copy all the four tables so I can query and show it to you what the problem here is, right? So first I wrote the customers table. If I look at my customers table, I have the ID, name, and email. Then I wrote the employee table; in my employee table I again have ID and name. You might think that probably we could join these two tables using the ID, but that would be a wrong thing to do because in the employee table ID means employee ID, whereas the customer table ID means customer ID; they are two different things. The column names are the same, but they are, they mean two different things. You cannot join with the customer ID and employee ID; they are two different things, right? Now, ideally the customer ID and employee ID is present in the sales order table. In the sales order table I have the customer ID and employee ID. I can use these columns to join the previous tables, right? So if I have to join this customer ID with the customer ID in sales order, I cannot mention basically, I cannot mention it here; so it has to kind of flow from the main table, right? Here the sales order is the main, kind of like the glue that stitched together all of the other three tables, right? Because only the sales order table is having the fields with which I can form a link or relation between the other three tables. So ideally, if you have this kind of a main table, then you should put this table at the top, okay? Otherwise you will not be able to join; you cannot mention the join condition here. You can try it yourself, okay?

So what I'll do here is I'll just remove that from here; I'll move it um here, okay? And now I'll mention my join condition: sales order, so s.o. employee ID equal to e. ID, and then here I'll just copy the same thing here; I'll put it here, and s.o. I think it is prod ID, it will go with p. ID, and the same thing copied here, so s.o. I think it is customer ID, it will join with c. ID. Okay, so now this is a right way of joining all the four tables, and you can see that I'm using inner join, but I not specified the inner keyword because it is an optional keyword; whether you mention inner join or join, they basically are the same. Okay. Now I think I have done all the joins; let me just put a star and I'll just run it, so I should get all the data, but I'm not, because the table name employ is wrong; it is employees. Now if I run it, I'm still getting an error because I have done a typo here; it is customer. Now let me run it, and now it works. Okay, so now if you see when I join these two tables, what happened was sales order joined with the employees; it, I think it will fetch me 10 records because there are 10, and all the employee IDs mentioned in the sales order, they are all present in the employee table, so all the records will match to each other. Same way, when I join with the products, I'm still getting 10 records because all the products mentioned in the sales order is present in the products table, so there is a complete match here. Same way, when I join with the customer table, again I'm getting 10 records. The only reason the whole query here is returning two records is because of this filter condition, because there are only two records where I have the status on hold and pending. Now what I want to display in this, in this problem statement, is I want to display the customer name, so I'll say c.name as customer. Then I want to display the employee name, so I'll say e.name as employee, right? And then I want to display the total sale amount, right? So if I want to find the total sale amount for all orders, that means I need to use an aggregation, right? I need to do kind of like a sum, but before that I'll just do a group by, so I'm going to group by this name, that is a customer name, right? I'll say Group by customer name and the employee name, e.name, right? If I just run it, you can see that I'm getting two records, right? So this particular record which is on hold and pending, it is basically the orders purchased by Rosa Chan, and the employees were two different employees: Abar Khan and Nina Kumari, right? If I want to find the total sale amount, I can just do a sum of the price; the price is basically coming from the products table, p.price, but now I remember that this p.price cannot be the total value of the order, right? Because in an order there could be multiple items purchased, so ideally the total amount of the order could be quantity into price, right? So ideally what I should be doing is I should say the quantity, that is s.o.quantity into the p.price, right? So this is what I should be doing, and exactly this is what I should have done in my query number nine as well when I was trying to find a total sales. We'll try to fix this problem number nine uh after we, we are done with the problem number 10. Okay, so I think I just ignored that particular part that in order to find the price I need to multiply it with the quantity, right? Because in an order, if let's say three items were purchased and the price of each product was 100, the total price of the order would be 3 into 100, that is 300, right? So that's what we are doing. So I think this is the total value of the order, so I'm, and I'm just going to give an alias like total sale, and if I just run this, let's see if this works. If I run it, I'm getting 2,400 and 2,100, but let's say if I did not had added this quantity, okay, and if I just added the price which I like did previously and it was a mistake, if I run it now we can see that I'm getting a wrong price; this is not correct, right? Here it's basically the same because probably in this order there was only one quantity, right? But the correct way of doing it is you, if you want to find the total amount of the particular order, you need to multiply the price with the quantity, right? So I think this is the correct solution to this particular problem, but the solution that I gave in the problem number nine is slightly wrong, so let me go back and try to fix it. So here what I have done is we need to find a total sales and average sales for each day, and I got this, but this is not right; I cannot just consider price; I need to consider the quantity as well. So here I need to say, in order to get quantity, okay, it is present in the sales order, so I can just quantity into the price, right? And I can put it here as well. I'll just move this to the next line; I'll move this to the next line, and now if I just run it, this is basically the correct output. Okay, so just a correction to my problem number nine; if you have already seen it and wondered why did I not consider the quantity, it was just a miss from my side. Okay, so this is the right way of finding the price. I hope this is clear, and I hope you understand why we are doing that. Okay, so this is basically problem number 10. Now let's move on to the next problem, that is my problem number 11, so I'll copy the problem statement; I'll go back here, 11, I'll put it here, and let me just move this to the next line. Okay. Now let's understand the problem statement: fetch all the orders which were neither completed or pending or were handled by the employee Abar; display employee name and the all details of order. Okay, so I think it's not or but of order. Okay. Now what do we need to do? So we need to fetch all the orders, so let me just straight away write the select, from, where clauses, and then of course I need the sales order table because I need to fetch all the orders, so straight away I'll just write that sales order; I'll give an alias like s.o. So I need to display all the orders which were neither completed or pending, so straight away I can put a filter condition saying that where the status is neither; that means it should not be in completed or pending, right? So what I'll do is I'll just put a lower uh function here so that I can match it with the lower values; I'll just put this one, so status is should not be completed and it should not be pending, right? So I'll do that. So this is one condition, or it should be where handled by the employee Abar. So either I should fetch all the orders other than these two orders which belong to other than these two status, or I should fetch all the orders which were handled by this particular employee, Abar, right? Now, in order to get this employee information, I need to join this table with the employee table, so I'll say employees, and I'll give an alias E. I can join it using the e.ID is equal to s.o.EMP ID, right? And then I'll add my filter condition. So now if you look at this problem statement, they are asking us we should find all the orders which should, first of all, they should either not be in any of these status, or they should be handled by the employee. So either this condition should get satisfied or this condition should get satisfied, right? So generally in all of our previous queries we used to use whenever we had multiple filter conditions we used like the and; if you, I think we must have some cases where we used and, right? Maybe I'm not getting it now, but you might have seen. So if you, whenever you are adding a filter condition after the first filter condition, if you basically add a second filter condition and you put it in between and, and that means both the filter conditions should be satisfied, but in our case we have two different filter conditions: one is with regards to the status, the other is with regards to the employee, but they, they have mentioned it to be or, so any one of the filter condition, if it is true, that is also good enough for this query, right? In that case, when you want like when any of the condition, any of the filter condition needs to hold true, then instead of and we should be using the or operator, okay? So that's what I'm going to be doing here, and here I need to say that the employee name is given Abar, right? So for that I'll just tell e.name is equal to Abar. I think Abar probably has a last name as well, so just not to get make it wrong, I'll just use the like keyword here, so I'll say like, and it basically the name should have Abar in the employee name, right? So I think that's also, this is my second filter condition. First filter condition, I put an or, so all the records which satisfies this will be returned plus all the records which satisfy this also will be returned from this query, okay? And then they are saying display the employee name, so I'll just put in that, so e.name as employee, right? And all the details of order, so I'll say s.o.*. So now let me just see if this query works, and I'm getting an error, and the error is s.o.order does not exist. Okay, so it's not s.o.order; it should be s.o.*, right? So all the columns of orders table, the sales order table, will be displayed. Now let me run it. You can see that I am getting the result, so I have Nina Kumari, Abrar Khan, and Nina Kumari. Now if you look carefully, what are the status that is not completed and pending? I know in with regards to complete, we have seen the data set; there are six records or six orders which are completed, and one of them is pending, and we can see that the three records that are displayed here belong to the status on hold, rejected, and canceled, so none of these two status are shown here, right? So I think all these three records were returned directly from this condition, okay? So the first filter condition. So let's say just for the sake of understanding, what if I did not include the last filter? I think I'm still getting the same records, right? Now the reason why this filter is not making any change for this particular data set is because probably because all the orders that were handled by Abrar they are basically falling under these two status, okay? Just to make sure that my understanding is correct, let me just query the data from this table, so select star from sales order table where, okay, so I don't know employ ID, right? So let's do one thing; let's just check the employee table, so employees table; in employees table, Abar's employee ID is two, so here I will just say sales order where EMP ID equal to two. If I run it, I can see I have six orders handled by Abrar, and all six of them are either pending, completed, or rejected, right? Pending, completed, or rejected, and what I get here is basically, okay, so I think this is a record which kind of holds true for both the filter conditions. So let's say just for the sake of better clarity, I'll just fil, I'll remove the first filter and run the query. I'm hoping I should still get one record, that is this record, okay, order ID 8, but I am not getting that record, so that means I'm doing something wrong, okay? And I guess I understood what I'm doing wrong; probably it's because Abar, the first letter A is basically in uppercase, and I am not handling that, so let me try to handle that, so I'll put a lower function here, and let's see what happens. Now I should get at least one record, okay? So I'm getting multiple records, okay? I'm getting all of this, so six records, but so I think this is right. Now let me remove the first, and now let me add the first filter as well, okay? Now I think I should get much more records, okay, in my output, and I'm actually getting eight, so I think Nina Kumari, the two orders handled by Nina Kumari is showing up, and then I think the six orders handled by Abar is, is also showing up, okay? Even though some of the orders are completed or pending, they are also showing up, why? Because here the filter is or, okay? So all the records which satisfy this filter condition plus all the records which satisfy this filter condition is actually showing up in this query, okay? So this is actually the solution to this problem. I hope you understood; the important thing to capture here is how your query results will differ by using an or. Just for the sake of explaining, let's say instead of or if I had used and, then, then can we guess how much records would show up? Now from the first filter condition, I think we would only get probably two records which are not completed and pending, so I think on hold would come up, rejected would come up, and canceled would come up, so three records should show up from the first filter, but then I have the second filter which are only handled by Abar, and I think out of these three records, so that is on hold, rejected, and canceled, there's only one record handled by Abar, right? So only one record should show up, number that is with the order ID 8. Let's see if my understanding is correct, and exactly that's what is happening. Okay, so just by changing and to or, your complete output changes. Okay, I hope you understand the difference between using an and and or, okay? So that is the purpose of having this query. Now let's try to move on to the next problem that we have, and that is the problem number 12. Let me just copy it here, and let me just uh put it here. Okay, so the problem number 12 basically states: fetch the orders which cost more than 2,000 but did not include the MacBook Pro; print the total sale amount as well. Now that means I first of all need to get the orders which are costing more than 2,000, so straight away let's try to do that, so I'll say select from where, right? And select clause we'll try to fill it later, but in the from clause first of all I need my sales order table, right? Sales order, and I'm going to give an alias so. Now I need to join it with the products table because I need to know which orders have cost more than 2,000, so I need information about the price, and price is only present in the products table, so I have to join with products, and I need to join it using the join condition p.ID equal to s.o.prod ID, right? So this is fine. Now I, let's, let's do one thing; let's straight away add this filter condition, so fetch the orders which cost more than 2,000. Now if I want to know which order has cost more than 2,000, just for the, for the sake of understanding, let's do one thing; I'll say order ID and I'll say Quantity and I'll say the price, okay? And let me just run this. Now when I run this, I'm getting 10 records; that is fine. So in the first order, two products were purchased; the price of each product was 800, right? So the total price of that order would be quantity into price, right? So I'm just going to do that; I'll say Quantity into price as total, toal cost, okay? And if I run it now, you can see that this is what is the total cost of each order, right? I am, so that is fine, and here the fetch the orders, and here the problem statement basically tells to fetch the orders which cost more than 2,000; that means the combination of this should be more than 2,000, so I should only fetch records where it is more than

2,000. Right. That is my first filter condition. Let me straight away run this, and now you can see that I'm getting records here, right? So this is kind of fine; so this is okay. Then I have another condition to add, that is, but did not include the MacBook Pro. So among these, I should see that if there were any orders where MacBook Pro was purchased, then I should not include it. Now I do not want to include any records which belongs to MacBook Pro, right? So what I'll do is, first of all, just for the sake of printing, let's see if I have any MacBook Pro products here. So I'm just going to say p.name—this should be my product, right—and you can see that these three orders are actually of MacBook Pro, so I should eliminate them, right? So I can put a filter condition saying that p.name is not like—I'll just do—I'll say MacBook, okay, and hopefully that will work. So I'll put the lower uh function here, so lower uh function here, and yeah, so this should eliminate these three records.

Now you might ask why am I simply using lower? I could have given Pro a proper name. Yes, I could, but imagine if you had thousands of records and you are not sure if they are following the same naming convention for MacBook Pro. In that case, just to be on the safer side, you could just add it; you could use this lower function so that you know it will always match, right? So now if I run it, I'm getting the two records, right? So let's see if we have done everything that was required for this problem. First, orders which cost more than 2,000—we have done that. But did not include MacBook Pro—we have done that as well. Print the total sale amount as well—the total sale amount, it's actually this one, right? So we already have that, and I think I don't need the product name here. I have the order ID. In fact, I can display everything from uh the O table, so I'll say s, so dostar, and if I—okay—if I just run this, I think this is basically the solution to my problem. So there are two orders which cost more than 2,000 and do not include the MacBook laptops, right? So this is basically my solution to the problem. I hope this is clear.

Now let's move on to the next problem, that is problem number 13, and I'm just going to put it here. I'll say this one, okay, and okay. So the problem statement basically tells: identify the customers who have not purchased any product yet. Now there are a couple of ways you can easily solve this. I'll try to solve it using a subquery, so you kind of get an understanding of how to use subquery. So we need to find the customers who have not done any purchase. First of all, let's look at what we have in the customers table; so customers, right? And we also need to look at the sales order table; so sales order. First and foremost, let's see in customers we have four customers, right? And the customer ID is 1, 2, 3, and 4. In the sales order table, we have 10 orders, and you can see that for each order which customer purchase we have it here, but if you look carefully in the customer ID, we have 1, 2, 3—that's it. We do not have the customer 4 mentioned here. So straight away we could analyze that there is one customer who has not done any purchase, right? Because his information is not present in the sales order, so he has not done any purchase, and that customer is with the customer ID 4, right? But we need to display that using a query. So how we can do that is, first and foremost, we can just write a query to identify all the different customers in the sales order table. So I can just tell: select distinct of customer ID from—let's say I'll put it here—sales order, right? And if I run it, I'm—I'm getting—these are the three customers who are present in sales order, that means who have basically been involved in a sale, that means who have done some purchase, right? And then if I look at my customers table, I have these customers four, but if I put a filter condition here saying that where I only want to fetch the customers whose ID is not present in this result, right? So the above query that I wrote, I got all the customers who have done some purchase, so I can just cop that query, so I'll just put it here, right? And I'll just move this to the right.

Okay. Now what I'm saying here—this is the subquery, as I told you previously—the subquery, previously we used it in the from Clause; now we are using the subquery in the where Clause. We can use subquery in where clause, in having clause, in from clause, in select clause, in multiple places. Okay. Now this subquery here is going to return me the customer ID 1, 2, 3, who have done some purchase. What I'm doing in my outer query is I'm trying to fetch all from the customer table where the ID is not one of these, right? So if there is a customer who has not done any purchase, then that should show up from this filter. So if I run it, you can see that I'm getting the information about the customer Zaria Duke who has not done any purchase. Now this is one way of solving this problem using subquery.

Now another way of solving this problem easily is by using an outer join. Now we can either use left outer join or right outer join. Now so far in all our queries whenever—whenever we have joined two tables—we have used the inner join concept. Inner join is when you join two tables and it will return the matching record within these two tables. Now outer join is a concept where it will, of course, return all the matching records, but in addition to that, it will also return some non-matching records, depending on which type of outer join you're using. Okay. Now let me try to quickly explain that using the same example that I used about these European countries and Asian countries when I explained you about inner join, right? Now if you remember, we had these two tables, European countries and Asian countries, and in Europe countries, now we have remaining four countries: Turkey, Germany, Poland, and France. They all belong to the continent Europe, and then we have two Asian countries: Malaysia and Russia. Now when I did an inner join here, you can see that I joined the European countries with Asian countries using the country field, because that is a common field. Now I'm not getting any records because there are no matching records between these two tables, like as you can see here, right? But let's say instead of using an inner join—so let me just go down here and I'll say this is inner join—okay, this is what I'm doing. So I'm joining the left table with the right table using an inner join; only matching records will show up, but currently, since there are no matching records, it does not show up anything, right? But let's say now if I do a left join—so I'll just mention it here: left join—okay, and if I just paste it here, and instead of inner I'll just make the keyword as left, and now if I execute it, what basically will happen is in my left table I have four countries, right? And in my right table I have two countries, which is Malaysia and Russia, but those Malaysia and Russia does not show up here, right? That means nothing is getting matched, but the concept of left join is it will first do an inner join, that means it will return all the matching records, right? But in this case, there is no matching records; it will be basically empty, and then it will also return—since I'm doing a left join—it will also return all the records from the left table which were not part of the inner join, right? So in this particular case, when I run the left join, even though there is no matching record, it will still return me every record from the left table, that means it should return me four records, as you can see here. So the four records from the left table is shown up, but in the right table, that is Asian countries, there is nothing to match with, so all the columns which I'm returning from the right table will be returned as null. Okay. So this is basically what a left join does. So inner join returns matching records; left join will return all the matching records plus it will return all the remaining records from the left table which were not basically matched, right? Now same way, if I have to tell you about right join, what right join does is—so in order to use right join, I just replace the left keyword with the right. Now you might have guessed what will happen: it will try to fetch all the matching records; in this case there are no matching records, so after returning any matching record, it will then look go into the right table and it will see: are there any records which were not returned? So any additional records in the right table which were not returned from the matching, right? In that case, in the right table I have two records, that is I think Russia and Malaysia, so those two records should show up, and that is exactly what is happening. If you see here, in the right column I'm getting Malaysia and Russia, but the left—the columns returned from the left table, that is European countries—is returning null. Okay. So this is what a right join does.

Okay. Now just to give you one additional example here, what I'll do is I'm going to add one record into this Asian countries table, and the record that I'm going to add is basically the country Turkey. Okay. And now I'll just query the data from these two tables so that I can at least have one matching record. Okay. Now in European countries I have Turkey, Germany, Poland, and France, and in Asian countries I have Malaysia, Russia, and Turkey. Now if you quickly analyze, you'll realize that Turkey is a country that is present in both of these tables, right? So when I do an inner join, now I'll go back and run the same queries. So when I do an inner join, that should—it should return me one record because Turkey is present in both the tables, right? So that is what is happening here. Okay. And when I do the left join, what should happen is it will basically take everything that was returned from inner join, so you could say that left join, which is kind of equal to inner join plus all records from left table. Okay. So inner—this is kind of like the formula that you can remember: left join equals inner join plus all records from the left table. So from the inner join I will still get this one record, plus it will go into the left table and see how many more records are there. In left table I have four records; one of them is Turkey, but there are three more remaining, so those three also should get returned, right? Totally it should return me four records, as you can see here. So for the Turkey record, you can see that in the right table also Turkey is present, so it's showing up that data, but for all the three records, they are just showing up because they are there in the left table, but in the right table they are not there, so it's showing as—showing as null in the right table, right? So now when it comes to the right join, the formula of right join is again almost the same: it's basically inner join plus all the records from the right table. So now when I execute this, can you guess what will happen? You could have guessed: it's basically it will still fetch me that matched record, that is the record for Turkey, plus in the right-hand table I have two more records, so 2 + 1, totally three records should show up, and that's exactly what is happening here, right? So this is basically what the concept of right join and inner join is. I quickly tried to explain this, but I have made a detailed video about all the different types of joins; it's a very good video with very good examples. I will leave a link to that; you can—you should definitely check that out to better understand all the different types of joins. Okay.

Now let's go back into our problem. So I had this problem where I wanted to find all the customers who did not make any purchase, right? So another way of solving this problem, or the second solution to this problem, is I fetch all the records from the customer table, so I—I say select star from customers; I'll give an alias like C, and then I generally what I do is I will join it with the sales order table, right? So I'll say sales order s o, and the join condition is the same: s o.customer ID equals c. I think it's ID, right? I can just print only the data from the customer: c.star. If I run it, you can see that—okay—and let's say there are too many records here, but if I just do a distinct here, I think I should fetch me three records, right? Because these are the only three records which are kind of matching between the two tables, because they are present in the sales order; they are present in the customer, but I am only interested in the record which is only present in the customer table but not present in the sales order. So what I'll do is I'll do a left join. So when I do left join, my customer table is in the left, so it's like it's on the left-hand side, so everything from the left table shows up, so these three are already present in the customer table, but in the customer table there is one additional customer as well, right? So even that should show up now. So now if I run it, you can see that this is the additional record that has shown up, right? Because I did a left join, but in order to find which is that particular record, right? I only want to know that additional record which was only there in the left table but not there in the right table. I can do that by using a filter, but before that I'll just show you one more thing. Okay. So I will remove the distinct, but I'll just tell you s o.star, so you can also see how the columns of the O table is shown on, and you can see that here you have data for everything, but if you look at this last record, Zaria Duke, she's the only customer who is present in the customer table but not present in the sales order. So for every column that is fetched from the sales order, it is displayed as null, right? So if I only wanted to find that customer who is present in customers table but not in the sales order, I can just put a filter saying that where s o.order ID is null, right? And if I just fetch that, I'm getting that one record, and here I don't need the sales order details; I just need the customer details, so if I show that, this is the only customer who has basically not done any purchase. So the output from this query and output from this query is basically the same; the only difference is above I'm using subquery; below I'm using left join. Okay. Now same way, instead of left join I can also do this by using right join. Just for the sake of showing, what I can do is I can put a right join here, but if I'm putting right join, the most important table here where I want all the records to be fetched is the customer table, so what I'll need to do is I'll need to move this to the right-hand side, and I'll move this sales order table to the left-hand side, and now if I run it, I'm still getting the same output. Okay. Three different queries, same output, slightly different logic. I hope you understand that.

Now let's move on to the next problem that we have, that is the problem number 14, and I'm just going to put it here. So let's say 14. Okay. So the problem statement basically states: write a query to identify the total products purchased by each customer, right? So let's only focus on this: we need to find the total products purchased by each customer. So let me just do one thing: I will say that I'm going to fetch the data from the two tables, that is I have the sales order, right? So sales—I'll put sales order s o—and I need to join it with the customers table, right? Customers, and I'll say c on c.ID equals s o. I think customer ID, right? So identify the total products purchased by each customer. Now if I want to find the total products purchased by each customer, then it's basically here the—it's the quantity field, right? Now in this order, two products were sold; in this order, three products were sold, and so on and so forth. So I need to do a sum of this quantity, right? So sum of this quantity specific to each customer, because they are telling: identify the total product purchase by each customer. So I'll just do that. So I'll just tell c.name, and I'll say sum of quantity, right? And this is the total—um—products—just give a short name: total product purchased. Okay. And I need to do a group by, so I'll say Group by the c.name, right? So I'm grouping the data based on each customer uh which is present in the customers table, and I'm summing up the total quantity, right? So I should basically get Megan Harley: 12 products, seven products, and five products by other customers, right? This is fine; so this is basically what was mentioned in the first line. Now let's read further: return all customers irrespective of whether they have made a purchase or not. Okay. This is interesting. Now in the customers table we know that there are four customers, but here only three customers are showing up, and they are asking us to display all the customers even if they have not made a purchase. Now here only getting three uh customers as my output because only three of them have made a purchase. If I want all the remaining customers also to show up, so instead of using a left join, I can do a right join, as I explained previously, and now if I just execute the query, you can see that I'm getting the data for Zaria Duke as well, and it is showing as null because she has not made any purchase yet, right? And sort the result with the highest number of orders at the top; highest order at the top, right? Now all the total or purchased products that I'm showing up here, it's an integer, but this one value here is null, right? So if you want to replace a null with some other like meaningful value, let's say zero in this case, then what we can do is we can use some functions. So I can use a coalesce function here. Coalesce is another inbuilt function; what this basically does is you say the function name, you pass two arguments. Okay. Minimum you can basically pass more, but the first argument it will basically check if the first argument is null or not; if it is a non-null value, then it will return whatever is mentioned in the first argument; if the first argument is a null, then it will go to the second argument and it will print that. Okay. So in this case, for these three records here, it is like 12, 7, and 5; they are not null, so it will display as it is, but whenever it is null, it will display zero. In of zero, I can make it display any value that I want. Okay. For example, now if I run it, you can see for Zaria Duke it is displaying zero. Instead of zero, let's say if I wanted it to display 9999, then if I run it, you can see that it will display 9999, right? But I want it to display zero because this is what makes more sense, right? If it is zero, that means Zaria Duke has not done any product purchase yet, right? So this is fine. The last thing is: sort the result with the highest order at the top. So if I want the highest order at the top, I can just do order by, and highest order I will use the alias name, that is this field, the total prod purchased, and in descending order. So if I just run it, this is basically the output of my problem number 14, right? So I hope this is clear, and I hope you understand this. Now let's move on to our problem number 15, and that is this one. Let me just copy this; I'll go back here and I'll say 15; I'll paste the problem. So the problem number 15 basically

States corresponding to each employee display the total sales they made of all the completed orders. Display total sales as zero if an employee made no sales yet. Okay, so here's what I need to do: for each employee, I need to find the total sales. Right? So let me straight away start writing my query: SELECT FROM WHERE. Now I know that one of the tables I definitely need to use is the employee table, and I also need to use the sales table. But I need to find total sales, right? And that means the total amount or total price that each employee has contributed in the sale. That means I need information about the price, and price information is only present in the products table, right? So I need to use the sales order table, so I need to join it with the products table, and that will be P. ID = SO.ProductID. And I also need to join it with the employee table, right? So employee, that will be E. ID = SO.EMPID. So this is fine. Now what I need to do is for each employee display the total sales. So I need to group the data specific to each employee and then sum the total sales, right? Now for employee, I can just tell E.name as employee, right? And then in order to find, let's say, the total sales—the sale is basically—I need to multiply the quantity into, I think, the price from the product table, right? And this is my total sales. But yeah, and let let it be as it is for now, and let me just run it. Let's see what happens. So I'm getting an error because it is employees. Now if I run it now, you can see I'm getting 10 records, and I think I'm getting the sale amount here, right? But I need to find this corresponding to each employee, so I need to—and here you can see there are multiple records for each employee because an employee might have been contributing for multiple sale orders that happened, right? So I need to group the data based on each employee and then sum these values. So what I'm going to do is I'm just going to say GROUP BY employee.name, right? And I will just add a sum here, so I'll just tell SUM of this whole thing AS total sale, right? So if I now run this, I am getting the total sale that has happened, right? But I'm only getting two employees, but I know I think there were more than two employees in the employee table. So if I remember properly, in the employee table we have SELECT * FROM employees. I think there were three. Yeah, so there are three employees, right? So but I'm only getting two. But here, if you look at the problem statement, the total sales they made of all the completed orders. Okay, so I need to add a filter here as well because I'm only looking at the completed orders. So I need to say WHERE the status is equal to completed, right? And since we have this case difference, I will just put the LOWER function so that all cases will be matched. So this is for all the completed orders. And one more thing is here we need to find the data corresponding to each employee, and if you read properly, display the total sales as zero if an employee made no sales. So that means if an employee has not contributed in the sales, that means his information will not be present in the sales order table, even then we should display that employee and we should mark the total sales as zero. Right? Currently we are not doing that. Currently what we are doing is when we run it, we're getting all the employees who have made a sale, but there is another employee that we have missed because he has not made a sale. His record also should show up here, and it should print the total sale as zero. That means if I want to print all the employees to show up in my output, so instead of doing an INNER JOIN with the employee table because it will only match the fetch the matching records, I want to do a RIGHT JOIN here. So when I do a RIGHT JOIN, even if there are any employees that were missed out from this join, it will also show up. So if I now run it, I'm still not getting the employee that was missed because if I look at my employee table, I have this Irene Costa who has not made any sale. The reason why that is not showing up is because I did the RIGHT JOIN here, but then I have this filter condition. So, for example, let's say if I just ran this okay, and I will just comment out the SUM. If I just ran with the JOIN, you will see that I have Nina Kumari, Abar Khan, and I have Irene Costa showing up here, right? But as soon as I put this filter condition, so now you see I have 11 records, but as soon as I put the filter condition, it only fetches me six records, and that Irene Costa is basically eliminated here, right? Now I do not want that to happen. So whenever you have a requirement where you are basically joining multiple tables and you're using some outer joins and you want a record from a particular table to always show up irrespective of the filter, then it is always better to put that filter as a join condition when you're joining that outer table. So here I can put this filter condition inside the AND clause as a join condition. Okay, so I'll just put this here. Okay, and I'll just mention it here. So instead of putting this as a filter condition in the WHERE clause, I'm putting this as one of the join conditions. Now the advantage of doing this is it will not eliminate any record that did not pass your filter. Okay? So, for example, if I just run this query now, previously I was getting six records when I had put this inside the WHERE clause. Now when I put it inside the join condition, and if I run it now, we can see that 6 + 1, I'm getting the seventh record. Why? Because this seventh record is actually coming from the employee table through this RIGHT JOIN. This six records is actually what matched all the conditions, but this seventh record is coming up because of this RIGHT JOIN condition where it will return any remaining employees in the employee table. Now this is fine. Now let me go back to my GROUP BY and run it. So if I run it now, you can see that I'm getting all the three employees, but for IR Costa, I'm displaying the total sales as NULL, but we can replace the NULL with zero by using the COALESCE function as I showed previously. So I'll say zero, and I think if I just run run it now, I'm getting a zero. Okay, so I think this is all what we wanted. Corresponding to each employee display the total sales they made of completed orders. We have done that. Display total sales as zero if employee made no sale. I think so. That's all. This is basically the solution to our problem number 15. I hope this is clear now. Let's go quickly and try to solve the problem number 16. So I'm just going to write it here: 16. So for problem number 16, they're asking us to rewrite the above query so as to display the total sales made by each employee corresponding to each customer. They're asking us to rewrite this above query because in the above query we found total sales corresponding to each employee. Now this each employee might have worked with multiple customers, right? So we now want to modify the query such that we display the total sales corresponding to each employee and also corresponding to each customer, right? So we will try to do that. Second, if an employee has not served a customer yet, then display it like a dash or a hyphen, okay, under that customer. We'll look into this second part later, but first let's see how can I modify this query to not just group the data based on the employee, not just segregate the data to find total sales for each employee, but also for each customer. So I'll just copy this query as it is and I'll just put it here. Here I have only taken the employee table. Let me straight away join with the customer table first, and here I'm not interested in all the customers. I only want customers who were responsible for some sale. So I'll just put it right before the RIGHT JOIN. Okay, so I'll say JOIN with the customers table, I'll say C, C.ID = SO.CustomerID. Okay, and I'll do one thing. I'll just—now if you see, I'll still not have any change in my output. It is still the same. I just joined the customer table here. Now what I want to do is I want to display the customer name as well. So I'll say C.name AS customer, right? And since I'm using this C.name, very important thing is whenever you're using a GROUP BY, always remember whatever columns you have mentioned in the SELECT clause, all those columns should be there in the GROUP BY. There are few rules that you need to remember when you're using GROUP BY. One is if I'm using two columns here, the customer name and employee name, both those columns should be there in the GROUP BY. Now you might ask, okay, what if what about these columns? I'm using quantity and price as well, right? But all the other columns that I'll be using in the SELECT clause, they should be within the aggregate function. So here I'm putting both of this inside the SUM, which is an aggregate function. In that case it is okay, but let's say if I did not have a SUM and if I'm using these two columns, then these two columns will also will have to be there in the GROUP BY, else it's going to throw an error. Okay, you can try it out yourself. Now let me just run this query where I have attached the customer name, and now you can see that for each customer, each employee corresponding to each customer, I'm getting the total share. Now just to have a better vision of this, I'm just going to sort the data by ORDER BY, let's say 1, 2. Okay. Now what does this 1, 2 mean? Now when sometimes you might see some queries where they have mentioned numbers. Now this basically means one is the position of the first column in your select query in your SELECT clause. Two is the position of the second column. So here one basically means employee and two basically means a customer name. Okay? Now if I just sort it, now you can see that Abar, Abar comes together. Two customers he has served. This is the total sale. Irene Costa has not served any customer, so total sale is zero. Nina Kumari has served two customers, and this is a total sale. Now the last point that they have mentioned here in the problem statement is if an employee has not served any customer, and that is Irene Costa in this case, we need to display like a hyphen or a dash here, right? So for that case what we will do is in the customer name, if let's say I'll again use the COALESCE function, so if the customer name comes as blank or empty, then I want to display like a dash, right? And now if I just run this query, I'm basically getting the output that I wanted. So this is basically the solution to the problem number 16. I hope this is clear. Now let's move on to the problem number 17, and I'm just going to put it here: 17. The problem number 17 basically tells: rewrite the above query so as to display only those records where the total sale is above 1,000. Okay, again we will be using the above query. So what I'll be doing is I'll just copy this query here again and I'll bring it down here. Okay, and let me just move this little down so we have more space to see. So we need to rewrite this query. This is the query that we wrote from in our problem number 16. We need to rewrite the query so that to display only those records where the total sale is above 1,000, right? Now you might think, okay, this looks like a simple filter condition, right? Wherever this amount is greater than 1,000, I should return that, and what you would generally do is let's say you would probably write a query like WHERE this amount is greater than 1,000, right? And if you run it, you would hope that it would return you the value, but you're getting an error saying that aggregate functions are not allowed in the WHERE clause, right? So basically I'm not even able to use the aggregate function, and even if you did not have an aggregate function and if you had some column, this is not the right way of filtering it. Okay, and I'll tell you why. So first of all, let me execute this query. You have this data, and you know that this data that you show here is basically coming from your aggregation by doing the GROUP BY, right? And they're asking us, we need to fetch only those records where the total sale is above 1,000. Now this total sale that is showing up here is the result of a GROUP BY. If we did not have a GROUP BY, then we could use a filter using the WHERE condition, that is fine. But whenever you have a data that is a result of a GROUP BY clause, and if you want to filter the result that has come after the GROUP BY, then you should always use a HAVING clause. Okay? Now HAVING clause is basically similar to a WHERE clause. It is used to filter the grouped data. So when you want to filter the data directly from a table, use the WHERE clause, but if you want to filter the data from the group result, then you use a HAVING clause. So what I need to do here is is after my GROUP BY, I say HAVING, and then I can just tell this whole thing. So COALESCE of this whole thing should be greater than 1,000, right? And now if I run it, if you look carefully how many records are there greater than 1,000? So it's 1, 2, and 3, right? These three should show up. So if I run it, you can see that those three are actually showing up. Okay? So the solution was very simple. If you had already solved the problem number 16, always remember when you want to filter the data based on the group result, you should use the HAVING clause. Okay? So you should not be using the WHERE clause. We use WHERE clause to filter the data only when we have to filter data directly from the table. If you want to filter the data from the grouped result, we always use the HAVING clause. Okay? So I hope this is clear. Now let's move on to our problem number 18, and let me just put it here: 18. So the problem states: identify employees who have served more than two customers. So we can say that we need employees and we need the customer information, right? So I need to find how many customers each employee has served. But I cannot just use the employee and the customer table because, as I told you previously, if I have to join the employee table with the customer table, there is no way I can join it, right? You might remember that. So SELECT * FROM employees and SELECT * FROM customers, right? In the customer table I have the customer ID. In the employee table I have the employee ID, but the column names are the same: ID, ID, but they have completely different data, right? One is representing the customer's ID, and the other is representing the employee's ID. We cannot join these two tables directly. The only way we can join these two tables is by using the third table, that is the sales order table, because in the sales order table I have both the employee ID and the customer ID, and that's exactly what I will be doing. So I'll say SELECT FROM the sales order, I'll say SO, JOIN it with the employees, I'll say E, and I need to join it using the E.ID = SO.employeeID. Then let me just copy this again, and I'll just say JOIN it with the customers table, C, and this will be C.ID = SO.CustomerID, right? So this is fine, and let me just put a * here, or let me just run it for now, and I get too much information. But I only need the information related to the, let's say, the employee name, E.name AS employee. Then I need the customer name, so C.name AS customer. And then I need who have served more than two customers, right? I think I don't even need anything else. Let me just run this, and now you can see that, and let's say just let me just sort the data by the employee name. I'll say 1, because 1 basically represents the first column in my SELECT, and now you can see that I'm getting 10 records. Abar Khan has served six. Okay, he has been responsible for six orders, but you can see some of the customers are repeated. Then we have Nina Kumari. Now what I basically want is I want to count how many employees have served more than two customers. Now I can see that Abar has served Logan Short and Megan Harley, right? And also Rosa Chance. So Abar has actually served three customers, whereas Nina Kumari has only served Megan Harley and Rosa Chan, only two customers, right? So how I can do that count? How I can do that is first of all, I need to group the data based on employee, so E.name GROUP BY. So I will group the data based on each employee. So let's say once I group the data, I'll get one record for Abar, right? So out of these six, I'll get one group that is by the name Abar K, and within that group if I count the DISTINCT of the employees, right, DISTINCT of the customers, then the DISTINCT of the customers is 1, 2, and 3, right? So that is exactly what I'll be doing. So GROUP BY employee name, and then here I'm just going to say COUNT, okay, and COUNT of customer name, okay? And I need to do a DISTINCT. Okay? Now why DISTINCT? Because if I did not do DISTINCT, it will basically count a total number of records for Abar, and that would be six, for example. Let's say if I remove the DISTINCT, it would basically tell six records for Abar and four for Nina Kumari, right? If I run it, you can see that six and four, but that is not correct because there is no six customers, right? Because I have only maximum of I think I have three or four customers, right? The reason why it's showing up six is because some of the customers have done repeated business with the employee, right? They have purchased multiple multiple orders with the employee. So I need to find DISTINCT customers. So now if I run it, now we can see that I'm getting Abar Khan as three, and Nina Kumari has served two customers. Okay? Now what I want to know is identify the employees who have served more than two customers. So this data is my group data. If I want to find any—if I want to now filter the data such that I only fetch wherever there are more than two customers, now I need to use the HAVING clause. So as I told you previously, HAVING, and I can just do this COUNT of this whole thing is greater than two. And now if I just run this whole query, now you can see that it is fetching me Abar Khan who has served three employees, right, three customers. And this is basically the solution to my problem number 18. I hope this is clear, right? Now let's move and solve the problem number 19, and the problem number 19 basically states that identify the customers who have purchased more than five products. Now, as you can see, we need to identify the customers who have purchased more than five products. Now you might read this problem and imagine, okay, we of course need to join with the customer table, and we might have to join with the products table, but actually we don't need to join with the products table because we are not trying to find the products. We are only trying to find the customers who have purchased more than five products, and if you want to know how many products a customer has purchased, we can just look at the quantity in the sales order table. So what I can do is I can just SELECT FROM WHERE, and let's say this one, I'll say sales order table, so JOIN it with the customers table C ON

c. ID equal to s.o.do customer ID. Right? And here, I can just, let's say, I'll do c.do name as customer, right? And I'll fetch the from the S.o. table. I want to fetch the um, let's say, okay, so I I'll fetch the order ID and I'll fetch the quantity. Okay, so quantity. And if I just run this now, we can see that from in the first order, Megan Harley purchased two products, right? Um, and then in again, we can see that Rosa Chan in the second order purchased three products, and so on and so forth. What we need, the we need to find the customers who have purchased more than five products. So if I can group the data based on each customer and then sum this quantity, it is I will get the total products that they have purchased, right? So what I, how I can do that is, let me just remove this where, and I just need to group the data. Group by the c.do name, the customer name, and okay, I don't need the order ID, but I need the quantity. I'll do a sum of the quantity and I'll say total products, right? And if I just run this now, you can see that Megan Harley has purchased 12 products, Logan short products, and Rosa Chan five products. But I only need to find who have purchased more than five products.

Now, I think it's very clear for you, if you want to filter this data, we cannot use where clause because this result is coming from a grouped data, right? So for that, we need to use the having clause, saying that having, okay, this one, that is sum of this quantity greater than five. And I think that's all. If I just run this query now, you can see that these are the two customers who have purchased more than five products. So this this is basically my solution to the problem number 19.

Now, finally, let's move on to the problem number 20. And the problem number 20 basically is this one. So let me just uh copy this and put it here. So identify the customers whose average Purchase cost exceeds the average sale of all orders. Now, in this problem, we need to find two different things: one, we need to identify the uh average Purchase cost of each customer, and then we need to find the average sale of all the orders. First of all, let us try to find the average sale of all the orders. So I have this sales order table. So select start from sales order, and I have this. If I want to find the average sale that happened uh for all the order, first of all, I don't even have the price information here, so I need to join it with my products table to get the products information. So I'll say p.ID equal to s.o.do prod ID, and here I can just tell order ID, comma, let's say, the total sale u or the average sale that we want in this case, but a sale amount would be the quantity into the price, right? The price comes from the product table, quantity from the orders table, and yeah. So I think if I just run this now, you can see that for each order, this is the kind of like the total cost of each order, right? But I want to find the average sale of all the orders. So here, I'm not trying to find the average sale specific to each order, but for all the orders. In that case, I can just tell average of this whole thing. I don't even need the order ID because I'm not I'm not trying to find average specific to each order, but in general, in this entire table, in this entire data set, what was the average sale amount that has happened, right? So if I now run it, you can see that it is 2670. So on an average, whenever a sale happens, on an average, it is 2670. That is the average amount. So this is the first thing that we wanted.

Now, next thing is we want to find the average spending from each customer. So we will try to find the average spending from each customer and display only those customer whose average spending is more than this kind of like the average to of all the sales that happens in the store. Okay. Now, in order to find the average Purchase cost of each customer, we need to join the sales order table, the sales order table with the customer table. So I'll say join with the customers c on c.ID equal to s.o.do customer um ID, and we also need to join it with the products table. So I'll just copy this whole thing here, join it with the products, right? And then what I need is c.do name as customer, and I need to find their total cost, right? For each order, how much have they actually spent? So that would basically again be this one, that is quantity into price, and in this case, I need to find the average of this cost, that is average for each customer, that means I need to group the data by each customer. So here I can just tell Group by c.do name, right? Now, if I run it, you can see that I'm getting the average spending from Megan Harley is 3225, from Logan short is 2916, and Rosa Chan is 1683. Okay, so this is the average cost they spend, but what we need to do is we need to find the customers whose average cost is greater than the total average cost of that entire store, right? So here I think the result that we got uh in this was 2670. Let me just comment it out here, and when I run this below query, I'm getting 3225, 2916, and this one. So ideally, these are the only two customers whose average cost is greater than or exceeds the average sale of all the products, right? So I need to put this data and filter basically I need to filter the result of this query using the result that I get from the above query, right? And this is where I need to use subquery, but I cannot filter this data just by using a where clause because this data that is coming is data coming from a group by. So I need to use the having clause. So what I'm going to do is I'll say having of this average, this whole price is, right, whatever is coming here is greater than the output from this subquery. So I will just put this subquery here, right? And I'll move this to the right. I'll remove this. Okay, so just look at this query here. Okay, so this is all the query that we got for each customer, and this is the subquery where we got the average sale of all the orders. Now, I'm filtering such that this average uh cost of each customer is greater than the average cost of all these orders. Now, if I just run this query, you can see that I'm getting only the two customers that actually kind of like spend more than the average spending of all the orders in the store. Okay, so this is basically my solution to the last problem, that is the problem number 20. I hope you understand this. Okay, so this is basically the end of this whole SQL tutorial for beginners. It was a very lengthy video, the biggest video that I have ever made, but I hope hope this was useful. You will find all the scripts and materials and data sets that I have used in this video in my blog. You'll find the link to the blog in the video description, so definitely check that out. The next steps, what you should do after this, I have also mentioned it in my blog. You can just go and check, and you will find the links that you can use to practice or you want to learn something, right? Thank you so much for watching, and I'll see you soon in the next one. Bye.