1
00:00:00,600 --> 00:00:02,910
So we made it through all of that crud.

2
00:00:03,810 --> 00:00:04,950
It's sort of a joke.

3
00:00:05,460 --> 00:00:10,680
Anyways, we're now talking about string functions, so basically we've seen how to interact with our

4
00:00:10,680 --> 00:00:14,850
database, create stuff, insert it, update, delete all of that.

5
00:00:15,150 --> 00:00:20,910
But now we're going to focus more on select or reading the data out, because what we've been doing

6
00:00:20,910 --> 00:00:23,640
right now is just reading the data directly as it is.

7
00:00:23,640 --> 00:00:29,610
So I could say I select breed and name from cats and that's it.

8
00:00:29,610 --> 00:00:31,320
I get it back and that's great.

9
00:00:31,320 --> 00:00:34,320
But there are things that I could do with that data.

10
00:00:34,560 --> 00:00:40,980
So with numbers I might want to add them together or I might want to, I don't know, reverse a string

11
00:00:40,980 --> 00:00:47,370
or replace all spaces with a certain character or conjoin first name and last name or conjoin.

12
00:00:47,820 --> 00:00:49,440
Two pieces of data together.

13
00:00:49,680 --> 00:00:55,530
All of these are different operations, different ways of printing our data out that we can do.

14
00:00:55,530 --> 00:00:58,230
And so we're going to start focusing on them in this section.

15
00:00:58,230 --> 00:01:00,240
We're talking about string functions.

16
00:01:00,240 --> 00:01:02,280
So there's going to be a bunch of things that we see.

17
00:01:02,310 --> 00:01:03,540
I already mentioned some of them.

18
00:01:03,630 --> 00:01:09,330
We can concatenate words, we can take certain parts of a string and leave other parts out.

19
00:01:09,330 --> 00:01:13,410
We can reverse, we can uppercase, lowercase, replace characters, all sorts of things.

20
00:01:13,410 --> 00:01:19,140
But before we get there, the first thing I want to do is show you something that I think is pretty

21
00:01:19,140 --> 00:01:22,110
useful that we'll use pretty often from here on out.

22
00:01:22,290 --> 00:01:28,800
So up until this point, all of the SQL that we've been writing has been manually entered into the command

23
00:01:28,800 --> 00:01:30,510
line into the CLI here.

24
00:01:30,780 --> 00:01:36,960
So if I want to create a table, I have to type create table and then the name of the table cat.

25
00:01:37,170 --> 00:01:38,490
But let's say that I mess it up.

26
00:01:38,490 --> 00:01:45,240
So let's say I typed singular cat, which technically isn't a problem per se, but it's a good idea

27
00:01:45,240 --> 00:01:47,220
to have things pluralist it won't break.

28
00:01:47,220 --> 00:01:55,680
But so let's say I don't notice for a little bit though so I create a table cat and we want name is

29
00:01:55,680 --> 00:02:03,900
bar char 100 age is ten what am I doing ages int just like that.

30
00:02:03,900 --> 00:02:05,520
Let's say we leave it at that.

31
00:02:06,330 --> 00:02:12,030
Now this isn't a great example because this is a short line, but if I messed it up, I have to go all

32
00:02:12,030 --> 00:02:14,250
the way back here and edit it.

33
00:02:14,250 --> 00:02:14,640
Right.

34
00:02:14,640 --> 00:02:17,610
And do cats like this?

35
00:02:18,060 --> 00:02:24,420
But let's now suppose that I have something even longer and I want to put it on separate lines and format

36
00:02:24,420 --> 00:02:26,760
it nicely like this great table cats.

37
00:02:27,780 --> 00:02:35,940
And then we have parentheses there and we'll do name is bar chart and let's say I misspell this like

38
00:02:35,940 --> 00:02:40,110
that and age is an int.

39
00:02:42,060 --> 00:02:45,030
And then I realized, oh shoot, I totally messed up.

40
00:02:45,120 --> 00:02:47,820
I don't have a way of getting back there.

41
00:02:47,970 --> 00:02:49,890
I don't have an easy way of getting here.

42
00:02:50,220 --> 00:02:52,290
So I have to retype the whole line or copy it.

43
00:02:52,290 --> 00:02:54,120
There's no simple way of editing it.

44
00:02:54,120 --> 00:03:01,950
So what I want to show you is that we can run code from a file and that's really, really useful running

45
00:03:01,950 --> 00:03:04,110
SQL files that we're going to talk about here.

46
00:03:04,440 --> 00:03:09,210
So basically by the end, we will be working with files that look like this.

47
00:03:10,140 --> 00:03:15,870
They're nicely formatted, they have nice syntax highlighting, we can edit them, make changes and

48
00:03:15,870 --> 00:03:21,030
so on, and run them when we're ready rather than typing things manually into the command line.

49
00:03:21,060 --> 00:03:26,850
That doesn't mean that we won't be typing things here because it's a very useful tool.

50
00:03:27,090 --> 00:03:27,780
Oh, go away.

51
00:03:27,780 --> 00:03:28,380
Sorry.

52
00:03:29,310 --> 00:03:30,960
It's a very useful tool.

53
00:03:31,260 --> 00:03:32,430
I should probably edit that out.

54
00:03:32,430 --> 00:03:38,610
Well, anyways, it's a useful tool that allows us to explore things, try things out.

55
00:03:38,610 --> 00:03:40,410
If they're short lines, it's great.

56
00:03:40,860 --> 00:03:45,390
But if we're really trying to do more serious stuff, it's much easier and better to work in a file.

57
00:03:45,420 --> 00:03:47,610
You can make edits, you can share it with people.

58
00:03:47,610 --> 00:03:48,960
So that's what we're going to do.

59
00:03:49,350 --> 00:03:56,880
So let's go ahead, let me get out of this and I'll just hit enter and get an error and that's fine.

60
00:03:57,690 --> 00:04:05,010
So right now I'm in Kat's database, and if I do show tables, we don't Oh, I do have a cats table,

61
00:04:05,010 --> 00:04:06,780
but if I look at it, it's empty.

62
00:04:06,780 --> 00:04:10,980
So what I'm actually going to do is drop that cat's table, drop table cat.

63
00:04:12,780 --> 00:04:15,300
So what I want to do is remake the cat's table.

64
00:04:15,540 --> 00:04:21,510
And what I could do is what I just did here, create table cat and do the whole thing.

65
00:04:21,779 --> 00:04:24,210
But rather than that, I'm going to do it in a file.

66
00:04:24,210 --> 00:04:28,770
And then all I'll have to do here is run that file, execute it with one short line.

67
00:04:29,400 --> 00:04:31,890
So up here I have a new file again.

68
00:04:31,890 --> 00:04:33,540
You can do file, new file.

69
00:04:33,540 --> 00:04:35,640
You can use the shortcut shown here.

70
00:04:35,820 --> 00:04:39,270
You can right click and do new file as well.

71
00:04:39,690 --> 00:04:43,950
Whatever we do though, we need a new file and I'm just going to put something in there.

72
00:04:43,950 --> 00:04:51,360
So create table and I am showing you creating a table, but we'll be putting any SQL query in here,

73
00:04:51,360 --> 00:04:56,280
creating a table, dropping tables, inserting data, selecting any sort of query we want.

74
00:04:56,280 --> 00:04:59,940
So create table cats and it's so easy now to format it nicely.

75
00:05:00,770 --> 00:05:05,960
We'll have name, which is var cha 100.

76
00:05:06,080 --> 00:05:07,550
Let's do an ID as well.

77
00:05:08,120 --> 00:05:09,440
See how easy that was.

78
00:05:09,560 --> 00:05:13,100
If I made a mistake, cat id will be an int.

79
00:05:13,460 --> 00:05:18,020
Not no auto increment comma.

80
00:05:19,010 --> 00:05:21,170
Name var chart and then we'll do.

81
00:05:21,170 --> 00:05:26,180
Age is an int and we'll do primary key is cat id.

82
00:05:26,540 --> 00:05:28,160
So this should all be review.

83
00:05:28,970 --> 00:05:31,910
But what's nice is I have this query like this.

84
00:05:32,150 --> 00:05:33,740
I need to save the file.

85
00:05:34,790 --> 00:05:40,460
So I did command s, but you can also go file save and we need to give it a name.

86
00:05:40,460 --> 00:05:41,400
Any name will do.

87
00:05:41,420 --> 00:05:43,460
I'll just call it first file.

88
00:05:43,460 --> 00:05:46,250
And this is the most important part, SQL.

89
00:05:46,520 --> 00:05:49,810
So that's what identifies the contents of the file as SQL code.

90
00:05:49,820 --> 00:05:52,100
So we need that click save.

91
00:05:52,370 --> 00:05:53,780
Make sure it changed up there.

92
00:05:53,960 --> 00:05:58,460
Also, you can see we got nice syntax highlighting now, which is also very useful.

93
00:05:59,810 --> 00:06:02,630
So I'm sure by now you can see the value in doing this.

94
00:06:02,630 --> 00:06:06,080
You have a history of your work, you can edit it, share it with people.

95
00:06:06,080 --> 00:06:07,550
It's just a much better way of doing it.

96
00:06:07,550 --> 00:06:09,200
So how do we actually run it?

97
00:06:09,980 --> 00:06:14,990
Well, this is the magic source and then a file name.

98
00:06:15,410 --> 00:06:18,590
So in our case, our file name is first file SQL.

99
00:06:18,590 --> 00:06:21,050
So we just need source file name.

100
00:06:21,350 --> 00:06:22,010
SQL.

101
00:06:23,000 --> 00:06:25,130
So I'm going to actually quit out of the class.

102
00:06:25,610 --> 00:06:33,830
And the first thing I want to show you is that it matters where you are when you initialize your CLI.

103
00:06:34,370 --> 00:06:39,500
So when I type LZ here and if you're not familiar with that, it just list the current files.

104
00:06:39,500 --> 00:06:44,000
So what this shows me is that I have book data, first file and problems.

105
00:06:44,180 --> 00:06:46,670
All right, so we want first file.

106
00:06:46,700 --> 00:06:48,200
Don't worry about these other two.

107
00:06:48,380 --> 00:06:51,830
So we are in the correct directory to access that file.

108
00:06:51,830 --> 00:06:57,590
So if I start up the CLI with my SQL Ctrl space cli.

109
00:06:59,040 --> 00:07:02,670
And I use the cat app database just like that.

110
00:07:02,700 --> 00:07:03,810
I'll make some space.

111
00:07:04,140 --> 00:07:10,530
Now, all I have to do is source first file dot, SQL, just like that.

112
00:07:10,950 --> 00:07:12,150
And we'll see what happens.

113
00:07:12,780 --> 00:07:14,130
It says Query OC.

114
00:07:14,250 --> 00:07:15,570
So now how do we know what works?

115
00:07:15,570 --> 00:07:20,640
Well, we could do a describe cat and here's our table.

116
00:07:21,600 --> 00:07:24,420
So that's all that there is to actually executing the file.

117
00:07:24,420 --> 00:07:26,250
It's just source and the file name.

118
00:07:26,430 --> 00:07:28,620
But what I want to show you two things.

119
00:07:28,710 --> 00:07:30,510
I'll go ahead and make a new directory.

120
00:07:30,750 --> 00:07:36,090
You don't have to do this, but I'm just going to call it testing.

121
00:07:36,360 --> 00:07:39,360
And then instead of testing, I'll make a new file.

122
00:07:39,870 --> 00:07:46,390
And what I'll put inside of that file is a select statement select star from Cat.

123
00:07:47,520 --> 00:07:55,830
Actually, let's do an insert insert into cats and we'll just do what do we have here?

124
00:07:55,860 --> 00:08:00,750
Name and age, which I'm just realizing and capitalized.

125
00:08:01,020 --> 00:08:05,010
So we could obviously go back and change that here and rerun it, but we'll leave it.

126
00:08:05,490 --> 00:08:09,450
So insert into cat's name and age just like that.

127
00:08:11,780 --> 00:08:22,640
Values and name will be, let's do Charlie and age will be let's do an old cat 17.

128
00:08:23,720 --> 00:08:24,890
Save the file.

129
00:08:27,070 --> 00:08:28,430
And we'll do file.

130
00:08:28,450 --> 00:08:29,170
Save.

131
00:08:30,970 --> 00:08:33,429
And make sure the file has SQL in it.

132
00:08:33,850 --> 00:08:39,070
So we'll do rename and I'll just call this insert dot SQL.

133
00:08:41,590 --> 00:08:44,200
Let's actually just to show you, we can do more than one thing at a time.

134
00:08:44,200 --> 00:08:47,920
Let's insert both or let's insert two and this one will be called.

135
00:08:48,760 --> 00:08:50,450
I think we already have a Cyndi.

136
00:08:50,470 --> 00:08:54,670
Let's do a Connie who is ten.

137
00:08:55,810 --> 00:08:59,830
And then at the end we'll also do select star from Cats just like that.

138
00:09:00,070 --> 00:09:04,060
So these three things will be executed at once if we do it correctly.

139
00:09:04,240 --> 00:09:08,560
So now to reference this file, our CLI is running in this folder.

140
00:09:08,710 --> 00:09:11,440
So we can't just say insert SQL.

141
00:09:11,680 --> 00:09:17,770
If we did that source insert SQL, it tells us it can't find that file.

142
00:09:19,030 --> 00:09:27,760
So what we need to do is do a source testing slash, insert SQL, and there we go.

143
00:09:28,360 --> 00:09:31,540
So that's important that you have to access the correct path.

144
00:09:31,540 --> 00:09:34,750
You have to refer to this file by its full path to get there.

145
00:09:34,900 --> 00:09:39,610
So if you're in this folder, all that we can access are these.

146
00:09:40,450 --> 00:09:43,690
But if we drill to this folder, then we have access.

147
00:09:43,690 --> 00:09:47,050
And obviously you could extrapolate that to as many layers as you need.

148
00:09:48,160 --> 00:09:50,300
So now let's make sure it worked.

149
00:09:50,320 --> 00:09:53,220
You can see after it worked, we had query.

150
00:09:53,230 --> 00:09:53,540
Okay.

151
00:09:53,560 --> 00:09:54,580
One row affected.

152
00:09:54,580 --> 00:09:56,410
That was our first insert.

153
00:09:56,410 --> 00:09:58,690
And then the same thing, our second insert.

154
00:09:58,690 --> 00:10:01,690
And then our select statement gave us this right here.

155
00:10:02,380 --> 00:10:04,690
So that's all there is to run in code from a file.

156
00:10:05,170 --> 00:10:06,550
It's really, really useful.

157
00:10:06,550 --> 00:10:08,470
We'll be doing it pretty much non-stop.

158
00:10:09,070 --> 00:10:14,230
And the first thing that we're going to do is actually get some new data into our database using this

159
00:10:14,230 --> 00:10:15,220
in the next video.