1 00:00:02,110 --> 00:00:05,550 So, what exactly is SQL? 2 00:00:05,550 --> 00:00:08,410 Well, I gave you a brief introduction 3 00:00:08,410 --> 00:00:11,610 in the previous course section already of course. 4 00:00:11,610 --> 00:00:13,520 Now, you did learn that for some people, 5 00:00:13,520 --> 00:00:16,430 SQL stands for structured query language, 6 00:00:16,430 --> 00:00:19,710 even though that's not actually where it comes from, 7 00:00:19,710 --> 00:00:20,870 but either way, 8 00:00:20,870 --> 00:00:23,630 it is a language that's used for talking 9 00:00:23,630 --> 00:00:26,480 to relational database management systems, 10 00:00:26,480 --> 00:00:29,750 which are systems that use multiple tables 11 00:00:29,750 --> 00:00:32,390 to store data in those tables. 12 00:00:32,390 --> 00:00:34,470 And that the idea there, 13 00:00:34,470 --> 00:00:38,560 is that the data in those tables is normalized 14 00:00:38,560 --> 00:00:43,000 and clearly structured that you have clearly defined schemas 15 00:00:43,000 --> 00:00:44,680 and data types, 16 00:00:44,680 --> 00:00:48,760 which then apply to all the data in the tables. 17 00:00:48,760 --> 00:00:50,640 And you learned that you can of course, 18 00:00:50,640 --> 00:00:54,440 then also query your data in those databases. 19 00:00:54,440 --> 00:00:58,620 And that also includes queries across multiple tables 20 00:00:58,620 --> 00:01:01,340 to query for a connected records 21 00:01:01,340 --> 00:01:02,270 as we, for example, 22 00:01:02,270 --> 00:01:05,803 do it here for our flights and then the airport data. 23 00:01:07,200 --> 00:01:09,720 So, SQL is a query language 24 00:01:09,720 --> 00:01:12,960 for relational database management systems. 25 00:01:12,960 --> 00:01:14,030 Though actually, 26 00:01:14,030 --> 00:01:18,060 it's also used beyond that for data analysis tasks 27 00:01:18,060 --> 00:01:18,980 and so on. 28 00:01:18,980 --> 00:01:21,810 So, knowing SQL is never a bad idea. 29 00:01:21,810 --> 00:01:24,370 It will always help you if you're doing anything 30 00:01:24,370 --> 00:01:27,960 related to development or data science. 31 00:01:27,960 --> 00:01:29,100 But in this course, 32 00:01:29,100 --> 00:01:33,110 and probably the biggest area where it's being used, 33 00:01:33,110 --> 00:01:36,490 is that you use it for querying data structured 34 00:01:36,490 --> 00:01:40,420 and stored in relational database management systems. 35 00:01:40,420 --> 00:01:42,620 And there, we have all these ideas 36 00:01:42,620 --> 00:01:44,640 I outlined a couple of seconds ago 37 00:01:44,640 --> 00:01:45,473 and therefore, 38 00:01:45,473 --> 00:01:48,530 definitely go through this previous course section again, 39 00:01:48,530 --> 00:01:51,870 if you're not 100% sure what databases, 40 00:01:51,870 --> 00:01:55,670 database systems to be precise, SQL and so on, 41 00:01:55,670 --> 00:01:56,763 are all about. 42 00:01:58,330 --> 00:02:00,340 Now, in this course section, 43 00:02:00,340 --> 00:02:03,000 I wanna introduce you to SQL. 44 00:02:03,000 --> 00:02:04,830 I wanna show you how to install 45 00:02:04,830 --> 00:02:07,890 and set up a SQL based database. 46 00:02:07,890 --> 00:02:11,300 I wanna introduce you to the SQL language 47 00:02:11,300 --> 00:02:13,710 and show you how you can write your own queries. 48 00:02:13,710 --> 00:02:14,920 And therefore, 49 00:02:14,920 --> 00:02:17,030 I prepared a little demo, 50 00:02:17,030 --> 00:02:19,359 a demo imaginary application 51 00:02:19,359 --> 00:02:23,490 for which we might wanna build our database structure 52 00:02:23,490 --> 00:02:25,430 in this course section. 53 00:02:25,430 --> 00:02:26,330 And therefore, 54 00:02:26,330 --> 00:02:28,670 in this course section together with you, 55 00:02:28,670 --> 00:02:32,130 I wanna build a database structure 56 00:02:32,130 --> 00:02:34,480 that we could use in an application 57 00:02:34,480 --> 00:02:37,300 that deals with restaurants and reviews 58 00:02:37,300 --> 00:02:39,150 for these restaurants. 59 00:02:39,150 --> 00:02:43,600 And we're not going to write the actual website code. 60 00:02:43,600 --> 00:02:45,520 We're not going to write any front-end 61 00:02:45,520 --> 00:02:47,070 or back-end code there, 62 00:02:47,070 --> 00:02:48,860 but instead in this course section, 63 00:02:48,860 --> 00:02:51,470 we're going to focus on setting up the database, 64 00:02:51,470 --> 00:02:53,810 such an application could use. 65 00:02:53,810 --> 00:02:55,640 In the next course section, 66 00:02:55,640 --> 00:02:58,870 we are going to write code and you are going to see 67 00:02:58,870 --> 00:03:01,150 how you can then use your SQL knowledge 68 00:03:01,150 --> 00:03:03,100 you gain in this course section, 69 00:03:03,100 --> 00:03:06,410 in your NodeJS code to actually send data 70 00:03:06,410 --> 00:03:08,173 into a database from there. 71 00:03:09,040 --> 00:03:11,200 But for this course section let's say, 72 00:03:11,200 --> 00:03:15,020 we actually want to have data about restaurants, 73 00:03:15,020 --> 00:03:17,660 for example, a unique ID, the name, 74 00:03:17,660 --> 00:03:19,420 the address of a restaurant, 75 00:03:19,420 --> 00:03:22,060 and the type of restaurant it is. 76 00:03:22,060 --> 00:03:24,830 And we also wanna have data about reviews. 77 00:03:24,830 --> 00:03:25,860 And the idea here, 78 00:03:25,860 --> 00:03:28,040 is that we also have an ID, 79 00:03:28,040 --> 00:03:29,500 the name of their reviewer, 80 00:03:29,500 --> 00:03:31,930 so of the person who left the review, 81 00:03:31,930 --> 00:03:33,160 then a rating, 82 00:03:33,160 --> 00:03:35,390 let's say one to five, 83 00:03:35,390 --> 00:03:37,350 then some review text. 84 00:03:37,350 --> 00:03:40,010 So, a short comment by the reviewer 85 00:03:40,010 --> 00:03:42,430 and the date when the review was left. 86 00:03:43,290 --> 00:03:46,360 And of course, these two pieces of data 87 00:03:46,360 --> 00:03:48,610 should also be connected somehow, 88 00:03:48,610 --> 00:03:52,100 so that reviews are connected to restaurants 89 00:03:52,100 --> 00:03:54,823 because we are reviewing restaurants in the end. 90 00:03:55,990 --> 00:03:58,380 So, that's the general idea 91 00:03:58,380 --> 00:04:01,590 with which we're going to work in this course section. 92 00:04:01,590 --> 00:04:03,150 And throughout the course section, 93 00:04:03,150 --> 00:04:06,190 we are going to plan the database tables 94 00:04:06,190 --> 00:04:07,250 we need for that, 95 00:04:07,250 --> 00:04:09,330 the data types we need for that, 96 00:04:09,330 --> 00:04:13,770 and we're going to write actual queries for inserting data, 97 00:04:13,770 --> 00:04:17,000 updating data, deleting and fetching data. 98 00:04:17,000 --> 00:04:19,769 And therefore, we've got a couple of steps 99 00:04:19,769 --> 00:04:23,250 through which we will go in this course section. 100 00:04:23,250 --> 00:04:26,760 And the first step is to actually install 101 00:04:26,760 --> 00:04:30,020 and set up a SQL database system. 102 00:04:30,020 --> 00:04:32,790 So, some software on our computer, 103 00:04:32,790 --> 00:04:36,820 which actually run stat database system. 104 00:04:36,820 --> 00:04:39,360 And we will do that in the next lecture. 105 00:04:39,360 --> 00:04:42,900 And then I will also talk about some choices we have there 106 00:04:42,900 --> 00:04:46,440 because there was more than one SQL database system provider 107 00:04:46,440 --> 00:04:47,530 out there. 108 00:04:47,530 --> 00:04:50,750 And that's the first thing we have to do. 109 00:04:50,750 --> 00:04:54,010 Now, once we got a database system up and running, 110 00:04:54,010 --> 00:04:56,500 we of course have to define the tables 111 00:04:56,500 --> 00:05:00,340 we wanna create in there and the structure of these tables. 112 00:05:00,340 --> 00:05:02,687 Which columns do we want to have in there? 113 00:05:02,687 --> 00:05:06,030 And which kind of data should be stored in each column? 114 00:05:06,030 --> 00:05:07,653 That will be the second step. 115 00:05:08,650 --> 00:05:10,530 And there after of course, 116 00:05:10,530 --> 00:05:12,640 we wanna learn how to insert data 117 00:05:12,640 --> 00:05:14,420 into our database tables, 118 00:05:14,420 --> 00:05:18,040 how we can then read data from those database tables, 119 00:05:18,040 --> 00:05:19,710 including filtering 120 00:05:19,710 --> 00:05:23,460 so that we maybe don't read all the records in a database, 121 00:05:23,460 --> 00:05:27,323 but only certain records that meet a certain condition. 122 00:05:28,210 --> 00:05:30,217 We will also learn how we can update data 123 00:05:30,217 --> 00:05:31,800 in the database tables. 124 00:05:31,800 --> 00:05:34,540 And of course also, how we can delete data 125 00:05:34,540 --> 00:05:35,850 in our database tables, 126 00:05:35,850 --> 00:05:38,590 and therefore, all these CRUD operations 127 00:05:38,590 --> 00:05:42,020 will be covered in this course section. 128 00:05:42,020 --> 00:05:42,853 And hence, 129 00:05:42,853 --> 00:05:46,290 let's now start with setting up and with installing 130 00:05:46,290 --> 00:05:48,040 our SQL database system. 131 00:05:48,040 --> 00:05:49,020 And let's first of all, 132 00:05:49,020 --> 00:05:51,393 understand which options we have there.