WEBVTT 00:00.180 --> 00:03.850 All right I mentioned I wanted to get us writing some code as soon as possible. 00:03.870 --> 00:05.850 So that's what we're going to do in this video. 00:05.880 --> 00:11.270 Obviously it's super early on in the course so don't get your hopes up for doing anything crazy impressive. 00:11.280 --> 00:12.630 It's going to be pretty basic. 00:12.630 --> 00:16.170 But the idea is that I just want to introduce you to writing some sequel. 00:16.170 --> 00:17.440 Show you what it's like. 00:17.460 --> 00:21.990 So the first thing you'll need to do is click on this link if you want to open the slides I've included 00:21.990 --> 00:27.580 them with this lecture or I've included the link itself as a resource with this lecture. 00:27.690 --> 00:33.330 So however you would like to get there go to this link which takes us to this page right here. 00:33.510 --> 00:38.790 This is the tri sequel Ed. And I'm going to make my screen a little bit bigger just so that you can 00:38.790 --> 00:39.380 see it. 00:39.390 --> 00:42.480 Here we go and I'll start by just deleting what's already in there. 00:42.870 --> 00:44.650 So you might be wondering what is this place. 00:44.670 --> 00:45.940 That's a good question. 00:45.960 --> 00:48.760 It's an interactive browser based sequel environment. 00:48.840 --> 00:53.910 And what that really means is that think of it as your own personal sequel sandbox in the browser where 00:53.910 --> 00:56.010 you can go try some things out and play around. 00:56.010 --> 00:57.770 It's purely for educational purposes. 00:57.780 --> 01:02.350 So this is not what you would be doing if you were actually working with a real production database. 01:02.370 --> 01:07.200 But what it lets you do is type some sequel queries and run them by clicking this run sequel button 01:07.470 --> 01:08.520 and you'll get a result. 01:08.730 --> 01:11.690 And that's fundamentally the process of working with sequel. 01:11.790 --> 01:14.740 You type something and then you run it and then you get something back. 01:14.970 --> 01:20.220 Oh and before we go any further I should point out that we're talking about sequel here not MY as well 01:20.220 --> 01:20.640 yet. 01:20.820 --> 01:24.510 And if you're confused about that distinction totally normal question to have. 01:24.510 --> 01:27.760 Wait until the next section when I go into detail about the differences. 01:27.780 --> 01:30.150 But for now I'd like you to try running some code. 01:30.390 --> 01:33.430 So here's the first line of sequel that we can try running. 01:33.630 --> 01:39.030 So like star from customers so you can either type this or copy it from the slides or from the included 01:39.030 --> 01:39.750 code file. 01:39.750 --> 01:42.850 After this lecture so I'll just copy it from the slides here. 01:43.840 --> 01:47.620 And then back in the editor I'll paste it in and click run. 01:47.830 --> 01:51.300 So the first thing you'll notice is that we get a bunch of data back. 01:51.400 --> 01:55.740 So I'm not going to go into the details of the syntax and what all this means for every command we run. 01:55.750 --> 01:59.240 It's really more about just giving you an experience of running commands. 01:59.380 --> 02:02.080 The rest of the Course talks about the details and the syntax. 02:02.140 --> 02:08.710 But what this does is it gives us all of the data about customers in this database so you can see we've 02:08.710 --> 02:14.010 got 91 customers and every customer here has a name. 02:14.020 --> 02:16.580 So these are stores or restaurants. 02:16.690 --> 02:23.520 There's the contact at the restaurant or the business the address the city the postal code and the country. 02:23.920 --> 02:24.260 OK. 02:24.280 --> 02:25.680 So that's all I'll say about that. 02:25.690 --> 02:26.870 So let's start from customers. 02:26.890 --> 02:31.070 That gives us all of our customers data that are in the database right now. 02:31.090 --> 02:32.500 So here's another thing we could try. 02:32.500 --> 02:33.440 It's very similar. 02:33.610 --> 02:36.280 Select start from orders. 02:36.280 --> 02:42.220 So we'll just copy that and back here in the editor I'll paste it in and click run as well. 02:42.250 --> 02:48.820 Non-sequel same thing happens but this time we get all of our data printed out for our orders. 02:48.820 --> 02:51.460 So you might be wondering where is this data coming from in the first place. 02:51.460 --> 02:52.580 Very good question. 02:52.840 --> 02:54.520 It's preprogramed in here. 02:54.550 --> 02:58.600 So every time you open this in your browser you have this preloaded data set that you can work with 02:59.110 --> 03:04.600 and just by taking a quick look at our order data you can see we have things like an order ID a customer 03:04.600 --> 03:10.540 id an employee ID an order date and a shipper ID but there's really not much descriptive information 03:10.540 --> 03:10.910 here. 03:10.960 --> 03:13.010 And that's actually why I chose this table. 03:13.060 --> 03:18.070 I wanted to make the point that a lot of the data you'll be working with on its own are like a snapshot 03:18.100 --> 03:20.060 like this isn't very meaningful. 03:20.230 --> 03:23.380 What we actually want to do is start combining it with other tables. 03:23.380 --> 03:27.190 So we're not going to do that now but that's what we're building up towards the end of the course is 03:27.190 --> 03:32.260 how do I combine customers with orders and products and shippers to get some meaningful answers out 03:32.260 --> 03:33.240 of my data. 03:33.250 --> 03:34.760 So here's another thing we can do. 03:34.990 --> 03:38.320 Very similar to what we've done already but there's a little bit extra. 03:38.440 --> 03:43.700 So select start from products ordered by price descending DGSE. 03:43.780 --> 03:49.540 And if I paste that in here and click run what I get now are all of the products in my database. 03:49.540 --> 03:56.190 So they have a product ID name a supplier ID category ID a unit and then or price. 03:56.350 --> 04:01.030 And they're currently sorted by most expensive down to the cheapest all the way at the end. 04:01.180 --> 04:05.470 So I won't force you to listen to me trying to pronounce some of these but these are our products and 04:05.470 --> 04:07.020 they're currently sorted by price. 04:07.060 --> 04:11.150 So just a simple modification to the query yields radically different results. 04:11.170 --> 04:16.900 And if we just get rid of that order by price we get the same data back but now it's ordered by product 04:16.930 --> 04:18.840 id rather than the price. 04:19.240 --> 04:19.610 Okay. 04:19.630 --> 04:23.420 And stepping up in complexity a little bit here here's another query. 04:23.500 --> 04:28.480 This one is significantly longer and this one actually involves two different entities. 04:28.480 --> 04:31.120 So we saw customers we saw orders separately. 04:31.120 --> 04:38.470 If you try copying this and pasting it in and you clicked run what we actually get here is a list of 04:38.470 --> 04:44.260 records for a customer and then the number of orders a given customer has placed. 04:44.260 --> 04:47.080 So remember this data is stored in two separate places. 04:47.140 --> 04:50.210 We had the customers data that had nothing to do with orders. 04:50.290 --> 04:56.110 And then we had the orders data and all that it contained was customer ID and then we combined them 04:56.260 --> 04:57.380 with this query. 04:57.520 --> 04:59.380 So do not focus on the syntax. 04:59.380 --> 05:00.550 Don't panic about that. 05:00.550 --> 05:01.950 Of course we'll get there. 05:01.960 --> 05:03.940 We'll talk a lot more about how to structure queries. 05:03.940 --> 05:07.840 I mean most of this Course is about structuring complex queries. 05:07.840 --> 05:10.590 In fact here's a quick preview of some of the things we'll be doing. 05:10.720 --> 05:13.810 So here's an example of a slightly more complicated query. 05:13.960 --> 05:16.880 This time we're working with photos and images. 05:17.020 --> 05:22.080 We're working with users and likes and putting them all together to get some information out. 05:23.050 --> 05:29.230 And here's another one little bit crazier where we're working with ratings or reviews and TV shows and 05:29.230 --> 05:29.890 reviewers. 05:29.890 --> 05:36.250 So think of like a Netflix or iTunes where you can rent or watch a TV show and then rate it from one 05:36.250 --> 05:39.400 to 10 or a to five for whatever the rating scale is. 05:39.580 --> 05:44.410 Here we're working with that data to figure out who our power users are who are the most active users 05:44.410 --> 05:46.290 who are reviewing the most things. 05:46.450 --> 05:49.440 So we'll be building up to these sort of queries by the end of the course. 05:49.540 --> 05:52.440 You should be able to make sense of this and do a lot more by the way. 05:52.640 --> 05:52.990 OK. 05:53.020 --> 05:54.610 So that was a taste of sequel. 05:54.610 --> 05:56.150 Hopefully it didn't taste too bad. 05:56.290 --> 06:00.690 So in the next section we'll dive into the nitty gritty of what sequel is and how it compares to my 06:00.690 --> 06:00.830 ass. 06:00.830 --> 06:05.660 Q Well we'll get everything installed and then finally we'll have our first set of exercises.