Jose: Hi guys and welcome back.
In this video, we're going to learn
how to interact with PostgreSQL
using ElephantSQL here in the browser.
I've left a link to the e-book in the resources section
of this lecture so that you can read
how to use HeidiSQL instead if you prefer.
So the first thing to do is navigate yourselves
to the Browser tab here on the left,
and then you get our SQL Browser.
Here we can type out our queries.
Also remember you can make this
bigger if you want more space,
and you always zoom in and out if you want.
At this point, I'm gonna zoom in so that you guys
can see a bit better what I'm typing,
but of course it's gonna make things look a bit chunky.
Don't worry too much about that.
And also I've lost the side of my field,
so I'm gonna make it
a little bit smaller first, there we go.
Okay, so now we're zoomed in,
and we can see what's going on a bit more easily.
So when we type in here,
we're gonna be typing our SQL queries,
and then we can press Execute to run them.
Whenever we run queries,
we'll get this Previous query here that we can use
if we want to go back to a previous query
that we've typed before,
and we've got Table queries
as well that will be helpful later on.
We don't have any tables at the moment though,
so we're going to create one.
We're gonna do, for example, create table.
We're gonna called it users,
and the ID's gonna be integer primary key,
and the name's gonna be text.
So here we've got a perfectly valid query.
Notice that one of the drawbacks of ElephantSQL
is that we don't get syntax highlighting,
so it's not really clear when we
make a mistake or something like that.
That's one of the benefits of using
a local database viewer like HeidiSQL.
But for now, this works,
so we're gonna press Execute,
and that is going to create our table,
nothing is returned though.
Then we can insert some data.
For example, insert into users the values one and Bob Smith.
We can execute this,
and again we won't get any rows returned.
Now, you can see that we still get no tables here.
We can refresh the page,
and that is now going to change
and allow us to get this table.
If we click on it,
we get this select star from public.users,
and we get only 100 rows.
Notice that here is PostgreS we've got public.users,
and they are inside quotation marks.
This is not necessary, we can remove the quotation marks,
and it still works in the same way.
However, it is convention to put
quotation marks around table names,
and that's double quotation marks.
If you use single quotation marks, that is for strings,
and we don't wanna be using that.
Also in PostgreS, we've got another layer
above the table which is used
to group together multiple tables that are related
and maybe a few other things as well,
and those are called schemas, and that is what public is.
By default, when you create a new PostgreS table,
you have one schema called public,
and that's the one where we create things
by default into as well.
So now we have created the users table
inside the public schema.
If we run this, we get back our data.
As you can see, we get the ID and the name.
Now this query here is saved into our previous queries.
You can see that I've got a few others there as well,
but you can reaccess that one if you want.
Notice I forget the semicolon at the end,
but that's not necessary unless you're
running multiple queries at once.
Also, I'll repeat at this point that you
can always access any tables,
and you can create tables, and select from tables,
and all that stuff without declaring the schema.
For example, just saying users,
or indeed just saying users
without the quotation marks if you prefer.
And whenever you do that,
whenever you access any table without declaring the schema,
that is going to be accessing the public schema.
You can of course put public if you want,
and that's also totally fine.
Later on when you create new schemas,
if you ever do, which I have never had the need to do,
then you will put your schema there.
All right, now that we can interact with our database
and we've put some data in here,
let's learn how to do it using Python.
I'll see you in the next video.