1 00:00:00,660 --> 00:00:01,530 ‫Welcome back. 2 00:00:01,560 --> 00:00:06,870 ‫In this video, we are going to create two more tables and we're going to set them up so that we can 3 00:00:06,870 --> 00:00:10,590 ‫create our little program that we have seen in the last video. 4 00:00:10,800 --> 00:00:11,220 ‫All right. 5 00:00:11,220 --> 00:00:14,260 ‫So let's get started by creating a new table here. 6 00:00:14,280 --> 00:00:22,590 ‫So I go to my tables in my server explorer under the correct data connection, and I create a new table. 7 00:00:22,590 --> 00:00:24,300 ‫So add new table here. 8 00:00:24,990 --> 00:00:31,620 ‫And the new table should be called animal because it's going to contain all the different animals that 9 00:00:31,620 --> 00:00:32,370 ‫we have. 10 00:00:32,610 --> 00:00:37,650 ‫Then as you can see, I change the name here, create table dbo animal. 11 00:00:38,100 --> 00:00:45,660 ‫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 12 00:00:46,200 --> 00:00:49,600 ‫so and var char 50. 13 00:00:49,620 --> 00:00:52,920 ‫So this is going to have 50 characters again. 14 00:00:53,130 --> 00:01:00,450 ‫And it's important that this ID here is going to be our primary ID here. 15 00:01:00,450 --> 00:01:07,110 ‫So ID specification or identity specification and I'm going to say, is identity true? 16 00:01:07,110 --> 00:01:09,450 ‫And you can see it will now automatically increment. 17 00:01:09,450 --> 00:01:14,130 ‫So this is the same approach that we've seen with the zoo table, but we do that with the animal table 18 00:01:14,130 --> 00:01:15,180 ‫now as well. 19 00:01:15,660 --> 00:01:20,880 ‫Once that is done, we can press the update key and update the database. 20 00:01:22,720 --> 00:01:29,200 ‫And once that is done, again, we can refresh here and we can see we have an animal table in our tables 21 00:01:29,200 --> 00:01:29,920 ‫as well. 22 00:01:30,340 --> 00:01:32,680 ‫Now let's add some data to that table. 23 00:01:32,680 --> 00:01:36,550 ‫So show data or table data. 24 00:01:36,580 --> 00:01:40,630 ‫And in here, I'm just going to add a fish. 25 00:01:41,050 --> 00:01:42,790 ‫Actually, I'm going to add a shark. 26 00:01:43,090 --> 00:01:44,500 ‫Then a clown. 27 00:01:44,500 --> 00:01:46,690 ‫Fish monkey. 28 00:01:47,470 --> 00:01:49,810 ‫Wolf gecko. 29 00:01:50,260 --> 00:01:51,400 ‫Crocodile. 30 00:01:52,180 --> 00:01:54,730 ‫Crocodile dial. 31 00:01:55,030 --> 00:01:57,250 ‫Then an owl. 32 00:01:57,730 --> 00:01:58,630 ‫A parrot. 33 00:01:59,680 --> 00:02:00,190 ‫That's it. 34 00:02:00,220 --> 00:02:01,920 ‫So I have these eight names here. 35 00:02:01,930 --> 00:02:03,790 ‫You can use different ones. 36 00:02:03,790 --> 00:02:05,740 ‫So these are just names of animals. 37 00:02:05,740 --> 00:02:11,050 ‫And as you can see, they have an ID as well, which is unique and which auto incremented each time 38 00:02:11,050 --> 00:02:12,560 ‫that I entered a value. 39 00:02:12,580 --> 00:02:15,670 ‫So now we have those eight animals in here. 40 00:02:15,670 --> 00:02:24,280 ‫And what I want to do is now create a combination of animals and zoos so there can be a shark in the 41 00:02:24,280 --> 00:02:31,150 ‫zoo of New York, but it can also be a shark in Miami or in Milan or in Berlin or whatever. 42 00:02:31,210 --> 00:02:36,010 ‫Then it can be a wolf in Miami, but could also be one in New York. 43 00:02:36,010 --> 00:02:37,260 ‫But it doesn't have to. 44 00:02:37,570 --> 00:02:44,260 ‫And what I need to do, therefore, is to create a relationship table so it's going to make a relationship 45 00:02:44,260 --> 00:02:46,630 ‫between an animal and a zoo. 46 00:02:48,040 --> 00:02:58,780 ‫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 47 00:02:58,780 --> 00:03:02,500 ‫is of type int and allow. 48 00:03:02,500 --> 00:03:04,330 ‫Now I'm going to deactivate that as well. 49 00:03:04,330 --> 00:03:05,290 ‫Same here. 50 00:03:05,500 --> 00:03:15,220 ‫I'm going to have a animal ID and it's also going to be of type int and allow now is also deactivated. 51 00:03:15,430 --> 00:03:19,930 ‫So now this primary key for my ID is not going to be enough. 52 00:03:19,930 --> 00:03:21,910 ‫I want it to be auto incremented as well. 53 00:03:21,910 --> 00:03:31,810 ‫So I go to my properties on the right hand side identity specification and it will be here as false 54 00:03:31,810 --> 00:03:32,830 ‫and I want it to be true. 55 00:03:32,830 --> 00:03:35,020 ‫So its identity is going to be true. 56 00:03:36,000 --> 00:03:36,570 ‫All right. 57 00:03:36,570 --> 00:03:38,220 ‫And that will create my table. 58 00:03:38,220 --> 00:03:45,330 ‫And I'm going to change the name because I want it to be called something like Zoo Animal, because 59 00:03:45,330 --> 00:03:48,240 ‫it's going to be the relationship between zoo and animals. 60 00:03:48,240 --> 00:03:50,310 ‫So which animals are in which zoo? 61 00:03:50,670 --> 00:03:57,540 ‫So once that is done, I can go ahead and press update and that will create the table for me. 62 00:03:57,630 --> 00:03:59,820 ‫So let's go ahead and refresh that. 63 00:03:59,820 --> 00:04:02,400 ‫And we see Zoo Animal is here as well. 64 00:04:02,850 --> 00:04:09,810 ‫And now there is something that we still need to do in order to see those data or to have data for the 65 00:04:09,810 --> 00:04:10,680 ‫zoo and the animal. 66 00:04:10,680 --> 00:04:13,740 ‫We need to update our data source. 67 00:04:13,740 --> 00:04:17,280 ‫And in order to do so, I'm going to press alt shift. 68 00:04:17,280 --> 00:04:25,980 ‫DX Well, I have a zoo manager selected that will open up data sources for me and I need to configure 69 00:04:25,980 --> 00:04:28,220 ‫my data source with the wizard. 70 00:04:28,230 --> 00:04:29,160 ‫I'm going to do that. 71 00:04:29,730 --> 00:04:33,660 ‫It's going to retrieve data and as you can see, only zoo is selected here. 72 00:04:33,660 --> 00:04:37,110 ‫And I want animal and zoo animal also to be selected. 73 00:04:37,110 --> 00:04:39,990 ‫So I'm going to select those and I'm going to press finish. 74 00:04:40,560 --> 00:04:44,430 ‫Once that is done, you can see that those are added to our data source. 75 00:04:44,430 --> 00:04:49,230 ‫And our data source is pretty much all the data that we have for those tables because otherwise they 76 00:04:49,230 --> 00:04:50,160 ‫would be empty. 77 00:04:50,160 --> 00:04:56,100 ‫And now there is something neat that we can do is we can edit our dataset with designer. 78 00:04:56,100 --> 00:04:57,540 ‫So I'm going to open that up. 79 00:04:57,540 --> 00:05:01,470 ‫And as you can see, my designer opens up and here I have my three tables. 80 00:05:01,470 --> 00:05:07,920 ‫So I have the zoo, I have the animal and I have the zoo animal and that's pretty cool. 81 00:05:07,950 --> 00:05:12,090 ‫Those are not connected to each other yet, but we will change that in a bit. 82 00:05:12,510 --> 00:05:15,930 ‫So this zoo here, it has an ID and that is a location. 83 00:05:15,930 --> 00:05:22,350 ‫The animal has an ID in the name and then we have a zoo animal which has a zoo ID and an animal ID. 84 00:05:22,650 --> 00:05:24,690 ‫So how do we connect them to each other? 85 00:05:24,690 --> 00:05:26,400 ‫Because zoo should be connected to zoo. 86 00:05:26,400 --> 00:05:29,310 ‫Animal and animal should also be connected to zoo animal. 87 00:05:29,760 --> 00:05:40,950 ‫Well, we go back to our data or to actually our here, our server explorer and we need to change our 88 00:05:40,950 --> 00:05:41,820 ‫zoo animal. 89 00:05:42,090 --> 00:05:46,020 ‫And in order to change it, I go to open table definition. 90 00:05:46,020 --> 00:05:49,920 ‫So right click on Zoo Animal Open Table Definition Next. 91 00:05:49,920 --> 00:05:55,920 ‫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 92 00:05:55,920 --> 00:05:59,010 ‫call that one foreign key zoo animal. 93 00:05:59,340 --> 00:06:02,010 ‫Or actually, let's keep it simple. 94 00:06:02,010 --> 00:06:04,020 ‫I'm going to call it zoo foreign key. 95 00:06:04,020 --> 00:06:11,340 ‫So Zoo F K, because this is going to be the zoo foreign key that we have. 96 00:06:11,520 --> 00:06:15,000 ‫And what it does, it connects a key. 97 00:06:15,000 --> 00:06:16,080 ‫Well, it connects. 98 00:06:17,570 --> 00:06:24,020 ‫And what it does is it connects a column of my table here with a column of another table. 99 00:06:24,020 --> 00:06:32,360 ‫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 100 00:06:32,360 --> 00:06:38,750 ‫connected to Zu and then to the column table or table column called ID. 101 00:06:40,800 --> 00:06:43,230 ‫And now I want to do the same thing. 102 00:06:43,890 --> 00:06:45,030 ‫I'm going to copy that. 103 00:06:46,110 --> 00:06:48,440 ‫And actually, I don't need the square brackets. 104 00:06:48,450 --> 00:06:49,860 ‫I'm going to do without them. 105 00:06:51,270 --> 00:06:56,160 ‫And I want to do the same for the animal. 106 00:06:57,090 --> 00:07:05,550 ‫So I'm going to have an animal foreign key, which is going to connect to animal ID. 107 00:07:07,800 --> 00:07:15,000 ‫And here I want to access animal ID and I don't need this comma at the end. 108 00:07:15,510 --> 00:07:21,900 ‫All right, so this way, I'm just updating my zoo animal table, and I'm going to get rid of that. 109 00:07:22,410 --> 00:07:23,130 ‫Just like that. 110 00:07:23,130 --> 00:07:25,530 ‫And now I can go ahead and update that. 111 00:07:27,240 --> 00:07:28,890 ‫So update the database. 112 00:07:29,820 --> 00:07:35,880 ‫Now that that is done, we can add some data to our zoo animal because it's pretty much empty right 113 00:07:35,880 --> 00:07:36,240 ‫now. 114 00:07:36,240 --> 00:07:40,620 ‫So let's open the table data and show it up. 115 00:07:41,220 --> 00:07:47,640 ‫And we're going to use actually let's open zoo animal data here. 116 00:07:47,640 --> 00:07:49,020 ‫We have animal data. 117 00:07:49,230 --> 00:07:52,800 ‫So shark is one, clownfish is to monkey and so forth. 118 00:07:52,800 --> 00:07:56,460 ‫So let's add a shark, any clownfish to our first location. 119 00:07:56,460 --> 00:08:00,840 ‫So the zoo ID is going to be one and the animal is going to be one. 120 00:08:00,870 --> 00:08:06,570 ‫The zoo ID is going to be one still and the animal ID is going to be two. 121 00:08:06,570 --> 00:08:08,070 ‫So what does that mean? 122 00:08:08,430 --> 00:08:14,490 ‫That means that in our first location and if we want to check what the first location was, we go to 123 00:08:14,490 --> 00:08:18,930 ‫our zoo table and we can see the first location is New York. 124 00:08:18,930 --> 00:08:23,370 ‫So in New York, we are going to have animals. 125 00:08:24,270 --> 00:08:28,810 ‫One and two because the zoo is one and there are animals, one and two. 126 00:08:28,830 --> 00:08:30,240 ‫So what are those animals? 127 00:08:30,240 --> 00:08:31,020 ‫One and two? 128 00:08:31,050 --> 00:08:33,390 ‫Well, they are shark and clownfish. 129 00:08:33,450 --> 00:08:36,390 ‫So now let's check out the second location, Tokyo. 130 00:08:36,420 --> 00:08:38,610 ‫Now let's add something to Tokyo. 131 00:08:38,640 --> 00:08:42,360 ‫Let's add the third and the fourth animal to Tokyo. 132 00:08:42,720 --> 00:08:44,820 ‫Then we have the third city. 133 00:08:44,850 --> 00:08:49,050 ‫Let's add the fifth animal there and the sixth animal. 134 00:08:49,440 --> 00:08:55,190 ‫And then the fourth city is going to have the seventh and eighth animal. 135 00:08:55,200 --> 00:08:57,990 ‫And now we have a fifth city, which was Milan. 136 00:08:57,990 --> 00:09:01,800 ‫And I'm just going to say Milan is going to have all of them. 137 00:09:02,130 --> 00:09:05,730 ‫So it's going to be full of animals. 138 00:09:07,710 --> 00:09:16,530 ‫Four, five, six, five, seven, five, eight and five is missing. 139 00:09:16,530 --> 00:09:23,770 ‫So five, five, five, six, five, seven and five, eight. 140 00:09:23,790 --> 00:09:29,850 ‫So what that means now is we have five zoos and we have eight different animals. 141 00:09:29,850 --> 00:09:35,160 ‫And in the first zoo we only have animal one and two, which are the fish animals. 142 00:09:35,160 --> 00:09:43,410 ‫And the second one, which is our Tokyo, we have three and four, which is a monkey and a wolf. 143 00:09:43,710 --> 00:09:50,580 ‫Then in Berlin we have five and six, which are gecko and crocodile. 144 00:09:50,700 --> 00:09:58,920 ‫Then in Cairo we have the owl and parrot, and then in Milan we actually have all of them. 145 00:09:58,920 --> 00:10:01,440 ‫So we have all eight animals in Milan. 146 00:10:01,740 --> 00:10:05,370 ‫So that's pretty much what this relationship table says. 147 00:10:05,880 --> 00:10:09,780 ‫It says that we have, well, animals in specific zoos. 148 00:10:10,770 --> 00:10:12,600 ‫Now, that's all great and stuff. 149 00:10:12,600 --> 00:10:15,300 ‫But how do we check that out? 150 00:10:15,330 --> 00:10:20,130 ‫How can we find out what's happening and everything is cool and so forth? 151 00:10:20,160 --> 00:10:27,240 ‫Well, we can create a new query onto our database, and I'm just going to go to create database or 152 00:10:27,240 --> 00:10:30,420 ‫create query here, right click and new query. 153 00:10:30,420 --> 00:10:35,370 ‫And you will be able to add a SQL query in here. 154 00:10:36,970 --> 00:10:41,830 ‫And I'm just going to select all data from zoo and animal and. 155 00:10:42,720 --> 00:10:49,410 ‫I'm going to just say select star from Animal. 156 00:10:50,250 --> 00:10:53,190 ‫And actually it's called Capital a Animal. 157 00:10:53,460 --> 00:10:57,440 ‫So like that select start from animal. 158 00:10:57,450 --> 00:11:04,650 ‫If I run that code, so I execute the code or control shift e you will see that this is what we get. 159 00:11:04,650 --> 00:11:07,710 ‫So we get all the entries of our animal. 160 00:11:07,800 --> 00:11:11,100 ‫So one is a shark, two is clownfish and so forth. 161 00:11:11,100 --> 00:11:13,290 ‫So the star means all. 162 00:11:13,530 --> 00:11:17,520 ‫So we can simply say, give me everything from the animal. 163 00:11:17,520 --> 00:11:20,370 ‫So every single entry that you have from animal. 164 00:11:27,660 --> 00:11:31,840 ‫Now for you, the little challenge show everything that is in a zoo. 165 00:11:31,860 --> 00:11:33,870 ‫So all the zoos that we have. 166 00:11:35,860 --> 00:11:36,190 ‫All right. 167 00:11:36,190 --> 00:11:37,270 ‫I hope you tried it. 168 00:11:37,720 --> 00:11:42,430 ‫I simply say select star from Zoo and I'm going to execute that. 169 00:11:42,430 --> 00:11:47,920 ‫And as you can see, I get my five cities here and now a little more challenging. 170 00:11:47,920 --> 00:11:52,960 ‫What are we going to do now is we are going to check all the animals that are in New York. 171 00:11:52,960 --> 00:11:55,420 ‫And, you know, New York has the idea of one. 172 00:11:55,420 --> 00:12:00,040 ‫So now we want to see which of those animals are in New York. 173 00:12:00,040 --> 00:12:05,320 ‫And in order to do that, we need to make a combination of two tables. 174 00:12:05,320 --> 00:12:12,130 ‫So first of all, we need to know the table in which the animals are, and then we need the table with 175 00:12:12,130 --> 00:12:13,390 ‫all the zoo animals. 176 00:12:13,540 --> 00:12:16,540 ‫So that's going to be the combination of the two. 177 00:12:16,540 --> 00:12:18,910 ‫And therefore, we need to create an inner join. 178 00:12:18,910 --> 00:12:24,700 ‫And I'm going to use a third name where a is my animal. 179 00:12:25,840 --> 00:12:30,670 ‫So from animal A and now it's going to be inner joined. 180 00:12:30,760 --> 00:12:34,630 ‫So inner join with zoo animal. 181 00:12:35,770 --> 00:12:39,610 ‫And there I give it the name of Z A. 182 00:12:41,350 --> 00:12:53,650 ‫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. 183 00:12:53,650 --> 00:12:55,480 ‫Animal, animal ID. 184 00:12:56,320 --> 00:13:04,630 ‫So as you know, there is an animal ID and we have the animal ID in zoo animal and now we want to combine 185 00:13:04,630 --> 00:13:10,240 ‫those and see which ones are at the New York Zoo. 186 00:13:10,510 --> 00:13:22,780 ‫And therefore, we simply use where the zoo animal zoo ID is equal to one because one was in New York. 187 00:13:23,200 --> 00:13:26,680 ‫And if we check that again, well, actually, we can run it. 188 00:13:26,680 --> 00:13:29,680 ‫Then we can see there is shark and clownfish. 189 00:13:29,770 --> 00:13:37,840 ‫In order to check that we go to Zoo Animal and we show table data here and we can see in one we have 190 00:13:37,840 --> 00:13:43,970 ‫one and two, which are the animals of well, you already know that shark and clone. 191 00:13:44,000 --> 00:13:48,490 ‫Now if we go back to your query, that's exactly what we got here, shark and clownfish. 192 00:13:48,550 --> 00:13:51,670 ‫So that's how we can join them together. 193 00:13:51,670 --> 00:13:53,740 ‫And we are going to use something similar. 194 00:13:53,740 --> 00:14:00,790 ‫So this is not an SQL tutorial, it's more of a see sharp database tutorial and we're going to do the 195 00:14:00,790 --> 00:14:02,950 ‫C sharp way in the next video. 196 00:14:02,950 --> 00:14:04,510 ‫So see you there.