Kyle here from Web have simplified.

Today I'm gonna be going through all the exercises inside of my learned sequel.

Get Hub repositories.

So if you haven't already completed these exercises, pause the video.

Now click the link in the description and make sure you go through all these exercises before going to this video.

I have all of the exercise is time stamped in the description for where I solved them and where I explain them.

So make sure you check out the ones you get stuck on.

Let's get started.

The first exercise is to create a song's table, which is going to work very your similarly to how we created that albums and the band's table inside of our learned sequel in 60 Minutes video.

We're going to start by writing out Create Table with the Name of the table, which is going to be songs and then inside of parentheses.

We're going to put the different columns that we want.

In our case, we want an I D calm, which is going to be our primary key, so we'll make it not know imager, and we'll make it auto increment.

Our next comb is going to be a name calm, which is just going to be a not an old string.

So use of our chart here will make it to 55 not know next will be the length of the album, and this is going to be a length in minutes.

So since songs could be less than a full minute or more than a full minute or some ranging between, we're gonna need to make this a floating point number.

We're going to use quote as our type here and also not know, since the song has to have some form of mine.

Then we need a way to link this to the album.

So we're going to use an album I d here, which is going to be our foreign key.

And we'll just make this.

I'm not know imager as well.

Next, we need to find our different keys.

So our first key is going to be our primary key, which will be the I.

D column that we created earlier, and then we needed to.

Our foreign key in the foreign key is going to be our album.

I D.

On this foreign key is going to reference come in a reference the album's table on the I D calling here and now if we run this query right here, we have created a song's table for a database, and we can see that we open this up, go down to our record company, open up our tables and refresh.

You see that we have our songs table here, which is exactly what we want.

The next thing we need to do is insert all of the rest of the data for our project.

So let's just open up this data here.

We're going tow copy all of these different insert Rose that we have created, and I could just copy that in to a new sequel.

File.

Paste That and run that.

And that's going to add all the data that we need into our database for our albums, songs and bands that we're going to use for the rest of this exercise Is clothes out of that now that we don't need it anymore?

And over here we now have our second exercise, which is just select only the names of all the different bands, and we need to change the name of the column that's being returned to be band name.

So in order to do this, it's going to be fairly straightforward.

We could just do a simple, select statement, and since we only want the name calm, we could just select the name column and we could tell what we wanted to be called band name.

And then we just say where we want it to come from.

So we want this to come from the band's table, and now if we run that, you see that we get all the different band names returned, which is exactly the same as the results over here inside of the get have Repo.

Now we can move on to the third task here, which just still select the oldest album.

This is a bit more of a complex task, since we need to now do some ordering and some filtering in order to get the album that is the oldest but also make sure we don't return any albums that don't have a release you, because there are some albums in our database that don't have a release here.

So first we need to get our select statement written.

We just want us like everything from this album, so we'll say, Select star from albums.

And then we wanted to say where the release year is not know, because we don't want to return an album that doesn't have a release year.

So where are these share is not?

No.

And we need to order these by the release year so we can get the oldest one.

So what are by really share?

And then, since we only want to get one result back, which is used limit one in order to limit our results that toe one.

And if you run that, you see that we now get the oldest album with a really sure of 1988.

If we, for example, wanted to get the newest album, we just change our order by to be descending instead of a sending.

And if we run that you not see that we get the newest album, which is from 2018.

Her next exercise that we need to complete is to get all of the names of the bands that have albums released, and we just want to get their name and nothing else.

So, like it says in the solution, there are multiple ways to solve this problem, but I'm gonna show you the simplest and smallest a solution that we can use.

The first thing we need to do is set up our select on the band's table, so we're going to select.

And since we know we want to get the name from the band's table will say bands not name, and we know we need to call it a band name as our title and then from the band's table.

It's right here.

We're getting all the different band names, but we only want the band names where this band has albums so we can use our inner join that we know about on bands.

We could do it on fans Die Idea equals albums dot band idea.

So this is going to get all the different albums that have a band I D.

That's the same as the band idea that stored in the album's table, as in the band's table.

And if we run this, it's only going to return bands that actually have albums since we're doing an inner join instead of a left join and if you run that you see we get all of our different names, but we get all them duplicated and we don't actually want all the names duplicated.

We just want the individual names one at a time.

So in order to fix that, we can just add a distinct here and now we're just going to get all the distinct results.

And as you can see, our results over here, look exactly the same.

Isn't the repositories on the right?

The next problem that we need to solve is to get all the bands that have no albums, which is very similar to the problem that we just solved.

But it's going to be a little bit more complicated.

So what's remove this all start from scratch, so we know we need to do the exact same thing.

To start.

We need to get the band name, and we need to get it as band name, and we want to get this from the band's table, and then we need to do a joint again on albums.

But since we only want the bands with no albums, we need to do a left joint so we can return bands that also don't have any album records.

So we left join.

We want to do this on the album's table.

We want to do where the bands that I d equals the albums that band I D.

As we talked about in the last example.

And then we need to group these by the band I D.

So we can determine which ones have albums and which ones don't.

So where is going to group buy albums?

Stop band I D.

And now all the different albums are going to be grouped into these different aggregates, which we can use our aggregate functions in order to count on in order to determine which ones have albums and which ones don't.

So we can say, having account of albums that I D.

That equals zero.

And this essentially means if we group all the albums by Bandai D.

We want to find all the groups where there's absolutely nothing in the group forgiven band, and in that case, that would be the bands that have no albums.

So if we run this, you see that the results we get back we get the band name of Dream Theater because of the only band in our database that has absolutely no albums assigned to them.

So far, these last five problems have been pretty straightforward.

But this 61 is going to get a little bit more complicated than the last few.

As it says, we need to get the longest album in our entire database, and we want to return the name of that album the year that album is released and the duration of that album.

So to get started, we know that we're going to need to be selecting things from the album table.

So it's all the information we're returning is based on the album, so we want to do a select and we want to get the album's name so albums dot name and we need to return that as a name.

Then we want to get the really share.

So albums Not really sure we want to turn that has released here.

Next, we need to return the duration of the entire album, which essentially all that saying is, we want to get the length of all the songs so we could just do some of the songs dot length.

So these will be all the songs related to the album, and that will give us the duration of the album, and we could just say as duration and I forgot to put a comma up here, since we have to separate all of our different rose that were selecting from commas.

And there we go.

Now we can say what we want to select from which, in our case, we want us like from the album's table.

Like I said, since his information is dependent upon the album and then we needed to join to the songs table in order to get that duration for the songs so we could do our joint down here joined songs we want to join the songs on albums.

Dot idea equals songs that album idea, just like we would do for the bands.

But this case, it's for different albums and songs, and then we can do our group by on the actual album I D.

So this will get all of the albums for our all the songs.

I'm sorry for a single album Group together, So goodbye songs that album I D.

And now we have all the different groups that we want to order these different groups by the duration that we define earlier so we could just said it's ordered by And since we alias this column has duration.

We could just say duration down here, and it's going to order by the duration.

And since we want the longest one first, we're going to use Descendant, and then women are results toe one, and this is going to return the longest album in our entire list.

But before we run that, we need to look at this error we have up here and it looks like I accidentally added s this joint statement that should be joined instead of joints.

So if we remove that and now we run it, you'll notice that we get the longest album in our entire database.

The year is released and the duration of that album, which is exactly what we want Next we could move on to our other problem, which is going to be updating the release year of the album that does not have a release, sir, in our database.

In order to do this, we first need to figure out which albums in our database don't have a release you, so we could just do a select just like everything we want to select from the album's table.

We want to start where the release year is.

No, so it's going to get us all the roads that don't have any really sure for their albums.

And you'll see that the only album we get back is the master of Puppets album because it hasn't really sure.

So now we need to update that release you, and it says right here that the release your should be 1986 so we can use an update statement.

We need to say we want to update the album's table.

We want to, said the release year.

We want to set it equal to 1986 as the problem says, and the reason we did our select earlier so we could get the I D that we want to set so we can say where I d equals four, because if we don't specify aware cause here, it's going to update every single album in our entire database to be 1986.

But we only want to update this one album.

And now if we run that and we run this query appear again to get all the release years that are no you'll see that we get nothing returned, which is perfect because that means that there are no albums left with no release.

Your so this update statement worked.

Our next exercise is working on inserting data into the database, so we just need to insert our favorite band and album.

And since I already have a lot of my favorite bands and albums in this database, I'm just going to pick something for my Spotify.

But I've been listening to recently and insert that in here.

So the first thing we need to do is we need to write our insert statement for a band because albums references bands so we need to have a band before we can insert our album.

So we'll say, Insert into bands.

We want to insert the name calm because all the other columns air auto generated and we need to get the values that we want to insert.

And in my case, I'm going to insert Van Hagen and we run this command right here.

We're going to get our band inserted and then in order to check that our band was actually inserted and to get the I D that we need to use for inserting her album.

We could just do a select We can select the I D from bands we can say We want to order by I d descending and we only want to get one result pack.

And essentially, what this is going to do is goingto order, are banned by I D and get the one with the highest I D and return it.

In that case, that should be the most recent band that we added since its auto implementing every time.

And if you run that, we get an i d.

Of a back, which means we can use that idea of heat toe link Our album toe are banned to insert our album.

We're going to do the same thing, insert into albums and then we put the calls you want to insert, which will be name released here and the band idea that we just looked up when we told the values that we want to insert.

In our case, it's going to be the album and vector.

It's going to have a release, your 2018 and the band I D is eight, just like we looked up.

Now we can run that statement and that is inserted the band into our table, and we can query up here the album's table instead.

Just get back everything.

And if you clear that, you see that our album was quickly inserted with the correct band I D, which is exactly what we want now looking at the ninth Statement here for exercises, this is going to be to delete the band and the album that we just added, and we can do this really easily, and we need to just keep in mind that we have to delete the album first, since the album references the band, and if we try to delete the band first, it'll now be referencing something that doesn't exist, and it'll throw in error.