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.