SQL Starter Pack - SQL Beginner's Guide using pgAdmin

1. Course Overview: Hello and welcome to the SQL start Pat course. My name is Ran and I'll be your instructor. I work at one of the UK's largest banks as a data analyst and I've designed this course to give you a solid basic understanding of SQL. SQL is a coding language that's used with databases to retrieve, organize, and analyze data. And in this ever changing world of tech, data is increasingly becoming essential for lots of different types of work. I've designed this course with beginners in Mind, so there's no prior experience needed. Step by step, I'll talk you through a number of processes, including retrieving and filtering data using simple SQL commands. Sorting, grouping, and modifying data to provide powerful insights, combining tables to create new datasets, and also manipulating and adding data to tables within databases. This quick and focused course is perfect if you're curious about SQL and what it entails, or perhaps you're changing career path and you want a foundational SQL knowledge base, or, of course, you might just want to learn SQL for fun or even just to communicate with tech savvy colleagues. Whatever you reason for wanting to learn SQL, by the end of this course, you'll be confidently writing your own queries, and you'll have a basic knowledge of how relational databases work. 2. Intro to SQL: So what is SQL? SQL stands for structured query language. It's a powerful language used to interact with databases. Databases are like digital warehouses where we can store, organize, and retrieve data. SQL helps us ask questions about that data and get meaningful answers. Let me give you a quick example. Imagine you're managing a database for an online store. It might contain tables for customers, such as their names, email addresses and phone numbers. It might also contain tables for your orders. So what each customer bought, when they bought it, and how much they spent. If you want to know which customer placed the largest order last month or how much revenue did we make last week, you could use SQL to answer those questions. A good analogy I like to think about is, if the database is the library, SQL is like the librarian. You can make a request and SQL retrieves exactly what you need. All you need to know is how to ask the question, which is what we'll be covering in this course. One thing to note about SQL is often referred to as SQL. This is because the original name for the language was SQL, but they had to change it to SQL at a later time. So if you hear someone talk about SQL as a language, they are talking about structured query language. Well, SQL is important because it's one of the most common ways to manage data, and nearly every business or app that deals with large amounts of information relies on it. Let me give you a few examples where SQL is used. One really common use for SQL is through e Commerce. So companies like Amazon use SQL to track millions of orders and products. Another example is through finance. So banks and investment firms use SQL to manage transactions and accounts. A great example of a platform using lots of information is, of course, social media. So platforms like Instagram or Twitter store data about posts, likes and followers in databases. Of course, there's also the example of healthcare. So SQL can be used to manage patient records and appointments securely. The thing is, though, SQL doesn't have to be used by a massive conglomerate business. It can also be used by small businesses who might use it to analyze sales trends or even hobbyists that use it to organize personal projects. In such a data driven world, there will always be a use for SQL. Now let's talk about who might use SQL. So SQL isn't just for programmers or tech experts. I would categorize it as one of the most accessible coding languages. It can be used by data analysts to find trends and insights from data or marketers who can use it to segment customers for targeted campaigns. It can be used by product managers to analyze how people use apps and products. And, of course, business owners who might want to track performance and make data driven decisions. Even if you're new to data, SQL is a skill that can set you apart. It's easy to learn the basics and incredibly versatile. Plus, it's in high demand. Many job descriptions list SQL as a must have skill for data roles. 3. Relational Databases: This lesson we'll explore relational databases, a cornerstone of data management in almost every industry. You'll learn what they are, how they organize information, and why they're so powerful for storing and retrieving data. Whether you're tracking online orders, patient records or financial transactions, relational databases keep everything structured, accessible and secure. So what is a relational database? A relational database is a system that organizes data into tables, which look a lot like spreadsheets that you'd see in Excel. Each table contains rows, which are individual records and columns, which represent specific attributes or data points for those records. For example, a customer's table might have columns for name, email and phone number. The magic of relational databases lies in their ability to connect tables. Instead of duplicating information across multiple places, you can store it in separate tables and link them together using SQL. So let's break down the key components of a relational database. So firstly, we've got our tables. The store the actual data. So each table might focus on one part of the business, like the customers, the products, or the orders. What you'll often find within these tables are primary keys. Primary keys are a unique identifier for every row in a table. For example, in a customer's table, the customer ID column ensures that no two customers are mixed up because they'll have a unique number assigned to their row. And this customer ID is a primary key because of that. As well as primary keys, we also have foreign keys. Foreign keys are columns in one table that reference primary keys in another. For example, an orders table might include customer ID to link each order to a specific customer. One final key component of a relational database is the relationships. These define how tables are connected. Common types of relationships include one to one, where one record in table A matches one record in table B. We've also got one to many where one record in table A could match multiple records in table B. An example, this could be one customer can have many orders. Finally, there's the many to many relationship. This is where multiple records in table A can relate to multiple records in Table B, such as students and courses where lots of students can take lots of courses. So why are relational databases so widely used? Firstly, they're incredibly efficient. Relational databases avoid redundancy. Instead of duplicating data, you store it once and link it as needed using SQL. They're also incredibly accurate. By enforcing data integrity rules like requiring unique primary keys, they minimize errors. They're also incredibly scalable, so these databases can handle small datasets, but they can also scale to manage millions or even billions of records. And, of course, they're incredibly flexible because we can use SQL on them, and that means we can create the data in endless ways, where they want a single record or a complex report. Let's think of a real world example. Imagine ecommerce store. A relational database might have three key tables, customers for personal details, orders to track purchases, and products for inventory. By connecting these tables, the store can quickly answer questions like, which products did a customer buy last month or how much revenue came from a specific product category? And of course, we can ask these questions using SQL. So to summarize, relational databases, organize data interconnected tables, making it easy to store, manage, and retrieve information. As we progress in this course, we'll see how SQL acts as the language that can unlock all this power. 4. Data Types: All right, welcome. In this lesson, we're going to be diving into a foundational concept in SQL, and that is data types. Data types define the kind of data a column can store, whether it's numbers, texts, dates, whatever. In this lesson, we're going to be exploring why we need data types, as well as showing you some of the most common ones that we'll find in this course. So firstly, why are data types important and why are they used? Well, the purpose of data types is to firstly ensure data accuracy. Secondly, they optimize database performance, and thirdly, they can prevent invalid data entry. Think of data types as rules for your data. They ensure that you store the right type of information in each column. For example, you wouldn't want to store a name in a column meant for numbers. So if you accidentally typed in a name in a price column, it wouldn't let you. And that's just a way to make sure that the data's accuracy, and like I say, it prevents invalid data entry. And it's also worth noting that each data type is optimized for the database. So making sure that each data type is correct for the column, make sure that your table and database are all optimized. I'm going to show you some of the data types that you might come across in this course. So if you look at this table, we've got integer, which means whole numbers. We've got numeric or decimal, which means numbers with decimal points. We've got text or RCR, which means a text value, you know, just, you know, words. We've got data and time datatypes, which are pretty self explanatory. Boolean is essentially a data type that can either be true or false. So it's good for when you're sorting through data. If, you know, someone's filled out a form, you can say true, and if they haven't, you can say false. And lastly, one we come across as serial, which is auto incrementing integers. And this is very useful for doing primary keys because it automatically adds another number to the next row. I also want to take this opportunity to talk about null values. While null is not a data type, it is something that you might come across in tables. And null basically means the absence of a value or the absence of a data. So it's not the same as a zero or an empty bit of text. It means that there's nothing there at all. So if you're missing data, it will come up with null values in the tables. But yeah, that's a quick rundown of data types. Just to summarize data types define what kind of data can be stored in a column, and null values represent missing or unknown data. And as we start to explore the tables in our databases, we start to see all these data types in action. But that's it for this lesson. We're going to be setting up our SQL environment next, so we'll see you there. 5. Setting up PgAdmin: Alright. In this video, I'm just gonna Alright. In this video. Alright. In this video, I want to show you how we can download and set up postgres. Alright. In this video, I want to show you how we can download and set up Postgres Post gescu. How do you say that? Post Rescue. Alright, so right. In this video, I want to show you how we can download and set up PostgreS. So we want to go to this link here or this URL here. So it's just posgrsqal.org slash DnlodSAH. And this should bring you to this page, and it should bring you to this page here. So depending on what you're using, Yeah, ****. I should I should hopefully bring you to this page here, and this is where we're going to download all our software. So just click on the link. And depending on your software system, depending on your operating system, you'll click one of these links here. So in my case, it's Mac. So we're going to go on here, click this and just go on to the download installer This will take us to this. And now it'll bring you to this page here. And it'll bring you to this page here. And what I'm going to do is just doload the latest version. Depending on what you're running, it might be the second latest version. Just have a look, but depend it'll then bring you to this page here, and I'm just going to download the latest version for Mac OSX. And I uh I should bring you to this page here, and now I'm just going to download the latest version for my operating system. So just click on this button here. And that will download the installation, and that will download the software for you. And that will download the installation software. And that will download and that'll start the download, hopefully. And that should start the download where we can then install our software. And that should start the download for the installation wizard. And that should start the download for the installation wizard. Once that once that's Once that's downloaded, I'm just going to click on the Download and just run this software here and it's gonna ask for my password. Once we've typed that in, hopefully, we've got our installation wizard coming up. There we go. And here and here is our setup wizard. So we're just going to click on Next here. And I'm going to pick and this is the default directory. That's fine by me. You can select where you want to install this. It's gonna click next on this. Next, I'm just going to download them all next. In terms of components, I'm just going to keep them as in terms of components, you can just keep them as default. You might not need stack Builder, but to be honest. In terms of the setup components, in terms of the components, just leave that as is. Next. And the same if you want to change where your data directory goes, you can change that, but I'm just going to keep it as default. And now this password is very important. This is what we use to actually get into our databases. So it can be something simple. I'm just going to use password, and you can use the same. And for port, I'm just going to keep that as default and the same with the locale. And we're just going to let that install. And we're just going to let that install now. We don't have to worry too much about where the databases are going because we're just using tables on our local system. Because we're just doing simple queries for this course, we don't have to worry too much about the setup. We just have to get it on our computer and running. All right, that's it all set up, and Okay, is that okay, that's it all installed for us? I'm just gonna untake this because I don't want to run anything and just click finish. Now I can go to my launchpad and I've got PG admin here, as well as a few other things. Now if I go to my launchpad, now if you see in my launchpad, I've now got PG admin installed. And now if you see in my launchpad, I've got PG admin installed, so I can just click on that now. It's going to do some verifications perhaps for you, and it might ask for your user password once more. It might take a second because it's the first time you open this I might take a wee while to set up. So here we go. It's finally so here we go. The screen you should see, it's starting to load up and reboot So here we go. So here we go. After a few moments, you should hopefully have this screen come up, and it's just waiting for PG Admin four to start. And like I said, you might have to put in your password a couple of times. And here we go. I've now opened a PG Admin. I've got nothing on my screen, basically, but okay, so that's PG Admin all loaded up. Okay, so that's PG Admin all loaded up. This is where we're going to be spending our course. Okay? That's all. Alright, that's PG admin all loaded up. This is where we're going to spend the entirety of our course now, but we've got a few more things we need to set up. So if we go to the left Object explorer here and we click this arrow, we've got a couple of servers. So I'm going to use PostgresS SQL 17 here. And as you notice, if I click on this, it says, Connect a server, please enter the password. This is where I type in the password, which is, in my case, when I set it up, password, the word password. I mean, I'm just going to save that because I don't need to worry about it too much. So when you open up your PJM, and you might see a couple of servers, one or two servers. Now, if you just click on one of these and type in the password that you put in the setup, in my case, it was password, you'll end up logging in and you'll have all these logos here. When you first now if you look to your left, you'll have an object explorer with a couple of servers, maybe one or two. If you just click on one of these and type in your password that you put in your If you look to your left and look into the Object Explorer, you can look at a couple of servers. And in my case, it's PostgRs SQL 16. I just clicked on this and put in my password that I defined in my installation wizard. So in my case, it was just the word password. I entered that, and then I've got this schema now. Once you've got that all set up, you might have a couple of servers. So you might have a couple of servers installed, so just make sure you pick the right one and just try a couple of passwords. It doesn't really matter which one it is because we're going to be making a different database anyway. You might have you might have a couple of servers installed, depending on. You might have a couple of different servers installed, but just have a look and just try a different You might have a couple of servers installed, but don't worry. Just click them both. You might have you might have a couple of servers installed, but don't worry. Click on them, try out the password, and if it works, that's the right server. And once you're logged in, you should have a few more logos like this coming up. And what we're now going to do is just create a new database. So we just click on databases, create database. And I'm going to call this SQL course. And then just save that. And this database is what we're going to be running off of. And this is the database we're going to be using throughout the course. Now, it's completely empty. If we go down to schemas and then further down go to tables, we can see it's got nothing in it. So how do we actually add tables to this? Well, what we can do is we can just what we need to do is just right click on this, go to query tool. And don't worry about this too much. And don't worry about this. I'll explain this. Now, don't worry about what all this is yet. Just And then what we then all we need to do is just paste the code that I've given you in the worksheet. And in this top box here, in the query box, it's got a little bit where we can type, paste the code that I've supplied you with. And in this top window here, in this top window here, there's a place to type. Just paste the code that's in the worksheet for this lesson. It should look something like this. Don't worry about the code. It's just to create some tables for us to use in our examples. And I would just click this play button, execute script, and this should hopefully create some tables. Once you've pasted this in, press the play button here, and hopefully that should once you've pasted this code in, once you've pasted this code in and press the play button up here, it should hopefully return saying, Que we return successfully. Once you've pasted this code in D. Once you've pasted this code in and pressed this play button, hopefully a message should appear ******* hell. Once you've pasted this code in and pressed this play button here, it should hopefully return a message saying, Query return successfully. Now, don't worry about any of this. This is just for now don't worry about what this code entails. You don't have to work. Now, don't be intimidated by all this code. This is just for setting up the database, so you can completely delete that and close these windows down by pressing this cross here and we'll explain all of this in further lessons. But now if we go down to but now hopefully, But if we go back to our object explorer on the left, and we just go right click on the server. But now if we go back to our servers here and the Object Explorer, just click Refresh, we should hopefully now be able to go down to our SQL course database, and then in tables, we should have customer free tables. But now, if we look at our object explorer, but now if we go back to our object explorer on the left and just right click on the server and refresh, then go back to our SQL course database and then down to schemas, we should now have three tables. And there we go, customers, products, and sales. And that's all set up for our course. And then for the rest of the course for the rest of the course, we'll be using this creamy tool, so I'm just going to go. And that's all set up for the course. M 6. SELECT: Alright, now that we're all set up, let's think about how we can actually start to use SQL. So what I'm going to do is going to go to my database here, SQL course and just open up a query tool. And this will open up the query tool, and we've got two windows here. Our top window is where we can input our code, our SQL code, and the bottom window is where the results of that code will be displayed. Now, if I go to my SQL course and go down to schemas and down to tables, as you can see, you've got three tables loaded in, customers, products, and sales. You should have the same as well. How do we get, for example, our customer's table onto our results sheet here in the bottom window? Well, our first step is to write the following code. We write a select, and we write from Now, these two keywords are incredibly important for SQL. These basically make up the framework for any SQL code. Once you've grasped this basic framework here of the select and from keywords, you can start to add filters and things like that, which we'll do in further lessons. But for now, this is our basic query. So what do these two keywords mean? Let's start with select. Select helps us define what columns we want from a table. So for now, we're going to type in the asterisk. This asterisk or star key basically tells the software, select every single column. This star key is very useful and it comes in handy all the time. So what does this code mean so far? We've got the first line, which is select all columns, and then the third line, which is from. Now, the from keyword just tells the software which table we want to be pulling the data from. We've got free tables, we've got customers products and sales. So let's just go customers. We will use the customers tables. And that is essentially our first SQL code. And if I read this out again in plain English, what we've got here is select all columns from the customer's table. And to finish this query off, what we're going to do is just add a semicolon. It's not needed, but it's good practice that you add a semicolon at the end of your query or code. To clarify, you can actually run this just on the one line, but for clarity's sake, I'm just running it over three lines just so you can see the separation of each part of this code. Now, how do we actually run this bit of query? It's really simple we just go up to this play button here and just click it. Or if you're fancy, you can use the F five key. I'm just going to click it. And just like that, we've got our customer's table. It's really as simple as that. Now, if you wanted to export this, you can just go onto here and just say Save results to file, and it'll save it for you as a CSV for Excel or whatever software that you might use. Now, what if we didn't want the full table on display here? We just want the first name and last name of each customer that we have? Well, let's just break this down. The third line doesn't change because we're still using this customer's table, but we're not going to be selecting all columns, so we're not going to use this star sign here. What we're going to do instead is just simply define which columns we want to use by typing out our columns that we want. So first name and then separated by comma, we've got last name. And again, if we just run this easy as that, we've got our first name and last name as a dataset. And again, we can export that into Excel. Now, already, you can probably see how readable this is as a code. You've got select first name column and the last name column from the customer's table. Really that simple. If you're not too sure what columns reside in a table, you can either do the select star sign and just pull it up and see which columns you want, or you can go down to this Object Explorer, go into our tables here, and we've got a thing here called columns and you can actually see each column here. If you didn't want to start pulling queries to just get the full table up, you can just go up to the left and say, Okay, I want to select, let's say, email, comma, and last name from the customers table. We can run that, and there we go. Really simple. 7. Using Aliases: Now let's talk a bit about aliases. So what I've got here is just the first name column and last name column from the customer's table and just run that query and here's the results set. As you can see, results the column names are exactly as expected. They're the same as what they were on the original customers table. Now, what we can do, though, is we can add this little keyword here called as, add some double quotes, and we can change what that is going to result in. So if I just do this, it's easier to just show you, to be honest. So I'm going to run this. So let's just show you this quickly. This is the alas here. We've got as and then in double quotes, first space name. So notice that this doesn't have a space at the bottom in the original result set. If I've run this now, we can see it's changed that in the results set here. So it's now first space name. And again, I might just capitalize that and run it again. There we go. It's capitalized it, and it's really that simple. We can just add an alias. But what's important to note is that this does not change the original table. This just changes the results set in this bottom window. So nothing will be changed in the customer's table. It's just on this query, we can do that. So what I'm going to do is I'm just going to do the same again for the last name and just change that. Aliases are really great because they allow you to improve the readability of a result set, and it means that you're not shackled into whatever the tables here. The columns are called here. Sometimes they're not as friendly as you might want them to be. You're not allowed spaces in these column names. So you can go in and change that and make it a much nicer table that you can then export into Excel or whatever. And, of course, you can mix and match. If we just add this again in email. Not every single thing that you pull has to be an alias. You can keep it like that. Or, again, you can add that as, let's say, E dash Mo instead. And there you go. It's that simple. So it's just another keyword as double quotes, and then whatever you want to call your column in the results set, you can do that. 8. Using DISTINCT: Let's talk a bit about the distinct keyword now. So what I've done is I've pulled up every single column from the products table. So we're changing from the customer's table to the products table here. So as you can see, if we go down and look at this table, we've got a column called category. Now, this has multiple entries of the same type. So we've got electronics three times, accessories a couple of times, et cetera, et cetera. What if we wanted a way to just show us the unique rows or entries into this column? Well, that's where we can use the distinct keyword. So what we can do is type that right. We can use this distinct keyword and then go distinct category. This is the column that we're going to use this distinct keyword on. If I run this, we get four entries back. We've got furniture, electronics, accessories and footwear. What this means is we've got four unique entries within the category column in the products table. So if I go back and actually look at this table. That does sound about right. We've got ten actual rows in the table. But if we look and count down, we've actually only got four unique things. We've got electronics, furniture, footwear and accessories. So it's a quick way of just going through a table and showing the unique entries within a row or a column, sorry. If we did this with the product name, it probably would come back with ten rows still because each of these products has a different name. But we'll try it anyway, just to show you. So as expected, we've got ten entries because each product name is distinct. They are different from each other. So the distinct keyword doesn't work with every single column. It just works with things like categories or maybe like customer countries, customer towns, things like that. You can then go in and see which towns or which entries are unique, and that might be useful for whatever you're doing with your data analysis. So, yeah, that's basically it for distinct. The way you write it is just in the select part of the query, distinct, and then just select which column you want to check. So again, we'll just do category. And then that just selects the distinct entries within that category column in the products table. And what's pretty cool about this, again, we can use what we've learned so far and make an alias for this column and just call it unique categories. And we've run this. We've then got a nice we table here just unique categories. So hopefully, that makes sense. It's not something you'd use every day, but it's a great we tool. If you need it, it's perfect. 9. Adding Comments to Code: So we've run a bit of code, but now I just want to show you how we can add comments to that code. Now, comments are things that you can write that do not affect the code at all. So if you want to add notes for maybe developers or notes for yourself, perhaps within the code, you can do that, and it wouldn't affect the code itself. Comments are something I use all the time, because as you get to use longer and longer codes that are a bit more complicated, you might want to start adding comments and things that just help clear up any parts of the code. Comments are very useful if you're working in a collaborative environment because you can write a snippet of code. You can add your comments that help clarify it for the next person who might take on some work. So you can leave notes for the next person, even if they're just running the code, you can just show them with comments what's going on. So how do we write comments? Well, it couldn't be more simple. It's literally just two dashes like that. Now, these two dashes allow you to add a comment for one line. So if I add a comment here, it just means, let's just say like that. But if I then went to press Enter and added anything else, that's actually part of the code because it's on a different line. So the two dashes is a comment just on the single line. And again, I can do it again. There you go. So already, you could send this to someone and they would probably understand what was going on. So these two dashes just means that it's a common. And as you can see, PG Admin colors it in green. Now, what we've got here is just the syntax for a multi line common. It's quite simple. So this is actually two halves. So if I just separate this out, you can see how it works. So we've got a slash, forward slash, and then the asterisk or star sign, and then we can add whatever we want. And this is different to the two dashes here because you can do this over as many lines as you want. And then once you've done with the comment, you just do basically the reverse of what's up here. You do the start in and then the slash at the end. And it's very useful because you can add, say, titles here, instructions, longer instructions you can add. Some people do aski artwork at the top of their code, things like that. And it's very useful. And you'll see this a lot in our exercises that we do in this course. Comments are also very useful for when you're writing code, or you just want to quickly change the code without actually deleting anything. So for example, if I want to change the select statement, I can actually just comment this selection out and then change it to the asterisk. And there we go. That gives me all the columns as expected, because this is all commented out. But it means that I've not deleted any of the code. It's just been temporarily put into a comment. And then if I'm done with that, I can then delete that and rerun the code for what I've selected. Again, very useful and saves a lot of time, save you having to, you know, delete this, run it, and then potentially, if you've not copy and pasted, write that all out again. Yeah, so that's it for comments. 10. Arithmetic Operators: All right. In this lesson, I want to talk about arithmetic operators. So these operators are very, very useful for when you're working with data and you want to perform simple or complex calculations with the table's data. In this video, I'm going to show you the basics of how you can start to use these operators within the products table. Now, if you look at the last two columns in this table, we can see we've got two columns with the data types numeric and integer. These two data types work with arithmetic operators. I really want to stress this because despite there being maybe numbers in the rows, this wouldn't work unless the data types were correct, so you need some number data type. It wouldn't work with varying characters, strings, things like that. But for our sake, the table looks good. We've got numbers in the rows and we've got the data types that are correct. We can actually do some arithmetic operations on this. I've got a list of these operators that we can use. Now, maybe the first two are pretty obvious. We've got Adam subtract, but we also use the star for multiplying. And the slash for dividing. And then finally, the percentage sign, which is a module, which means it returns the remainder of one number divided by another. We're going to go through all these so don't panic if you're a bit overwhelmed. Now, how do we actually use these operators in the code? Well, firstly, we're just going to get rid of anything in the select statement, and we're just going to put parenthesis, so brackets. And within this bracket, we're going to actually do our little calculation. Let's start off with the addition operator. So we've got a plus here. And for the sake of this, we'll just put that in here. We've got our operator in the parenthesis. Now we've just got to add the information that tells the program what two columns we want to add together. So in this case, we want to add price and we want to add stock quantity. Adding the price in stock quantity might not be very useful in terms of actual a real use case, but I'm just going to show you what it actually does. So if you just run this code, there we go. Now notice that there's no column name for this. It's just giving us the calculations and nothing else. Not very useful. What we can do is we can actually re add that star back in with a comma, and this will actually give us all the columns that are on the table plus this extra calculation that we've added. And there we go. Now, as you can see, we've got our normal table plus this extra column, which is our calculated column that we added. If we actually look at the data within this column, we can see it is indeed the sum of these two columns here. And of course, what we can do is add an alias to this and we can just call it sum, just for now. I just run that again and there we go. We've now got a name for this column. Again, we can do the same thing by changing our operator and just making it a minus. Just take this subt. Run this again. And again, we've got our calculation within this column here. And again, we can do this with the star. And This is actually quite a good one because it's the price times the stock quantity, which could be stock value. Which actually is a good calculation to add to this table. This will show us the total value of the products that we have in stock. So already, that's a very useful use case for this operator. So if your boss was asking, you know, how many wireless mouses do we have? And, like, what's the total value of that? In stock, you can quickly do that calculation and send it to them. Again, we could just change that operator to be price divided by stock quantity. I'll just call that divided. And there we go. Again, not very useful in terms of the output, but I'm just trying to demonstrate the actual operator itself. Now, modulo is a bit of a weird one. It's not something I use much, and it can trip people up. So basically what it is, it produces the remainder of, in this case, price divided by stock quantity. So we've got 25/100. Now, that doesn't actually divide, so we've got a remainder of 25 99. Again, it's a bit of a weird one. So in this example, let's find one that actually does have remainder that's different. Yeah, so 70/40. We've got that can go in once you can get 40 and then 30 leftover equals 70. So it's just basically a remainder. Now, what's cool about these operations is that you don't need to use two columns to actually do any calculations. We can actually just do you know, using numbers calculate new values. So for example, let's say your boss for some reason said, Oh, we had a customer in that bought ten of everything, so we need to remove ten from the stock quantity. We can do that quite simply by using our brackets here firstly and just put stock quantity, takeaway, minus ten. Let me just say that as new stock. I mean, if we run that, we've got a calculation here of the stock quantity minus ten. It makes sense, but I just want to show you that you don't have to do, you know, stock quantity minus price or anything. It can just be numbers. And for whatever reason, you could just do, like, simple calculations as well. If you just want to use two numbers, you can add that in. Maybe that's like identifying number or something like that. But, yeah, you don't need columns for these, but you would end up probably using some sort of column in your calculations. And of course, you can do multiple calculations. So let's say we've got in this inner bracket here, we're just going to put price times too. And then let's say, we want to add some more brackets because I like brackets. They help kind of compartmentalize things for Um, sorry. We've got our inner brackets here. We can then add, let's say, 50 to that, 30, sorry. Yeah, an example. That'll run. So we've got our price times by two plus 30. For whatever reason, if that's something you needed to do, you can do that. So you don't even just need to do the one calculation. You can do as many as you want, plus 40, divide by 20, let's say, you know, we can go as crazy as we wanted, and it might get confusing because I'm using lots of brackets, but that's something I like to do because it then helps me break down what calculations are happening. We've got price times two in its own little bracket, plus 30. We can put that in brackets if we wanted. That's kind of its own thing. And then plus 2022/20, that's its own little calculation. So yeah, some people might not like to do this, but I like to do it because it allows me to kind of compartmentalize the kind of additions that I'm doing or whatever operations. And also, sometimes helps with the actual code, you know. Sometimes the code might not know what to do with numbers and calculations if you've not got it in brackets. But yeah, let's think of an actual use case for this. So let's say we've got a 10% discount on all our prices. What we can do is first start with our brackets, price, divide by 100 and that will give us 1% of the price. We can then times this by 90. Again, just for my sake, really, I just want to put that in brackets, and we can say that as the new name, 10% of and we're going to run this and notice what happens. We've got a 10% off discount of our price. Now, as you can see, we've got a lot of zeros in this. This can sometimes happen when you're dividing certain data types or working with certain data types, the way kind of sometimes, integers and numeric datatypes work, they might not be so precise. Now, what we can do, this is a bit of a bonus. We can use the round function. I'm just going to put that in brackets as well. And what we can do, I'll just write this out and show you. That cleans it up. So what's happening? The round function is essentially rounding off our number. We're rounding out something, so I'm just going to put this in brackets calculation. And then we defined what decimal point we want that to. That's the syntax if you want to use that. Round in brackets the calculation, and then a comma and define what accuracy you want. Let's just say we want the price to be for some reason, ten decimal places. We can do that. There we go. We've got all these. So if we, you know, went to 100 decibel places, if we really want to do that, we could do that, but, you know, probably not necessary. But that's probably enough information for now. Have a little mess around and just kind of play around with it. I think that's the best advice I can give you. You're not going to learn by just simply watching me mess around with stuff. I think it's really important that you start to mess around with stuff. I will provide a worksheet that you can actually start to work through and answer some questions. But for now, just have a little mess around. And that's a basic guideline for arithmetic operators. 11. Order By: Hello, and welcome back to another lesson. In this lesson, I'm going to show you how we can sort our results set. This can be very useful if you're wanting to sort your results set for export, say you want something alphabetical, you can do that, or if you want something priced high to low, you can do that as well. And I'm going to show you how you do that now. So right now we've just got all columns from the products table, and we're not going to do any filters or anything like that. We're just going to focus on how we can sort this into a custom order for ourselves. All we need to do to start organizing this data is simply at the end of this code, type in order B. This is our statement where we can define what we want to order our results set by. So for this example, let's look at ordering it by category. So we're just going to type in the column we want to organize it by. Now we've got an option of sorting it high to low alphabetically or low to high alphabetically. And that's what these two options are here. So we can either put ascending, so ASC or descending, DESC. In this case, I'm going to do ascending, just to show you how it looks. And if I run this, as you can see, it's not changed anything in terms of the data being produced, but it's changed the order of it. So we've now got a category, the column category. It's all alphabetical ascending. And conversely, if we did this descending, it now has it the other way around. So the further along the alphabet starts at the top, and then it comes back to the start of the alphabet. If we did this with price and numerical data type, and just run that price ascending. It sorts the price low to high. And then descending price is high to low. Similar to how you know, if you're doing online shopping, you can do your sort by price descending or ascending. Same thing we could do here with our code. And of course, you can do that with any column you want. But what's really cool is you can do multiple orders. So let's get back to category. So that's a Run. So I've just run that order by category ascending and it's obviously start with A, and we're going down alphabetical. But what if we wanted to go further and add another order? So if you looked within the electronics section, we've got prices that are kind of hickory Pigoty. They're not really organized. What we can simply do is add a comma and then add a second order. So let's do price descending. And if we do that and run it, we first got our category. Organized A to Z, and then, secondly, within those categories, we've actually put another order, which is price descending. So we've got the highest price at the top, and it goes down. And that's the basics of ordering and sorting your data sets. You could potentially add a third or fourth or however many order buys, but I think, to be honest, it starts to get a bit limited because if you're ordering something by the category first and then within that price, there's still some fluctuation, you can maybe add a third order by, for example, in this case, we couldn't really do much else. We could maybe order by stock quantity, but unfortunately, if we did that, it would then change the order of the price. It wouldn't be able to go ordered by ascending. So it's a kind of hierarchy. It will start with the category and then it will go into the price, and then if there is anything else we could sort, you could add that in. But in this case, we can only really do two. And if you're running this query with filters and things, with a Ware clause, this order by feature will go at the end of this. So we're doing all the filtering first, and then we're ordering things at the very end of the query. So you can basically build up your code, get that all working, and then at the end, you can add your order by category ascending and then price descending or whatever you want to do. But yeah, the basic syntax is order by and then put column, wherever you want to do, and then ascending or descending. And that's basically it. Pretty simple. 12. WHERE: I've looked at how we can use arithmetic operators, but now I want to talk about how we can actually filter our results using SQL. This is one of the most powerful and useful ways to use SQL as a beginner, and using filtering is something you do every day if you're using SQL. So let's talk about how we can filter results. So right now, I've selected all the columns from the customer's table. By now, this framework should be pretty familiar. We've got the select function and the F function. We're going to add a third one after the F called W. This is the Ware clause. And this is where we define how we want to filter our results in this bottom window. And this is where the list of operators will come in handy. We're just going to focus on this first one, which is equal to, which is an equal sign. So how do we filter our results? We start with the Ware clause, but what do we do here afterwards? Firstly, let's think about what we want to filter. For this first example, all I want to do is filter every customer that has the first name John. So in this Ware clause, all we have to do is define which column we want to filter. So first name. And then equals, and in single quotations, we put what we want to filter to. So if I run this code, we get back first name John. So this is the only row in the entire table where the first name equals John. Now notice what I do if I change that to a non capital J. Nothing turns up. That's because this in quotation is case sensitive. So that's something to be aware of. And that's how you filter things. And that's the basic syntax for aware clause. We have to define firstly what column we're filtering and how we're filtering that. And, of course, we can do these filters on any of the columns. So let's say we want to find the customer ID that's number four. So we just type in select all columns from customers where customer ID equals four. And because this column is a data type that's numerical, you don't have to necessarily put it in single quotes. So if I run this, yeah, we've got that back. And of course, if we still put it in single quotes, it would still run. However, if we tried that with the first name, just as before, we're going to run that, and that should come back with the first name John. If we didn't have that in quotations, yeah, we're getting an error because it doesn't exist. We have to put it in quotations. So in at least PGdbin you have to put single quotes when you're filtering text based columns. And by text based columns, I mean the actual data types themselves. And that's how you do your basic filtering. Now, what we have here are some of the different operators that we can use with these filters. So we've already covered equal, and that's pretty obvious. It's where something is equal to what you type in. Next, we've got greater than, less than. So if I typed in, again, customer ID is, let's say, greater than three. It results in all the customer IDs that are greater than three as expected. And of course, if we ran that as less than three, we'd get the one, too. Now, these two symbols don't include the number you type in, so it's greater than three, doesn't include free, and of course, less than three doesn't include three. But if you wanted to include that within your dataset, you can use let's say, greater than equal to. So this will be everything greater than three, but also includes free as well. And likewise, less than three or equal to free. It filters it in that sense. And finally, a nice one to use sometimes is this one here, where you put an exclamation mark before the equal sign. And that means select all columns from customer tables where customer ID is not three. And as you can see, and as you can see, we've got all the customer IDs from the table apart from customer ID free. What's interesting is when you start to do greater than and less than filters on text is that it starts to do alphabetically. So when you've got things that are less than John, you've got things that are before John alphabetically, and likewise, greater than John. It will have first names that are after John alphabetically, which is quite useful and quite an interesting way of filtering things. And, of course, if you add the eco sign to that, it'll include John within your result set. And that about covers the basics of filtering data using SQL. My advice is to start messing around with these tables, mess around with quotations and see what works and see what doesn't with different data types, and get a feel for it. 13. Using logical operators AND/OR: Right, the previous lesson, we looked at how we can start to filter our results using the Ware clause. This is great and very useful, but it's quite limited because you can only run the one filter. So in this case, we're running a filter where we're finding the first name and the first name has to be John. So if we run that, we get our result. We get John Doe, and that's customer ID number one. And that's great, but it's quite limited because what if we want to start filtering things by first and last name? Because there might be multiple johns in our database, but we're looking for a specific John. Well, that's where we can start to use these keywords here. These are sort of just logical operators that we can use within the filter, which sounds scary, but it's really not. And honestly, the best way to learn about it is just by showing you. So we're going to start off with this and keyword here. So what this and keyword does is essentially tells the filter that a second clause has to be fulfilled to produce a result. So let's have a look. First name is John, and in this case, the last name is Doe. So if we just do this, we still got our result because the first name is John and the last name is Doe, and that's correct. But say if I change this name to something else, let's say, John Smith. Now, this doesn't exist in our database. Even though the first name John, that exists because we have it here. There is no entry in our database that has the first name John and the last name Smith. So here we go. I ran the script and nothing came back. So the and keyword is essentially another filter within that first filter. So the and keyword basically tells the software that both this and this have to be true in order to produce a result. On the other hand, we've got the O keyword. This is where either of these can be correct, and it will produce a result. So if I run this now, it won't make a difference because the first name is John or the last name is Doe. It's the same row. It's the same data point. However, I've changed the script, and I've actually added an or clause of the first name equaling Jane. So if I run this now, we get two results. We've got John Doe and Jane Smith. What we've basically told the filter to do is select all the rows from customers table where the first name is John, or the first name is Jane. So these two filters are essentially independent from each other. You've got your first name equals John, or first name equals Jane. So this is a good way to kind of have two filters on the go. So we're filtering by the first name equals John or the first name equals Jane. If I change that to A, it won't work because there's no role that has the first name John and has the first name Jane. Next up, we've got the between keyword, and this is where we want to select a range of data points. So for example, we want to select, let's say, the customer ID 3-5. And if we run this, we get all the results that have customer ID between free and five, which is exactly what we've got down here, which makes sense. So it's a great way to quickly get a range of data points. The between keyword is very useful for where you want to select a range of data points. So an example here, I've actually filtered it alphabetically. So first name is between A and E. And two results came back. We've got David and Chris, which is alphabetically between A and E. So you can see how already that's quite an interesting filter. And, of course, we're going to add another clause to that. Where not only this has to be true, but their first name also has to be David. And that narrows it down to just this one here. So you can already start to see how powerful this can be. Lastly, we've got the in keyword. But before that, I'm going to show you a very bad way of doing something. So let's say I want a number of these columns back. What I can do is this very lengthy process where I do basically an or filter for each name. So I'm just going to write this out really quick. What I've done here is a pretty messy way of filtering out the names that I want. So the filter is, you know, select everything from customers where first name is John or first name is Jane or Emily or Michael or David. And it has given me that, but it is a bit nasty to write out. And you can imagine writing this out for 100 entries might get a bit tiresome. What you can instead do is this What you can do instead is this. You can use the end clause. With a pair parenthesis, you just put each entry that you want. Obviously separated by commas and with single quotes. And this will give you the same result as typing out, you know, first name is John or first name is Jane or first name is Emily, et cetera. This is very useful for when you're filtering multiple things in a big list, you can just use this clause, and it just saves a lot of space on the screen, and it saves your fingers from typing out or lots of types. And, of course, you can start to use these in conjunction with each other. We've got a filter for the first name, any of these first names, and the last name is either De or Brown. And it comes back with this result set here. And my advice, again, is just to mess around with this, see what works, see what doesn't work, see how you can change the orders of these. But as long as there's a Ware clause, you can do whatever you want. That about sums it up. And, of course, there will be a worksheet to go along with this. So if you're still feeling a bit confused, there will be a worksheet with questions and answers, so you can see how it's used in context. 14. Using LIKE and its Wildcards: Okay, so we've had a bit of exposure to how we can filter our datasets and results using the Ware clause. Now we can look at the L operator. Now, the operator is used in conjunction with the Ware clause to highlight and filter very specific patterns within strings or text. It's important to note that the operator works with string based or text based data types. So as long as your column has some sort of text based data type, it should work. Now, as always, it's easier to show you how it works rather than talk about it in theory because it's actually easier to see in action rather than just be waffling about it as always. So how do we get this like operator into action? Well, we start off with our user filter, which is the Ware clause, and then we're just going to pick a column. So we're going to filter the first name. Let's do that. And instead of putting an equal sign or one of our other operators, we're instead going to just type in like then after that, we're just going to add some single quotes. And within these single quotes is where we can start to define what we're searching for or what we're filtering for. And this is where the wild cards come in. So as you can see, in the comment here, we've got a couple of things. So wild cards are essentially what you can put into these quotations to help specify what you're looking for. We've got two wild cards. We've got a percentage sign and an underscore. A percentage sign represents zero, one or many characters, whereas the underscore just represents one singular character. Now, that probably won't make too much sense until we start to use the like operator, but I just want to make you aware if that's what they are. Let's focus on the percentage first. Now, what I'm going to write out here is just a capital J and a percentage sign. Now, what does this actually mean? What this actually means is we're going to be searching for a first name that has a capital J at the start and then any number of characters after that capital J. In other words, we're looking for a first name that begins with J. And if we execute this script, we get back two results. And as expected, the first name for these results both begin with J. As usual, this is case sensitive. So if I put in a lower case J, nothing comes up because none of the names, none of the first names in this dataset has a lowercase J to start with. So yeah, that basically tells the software that we're looking for a specific pattern within the first name column. And that pattern is a J, a capital J, and then any other characters after that J. Now, conversely, if we put that percentage sign at the start of our quotation and then put an E at the end, we're now looking for, let's have a think. So we've got any number of characters and any sort of character and an E at the end. That basically is telling the software we're looking for a first name that ends in E. So we execute that script. We get one result, a first name that ends in E, Jane. That makes sense. Now hopefully you can start to see already how powerful this like operator is. Now, of course, it doesn't have to be one wild card and then one letter. It can be any sort of combination that you want. Let's have a look. If we do MI and then an E and then maybe another percentage sign, what we're going to get? Well, let's think about what we're looking for or what we're asking it to look for. We're looking for a first name that has an M and I at the start and then any number of letters after that. But we also want an E between those letters. So as long as there's an M and I, some letters an E, and maybe some letters after that. If we execute that script, we'll get one result, Michael. And that makes sense. And if I change that E to an L, which is the last letter in Michael, it will still give us the same result because this percentage sign is just covering everything from zero characters to any number of characters. And just for context, if I just put a percentage sign, this is telling the software to search a first name that has any number of characters and any sort of combination of characters. So if I type that in and execute it, we'll get every single first name because each name matches that criteria of zero, one or many characters. And of course, you don't need to use any wild cards at all. If I just typed in John as written and search for that, it will give you that result back. But if I just typed in J, that won't give me any results because there's no first name that just has a J and nothing else in it. In order to do that correctly, I'd have to do this and add a wildcard back in. So that covers the percentage side. It's just it represents any number of characters. On the opposite side, we've got our underscore. Now, this represents one character and one character only. And this is great for if you're looking for certain lengths of, you know, text, let me just demonstrate how that works. Let's say we're looking for first name that begins with J and has three other letters after that. Never searched that, we once again get John and Jane. They seem to be the popular ones at the moment. And that's because when you think about it, we're looking for a first name that begins with a J and then has three other characters, no matter what they are, has three other characters after that. If I added another underscore and search this again, you'll notice that no results come back, and that's because we're looking for now a first name that has a J to begin with. Yet, we've got some of those. But then we're looking for ones with four letters after that J, and we don't have any names like that. So you can see how specific you can get with these things. And you can start to combine these wild cards to get really specific with your searches. So here's a quick example of a kind of combination of all three things. I've got a percentage wildcard, I've got the underscore wildcards, and I've just got normal characters. So what are we looking for here? We're looking for any last name that has any number of characters, but it contains OH at some point. And then after that OH, there's one, two, three, four characters. So I'm going to go ahead and run that and there we go. We've got one last name that fulfills that criteria, Johnson. And that looks about right. So we've got OH. Tick that off, and then NSON and that's four characters after that OH. And then the percentage sign that can represent the J. And that's one way of using the like operator. And, of course, we can start to stack these like operators using the O or and functions. So in this case, I'm looking for a last name that begins with D or a first name that begins with J. And if we run that script, we get three results. Last name that begins with D, we've got Doe and Davis, and then any first names that begin with J, John and Jane. A great example of using this is in emails where you want to have specific emails where you want all the customers that have a Gmail account, so you can just type in, you know, gmail.com with the percentage sign. And that'll give you any customers with the email that has a gmail.com at the end. In this case, we don't have any Biva type in example.com. I'll bring back all of the emails that have example.com at the end, and in this case, it's all of them. And that's the basics of the like operator. I encourage you to just mess around with these wildcards and just type in any sort of combination and see if you can get specific results. 15. AGGREGATIONS and GROUP BY Clauses: Okay, so next on the agenda is to talk about aggregations and also the group by clause. So aggregations are calculations on a set of values that produce one result. So I've got a list of the main aggregations that you might use here. So we've got Min which shows the smallest value in a selected group or column in this case, Max, which shows the largest value in a selected column, count, which returns the number of rows in a set. This one's quite useful. Sum and that returns the total sum of a numerical column and the average, which returns the average value of a numerical column. Now, these are probably quite similar to what you'd see on Excel, especially the sum and average ones. But they're very useful for if you want to just compile a bunch of data into one result. See, say you're working with E Commerce, you might want to find the MAX the highest priced order that you've had, or maybe the account, which is the number of orders you've had, you can quickly do that with aggregations. Even the average, all of these aggregations are very useful, especially when we start to combine it with the group by clause. So how do we apply these aggregations? It's actually pretty simple. So we're going to go up to our select clause and we're going to type in. Let's just say we'll use the MI. And we'll put our brackets in. And within this bracket, we put the column that we want to use this aggregation on. So let's try it on the price for our products table. And if we type that in and execute that script, we get the minimum value of the price column. Same for the max. We get the highest price we've got on our database. If we want to look at the average price of what we were doing, we can just type in AVG instead. We run that and we get this very long number here, which is just the way the number works, but we can use, once again, our round function, put that into brackets. And just add. Sorry. Just do that, run that. We can then round that up to just two decimal values, and then we've got a nice looking average price. And finally, if we want to find the total sum, which is, in this case, 1677, we just use this syntax here, SUM sum. Lastly, we can look at the count aggregation and counts a bit different. You can define a column, so we can select count price, and that gives us back ten rows here, which is exactly what's on the database. But you can just use the star sign, which is the count of any of the columns, which still returns ten. Now, this can be good because you can specify which column you want to count because if you're maybe looking at one specific column, it might not be fully populated. There might be some null values in there, which means that the count of the rows would be different from just counting from every column, if that makes sense. Let's think about that for a second. In this table, we've got ten rows in total. So if I did, select count. On everything, it would give me ten results. But let's say within the column, we had three null values. If we did the count for the category, it would only come back with seven. And that's the difference between using count star and count within a column. Most of the time I just use the count star, though, because that'll give you the total count for the whole table. We can't really use the sum and average aggregations on anything that's not numerical data because how can you get the sum of a column of text? Does it really work that way? We can still use the Min max and count, though. The MN that just works alphabetically. So if I put in product name, what's the minimum product name? I'll give us backpack, which is, I guess, you know, closest to A, in that sense, and then conversely, we use the Max product name. That'll give us the thing that's closest to the end of the alphabet, which is wireless mouse in this case. Now you're probably thinking that's quite limited, but as soon as we start to use these aggregations with the Group By clause, it starts to become really useful. So the Group By clause helps basically separate your results set into groups, and you can define what's actually being separated. And again, let's just go through it and I'll show you. So what we're going to do is just add a group by clause at the end of this. And let's just say let's go price again. We'll do average. We'll go minimum price. And I'm going to group that by. Let's just say category. So we just group it by whatever column we want to group it by. In this case, we do category. And this is going to produce a result that's probably not that useful yet, but I'm just going to do it anyway. Notice we don't just get one minimum price now. We get four minimum prices. Now, this is not very easy to read. So what I'm going to do here is just type in a second column to pull alongside the minimum price, and that's just going to be the category. We do this again. We now get something that's probably a bit more obvious what we're doing. So with the group category clause, we're taking the minimum price for each category of item. And that group by clause is very useful for worker with categories, dates, anything you want, you can start to separate out your data and still perform aggregations. When I mentioned, you can't just start adding more columns to the select clause. You can only do the aggregation and the column that you're doing the group by clause on. Because if you added the third column, it will have more rows than these two columns here that have been produced, so it wouldn't really work. You know, if it had product name, alongside this, it wouldn't work. So I've brought up the sales. So I've got here the sales table that we've not really explored yet. And let's think about what we can do as a little aggregation and group by clause. So I'm going to do just let's have a look. We'll do the max total amount, and we'll group that by group by, let's say, customer ID. And that way, that gives us the highest order that each customer has given us. So I'll just type in customer ID here, and we'll run that. Let's order, as well. Let's order by what do customer ID is sending. And actually, just to make this a bit nicer to look at, swap the order of this round, these are little things you can do to kind of make your results set a bit more readable, order by, change the column orders, things like that. And what we end up with is customer ID ordered one to six, and we've got the max order value for each customer from that results set. So you can export this, and, you know, depending on it, maybe you could send that to the marketing team and say, Oh, customer five, you know, they've had a max order of 500. But if you want to go in and say, what was the average total amount that our customers did. You could do that, as well. And still, yeah, customer five has got an average, you know, order of 280. And again, we can round that, put it in parenthesis, add a round figure. That makes it a bit more readable again. And yeah, that kind of shows you how you can use the aggregations alongside the Group By clause. I really enjoy messing with the group B clauses and the aggregates because you can get some really interesting insights. And especially as you start to get into data analysis, these kind of group by and aggregations are very useful for really poking at the data and finding out things that might not be so obvious when you first look at a dataset. Let's do one more example. Let's look at how many orders has each customer given. And we can just run this. And so it looks like everyone's had two orders apart from customer six. And is that correct? Let's have a look. So customer six has only had one order. And yeah, that's correct. We've only got one order from customer six here. And there you go, you can start to see how powerful that is. Say you had a database that worked, you know, on sales per day, you could start to do, you know, select count of sales and then group it by the date. So you could then get, you know, a count of each date and have a number of sales, how many sales were made on that date, and then you can do, like, you know, a maximum or average sales for each date, things like that. So, yeah, it's super flexible and very useful. So hopefully that comes in handy, especially if you start working ecommerce or big data and things like that. 16. SQL Join Basics: Hello, and welcome back to another lesson. Today, we're going to be covering joins. Now, joins are incredibly important when you're working with SQL, because it allows you to take different tables and merge them together to form new tables that might have different insights from what you might originally have had. It's something that's very powerful and the reason we use relational databases. But to be honest, it's probably the most advanced thing we're doing in this course. So just take your time with this video and go through the notes as well, because for me, Joins was probably the most confusing thing when I was starting to learn SQL. And the way I got over that was just by reading notes and just doing examples and experiment for myself. B, I just want to warn you in this lesson that it's probably the most advanced stuff we'll be doing. With that being said, let's get into it. So for this lesson in particular, I'm going to ask you to copy and paste this coding. Similar to the start of the course, all this is going to do is add a couple of tables. It's going to add an emails table and a user's table. And these are two basic tables that we're going to use to demonstrate the joins. And of course, this code will be in the lesson resources. And once you've run this code, just remember to refresh your tables, and it should pop up here. Right. Before we do any sort of coding, let's talk about what joins are. So joins in SQL allow rows from two or more tables to be joined together using a mutual column. The important thing to know is the mutual column between tables. This is what allows us to join them. So let's look at our two tables here. I've got the users table. Let's type that in. We've got a user ID and a name, and then emails we've got email ID, user ID, and email. Now, if you notice between these two tables, we do have a mutual column. We go back to users. We've got user ID, and then within the emails table, we've also got user ID. So this is where we can perform a join. We can perform a join using the user ID as our reference for the rules. And in my experience, the best way to visualize joins is with the VN diagram. So in the VN diagram, each circle represents a table. Let's say out of our two tables, the left one is the user's table and the right one is the emails table. The section in the middle of the diagram represents the rows that match up in both tables. Now with that diagram in mind, let's think about how we can write out our first SQL join. Now we're going to start simply with select all and then from. And now we're going to select the left hand table. So in this case, it's going to be users. And then we're going to type in join and then the right hand table, which is emails. So we've got our left and right table here, and then we need to define what we're actually joining on. So we're just going to put on keyword, and then we need to type in users dot user ID. And what this is defining is firstly the table users, and then with this dot, we're going in and then selecting the user ID column. The reason we're having to go specific with the table name is because we're going to add an equal, and we're going to type in emails dot user ID. So there's two user IDs, so we need to actually define which of these are coming from which table, if that makes sense. And that there is your basic join. Now, before we run this, we need to define what kind of join we're doing. So there's four main joins that we do. There's the inner, left, right, and full join, and I've got them down here so you can read them as we go. The basic one is an inner join, which returns the records that match both tables. A good way to visualize this is with the VN diagram, it's just that section in between that intersects the two tables. So we can go inner join, and that is our final query. So what we're going to be doing here is selecting all the columns from users and emails table. We're joining on the user ID, and we're only going to be pulling the mutual rolls. So let's just run this. And there we go. We've got our two tables joined. And then just a reminder, we've got the user, which is just literally a user ID name, and then our emails is just emails. But with this, we've generated a new table that has both the name and email. And up here in the select cause, we can be a bit more specific with the columns we're pulling. And again, because we're actually using two tables in this query, we need to define which table each column is coming from. So we can do this users dot NAM and then emails dot email. So we're taking the name column from the user's table and the email column from the emails table. And if we run this again, we then get just a name and email, and that's your basic inner join. Next, we've got the left join. The left join, as we can see here, returns all records in the left table. So it's going to return everything from the user's table alongside any records that match both tables. And again, the VN diagram will be very useful in visualizing. So it'll be the full left table, alongside any intersection from the right table. And if we run this, we get this following result. So here's the results from the left join. We've got some extra rows. We've got Hank, Frank, and Diana. Now what's interesting about this is we don't have any emails or email ID or user ID within the email table for these free rows. And because of that, these weren't actually included in the inner join. But with the left join, because these are in the user's table, they exist. So these three user IDs, Hank, Frank, and Diana, don't actually exist in the emails table. If you look for eight, six, and four, they don't actually exist in the emails table. However, they do exist in the users table, which is the left hand table. Therefore, they're included in the left join. The same exact thing goes for the right join. So if we run this, we can see that there's two emails here that don't exist in the user's table, yet they're still included in these results. And again, the VN diagram would be the right hand circle, the right table alongside the intersection with the left table. Finally, we have the full join, which is the full VN diagram, which is everything from all tables and records that match both tables. We run that, we get everything. There you go. And that's the basics of joins. Like I said, you might have to take some time to kind of figure out how left join and right join work. These two, in particular, were quite confusing for me at the start. But I think just as you use it a bit more and more, you'll get the hang of it. Now, let's look at a proper use case of a join. We're going to look at the sales table from our original database and original table set. So if we run this, we can see this table comes up. Now, as you can see, we've got not much information, to be honest. We've got some sale date and sale amount, total amount of sales, but, you know, sale ID, customer ID, product ID. These aren't very useful. Like, if there was thousands of products, how would I know what product ID five is? And how do I know what customer customer ID four is? Now, this is the fun thing. We actually have customer IDs and product IDs in separate tables. If we look at customers, we have a customer ID here and products. We have product ID. So we've got everything we need to start joining our sales table and making a kind of more useful table with that. So I'm going to start out with a select statement as always, select star, from, and the left hand table, we're going to make customers. And we'll do an inner join. And then we need to use the sales table. And what do we join these on? So we need to find a way to join these two tables. And what is the mutual column for the customers and sales table? Well, it is customers dot Customer ID. The customer ID column exists in both the customer's table and the sales table. But because we're doing things on two tables, we need to define firstly the table and then the column that exists in that table. And we're joining that with an equals sign with sales table, customer ID. There we go. And if we run this, we get back a lot of information. We get a pretty big table because we're joining two tables together with all the columns. But if we can see, we've joined, I think the sales table starts in the sale ID column here. So we've got our full customers table here joined up with our sales table here. So we can already see now John Doe was sale ID number one. So we've already improved our use for the sales table. We could do more than one join, though, so we can do a second join that'll show us what products they ordered. So we're going to go inner join. And then the products table. And how are we joining these? Well, we've got product ID in the sales table, and we also have product ID in the products table. So once again, we're going to go product dot product ID equals sales, product ID. So once again, we're using the product ID in both the sales table and the product table. And let's run that. This is going to be a very long table because it's three tables essentially joined together, but we've got our names. We've got our orders. And we also have now what they ordered. So we've got the product name, the category, price, et cetera, et cetera. And there you go. So we can see that John Doe ordered two wireless mice. Mouse, mouses, I don't know. Yeah, mice. And now if we didn't want this full table, we can go back into our select clause and just, you know, start to refine that. So let's just say customers first name, customers last name. Then we'll go, Let's do products product name. And then let's just do let's do, yeah, sales quantity. Sales total amount. And right here will be probably quite a nice, pretty table that you can send off to whoever. Oh. Missed an R. See, you got to check your spelling. That's just another example of me mistyping all the time. But yeah, if you've typed everything correctly, unlike me, you should have a very nice table at the end of this. So we've got John Doe. Wires mouse two and the order total. And that there is a proper example of how you can use joins to create a new set of data using existing data in separate tables. And that comes back to why relation databases are so good because you don't need to have a separate table here for this, which would take up space in the database. You can just use joins to make this table here and then send that off or, you know, store this as a query, and then just run it when you need that table. But yeah, it just takes a bit of time just figuring out how But yeah, it just takes a bit of time to figure out which tables go where and just how it all joins together, but it'll just be a case of trial and error. And yeah, just trying out examples for yourself. And you can use chat GBT to just kind of generate new tables for you if you just type in, you know, generate a simple table that I can practice joins on, and it will do that. And you can insert that into this and have a play around with that. But yeah I'm going to leave it there. That's a lot to digest. Hopefully, that makes sense. Like I said, joins was the thing that kind of tripped me up when I first started learning, but just persevere with it, and it eventually starts to get easier and easier. 17. Inserting Data Into Existing Tables: In this chapter, we're going to be focusing on how we can change the actual tables in a database. So how we can insert new data points, how we can delete tables, how we can update data in tables, and finally, how we can create new tables. In this first lesson, we're going to be talking about how we can add rows to an existing table. So I've pulled up the products table, and let's say our boss has told us that we're now going to be selling drum kits and guitars, and we want to add that to our database. Well, we can use this function here, we can see insert into. And this is our function that we use when we want to add rose to an existing table. So after this insert into function, we want to define which table we're inserting into. In this case, it's the products table. We then add some brackets and we just add the columns that we want to add data to. We're going to be adding data to the product name, category, price, and stock quantity. Because the product ID is a primary key, you can see that in the PK in the brackets, it will automatically be populated. So we don't have to manually insert any data in here. I will automatically create a new product ID serial number. But yes, we can add all the columns apart from product ID within these brackets separated by a comma. And then you'll end up with something like this. So we're inserting into products, and then we're inserting into product name, category, price and stock quantity. These are the columns that we're inserting something into. The next line, we add the word values. And this is where we actually define what values are going into these columns in our new row. And we just add some brackets after the value statement, and this is where we're going to add our data points. So using single quotes, we can add some values. Now we want to take heed of what's in our first set of brackets because we want to make sure that our data points match up with the columns. So our first column is product name, so we'll add the product name in here. Guitar, separate that with a comma. Then we've got category. So we'll just do instruments. Comma. Price will say 29999. And finally, stock quantity will say 50. So you can see how that kind of works. We've got our first line which defines the order of the columns and how they're going to be update, and then we're adding each value into the column as we go. So we've got product name, guitar, category is instruments, price 300, stock quantity 50. And if I run this now, it would insert a single row into the end of this table. But you can actually add more than one row at a time. So I'm going to just put a comma at the end of this to go to the new line and some new brackets. And we could do the same thing. We could add some more values. So we'll see drum kit. It's in the same instruments category. You can see the price is 1,000. And finally, the stock quantity is only 15 because drum kits are big. We'll end that statement with the semicolon. There you go. It's the same thing. We've just separating out these statements in the brackets with a comma here. And there you go. This code will now add two rows to the products table, the guitar row and the drum kit row. So let's just run that. There we go. We've got our message here insert, and it has a number two, which means it's inserted two rows. And if I select all from products, we should have two more rows at the bottom. Guitar and drum kit. There we go. It's got our information put in correctly. I notice that my product ID will probably be different from yours, and that's because I've gone ahead and deleted and added rows a number of times. So the way this works, it will always have a new number. So yours will probably be 11, 12. But if you were to delete these two rows and add more rows, it would then be 13, 14, et cetera, et cetera. You can't have repeats of the same product ID, so it will always generate some new ones for you. So don't panic if it's different for you. But yeah, that's how you add data into existing tables. Now it's worth noting that you don't have to add every single column from a table in this first insert into statement. Say you didn't know the stock quantity, we didn't have to add that in if we didn't know it. We can get rid of that. And then if we get rid of it in the value statement as well, we could run this, and it would end up with no stock quantity information in these two squares at the bottom here. However, sometimes columns must be populated. This is because we don't want a products table that has no information on product name, for example, or no information on the price. And we call these little rules constraints. We'll be talking more in depth about constraints when we're creating tables. But for now, if we go to the object explorer, right click and go to the properties of the products table. And then just go into columns. Now, if we look at this column not now, we can see that there's three columns ticked, and these are the columns that have to have information in them. So that means we can leave the category and stop quantity blank. So let's go back to our statement and say we don't have the category. We can just go product name and price. So we can get rid of our category here in our value statement, and to say, let's say, microphone and cables. And you just want to go over that, make sure that it looks nice, and there's no extra commas or extra quotation marks. That looks good to me. And if I run this now, let's have a look. There we go. It's worked, and we just select all products. We go down. There we go. We've got microphone and cables, and yet the category is null. But what's interesting is the stock quantity, although we didn't put any data in, it's defaulted to zero. And that'll be something that's designed within the table. So if we go back to the products table up here and go to properties, and we look at columns. We can see that stock quantity does indeed have a default value, zero. But if we deleted that and then re ran the query, this would also be null, like the category. But yeah, I just want to show you that you don't have to add values into every single column. Like I said, we'll be touching on constraints in further lessons, so don't worry. 18. Deleting Data From Tables: Alright, so the previous lesson on insert and all the kind of different constraints on tables was pretty intense. So in this one, I just want to do a nice chill lesson on the delete function. The delete function is just a way to remove rows from a table. And you can specify which rows using filters, like we have done before when we select ros. So let's just say our boss has changed their mind and we're no longer selling musical equipment. We need to get rid of these free rows here. We need to get rid of the drum kit row, the guitar row and the microphone row. So how do we delete these rows from the table? Well, we start off with this phrase here, delete. From and in this case, we'll be deleting from the products table. And we just use our ware filter, and this is where we define which rows are going to be deleted. So let's have a think of the different ways we can do this. We can just look at the roles that we want to delete and we can use any of the attributes that are associated with this role to delete it. Let's start off with deleting the microphone role. A good way of doing this is by using the product ID because that's unique to that role, and it means that there's no duplicates that are going to be deleted or anything like that. It's going to be just that one row. So we can do this. Delete from products where product ID equals 17. We run that. And if we run that, it's deleted one row, comes back with that output there. And if we select from all products, we'll see that the microphone row has been deleted, and this is a permanent change to the table and the database. Now let's have a look at how we can delete the drum kit and guitar rows. Well, like before, we can use the product ID like this, 13. If we run this now, it would delete the drum kit row. But then if we want to speed that up, we can do multiple deletions at the same time. In this case, you could use the category column to filter our deletion, because the only ones that are in the instrument category are the ones we want to delete. Alternatively, you could stick to the product ID. If you want to delete multiple rows at the same time, you could start to use the in clause. Or if you want to get fancy, you can start to use logic, depending on what situation you're in. You can use the or or the and function if you wanted to get more specific with what you're deleting, This would also work here, but I'm going to stick with the in clause. I'm just going to run that. And here we go. It's come back with Delete two, so that's deleted two rows. And if I run that, it's now coming back with our original ten rows. So there you go. That's how you delete rows from tables. So now you know how to insert data and delete data as well. So why not have a play around with inserting data into a table and then deleting that data? 19. Editing Data In Tables: As well as inserting and deleting rows and data from tables, we can also update and change the existing values within the table. We can do that using the update table command. This is useful if you want to make some permanent changes to an existing table in a database. There might be a case where you're permanently changing a price of a product or maybe you're changing the email permanently for a customer or maybe a customer got married and they want to change their last name within the database. It goes without saying that updating tables and data is an integral part of what we do. So it's important to learn how we do that. So let's look at how we can update some things in the products table. What we're going to start with is the simple command update. After this, we define which table we want to update. So in this case, it's the products table. Next, we write the set command, and within this command is where we're going to define what columns are changing and what we're changing within those columns. So let's have a think of what we want to change. Let's say we want to change the price of the standing desk, and it's gone up in values. It's 300 pounds now. All we have to do is type in set price equals 300. Now, it's important that we don't run anything yet because if we just run this, it would set every single data point within this column to 300. What we want to do now is just define what role we want to actually set this price to. So we want to add a Ware clause. And as usual, a great one to use is the product ID for filtering. So let's say, yeah, product ID for the standing desk, which is the row we want to change is ten. So product. So there we go. We've narrowed that down. So we're only changing the price to 300 where the product ID equals ten. And in this case, we go down. Product ID ten is the standing desk, which is correct. That's the one we wanted to update. So I'm just going to highlight and run this and there we go. We've update one, updated one row. And if we select everything from products again, we now have the updated price for the standing desk. We can go back and change that to the original value if the price goes down again. And once again, we've got 250. And now it goes without saying we can update more than one thing at a time. So in the set clause, we can add a little comma here, and we can add another thing. So let's say we want to change the category to something else to say office furniture instead of just furniture. And, yeah, we can run that. We've updated one row, that's correct. And if we and we run and if we just select everything from products again and we run that, we've now got an updated category to office furniture. And again, I really want to stress, if I got rid of this filter and did this and ran it, it would change everything in the table. So everything would be set to the price 250, and every single category would end up being office furniture. So you want to be really careful and always have some sort of filter when you're updating data. There's obviously countless uses for how we can update data, but a good one is, you know, if there's a spelling mistake in a category, you can just say where category equals whatever is misspelled, and you can change it here to the correct spelling. And, of course, with the Ware clause, you can be as creative as you want with the filtering and what you're actually updating within the table. In this case, we're updating by product ID, which changes one role, but you could do whatever you want. You could change things by category, by price, by stock quantity, wherever you want, you could change it. And that's a basic guide to how you can update existing data in a table. Now, I am aware there's a lot of new information in this chapter, especially all the new keywords like update, set, values, delete, insert, all this stuff. But as always, the worksheet and the reference sheet will be there to guide you, and you could use that when you start to write your own code. As always, the best way of learning this stuff is just by doing and trying, seeing what works, see what doesn't work. And honestly, that's the best way I learn. 20. Creating New Tables / Contraints: All right. In this lesson, I want to talk a bit more about how we create tables in SQL. I also want to talk a bit more about table constraints. Also, towards the end of the lesson, we're going to be looking at some quick ways to go about the stuff we've been doing in this chapter. There might be some quicker ways to add tables, change those tables, and add data to those tables as well. Right now, I just want to focus on the code because without the foundation understanding of the code, it's pointless doing the quicker ways because you don't really gain an understanding of what's happening at a foundation level. For now, we're just going to focus on how we add tables using SQL. Now, if you've had a little read through the code that we copy and paste at the very start of the course, to create a table, we just simply add this command. Great. Table. Couldn't be more simple. And what I've got here is just an Excel sheet of information about cars. So this could be like your fleet information, so the cars that are running in your company. So we're going to use this dataset to make a new table. And this Excel sheet will be in the lesson resources for you to download and use. But for now, let's just call that table fleet. Next, we're just going to add some brackets, and this is where we're going to define our column names, the data types of those columns, and any constraints on a table. The nice thing about having a dataset already here is we can just copy the names of the columns from this Excel sheet. So I'm going to go ahead and just do that, list these out. So we've got make model. Year mileage, fuel and last used. That's all the columns that exist within the Excel sheet. Make sure that these are matching the Excel sheet because this will be important later. Along with these columns, I want to add another one. I want to just say a car ID, and this will be our key column. So we've got all our columns that we want in the table. Next, we need to define what each column is in terms of its data type. Now, in the lesson resources, I shall give you a link to this website here, which is the PostgresS SQL AKAPG Admin official website, and it gives you a list of all the data types. So depending on what we need, we can use any of these data types. A lot of the time you'll be using Varcar which is variable length character string, essentially text, and you'll also be using integer, which is numbers. There might be some other things you'll use like calendar day as well, and this is in the format year month day. Another one we'll be using is serial, and we'll be using this with the key column. And what Serial does is, as we've seen in the past, it adds a number each time you add a row. So as you keep adding values to this table, we'll be generating a unique key number every time. So you can have a we look through this list of data types. Obviously, there's loads here. You probably won't need all of these. But yeah, just focus on the ones you might need such as serial, integer, and variable character, and maybe date as well. So let's go back to our correct table statement and define which datatype each column will be. So the car ID, that'll be our key column. So we want to make that a serial data type. The make will be variable character, like that. Likewise, the model, that'll be Varcar because it's just text. The year, I'm going to make that an integer. Likewise, with the mileage. The fuel, that's just the text pase thing. If we look again, we've got just text. So make that varcar and last used in this column, it's a date format, so we'll use that date. Now, we could run this code, and it would create the table successfully, and it would have all the columns with the right data types. But what we want to do is add some constraints to this. So constraints are used to specify rules for the data within a table. And what I've got here in this comment is some of the main constraints we might use. We've got the not Null constraint, which means the column must have data. We've got the unique constraint. All values in a column must be different. The primary key statement defines the primary key, and it makes the column not null and unique at the same time. The check constraints are really useful because you can set a specific criteria for the column that must be met in order to add data. So we're going to add a check constraint to the mileage, for example, because a mileage can't be minus, so we're going to make sure that every thing that goes into this mileage column is higher than zero. And we can also add a default constraint, which sets a default value to the column. So let's go ahead and look at what we need to do. So we're going to make sure that the car ID is the primary key, so we can copy and paste that in just after the data type, like so. And that'll make sure that the car ID is serial data type, but also primary key. The make and model, we want to make sure that there's always a make and model within the column. So we're going to make that not null for both of these. The year, not too fussed about that, we can leave it as is. The mileage, we want to make sure we add a check, and then within these brackets, we add what we want to check. So in this case, mileage. Is greater than zero. I just realized I accidentally typed in mileage instead of integer for the data type. So there's no mileage data type. I just want to clarify that. Sorry, that's just because I've run out of coffee and I've not had my coffee today. But yeah, make sure that's the right data type. So the mileage is an integer data type. So let's go ahead and run this. So there we go. We've just run that and return successfully. And what we've got to do is go down to the Object Explorer, right click, refresh, and there we go. We've got our fleet. And what I'm going to do is select all from fleet. And I'm going to run that, there'll be no data because we've not added any data to the table, but we do have our columns all set up. Now this is where we can start to do our insert query, where we insert and add the data manually. But what I'm going to show you here is how we can take this Excel and just import the data quickly. So what we're going to do is just so we're just going to right click and go up to import and export data. And we're going to go into our file name here and just choose the CSV file. You want to make sure that it's a CSV file. You don't want to use an Excel file because it won't work. I has to be CSV. Once you've got your file, we're going to go to Options and make sure the comm is selected, and then let's look at columns. Now, the columns to export, we don't want to add card ID because that's not actually in our Excel sheet. We've just added that into our table for our reference when we're doing key columns and joins or whatever. So just get rid of that, and then you should be good to go. We're just going to click Okay. And after a few moments it should say process complete it. And if I select all from the fleet and run it, there we go. We do have our fully populated table. And as you can see, the car ID is already populated for us nicely. And that's a very quick way of adding a large chunk of data, especially if you're using Excel sheets. So yeah, just make sure that you're using a CSV file and that the columns match up in terms of their names, and you're selecting the right columns when you're importing. And as you can imagine, that saves a lot more time compared to writing insert for each of these roles. But, yeah, that's the basics of importing data using Excel. Next, I want to show you a few shortcuts now on how we can create tables, change those tables, et cetera, et cetera. And the reason I didn't show you this at the start of the chapter is because I wanted you to have the understanding of the code itself and how we add tables and change those tables using SQL. It's important to understand the processes that happen in the background because you might not always have this create table window, and you might have to do it manually using the code. But I've given up the secret. If you right click on tables create table. You've got a nice window, which just basically does the same thing as what we've done in the code. So we can name our table. We can add columns using this plus here, Column one. We can change what that data type is quickly. So let's just call it serial. We can add constraints here, primary key and not now. And we can do this for as many columns as we want. We can also go into constraints, and we can go into check constraints, and we can add check constraints this way. We can just call this check one. And then what you type in here is what we typed in our brackets so we can do, you know, something like this. You can add lots of different constraints and change the parameters. This is all quite advanced stuff. You don't need to know too much about this. But lastly, you get, at the very end a generated code for you. And yes, I realize how much easier this is, but of course, now you can read that SQL code and understand what's going on rather than just using this table and just kind of blindly putting it in. And, of course, we can right click and go into the properties of an existing table, change the column names, change the data types. But there's also a command here if we right click, we can go in the scripts, and then it's got some templates we can use. So if we want to insert data, we can go up to this and insert script. And it'll generate basically a template that we can use, and then we can just punch in our values where these question marks are. And yes, this is so much easier, and I'm sorry I left this till the last minute. But it's just the way I wanted to teach you, and you're probably going to hate me for that. And yeah, there's create, select and set update, and delete scripts that you can use that are great as templates. But yeah, it's good to know how these are actually working. But yeah, I'm going to leave it there for now. You now know how to create a table using QL code, but also how to do it on the PG admin software. You also now know how to import large chunks of data such as Excel sheets into PG Admin, using the import export option. But yeah, have some fun, make some tables, create constraints, see if you can add data or when you can add data, and yeah, have fun with it.