SQL for Beginners - A Data Analyst Path

1. Introduction: Hello friends, I'm Tony and you're in the right place if you're looking to learn how to use SQL or sequel to get data from databases. For those of you who don't know me. A couple of years back, I graduated as an engineering Boston and went straight into data science for a couple of years until I decided to come back home to Venezuela, where I'm now working on a business intelligence team for half of my time. And the other half of my time, I'm pursuing what I'm getting excited to do and to wake up every morning to which is learning how to learn and inspiring others to do the same thing. That is what brings me here today and also to my YouTube channel. When it comes to SQL, I guess the professionally for multiple years. And he came to the point where I wanted to share my experience and teach others what I've learned. In this class, you'll learn how to use eco from scratch with a class structure that is aimed to accelerate your learning. You'll be able to use sequel to write the code you need to get the data that you want from databases. This class is meant for anyone who has no idea of what a database is or what SQL is and is looking to learn how to use them from scratch. The class can also be super helpful for anyone who is already familiar with these concepts and is looking for a refresher. The only things you'll need to be when you take this class is a computer and eagerness to learn new things. The class is designed so that you take one lesson each day by spending around 10 min on each one. This way, new knowledge will sell into your brain after a good night's sleep and you'll be taken advantage of rest and recovery. We're going to start off the class by learning what is equal. Then we're going to look into manipulating data. Next, we'll look into querying data, then aggregating data. And finally, we'll look into how we can work with multiple tables will also have a class project that will be to build your own database using sequel in a database browser that won't be downloading in the course, I will be building a database meant for a company as an example throughout the class. And you'll be able to follow along with my example and get creative in your own ways. But that's it for now, and with nothing else to add, Let's get started. 2. Class Project: Welcome my friend, and I just wanted to give you some more details on the class project. So again, I'm going to be building an example of the database for a company throughout the class, it is going to have three tables, so it's going to have any customers stable, subscript fits table and then order stable. And you'll be able to follow along throughout the class on how to build that and how to build anything that you find yourself getting creative with, we are much more likely to finish the things that we start. So I would highly encourage you to start off with the class project right away. And for that, all you need to do is to click on the Class Project button down below. And since we haven't started the class yet, you can simply add my new project does a title, and then for the text you can add this will turn into my final project soon. So I highly encourage you to do that and to make updates to the project as you go throughout the class. That way you learn by being active instead of passively absorbing the information. And we'll also learn from each other as well. But that's it for now and I'll see you on the first lesson. 3. Day 1 - What is SQL?: Hello friends and welcome to day one. And today we're going to cover some of the key concepts we need to know before writing our first line of code in SQL, we know that we can use data to solve real-world problems. And when we start to work with data, we eventually stumble into databases. Sql stands for Structured Query Language, and it's a programming language that we can use to manage and explore data stored in databases. A database is a set of data stored in a computer. And this data is usually structured into tables, which can look very similar to an Excel spreadsheet. We can use SQL queries to directly get the data we want from the database. The SQL language is widely used today across web frameworks, database applications. We also have that a relational database is a type of database. It uses a structure that allows us to identify and access data in relation to another piece of data in the database. Often, data in a relational database is organized into tables. Tables can grow large and have a multitude of columns and records. Columns are labeled descriptively and have a specific datatype, e.g. a. Column called Customer ID can have a data type of integer. And then here we have an example of a relational database where we have different tables that relate to each other with references of different keys and different fields that are referenced among the different tables. And this is a type of database that we're going to be looking at throughout the course. We also have that a relational database management system, or RDBMS, is a program that allows you to create, update, and administer a relational database. Most relational database management systems use the SQL language to access the database. E.g. we have SQL Lite, which is a relational database management system will use and discourse SQL-like contains a minimal set of SQL commands, which are the same across all relational database management systems. And other relational database management systems may use other variants. Some popular RDBMSs or MySQL, postgresql, Oracle DB, sql Server. And then the one we'll be using, which has SQL light. There you have it. These are the concepts that we learned today and now we're ready to start using SQL light on our next lesson and we're going to start writing our first lines of program in sequel. So I'll see you here tomorrow. 4. Day 2 - Install SQLite (Mac & Windows): Hello my friends and welcome to day two. Today we're going to install SQL light. So we're going to start off with Mac. If you're on Windows, you can skip ahead in the same video and you'll see the installation for that. But if you're a Mac, we start off by going into Google and we're going to search for sequel light browser. So this one SQL-like browser and we want to find the SQL lite browser.org website. So we're going to click here. Next. We're going to try to find the download section. So I'm gonna click up here in downloads. And since we're on Mac, we're gonna go down here to Mac OS. And in you have two scenarios. You can either have the Intel chip or the Apple silicon chip. So in my case I have the Apple silicon chips. So I'm going to click here. Down to the bottom. At the left you're going to see the downloader and that is going to start downloading. You're going to click on that. Once it finishes, then you're going to scroll this icon, the application into your applications folder. And now you're ready to do Command Space to get Spotlight search. And you'll be able to do DB browser for SQL Lite. So then I hit Enter, I hit on open. And that will open up the application. So if you're on Windows, you can go to Google and do SQL-like browser. And we're going to search for the SQL lite browser.org website. So there's one. Next, we're going to find the download section. Once in the download section, you can go to the Windows section and then you have four different options. You can click on the standard installer for 64 bit Windows, and that will start the installation. Once that is done, you can click on that. And that will open up the installers. So click on Next, agree, and next. And then this is the important part. You want to check off that desktop under DB browser. Then next, next and install. Once it finishes, you'll be able to search for the DB browser application. And you can open that up and see that now you have the application installed. 5. Day 2 - Create a Table: Once you open up your database browser, you're going to see that it has a lot of functionality in there. For the purposes of this course, we want to be focusing on SQL commands specifically. So that's where we're going to be focusing on. So we're going to be ignoring most of those functionality. And we're going to be focusing on what we can write down in SQL, which is the most powerful thing. One thing that we do have to do is that we need to create a new database so we can start creating our new tables and have our new database. We click on new database up here and that will prompt us to add a name to that file. I'm going to call that company in my desktop, and that will have the SQL extension. So you'll see here too, it'll have the dot db extension. I'm going to hit Save. And now this window pops up and I'm going to close this window because I'm going to ignore this window for now. But now we're working on, as you can see, the extension on the top of the window here that it says company dB. We're working on our new database that we have just created. The very first thing that we're going to do in our databases, that we're going to create a new table so we can start adding data to a table and adding records to that table. So we're going to navigate up here to the execute SQL section. This would bring us to this text area. We can write any texts that we want and this is where we're going to be typing or SQL commands. So to create a table, we add, this is what's called a sequel clause. So the class will be creates table. Then we add the name of the table. And this one we're going to create a table called actors. Next, we have brackets and then we finish the statement with a semicolon. We have to finish all the SQL statements with a semicolon. Now, between these brackets is where we would put the columns that we want to have. I'm going to add some space just so it's more readable. And our first column is going to be called id. Next we're going to have a column called name. And finally we're going to add a column called age. And as you can see, we're separating these by commas. And another thing that we put with the parameters for each column we need to add and we need to specify the datatype of each column. So for ID, we're going to have integers. It's going to be an integer. The name is going to be a text of type text, and then h is going to be of type integer as well. So now we have our complete statement of our create table statement to create the table called doctors with the, all the columns and all of the parameters. And we can click on this bond right here. Or we could do Command Enter to execute this query. And we can see down here that our query has been executed without errors. And now we can navigate up here to the database structure section. And we can see that we have one table called actors that we have just created. And we can expand this as well to see what are the attributes and what are the parameters that we're inputting into that command that we just ran. Okay, so now that we have our table created, we can run a different query. So I'm going to delete this query because we have just created our table. And we're going to run a different query to insert values into the table. We have just created the class where this query is going to be insert into. And then we're going to state the name of the table, which is actors. And then we're going to do brackets. And between the brackets we're going to add the columns that we're going to be adding name, id, name, and age. Then we add a values clause. And again, remember that you could write everything in one line if you want it to. And the spacing here doesn't really matter, but I'm just adding another line just to make it more readable. So we add the values clause, and then between more brackets we add the values that we're adding in the same order that we stated above. So if we did id name and age, we first input the ID that we're inputting. Then we're gonna say, we're gonna say we're gonna add Brad Pitt as our first factor. Then we're going to do 58 for the age. And we're going to do a semicolon. So Command Enter and we see down here execution finished without errors. So that's perfect. Now if we wanted to see what's inside of that table, we could do Browse Data up here, and then we have our table. We select the table up here at the table for actors. And then we have ID1, Brad Pitt and age 58. We see that we have just inputted into our table that record. So what we're going to go back to our Execute SQL section and we're going to add more values. So we're going to do the item number two or ID number two. We're going to do Scarlett Johansson is 37. And if we wanted to add more items in here, we could do, we could separate the brackets by commas. So we could do this e.g. add a comma up here, do add more spacing so it's more readable and add more fields to it. So now that we added more items to our table, we can do Command Enter or Command Return again. And we can see that our execution finished without errors. And then we go back to our Browse Data section. And we can see our table here with our four actors. And now that we have created our table with the create table statement and then insert into statements. We can now run a select statement. And a select statement is run when we want to get data from a table that is already existing. So we can do select. Then we're going to do the wildcard called all. So it's a star and we call this all. So select all. Then from. We use the from clause to then state the table we want to get from actors, and then do a semicolon. And when we run this, we see down here that we have everything that we have selected. So we have select everything from the actor stable and that is what returns or what is returned from that query. And we can see that our table gets returned. And again, remember that we did this for each datatype, right? So the first column is an integer, the second column is a text column, and then the third column has an age column. And we could see this in the database structure. If we went up here, database structure, we opened up our actor stable. We could see that id is integer, name is text, and age is integer. One last thing is that the classes are written capital letters, but they're not mandatory to be written capital letters, but they're just done that way for convention. So remember that and then we could run everything in one line if we wanted to as well. This would run perfectly fine as well, but we're just running separate lines so it's more readable. So there you have it. Today we looked into how to create a table with the datatypes appropriate to the columns. Then how to add new records to that table, and then how to select everything from that table. If you want, you can feel free to update your class project with the things that you learned today with your new database and your new tables. And apart from that, Thank you for watching and I'll see you again tomorrow. 6. Day 3 - Managing Tables: Hello friends and welcome to day three. And today we're going to look into different ways we can manage our tables. We're going to look into the select statement again, alter statement, update where Delete and different constraints. So we know from the last lesson that we can select all from a table using this statement. So if we do select all from actors, we have our entire actor stable that gets returned. If we wanted to select a different columns that we wanted to get back from our table. We could name them by the name of the column. So we could do instead of select all we can say select name, Let's say name, and then separated by comma, we can say h. So now we have select name and age. And when we hit Enter, we see that we get returned everything except from the other column that we didn't specify. So we have our name and our H returned. Now let's say that we want it to change our table and we want to modify a table by adding a new column into that table. So let's delete this. And for that we're going to run the alter table, this new class that we're learning. So alter table, we're going to specify the table. We're going to say if actors. And we're going to add column. Again, I'm doing this in a separate line, but if you wanted to, you could do it everything in one line. I'm just doing a separate lines to make it more readable. And remember that all caps is not necessary, but it's done that way for Convention, the new column is going to be called Instagram handle. And it's going to be a type text. So there you have it. You first specify that you want to alter the table and name the table that you want to alter. And then add a column with the column name and the column datatype. So when I run this, it says execution finished without errors. And now if I wanted to say, select all from actors. And remember you can use this Play and Stop button just to run the current line that you have. So you can run this just to run this current line. And you see that we have our table selected with a new column with everything as a null. So null means that it has no data in that field or in that record. So what if we wanted to update our table to add data and add information into those null column or into those null records that we just created for that. I'm going to delete this for now and I'm going to say update. So this would be the update cluster. So I'm going to say update actors. So the name of the table that I want to update. And I'm going to say set Instagram handle equal to. And then this would be some texts. So any texts that you want that you want in here, you can input any texts that you wanted here. For now, we're going to do the Instagram handle of Brad Pitt. So we're going to say Brad Pitt. And we can add a where statement and we'll get learned. We're going to learn more about the word where clause in further lessons. But for now we can see where id equal to one. So what am I saying here? I'm saying update the table called doctors and set the field called Instagram handle or the column called Instagram handle. You're going to set it equal to Brad Pitt, where id equal to one. So when I run this, and then I say select, select all from actors and run it with only the statement that I have selected. We can see that Brad Pitt now has an Instagram handle. So I'm gonna go ahead and create statements to add handles to other actors as well. Okay, so now I have created another statement or another query to add or to add our Instagram handle to Scarlett Johansson as well, and to Matt Damon by referencing their ID numbers. So if I select or if I put my cursor on this query and I do this play and stop button that will only run this query again. So then I'm going to select this query from the button. And I'm going to run that again. Then just a select statement. And then I'm gonna run that and I'm going to see my new table, or the same table as before, but altered in a way that I now have Instagram handles for most of my actors, all except Morgan Freeman. And now the next statement that we're going to look at is going to be the delete statement. So I want to delete, let's say I wanted to delete Morgan Freeman. There are many ways I could do that, and one way is to use the Delete Class. Delete from actors. I say delete from actors. And then I add a where clause to specify which one I want to delete. So where Instagram handle. Is null. So this is known as a new statement that references of the values that are not, you could do is null and you could also do is not null. But for now that's used is null. And when I run this query, I can see, okay down here, execution finished without errors. And now I get with, when I select, select all from actors. Again, in here, I can see that Morgan Freeman has been deleted. So now I can see Brad Pitt scarred your hands and Matt Damon only the, only the records and only the rows that have an Instagram handle because everything that didn't have an Instagram handle was deleted. So if I had if I would have had more rows or records without an Instagram handle as a null. Those would have been deleted as well. Okay, so that was all we were going to learn from our actor stable. So now we could use, what if we wanted to delete a table, we want it to drop a table. We would say drop table actors. This would, when I run this, I would go to my browser data and I would see that I don't have any tables and I would go to data structure or database structure. And I would see that I have no tables because I dropped the table. So that is how you delete a table. So now we're going to delete this and we're going to create a new table, which is the one that we'll use for our company database and the one that we'll use throughout the rest of the course. And it's going to be a customer's table. So to do that, we do create table, as we saw in a previous lesson. Customers. Then we can do ID, integer, name, texts, age, integer, and nationality text. And we're also going to look into some other parameters that we can specify when we create a new table. E.g. let's say that we want the ID to be a unique identifier in school and in relational databases. Thus, how we would call, or we would call this a primary, primary key. So now I'm telling that I'm telling SQL or SQL light to turn this id column into an integer and also make it a primary key. Another parameter that I'm stating just for now, we're going to learn more about primary keys in a further lesson. But for now, let's just say that the ID is going to be unique and it cannot be null. And it will be perfect to identify every single row uniquely from the table. So that's what a primary key is. Then we have our text column, name column. We want this to be unique as well. Not a primary key, but we want to make it unique so we can specify that as well. And we can also specify that we want it to be not null. Then SQL light will make sure that that it will not allow us to fill a record with the name column has a null because we're required to make it not null. Then age is just gonna be an integer. And then nationality, we're going to add a default value. And we want that default value to be not available. So whenever we input a new record into this table and have nationality as a null, then the default value will take place and this would be a not available instead of a null. So there you have a different way or a different, a different way that we can create a table with more parameters to make it more specific and make it more functional when we use it later on. So we have our primary keys, we have our unique statements. We have our NOT null parameter as well, and then we have our default parameter. And when we hit Command Return, we can see the execution has finished. And when we go to our database structure, we have our new table called customers with an id, name, age, and nationality in the description and the data types that we have here. But now we're ready to go into our next lesson where we work with the customers table. If you want, you can update your project with what you have learned today. And I look forward to meeting you back here for the next lesson tomorrow. I'll see you. 7. Day 4 - Select, Order, and Filter: Welcome back my friends today for it. And today we're going to look into different queries that we can write. So we have different sections on this course on queries and today's going to be our day one of looking into those queries. So we have our database structure for now. Like this. We have that we have only one table in a relational database called Customers with these four columns or this four attributes. And we have our ID as our primary key, which will look into further in a further lesson. But now we can go into our execute section or Execute SQL section. And the first thing we can do, just to examine what we have, we could do select all from customers just to see what we have, right? So Command Return. We see that nothing is returned in the second window down here, because we have nothing in this table yet. So in the supporting material down below, you can see in the description that you can download a supporting resource that is going to look something like this. So it's sort of a text file that will have some of the information that is going to be helpful to create the same database and so that we can work on the same, in the same database. So here there's a create statement that we already ran, the create customer's table. And then there's a statement to add records to that table. So I'm just going to copy this and paste this in here. And I'm going to run it. Once I run it, I can now delete this and do select all from customers. And now I can see down here in the execution that I got my full table into the database. So again, we could do, instead of selecting all we can also do e.g. name and age Command Return. And we can see that we can select only the columns that we specify. We select every single one from those columns as well. So we get all the records from the table only with that column. We can also use the keyword, which is this one. So you can say As and then years, e.g. and now you are turning this name instead of saying age is going to say years because we're turning it using the keyword. There is another statement called the order BY clause. So we can do order by and sprung at the bottom of our SQL query in this case. So we can do order by and specify name. And by default the order BY does ascending. But we can say descending like this, DESC. And when we run this, we could see that we get our return state are returned content, it is the names descending and then we have the years as well. There's also a way to select distinct from the tables. We could do select distinct, let's say nationality from customers. So now we're selecting the nationality but only the distinct ones. So we do Command Return and we get all the countries. We see that all of our customers are from seven unique nationalities as we see down here. Then remember again that we have the where clause that we can use to specify or to filter specific things. So we can say e.g. select all from customers. We could do where h is greater than 30. So this is how we would select all of our customers where the age is greater than 30. So we see that we get seven customers returned. And that is a good way to filter. And because instead of this, greater than you could do also use greater than or equal to, less than, less than or equal to. And you could also do equal to, of course, which would be like this. And you could also even do not equal to. So that would be an exclamation point and an equal sign. So we can say H naught equal to 30. And we do Command Return. And we see that we get all of our customers returned because none of them are equal to 30. But e.g. if we do, we did 25 not equal to 25? We would get one customer lists, so we will get nine costumers returned it. So that was it for today. And as an exercise, I'm going to throw out some ideas and some things you can try to exercise and some questions you can try it. Why don't you try selecting all the customers ordered by their age and descending. So that will be a challenge. And then another challenge would be select all the ages that are distinct. So select all the distinct ages from our customer table. And finally, select all the customers with an age that is less than or equal to 25. Let's see what we get. So those are some examples of things that you can try. And for today that will sit and I'll see you again here tomorrow to look into more queries that we can run. 8. Day 5 - Wildcards and NULL Filter: Welcome back my friends. They five. And today we're going to look into more queries that we can run. And more specifically, we're going to start by using wildcards. So let's say if we go here to browse data, we have all our users, right, with all their nationalities within their names and their IDs. Let's say that we wanted to select e.g. there are these two users that are named really similarly. So Mary Madan and Mary Malin. So what if we wanted to select all of the costumers called a certain way with a character that may vary or with, with whatever character field in between. I'm going to show you here. I could say that we could do select all from customers where we could use our where clause, where name is like. So this is our clause for now or a filter for now. It's going to the one that we're learning now it's called like and when we can pass it in a string or a text. And this text, we're going to say Mary, as we saw here before. So it's Malin and made it. So we want to select both of them with a single command and we're going to say May underscore and E n. So this would, it tells the query into executing sql says pretty much select every name where it's like this name like this. And this is what's called a wildcard, which states that this could be filled with pretty much any character and any customer that meets that criteria. So when I hit Command Return, I see that I get those two customers returned because both of them are meet the criteria that I specified up here with the wildcard of the underscore that specifies that that could be any character. Another example of a wildcard would be that also in the name I can say that it's like then let's say I want to select all the customers that start with a J, a capital J, and a no. So I could do, I could use this wildcard. The percent. And this would it tells sequel is that it pretty much select everything that starts with JO and then it's followed by any other string of characters. So when I do Command Return, I could see that I get all the customers that start with j and 0. I could also use this wildcard in the beginning as well. So I could do everything that starts with whatever has an a, an a followed by an N, and then ends with whatever. So if I do Command Return and I run this query, I see that I get Jane Doe, Simon Letterman, and Mike Hannigan because they all have an a followed by an end. So now let's say we want to work with the not null operator we've talked about before. So when we want to select rows that have a null value or that don't have a null value. We don't use the not equal to sign or the equal to sign, but instead we use the following. Let's say we wanted to select all the names. So select names from customers where age is not null. So I want to see all the ones, all the customers when I run that have the age is not null. So I get a list of all the names. And as you can see, it's a list of ten names. And if we went back here up to browse data, we see that we have 12 customers in total, but Liam and Matt here at the bottom don't have an age and have an age as a null. So they are not selected. If I wanted to select the customers that have a null value, I would do is null. And then I see the Lehman and Matt gets selected. But just remember that this is how we manage null values are how we use null in the select statements, in the worst statements, instead of using the equal or they're not equal sign we use is null. It's not null instead, but there you have it. Those were different ways of different queries that we could use. You can play around with the wildcards, with the text items and attributes that we selected before. You could play around with that and feel free to update your project with the things that you have learned so far. And we'll make sure to chat about that or to talk about that and give feedback in the community section. But I'll see you again here tomorrow and thank you again for watching. 9. Day 6 - Between, And, and Or: Welcome back my friends. If they six. And today we're going to keep looking into different queries that we can write. Another example would be, what if we wanted to take or what if we wanted to select all of our users that are between a different or between a specified age range. So for that, we could do select, let's say I want to select all the columns. So select all from customers. Then in the where clause, we would do where, age, and we would add between literally between, let's say 29.35. That's how you would write that you would do the first number and the second 135. So I do Command Return and I get an error because I misspelled age. But there we have it Command Return. And here we have John Doe and then Jose Gomez, and then we have, those are the ages 29-35. We could also do something similar with the names. So we could say e.g. let's say select the name, where the name is between if we say D and g. So this would select all of the names that start with a D up until the G without including the G on less than name is purely one. G, as in the name is purely one character. Because before when we did 29 to 35, we were including everything up to 35 and including 35. And in this case when we're doing the letters, so we do Command Return and we do a Command Return, we see that we get nothing returned because we have no names on under those characters. But if we look back at our data, let's say m and DOE, let's say m and 0 between M&O, we see that we get all the way from Mary married Matt, Mike and Monica are the way to 0. But if we let say we wanted to select all the way to S with to see if we get assignment included. If we went back here and did S, we see that we do command return. We run the query and we still don't get Simon included because the S is not included. But keep in mind that if this person's name will be only an S, literally just one character, it would get, it would get picked up. Now let's see how we can run more useful where statements. So now let's say we wanted to select all the customers from customers. Now let's say we want to do to wear statements. So we want to select everyone that is younger than 40 years old and has a nationality of the United States. So for that we could do, we could run our where clause as before. And we can say h is less than 40, So everyone younger than 40 years old. And so we can specify the end. Nationality is equal to United States. So if we were to add a semicolon and run this, we could see only John Doe because he is younger than 40 and his from the United States. There's also an operator called the or operator. So if we were to say, come back here again and say OK, select all from customers, where nationality is equal to Italy, or nationality is equal to Jamaica. We hit Command Return. And we see the people that are from Italy and from Jamaica because we use the or operator. And that is also a super useful command to use. Keep in mind that the order by operative and we talked about before, it goes ghost after the where clause. So if we wanted to order this by name, e.g. and descending or something like that. And we could do Command Enter and we can see, okay, It flipped. Now it's Matt Damon are mad diamond at the beginning and Liam Nissan in the second one because we ordered by descending. I named ascending, but that was it for today guys feel free to play around with these new closets and we'd learned because they are so, so much more useful. And e.g. for the word class, when we get to use the AND operator AND the OR operator, there are a lot more useful than before. We also learned how to use the between class and that the order BY clause goes after the where statement. So that was it for today. Thank you all so much for watching. Feel free to update your class projects with the progress that you've made. And I'll see you again here tomorrow. 10. Day 7 - Limit and Case Statements: So welcome back, my friend. And two days seven and today we're going to look a little bit more into query so we can write. And let's start by saying that we want it to select from Customers, but limited to ten users. We don't want to get every single use from cut from our customers table. But we want to get, we would want to get all of them up to ten. So for that we could use the limit statement. So let's say select all from customers, customers, and then we can say limit ten. If I wanted to pick up the limited to ten. So we could do Command Return. And we see from here that we get only ten users returned. We could specify different numbers in here for sure. So we can save five, e.g. and we would get the limit to five or sample of five. So now let's say we wanted to select the top oldest, three people or three customers. So the top three oldest customers. For that we could do select all from customers the same way that we have. We could do order by age, descending, and then we can limit to the first three. And that would give us our top three oldest customers. So Jaime, Mary, and Mike analysis, we can do something really interesting, which is a way that we could do to add some logic to our sequel commands. So let's say that we wanted to categorize our, our customers. So let's say we wanted to add categories to our customers depending on their age. In one to say that everyone under 18 is under age, anyone over 61 years old is a senior. And then anyone in-between is simply in-between. So we could add that category this way. So we can say select name, like this. And then as another column that we wanted to add, we're going to add a case statement, which is sort of like an if statement if you're familiar with programming. So it's a way of selecting different options. And we can do it like this. We can say case. Again, I'm doing, I'm adding all the spacing just to make it more readable. So case, but we can add all of this into one line if we want it to. But I'm just trying to make it more readable. So I can say case when age is less than 18. Then under H, like this, then I say, I can do another when h is greater than 61. Then senior. Then I could do else. If none of those statements are true, then else you could do in-between, In-between like this. And all of our case statements end with an end statement like this. So we can have our case B case when less than 18, under age over 61 than senior. And else it is going to be in-between. So then when I do this from customers and I do, I'm going to expand this a little bit and I'm gonna do Command Return. Then I see that every single customer is categorized with either in-between, either senior or under H, like this, when I see below. And then we can see that the name of the column is really long for that. And here we can use the *** statements. So we can do as, let's do category and do Command Return. And then we can see that the name of the column is now category. And then we see that we have the category for each of the customers that we have. That was it for today. We saw how to use the limit statement. Also the limit statement combined with the order BY clause. And now we use also a case statement so we can have logic in our statements and we use the case statement to categorize different options in our customers. But that was it for today, guys, thank you all so much for watching. Feel free to update your class projects with the things that you've learned so far in your progress. And I'll see you again here tomorrow. 11. Day 8 - Aggregate Functions: Welcome back my friend. And today eight and today we're going to look into aggregate functions in SQL. We're going to start by talking a little bit about comments though. There are two ways that we can do comments. We could do forward slash star and then everything that we enclose between that. So forward slash star and star slash to close, that would make it into a comment. Or you could do two dashes like this are two hyphens like this. That would do a one-line comment as opposed to the multi-line comment that I mentioned before, like this. But that is a good way to add. Let's say, let's say e.g. you want to add a description to the code that you're running. In that way, you can add a description without interfering with the, with the code editor because it would only be, it will not be executed, it will be ignored because that is what a comment is four. So back to aggregate functions, we're going to look into e.g. the count function, also the sum function, the max and Min functions, average. And finally the round function. So these are the aggregate functions that we're going to look into today. And those would add a lot more functionality to the ways that we're writing our queries. And it will give us a lot more information than the one that we were getting before. We start to get a lot more flexible with the things that we can request or the queries that we can run to get data from our database. So let's say e.g. let's say that we wanted to look, select all from customers again, just to examine our table. So we have our ID, we have our name, we have the age, and we have the nationality. So let's say that we wanted to count all the customers that we have that are over 30 years old. So for that, we would do in here, we would do select all the things that we know from our previous lessons, right? So we could do where age is greater than 30. But how do we count it? Well, we hadn't hearing the beginning. We can do count and add whatever you want to count between the brackets in here we want to count simply all the rows that we get returned. If we do command running, we execute this. We see the number seven, which means we have a count of seven customers. And in here again we can do and as claws and say, Let's say that we could say, let's do count. Again, we have seven customers. Let's say that we also wanted to add or we wanted to get the age, the sum of the age of the customers that we had. That data isn't that useful. But in this example is the thing that we can do with the sum aggregate function. We can add the age. We can add in here the sun and say, Okay, what is the DH added? So how many years have our customers lived in total? Again, we can also use the max function. So we can say what is, who is the oldest of our customers? And then we see that the oldest one is 67 years old. We could do the same with the minimum. We get them in here. And that would get us 21 years old is the youngest. Okay, interesting. And we could also do the average. We could do average age as average. And we can see that the average age is 38.9 years old. And now, what if we wanted to round this differently? What if we wanted to round this to two decimals? We could say round the average. We add two statements to around, separated by a comma and then say two with two the number of decimal places that we want that to be rounded. So we get the same result because of that only had one number before. What have we said? Zero in here? It gets rounded up to the, the integer number. So this would be a 39. But there you have it. These are some different ways that you could use and some different aggregate functions that you can use with your queries. You could count, you could some good to get the max or Min, also the average number and you can round that average number as well. So these are some useful functions and you can use for that. So that was it for today. Make sure if you have any questions to post that into the community section and we'll make sure to be helpful. They're also feel free to add any changes to your class project and upload data as well. And if not, thank you all so much for watching and I'll see you again here tomorrow. 12. Day 9 - Aggregate Functions With Grouping: Hello my friends and welcome back. Today is named nine, and we're going to talk about a little bit more about aggregate functions today. So first thing we're going to talk about is how to use group BY. So let's say e.g. we wanted to get the average age, but not for the entire table. But we want it to get the entire age by nationality. So there's a way we could do that. We could do select nationality because that's the first thing that we want to get. And then we say average. And then say age from customers. Then we do group BY down here after the Fromm statement. So group by nationality. And that's how we would do this. And we can also order by nationality if we wanted to do, We do Command Return. And we seen here that we get all the nationalities ordered as ordered by nationality. And then we get the average age for each one of those nationalities. So that is how we use the group by statement or the group by clause. We could also, if we wanted to, we could also group by a calculated field. So e.g. we could do, let's say select. We're going to see round age ten. And then we're gonna do count names. We want to get the count of the names from customers. Group, grouped by the same statement as above, right? Age. And then we're also going to order by this around age ten, right? So we do Command Enter, we get an error because I missed a comma there, so we get the round age field and then we also have another column with a count of all the users. So this would be a useful way to see e.g. how many users are in each decade. So how many users are in their 20s or 30s and 40s and so on. Cbo also has a way to make our lives a lot easier in a way that we can actually referenced in the group BY, by the column that we're stating as a number. So we can say e.g. group by one, and that will reference this first statement up here. And then I could do the same reference down here of the same group by, and I would get the same reference down here. So I could do Command Return and that would result in the same query as before. And then finally, let's go back to our previous query that we had. So let's say we wanted to group by or count the names of the users in each nationality. So we could do count name, nationality, and then group by one. What if we wanted to select only the nationalities that had a count of greater than one or greater than and equal to one. So the intuitive thing to do would be something like this, right? Do a where clause that had discount somehow greater than one. But the thing is that with aggregate functions, we cannot use a where clause. We need to use a having having statement or having clause. In this way that we could say having the account name greater than or equal to two, e.g. this one I do command run. We can see that we get off the nationalities and all the countries with more than, with two or more users inside of them, or as a count of two or more users inside of. So instead of using the word class, we use the having whenever we're using an aggregate function to do that comparison. But that was it for today, guys, you saw today how you could use the group by clause. How you can reference by numbers, using different numbers, you can reference the columns so you don't have to type everything out. And you also learned how to use the having clause. Thank you all so much for watching. Feel free to update the class project with the progress that you've made. And I'll see you again here on the next lesson. 13. Day 10 - Inner Join Tables: Welcome friends to day ten. And today we're going to talk about multiple tables and joins more specifically. Because it turns out that to store data more efficiently, we might spread related information across different tables. Instead of having one unmanageable table with all the data, we organize our records across multiple tables. So as you see here, we have maybe the users table in this example. This has multiple tables. So this would be a relational database with other tables, e.g. this ratings table would have a reference to a user ID. Instead of having all the information in one table, we spread out the information across multiple tables with the data spread out, we have to match the IDs between tables. And doing this kind of matching is called joining tables. So when we match this ID, the user ID to the user ID and a rating stable. Here we're doing a matching and that is what is called joining tables in SQL. So heading back now to the database browser, we are going to add two more tables. So we're going to go to our supporting material. The one that we used to add the data into the customer's column. We're going to copy this information below, right, for it. We're going to copy the orders table, drop table orders, create table orders and insert into orders of this data. And then we're also going to add the same for subscriptions. So we're going to create table subscription, insert into sufficient. And there we have that information as well. So we're going to copy this, go back to our database browser. And I'm just going to go over this really quickly. So it's drop table if exists. This command is telling SQL to drop the table orders if, if it exists, in the case of if it exists, then down here we're creating the table orders with an ID, subscription ID, Customer ID, start month and end month. And notice that the ID is an integer which is a primary key that will be joining. Next we have the data that we're going to be inserting into that table, into orders. Next we have Create Table subscriptions with an ID that is going to be also an integer. And the primary key, we're going to have a description. And then we're going to insert into the table subscriptions three rows of data. So I'm going to go ahead and do Command Return. And I ran this. And next I can feel free to delete the code that I wrote. And I could go back to browse data, right? And here we have our table customers, and this drop-down list lets us change the table. So if we want to examine our orders table, we can see that we have an id column with the primary key. Next we have a subscription ID, then we have a customer ID, and then we have a start and an end month. And these months would be the months of the year. So this one, e.g. this row, the first row, would have the starting month being January, and then January, February, March, April, the end month being April, and that for the rest of the records as well. And as you can notice, this column and this column, so subscription ID and Customer ID, they're referencing IDs in other tables. So e.g. if I wanted to customer and I see the ID in here, and back to orders. When I see the customer ID of one, e.g. I. Know that this row this customer would be customer one in here. So Jane Doe. And in the orders, I know that this is Jane Doe who made disorder and has the Start month being April and then June with the end month and has the subscription ID of two. So if I went over to the table subscriptions, I know that the idea of two, which is this one, is a Gold subscription. So I know that Jane Doe ordered Jane Doe which has customer one ordered Gold subscription, which is subscription id2. And these are the months, so April, all the way to June. And that is how we relate the information between the different tables. So now let's go ahead and go to Execute SQL section. So we can write queries where we can be joining that information directly using SQL. So to do this, what we do is that we do select, just as before. We're going to select everything. So select all from, we're going to select all from orders. And we are going to join this. So we joined the information between the tables. So we do the join clause and we're going to join the customer, Customers table. And we say to specify whether we're joining on. So we're going to say orders, customer customer ID. So this is the notation, right, the table, and then use a dot and then. Costumer IDs. So the field that you're referencing from that column. So orders customer ID and then customers customer ID. And actually in here, this customer ID in the table customer is called just simply playing the ID. So when I do this, I could see down here that I get a join of both tables. So I have the full Orders table and then I have the join with the customers that we mentioned before. So e.g. for this first order we have that it is in fact or the second-order. It is in fact Jane Doe from these months and we see that it is in fact her and we get the information joint. Remember to use this dot notation so we're making sure that the reference is on ambiguous. When we do a join like this, we're doing what is called an inner join. So it is an inner join, which means that the fields, all of the records in one, in the left column or in the left table. This is what is called the left table. The first day what you mentioned needs to be included or needs to be present in the customers table, in the second table or in the table on the right that you're referencing. So notice that the table on the left of the fields are present in the table on the right. And all the ones on the right are referenced or are present in all the fields from the table on the left. So if we look at this diagram, e.g. we can see that this would be an inner join. And here it specifies inner join specifically, but you can simply say join, and it implies that it's an inner join. And it'll take all the fields that are in both tables like this. But going back to our DB browser, Let's see how we can do a different joints. So we can do select or a different inner join. We can see a select. We're going to be selecting from orders. So I can say orders. I want to see the ID of the order. The order ID. Then I want to see customer, from customers. I want to see the name of the customer. And then from subscriptions, I want to see the description of this as a subscription. So now I have from, from orders and I need to join all the tables. Folks will join. First of all, customers. And I have to specify on orders customer ID equal to customers ID. I can also join. I can do another joint and I can say Orders, join subscriptions. On orders. Subscription ID equal to subscriptions ID. And now when I do Command Return, I see that I get the idea of the order. So the order ID, and I get the name because this is where I specified up here in the columns that I wanted to see. I get the name and I also get the description of the subscription that they got. And this is an example of how we could join all three tables. So orders, customers and subscriptions, all by ID. And here is where you specify that information. Now let's do a reminder of the count option that we have before, the count aggregate functions. So we wanted to count, let's see if we can count everything from the table of customers. So we see that we have 12 customers when we run this, we see we have 12 customers. If we do the same with orders, we see that we have nine orders. But now let's say we wanted to count everything from the customers table, customer stable. And we want to join, join orders on costumers. Id, equal to orders customer ID. So now we see, okay, we do have, we have the same number of order, so each customer, or let's say that when we join the costumers with orders, we see we have nine rows in total. So that makes sense because, because it is an inner join, right? And there's another thing that we can do that we can do aliases for the tables. So if we add some texts after the table name. So if we say e.g. in here, see for customers and after orders we add an OH, then we can reference our tables with these aliases. So e.g. this would be customers, 0 would be orders. Next, we can run this and we would see that we could get the same expected results because we're using table aliases and this is way easier to read and way easier to write. So it is more convenient and that is something we can use as well. But that was today's lesson. Thank you all so much for watching. Remember that inner joins as what we saw today. And inner join sits when you join two tables and you can join more tables and you get only the fields that are shared between the tables that you are joining. So thank you again for watching and I'll see you again here on the next lesson. 14. Day 11 - Left Join Tables: Hello friends and welcome to day 11. Today we're going to look into multiple tables and left joins specifically. So yesterday we saw, we talked about inner joints, which are only the fields that are both breasts and so Brett present in both tables are joined. Now we're going to look into this type of joint, which is a left join, which is a type of joint that we do whenever we want to keep all the records from the left table, even the ones that are not matched to the table on the right. So let's see how we can do that in sequence. So let's say we want to select everything. So select all from our customers table. We're gonna give it an alias of a C. So customers seek table. And we're going to do a left join to a table orders. I'm going to give it an alias of an 0. And we're going to say on CID equal Order Customer ID. And we're going to do Command Return. And let's see what we get in the return. We see that we have pretty much the entire customer's table. So from 123456 all the way to 12, all of the customers. Then to the right, we have the table that we joined. And notice how there are some records such as this one and all these three, e.g. and then Matt diamond. So we see that all of these fields have the right table with all null values. This pretty much means that there weren't any items or any records present in the right table that we could join to the left table. So we simply get null values and return. So this is a great way to see e.g. if we had the question of okay, how many customers are, which customers have not made any orders or have not placed any orders. So here we can say in the very same query that we just wrote, we can say, we can add a where clause and say where order ID is not. And then when we run this, we see that we get the same four fields that we pointed out before, which are the four customers that haven't made any orders, haven't placed any orders. So there you have it. That is how we use left joins. And notice that every single time that we have made a joint, we have done it using the primary keys of the different tables. So in this example, where we're using the primary key of customer, which has referenced also in the Orders table. And when this key or when a primary key is referenced in a different table, it is called a foreign key in the orders table. So when we go to the bow section, we have our orders table. We see that the customer ID is here so we can, the primary key of the customers table is present in this table. Here, this column is called a foreign key. And when we go to the customer stable the id, it's what's called the primary key. So again, we use primary keys to match the tables and to join the tables. And again, the rules for primary keys is that they must be unique. A table can't have more than one primary key column and they can't be null. And what I mentioned before was that when a primary key for one table appears on a different table, it's called a foreign key. And again, this is important because most common types of joins will be joining on a foreign key from one table with the primary key from another table. So there you have it. We have talked about energy joints on a previous lesson and then today we've talked about left joins and we saw how those work and how they relate to primary keys and foreign keys. But thank you all so much for watching and I'll see you again here on our next lesson. 15. Day 12 - Cross Join Tables: Hello friends and welcome back to day 12. And today we're going to talk about again, multiple tables and more specifically a cross join. When we talk about cross joints, we're talking about joints that match every row in every possible combination. It does not require an odd statement since you're not joining on any columns. So e.g. if you want it to do a cross join, let's say select all from customers. And then we're going to say cross join subscriptions. And again, we don't need to specify in on statement or a non-class because we're not joining on any specific column, we're simply getting everything, every possible combination returned. So when I run this, I would see in the execution below, we would see that we have every single customer with every possible combination with the subscriptions column. So we have Jane Doe three times because we have Jane Doe with basic golden premium and then Jack Johnson again with basic gold and premium and so on. And so we would get a total of 36 columns because 36th row, sorry, Because we had of customers in total and 12 times three would be 36. So we're pretty much getting every single combination of the rows between the two tables. So this is going to be useful in very specific cases. And e.g. one question that we can answer is, how many months or how many customers were subscribed on each month of the year. And for that, we're going to go into our supporting material and we're going to get our month stable. So we're gonna do the drop table of exists, create a table month, and then we'll simply get inserted month values were simply get a list of the numbers 1-12. So when we copy this and we paste that into our SQL editor, we're going to run this. And when we go to our browser data, we see that we have a month stable with all the months of the year 1-12. So going back to our execution editor, we're going to run a query that we're going to see select, we're going to say select each month on the unstable, and then we're going to count all of the rows, we're going to say as users. And this will be from orders, give it an alias of an 0. And we're going to do the cross join, and we're going to cross join the table months. So now we're getting we're getting everything from orders and we're going to cross join it with the table, months. And we're going to say where orders started, month is less or equal to month. In the month stable and the end month is greater than or equal to the month of that table. So here we're getting pretty much were for filtering out all of the months that are outside. When we go back to this date range in orders, we see that we have a start and an end month. And we're pretty much filtering out of the ones that are not within that date range. And we're also going to move this by group by one. And when we do Command Enter, we're going to see that we have every single month of the year. So from one all the way to 12 down here. So from one up here all the way to 12 down here each month of the year. And then we see how many users were subscribed or had an active subscription in those dates so or those months. So in months six, e.g. we would have for users with a subscription, active or inactive subscription. We see that on April we have six active subscriptions and so on. So that is an example of how we could use a cross join that we need to compare pretty much every single row of the orders to every single row of months. Another thing that we'll learn today is that to stack one dataset on top of the other, we use the union clause in here. This what it does is it appends data of two sets of data. So e.g. if I did a select, let's say one record and then a union another record and do command run. We can see that we get two rows, those rows appended because they're both, they both have the same number of columns. They need to have the same number of columns and the same datatype. In this example, we only have one column and each select statement and they're both text. Another example could be that if we wanted to select all from customers where ID is less than or equal to three, let's say, then we could union that with a select. Again, all from customers where we're ID is greater than or equal to eight, e.g. and now we could run this and we see that both results get stacked one on top of the other. So that is how you can use the union statement. So again, today we saw how we could do cross joins, and we saw how we could use the union statement and that was it for today. Thank you all so much for watching and I'll see you again here on our next lesson. 16. Day 13 - Temporary Tables: So welcome back my friends to day 13. And today we're going to talk about temporary tables. We can use temporary tables which are created with a width clause. And we'll see how that works in a second. And essentially we add an entire query inside the parentheses and give it a name and reference the results that we get. So let's make an example query. Let's say that we wanted to select Customer ID, and let's say customer ID. And we want to get the count, count the subscription ID as number of subscriptions. And we're gonna do from orders, the orders table, and we're going to group by one, the first one, the customer ID. So when we run this, we see that we have our customer IDs. So which one of the customers? And we see how many subscriptions each one of them has. We see that pretty much everyone has one subscription except the customer ID, which has two subscriptions. Now let's say that we wanted to turn this into a temporary table because there's going to be useful for the second table while we want to do, because we want to get the customer names from this execution. So we could do something like this. We could do with, this is the temp name, whatever name you want to put to the temporary table. And then you do As. And between the brackets. You can put in whatever query execution you would like someone to do that in here, the one I just did. And now the results of this temporary execution or from this query that I put in here are going to be placed into this temp name table. So now I can reference this temp name as if it were any other table. So now I could say e.g. it's to select customer's name. And then I can say temp name subscriptions from temp name. And then I can join this timetable to customers on temp name customer ID equal to customers ID. Then when I run this, I get an error because I had a semicolon and my timetable which shouldn't be there. And when I run this again, I get that every single name, every name in here is now shown with the number of subscriptions or the subscription count. Again, let's review what we have just done. So we have our temp table in here, which can be pretty much any execution up here. So you wouldn't add a semicolon at the end of this execution. And then you can reference this temp name or this temp table, which you can give it whatever name you want. You can reference it as a table. And in here we can even add, let's say, aliases. So we can do c and d, we can reference this table is same, you can treat it as another, simply another table. So we can do aliases down here and we could do run and we can see that it would execute successfully as well. And also if you wanted to add more temp tables, you can do this notation. So you've got a comma to the end of the first one that he has just made. And then you can add another one As, and then just specify everything between the parentheses and say e.g. select. I don't know. Let's say Tony and then subscriptions, I don't know, say five as subscriptions like this. And then down here I could do a union select all from the second table. Another one that I just created like this. When I run this, I get C. Okay, I just did the union of another temp table that I created. So you can see Tony down here at the end. And number of subscriptions five, because that is what the temp table had inside. So another one, this second timetable. So you can use this notation to add multiple timetables, but everything within or the use of this width clause. But there you have it. That is how you can use temporary tables and sequence, and that was it for today. Thank you all so much for watching. Feel free to update your class project with everything you've learned so far in the progress you've made. And I'll see you back here tomorrow. 17. Day 14 - Conclusion: So thank you all so much for watching and congratulations for finishing this class. Most people don't finish the things that they start and you just did. So that means that you're an outlier. Throughout this class, you'll learn to use different SQL commands to pull exactly the data you want from your database. We started off by creating our own local database with SQL light. Then we learned how we can manage our tables. Next, we looked into different queries. We can run also aggregate functions, multiple tables with joins and finally temporary tables. If there's one thing I hope you take from this class is not only how to write sequel to get the data you want, but also that consistency is powerful when it comes to learning something new. So always remember to keep coming back when you're learning something new, but with nothing else to add. Thank you all so much for watching. If you enjoyed this class, please feel free to leave a review, follow my channel, and check out my other courses that helps me make a lot better content for you guys. Also don't forget to upload your class project gallery so we can all learn from each other and we can give each other feedback. And finally, also feel free to check out my YouTube channel through the link in my profile. But again, thank you all so much for watching and I'll see you around.