1
00:00:00,090 --> 00:00:00,540
All right.

2
00:00:00,540 --> 00:00:06,689
So the next thing I always like to do is start off with some SQL as soon as possible, because the next

3
00:00:06,689 --> 00:00:13,790
couple of videos after this are very much focused on talking about high level concepts like what is

4
00:00:13,800 --> 00:00:17,610
equal, what is my SQL, how do they compare, what are other database types?

5
00:00:17,610 --> 00:00:24,000
And then things like installation for Mac versus Windows and installing a graphical interface, installing

6
00:00:24,000 --> 00:00:25,230
a command line client.

7
00:00:25,230 --> 00:00:27,840
And then finally we can start writing code.

8
00:00:27,840 --> 00:00:34,080
So I feel like it's a bit of a slog if we don't at least start with some basic SQL right away.

9
00:00:34,080 --> 00:00:35,340
So that's what we're going to do here.

10
00:00:35,340 --> 00:00:37,020
You're not going to learn all that much.

11
00:00:37,020 --> 00:00:40,170
We're not going to talk about the syntax, but we're going to play around with it.

12
00:00:40,170 --> 00:00:41,760
You don't have to install anything.

13
00:00:41,760 --> 00:00:44,520
In fact, there is a link I'd like you to click.

14
00:00:45,030 --> 00:00:46,710
You don't have to open the slides to get this link.

15
00:00:46,740 --> 00:00:49,980
You can just find the link attached to this video as a resource.

16
00:00:49,980 --> 00:00:53,520
It will take you to this web page full of lovely ads.

17
00:00:53,520 --> 00:00:59,580
This is a website that allows us to run some basic SQL code in the browser, so we're going to install

18
00:00:59,580 --> 00:01:00,990
my SQL locally.

19
00:01:01,260 --> 00:01:06,120
It can do a lot more than we can do in the browser, but there's a database already set up here that

20
00:01:06,120 --> 00:01:07,470
has some data in IT.

21
00:01:07,500 --> 00:01:09,000
Customers and categories.

22
00:01:09,000 --> 00:01:14,340
Employees order details, orders, product shippers, suppliers, and we can interact with that data

23
00:01:14,340 --> 00:01:16,350
by writing some basic queries.

24
00:01:16,350 --> 00:01:17,580
And that's the format.

25
00:01:17,580 --> 00:01:19,380
That's how we work with SQL.

26
00:01:19,410 --> 00:01:24,930
We write these queries, they might look like this or way longer, and then we execute them and we'll

27
00:01:24,930 --> 00:01:25,860
get some results.

28
00:01:25,860 --> 00:01:30,780
So if I click the Run SQL run SQL button, here are the results.

29
00:01:30,780 --> 00:01:32,880
And this data is just dummy data that's in here.

30
00:01:32,880 --> 00:01:40,050
I don't think it's real customer data, but you can see we are using a command called Select Star from

31
00:01:40,050 --> 00:01:40,560
customers.

32
00:01:40,560 --> 00:01:45,120
Again, we're not going to worry about what this all means, but if you read it, maybe you can figure

33
00:01:45,120 --> 00:01:45,720
it out, right?

34
00:01:45,720 --> 00:01:50,430
Select customers Where city is equal to London or country is equal to UK.

35
00:01:51,000 --> 00:01:56,610
Okay, so we're finding people who live in London or generally in the UK, and if you're having trouble

36
00:01:56,610 --> 00:02:00,810
running this, make sure you're using a modern browser like Chrome or Firefox.

37
00:02:01,200 --> 00:02:02,880
Opera I think works as well.

38
00:02:03,060 --> 00:02:04,410
I would just use Chrome though.

39
00:02:04,410 --> 00:02:05,130
That's what I'm using.

40
00:02:05,130 --> 00:02:07,680
And if it doesn't work at all, it's really not a big deal.

41
00:02:07,680 --> 00:02:10,889
We're using this for 5 minutes in this one video and then never again.

42
00:02:11,730 --> 00:02:16,710
So we can also do something simpler, like look at some of these other tables on the right here.

43
00:02:16,710 --> 00:02:18,600
These are all different tables that we have.

44
00:02:18,600 --> 00:02:20,460
Let's look at products.

45
00:02:20,460 --> 00:02:24,960
So the command would be select star from.

46
00:02:24,960 --> 00:02:29,070
And you don't even have to capitalize it the same as me, but I'm going to do it.

47
00:02:29,070 --> 00:02:31,230
It's easier to kind of read and differentiate.

48
00:02:31,890 --> 00:02:35,550
And down here you'll see we have 77 products that we get.

49
00:02:36,420 --> 00:02:43,560
I can do things like reorder, so I can get all my products and then order them by the price in descending

50
00:02:43,560 --> 00:02:44,100
order.

51
00:02:45,000 --> 00:02:46,470
Order by price.

52
00:02:46,470 --> 00:02:53,040
You can see price is one of these values, what we call a column order by price in descending order,

53
00:02:53,040 --> 00:03:00,300
DSC And the highest priced item is, I think this is some sort of Bordeaux or bottle of.

54
00:03:00,300 --> 00:03:01,530
Yeah, I think it's Bordeaux.

55
00:03:01,740 --> 00:03:04,350
And then, I don't know, we got sausages.

56
00:03:04,350 --> 00:03:09,030
50 bags of 30 sausages is $123 or hundred and 23.

57
00:03:09,030 --> 00:03:13,410
Whatever the currency is here, we can change that order.

58
00:03:14,230 --> 00:03:19,600
And you'll see the cheapest items are here are two and a half, four and one half, six, seven, all

59
00:03:19,600 --> 00:03:21,940
the way up to 263.

60
00:03:23,320 --> 00:03:25,260
But we can even get more complicated.

61
00:03:25,270 --> 00:03:30,580
We can do things like this query here, which if you want to run this, don't type it, don't worry

62
00:03:30,580 --> 00:03:31,210
about it.

63
00:03:31,750 --> 00:03:35,650
You can either watch me do it or just copy it from this video or from the slides.

64
00:03:35,650 --> 00:03:38,860
I'll click the copy button and I'm going to run this.

65
00:03:39,010 --> 00:03:40,900
And this is obviously more complicated.

66
00:03:40,900 --> 00:03:42,850
It has more lines involved.

67
00:03:42,910 --> 00:03:46,240
So you can I make this larger here does not appear that way.

68
00:03:46,240 --> 00:03:48,160
So we'll dissect it here.

69
00:03:48,190 --> 00:03:52,840
We're looking to get the customer name and then we're counting something, counting star, whatever

70
00:03:52,840 --> 00:03:55,510
that means from the customer's table.

71
00:03:55,510 --> 00:03:59,140
But we're doing this thing called a join inner join orders.

72
00:03:59,140 --> 00:04:04,780
We're basically combining information from the customers table and we're combining that with information

73
00:04:04,780 --> 00:04:06,100
from the orders table.

74
00:04:06,100 --> 00:04:11,200
And that way we're able to figure out how many orders each customer made and who made the most.

75
00:04:11,200 --> 00:04:15,460
So this guy, Ernst Handle Handle has made ten orders.

76
00:04:15,580 --> 00:04:18,910
And then down here we have a lot of people who've only made one order.

77
00:04:19,329 --> 00:04:23,020
So we're also sorting once again by the number of orders that they've made.

78
00:04:23,980 --> 00:04:30,040
So we're going to go towards something like this later on in the course where we have a bunch of different

79
00:04:30,040 --> 00:04:30,430
tables.

80
00:04:30,430 --> 00:04:32,260
So we got photos and likes.

81
00:04:32,260 --> 00:04:35,200
We're working with hashtags and some of our selections.

82
00:04:35,200 --> 00:04:37,300
We're working with users.

83
00:04:37,690 --> 00:04:41,980
This is sort of an Instagram clone database that we'll be working with later on.

84
00:04:41,980 --> 00:04:43,660
But we've got a lot of stuff going on.

85
00:04:43,660 --> 00:04:50,140
We have select, we have count, we have inner join inner join group by order, buy limit.

86
00:04:50,140 --> 00:04:52,900
These are all things that we are going to learn throughout the course.

87
00:04:52,900 --> 00:04:58,360
So my point in this video is not to overwhelm you with all this syntax, but just to show you this basic

88
00:04:58,360 --> 00:05:03,820
process of interacting with the database, even if it is in a browser filled with ads on the sides.

89
00:05:03,820 --> 00:05:04,810
Hot topic ads.

90
00:05:04,810 --> 00:05:06,300
Wow, I haven't seen those in a while.

91
00:05:07,150 --> 00:05:08,140
It doesn't really matter.

92
00:05:08,140 --> 00:05:09,550
The process is the same.

93
00:05:09,550 --> 00:05:12,730
We'll be typing our code in an editor like this.

94
00:05:12,730 --> 00:05:15,610
We're all typed some query and then I'll execute the query.

95
00:05:15,610 --> 00:05:20,470
Or maybe from my terminal I'll execute the query, but we're going to get results back.

96
00:05:20,470 --> 00:05:23,260
Either way, they just look slightly different in the browser.

97
00:05:23,860 --> 00:05:28,060
Okay, So as I said, we're going to do some more complicated things later on.

98
00:05:28,060 --> 00:05:31,090
Even things like this here we've got a bunch of stuff happening.

99
00:05:31,090 --> 00:05:37,180
If null round case when count count left, join here.

100
00:05:37,180 --> 00:05:38,200
There was an inner join.

101
00:05:38,200 --> 00:05:41,590
We're going to learn all about joints, all of this stuff grouped by.

102
00:05:41,620 --> 00:05:43,600
There's a lot that we have to cover.

103
00:05:43,600 --> 00:05:48,790
But the core mechanic, as I've mentioned many times in this video, is we write some sort of query.

104
00:05:48,790 --> 00:05:53,950
We want to find something out from a database and then we run that query and we hopefully get a result.

105
00:05:54,070 --> 00:05:55,800
Of course, we could get an error, right?

106
00:05:55,810 --> 00:06:01,540
If I do something that doesn't exist, like let's simplify this, let's select Star, let's look at

107
00:06:01,540 --> 00:06:08,230
the shippers table, shippers, some sort of shippers information.

108
00:06:08,470 --> 00:06:13,270
Well, if I mess up the spelling shippers instead, I'll get an error.

109
00:06:13,270 --> 00:06:15,220
This time the error is in the browser.

110
00:06:15,250 --> 00:06:16,420
It's just a little pop up here.

111
00:06:16,420 --> 00:06:18,010
It says you could not run that statement.

112
00:06:18,010 --> 00:06:19,450
I don't know what you're talking about.

113
00:06:19,450 --> 00:06:24,400
So like any programming language or technical language, we have to be precise.

114
00:06:24,400 --> 00:06:26,860
We have to use the correct spelling and formatting.

115
00:06:26,860 --> 00:06:29,770
But that's what this course is here to teach you.

116
00:06:30,340 --> 00:06:35,260
So finally, instead of doing select, which is what we've been doing over and over and over, and we're

117
00:06:35,260 --> 00:06:36,820
going to learn what select really means.

118
00:06:36,820 --> 00:06:39,250
But basically it's retrieving information.

119
00:06:39,250 --> 00:06:42,190
Instead of doing that, we can do something different.

120
00:06:42,190 --> 00:06:45,220
We can delete, we can drop a whole table.

121
00:06:45,250 --> 00:06:51,880
Now, when I run this command drop table customers, just to remind you, if I do select star from customers

122
00:06:51,880 --> 00:06:56,950
and you can do this along with me, I'm getting 91 different customer records.

123
00:06:57,790 --> 00:07:03,400
And if I instead say drop table customers, they're all going to go away.

124
00:07:04,590 --> 00:07:05,300
Okay.

125
00:07:05,370 --> 00:07:07,370
It tells me you've made changes to the database.

126
00:07:07,380 --> 00:07:08,720
How do we know that it worked?

127
00:07:08,730 --> 00:07:11,550
Well, if I try and select star from customers again.

128
00:07:13,460 --> 00:07:13,970
Oh, no.

129
00:07:13,970 --> 00:07:14,630
I get an error.

130
00:07:14,630 --> 00:07:16,900
It says no such table customers.

131
00:07:16,910 --> 00:07:17,810
It's gone.

132
00:07:18,050 --> 00:07:21,710
So just to show there are other commands, it's not all about retrieving information.

133
00:07:21,710 --> 00:07:24,320
We can update, we can add or insert information.

134
00:07:24,320 --> 00:07:28,250
We can drop delete tables or individual entries.

135
00:07:28,250 --> 00:07:33,530
And then if you do want to keep exploring this little data set here in the browser, you can click restore

136
00:07:33,530 --> 00:07:34,340
database.

137
00:07:35,400 --> 00:07:37,530
And now we should have customers again.

138
00:07:37,770 --> 00:07:40,240
And we do so they're not gone forever.

139
00:07:40,260 --> 00:07:44,010
But in the real world, if you do drop a table, it often is gone forever.

140
00:07:44,010 --> 00:07:46,350
And that's the thing we'll discuss when we get to that section.

141
00:07:46,980 --> 00:07:49,590
So that is our quick taste of my SQL.

142
00:07:49,650 --> 00:07:53,250
We can run queries, get results, those queries can do different things.

143
00:07:53,250 --> 00:08:00,660
We can ask to find information, find the highest selling item in our database, or delete the lowest

144
00:08:00,660 --> 00:08:04,050
selling items, or add a new item or update an item to price.

145
00:08:04,050 --> 00:08:08,280
We can interact with the database via these queries that we write and then execute.

146
00:08:08,520 --> 00:08:14,580
So the next thing we have to do, unfortunately is talk about installation, talk about high level questions

147
00:08:14,580 --> 00:08:20,730
around SQL in general versus my SQL, talk about different types of databases, but we'll keep it brief

148
00:08:20,730 --> 00:08:22,950
so that we can get on to the code as soon as possible.