‫Welcome back.
‫In this video, we are going to create two more tables and we're going to set them up so that we can
‫create our little program that we have seen in the last video.
‫All right.
‫So let's get started by creating a new table here.
‫So I go to my tables in my server explorer under the correct data connection, and I create a new table.
‫So add new table here.
‫And the new table should be called animal because it's going to contain all the different animals that
‫we have.
‫Then as you can see, I change the name here, create table dbo animal.
‫Then we need to add a row here which I'm going to call name and it's going to be of type and var char
‫so and var char 50.
‫So this is going to have 50 characters again.
‫And it's important that this ID here is going to be our primary ID here.
‫So ID specification or identity specification and I'm going to say, is identity true?
‫And you can see it will now automatically increment.
‫So this is the same approach that we've seen with the zoo table, but we do that with the animal table
‫now as well.
‫Once that is done, we can press the update key and update the database.
‫And once that is done, again, we can refresh here and we can see we have an animal table in our tables
‫as well.
‫Now let's add some data to that table.
‫So show data or table data.
‫And in here, I'm just going to add a fish.
‫Actually, I'm going to add a shark.
‫Then a clown.
‫Fish monkey.
‫Wolf gecko.
‫Crocodile.
‫Crocodile dial.
‫Then an owl.
‫A parrot.
‫That's it.
‫So I have these eight names here.
‫You can use different ones.
‫So these are just names of animals.
‫And as you can see, they have an ID as well, which is unique and which auto incremented each time
‫that I entered a value.
‫So now we have those eight animals in here.
‫And what I want to do is now create a combination of animals and zoos so there can be a shark in the
‫zoo of New York, but it can also be a shark in Miami or in Milan or in Berlin or whatever.
‫Then it can be a wolf in Miami, but could also be one in New York.
‫But it doesn't have to.
‫And what I need to do, therefore, is to create a relationship table so it's going to make a relationship
‫between an animal and a zoo.
‫So let's go ahead and create that new table and that one will have an ID and it will have a zoo ID which
‫is of type int and allow.
‫Now I'm going to deactivate that as well.
‫Same here.
‫I'm going to have a animal ID and it's also going to be of type int and allow now is also deactivated.
‫So now this primary key for my ID is not going to be enough.
‫I want it to be auto incremented as well.
‫So I go to my properties on the right hand side identity specification and it will be here as false
‫and I want it to be true.
‫So its identity is going to be true.
‫All right.
‫And that will create my table.
‫And I'm going to change the name because I want it to be called something like Zoo Animal, because
‫it's going to be the relationship between zoo and animals.
‫So which animals are in which zoo?
‫So once that is done, I can go ahead and press update and that will create the table for me.
‫So let's go ahead and refresh that.
‫And we see Zoo Animal is here as well.
‫And now there is something that we still need to do in order to see those data or to have data for the
‫zoo and the animal.
‫We need to update our data source.
‫And in order to do so, I'm going to press alt shift.
‫DX Well, I have a zoo manager selected that will open up data sources for me and I need to configure
‫my data source with the wizard.
‫I'm going to do that.
‫It's going to retrieve data and as you can see, only zoo is selected here.
‫And I want animal and zoo animal also to be selected.
‫So I'm going to select those and I'm going to press finish.
‫Once that is done, you can see that those are added to our data source.
‫And our data source is pretty much all the data that we have for those tables because otherwise they
‫would be empty.
‫And now there is something neat that we can do is we can edit our dataset with designer.
‫So I'm going to open that up.
‫And as you can see, my designer opens up and here I have my three tables.
‫So I have the zoo, I have the animal and I have the zoo animal and that's pretty cool.
‫Those are not connected to each other yet, but we will change that in a bit.
‫So this zoo here, it has an ID and that is a location.
‫The animal has an ID in the name and then we have a zoo animal which has a zoo ID and an animal ID.
‫So how do we connect them to each other?
‫Because zoo should be connected to zoo.
‫Animal and animal should also be connected to zoo animal.
‫Well, we go back to our data or to actually our here, our server explorer and we need to change our
‫zoo animal.
‫And in order to change it, I go to open table definition.
‫So right click on Zoo Animal Open Table Definition Next.
‫What I need to do is to add foreign keys and I'm going to create a new foreign key and I'm going to
‫call that one foreign key zoo animal.
‫Or actually, let's keep it simple.
‫I'm going to call it zoo foreign key.
‫So Zoo F K, because this is going to be the zoo foreign key that we have.
‫And what it does, it connects a key.
‫Well, it connects.
‫And what it does is it connects a column of my table here with a column of another table.
‫So here I'm going to say the foreign key is going to be Xu ID and the reference well I want to to be
‫connected to Zu and then to the column table or table column called ID.
‫And now I want to do the same thing.
‫I'm going to copy that.
‫And actually, I don't need the square brackets.
‫I'm going to do without them.
‫And I want to do the same for the animal.
‫So I'm going to have an animal foreign key, which is going to connect to animal ID.
‫And here I want to access animal ID and I don't need this comma at the end.
‫All right, so this way, I'm just updating my zoo animal table, and I'm going to get rid of that.
‫Just like that.
‫And now I can go ahead and update that.
‫So update the database.
‫Now that that is done, we can add some data to our zoo animal because it's pretty much empty right
‫now.
‫So let's open the table data and show it up.
‫And we're going to use actually let's open zoo animal data here.
‫We have animal data.
‫So shark is one, clownfish is to monkey and so forth.
‫So let's add a shark, any clownfish to our first location.
‫So the zoo ID is going to be one and the animal is going to be one.
‫The zoo ID is going to be one still and the animal ID is going to be two.
‫So what does that mean?
‫That means that in our first location and if we want to check what the first location was, we go to
‫our zoo table and we can see the first location is New York.
‫So in New York, we are going to have animals.
‫One and two because the zoo is one and there are animals, one and two.
‫So what are those animals?
‫One and two?
‫Well, they are shark and clownfish.
‫So now let's check out the second location, Tokyo.
‫Now let's add something to Tokyo.
‫Let's add the third and the fourth animal to Tokyo.
‫Then we have the third city.
‫Let's add the fifth animal there and the sixth animal.
‫And then the fourth city is going to have the seventh and eighth animal.
‫And now we have a fifth city, which was Milan.
‫And I'm just going to say Milan is going to have all of them.
‫So it's going to be full of animals.
‫Four, five, six, five, seven, five, eight and five is missing.
‫So five, five, five, six, five, seven and five, eight.
‫So what that means now is we have five zoos and we have eight different animals.
‫And in the first zoo we only have animal one and two, which are the fish animals.
‫And the second one, which is our Tokyo, we have three and four, which is a monkey and a wolf.
‫Then in Berlin we have five and six, which are gecko and crocodile.
‫Then in Cairo we have the owl and parrot, and then in Milan we actually have all of them.
‫So we have all eight animals in Milan.
‫So that's pretty much what this relationship table says.
‫It says that we have, well, animals in specific zoos.
‫Now, that's all great and stuff.
‫But how do we check that out?
‫How can we find out what's happening and everything is cool and so forth?
‫Well, we can create a new query onto our database, and I'm just going to go to create database or
‫create query here, right click and new query.
‫And you will be able to add a SQL query in here.
‫And I'm just going to select all data from zoo and animal and.
‫I'm going to just say select star from Animal.
‫And actually it's called Capital a Animal.
‫So like that select start from animal.
‫If I run that code, so I execute the code or control shift e you will see that this is what we get.
‫So we get all the entries of our animal.
‫So one is a shark, two is clownfish and so forth.
‫So the star means all.
‫So we can simply say, give me everything from the animal.
‫So every single entry that you have from animal.
‫Now for you, the little challenge show everything that is in a zoo.
‫So all the zoos that we have.
‫All right.
‫I hope you tried it.
‫I simply say select star from Zoo and I'm going to execute that.
‫And as you can see, I get my five cities here and now a little more challenging.
‫What are we going to do now is we are going to check all the animals that are in New York.
‫And, you know, New York has the idea of one.
‫So now we want to see which of those animals are in New York.
‫And in order to do that, we need to make a combination of two tables.
‫So first of all, we need to know the table in which the animals are, and then we need the table with
‫all the zoo animals.
‫So that's going to be the combination of the two.
‫And therefore, we need to create an inner join.
‫And I'm going to use a third name where a is my animal.
‫So from animal A and now it's going to be inner joined.
‫So inner join with zoo animal.
‫And there I give it the name of Z A.
‫On its ID, so it's going to be in a joint on the ID of animal and it's going to be equal to the zoo.
‫Animal, animal ID.
‫So as you know, there is an animal ID and we have the animal ID in zoo animal and now we want to combine
‫those and see which ones are at the New York Zoo.
‫And therefore, we simply use where the zoo animal zoo ID is equal to one because one was in New York.
‫And if we check that again, well, actually, we can run it.
‫Then we can see there is shark and clownfish.
‫In order to check that we go to Zoo Animal and we show table data here and we can see in one we have
‫one and two, which are the animals of well, you already know that shark and clone.
‫Now if we go back to your query, that's exactly what we got here, shark and clownfish.
‫So that's how we can join them together.
‫And we are going to use something similar.
‫So this is not an SQL tutorial, it's more of a see sharp database tutorial and we're going to do the
‫C sharp way in the next video.
‫So see you there.