1 00:00:00,570 --> 00:00:09,900 Welcome back in the previous video we've created a login table now that we have these two tables together. 2 00:00:09,930 --> 00:00:12,900 I want to show you the real power of relational databases. 3 00:00:15,440 --> 00:00:22,200 Going back to our drawing the most important part of relational databases is this idea of schemas, of 4 00:00:22,200 --> 00:00:29,520 having tables that are just concerned about storing information specific to them such as users, tweets 5 00:00:29,580 --> 00:00:38,030 and following and connecting them through a primary key and a foreign key. I'm going to show you how 6 00:00:38,030 --> 00:00:39,250 to do that. 7 00:00:39,680 --> 00:00:41,170 Well we've already done it. 8 00:00:41,180 --> 00:00:46,940 But how do we grab information from here. If we go back to our table 9 00:00:47,080 --> 00:00:49,470 we remember that we ran our query, 10 00:00:54,920 --> 00:01:04,069 we created the Create login table here and we created a name variable that when we planned this was 11 00:01:04,069 --> 00:01:10,680 going to match whatever the users table had and we can use something called JOINs. 12 00:01:10,720 --> 00:01:18,510 now with our queries. And this is a new word in SQL and probably one of the most powerful, because we 13 00:01:18,510 --> 00:01:24,890 don't necessarily want to have one massive table with hundreds of columns, hundreds of rows. 14 00:01:24,900 --> 00:01:31,350 Databases aren't designed to just have- just like an Excel sheet tons of rows and columns and have all 15 00:01:31,350 --> 00:01:34,740 that information in one place because it's not efficient. 16 00:01:34,770 --> 00:01:36,110 It'll be a lot slower, 17 00:01:36,160 --> 00:01:37,670 it'll be really, really hard to manage. 18 00:01:37,680 --> 00:01:41,960 And the bigger and bigger your company grows it's going to be really, really tough. 19 00:01:42,000 --> 00:01:48,150 A good way to design a database is to have these small little tables that are just concerned, just like 20 00:01:48,150 --> 00:01:53,500 separation of concerns, concerned with their minimal needed information. 21 00:01:53,550 --> 00:02:00,180 And if something else is needed we can just join tables together to grab information. 22 00:02:00,180 --> 00:02:01,270 Let me show you what I mean. 23 00:02:02,850 --> 00:02:09,830 We have over here our test database and let's do \d to see. 24 00:02:09,840 --> 00:02:12,230 All right we have three files here. 25 00:02:12,420 --> 00:02:16,140 We have the login and users which we created. 26 00:02:16,140 --> 00:02:20,130 But what is the login ID sequence. 27 00:02:20,160 --> 00:02:26,480 Remember how we created a primary key. The way relational databases work is when you create a primary 28 00:02:26,480 --> 00:02:35,320 key it creates another file that contains this primary key. And you can see that the type is sequence. 29 00:02:36,570 --> 00:02:44,280 Now this is a file that most likely we won't access but internally Postgres is going to make sure because 30 00:02:44,280 --> 00:02:50,330 we set this primary key it's gonna be really, really fast to grab data from the login table when we 31 00:02:50,330 --> 00:02:50,890 request it. 32 00:02:51,100 --> 00:02:58,890 But let's say I wanted to get information from login and users. Perhaps I want to get Sally's information 33 00:02:59,640 --> 00:03:08,320 but I need her birthday, score, age as well as her secret. By using JOINs we can do this. 34 00:03:09,110 --> 00:03:11,850 I can say SELECT star, 35 00:03:11,990 --> 00:03:15,370 So everything FROM users. 36 00:03:15,690 --> 00:03:20,900 And now if I do JOIN, I can say another table name that I want to join with the users. 37 00:03:20,900 --> 00:03:27,580 In our case login and we have to say ON what are we going to join or not. 38 00:03:27,590 --> 00:03:33,370 How is it going to know what matches with Login. 39 00:03:33,470 --> 00:03:41,400 Because if I go back here, how is the database going to know that I want to match the first line with 40 00:03:41,400 --> 00:03:41,760 Andrei, 41 00:03:41,760 --> 00:03:48,690 the second line with Sally, a third line with John. The ON key word allows us to make sure that we match 42 00:03:48,690 --> 00:03:49,280 on something. 43 00:03:49,320 --> 00:03:56,360 In our case it's the name their primary key here and the foreign key here. 44 00:03:56,470 --> 00:03:59,730 So let's do that ON users 45 00:03:59,920 --> 00:04:07,820 .name equals login.name 46 00:04:10,500 --> 00:04:11,290 semicolon. 47 00:04:11,340 --> 00:04:12,080 Let's do that. 48 00:04:13,230 --> 00:04:14,080 And look at that. 49 00:04:14,280 --> 00:04:21,810 We have name, age, birthday, score but also ID, secret, name altogether. 50 00:04:21,980 --> 00:04:29,750 We've just joined these values and they all match because we've done login.name and users.name, and 51 00:04:29,750 --> 00:04:30,810 with JOINs 52 00:04:31,010 --> 00:04:40,170 comes great power now, we can have small little tables based on what we actually need and any time something 53 00:04:40,170 --> 00:04:46,710 specific is needed we just join tables and we can do multiple tables, multiple JOINs in order to select 54 00:04:46,710 --> 00:04:48,720 the information that we need. 55 00:04:48,720 --> 00:04:54,030 All right I think we've done a really really good job understanding SQL and the power of relational 56 00:04:54,030 --> 00:04:54,940 databases. 57 00:04:56,890 --> 00:04:57,890 I'll see you in the next one. 58 00:04:57,940 --> 00:04:59,980 And good job getting this far, bye-bye.