WEBVTT 1 00:00:01.010 --> 00:00:02.590 Hi Guys, and Welcome back. 2 00:00:02.590 --> 00:00:05.970 In this video we're going to learn more about SQL. 3 00:00:05.970 --> 00:00:07.490 So what is SQL? 4 00:00:07.490 --> 00:00:08.323 How do we use it? 5 00:00:08.323 --> 00:00:09.156 What does it mean? 6 00:00:09.156 --> 00:00:10.780 What's it for? 7 00:00:10.780 --> 00:00:14.140 SQL stands for Structured Query Language, 8 00:00:14.140 --> 00:00:15.830 and it is used to interact with 9 00:00:15.830 --> 00:00:18.340 Relational Database Management Systems. 10 00:00:18.340 --> 00:00:20.333 And those are normally called RDBMS. 11 00:00:21.430 --> 00:00:24.070 And the two terms that we're going to learn in this lecture 12 00:00:24.070 --> 00:00:27.160 Structured Query Language, SQL, or SQL 13 00:00:27.160 --> 00:00:28.244 and 14 00:00:28.244 --> 00:00:29.410 RDBMS. 15 00:00:29.410 --> 00:00:33.110 SQL is meant to be similar to English in some ways. 16 00:00:33.110 --> 00:00:35.720 So the queries or the things that you use it for 17 00:00:35.720 --> 00:00:37.380 sometimes look like English, 18 00:00:37.380 --> 00:00:40.380 or they are meant to be reminiscent of English. 19 00:00:40.380 --> 00:00:43.560 And now, they're not exactly like English 20 00:00:43.560 --> 00:00:45.050 by any stretch of the imagination. 21 00:00:45.050 --> 00:00:48.450 Here we've got for example, a SQL query or Command 22 00:00:48.450 --> 00:00:51.640 used to find some data in a table. 23 00:00:51.640 --> 00:00:55.090 Here we're finding data pertaining to users. 24 00:00:55.090 --> 00:00:57.640 And that's what from users means at the end 25 00:00:57.640 --> 00:01:00.190 and we're selecting the first name and the surname 26 00:01:00.190 --> 00:01:01.610 from each user. 27 00:01:01.610 --> 00:01:05.579 You can see how this is somewhat similar to English. 28 00:01:05.579 --> 00:01:09.120 But obviously, we wouldn't say stuff like this in English. 29 00:01:09.120 --> 00:01:12.150 Now to insert data, for example, as another example, 30 00:01:12.150 --> 00:01:13.860 we're going to learn more about these queries 31 00:01:13.860 --> 00:01:17.300 in a lot of detail as we go through the course. 32 00:01:17.300 --> 00:01:19.700 But just as another example of inserting data 33 00:01:19.700 --> 00:01:21.670 into the users table, 34 00:01:21.670 --> 00:01:24.730 then we specify the columns that we want to insert into, 35 00:01:24.730 --> 00:01:26.700 and the values that we want to insert. 36 00:01:26.700 --> 00:01:28.910 But again, we'll look at that in more detail 37 00:01:28.910 --> 00:01:29.833 as we go along. 38 00:01:30.820 --> 00:01:33.280 Now, at the very top in the first bullet point here, 39 00:01:33.280 --> 00:01:35.760 we mentioned that SQL is used to interact 40 00:01:35.760 --> 00:01:38.050 with Relational Database Management Systems. 41 00:01:38.050 --> 00:01:40.380 So what are Relational databases? 42 00:01:40.380 --> 00:01:42.570 And what are RDBMS? 43 00:01:42.570 --> 00:01:45.870 Well, RDBMS is our large, complex and powerful. 44 00:01:45.870 --> 00:01:48.850 And some examples of them include PostgreSQL, 45 00:01:48.850 --> 00:01:51.960 or MySQL, or even SQL Lite. 46 00:01:51.960 --> 00:01:56.960 SQL lite is, obviously a lite version of an RDBMS. 47 00:01:57.210 --> 00:01:59.540 And it's less powerful, 48 00:01:59.540 --> 00:02:01.660 but it's also much more flexible 49 00:02:01.660 --> 00:02:03.780 in terms of how you can use it. 50 00:02:03.780 --> 00:02:05.110 So we're going to learn more about 51 00:02:05.110 --> 00:02:07.810 what I mean by that as we go along as well. 52 00:02:07.810 --> 00:02:10.890 Databases are primarily constructed from tables, 53 00:02:10.890 --> 00:02:13.700 tables have columns, and they also have rows 54 00:02:13.700 --> 00:02:16.520 and they can have zero rows if the table is empty. 55 00:02:16.520 --> 00:02:18.650 So here we've got an example of a table, 56 00:02:18.650 --> 00:02:21.930 where we've got two columns first name and surname. 57 00:02:21.930 --> 00:02:24.470 And then we've got four rows of data. 58 00:02:24.470 --> 00:02:28.120 John smith, Rolf Smith, Anne Pun and Robert Baratheon. 59 00:02:28.120 --> 00:02:30.240 Some things that may stand out 60 00:02:30.240 --> 00:02:33.790 in here is that you can have duplicate values. 61 00:02:33.790 --> 00:02:35.280 Each row is independent. 62 00:02:35.280 --> 00:02:36.860 So you can have duplicate values in there 63 00:02:36.860 --> 00:02:38.180 like Smith in two rows, 64 00:02:38.180 --> 00:02:39.490 and that's totally fine. 65 00:02:39.490 --> 00:02:41.810 But there are ways to prevent that if you wish, 66 00:02:41.810 --> 00:02:43.880 and we'll learn about those later on. 67 00:02:43.880 --> 00:02:45.700 And you can see that every cell 68 00:02:45.700 --> 00:02:49.200 every row and column combination has a value, 69 00:02:49.200 --> 00:02:51.800 although there are ways to allow empty values 70 00:02:51.800 --> 00:02:54.600 and we will learn more about that later on as well. 71 00:02:54.600 --> 00:02:55.433 All right. 72 00:02:55.433 --> 00:02:56.640 So why Relational? 73 00:02:56.640 --> 00:02:58.820 What does the relational term mean 74 00:02:58.820 --> 00:03:01.210 in the name RDBMS? 75 00:03:01.210 --> 00:03:04.750 Well, it's to do with how tables are used together. 76 00:03:04.750 --> 00:03:06.830 Obviously, for something to be relational, 77 00:03:06.830 --> 00:03:07.940 it has to be related. 78 00:03:07.940 --> 00:03:11.100 So you can see how these two tables here are related 79 00:03:11.100 --> 00:03:12.340 in a way. 80 00:03:12.340 --> 00:03:15.020 You've got the first name and the surname columns there 81 00:03:15.020 --> 00:03:17.530 in the users table, let's call it. 82 00:03:17.530 --> 00:03:19.660 And you've got an accounts table. 83 00:03:19.660 --> 00:03:22.070 And this one has the holder 84 00:03:22.070 --> 00:03:24.570 and the account number of the person. 85 00:03:24.570 --> 00:03:26.570 You can see how they are related because 86 00:03:26.570 --> 00:03:29.560 clearly, John Smith, the first user on the left 87 00:03:29.560 --> 00:03:31.550 is the third user on the right 88 00:03:31.550 --> 00:03:34.183 and it has this account number 9586859. 89 00:03:35.610 --> 00:03:38.520 So there is a relationship between these two tables 90 00:03:38.520 --> 00:03:42.860 and SQL on RDBMS, allow us to formalise 91 00:03:42.860 --> 00:03:45.790 that relationship and make it so that the relationship 92 00:03:45.790 --> 00:03:48.300 is very easily usable. 93 00:03:48.300 --> 00:03:52.910 To simplify relations, we often use unique identifiers 94 00:03:52.910 --> 00:03:54.340 for each row. 95 00:03:54.340 --> 00:03:57.010 So here we're gonna give each row in both tables, 96 00:03:57.010 --> 00:03:58.460 a unique identifier. 97 00:03:58.460 --> 00:04:01.860 Here you can see that, John Smith got the ID one, 98 00:04:01.860 --> 00:04:04.390 Rolf Smith got the idea two, and so on. 99 00:04:04.390 --> 00:04:07.740 Now, that means that we can refer to each user 100 00:04:07.740 --> 00:04:11.440 with just the number because these should be unique. 101 00:04:11.440 --> 00:04:13.760 Similarly, in the accounts table, 102 00:04:13.760 --> 00:04:17.470 we've also gone and given each row an ID, 103 00:04:17.470 --> 00:04:21.110 you can see now that the ID one or the account one 104 00:04:21.110 --> 00:04:24.380 is for Anne Pun, and this is the account number. 105 00:04:24.380 --> 00:04:25.900 However, we've gonna step further 106 00:04:25.900 --> 00:04:29.680 and also give them an ID of the user 107 00:04:29.680 --> 00:04:31.920 that should match this person. 108 00:04:31.920 --> 00:04:34.240 So you can see that the account with ID one 109 00:04:34.240 --> 00:04:36.100 is for user with ID three, 110 00:04:36.100 --> 00:04:38.800 so we can then grab this and go to the users table 111 00:04:38.800 --> 00:04:43.290 and grab a user with ID three, and we get Anne Pun. 112 00:04:43.290 --> 00:04:44.960 Obviously if we do this, 113 00:04:44.960 --> 00:04:48.190 we no longer need the holder column here. 114 00:04:48.190 --> 00:04:50.590 Because the holder ID already allows us 115 00:04:50.590 --> 00:04:52.750 to go to the users table and grab the name. 116 00:04:52.750 --> 00:04:55.350 We don't need to have the name here as well. 117 00:04:55.350 --> 00:04:57.960 So we can just get rid of that. 118 00:04:57.960 --> 00:05:00.140 And this is a very common approach 119 00:05:00.140 --> 00:05:01.730 for designing tables, 120 00:05:01.730 --> 00:05:05.790 you see what pieces of data can be stored in its own table 121 00:05:05.790 --> 00:05:07.430 with its own meaning. 122 00:05:07.430 --> 00:05:10.200 And then you can reference those tables from other places, 123 00:05:10.200 --> 00:05:11.960 we're gonna be doing a lot of this throughout the course. 124 00:05:11.960 --> 00:05:15.010 So don't worry if it's not so obvious at this point in time. 125 00:05:15.010 --> 00:05:16.850 But we're going to be learning a lot more about 126 00:05:16.850 --> 00:05:18.400 how to do this. 127 00:05:18.400 --> 00:05:22.810 So again, through some smart querying of the accounts table, 128 00:05:22.810 --> 00:05:24.580 we could find the user's name. 129 00:05:24.580 --> 00:05:26.430 And similarly through some smart querying 130 00:05:26.430 --> 00:05:27.870 of the users table, 131 00:05:27.870 --> 00:05:30.470 we could for each person find their account number. 132 00:05:30.470 --> 00:05:32.320 So the relationships go both ways, 133 00:05:32.320 --> 00:05:35.330 and we can use them very flexibly. 134 00:05:35.330 --> 00:05:36.850 So let's go and learn about those queries 135 00:05:36.850 --> 00:05:39.190 that we wrote at the start of this presentation. 136 00:05:39.190 --> 00:05:40.740 In the next few videos. 137 00:05:40.740 --> 00:05:42.190 I'll see you in the next one.