Learn SQL In 60 Minutes

hello everyone I hope you're having a good day my name is Kyle and this is web dev simplified where we make the web easy to understand and accessible for everyone in this video I'm going to be going over everything that you need to know about sequel in order to do about 95% of the stuff that you'll ever need to do with sequel it's going to be a long video because I have quite a bit to cover so I'm going to start by talking about what sequel is and why it's important for you and then for the majority of this video I'm going to go over everything that you need to know about sequel all of the syntax all of the language and everything that you're going to use when you're using sequel in your day-to-day life and sequel is a lot like CSS and that it's very simple to understand and use and learn but the complexity of actually using it and the different things you can do with it is what makes it difficult and hard to master that's why I've included a list of exercises in the description I have a github repo that'll have a bunch of exercises with their solutions and the results so that you can work through those exercises after watching this video to get a better understanding of how sequel works and how to use it then coming up next week I'm going to go over the solutions for all those different questions that I have in the repo so make sure you stick around for the video next week as well which will be linked at the end of this video if you're watching it after it's out also I'm going to be creating additional videos relating to topics that are more difficult to understand as we go through this so in the coming weeks and months I'm going to have additional videos going over the more difficult topics of sequel so let me know in the comments down below which topics you find the most confusing from this video so that I can make sure to dedicate extra time and videos to making those easier to understand for you so without any further ado let's get started to get started we first need to talk about what sequel is and is luckily fairly straightforward sequel which stands for structured query language is essentially just a language that's designed for creating reading updating and deleting data from databases and pretty much any relational database management system will use sequel as the baseline for how it accesses its data for creating reading updating and deleting so essentially when you learn sequel you're able to interact with pretty much any relational database management system using your sequel background and each relational database management system will handle higher level or lower level specific tasks that are used much less often in sometimes individual ways but everything defined in sequel is Universal between all of the different database management systems and now before we can actually jump into learning why sequel is so important we first need to talk about and understand what a database is and kind of how they work a little bit essentially a database is just a collection of data and separated out into different tables and these tables are individual models of data so you may have a user table you may have a products table you may have an orders table and all these tables will be linking to each other in order to create connections between the different data so then you have a table which contains data for a single model inside of your relation and then inside of that table you have different columns and different rows the rows are the different records of your individual models so if you have one user that will equate to one record or row inside of your table and two users would be two separate records or rows in that table and then all of the properties of that user such as their ID name email password those are all going to be columns inside of your database and essentially it's just a table with columns and rows that represent your different records and the different properties of those records and then the different ways that the data is related to each other is how it becomes a relational database system where you can link data from different tables to data as and other tables and that's how you can create a complex data layout system using databases there straightforward and essentially just have to think about it as a collection of different tables that represent different objects inside of your data now let's quickly talk about why it's important to learn sequel it's fairly straightforward as to why it's so important and that's because sequel deals with data and data is everywhere almost every application that you use whether it's on your phone whether it's on your computer on the internet it has some form of data that it needs to save somewhere and databases which use sequel are one of the greatest and easiest ways to store data for any small scale or especially for large scale applications which is why you see databases being used absolutely everywhere across development in any form and this is why it's so crucial to learn sequel because as a developer no matter what you work on you will eventually encounter sequel and have to work with databases and knowing sequel at a strong level will help you significantly in your development career now that we understand why sequel is so important and what sequel is let's jump into my sequel workbench in order to demonstrate the syntax of sequel and then talk about the different commands that we can use with sequel in order to create read update and delete our data if you haven't already downloaded my sequel workbench I recommend checking out my last video which I'll link up in the top corner and in the description below that will tell you how to download my sequel server and my sequel workbench on your computer in order to follow along I now have my sequel workbench open and connected to my local my sequel server but I don't have any files open for me to be able to run sequel commands in order to do that I need to click this icon in the top left corner here that allow me to create a new sequel tab for executing queries when I click on that it'll open up a file for me that I can start writing sequel inside of so now let's talk a little bit about the sequel syntax which is luckily really straightforward to understand there are different keywords in sequel such as the keyword select where from and all of these different keywords they'll highlight in blue most likely for you if you're using my sequel workbench and if you're using some form of other editor they're going to highlight in a specific color so that you can distinguish your keywords from your non keywords and these keywords are not at all case sensitive so for example this is selected I have here I could write it like this select I could write all lowercase I could write it with capital letters just randomly throwing throughout it it doesn't matter as long as you write the word select it is going to work as your select keyword for sequel and sequel is using a combination of keywords table names and column names in order to string together a different query so for example we could just say select from and then you put the table name that you want to select from and select the column here and essentially this is a sequel command they have a bunch of different keywords you have different table names different column names and then at the end of your sequel command you need to put a semicolon now this is not required in every single database management system but if you wanted to write two different sequel queries in one file you need semicolons to separate them so I recommend always ending a semicolon to the end of your sequel statement no matter what you're doing also even though the keywords can be written in all caps all lowercase for any other combination of uppercase or lowercase it is almost always best practice and the standard to write all of your keywords in full uppercase in order to distinguish them from your column names and table names which will most likely be in lowercase format as opposed to uppercase also if you need to write a string inside our sequel you use single quotes and then put the string inside of those single quotes to distinguish that you have a string instead of some form of keyword or some form of table or column name so now that we have that out of the way let's get started with actually creating a database for us to use in this example I'm going to create a database for a record company which will have bands albums and songs inside of it for you to be able to work with now that we've got the syntax out of the way we're going to create a database for a record company which is going to contain tables for bands and albums so to get started we need to create this database because we don't actually have any databases in our sequel server that we've created yet so let's remove all of this and we're going to write the create database command this command is super straightforward just write the words create and then database and then the name of the database that you want to create and in our case we're just going to create a database called test so we just put test end it with a semicolon and then inside of my super server there's two different ways that you can run a command there's this lightning bolt icon on the left here that just executes absolutely everything inside of this file over here or it'll execute whatever you highlight so if you want to just execute a few commands you can highlight them and then click this excellent by n bolt in order to execute them or there's the second option which is this lightning bolt with the cursor and then I'll just execute whatever statement your cursor is inside of which is the method I usually use for running sequel commands inside of sequel server so if we just click on this icon right here we'll create a database called test and you'll notice nothing actually happens it doesn't look like anything is created and that's because my sequel workbench doesn't actually update the UI when you create new things very quickly so if you go over to the schema section this is just where your databases are listed if you click refresh it'll actually populate our database down here of tests and if we open that up you see that we have an empty test database with no tables or anything else inside of it and that's exactly what we want since we use to create database to create that database but since we're not creating a database called test we actually want our database to be about something else let's remove this database to do that we're going to use the drop database command and then we just put the name of the database again that we want to drop afterwards which is test in our case end it with a semicolon and if we run that statement you see that over here on the left our test database has been removed and if you refresh it you see it won't pop back up and that's because we've dropped that database all of the tables inside of it and all of the data inside of it and now this is something that you are almost never going to use because dropping a database deletes all of the data inside of that database and once you have your data in your database you almost never want to completely destroy it so this is a command that you'll almost never use but it's good to know that it exists in case you accidentally create a database that you don't actually want in our case that's exactly what we did so now it was create the actual database that we want which as I mentioned earlier is going to be for a record company so create database put the name of our database which is going to be record company in here and if we run that and refresh down here you now see that we have a record company data and we can start adding tables to this database and start adding data into those tables now in order to make it so that our sequel queries that we're running over here in our file actually run against the database that we just created we need to tell sequel that we are going to use that database and to do that we just use the use command so we type in use and then the name of the database that we want to run our queries on so in this case we just want to run them on the record company database that we just created and if we just hit that to execute we are now using the record company database and you see that it is bolded over here in my sequel workbench which tells us that we are now using that database so when we run commands such as creating tables or adding data it'll add it and create it on the record company database otherwise it won't actually know what database we want to run these commands on so now we can work on creating our first table this is going to be done in a very similar fashion to how create database was done but instead of using database we're just going to use create table and then the name of the table that we want to create so we're just doing to do a test table to start here and now as I mentioned earlier tables have columns inside of them that represent the different properties of the object that it's representing so when we create our table we need to tell it what columns we want to create with so we create parentheses and inside of these parentheses we're going to put the different columns that we want for our table so for example in this table we're just going to add one column I'm just gonna call it test column and then we need to give that column a type because our database needs to know what type of data it's storing for example is it going to be a string is it an integer is it a date a floating-point number we need to tell our database what type of data that holds so in our case we'll just use int and again since this is a keyword I like to keep it all uppercase in order to distinguish it from my column names and table names and we don't need to end any of this with a semicolon because all of this three lines right here is a single sequel command so we just want to put the sequel the semicolon at the end of that command and this inside of here is not actually in command so we don't want to end it with a semicolon otherwise we're going to get so now if we put our cursor inside of this command click to execute it and if we refresh our schema over here on the left you'll see that we now have a little drop down by our tables and we have a test table and inside of that table and our columns we have our test column which is a type of integer and that's awesome but let's say we want to add another column to our table we forgot to add it in the beginning and we don't want to go back and change this create table because we already have data in there and if we recreate the table we're going to lose that data so we have a command called alter table which will allow us to change properties of our table after we create it so we just type an alter table and then the name of the table that we want to alter from in our example it's going to be the test table and then we tell it what we want to do so we're going to tell it we want to add and we want to add a column so we're gonna put the column name here we're just gonna call it another column and then we say what type we want that column to be and in our case we're going to use a string which in sequel there's many different ways to determine a string but the easiest way is using a VAR char which essentially says this is a variable length character array which is essentially just what a string is and then we need to tell the varchar' the maximum length that it can be so in our case we'll just say 255 is the maximum length that our string will be so this will create a string column with a max length of 255 that is going to be named another column and then we'll end that with a semicolon and you'll notice that I've created a line break in here I added this on to another line and sequel actually doesn't care about line breaks in the statement it just reads it until it sees this semicolon so I could put as many line breaks in here as I wanted to and it would still work just fine even if I had it on multiple lines or all on one line so now if we run that refresh our schema you'll see that we now have that another column added to our columns of our test database and that's great we now know how to create database tables and we know how to add columns to those tables after we've created them and that's perfect so now since we had this test table and we don't actually want it let's look at dropping that table which works exactly the same as in a database we just go in here type in drop table and then name the table and if we run that you'll see that our test table completely removes itself from the tables section in the schema of the record company now that we've removed that table that we don't actually want let's work on adding a table for a band that we're going to use inside of our record company database since we want to represent different bands for our record company so let's right create table and we're going to call our table bands because it's going to just hold all of our bands again we want our parentheses in order to say what our columns are going to be inside of here and in our case our bands are just going to have a name that's all we really care about is the name of the band so we'll use name as our column name again we want this to be a VAR char and we'll just say 255 again for the length and we never want a band to not have a name so to make sure our band always has a name we're going to add not null to our column so we just put not null here and this says that our column can no longer have any null values inside of it which means it must always have a name defined and this is a great way in order to force your table to have different values defined and it'll throw an error if you try to insert a band that does not have a name and now you may think that that's all we need to do to create a band but what if a band has the same name as another band that can happen how do we distinguish these two bands from each other and that's we're using an ID column comes in handy in almost every table that you create inside of a database you're going to want to add an ID column in order to uniquely identify that row in that table from all the other rows inside that table so up here OSHA's add a column we'll call it ID we want this to just be an integer because that's an easy way to distinguish different things it can be one two three four and we easily can distinguish them from one another we never want this to be null again just like the name and since this ID is something that is going to distinguish our records we don't actually want to add this ID when we insert records we want it to be automatically generated whenever we add a new band to our table so we're going to give it the auto increment property and this just tells the tape that we want to automatically increment this ID every time we add a new band so the first band will be one the second band will be two and three and four and so on and it will constantly auto increment this number for us without us having to do anything one last thing that we need to do we need to add a column in between our ID and name column so that sequel knows that these are two separate columns and we use that comma to separate them just like we used semi commas semicolons sorry in order to separate our different sequel commands instead of our file and lastly since this ID is going to be the identifier of our table it is what's called a primary key and a primary key is the primary identifying column for that table and that's what you used to say that this is unique and it is going to be what identifies an individual record inside of a table so we want to tell sequel that our ID column is our primary key so down here we're going to use the primary key keyword in order to define a primary key and then inside of parentheses we put what our primary key is and in our case it is the ID column now if we run this command and refresh our schema you'll see that we now have the Bands table inside of that bands table we have these different columns and you'll also see that we have an index for our primary key which tells sequel that this is what distinguishes our band from the other band records inside of our table which allows it to do quick queries if we give it an ID it'll be much quicker than if we query on say the name column inside of our band table while around the case of creating tables let's create the album table that's going to contain the different albums for our different bands inside of our database so we could just use a create table again we'll call this table albums since it'll contain our albums and then inside of here we're gonna put our parentheses put the semicolon at the end of it and now we want to define our different columns our first column again we're going to have that ID column to uniquely identify our different albums so we'll say ID make it an integer not an OL again since we don't want this to ever be empty and autoincrement lastly so that it'll automatically take care of Inc maintain this number as we add new albums next we want our album to have a name again so this is going to look very similar to our band at the top we want to create a VAR car we're gonna make it 255 just since it's the same as everything else and again we don't want this to ever be empty so we'll say not null so that every album will have a name and the last thing that our albums are going to have is we want to know when they were released so we're going to add a release year on to our albums order to add this release year column we'll make it an integer and we don't care if this is null because maybe we don't know when the album is going to release or maybe it hasn't been announced when this album will release so we want this to stay no so we won't put not know on here and then we need to be able to connect an album to a band but we can't just put a band column inside of here and we can't put all the band information inside of the album we need to reference the band table from inside of our album table and that's where this ID that we created up here comes in handy because now we can save that ID in the albums table and that'll allow us to reference the band's table from within the albums table so in here let's add a band ID column which is what is going to have the ID of the band that this album is for so this is going to be an integer because it's the same as this ID up here and we want to make sure that it's not no because we don't ever want a album to not have a band since every album needs to be composed by some band and then we need to define our primary key just as we did above so we'll put primary key and then ID since the ID is our primary key for the album table as well and then the very last thing that we need to do is this band ID is referencing this band's table which is referred to as a foreign key which is any form of key that references a table other than itself so albums has the primary key because that is the key to finding the album records from each other the uniqueness and band ID is referencing the band's table so that is a foreign key referencing a foreign table so we need to define that relationship between the band ID and the band table to do this we're going to use the foreign key property and then inside of parentheses we want to put what our foreign key is which is band ID so this is so far very similar to primary key but we need to tell our foreign key what table it references so we're gonna say that it references the band table and then we need to tell it what column it is referencing and it is referencing the ID column inside of that band table and now we have our foreign key set up between our albums and our bands so that sequel will no longer let us create an album if we give it a band ID that doesn't already exist in the band table also if we try to delete a band that has albums linking to that band it'll throw an error saying that you have a albums that exists for this band so you can't delete the band unless you also delete the albums that go with that band so before we run this I need to fix one error that I made and that is where we reference in our table we called our table bands so we need to make sure that we reference that table exactly by name where it should be bands instead of band which I've accidentally written and now if we run this code you'll see that if we come over to our tables and we refresh we're now going to have this albums table and inside of this album table instead of our foreign keys you'll see that we have a foreign key linking our albums to our band table we also have all the columns that we've created and the index for our primary key and band ID which allows us to do quick searches for these different columns inside of our database now that we've finally gotten all the tables that we needed created we can actually start working on adding data to our tables and querying that day both from our tables because that's really what sequel is for is for adding data and reading that data so let's get started by inserting some bands inside of our band table let's go down here a little waist we have a little bit of space and we want to do is we want to insert into the bands table and we want to supply the different values for the bands that we want to insert so we're going to use the insert into command and then you put the table name you want to insert data into and in our case that is going to be the bands table and then after that you need to put all of the different columns that you want to insert into inside of parentheses and in our case we only have one calm which is the name column instead of our band table as I mentioned earlier the ID column inside of our band table automatically generates itself so we don't actually need to enter this when we add data into our table so we insert it into our bands we're going to insert a name and then we need to put the different values that we want to insert so we use the values keyword and then after that in parentheses we're going to put the name of the band since that corresponds with this name column that we defined so let's just add Iron Maiden to our database add the semicolon at the end and if we run this command you'll see that nothing happens but we've actually added that band into our database so let's add a few more bands into our database and then start querying these different bands so another way to insert data into the database is we're going to use this insert into command again and then put the band name or the table name sorry which is bands the columns again values and then if we want to put more than one entry inside of our table at the same time instead of having to rewrite all the different inserts into bands name all that stuff over and over all we have to do is put the different columns that we want so in here let's say we want to enter the band Deuce and then if we wanted to enter another band what we do is put a comma and then inside of more parentheses we put the columns values for the next entry so we'll put in avenged sevenfold and then lastly we'll put in the band anchor but the semicolon at the end and now if we run this statement it'll add three different bands to our band table and it will give all of them a unique ID that will be Auto incremented on its own so if we run that command and now we have all four of those bands inside of our bands table and we can actually start querying the data from our table so to query the data from our table we're going to use the Select command so you just write out select and then the second thing that you want to write is the different columns that you want to select but if you want to select every column all you need to do is put a star and then I'll select every single column that your aquarium and then we need to tell it what table we want to query from so we want to query from the bands table now if we just end that with a semicolon and execute that you'll see that we did our different results you see that we have our ID column our name column and then the four names that we entered as well as four unique IDs that were automatically generated by the database from our auto increment up above that we created when we created our table and that's great but what if for example we only wanted to get two bands back instead of all of the different bands to do that we would just do this select exactly the same as before we're going to select from bands but we need to tell it a limit so we just say limit I must say we just want two bands so this is going to just get us the first two bands from our query and if we run that you now see that we just get Iron Maiden induce which are the first two bands returned by this query we can also get just certain columns instead of getting all the calls so to do that we're going to do the same thing but instead of putting a star here we're just going to put the name of the column we want to get which in our case we just want the name column so we'll say select name from bands and if we run then we now just get the name column being returned and we are no longer getting the ID column from the band's table another nice thing that you can do is you can actually rename the columns in order to be easier to be read or used inside of your program so let's write up our select statement again and let's say we want to select the ID but we want to change the name of how our ID looks so we'll say as to alias the name as something else and we'll say we wanted to be returned as ID all uppercase and then we can do the same thing for the name so if you want to select multiple columns just put a comma between them we'll say we want the name column and we want this to be written as band name and then we just say we're gonna get them from bands and we'll end that with a semicolon and if we run that you'll see that the titles for our different columns have changed to be the same as what we wrote in the as here for our aliases of our column names these aliases are really useful because you can also reference what you alias later in your execution which we'll see when we start talking about more complex uses of the Select statement as well as other statements in sequel the last thing that I want to talk about with the Select statement before we move on is that you can actually order the way the elements instead of your Select statement are rendered so let's write another select here we're just going to select everything from the band's table again and then let's say we want to order them by the name we can just stay order by and then we write what we want to order them by and in our case would you want to order by the name column so if we run this you'll see that now instead of being ordered by ID they're ordered an alphabetical order of the name that we supplied but if we wanted to reverse that order we could just do it in descending order instead of ascending order so we write descending at the end here run this and now they're in reverse alphabetical order and by default descending is set to a sending which is given gave us the alphabetical order the first time but if you just leave this off it'll just default to ascending order which is what we had originally done and as you can see when we run that it orders them in ascending order which is what this order by property is great at doing so now that we've learned about many of the different ways that we can select data from our table let's add in some albums to our albums table down below so we need to use our insert into as we've talked about earlier put title of the table that we want to insert it into which is albums and then all of the different columns that we want to insert into so we want to insert it into the name column the release year column and the band ID so we can link the ID of the album to a specific band that wrote that album and then we want all the different values that we want to insert and we're going to insert a bunch of different elements into our albums table in this one statement so in parentheses here we see that our first column is the name column so we're going to put the name of our album we're just going to be the number of the beast and then we're going to put the release year of that album which is going to be 1985 and then the ID of the band that wrote that album so iron maiden wrote that album their ID is 1 so we're going to put a 1 here as the ID of the band and then we'll put a comma we'll go down to the next line just to make this easier to read and we'll add in our next statement which is going to be another album by Iron Maiden called power slave really sure of that 1984 and again a band ID of 1 and we can enter another album this one is going to be Nightmare released in 2018 and this is by deuce so we're going to use the ID of 2 here which corresponds with deuce for the band and again another album which is also called nightmare this one released in 2010 and this one's by Avenged Sevenfold so we're going to use the ID of 3 and then lastly we're going to add one last album this one we're just gonna call test album it's not going to have a release date because we don't know when this is actually released and we're going to say that this was an album put out by Avenged Sevenfold and end that with a semicolon and now if we run this we've actually added all those different albums to our albums table and we can select them just by using the Select statement that we talked about earlier so we can say select star from albums and if we run that you see that we have all of our different albums being returned down here with the release year and the band ID that they correspond with and if you look at test album you'll see that this is null for the release year because it doesn't actually have a year that released since we didn't actually supply a year which is exactly what we want as I talked about earlier we can select just the name from our out in this table so we can slice select name from albums and if we run this we're going to get all the names of the different albums inside of our database you'll notice that Knightmare shows up twice because that album there's actually two nightmare albums instead of our database but for this query we just for example want to get the name of all the albums in our database but we only want the unique names we don't want to get the same name back twice so in order to get only unique rows from our database we just put distinct instead of our select query here and if we run that you'll see that now we only get the names that are unique inside of our database and this distinct line all it does is say everything that gets returned which in our case is just the name it compares them and if any of them are the same it just removes all of them except for one so you only get one unique row for every single item inside of your database instead of getting duplicates if for example in our case we have two nightmare albums it'll now only return one which is exactly what we want in this case and now if any of you are Iron Maiden fans you may have noticed that I actually put in the wrong release year for the number of the Beast album which I did on purpose I promise you I'm not terrible with knowing the years and in order to change that we need to use the update query inside of sequel so if we come down here just right update and we need to put the table name that we want to update so in our case we want to update the albums table and then we need to tell our table what we want to update so we're gonna say we want to set the release year and in our case the release year for this album should be 1982 instead of 1985 and then if we just run this right now this will update every single album inside of our table to have the release year of 1982 which is definitely not what we want all we want to do is update the release year of a single record inside of our table so let's query all of our albums again by selecting this row up here and we can see that this album has an idea of one that we want to change the release year to 1982 instead of 1985 so if we scroll down here we can use what is called the where statement which can be added to almost every sequel query in order to filter down the actual results painter returned so we can say where we put what we want a query on so we'll use the ID column but we want to say where the ID equals one we want to do this query so we want to update the albums table by setting the release year to 1982 for every record where the ID equals one which will just be one single record in our case and now if we run this you'll notice looks like nothing happens but if we create our albums table again you'll see that the number of the Beast release year has been updated to 1982 from the original 1982 five that we inserted into our table this update method is incredibly useful for whatever your data changes inside of your database which is something that is going to happen all the time inside of an application as I mentioned this where statement can be added to the end of multiple different statements and one of the cases where it's used all the time is when you want to filter what you're selecting from the database in our case we've just been selecting every row in the table but this is normally not what you want to do what if you want to just select all of the albums that were released before the year 2000 we could write our select statement we'll just select all the columns or my table albums and then we just say our where statement so we can say where the release year in our case we want the release year to be before 2000 so we'll just say less than 2000 and if we run this you'll see that it only returns albums with a release year that occurred before the year 2000 and the where clause has so many different ways that you can filter by and it's incredibly useful another way that we can filter is we can filter on the string by using wildcards to filter where the string contains certain parts of it so let me just write this up because it's much easier to explain by looking at it rather than me trying to talk about it so we'll select from the albums table or the name and we want to say where it is like so it's just going to be similar to the string that we give it so where the name is like and then inside of these quotes whatever we put is what is going to compare the string to so if we put a percent sign that says that everything inside of this percent area can be absolutely anything it can be either no characters as many characters you want it doesn't matter so let's put percent ER and then another % so this says we want any amount of characters anywhere before this and then the letters er somewhere in order inside of the string and then any amount of characters after that inside of our strength but the semicolon at the end and we'll run that and you'll see that we get the results for the number of the beast and powerslave and that's because if you look at their name they have ER inside of their name somewhere and some amount of characters before it and some amount of characters after which is what these two percent signs are equal to it can be a little bit confusing how that % works but essentially just think about it as it can be anything it doesn't really matter so really this is just checking if there's ER somewhere inside of the string and you can also combine different where clauses inside of your single query so in our case if we wanted to select where the name has ER inside of it or where the band ID is equal to two we could just say or band ID equals two and now this will check for this cause right here or this Clause over here and return both of those sets of results so if we run this you'll see that we get both of our ER results up here and we get the Knightmare album because that's released by ban number two based on this band ID to which we queried on up here in the or clause we could also make and be a different one so if we wanted to select from our albums again and we wanted to say where the release year is equal to 1984 and we want to make it so it's only for band ID one and if we run this it'll check for the release year of 1984 and a band ID of 1 and it will only return rows that have both of these statements working so if we run that you'll see that we get the powerslave album because it has a release year of 1984 and it was put out by bang number one now they're just two more quick ways that I want to talk how the where statement can be used and that is if you wanted to filter between two different values so if we want to select from the albums and we want to filter or the release year release year and we want to say where the release year is between two different numbers so we want to get all the albums between 2000 and 2018 so we just use the between keyword we put the beginning value the minimum value and and then we put the maximum value that we want to filter between and if we run this you see we get only albums released between 2000 and 2008 een the last thing that we can do with the where statement is we can filter for things that are no so in our case we can say where the release year is null and this will return to us all the records inside of our albums table that don't have a release year set and if we run this you see that we get this test album back because it's the only album in our database but has no release you're set for it and we don't really want this test album because this was a mistake we didn't actually want this data we should probably remove this data so we can use the delete command which we just right by doing delete from which is very similar to our select from we put the table name which in our cases albums and then if we run this right now it'll delete every single row from our albums table which we definitely do not want to do so we need to add a where clause to tell it where we want to delete so in our case we're just going to say where ID is equal to 5 since that is the ID of our test album as we can see down here and if we run this you'll see nothing actually looks like it happens but if we try to just select all of our albums or just do that real quick we select all of our albums and run that you'll see that that 5th ID is no longer here because we deleted it from our database now with that last statement of delete from being written we've talked about the four main ways that sequel is used to interact with data by creating it reading it updating it and deleting it and this is really the cornerstone to everything that you need to do with sequel but there's quite a few more really unique and really powerful features that you're going to use all the time in sequel that I want to talk about and the first one is the join statement which allows us to join two different tables together on different properties so in our case we created this band ID column so we can join our band ID to our band table in order to query the different albums for our different bands or the different bands for our different albums and it really allows us to create powerful queries that allow us to create relations between our data inside of our database which is what makes sequel so powerful so let's get started by just doing the most basic join statement on our different albums and bands so if we go down here we just need to do a basic select to start so we say we want to select everything from the band's table and then we tell the band's table that we want to join it on the albums table so we'll say join followed by the name of the table we want to join on which is the albums table and then we need to tell sequel how we want to join these tables because all join does is check a query to say are these things equal essentially so we say we want to query the albums table on when the band's dot ID so we are querying this table and then we want to get the ID column from that table so we're just getting the ID of our band we want to check when it's equal to the band ID from our albums table so we're saying the albums table column band ID and we're just comparing the different values inside of these rows together to see when they are equal we want to select them from our bands and our albums we put a semicolon and run that you'll see that we get all of our band information being returned as well as all of our album information being returned for all of the bands that have albums inside of our database as you can see this band ID is equal to the band ID in the albums table for all of our different records inside of our table as you can see which is exactly what join is doing it is just joining those two together on the band ID column and the ID column of the Bands table and then it shows output into band information and the album information because when we say in select star is selecting everything from the band's table and the albums table you'll also notice that the Iron Maiden row is actually duplicated here and that's because it has two different albums associated to that single band so it's listing the band twice and then the albums for each one over in the section where the album is listed and there's multiple different ways that you can join in sequel 3 specifically for my sequel and that is using inner join which is exactly what the basic join does so you can either write inner join like this or this join and if we run that you'll see that we get the exact same results there's also a left join and right join so an easy way to understand how these different joins work is an inner join combines data where there is both a value on the table on the Left which in our case is the band's table it's whatever table you write first as the table is on the left and the table on the right is the albums table which is the table you're joining on - it only returns values that have a match so if for example the ID is in the table here on the left and the ID is in the table on the right left join will allow us to have all of the bands that don't have any albums will also show up because left join list everything from the left side which is the first table you list in the from here the band's table it'll list all of those tables even if they don't have a match in albums so let's copy this here and do a left joint instead of an inner join so if we change inner to be left and we run that you'll see that we now get this the result down here for the anchor band and you'll see that there's actually no albums associated with this band at all but it's being returned because we're doing a left join which joins with this from statement right here and it's saying even if there is nothing for it's okay because we're left joining so we're returning everything on the left side no matter what and then if we wanted to do a right join it'll join on the right side so if there is an album with no band associated if would still return that album but in our case as you can see there are no albums that don't have any bands so in order to make it look like this right joins working if we swap albums up to here and bands down to here now the right side of our table is this bands table and if we run this you'll see that now we get this anchor band here again because it's on the right side of our query so just on a joint statement and even though it has nothing on the left side which is this album section is still being returned because we're doing a right joint instead of doing a left join or an inner join so for the most part you're only going to use inner joins and left joint because right joints are essentially the same thing as left join just flipped which makes it a little bit more confusing to reason with in your mind so inner joins are really useful when you only want to get records back when there is both a value in the table on the left which are selecting from and the table on the right which you're joining two and left joins are really useful when you just want to get absolutely everything from the left side table which you're selecting from and then just get the things from the right side table if they exist and if they don't still return the thing from the left side even if it doesn't have anything on the right side these are two very common queries that you're going to use all the time in sequel because the point of using a relational database system is so you can have relations in your database it's literally in the name and that's what these join statements allow you to do is to build a query data based on these different relations now we're finally on to the very last topic that I want to talk about in this video which is aggregate functions and grouping by in your different sequel statement let's first start by talking about aggregate functions because they're very straightforward to understand let's go down here a little ways we're just going to create a select and instead of selecting an individual column from our table we're going to use an aggregate function in order to select us aggregate of our data so we want to select the average so we'll use average here AVG and inside of here we're going to place the column name that we want to average over so we'll average the release year because we want to figure out what the average release you have all the data in our database is to figure out if we like new music or old music so we'll select from albums and if we run this you'll notice we just get one single row turn to us because it's averaging all of the rows instead of her albums table and returning the average release year which in our case is nineteen ninety eight point five so this aggregate just takes all the data returned from the Select and then runs a function on it in this case we're using the average function but there's many other functions such as some if we wanted to add all the release years for some reason we could run that and you see that we get seven thousand nine hundred ninety four and there's tons of different aggregate functions but some of the most useful ones are average sum and account so now we'll take a look at a situation where count would be incredibly useful and this is if we want to use the group by as well so we're going to use our select here now we're going to select the band ID and we're going to select the count of the band ID from our albums table because what we want to do is we want to get all of the bands and we want to figure out how many albums each of these bands have but in order to do that we need to group by that band ID because as we know inside of our albums table if a band has two albums there'll be two records inside of that table that have the exact same band ID and what group by does is it takes all of the records and groups them by a single column inside of that table so in our case it's the band ID so we're going to take all the rows with the same band ID and squish them into one single row and then this aggregate function will run over those different groups of our rows so we just need to put groups by to tell us what we want to group by and we put the column which in our case is band ID and if we run this you'll see that for band ID one we have two records inside the albums table band ID - we have one and band id3 we also have one and the reason that this aggregate function here is returning multiple rows instead of just one like it did up here is because aggregates work on the actual group as a whole instead of the whole entire query and since we're grouping by band ID we have three unique band IDs so we have three separate groups that our count is executing itself upon if that doesn't make sense we have a few more examples that I'm going to go over we can look at a little bit more in depth on how we can combine group by and joins in order to make some really complex and really useful queries inside of sequel also inside of the exercises that I have listed in the repository below there's lots of different examples that will slowly step you up into creating complex group by and join statements which would be really useful in helping you figure out and learn exactly how these group bys work because they are in my opinion the most complicated part of learning sequel so this last query that I have all that's going to do is it's going to take what we have already created up here and it's going to give us a little bit more meaningful information because band ID 1 looking at this doesn't mean anything what is band ID 1 what band is that I want to know what band that is instead of just looking at an ID so we need to join this table on the band table in order to give us information about the band so the first thing that we want to do is essentially copy what we've already created but we want to select the band name and you'll see in a little bit while I'm using B instead of the actual table name so say B dot name and we want to return this as band name and we also want to get the count of the different albums because that's essentially what this query is doing up here so we're going to use count will be a dot ID and again I'll explain why I'm doing this a little bit and we'll return this as the number of albums instead and then we want to select this from the band table so say from bands and we're also going to alias bands so we can say as B and now instead of having to put bands everywhere we can just put B like I did up here B dot name and this B will reference this bands table which allows us to easily shorthand this bands table to make writing our queries a little bit more simple then we're going to do a left join because in this case we want to get the bands even if they don't have any albums because we'll just return 0 as the number of albums that that band has so we'll do a left join on the albums and we'll call albums a so we can reference it as a as I did up here and then we'll just say we want to join it on the bands table ID is equal to the albums table and ID and then the last thing we need to do is we need to group by that band ID again in order to get those different aggregates that we can aggregate over so we'll say group by van ID and if we run this you'll see that iron baton has two albums deuce has one album avenged sevenfold has one and then anchor has zero albums and this works very similarly to all these statements that we've talked about up here but we've grouped it all into one so I'm gonna walk you through it a little bit to make it a little bit easier to understand so the first thing we've done is talked about the columns we want to select and we've alias them so that they're easier to see down here as band name and number of albums as opposed to you know B dot name and count a dot ID that really doesn't tell us anything so that's what we've a leus these different column names we've also done a join on the albums table with the left join which means that we're even getting the bands that have no album records as we did up here if I run this right here you'll see we're getting the bands that even don't have any album records which is what we want when we run this query down here because when we do the count of the album IDs we'll just get 0 which you can see happened for anchor right here and then lastly we're grouping these by the band ID so that we can have unique rows for the different band IDs and all that gets squished together but since the name is exactly the same where the ID is the same that's why we can select this band name up here and the count of the album ID is what's going to tell us how many unique albums there are inside of that different grouping by band ID and that's how we're able to get these different columns for the D band names and the number of albums and I understand that this is quite confusing and it really just takes working through different problems to fully understand this it's really hard to explain and hear about so that's why I've included those exercises that I highly recommend you look at the last thing that I want to talk about is what if we want to filter by the aggregate we want to filter by the number of albums because we only want to return bands that have one single album so you would think all we do is just put aware in here and we'd say we're the number of albums is equal to one but this won't actually work because we're statements happen before the group buy so we can't actually query by the number of albums because as aggregate happens after the group buy so in sequel we have to use what is called the having statement and having is exactly the same as where but it happens after the group buy so you can use aggregate function data instead of having so in here we can put the number of albums set of equal to one and now if we query this you'll see that we only get the bands that have exactly one album and we were able to use this number of albums alias because we defined it up here as the count of aida ID so that's exactly how we want to query on aggregate data it has to happen inside of a having which has to be after the group by and if we still want to use where we can do that by just saying where we can check where the name for example is equal to deuce and if we create that we now are only getting bands that have the name of deuce and that have more than one album which is exactly what we want and as you can see we get that a-one record down here which is perfect now as I mentioned this is very complicated to wrap your head around so if you don't fully get it don't be discouraged go down into the description check out that repository and go through the different examples I have in there they start out really simple where you're just selecting basic data and inserting basic data and it'll get more and more complex until you work your way all the way up to creating large queries like this to query complex join and group by data from the different tables so I highly recommend you check that out and let me know what you think I'm going to be creating solution video for all the different problems inside of that worksheet coming out next week so make sure you stay tuned for that and that's all I had to talk about when it comes to sequel so if you guys enjoyed this video please make sure to leave a like down below and subscribe for more similar content just like this coming in the future thank you guys very much for watching have a good day

Channel: Web Dev Simplified

Views: 417,369

Rating: 4.9719687 out of 5

Keywords: webdevsimplified, learn sql, learn sql in 1 hour, learn sql in 60 minutes, learn sql fast, learn sql for beginners, learn mysql, how to learn sql, how to learn sql quickly, how to learn sql fast, sql for beginners, sql for beginners tutorial, sql for beginners course, free sql course, free sql course for beginners, learn sql queries, learn sql queries step by step, how to write queries in sql, sql tutorial, sql tutorial for beginners, sql explained, sql in one video, sql

Id: p3qvj9hO_Bo

Length: 56min 23sec (3383 seconds)

Published: Sat Nov 17 2018