WEBVTT

1
00:00:01.810 --> 00:00:06.279
so we got the computer setup so that we
can use sql lite from the command

2
00:00:06.279 --> 00:00:10.209
line we're going to have a look at how
to create databases and tables and start

3
00:00:10.209 --> 00:00:14.920
to see how the sql language is used
so we'll be using sql lite in our

4
00:00:14.920 --> 00:00:19.390
programs later but for now we're just
going to focus on the sql language so

5
00:00:19.390 --> 00:00:22.749
that we can concentrate on learning a
bit about sql without worrying about

6
00:00:22.749 --> 00:00:27.460
the java side of things so you need to
start a terminal session or command

7
00:00:27.460 --> 00:00:31.509
prompt on your computer on windows
click on the start menu and type command

8
00:00:31.509 --> 00:00:35.530
to launch a command prompt or you could
just use the same methods that I used

9
00:00:35.530 --> 00:00:40.030
in the setup video to start a command
prompt for Windows 10 depending on what

10
00:00:40.030 --> 00:00:44.260
version you're running on a Mac command
space and type in terminal and on

11
00:00:44.260 --> 00:00:48.940
Linux you can launch this terminal
quickly using ctrl alt t alright I'm

12
00:00:48.940 --> 00:00:52.930
on a mac as I said so I'm going to
start sql lite and the way we start

13
00:00:52.930 --> 00:00:58.180
that is by typing sql lite 3 and press enter

14
00:00:58.180 --> 00:01:04.269
so that starts the sql lite shell
program and we can also specify the name

15
00:01:04.269 --> 00:01:07.899
of a database on the command line so I'm
going to show you how to do this I'm

16
00:01:07.899 --> 00:01:15.939
going to quit out of this by typing
.quit

17
00:01:15.939 --> 00:01:19.960
as I mentioned we can specify the name
of a database on the command line so i'm

18
00:01:19.960 --> 00:01:24.340
going to call this database test so to
do that we type a space after the

19
00:01:24.340 --> 00:01:31.960
sql lite 3 program name and type test . DB and press enter incidentally

20
00:01:31.960 --> 00:01:35.950
there's also command to open a
database file if you forget to put the name

21
00:01:35.950 --> 00:01:39.999
on the command line and we'll have a
look at that a bit later you will find

22
00:01:39.999 --> 00:01:44.289
that this sql lite program is a
fairly minimal interface that just

23
00:01:44.289 --> 00:01:47.889
really tells us the version of sql lite that were using and that we can use

24
00:01:47.889 --> 00:01:52.630
. help to get some instructions we can
also enter sql statements and with

25
00:01:52.630 --> 00:01:56.229
some versions there's also a helpful
reminder that sql statement must be

26
00:01:56.229 --> 00:02:00.520
terminated with a semi colon and you
can see earlier i put a semicolon to

27
00:02:00.520 --> 00:02:05.469
finish to finish up the command quit but
the quit was meant to be a . quit which

28
00:02:05.469 --> 00:02:09.100
doesn't have a semicolon but in any
event will talk more about semicolons in

29
00:02:09.100 --> 00:02:13.180
and the need to out those in a little bit
later and it is normal that you forget

30
00:02:13.180 --> 00:02:14.610
to do this quite often

31
00:02:14.610 --> 00:02:17.850
you'll have forget to put a semicolon in
but as you say it's not the end of the

32
00:02:17.850 --> 00:02:21.120
world you can just enter the semi colon
on the next line and the statement will

33
00:02:21.120 --> 00:02:25.110
then be executed but we'll get to that
in a minute but let's start off that by

34
00:02:25.110 --> 00:02:29.520
typing . help you can see that sql lite is helpfully are telling us that

35
00:02:29.520 --> 00:02:34.710
if we type .help we'll get some help so
we'll do that . help press enter and you

36
00:02:34.710 --> 00:02:38.010
can see it's got a whole page of
information on the screen there and I'm

37
00:02:38.010 --> 00:02:40.830
just scrolling up and down with my mouse
lots of different command options

38
00:02:40.830 --> 00:02:45.930
they're available for us is well its not a lot
but really in the scheme of things it's

39
00:02:45.930 --> 00:02:49.380
not really a lot of commands they're
comparing that so to the Java language

40
00:02:49.380 --> 00:02:53.489
is a significantly higher number of
things you need to know in java than

41
00:02:53.489 --> 00:02:57.780
sql lite but even with that said you
probably won't remember them all

42
00:02:57.780 --> 00:03:01.350
straight away if ever though so .
help is a useful way to remind yourself of them

43
00:03:01.350 --> 00:03:04.980
if you ever need to go back and
see what a particular command all about

44
00:03:05.550 --> 00:03:10.200
alright so before creating a new table
in the database you want to type in .

45
00:03:10.200 --> 00:03:17.010
headers....this
actually shows the column names at the

46
00:03:17.010 --> 00:03:21.000
start of the data which is a handy
reminder of what we call the columns

47
00:03:21.510 --> 00:03:26.190
ok so those are a list of the commands
that sql lite recognizes but

48
00:03:26.190 --> 00:03:31.260
when creating and querying tables we
just use sql statements so let's

49
00:03:31.260 --> 00:03:36.450
create a simple contacts table with the
sql commander we are about the type i'm going

50
00:03:36.450 --> 00:03:46.860
to type in.....

51
00:03:46.860 --> 00:03:54.209
....

52
00:03:54.209 --> 00:04:00.090
....and i'm
going to press enter now that doesn't

53
00:04:00.090 --> 00:04:03.959
seem to do much and that's something
you'll notice on the sql lite if you

54
00:04:03.959 --> 00:04:07.650
do something wrong it will let you know
but if everything works fine then it is

55
00:04:07.650 --> 00:04:11.430
keeps quiet so this case because we
haven't got anything back other

56
00:04:11.430 --> 00:04:15.780
than the prompt asking us to type in
something else on the next line it's

57
00:04:15.780 --> 00:04:19.650
nice and quiet and it generally doesn't
mean that the command worked so in this

58
00:04:19.650 --> 00:04:24.660
case it's created that table for us that
table called contacts has three columns

59
00:04:25.360 --> 00:04:29.979
the name the phone and the email but to
sql lite doesn't tell us that it

60
00:04:29.979 --> 00:04:34.360
worked and again if it hadn't worked
would get an error but otherwise we'll

61
00:04:34.360 --> 00:04:36.699
just move on to the next instruction

62
00:04:36.699 --> 00:04:40.389
alright so with this table now let's
actually put some data into that table

63
00:04:40.389 --> 00:04:47.500
and we can use the sql insert statement
to do that so we can type....

64
00:04:47.500 --> 00:05:14.080
....

65
00:05:14.080 --> 00:05:20.560
....press enter
once again we get no confirmation that

66
00:05:20.560 --> 00:05:25.930
it worked but in this case no news is good
news and I use single quotes there but

67
00:05:25.930 --> 00:05:29.919
you can also use double quotes thing to
remember those if your embedding sql

68
00:05:29.919 --> 00:05:34.180
commands in java that makes sense to use
double quotes for the strings and single

69
00:05:34.180 --> 00:05:37.960
quotes around the sql statements and
you'll see me do that when it comes to

70
00:05:37.960 --> 00:05:40.900
creating programs that work on our
databases

71
00:05:40.900 --> 00:05:43.750
alright so how do we know that this
actually worked these commands actually

72
00:05:43.750 --> 00:05:48.370
did something we can actually check that
it has we can query the table now the

73
00:05:48.370 --> 00:05:53.500
Select statement is very useful in
sql or SQL and it's how you query the

74
00:05:53.500 --> 00:05:55.029
data at the table

75
00:05:55.029 --> 00:05:59.319
now it's a very flexible command but as
at its simplest you can just tell it

76
00:05:59.319 --> 00:06:03.759
what columns you want and the name of
the table to get it from now i'm going

77
00:06:03.759 --> 00:06:08.740
to actually type the sql reserved
words in capitals in this statement and

78
00:06:08.740 --> 00:06:12.279
it's actually useful to do that
especially in programs and scripts but to

79
00:06:12.279 --> 00:06:14.050
sql itself doesn't care

80
00:06:14.050 --> 00:06:18.279
people generally just do it to make it
obvious which other sql reserved

81
00:06:18.279 --> 00:06:23.080
words and which are things like tables
and columns names so to see whats in the contacts

82
00:06:23.080 --> 00:06:28.029
table we use this we type....

83
00:06:28.029 --> 00:06:38.120
....and press enter

84
00:06:38.120 --> 00:06:43.310
we can now see the record that we just
inserted now the asterix there by the

85
00:06:43.310 --> 00:06:47.840
way means all columns you saw me type in the Select statement we could

86
00:06:47.840 --> 00:06:51.919
have been more explicit and type
something like this....

87
00:06:51.919 --> 00:07:00.919
.....that give
us the same result and we just wanted an

88
00:07:00.919 --> 00:07:07.460
email addresses for example we could
just do select email from contacts and

89
00:07:07.460 --> 00:07:11.510
that would just give us the email
alright I'm gonna do that command again

90
00:07:11.510 --> 00:07:15.410
but this time I'm going to forget to put
a semicolon at the end so select email

91
00:07:15.410 --> 00:07:23.780
from contacts press enter you can see that
nothing is printed and sql lite has

92
00:07:23.780 --> 00:07:27.350
just to put another prompt up waiting
for more input and notice it's got the

93
00:07:27.350 --> 00:07:30.350
three dots and then the greater than
sign here rather than the sql lite

94
00:07:30.350 --> 00:07:34.340
greater than that normally starts
up the command prompt when we are about to

95
00:07:34.340 --> 00:07:39.440
type command in now you can add other
clauses after select and it's nice to be

96
00:07:39.440 --> 00:07:43.639
able to spit them on two different lines
to make it more readable so sql lite

97
00:07:43.639 --> 00:07:47.450
will keep letting you type a sql command and won't try to execute it until

98
00:07:47.450 --> 00:07:51.260
you type the semicolon now at the moment I
don't want to add anything to the

99
00:07:51.260 --> 00:07:56.750
statement so I'm just going to enter a
semicolon and press enter the statement

100
00:07:56.750 --> 00:08:00.919
executes as you can see and we get the
email address for our one record so when

101
00:08:00.919 --> 00:08:04.430
you forget the semicolon just type it on
the next line and generally everything

102
00:08:04.430 --> 00:08:05.300
will work fine

103
00:08:05.300 --> 00:08:09.560
ok so let's add a couple of additional
records are going to use double quotes

104
00:08:09.560 --> 00:08:12.320
for the first ones just to show you it's still works fine....

105
00:08:12.320 --> 00:08:26.750
so.....

106
00:08:30.199 --> 00:08:34.310
notice  that's a slightly different form of
the insert statement and because we're

107
00:08:34.310 --> 00:08:37.700
providing values for all the fields and
giving them in the order that the fields

108
00:08:37.700 --> 00:08:41.750
were defined in the table there's no
need in this case to specify the list of

109
00:08:41.750 --> 00:08:48.079
fields so it actually works out to
be a bit simpler if we try this insert.....

110
00:08:49.420 --> 00:09:02.410
....

111
00:09:02.410 --> 00:09:07.510
....press enter we actually get an error and
obviously that's because we've been

112
00:09:07.510 --> 00:09:11.350
specified two values but the table
contacts got three columns like it's

113
00:09:11.350 --> 00:09:15.940
telling us on the screen so i could fix
that by adding another value but if we

114
00:09:15.940 --> 00:09:20.019
don't know the email address and that
wouldn't be an option so instead i can

115
00:09:20.019 --> 00:09:23.829
tell which columns i want to add data
for just provide value to insert into

116
00:09:23.829 --> 00:09:32.260
those columns so i could do something
like....

117
00:09:32.260 --> 00:09:44.290
....you can see there's no error in

118
00:09:44.290 --> 00:09:48.519
that case and we can now check what's in
the table so let's look at that so

119
00:09:48.519 --> 00:09:56.829
select star from contacts and you can see
we've got 3 entries showing on

120
00:09:56.829 --> 00:10:00.940
the screen now and note that timand
Brian have email addresses but steve

121
00:10:00.940 --> 00:10:05.079
hasn't incidentally you can see the
column names appearing at the start of

122
00:10:05.079 --> 00:10:05.860
the list

123
00:10:05.860 --> 00:10:10.540
that's because we use that command .
headers on earlier without that would

124
00:10:10.540 --> 00:10:14.440
see the data but the commons wouldn't be
labeled for us and that's maybe not that

125
00:10:14.440 --> 00:10:18.310
important in this example as there aren't any three fields and they

126
00:10:18.310 --> 00:10:21.910
all contain obvious values in other
words it's easy to see that brian is a

127
00:10:21.910 --> 00:10:25.690
name and Brian@email.com is an email address but if there

128
00:10:25.690 --> 00:10:29.320
are a lot of say numeric fields then
it could be useful to have a reminder of

129
00:10:29.320 --> 00:10:34.329
what's in each column now talking about
numbers we probably shouldn't store the

130
00:10:34.329 --> 00:10:38.079
phone number in an integer column i just
did that to show you how you can specify

131
00:10:38.079 --> 00:10:42.310
the type of columns when you create the
table and a phone number is really best

132
00:10:42.310 --> 00:10:46.930
stored as text field now sql lite
doesn't actually have type for its fields

133
00:10:46.930 --> 00:10:51.639
it's strange in that respect although you
specify a type when defining the columns

134
00:10:51.639 --> 00:10:56.500
that's really just  what you intend
to put into them and in fact because the

135
00:10:56.500 --> 00:11:00.819
sql lite implement standard sql it
has to use that standard form for

136
00:11:00.819 --> 00:11:02.350
creating tables

137
00:11:02.350 --> 00:11:06.880
the columns have a data type but you can
actually put any kind of data into any

138
00:11:06.880 --> 00:11:11.140
column and will look at doing that
when we continue this in the next video