In this lecture, we're going to go over

the SQL database introduction.

So, what is a database?

Well, a database is an organized

collection of structured information of

data which is stored in a computer

system.

The data can be easily accessed.

The data can be modified.

The data can be controlled and

organized.

And many databases use a structured

query language which is also known as

SQL to modify and write data to a

database table.

But before we jump into a database, we

might get the question of well what is

data?

So let's continue on. What is a

database? Well, data can be related to

just about any object.

For example, if we think of a user on an

application, we may think of the name,

the age of a user,

an email,

or a password for a user. All four of

these characteristics are all data of a

user that might be trying to get into an

application. So each user is going to

have a name, an age, an email, and a

password associated to them.

So really a database is just a

collection of data.

Since data on its own is just data, a

database allows management of all of

this data.

So, databases are organized in how data

can be retrieved, stored, and modified.

Now, when we're talking about databases,

there's kind of a parent of a database.

And there are many different types of

this, and this is called a database

management system, also known as a DBMS.

It's the application or the software

that is now in charge of the database.

Now some really popular SQL databases

are SQLite and SQLite is be the very

first DBMS that we use within our

application.

My SQL is also a very popular choice for

database management systems

and Postgress QL is also an amazing

choice for a DBMS system.

Now I keep saying that these are used

for SQL database management systems. So

let's dive in and learn a little bit

more about what is SQL.

Well, it's either pronounced as SQL or

SQL. You'll see that both are used very

often within the production world.

SQL is the standard language for dealing

with relational databases. Now, a

relational database means there's going

to be a table with columns and

everything kind of makes sense. Like

think of an Excel sheet or some kind of

sheet where there's going to be columns

and rows with data inside kind of like a

table.

So SQL can be used to do different

things within a database record. And a

database record is each row of data

within a database.

SQL can create data within a database.

It can read data within a database. It

can update data within a database. and

it can delete data within a database.

Now, this looks awfully familiar to our

HTTP requests. These are known as the

CRUD operations, create, read, update,

and delete. And SQL will be able to

handle all of this for us for our fast

API application and the database. So,

now that we kind of did an introduction

to SQL and databases, I'll see you in

the next video.

All right. Hey, hey everybody and

welcome to another section of our fast

API course where we're going to be going

over our project 3 which is our todos

project. And now our todos project is

going to be a way for us to be able to

create to-dos where we can write a list

of items that we want to be able to

complete. We then have a complete

feature where we can check or check off

and we're going to have the ability to

be able to prioritize these to-dos.

So the very first thing we want to do to

get this project started is let's go

ahead and rightclick on fast API. I'm

going to say a new Python package and

I'm going to name this package todo app.

All right. And then Python is going to

create this todo Python package. And I'm

going to delete books and books too

because we don't really need these

anymore because we're moving on to

projects three and books projects are in

the past. So, we're moving forward with

the course and we no longer need books

or books, too.

All right. So, now that we have our

to-do package created, let's go ahead

and just rightclick on todo app and say

new Python file. And this Python file is

going to be called database.py.

And now our database.py PI file is going

to be used for us to be able to create

our URL string which will connect our

Fast API application to our new

database. And we're going to be using

SQLite.

And we need to create some other

information for us to be able to open up

the database connection, be able to

close the database connection, be able

to create some database tables. And some

of this happens within our database.py

file that we now added within our to-do

app.

Now one thing to notice is that in our

terminal we still have our fast API

environment running which is correct but

our latest file is our fast API parent.

If we look at our project structure we

can see that fast API is at the top but

inside fast API we have our fast API

environment and our todo app. Well all

of our logic for this project is going

to be in our todo app. So we don't want

to be at our parent route of fast API.

We want to cd one directory lower into

our todo app.

To do this, we can do cd todo app.

We can now see that our terminal has

changed the direction from our fast API

to our todo app, which is correct.

So now inside our database.py file, we

need to create some logic.

But before we go ahead and create our

database.py PI file. One thing that we

need to do is go ahead and install

something called SQL Alchemy.

Now, SQL Alchemy is an OM, which is

object relational mapping, which is what

our Fast API application is going to use

to be able to create a database and be

able to create a connection to a

database and being able to use all of

the database records within our

application.

So, before we get started creating our

database.py Pi file. Let's go ahead and

just say pip install SQL Alchemy.

All right. And now once you click enter,

it's going to download SQL Alchemy onto

your machine. And that's exactly what we

needed. We needed it installed into our

fast API environment.

All right. Now let's go ahead and start

writing some of our SQL alchemy code

within our database.py file. Now the

very first thing we need to do is create

a new variable SQL alchemy

database URL which is going to be equal

to a new string which we're going to

call SQLite

colon slash slash so three slashes dot

slashtodos.db

DB.

Now, this URL is going to be used to be

able to create a location of this

database on our Fast API application.

Now, that might sound kind of crazy at

first, but we're going to dive in and go

over everything step by step, but really

what we're saying right here is that our

database is going to be inside this

directory of our todo app.

And you'll see it when we run the

application here in a little bit.

The next thing we need is to be able to

create an engine for our application.

And now a database engine is something

that we can use to be able to open up a

connection and be able to use our

database.

So before we get started, let's go ahead

and just import create engine from SQL

Alchemy. So we can go ahead and say from

SQL Alchemy import create engine.

Now once we imported create engine we

can now create a new variable engine

which is equal to create engine

and now inside our engine we need to add

the URL path for our database which is

going to be the SQL alchemy database URL

that we provided and then we want to say

comma connect arguments.

Now these connect arguments are

arguments that we can pass into our

create engine which will allow us to be

able to define some kind of connection

to a database. Well, we want to say

check same thread of type false.

And now, by default, SQLite will only

allow one thread to communicate with it,

assuming that each thread will handle an

independent request.

This is to prevent any kind of accident

sharing of the same connection for

different kind of requests. But in fast

API, it's very normal to have more than

one thread that could interact with the

database at the same time. So we just

need to make sure SQLite knows that hey

we don't want to be checking the same

thread all the time because there could

be multiple threads happening to our

SQLite database.

Now we need to create a session local

and each instance of the session local

will have a database session. The class

itself is not a database session yet. We

will add that later on. But right now,

we just need to be able to create an

instance of session local that will be

able to become an actual database in the

future. So now let's scroll back up to

the top and let's import session maker

from SQL Alchemy.

And we can do that by saying from SQL

Alchemy. Let's import session maker.

Now we can scroll back down and say

session local equals session maker.

And inside here we want to say

autocommit equals false

auto flush equals false

and bind equals engine. So what we're

saying is we want the session local

which we're going to be using in our

application. And you'll see when we use

session local, we want to bind to the

engine that we just created. And we want

to make sure that our auto commits and

auto flushes are false or the database

transactions are going to try and do

something automatically. And we want to

be fully control of everything our

database will do in the future.

Now, the last thing we need to do is

make sure that we can create a database

object that we can then interact with

later on. So let's go back to the top

and say from SQL Alchemy.ext.decldeclare

declarative

import declarative_base

and then we're just going to say base

equals declarative_base and then

parenthesis.

So what we're saying here is later on we

want to be able to call our database.py

PI file be able to create a base which

is an object of the database which is

going to be able to then control our

database.

What this really just means is we're

going to be creating database tables and

then here in our database PI we're going

to be able to create an object of our

database which will then be able to

interact with the tables that we create

in the future.

Now, this is all awesome stuff and it

might seem a little foggy on what's

going on right now, but we will continue

to dive into it and it will start making

sense later on as we continue to be able

to create database connections with our

fast API application.

But with that, this wraps up creating

our database.py file, and I will see you

in the next video.

All right. Hey everybody. In the last

video, we went ahead and created our

database.py file. Now, in this video,

we're going to create our models.py

file. So, let's go ahead and just click

on our todo app, say rightclick, new

Python file, and we're going to call

this models.

Now, models is a way for SQL Alchemy to

be able to understand what kind of

database tables we are going to be

creating within our database in the

future.

Now, a database model is going to be the

actual record that is inside a database

table.

So, for example, we're going to have a

table that is going to be called to-dos.

And inside to-dos, each record is going

to have an ID, a title, a description, a

priority, and a complete column, which

will make sure a record in a database is

an actual record that we can then use

some kind of manipulation with, whether

that's in our fast API application or if

we just want to be able to view it in

the database.

Well, the very first thing we need to

import is inside our database. We

created a base from our declarative base

and we want to import that into our

models. So, let's start by just saying

from database import base.

And that just means that we are going to

be creating this model for our

database.py file.

So, the very first thing we can code

inside our models.py PI file is our

to-dos which is going to be our to-dos

table. So we can say class to-dos which

now inherits base from our database.py

file.

We can then inside class say underscore

table name

equals todos.

Now, this is just a way for SQL Alchemy

to know what to name this table inside

our database later on.

So, now that we have this class to-dos,

which is going to mimic the table of

to-dos, we can now start writing up the

columns that take place inside our

table, which is going to be ID, title,

description, priority, and complete.

Now, let's start with doing our ID. So

when we think of an ID, the ID is going

to be our primary key, which is the

unique ID that distinguishes each record

within the database.

We also want this to be an integer and

it's going to be a new column for our

database.

So before we start, let's scroll up to

the top and say from SQL alchemy import

column and integer.

Now let's scroll back down to our todos

and say ID equals column

and then in parenthesis we're going to

say integer. So we're saying this ID is

going to be a new column within our

table and it's going to be an integer.

We then want to say primary key equals

true because our ID is going to be the

primary key which is the unique ID or

the unique identifier for each record.

And then let's also just say index

equals true. And index is just a way for

us to be able to increase performance by

telling our database table that this is

indexable, which means it's going to be

unique. We're going to be able to find

it directly and it's just going to

increase performance just slightly. All

right. And now that we created our ID,

we want to be able to create a string.

So let's go back up to the top and look

at our other imports. We've imported

column and integer. Let's go ahead and

just import string and boolean. string

is going to be for all of the word

columns that we want to add within our

table and boolean is going to be for our

last one dictating whether this is

complete or not.

All right. All right. Now, let's come

back down and say title equals column of

string.

Description equals column of string.

Priority equals column of integer.

and complete equals column of boolean

and here we can say a default value. So

everything else is going to default as

none or null. We're going to say default

as false.

So now our todos table is going to have

an ID, a title, a description, a

priority, and a complete option within

our application.

So just a quick overview of everything

we just did. We imported our database

and then we imported SQL Alchemy where

we're saying, "Hey, we want to beble to

create certain records within our

table."

And then we're going to say, "Hey, we

want to create a new table within our

database, which is going to be called

to-dos that has a ID column, a title

column, a description column, a priority

column, and a complete column.

Each column is either going to be an

integer, a string, or a boolean. And

we're only going to say our ID of each

to-do is going to be the primary key.

And we're going to say index is true.

All right. So with that, this wraps up

everything we want for our models.py,

which again is going to create our table

of to-dos with each column, but we still

don't have any records. So I'll see you

in the next video where we dive in and

continue adding to our database setup

for our fast API to-do app application.

All right. So, we've currently created

our database.py file and our models.py

file. However, we still don't have a

database or a database table created.

Now, being able to create a database in

a table is super easy with SQL Alchemy.

We've already created the database.py

file, which has the location of where we

want to store our todos. DB and we

already have a model which is going to

be our table that we want to be stored

in our database.

So let's just go back up to our to-do

app and let's rightclick and say new

Python file and we're going to name this

main.

Now our main file is where all the magic

for this project is going to happen.

It's going to be our root folder where

we create our fast API application.

Now being able to create a table using

fast API in SQL alchemy is extremely

easy.

Let's start by creating a normal fast

API application. So we can say from fast

API import fast API

and then under this we can say app

equals fast API parenthesis. So we've

done this before. We now have our app

which is connected to a fast API

application.

We then want to say import models and we

want to import our engine from database.

So we can say from database import

engine.

And now let's go ahead and just say

models.base

do metadata

dotcreate all.

And then inside this we want to say bind

equals engine.

All right. And remember in our

database.py file we said we want our SQL

alchemy database URL to just create a

SQL light database called todos.db

in the location of our todo app.

Now this will happen automatically when

we run the application. So if we open up

our terminal and we cd into app. So

remember we're in our fast API parent.

We need to go one directory lower into

our todo app.

And now from here if we say uicorn main

colon app-reload

we are going to create a new database

within our todo app. So so far we can

see there's only four files. But now if

we start up our Uicorn

and we run the application

and we hover over to-do app, we can see

that it'll automatically refresh and we

now have a new database called todos.db.

Now you might be thinking, okay, well

how does this happen? Well, our

models.base.madata.createallbind

create all bind equals engine. We'll

create everything from our database.py

file and our models.py file to be able

to create a new database that has a new

table of to-dos with all of the columns

that we laid out in our models.py file.

So, this happens all behind the scenes

on how to create a new database for our

fast API application using SQL light.

All right. So, this is awesome stuff on

how we're able to be able to dynamically

be able to create a database without

actually having to write any kind of SQL

queries to be able to accomplish this.

All right. Well, now that our tudos

database is now created, we now want to

go ahead and install SQLite on our

machines. So, we can now play around

with the actual database using query

commands.

and we want to be able to store

information to our to-dos.

So, let's go ahead and jump into the

next video where we dive into SQLite a

little bit more so we can start

populating our todos db with some

records.

Hey everybody and welcome back to

another lecture with coding with Robi.

In this lecture, I'm going to show you

how to install SQLite 3 on a Windows

operating system so you can manipulate

the data of a database, be able to

select all from a database, and be able

to just completely be able to use a

SQLite database from your terminal.

To be able to do this, the first thing

you need to do is go to Google and type

in SQLite 3.

From here, you're going to want to go to

https

www.sqlite.org.

If you want to skip the entire first

step, you can just go to this URL, which

is sqlite.org/index.html.

From the homepage, we're going to want

to click download.

And when you're in the download page,

you're going to want to scroll down

until you see the pre-ompiled binaries

for Windows.

And you're going to want to click the

very last one, which is a SQLite tools

132 x86. You want to click this one

because this comes with a lot of stuff.

It comes with the entire bundle for

command line tools for managing SQLite

database files. That's like one of the

biggest things you want. So let's

download this by clicking on the link.

And now once you download it, let's open

up our downloads file.

And I'm going to extract this. So, we're

going to need to extract this file that

we just downloaded because it came as a

zip.

And I'm just going to extract it right

here in the downloads file. And now, the

file that we just downloaded should come

with three files within.

We're going to want to copy this or cut

it.

and we want to hop on over to our PC and

then go into our C drive. And within our

C drive, let's just paste it in here.

We're then going to want to rename this

to SQLite 3.

And now we have the SQLite 3 file on our

C drive. We're just going to want to

open this up and grab this path.

From here, we're going to want to open

up our systems environment properties.

You can accomplish this by pressing the

Windows button and then typing in ENV

for environmental properties.

From here, let's click the environment

variables.

And inside our system variables, we want

to modify path.

inside path, let's say a new

environment variable. And let's just

paste in our C drive SQLite 3 path and

press okay. And then let's press okay

again. And then one more okay.

From here, we should be able to open up

our command prompt

and say SQLite 3.

If everything looks correct, you should

now get a version of SQLite 3 printed

back to you.

This wraps up the lecture on how to

download and install SQLite 3 on your

Windows machine and set up your

environment variables. and I will see

you in the next video.

Hey everybody and welcome back to

another lecture with coding with Roi.

And in this lecture I'm going to show

you how to install SQL light on a MacOSS

so we can use the terminal to manipulate

the database and tables and columns and

all the fun stuff that comes with

databases. The first thing we're going

to do is install Homebrew. So in my

Google search I'm going to type in

Homebrew.

and I'm going to go to https colbw.sh

and then from here I'm going to copy

this command

and from here I'm going to open up my

terminal.

I'm going to paste in that command and

press enter.

Here it's going to say you need to type

in your password. So I'll type in my

password.

It'll tell you what scripts are about to

be installed.

Press the return button to continue.

And it's going to download and install

Home Brew.

Let's now close out of the terminal. And

let's reopen the terminal.

And now you have Homebrew installed on

your machine. Now SQLite 3 should come

pre-installed on Homebrew. So if we type

in brew list,

we can see that we now have SQLite.

If for some reason SQLite 3 is not

installed, we can install SQLite by

typing in brew install SQLite. When you

press enter, it will install all the

dependencies for SQLite. And since I

have it installed, let's just use

SQLite. We can now run SQLite by typing

in SQLite 3.

And immediately we can see that we get

the version returned to us.

We can close SQLite by saying dotquit.

This wraps up the video on how to

install SQLite 3 on a MacBook and I will

see you in the next video.

Hey everybody and welcome to another

lecture with coding with Roi. And in

this lecture we're going to be going

over basic SQL queries.

All right, let's start by going over

inserting data into a database table.

Imagine our to-do database table. A

single to-do record within our database

looks very similar to this. Our to-do

will contain an ID, which is our primary

key to identify each record.

a title for the title of our to-do. A

description that describes the to-do and

gives it a little bit more information.

Priority, which is the ranking of how

important it is to accomplish this

to-do, and a complete column, which

displays if the to-do is complete or

not. And eventually, we'll be adding a

foreign key of owner, which connects

this to-do to a specific individual or

user.

So in SQL terms, how do we add data to a

to-do?

To start, we need to say which fields of

the record we want to add to.

We can see the SQL command that says

insert into todos. So, insert into the

to-dos table a title, a description,

priority, and complete.

We can see we are not saying ID, and

that's because we want the database to

handle this automatically for us and to

auto increment. So, we can't

accidentally assign the same primary key

to two different to-do records.

We then want to type values which is go

to store to pick up eggs for as a

priority and false for complete.

When we run this we will be able to see

that we are creating a new record in the

database. Our title, description,

priority and complete all get updated

based on this command. And we now have

our database configured to auto

increment the ID on its own. This way,

we never have to worry about updating

the ID ourselves, and we can trust the

database to do this for us.

All right, now that we've created one

record in our database, let's create

another record.

We'll be inserting into our to-dos a

title description priority and

complete.

And we'll say the values of these are

haircut, need to get length of 1 mm,

three priority, and false for complete.

Once we submit this to the database, we

can see that we now have two records in

our database. One with an ID of one and

one with an ID of two.

All right. Now that we just went over

inserting into our database, let's keep

adding to it. Now, I'm just going to

pretend we are inserting this data. So,

let's move on to a new record where we

just created a feed the dog as a title.

As a description, we are going to be

using make sure to use the new food

brand with a priority of five because we

need to feed our dog and not complete.

And then let's add another one about

watering our plant as a title.

Description is inside and outside plants

with a priority of four and also not

complete.

Let's add a fifth record of learn

something new. Learn to program five and

also not complete.

And lastly, let's pretend we added a six

record, which the title is shower. Um,

and the description is you have not

showered in a few days, so it's probably

a good idea to take a shower. Um, a

priority of five cuz you smell really

bad. And complete is false.

All right. Well, we now have some data

in our database. We now want to be able

to withdraw data from our database. So,

how can we do that? To be able to start,

we need to use the select attribute.

We can say select asterric from to-dos.

Now in SQL, an asterric means all

columns and rows. So with this example,

we are saying select all rows and all

columns from our to-dos table,

which in reality would just give us

everything in that table. So all six

records we currently have saved in our

database will be displayed on the

screen.

What if we modified our select script

just a little bit to only retrieve the

titles of our records?

We can accomplish this by saying select

title from to-dos.

By doing this we are selecting just a

title from our to-dos columns. Our

response from the database will look

very similar to this. We get go to the

store, haircut, feed dog, water plant,

learn something new, and a shower with

no other information. And that is

because we are specifically said we only

want the titles from the table to-dos.

What if we modified our select script

even a little bit more to retrieve the

description of all the records?

Well, this would be very similar to how

we handled the title portion of our

select command. We can accomplish this

by saying select description from

to-dos.

By doing this, we are selecting just the

description from all the to-do columns.

Our response from the database would

look very similar to this. You will get

to pick up eggs. Need to get length 1

millm. Make sure to use new food brand

inside and outside plants. Learn to

program. And you have not showered in a

few days.

Hm. Well, being able to get only one

column may be great in theory, but in

our case, it doesn't actually do much.

If we get a title, we want the

description as well, and vice versa.

Let's go ahead and modify our SQL

command a little bit.

Now let's type select title,

description from to-dos.

This will properly select all titles and

descriptions from our table which will

give us the titles and descriptions that

we are seeking.

And there it is. Great job. We get the

title and description from our todos

table.

Let's keep this going and add another

column to our select command. This time

we are going to be adding priority along

with our title and description.

We can accomplish this by saying select

title, description, priority from

to-dos.

This will retrieve all titles,

descriptions, and priorities from our

columns. And now we will be able to see

our titles, descriptions, and how

important each task is.

Now that we covered the select command

from SQL, let's keep this learning up

and learn about the wear clause. The

wear clause specifies criteria that a

field value must meet for the records

that contains the values be included in

the query results. Let's check this out.

Okay, remember when we wrote select

asterric from to-dos and we returned all

rows and columns from the table's

to-dos.

This time we will be writing the same

thing. However, we'll be adding a wear

clause where the priority of the record

must be equal to five.

This means we are selecting all rows and

columns where priority is equal to five.

Therefore, when we run the SQL

statement, we will get only three of our

six records returned. This is because

only three of the records have a

priority of five. But we'll be getting

all columns of each of these records.

Let's try another SQL statement with a

wear clause. This time, let's write

select all from to-dos where title is

equal to feed dog.

So this means we are saying let's select

all rows and columns where title is

equal to feed dog. Well based on our

table we only have one record where

title is equal to feed dog.

Therefore if we run this execution and

we will get only one record back which

is feed dog as a title.

Okay. Now the main use case for using

the wear clause is when trying to

identify a record by ID. We can pretend

that our API passed in an ID and we want

to return that specific ID and record.

Well, if we are in the database, we can

say select from to-dos where ID is equal

to two.

And to put this in plain English, this

is saying select all rows and columns

where ID is equal to two.

And this time when we run the script we

will get our single to-do record which

has an ID our primary key that is equal

to two.

Let's now move into the update clause.

The update clause is used when we want

to update a record in the database.

Now to update a record we will have to

use a few different attributes.

We will have to say update to-dos set

complete equal to true where id is equal

to five.

So in theory this is saying update all

rows and columns to now have complete be

equal to true where a record has an ID

that is equal to five.

Therefore, the record with the ID of

five will change the complete column to

true.

This can be kind of confusing at first,

but the more you work with it, the more

sense it starts to become. We are saying

we are making an update to the to-dos

table and then setting a column field to

a new value and then just locating the

record we want to update to make the

update true.

Now on the last slide, we use an ID to

identify which record to update. This is

by far the most practical and best way

to update a record is to always locate

the record by the primary key because

the primary key is going to be unique.

However, it is possible to push an

update when not talking about the ID.

For example, we could say update to-dos

set complete to equal to true where

title is equal to learn something new.

This will essentially update all rows

and columns to now have complete equal

to true where title is equal to learn

something new.

This will work and modify the record

where the title is learn something new.

However, the issue with this approach is

that there could be more than one title

that says learn something new and that

means nothing is unique. So if you want

to use this approach, always remember

that you know the title is not a unique

name. Therefore, we could be changing

multiple records to true.

All right. And now for the final clause

for this basic SQL intro is the delete

clause. This clause is used to delete a

record from a database.

Again, the best way of deleting a single

record is by the primary key, which in

this case is the ID.

We can say delete from to-dos where id

is equal to 5.

This statement says to delete all rows

and columns where id is equal to 5.

Therefore, after the statement gets

executed, we will be left with a table

of five records. However, the record

with the ID of five is completely

deleted. Now, in many applications, if

you do not want to completely remove a

to-do, so a user has a history of

to-dos, the application will create a

table that holds quote unquote deleted

records. This way things are not

permanently erased but just removed from

the table of the main application and

moved to another table where the table

still exists.

And as always, you do not have to delete

based on ID or a primary key even though

it is significantly more safe. We could

write delete from to-dos where complete

is equal to zero.

This will delete all rows and columns

where complete is equal to zero. So if

we do not change any of our complete

areas to true, we would essentially

erase and delete our entire database.

This is the risk in using the delete

functionality when not using it by a

primary key to delete a specific record.

All right. Now, I know we went over a

bunch of SQL commands and the most

popular commands when dealing with CRUD

operations in case you want to

manipulate the data yourself. Luckily

for us, SQL Alchemy handles a lot of

these commands on the application side.

So, we don't have to worry about writing

the commands ourselves to manipulate the

data.

All right. Well, cheers friends. And

this wraps up this video and I will see

you in the next lecture.

Hey everybody and welcome back to

another lecture with coding with Robi.

In this lecture I want to show you how

to manipulate our SQLite 3 database

using our terminal after successfully

installing SQLite 3 under your machine.

So once we're in our project, let's open

up our terminal.

And the first thing we need to do is cd

over into our todo app.

Once you're in your Tudo app, let's type

in SQL light 3 and then let's type in

tuddos. DB and tudos.db is going to be

mimicking the database that you created

within the folder.

So immediately we get our SQLite version

and it's telling us to tap and enter if

we want to help. I'm going to scroll our

terminal all the way to make it full

screen so we can see more.

The first thing I'm going to do is type

in schema. This will show you all the

tables that are currently within our

SQLite 3 database.

We can see that we have a table of

to-dos that have an ID, a title, a

description, a priority, and complete.

And then it's telling us our primary key

is our ID.

So let's insert a record into our to-do

table. We can accomplish this by typing

in insert into to-dos

and then within parenthesis typing in

title description priority and

complete.

And then making the values. Go to the

store.

Pick up eggs.

Five and false.

And here it's telling me I forgot a

semicolon. So you can just type in

semicolon if you ever get these dot dot

dots and arrows.

And now let's type in select all from

to-dos. However, all is going to be a

star. So select star from to-dos.

And don't forget that semicolon.

Here we can see that we have go to the

store, pick up eggs, 5 0 means false,

one means true,

and then our ID is getting automatically

created because we have integer not

null. So it's automatically going to

increment up. So we have a primary key

for each element within our table.

I'm going to press up on our right arrow

pad. And I'm going to press up one more

time. And then one last time until we

get insert into again. Here I'm going to

just modify this list so we can add

another to-do to our table.

Instead of go to the store, I'm going to

say cut the lawn.

For the description, I'm going to say

grass is getting long.

I'm going to make this priority a three

and then also false. However, I'm going

to add a semicolon at the end.

Now, if we say select all from to-dos,

we will get two elements returned.

We get two elements go to the store,

pick up eggs, and we also get cut the

lawn, grass is getting long, and the ids

are one and two.

I'm going to create one more element.

I'm going to say feed the dog.

He is getting hungry.

I'm going to make this a five and also

false.

And one last time, I'm going to say

select all from to-dos

where we now get three elements.

Now, if you're like me, you don't like

the view of how this comes in

automatically and by default for SQLite

3.

You can change the mode by typing in dot

mode

and then typing in what type of mode you

like at the end.

A few examples are column.

And now if we say select all from

to-dos, we're going to get a nice little

column list with ID, title, description,

priority, and complete.

You can do mode mark down.

And if we type select all from todos, we

are now going to get a new list of the

same elements.

You can do dot mode box

which is going to make it a sealed box

as the return. Or you can do my favorite

which is table

which is a very similar to the box but

with dashed lines.

Now I'm going to create one more

element.

I'm going to click up until I see our

last insert.

And here I'm going to type in test

element.

and I'm going to press enter. If we now

say select all from to-dos,

we now get a table view of our elements.

And now let's say we want to delete test

element. We need to say delete an

element from this to-dos table where the

primary key of id is equal to four. And

the reason we're using the primary key

is because the primary key is the only

thing within this entire table that

cannot be duplicated.

To do this, we can say delete from

to-dos where id is equal to four.

If we now say select all from to-dos,

we can see that the element was erased.

Now let's go and create one more

element.

And here I'm going to say a new test

element.

Now if we say select all from to-dos,

we can see that it reused the primary

key that we deleted.

So as for an example, it doesn't just

skip four and then go to five. It reuses

some of the elements. And now I'm going

to delete this last element. So delete

from to-dos where ID is equal to four.

I got my semicolon and then select all

from to-dos.

And now we have our starting elements

for our new to-dos application.

This wraps up SQL light and I will see

you in the next video.