Learn SQL In 60 Minutes

Hello everyone I hope you are having a good day. My name is Kyle and this is simplified web development where we make the web easy to understand and accessible to everyone. In this video I will go over everything you need to know. about the sequel so I can do about 95% of the things that you'll need to do with the sequel, it's going to be a long video because I have quite a bit to cover so I'll start by talking about what the sequel is. and why it's important to you and then for most of this video I'm going to go over everything you need to know about the sequel, all the syntax, all the language and everything you'll use when you're Using Sequel in your daily life and Sequel It looks a lot like CSS and is very simple to understand, use and

learn

, but the complexity of using it and the different things you can do with it is what makes it difficult and difficult to master, which is why I have included a list of exercises in the description.

I have a github repository that will have a bunch of exercises with their solutions and results so you can work on those exercises after watching this video to get a better understanding of how the sequel works and how to use it. Next week I'll go over solutions for all those different questions I have in the repository, so be sure to stick around to watch the video next week as well, which will be linked at the end of this video if you're watching after it's released. I will also be creating additional videos related 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 most difficult topics in the sequel, so let me know in the comments below which topics you find most confusing in this video so I can make sure I spend more time and videos making them easier. To understand it, so without further ado, let's begin, we must first talk about what the sequel is and fortunately, it is quite simple. Sequel, which stands for Structured Query Language, is essentially a language designed to create, read, update, and delete data. Most database management systems and virtually any relational database management system will use Sequel as the basis for accessing your data to create, read, update, and delete, so essentially when you

learn

Sequel you will be able to interface with virtually any management system. of relational databases using their The background of the sequel and each relational database management system will handle specific higher- or lower-level tasks that are used much less frequently, sometimes individually, but everything defined in the sequel is universal across all different database management systems and now before we can jump into To learn why sequel is so important, we first need to talk and understand what a database is and how it works.

Basically, a database is just a collection of data separated into different tables, and these tables are individual data models. so you can have a users table, you can have a products table, you can have an orders table and all these tables will be linked together to create connections between the different data so that you have one table containing data for a single model inside your relationship and then inside 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 equal one record or row inside your table and two users There will be two separate records or rows in that table and then all the properties of that user such as their id name, email password will be columns within your database and essentially it is just a table with columns and rows that represent your different records and the different properties of those records and then the different ways the data relates to each other is how it becomes a relational database system where you can link data from different tables to data like and other tables and This is how you can create a complex data design system that uses databases.

It's simple and essentially you just have to think of it as a collection of different tables that represent different objects within your data. Now let's quickly talk about why it is important to learn the sequel. It's pretty easy to see why it's so important. and that's because the sequel deals with data and data is everywhere, almost every app you use, whether it's on your phone, whether it's on your computer on the internet, has some kind of data that you need to save somewhere. place and databases using sequel are one of the best and easiest way to store data for any small scale or especially for large scale applications that's why databases are used absolutely everywhere during development in any form and that's why it's so crucial to learn the sequel because as a developer it doesn't matter what. which you work on, you will eventually find a sequel and you will have to work with databases and knowing the sequel at a solid level will help you significantly in your development career now that we understand why the sequel is so important and what is the sequel, let's jump to my sequel workbench in order. to demonstrate the syntax of sequel and then talk about the different commands we can use with sequel to create, read, update and delete our data.

If you haven't downloaded my sequel workbench yet, I recommend you watch my latest video, which I link 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 to be able to move forward. I now have my sequel workbench open and connected to my local sequel server, but I don't have any files open so I can run sequel commands. In order to do that, I need to click on this icon in the top left corner that allows me to create a new sequel tab to run queries when I click on that.

It will open a file for me where I can start writing the sequel, so now let's talk a little about the syntax of the sequel, which fortunately is very simple to understand. There are different keywords in the sequel, such as the select from where keyword and everything. Of these different keywords, they will most likely be highlighted in blue if you are using my sequel workbench and if you are using some other editor, they will be highlighted in a specific color so that you can distinguish your keywords. of your non-keywords and these keywords are not case sensitive, so for example this is selected.

I have it here. I could write it like this. Select. I could write everything in lower case. I could write it in capital letters just by throwing it out at random. It is not like this. It matters, as long as you type the word select it will work since your select keyword for sequel and sequel is using a combination of keywords, table names and column names to chain a different query, so e.g. we could say just select from and then you put the name of the table that you want to select and you 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, Now you need to put a semicolon, this is not necessary in all database management systems, but if you want to write two different sequel queries to one file, you need a semicolon to separate them, so I recommend always ending with a semicolon at the end of your sequel statement.

No matter what you are doing, although keywords can be written in both upper and lower case, for any other combination of upper or lower case, it is almost always good practice and standard to write all keywords in upper case so you can distinguish them. of your column names and table names, which will probably be in lowercase rather than uppercase format. Also, if you need to write a string inside our sequel, use single quotes and then put the string inside those single quotes to distinguish that it has a string instead of some form of keyword or some form of table or column name, like so So now that we've got that out of the way, let's start creating a database to use in this example.

I'm going to create a database for a record company that will have albums and songs from bands in it for you to work with. Now that we have the syntax out of the way, let's create a database for a record company that will contain tables. for bands and albums, so to start we need to create this database because we don't have any database yet on our sequel server that we created, so let's delete all of this and we'll type the create database command. This command is very simple, just type the words create and then database and then the name of the database you want to create and in our case we are just going to create a database called test, so we just put test and finish with a semicolon. and then inside my super server, there are two different ways you can run a command.

There's a lightning bolt icon on the left here that just runs absolutely everything inside this file here or it will run whatever you highlight, so if you want to just run some commands, you can highlight them and then click this great one next to n bolt 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. is the method I usually use to run sequel commands within the sequel server, so if we just click on this icon here we'll create a database called test and you'll notice that nothing actually happens, it doesn't look like anything was 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 to the schema section, this is where your databases are listed; if you click refresh, it will actually populate our database down here. tests and if we open it you will see that we have an empty test database with no tables or anything else inside it and that is exactly what we want, since we used to create a database to create that database, but since we don't we are creating a database called test, we actually want our database to be about something else, let's drop this database to do that, we will use the drop database command and then we will just put the database name back that we want to delete later, which is test in our case ends with a semicolon and if we execute that statement you will see that here on the left our test database has been deleted and if you update it you will see that it will not appear again and that will be because we deleted that database. all the tables inside it and all the data inside it and now this is something that you are almost never going to use because deleting a database deletes all the data inside that database and once you have your data in You'll almost never want to completely destroy your database, so this is a command you'll almost never use, but it's good to know it exists in case you accidentally create a database you don't actually want - in our case, that That's exactly what we did.

So now it was to create the actual database that we want, which as I mentioned before, it will be for a record company, so create the database, put the name of our database which will be the record company here and if we run it and we refresh here now you see we have data from a record company and we can start adding tables to this database and start adding data into those tables now so that our subsequent queries that we're running here on 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 simply use the use command, so we type use and then the name of the database that we want to run our queries.

So in this case, we just want to run them on the record company database that we just created and if we hit that to run, now we're using the record company database and you'll see it's in bold here in my bank of sequel job, which indicates tells us that we are now using that database, so when we run commands like create tables or add data, it will add it and create it in the record company database; otherwise it won't know which database we want to run these commands on. Now we can work on creating our first table.

This will be done in a very similar way to how the database was created, but instead of using the database, we will simply use create table and then the name of the table we want. to create, so we are just making a test table to start here and now, as I mentioned earlier, tables have columns inside that represent the different properties of the object they represent, so when we create our table we musttell him what columns we want to create with, so we create parentheses and inside these parentheses we are going to place the different columns that we want for our table, so, for example, in this table we are only going to add one column.

We will 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 is storing, for example, will it be a string? is it an integer? is it a date? a floating point number that We need to tell our database what type of data it contains, so in our case we will use int and again since this is a keyword. I like to keep everything uppercase to distinguish it from my column and table names, and I don't need to end any of this with a semicolon because the three lines here are a single sequel command, so we just want to put the semicolon in the sequel at the end of that command and this inside here is not actually in the command. so we don't want to end up with a semicolon, otherwise we'll get it now, if we put the cursor inside this command, click to run it and if we update our outline here on the left, you'll see that we now have a little drop-down menu next to it. to our tables and we have a test table and inside that table and our columns we have our test column, which is an integer type and that's great, but let's say we want to add another column to our table, we forgot to add it at the beginning and I don't want to go back and change this creation table because we already have data there and if we recreate the table we are going to lose that data, so we have a command called alter table that will allow us to change the properties of our table. after creating it we just write a modify table and then the name of the table that we want to modify in our example it will be the test table and then we tell it what we want to do so let's tell it we want to add and we want to add a column, so we're going to put the name of the column here, we're just going to call it another column and then we're going to say what type we want that column to be and in our case, We're going to use a string which then there are a lot of different ways to determine a string, but the easiest way is to use a VAR character which essentially says it's a variable length array of characters which is essentially what a string is and then we need to say the varchar' is the maximum length it can be, so in In our case we will simply say that 255 is the maximum length our string will have, so this will create a string column with a maximum length of 255 that will be called another column. and then we'll end with a semicolon and you'll notice that I've created a line break here.

I added this to another line and the sequel doesn't actually care about the line breaks in the statement, it just reads it until it sees this semicolon, so you could put as many line breaks here as you wanted and it would still work fine even if you did. had on multiple lines or all on one line, so now if we run that, we update our schema. Let's see now we have another column added to our test database columns and that's great, now we 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 now that I had this test table and we don't actually want it, let's see how to delete that table, which works exactly the same as in a database, we just go in here, type the dropdown table and then give the table a name and if we run it , you will see that our test table is completely removed from the tables section in the record company schema now that we have removed that table that we don't actually want, let's work on adding a table for a band that we will use within our database data from our record company, since we want to represent different bands for our record company, so let's create a table and we're going to call our table bands because it's going to contain all of our bands again, we want our parentheses to say what our columns are going to be inside of here and In our case our bands will only have one name, the only thing we really care about is the name of the band, so we will use name as the name of our column again.

We want this to be a VAR character and we'll just say 255 again for the duration and we never want a band to not have a name so to make sure our band always has a name we'll add not null to our column so just put not null here and this says that our column can no longer have null values ​​inside it which means it should always have a name defined and this is a great way to force your table to have different values ​​defined and it throws an error if you try to insert a band that doesn't have a name and now you may think 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 do it? To distinguish these two bands from each other is that we are using an ID column which is useful in almost every table you create within a database, you will want to add an ID column to uniquely identify that row in that table of all the other rows within that table, so up here OSHA adds a column that we'll call ID. We want this to just be an integer because it's an easy way to distinguish different things. It can be one, two, three, four and we can easily. distinguish them from each other, we never want this to be null again just like the name and since this ID is something that will distinguish our records, we don't actually want to add this ID when we insert records, we want it to be automatically. is generated every time we add a new band to our table so we'll give it the auto increment property and this just tells the ribbon that we want to automatically increment this ID every time we add a new band so that the first band is a . the second band will be two and three and four and so on and it will constantly automatically increment this number for us without us having to do anything.

One last thing we need to do: we need to add a column between our ID and the name column. so that Sequel knows that these are two separate columns and we use that comma to separate them just like we use semicommas, semicolons, sorry, to separate our different Sequel commands instead of our file and lastly, since this ID will be the identifier of our table is what is called a primary key and a primary key is the primary identification column for that table and that is what you used to say this is unique and it will be what will identify an individual record within a table so we want to tell you below that our ID column is our primary key, so down here we will use the primary key keyword to define a primary key and then in parentheses we put what our primary key is and in our case it is the ID column now, if we run this command and update our schema, you will see that we now have the Bands table within that bands table, we have these different columns and you will also see that we have an index for our primary key which indicates sequel that this is what which distinguishes our band from the other band records within our table, allowing you to do quick queries if we give it an id, it will be much faster than if we query for example the name column within our band table .

While in the case of creating tables, let's create the albums table which will contain the different albums for our different bands within our database, so that we can use a creating table again. We will call this table albums since it will contain our albums. and then inside here we're going to put our parentheses, we're going to put the semicolon at the end 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 I'm going to say that ID makes it an integer, not an OL again as we don't want this to be empty and auto incremented last so that it automatically takes care of holding this number as we add new albums below that we want our album.

We have a name again, so it will look very similar to our band on top. We want to create a VAR car. We're going to do it 255 just because it's the same as everything else and again, we don't want this to ever happen. will be empty so we'll say not null so each album will have a name and the last thing our albums will have is we want to know when they were released so we'll add a release year to our To add this release year column we'll convert it in an integer and we don't care if it is null because maybe we don't know when the album will be released or maybe it hasn't been announced when this album will be released. is going to be released, so we want this to stay, no, so we won't put "don't know" 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 here and we can't put all the band information inside the album, we need to reference the bands table from inside our albums table and that's where this ID that we created here comes in handy because now we can save that ID in the albums table and that .

It will allow us to reference the bands table from the albums table, so here let's add a band ID column, which is the one that will have the ID of the band this album is for, so it will be an integer because it's the Same thing as this ID up here and we want to make sure that it's not no because we never want an album to not have a band because every album has to be made up of some band and then we have to define our primary key. As we did above, we'll put in the primary key and then the ID, since the ID is also our primary key for the albums table and then the last thing we need to do is make this band ID reference this band table to the that is referenced. as a foreign key, which is any form of key that references a table other than itself, so albums have the primary key because that is the key to finding the album records from each other, the uniqueness and identification of band refer to the band table, so it is a foreign key that refers to a foreign table, so we need to define that relationship between the band ID and the band table.

To do this, we'll use the foreign key property and then in parentheses we want to put what our foreign key is. is stripe ID so so far it's very similar to the primary key but we need to tell our foreign key which table it references so we'll say it references the stripe table and then we need to tell it which column it references reference and it is referencing the ID column within that bands table and now we have our foreign key set between our albums and our bands so that sequel will no longer allow us to create an album if we give it a band ID that doesn't exist yet . in the bands table also, if we try to delete a band that has albums linked to that band, it will throw an error that an album exists for this band, so you can't delete the band unless you also delete the albums. that goes with that band, so before we run this I need to fix a mistake that I made and that's where we reference in our table, we call our table bands, so we need to make sure that we reference that table exactly by name where it should will be bands instead of band which I accidentally wrote and now if we run this code, you will see that if we go to our tables and refresh, now we will have this albums table and inside this albums table. from our foreign keys, you'll see that we have a foreign key that links our albums to our bands table.

We also have all the columns we've created and the index for our primary key and the stripe id, allowing us to do quick lookups of these different columns within our database now that we've finally created all the tables we needed, we can start working on adding data to our tables and querying that day from our tables because that's really what the sequel is for, it's to add data and read that data, so let's start by inserting some bands inside our bands table, let's go down here a small waist, we have a little bit of space and what we want to do is insert them into the bands table and we want to provide the different values ​​for the bands that we want to insert, so we will use the insert into command and then it will place the name of the table in the that you want to insert data and in our case that will be the band table and then after that you have to put all the different columns that you want to insert in parentheses and in our case we only have one which is the name column instead of our band table, as I mentioned earlier, the ID column within our band.

The table is automatically generated, so we don't need to enter this when we add data to our table, so we insert it into our bands, let's goto insert a name and then we must put the different values ​​that we want to insert. so we use the keyword value and then in parentheses we are going to put the name of the band since it corresponds with this name column that we defined, so let's add Iron Maiden to our database, add the semicolon at the end and if we run this command, you will see that nothing happens, but we actually added that band to our database, so let's add some more bands to our database and then let's start querying these different bands, so another way to insert data into the database is Let's use this command insert into again and then we will put the band name or the table name, sorry what is the band of the columns again and then if we want to put more than one entry inside our table at the same time instead of having to rewrite all the different striped inserts, name all those things over and over again, all we have to do is put in the different columns that we want, so here let's say we want to enter the band Deuce and then if we want to enter another band, what What we do is put a comma and then inside more parentheses we put the column values ​​for the next entry, so we will put Avenged Sevenfold and then lastly we will put the band anchor but the semicolon at the end and now if I run this statement, it will add three different bands to our bands table and give all of them a unique id that will automatically increment on its own, so if we run that command and now we have the four bands inside our bands table and we can start querying the data from our table so to query the data from our table we will use the Select command so just type select and then the second thing you want write are the different columns. which you want to select but if you want to select each column all you need to do is put a star and then I will select each column of your aquarium and then we need to tell you which table we want to query from so I want to query from the band table now if we are done that with a semicolon and we run it, you will see that we did our different results, you will see that we have our ID column, our name column and then the four names that we entered, as well as the four unique IDs that were automatically generated by the database. data from our auto increment that we created when we created our table and that's great, but what if, for example, we only wanted to retrieve two bands? instead of all the different bands to do that, we would just do this selection exactly the same as before, we're going to select between bands, but we need to tell it a limit, so we just say limit.

I must say that we only want two bands. so this will only give us the first two bands from our query and if we run that, now you will see that we only get Iron Maiden induces what are the first two bands returned by this query, we can also get only certain columns instead of getting all the calls for to do that we are going to do the same but instead of putting a star here we are just going to put the name of the column that we want to obtain, which in our case we only want the name of the column, so we will say select name of the bands and, if we execute, now we only get the name column that is returned and we no longer get the id column from the band table.

Another interesting thing you can do is rename the columns in to make it easier to read or use within your program, so let's write our select statement again and say we want to select the ID but we want to rename how you see our ID so we'll say alias the name as something else and we'll say we want it returned as ID all in uppercase and then we can do the same thing with 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 typed as band name and then we just say we'll get them from bands and we'll end up with a semicolon and if we run that you'll see that the titles of our different columns have changed to be the same as what we wrote here for our aliases for our column names, these aliases are really useful because you can also reference your 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 the sequel, the last thing I want to talk about with the Select statement before we continue is that you can actually sort the way elements are rendered instead of your Select statement, so let's write another selection here we're just going to select everything from the band table again and then let's say we want to sort them by name, we can just stay sorted and then we write why we want to sort them and in our case it would be you want to sort by the name column, like this which if we run this you will see that now instead of being sorted by ID, they are sorted alphabetically from the name that we provided, but if we wanted to reverse that order, we could simply do that. in descending order instead of ascending order so we type descending at the end here run this and now they are in reverse alphabetical order and by default descending is set for a submission that gave us alphabetical order the first time but if just leave this off, the default order will be ascending which is what we had originally done and as you can see when we run it it sorts them in ascending order which is what this order by property does great now that we've learned a lot of the different ways we can select data from our table.

Let's add some albums to our albums table next, so we need to use our embed, like we've talked about before. Enter the title of the table we want. to insert it into which albums and then all the different columns that we want to insert it into, we want to insert it into the name column, the release year column and the band ID so we can link the album ID to a specific album. 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 statement, so in parentheses here we see that our first column is the name column, like this that we are going to put the name of our album, we are just going to be the number of the beast and then we are going to put the year of release of that album, which will 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 band ID and then we're going to put a comma and go down to the next line. just to make this easier to read and we will add in our next statement that it will be another Iron Maiden album called Power Slave really sure from that 1984 and again a band ID of 1 and we can enter another album this is Nightmare will be released in 2018 and it's by Deuce, so we'll use ID 2 here which corresponds to Deuce for the band and again another album which is also called Nightmare, this one was released in 2010 and this one is by Avenged.

Seven times, so we'll use the ID of 3 and lastly we'll add one last album, this one we'll call the test album and it won't have a release date because we don't know when. was actually released and we're going to say this was an album released by Avenged Sevenfold and we're going to end it with a semicolon and now if we run this, we've actually added all of those different albums to our albums table and we can select them. just using the Select statement that we talked about earlier so we can say select star from albums and if we run that, you'll see that we have all of our different albums returned here with the release year and the band ID that they correspond to. and if you look at the test album you'll see that this is null for the release year because it doesn't actually have a release year since we don't actually provide a year, which is exactly what we want, as I mentioned before.

We can select just the name from our table so we can select the name of the albums and if we run this we will get all the names of the different albums within our database. You'll notice Knightmare appear. twice because that album there are actually two nightmare albums instead of our database, but for this query we only want, for example, 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. Let's go back twice, so to get only unique rows from our database, we simply 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 within our database and this different line. all it does is say whatever is returned, which in our case is just the name, it compares them and if any of them are the same it just removes all but one of them so you only get a single row for each element inside from your database instead of getting duplicates if for example in our case we have two nightmare albums now it will 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 the wrong release year for Beast's album number, which I did on purpose.

I promise you that I'm not terrible at knowing the years and to change that we need to use the update query inside the sequel, so if we come down here just update and we need to put the name of the table that we want to update, so in our case we want update the albums table and then we need to tell our table what we want to update, so we'll say we want. to set the release year and in our case the release year of this album should be 1982 instead of 1985 and then if we run this right now it will update each album within our table to have the release year 1982 which It's definitely not what we want, all we want to do is update the release year of a single record within 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 all sequel queries to filter the actual results that the painter returned and be able to say where.

We put what we want a query, so we'll use the ID column, but we want to say where the ID is equal to one. We want to do this query, so we want to update the albums table by setting the release year to 1982 for each record. where the ID is equal to one, which in our case will just be a single record and now if we run this, you'll notice that nothing seems to happen, but if we create our albums table again, you'll see that the release year number of Beast has updated to 1982 from the original five 1982s that we inserted into our table, this update method is incredibly useful for any data changes within your database, which is something that will happen all the time within an application, as I mentioned here.

The statement can be added to the end of several different statements and one of the cases where it is used all the time is when you want to filter what you are selecting from the database. In our case, we have just been selecting each row in the table. but this is usually not what you want to do. If you want to select all albums that were released before the year 2000, we could write our select statement, we'll just select all columns or my table albums and then just say our where statement so we can say where is the release year, in 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 one release. year that occurred before the year 2000 and the where clause has so many different shapes that you can filter by and it's incredibly useful.

Another way we can filter is we can filter on the string using wildcards to filter where the string contains certain parts of Let me write this down because it's much easier to explain by looking at it rather than trying to talk about it, so we'll select from the table of albums or the name and we want to say where it is, so it's just going to be similar to the string that we give it, where the name is and then inside these quotes, whatever we put is what the string will be compared to, so if we put a percentage sign that says that everything within this percentage area can be absolutely anything, it can be no characters, as many characters as you want, it doesn't matter, so let's put the ER percentage and then another %, whichever It says we want any number of characters anywhere before this and then the letters er somewhere in order. inside the chain and thenany number of characters after that within our force, but the semicolon at the end and we'll run it and you'll see that we get the results for the number of the beast and the power slave and that's because if you look at their name, they have ER inside your name somewhere and a certain number of characters before and a certain number of characters after which is what these two percent signs equal.

It can be a bit confusing how that % works but essentially just think about it as it can be anything that doesn't really matter so really this is just checking for ER somewhere within the string and you can also combine different clauses Where inside your single query, so in our case, if we wanted to select where the name has ER inside or where the band ID is equal to two, we could just say or band ID is equal to two and now this will check this cause here or this Clause here and it will return both sets of results, so If we run this, you'll see that we get our ER results here and we get the Knightmare album because it was released by ban number two based on the ID of this band that we we query here in the clause or we could also do 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 it to be just for band ID one and if we run this , check for the release year of 1984 and a band id of 1 and it will only return rows that have both statements working, so if we run that you will see that we get the album powerslave because it has a release year of 1984 and was released by bang number one now is just two quicker ways that I want to talk about how you can use the where statement and that is if you want to filter between two different values, so if we want to select from the albums and we want to filter or the release year, the year of release and we want to say where is the release year between two different numbers, so we want to get all the albums between 2000 and 2018, so we just use the keyword between, put the initial value minimum value and then put the maximum value that we want to filter between and if we run this, you'll see that we only get albums released between 2000 and 2008.

The last thing we can do with the where statement is we can filter on things. that is not so, in our case we can say that the release year is null and this will return us all the records within our albums table that do not have a release year set and if we run this you will see that we get this. Test album because it's the only album in our database but it doesn't have a release, you're set up for it and we don't really want this test album because it was a mistake, we didn't actually want this data, we should probably delete it. then we can use the delete command which we do correctly on delete, which is very similar to our selection.

We put the name of the table which in our cases is albums and then if we run this right now it will delete every row from our albums table which we definitely don't want to do so we need to add a where clause to indicate where we want to delete, for which in our case we are just going to say where ID is equal to 5 as that is the ID of our test album as we can see down here and if we run this you will see that nothing seems to happen but if we try to select all of our albums we will just let's do that real quick, select all of our albums and run, you'll see that that fifth id is no longer here because we deleted it from our database now, with the last delete statement written, we've talked about the There are four main ways that Sequel It's used to interact with data: creating it, reading it, updating it, deleting it, and this is really the cornerstone of everything you need to do with Sequel, but there are quite a few more really unique and really powerful features that you can use.

I'm going to use all the time in the sequel that I want to talk about and the first one is the join statement which allows us to join two different tables on different properties, so in our case we create this band ID column so we can join . our band ID to our bands table to query the different albums of our different bands or the different bands of our different albums and it really allows us to create powerful queries that allow us to create relationships between our data within our database, which is What makes the sequel so powerful?

So let's start by simply making the most basic bonding statement across our different albums and bands. If we go down here, we just need to do a basic selection to start, so we say we want to select everything from the band. table and then we tell the band table that we want to join it in the albums table, then we will say join followed by the name of the table we want to join, which is the albums table and then we need to tell Sequel how I want to join these tables because all the join does is check a query to say if these things are essentially the same, so we say we want to query the albums table for when is the band point ID, so we are querying this table and then we want to get the ID. column of that table, so we only get our band ID, we want to check when it is equal to the band ID of our albums table, so we say band ID of the albums table column and we are just comparing the different values.

Inside these rows together to see when they are the same, we want to select them from our bands and our albums, we put a semicolon and run, you will see that we get all of our band information as well as all of our album. Information is returned for all bands that have albums within 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 within our table, as you can see what exactly join does is it simply joins those two in the band id column and the id column of the Bands table and then it shows the output in band information and album information because when we say select star it is selecting everything from the bands table and In 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 with that one band, so it lists the band twice and then the albums of each one in the section where the album is located. is listed and there are several different ways you can join in sequel 3 specifically for my sequel and that is to use the inner join which is exactly what the basic join does so you can write an inner join like this or this union and if we execute it, We will see that we obtain exactly the same results.

There is also a left join and a right join, so an easy way to understand how these different joins work is an inner join that combines data where there is a value in the left table, which in our case is the table on the left. band, is whatever table you write first, since the table is on the left and the table on the right is the albums table, which is the table you are joining to; it only returns values ​​that 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.

The left join will allow us to have all the bands that do not have any albums also appear because the left join lists everything from the left. side, which is the first table you list from here, the band table will list all of those tables even if they don't match on albums, so let's copy this here and do a left join instead of an inner join, so if we change the inside so that it is on the left and we run that, you will see that now we get the result here for the anchor band and you will see that there is actually no album associated with this band, but it is being returned because We are doing a left join that joins with this statement here and it says that even if there is nothing it's fine because we join on the left so we will return everything on the left side no matter what and then if we wanted to do a right join it will join on the right side , so if there is an album without a band associated with it, it will still return that album, but in our case, as you can see, there are no albums that do not have any band, so in order.

To make it look like this joins on the right, if we swap albums up to here and bands up to here, now the right side of our table is this bands table and if we run this you'll see that we now have this anchor band here again. because it's on the right side of our query so just in a join statement and even though it doesn't have anything on the left side, which is this section of the album, it's still returned because we're doing a right join instead of a left join or an inner join, so for the most part you'll just use inner joins and a left join because right joins are essentially the same as a left join just reversed, which makes it a little more confusing to reason in your mind, so that inner joins are really useful when you just want to retrieve records when there is a value in the left table you are selecting from and the right table you are joining two and left joins are really useful when you just want to get absolutely everything you are selecting from the left side table and then just get the things from the right side table if they exist and if not they still return the things from the left side even if you have nothing on the right side, these are two very common queries that you will use all the time in the sequel because the whole point of using a relational database system is that you can have relationships in your database, it's literally in the name and that's what these join statements do.

I allow you to do is create a data query based on these different relationships. Now we finally get to the last topic that I want to talk about in this video, which is aggregate functions and groupings in your different sequel declaration, let's start first. talking about aggregate functions because they are very simple to understand, let's look at some ways where we will simply create a selection and instead of selecting an individual column from our table, we will use an aggregate function to to select our aggregate from our data, so we want to select the average, so we're going to use the average here AVG and inside here we're going to put the name of the column that we want to average over, so we're going to average the release year because we want to find out what the average release is.

It has all the data in our database to determine if we like new music or old music, so we'll select between albums and if we run this, you'll notice that we only get one single. The row comes back to us because it averages all the rows instead of the albums table and returns the average release year, which in our case is nineteen ninety-eight point five, so this aggregate simply takes all the data returned by Select and then run a function on In this case we are using the average function, but there are many other functions, like some, if we wanted to add all the release years for some reason, we could run it and you will see that we get seven thousand nine hundred ninety-four and there are tons. of different aggregate functions, but some of the most useful ones are average sum and count, so now we're going to 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 get all the bands and we want to know how many albums each of them has these bands, but to do that we need to group by that band ID because as we know within our albums table, if a band has two albums, there will be two records within that. table that has exactly the same band ID and what group by does is take all the records and groups them into a single column within that table, so in our case it is the band ID, so we will take all the rows with the same band ID and group them into a single row and then this aggregate function will run on those different groups of our rows so we just need to place the groups to tell us why we want to group and we place the column which in our case is band id and if we run this you will see that for band id one we have two records within the albums table band id - we have one and band id3 we also have one and the reason why this aggregate function here returns multiple rows instead of just one like you did here it is because the aggregates work on the actual group as a whole rather than the entire query and since we are grouping by band ID we have three unique band IDs so we have three separate groups that our account is running alone, if that doesn't make sense, we have a few more examples that I'm going toreview, we can look a little more in depth at how we can combine group by and joins to make some really complex and really useful queries within the sequel, also within the exercises that I have listed in the repository below, there are many different examples that will do it for you. they will slowly help you create complex group and join statements which would be really helpful in helping you solve Go out and learn exactly how these groups work because in my opinion they are the most complicated part of learning the sequel so this last query I have What is Band ID 1?

What band is it? I want to know what band it is instead of just looking at an ID, so we should join this. table in the band table to give us information about the band, so the first thing we want to do is essentially copy what we've already created, but we want to select the band name and you'll see it in a moment. I'm using B instead of the actual table name, so say the name of point B and we want to return this as the band name and we also want to get the count of the different albums because that's essentially what this query does here, so Let's use count will be a point ID and again I'll explain a little bit why I'm doing this and we'll return this as the number of albums and then we want to select this from the bands table for example. of bands and also let's alias bands so we can say like 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 band table that allows us to easily shorten this bands table so that writing our queries is a little simpler, so we will do a left join because in this case we want to get the bands even if they don't have any albums because it just returns 0 as the number of albums it has that band, so we'll do a left join on the albums and we'll call the albums a so we can reference them like I did up here and then we'll just say we want. to join it in the band table, the ID is equal to the album table and the ID and then the last thing we need to do is group by that band ID again to get those different aggregates that we can add so that I'll say group by band ID van 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 similar to all of these statements that we've talked about. up here, but we've grouped it all into one, so I'm going to walk you through it a little bit to make it a little bit easier to understand, so the first thing we've done is talk about the columns that we want to select and we've given them an alias so that are easier to see down here like the band name and album number rather than knowing the name of point B and counting a point ID which doesn't really tell us anything, so that's what we have.

After these different column names, we've also done a join on the albums table with the left join, which means we're even getting the bands that don't have albums like we did up here, if I run this right here, you'll see we're getting the bands that don't even have any album records, which is what we want when we run this query here because when we count the album IDs we'll get 0, which you can see happened for the presenter. here and lastly we group them by band ID so we can have unique rows for the different band IDs and all of that is put together, but since the name is exactly the same where the ID is the same, that's why we can Select this band name here and the album ID count is what will tell us how many unique albums there are within that different grouping by band ID and that's how we can get these different columns for the band names D . and the number of albums, and I understand that this is quite confusing and you really just need to work on different problems to fully understand it.

It is very difficult to explain and listen to, which is why I have included those exercises that I highly recommend you watch. The last thing I want to talk about is 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 a single album, so you would think that everything we do would just be included here and we would say that the number of albums is equal to one, but this won't actually work because the declarations happen before the group buys, so we can't actually query for the number of albums because since the add happens after the group buy, in the sequel we have to use what is called the have statement and have is exactly the same as where, but it happens after the group buy, so you can use aggregate function data instead of having it here. we can put the number of albums 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 album aliases because we defined it here. like aida id count this is exactly how we want to query the aggregated data it has to happen inside a have which should be after the group and if we still want to use where we can do it by simply saying where We can check where for example the name equals deuce and if we create that now we will only get bands that have the name deuce and that have more than one album, which is exactly what we want and as you can see we get a -a record down here which is perfect now , as I mentioned, it's very complicated to understand, so if you don't understand it completely, don't be discouraged, go to the description, check out that repository and check out the different examples.

I have there they start really simple, where you just select basic data and insert basic data and it will get more and more complex until you progress to creating large queries like this to query complex joins and group by data from the different tables, so I recommend you check it out and tell me what you think. I'm going to create a solution video for all the different problems within that worksheet coming out next week, so be sure to stay tuned. for that and that's all I had to talk about when it comes to the sequel so if you enjoyed this video be sure to leave a like below and subscribe for more similar content like this in the future thank you so much to everyone seeing you have a good day