1 00:00:05,920 --> 00:00:09,300 so we got the computer setup so that we can use sql lite from the command 2 00:00:09,300 --> 00:00:13,230 line we're going to have a look at how to create databases and tables and start 3 00:00:13,230 --> 00:00:17,950 to see how the sql language is used so we'll be using sql lite in our 4 00:00:17,950 --> 00:00:22,420 programs later but for now we're just going to focus on the sql language so 5 00:00:22,420 --> 00:00:25,770 that we can concentrate on learning a bit about sql without worrying about 6 00:00:25,770 --> 00:00:30,490 the java side of things so you need to start a terminal session or command 7 00:00:30,490 --> 00:00:34,530 prompt on your computer on windows click on the start menu and type command 8 00:00:34,530 --> 00:00:38,560 to launch a command prompt or you could just use the same methods that I used 9 00:00:38,560 --> 00:00:43,060 in the setup video to start a command prompt for Windows 10 depending on what 10 00:00:43,060 --> 00:00:47,290 version you're running on a Mac command space and type in terminal and on 11 00:00:47,290 --> 00:00:51,970 Linux you can launch this terminal quickly using ctrl alt t alright I'm 12 00:00:51,970 --> 00:00:55,960 on a mac as I said so I'm going to start sql lite and the way we start 13 00:00:55,960 --> 00:01:01,210 that is by typing sql lite 3 and press enter 14 00:01:01,210 --> 00:01:07,290 so that starts the sql lite shell program and we can also specify the name 15 00:01:07,290 --> 00:01:10,920 of a database on the command line so I'm going to show you how to do this I'm 16 00:01:10,920 --> 00:01:18,960 going to quit out of this by typing .quit 17 00:01:18,960 --> 00:01:22,990 as I mentioned we can specify the name of a database on the command line so i'm 18 00:01:22,990 --> 00:01:27,370 going to call this database test so to do that we type a space after the 19 00:01:27,370 --> 00:01:34,990 sql lite 3 program name and type test . DB and press enter incidentally 20 00:01:34,990 --> 00:01:38,980 there's also command to open a database file if you forget to put the name 21 00:01:38,980 --> 00:01:43,020 on the command line and we'll have a look at that a bit later you will find 22 00:01:43,020 --> 00:01:47,310 that this sql lite program is a fairly minimal interface that just 23 00:01:47,310 --> 00:01:50,910 really tells us the version of sql lite that were using and that we can use 24 00:01:50,910 --> 00:01:55,660 . help to get some instructions we can also enter sql statements and with 25 00:01:55,660 --> 00:01:59,250 some versions there's also a helpful reminder that sql statement must be 26 00:01:59,250 --> 00:02:03,550 terminated with a semi colon and you can see earlier i put a semicolon to 27 00:02:03,550 --> 00:02:08,490 finish to finish up the command quit but the quit was meant to be a . quit which 28 00:02:08,490 --> 00:02:12,130 doesn't have a semicolon but in any event will talk more about semicolons in 29 00:02:12,130 --> 00:02:16,210 and the need to out those in a little bit later and it is normal that you forget 30 00:02:16,210 --> 00:02:17,640 to do this quite often 31 00:02:17,640 --> 00:02:20,880 you'll have forget to put a semicolon in but as you say it's not the end of the 32 00:02:20,880 --> 00:02:24,150 world you can just enter the semi colon on the next line and the statement will 33 00:02:24,150 --> 00:02:28,140 then be executed but we'll get to that in a minute but let's start off that by 34 00:02:28,140 --> 00:02:32,550 typing . help you can see that sql lite is helpfully are telling us that 35 00:02:32,550 --> 00:02:37,740 if we type .help we'll get some help so we'll do that . help press enter and you 36 00:02:37,740 --> 00:02:41,040 can see it's got a whole page of information on the screen there and I'm 37 00:02:41,040 --> 00:02:43,860 just scrolling up and down with my mouse lots of different command options 38 00:02:43,860 --> 00:02:48,960 they're available for us is well its not a lot but really in the scheme of things it's 39 00:02:48,960 --> 00:02:52,410 not really a lot of commands they're comparing that so to the Java language 40 00:02:52,410 --> 00:02:56,510 is a significantly higher number of things you need to know in java than 41 00:02:56,510 --> 00:03:00,810 sql lite but even with that said you probably won't remember them all 42 00:03:00,810 --> 00:03:04,380 straight away if ever though so . help is a useful way to remind yourself of them 43 00:03:04,380 --> 00:03:08,010 if you ever need to go back and see what a particular command all about 44 00:03:08,580 --> 00:03:13,230 alright so before creating a new table in the database you want to type in . 45 00:03:13,230 --> 00:03:20,040 headers....this actually shows the column names at the 46 00:03:20,040 --> 00:03:24,030 start of the data which is a handy reminder of what we call the columns 47 00:03:24,540 --> 00:03:29,220 ok so those are a list of the commands that sql lite recognizes but 48 00:03:29,220 --> 00:03:34,290 when creating and querying tables we just use sql statements so let's 49 00:03:34,290 --> 00:03:39,480 create a simple contacts table with the sql commander we are about the type i'm going 50 00:03:39,480 --> 00:03:49,890 to type in..... 51 00:03:49,890 --> 00:03:57,230 .... 52 00:03:57,230 --> 00:04:03,120 ....and i'm going to press enter now that doesn't 53 00:04:03,120 --> 00:04:06,980 seem to do much and that's something you'll notice on the sql lite if you 54 00:04:06,980 --> 00:04:10,680 do something wrong it will let you know but if everything works fine then it is 55 00:04:10,680 --> 00:04:14,460 keeps quiet so this case because we haven't got anything back other 56 00:04:14,460 --> 00:04:18,810 than the prompt asking us to type in something else on the next line it's 57 00:04:18,810 --> 00:04:22,680 nice and quiet and it generally doesn't mean that the command worked so in this 58 00:04:22,680 --> 00:04:27,690 case it's created that table for us that table called contacts has three columns 59 00:04:28,390 --> 00:04:33,000 the name the phone and the email but to sql lite doesn't tell us that it 60 00:04:33,000 --> 00:04:37,390 worked and again if it hadn't worked would get an error but otherwise we'll 61 00:04:37,390 --> 00:04:39,720 just move on to the next instruction 62 00:04:39,720 --> 00:04:43,410 alright so with this table now let's actually put some data into that table 63 00:04:43,410 --> 00:04:50,530 and we can use the sql insert statement to do that so we can type.... 64 00:04:50,530 --> 00:05:17,110 .... 65 00:05:17,110 --> 00:05:23,590 ....press enter once again we get no confirmation that 66 00:05:23,590 --> 00:05:28,960 it worked but in this case no news is good news and I use single quotes there but 67 00:05:28,960 --> 00:05:32,940 you can also use double quotes thing to remember those if your embedding sql 68 00:05:32,940 --> 00:05:37,210 commands in java that makes sense to use double quotes for the strings and single 69 00:05:37,210 --> 00:05:40,990 quotes around the sql statements and you'll see me do that when it comes to 70 00:05:40,990 --> 00:05:43,930 creating programs that work on our databases 71 00:05:43,930 --> 00:05:46,780 alright so how do we know that this actually worked these commands actually 72 00:05:46,780 --> 00:05:51,400 did something we can actually check that it has we can query the table now the 73 00:05:51,400 --> 00:05:56,530 Select statement is very useful in sql or SQL and it's how you query the 74 00:05:56,530 --> 00:05:58,050 data at the table 75 00:05:58,050 --> 00:06:02,340 now it's a very flexible command but as at its simplest you can just tell it 76 00:06:02,340 --> 00:06:06,780 what columns you want and the name of the table to get it from now i'm going 77 00:06:06,780 --> 00:06:11,770 to actually type the sql reserved words in capitals in this statement and 78 00:06:11,770 --> 00:06:15,300 it's actually useful to do that especially in programs and scripts but to 79 00:06:15,300 --> 00:06:17,080 sql itself doesn't care 80 00:06:17,080 --> 00:06:21,300 people generally just do it to make it obvious which other sql reserved 81 00:06:21,300 --> 00:06:26,110 words and which are things like tables and columns names so to see whats in the contacts 82 00:06:26,110 --> 00:06:31,050 table we use this we type.... 83 00:06:31,050 --> 00:06:41,150 ....and press enter 84 00:06:41,150 --> 00:06:46,340 we can now see the record that we just inserted now the asterix there by the 85 00:06:46,340 --> 00:06:50,870 way means all columns you saw me type in the Select statement we could 86 00:06:50,870 --> 00:06:54,940 have been more explicit and type something like this.... 87 00:06:54,940 --> 00:07:03,940 .....that give us the same result and we just wanted an 88 00:07:03,940 --> 00:07:10,490 email addresses for example we could just do select email from contacts and 89 00:07:10,490 --> 00:07:14,540 that would just give us the email alright I'm gonna do that command again 90 00:07:14,540 --> 00:07:18,440 but this time I'm going to forget to put a semicolon at the end so select email 91 00:07:18,440 --> 00:07:26,810 from contacts press enter you can see that nothing is printed and sql lite has 92 00:07:26,810 --> 00:07:30,380 just to put another prompt up waiting for more input and notice it's got the 93 00:07:30,380 --> 00:07:33,380 three dots and then the greater than sign here rather than the sql lite 94 00:07:33,380 --> 00:07:37,370 greater than that normally starts up the command prompt when we are about to 95 00:07:37,370 --> 00:07:42,470 type command in now you can add other clauses after select and it's nice to be 96 00:07:42,470 --> 00:07:46,660 able to spit them on two different lines to make it more readable so sql lite 97 00:07:46,660 --> 00:07:50,480 will keep letting you type a sql command and won't try to execute it until 98 00:07:50,480 --> 00:07:54,290 you type the semicolon now at the moment I don't want to add anything to the 99 00:07:54,290 --> 00:07:59,780 statement so I'm just going to enter a semicolon and press enter the statement 100 00:07:59,780 --> 00:08:03,940 executes as you can see and we get the email address for our one record so when 101 00:08:03,940 --> 00:08:07,460 you forget the semicolon just type it on the next line and generally everything 102 00:08:07,460 --> 00:08:08,330 will work fine 103 00:08:08,330 --> 00:08:12,590 ok so let's add a couple of additional records are going to use double quotes 104 00:08:12,590 --> 00:08:15,350 for the first ones just to show you it's still works fine.... 105 00:08:15,350 --> 00:08:29,780 so..... 106 00:08:33,220 --> 00:08:37,340 notice that's a slightly different form of the insert statement and because we're 107 00:08:37,340 --> 00:08:40,730 providing values for all the fields and giving them in the order that the fields 108 00:08:40,730 --> 00:08:44,780 were defined in the table there's no need in this case to specify the list of 109 00:08:44,780 --> 00:08:51,100 fields so it actually works out to be a bit simpler if we try this insert..... 110 00:08:52,450 --> 00:09:05,440 .... 111 00:09:05,440 --> 00:09:10,540 ....press enter we actually get an error and obviously that's because we've been 112 00:09:10,540 --> 00:09:14,380 specified two values but the table contacts got three columns like it's 113 00:09:14,380 --> 00:09:18,970 telling us on the screen so i could fix that by adding another value but if we 114 00:09:18,970 --> 00:09:23,040 don't know the email address and that wouldn't be an option so instead i can 115 00:09:23,040 --> 00:09:26,850 tell which columns i want to add data for just provide value to insert into 116 00:09:26,850 --> 00:09:35,290 those columns so i could do something like.... 117 00:09:35,290 --> 00:09:47,320 ....you can see there's no error in 118 00:09:47,320 --> 00:09:51,540 that case and we can now check what's in the table so let's look at that so 119 00:09:51,540 --> 00:09:59,850 select star from contacts and you can see we've got 3 entries showing on 120 00:09:59,850 --> 00:10:03,970 the screen now and note that timand Brian have email addresses but steve 121 00:10:03,970 --> 00:10:08,100 hasn't incidentally you can see the column names appearing at the start of 122 00:10:08,100 --> 00:10:08,890 the list 123 00:10:08,890 --> 00:10:13,570 that's because we use that command . headers on earlier without that would 124 00:10:13,570 --> 00:10:17,470 see the data but the commons wouldn't be labeled for us and that's maybe not that 125 00:10:17,470 --> 00:10:21,340 important in this example as there aren't any three fields and they 126 00:10:21,340 --> 00:10:24,940 all contain obvious values in other words it's easy to see that brian is a 127 00:10:24,940 --> 00:10:28,720 name and Brian@email.com is an email address but if there 128 00:10:28,720 --> 00:10:32,350 are a lot of say numeric fields then it could be useful to have a reminder of 129 00:10:32,350 --> 00:10:37,350 what's in each column now talking about numbers we probably shouldn't store the 130 00:10:37,350 --> 00:10:41,100 phone number in an integer column i just did that to show you how you can specify 131 00:10:41,100 --> 00:10:45,340 the type of columns when you create the table and a phone number is really best 132 00:10:45,340 --> 00:10:49,960 stored as text field now sql lite doesn't actually have type for its fields 133 00:10:49,960 --> 00:10:54,660 it's strange in that respect although you specify a type when defining the columns 134 00:10:54,660 --> 00:10:59,530 that's really just what you intend to put into them and in fact because the 135 00:10:59,530 --> 00:11:03,840 sql lite implement standard sql it has to use that standard form for 136 00:11:03,840 --> 00:11:05,380 creating tables 137 00:11:05,380 --> 00:11:09,910 the columns have a data type but you can actually put any kind of data into any 138 00:11:09,910 --> 00:11:14,170 column and will look at doing that when we continue this in the next video