1
00:00:00,030 --> 00:00:05,000
[MUSIC]

2
00:00:05,000 --> 00:00:06,449
Hi, I'm Ben.

3
00:00:06,449 --> 00:00:11,160
And in this workshop, we're going to
learn about SQLite from the command line.

4
00:00:11,160 --> 00:00:15,210
So far, we've been learning
SQL inside of SQL Playgrounds.

5
00:00:15,210 --> 00:00:17,020
And while there's nothing wrong with that,

6
00:00:17,020 --> 00:00:21,570
it's not super helpful when you need
to start creating your own databases.

7
00:00:21,570 --> 00:00:24,760
In this workshop, we're going to
look at how you can create and

8
00:00:24,760 --> 00:00:27,340
manage databases right
on your own computer.

9
00:00:28,470 --> 00:00:31,880
The first thing you'll need to do
is make sure that you have SQLite

10
00:00:31,880 --> 00:00:33,690
installed on your computer.

11
00:00:33,690 --> 00:00:38,740
If you're on a Mac or Linux computer,
then it's likely you already have SQLite.

12
00:00:38,740 --> 00:00:42,710
To see if SQLite is already on
your computer, open up a terminal.

13
00:00:42,710 --> 00:00:47,280
And at the command prompt type,
sqlite3 then hit Enter.

14
00:00:48,960 --> 00:00:52,300
If your terminal looks like this,
then you're good to go.

15
00:00:52,300 --> 00:00:55,490
If it doesn't,
then you'll need to install SQLite.

16
00:00:55,490 --> 00:00:59,210
Check out the teacher's notes for
links to installation instructions.

17
00:00:59,210 --> 00:01:01,680
All right,
now there's two commands that you

18
00:01:01,680 --> 00:01:06,530
absolutely need to know to be able
to use SQLite from the command line.

19
00:01:06,530 --> 00:01:08,435
The first is .help.

20
00:01:08,435 --> 00:01:13,370
Calling .help will show you a list of
all the commands that you can run.

21
00:01:14,740 --> 00:01:15,520
So if you're looking for

22
00:01:15,520 --> 00:01:20,370
anything in particular,
remember to look in .help first.

23
00:01:20,370 --> 00:01:23,967
The second command you absolutely
need to know Is .quit,

24
00:01:23,967 --> 00:01:28,000
which quits SQLite and
brings us back to the command prompt.

25
00:01:29,730 --> 00:01:31,427
It's easy to forget this one.

26
00:01:31,427 --> 00:01:36,338
So if you're ever stuck at the SQLite
prompt remember that you can

27
00:01:36,338 --> 00:01:38,410
always use .quit to exit.

28
00:01:38,410 --> 00:01:43,440
And if you forget .quit you can always
use .help to help you remember.

29
00:01:44,550 --> 00:01:50,407
Okay, now that we're back at the command
prompt, let's create our own database.

30
00:01:50,407 --> 00:01:55,140
But first let's create a folder where
we can keep our practice files.

31
00:01:55,140 --> 00:01:58,070
You can do this using the file
explorer if you'd like, but

32
00:01:58,070 --> 00:01:59,240
I'll be using the command line.

33
00:02:00,400 --> 00:02:03,860
Let's type mkdir, which stands for
make directory, and

34
00:02:03,860 --> 00:02:05,984
let's call it DatabasePractice.

35
00:02:10,010 --> 00:02:11,910
And hit Enter to create our new folder.

36
00:02:13,620 --> 00:02:18,490
Then let's change directory into
that folder by typing cd and

37
00:02:18,490 --> 00:02:20,350
then the name of the folder.

38
00:02:20,350 --> 00:02:23,700
You can use tab to auto-complete.

39
00:02:23,700 --> 00:02:25,210
And hit Enter to run the command.

40
00:02:26,410 --> 00:02:31,660
Now that we're in our new folder, we can
create a new database by typing sqlite3

41
00:02:31,660 --> 00:02:34,400
followed by the name of the database.

42
00:02:34,400 --> 00:02:39,260
Let's type sqlite3, add a space, and

43
00:02:39,260 --> 00:02:45,790
then name our database mydatabase.db,
then hit Enter.

44
00:02:47,430 --> 00:02:50,385
And now we can start using our database.

45
00:02:50,385 --> 00:02:54,194
Though before we do, let's quickly
check out our DatabasePractice folder.

46
00:02:58,051 --> 00:03:03,210
We just created a new database,
and yet, there's nothing in here.

47
00:03:04,370 --> 00:03:07,820
Turns out that SQLite
won't create our database

48
00:03:07,820 --> 00:03:11,300
until it's more than
just an empty database.

49
00:03:11,300 --> 00:03:15,100
So let's move forward with creating a
table, and then we'll check back in later.

50
00:03:16,700 --> 00:03:21,596
Back in the terminal, let's create a new
table to store information about cars.

51
00:03:21,596 --> 00:03:26,720
Let's type CREATE TABLE,
we'll name it CARS.

52
00:03:27,980 --> 00:03:34,601
Then for the columns, we'll give it an ID,
which should be a PRIMARY KEY.

53
00:03:34,601 --> 00:03:41,090
And then we'll add a couple more
columns like MAKE, MODEL, and YEAR.

54
00:03:41,090 --> 00:03:42,180
Then hit Enter to run it.

55
00:03:43,790 --> 00:03:44,650
And that's right.

56
00:03:44,650 --> 00:03:46,700
We can't forget the semicolon.

57
00:03:46,700 --> 00:03:50,930
If you're ever looking at something like
this, you just need to add a semicolon.

58
00:03:52,860 --> 00:03:56,070
And with that, if we check back in
with our database practice folder,

59
00:03:57,690 --> 00:04:01,730
we can see that we've
now got mydatabase.db.

60
00:04:01,730 --> 00:04:02,230
Awesome.