1
00:00:05,060 --> 00:00:09,520
so we got the computer setup so that we
can use sql lite from the command

2
00:00:09,520 --> 00:00:13,450
line we're going to have a look at how
to create databases and tables and start

3
00:00:13,450 --> 00:00:18,170
to see how the sql language is used
so we'll be using sql lite in our

4
00:00:18,170 --> 00:00:22,640
programs later but for now we're just
going to focus on the sql language so

5
00:00:22,640 --> 00:00:25,990
that we can concentrate on learning a
bit about sql without worrying about

6
00:00:25,990 --> 00:00:30,710
the java side of things so you need to
start a terminal session or command

7
00:00:30,710 --> 00:00:34,750
prompt on your computer on windows
click on the start menu and type command

8
00:00:34,750 --> 00:00:38,780
to launch a command prompt or you could
just use the same methods that I used

9
00:00:38,780 --> 00:00:43,280
in the setup video to start a command
prompt for Windows 10 depending on what

10
00:00:43,280 --> 00:00:47,510
version you're running on a Mac command
space and type in terminal and on

11
00:00:47,510 --> 00:00:52,190
Linux you can launch this terminal
quickly using ctrl alt t alright I'm

12
00:00:52,190 --> 00:00:56,180
on a mac as I said so I'm going to
start sql lite and the way we start

13
00:00:56,180 --> 00:01:01,430
that is by typing sql lite 3 and press enter

14
00:01:01,430 --> 00:01:07,510
so that starts the sql lite shell
program and we can also specify the name

15
00:01:07,510 --> 00:01:11,140
of a database on the command line so I'm
going to show you how to do this I'm

16
00:01:11,140 --> 00:01:19,180
going to quit out of this by typing
.quit

17
00:01:19,180 --> 00:01:23,210
as I mentioned we can specify the name
of a database on the command line so i'm

18
00:01:23,210 --> 00:01:27,590
going to call this database test so to
do that we type a space after the

19
00:01:27,590 --> 00:01:35,210
sql lite 3 program name and type test . DB and press enter incidentally

20
00:01:35,210 --> 00:01:39,200
there's also command to open a
database file if you forget to put the name

21
00:01:39,200 --> 00:01:43,240
on the command line and we'll have a
look at that a bit later you will find

22
00:01:43,240 --> 00:01:47,530
that this sql lite program is a
fairly minimal interface that just

23
00:01:47,530 --> 00:01:51,130
really tells us the version of sql lite that were using and that we can use

24
00:01:51,130 --> 00:01:55,880
. help to get some instructions we can
also enter sql statements and with

25
00:01:55,880 --> 00:01:59,470
some versions there's also a helpful
reminder that sql statement must be

26
00:01:59,470 --> 00:02:03,770
terminated with a semi colon and you
can see earlier i put a semicolon to

27
00:02:03,770 --> 00:02:08,710
finish to finish up the command quit but
the quit was meant to be a . quit which

28
00:02:08,710 --> 00:02:12,350
doesn't have a semicolon but in any
event will talk more about semicolons in

29
00:02:12,350 --> 00:02:16,430
and the need to out those in a little bit
later and it is normal that you forget

30
00:02:16,430 --> 00:02:17,860
to do this quite often

31
00:02:17,860 --> 00:02:21,100
you'll have forget to put a semicolon in
but as you say it's not the end of the

32
00:02:21,100 --> 00:02:24,370
world you can just enter the semi colon
on the next line and the statement will

33
00:02:24,370 --> 00:02:28,360
then be executed but we'll get to that
in a minute but let's start off that by

34
00:02:28,360 --> 00:02:32,770
typing . help you can see that sql lite is helpfully are telling us that

35
00:02:32,770 --> 00:02:37,960
if we type .help we'll get some help so
we'll do that . help press enter and you

36
00:02:37,960 --> 00:02:41,260
can see it's got a whole page of
information on the screen there and I'm

37
00:02:41,260 --> 00:02:44,080
just scrolling up and down with my mouse
lots of different command options

38
00:02:44,080 --> 00:02:49,180
they're available for us is well its not a lot
but really in the scheme of things it's

39
00:02:49,180 --> 00:02:52,630
not really a lot of commands they're
comparing that so to the Java language

40
00:02:52,630 --> 00:02:56,730
is a significantly higher number of
things you need to know in java than

41
00:02:56,730 --> 00:03:01,030
sql lite but even with that said you
probably won't remember them all

42
00:03:01,030 --> 00:03:04,600
straight away if ever though so .
help is a useful way to remind yourself of them

43
00:03:04,600 --> 00:03:08,230
if you ever need to go back and
see what a particular command all about

44
00:03:08,800 --> 00:03:13,450
alright so before creating a new table
in the database you want to type in .

45
00:03:13,450 --> 00:03:20,260
headers....this
actually shows the column names at the

46
00:03:20,260 --> 00:03:24,250
start of the data which is a handy
reminder of what we call the columns

47
00:03:24,760 --> 00:03:29,440
ok so those are a list of the commands
that sql lite recognizes but

48
00:03:29,440 --> 00:03:34,510
when creating and querying tables we
just use sql statements so let's

49
00:03:34,510 --> 00:03:39,700
create a simple contacts table with the
sql commander we are about the type i'm going

50
00:03:39,700 --> 00:03:50,110
to type in.....

51
00:03:50,110 --> 00:03:57,450
....

52
00:03:57,450 --> 00:04:03,340
....and i'm
going to press enter now that doesn't

53
00:04:03,340 --> 00:04:07,200
seem to do much and that's something
you'll notice on the sql lite if you

54
00:04:07,200 --> 00:04:10,900
do something wrong it will let you know
but if everything works fine then it is

55
00:04:10,900 --> 00:04:14,680
keeps quiet so this case because we
haven't got anything back other

56
00:04:14,680 --> 00:04:19,029
than the prompt asking us to type in
something else on the next line it's

57
00:04:19,029 --> 00:04:22,900
nice and quiet and it generally doesn't
mean that the command worked so in this

58
00:04:22,900 --> 00:04:27,910
case it's created that table for us that
table called contacts has three columns

59
00:04:28,610 --> 00:04:33,220
the name the phone and the email but to
sql lite doesn't tell us that it

60
00:04:33,220 --> 00:04:37,610
worked and again if it hadn't worked
would get an error but otherwise we'll

61
00:04:37,610 --> 00:04:39,940
just move on to the next instruction

62
00:04:39,940 --> 00:04:43,630
alright so with this table now let's
actually put some data into that table

63
00:04:43,630 --> 00:04:50,750
and we can use the sql insert statement
to do that so we can type....

64
00:04:50,750 --> 00:05:17,330
....

65
00:05:17,330 --> 00:05:23,810
....press enter
once again we get no confirmation that

66
00:05:23,810 --> 00:05:29,180
it worked but in this case no news is good
news and I use single quotes there but

67
00:05:29,180 --> 00:05:33,160
you can also use double quotes thing to
remember those if your embedding sql

68
00:05:33,160 --> 00:05:37,430
commands in java that makes sense to use
double quotes for the strings and single

69
00:05:37,430 --> 00:05:41,210
quotes around the sql statements and
you'll see me do that when it comes to

70
00:05:41,210 --> 00:05:44,150
creating programs that work on our
databases

71
00:05:44,150 --> 00:05:47,000
alright so how do we know that this
actually worked these commands actually

72
00:05:47,000 --> 00:05:51,620
did something we can actually check that
it has we can query the table now the

73
00:05:51,620 --> 00:05:56,750
Select statement is very useful in
sql or SQL and it's how you query the

74
00:05:56,750 --> 00:05:58,270
data at the table

75
00:05:58,270 --> 00:06:02,560
now it's a very flexible command but as
at its simplest you can just tell it

76
00:06:02,560 --> 00:06:07,000
what columns you want and the name of
the table to get it from now i'm going

77
00:06:07,000 --> 00:06:11,990
to actually type the sql reserved
words in capitals in this statement and

78
00:06:11,990 --> 00:06:15,520
it's actually useful to do that
especially in programs and scripts but to

79
00:06:15,520 --> 00:06:17,300
sql itself doesn't care

80
00:06:17,300 --> 00:06:21,520
people generally just do it to make it
obvious which other sql reserved

81
00:06:21,520 --> 00:06:26,330
words and which are things like tables
and columns names so to see whats in the contacts

82
00:06:26,330 --> 00:06:31,270
table we use this we type....

83
00:06:31,270 --> 00:06:41,370
....and press enter

84
00:06:41,370 --> 00:06:46,560
we can now see the record that we just
inserted now the asterix there by the

85
00:06:46,560 --> 00:06:51,090
way means all columns you saw me type in the Select statement we could

86
00:06:51,090 --> 00:06:55,160
have been more explicit and type
something like this....

87
00:06:55,160 --> 00:07:04,160
.....that give
us the same result and we just wanted an

88
00:07:04,160 --> 00:07:10,710
email addresses for example we could
just do select email from contacts and

89
00:07:10,710 --> 00:07:14,760
that would just give us the email
alright I'm gonna do that command again

90
00:07:14,760 --> 00:07:18,660
but this time I'm going to forget to put
a semicolon at the end so select email

91
00:07:18,660 --> 00:07:27,030
from contacts press enter you can see that
nothing is printed and sql lite has

92
00:07:27,030 --> 00:07:30,600
just to put another prompt up waiting
for more input and notice it's got the

93
00:07:30,600 --> 00:07:33,600
three dots and then the greater than
sign here rather than the sql lite

94
00:07:33,600 --> 00:07:37,590
greater than that normally starts
up the command prompt when we are about to

95
00:07:37,590 --> 00:07:42,690
type command in now you can add other
clauses after select and it's nice to be

96
00:07:42,690 --> 00:07:46,880
able to spit them on two different lines
to make it more readable so sql lite

97
00:07:46,880 --> 00:07:50,700
will keep letting you type a sql command and won't try to execute it until

98
00:07:50,700 --> 00:07:54,510
you type the semicolon now at the moment I
don't want to add anything to the

99
00:07:54,510 --> 00:08:00,000
statement so I'm just going to enter a
semicolon and press enter the statement

100
00:08:00,000 --> 00:08:04,160
executes as you can see and we get the
email address for our one record so when

101
00:08:04,160 --> 00:08:07,680
you forget the semicolon just type it on
the next line and generally everything

102
00:08:07,680 --> 00:08:08,550
will work fine

103
00:08:08,550 --> 00:08:12,810
ok so let's add a couple of additional
records are going to use double quotes

104
00:08:12,810 --> 00:08:15,570
for the first ones just to show you it's still works fine....

105
00:08:15,570 --> 00:08:30,000
so.....

106
00:08:33,440 --> 00:08:37,559
notice  that's a slightly different form of
the insert statement and because we're

107
00:08:37,559 --> 00:08:40,950
providing values for all the fields and
giving them in the order that the fields

108
00:08:40,950 --> 00:08:45,000
were defined in the table there's no
need in this case to specify the list of

109
00:08:45,000 --> 00:08:51,320
fields so it actually works out to
be a bit simpler if we try this insert.....

110
00:08:52,670 --> 00:09:05,660
....

111
00:09:05,660 --> 00:09:10,760
....press enter we actually get an error and
obviously that's because we've been

112
00:09:10,760 --> 00:09:14,600
specified two values but the table
contacts got three columns like it's

113
00:09:14,600 --> 00:09:19,190
telling us on the screen so i could fix
that by adding another value but if we

114
00:09:19,190 --> 00:09:23,260
don't know the email address and that
wouldn't be an option so instead i can

115
00:09:23,260 --> 00:09:27,070
tell which columns i want to add data
for just provide value to insert into

116
00:09:27,070 --> 00:09:35,510
those columns so i could do something
like....

117
00:09:35,510 --> 00:09:47,540
....you can see there's no error in

118
00:09:47,540 --> 00:09:51,760
that case and we can now check what's in
the table so let's look at that so

119
00:09:51,760 --> 00:10:00,070
select star from contacts and you can see
we've got 3 entries showing on

120
00:10:00,070 --> 00:10:04,190
the screen now and note that timand
Brian have email addresses but steve

121
00:10:04,190 --> 00:10:08,320
hasn't incidentally you can see the
column names appearing at the start of

122
00:10:08,320 --> 00:10:09,110
the list

123
00:10:09,110 --> 00:10:13,790
that's because we use that command .
headers on earlier without that would

124
00:10:13,790 --> 00:10:17,690
see the data but the commons wouldn't be
labeled for us and that's maybe not that

125
00:10:17,690 --> 00:10:21,560
important in this example as there aren't any three fields and they

126
00:10:21,560 --> 00:10:25,160
all contain obvious values in other
words it's easy to see that brian is a

127
00:10:25,160 --> 00:10:28,940
name and Brian@email.com is an email address but if there

128
00:10:28,940 --> 00:10:32,570
are a lot of say numeric fields then
it could be useful to have a reminder of

129
00:10:32,570 --> 00:10:37,570
what's in each column now talking about
numbers we probably shouldn't store the

130
00:10:37,570 --> 00:10:41,320
phone number in an integer column i just
did that to show you how you can specify

131
00:10:41,320 --> 00:10:45,560
the type of columns when you create the
table and a phone number is really best

132
00:10:45,560 --> 00:10:50,180
stored as text field now sql lite
doesn't actually have type for its fields

133
00:10:50,180 --> 00:10:54,880
it's strange in that respect although you
specify a type when defining the columns

134
00:10:54,880 --> 00:10:59,750
that's really just  what you intend
to put into them and in fact because the

135
00:10:59,750 --> 00:11:04,060
sql lite implement standard sql it
has to use that standard form for

136
00:11:04,060 --> 00:11:05,600
creating tables

137
00:11:05,600 --> 00:11:10,130
the columns have a data type but you can
actually put any kind of data into any

138
00:11:10,130 --> 00:11:14,390
column and will look at doing that
when we continue this in the next video