My name is Nelson. And in this course, you
will learn about a database called Postgres. Postgres is by far one of the most popular
databases out there. And the cool thing about it is that it's open source, robust, high
performance. And it comes with a lot of great features. And for that reason, a lot of startups
are using it for their back end applications. And you as a software developer, or engineer,
you should be aware of this database, how to use it for your own projects, as well as
for your own career as a developer. We're going to start this course by understanding
exactly what a database is the story behind Postgres, and then I'm going to show you exactly
how to set it up on both Windows and Mac OS users. Now, I want to be really straightforward
with you in this course. And that is, we're not going to learn how to use Postgres or
this database called Postgres, by using some graphical user interface. Because I don't
think that's fair when you learn a database by pretty much just clicking and dragging,
and, you know, adding things by using some uy, because that way, you know, really understanding
how the actual logic behind works, right, so in this course, we're going to be using
a interactive shell called p SQL. And pretty much, we're going to be using the terminal
or command line. And just let you know that it's not difficult, I'm going to make sure
that it's very straightforward. And by the end of this course, you'll be very familiar
by using terminal or command line to work with databases. And to be honest, if you were
to SSH into a remote seven, you wouldn't have the ability to use one of those graphical
user interface clients, because to be honest, like no one does it right. And also, sometimes
it can be very slow, so on and so forth. But towards the end of this course, obviously,
I'm going to show you, you know, available options for both Windows and Mac users. But
the best way of learning any database is by getting your hands dirty by pretty much learning
the raw commands behind everything you do. So once we set up everything, we're going
to go ahead and dive into the fundamentals and essentials of Postgres. So Postgres uses
SQL as its main query language. And we're going to go ahead and learn how to create
databases, how to create tables, how to insert records into the database, how to delete,
update, and also understand you know, how we can actually join two tables together foreign
key relationships, sequences, how to export to CSV, grouping, aggregation, database constraints
to make sure that we don't have garbage data in our tables, primary keys, and a whole bunch
of other stuff. Without further ado, let's go ahead and learn this awesome database called
Postgres. All right, in this video, let's go ahead and learn what exactly is a database.
A database is a place where you can store, manipulate and retrieve data. So usually,
this data is often stored inside of a computer's server. So basically, you put data into it,
and then you can retrieve, you can see data, you can manipulate, you can delete, you can
update, you know, all of the operations provided by the actual database. So let me show you
a quick example of where you might see, you know, data coming from a database. We all
know Facebook, right? So Facebook is a place where we can connect with different people
from all around the world. So with Facebook, they actually store a lot of information about
us. And the data that they store about us is in a data base. Right? So for example,
you know, our names, our friends, our legs, everything is stored in a database. And then
once you actually consume or you you go to someone else's page, and view, you know, all
the comments, so on and so forth. So all of that data is coming from a data base. The
same with eBay, right? So if I show you this product right here, you can see that you've
got a title you've got, how many sold if what the price, if I scroll down, you've got the
description right here. And basically all of this, so even the reviews as well. So all
of this is coming from a database, they have to store this information. And then anyone
visiting this page can see this data. So, to summarize, a database is just a place where
you can store, manipulate and retrieve data. Let's go ahead and learn exactly what PostgreSQL
means. So Postgres is the actual database engine. And then SQL is the actual Structured
Query Language. So this Structured Query Language allows us to work with databases, so basically
interact with it. So SQL is a programming language that allows us to have commands like
this. So select where select is the actual SQL command, and then we have to specify some
columns, right. And then from an also from is a reserved keyword for SQL, and then the
actual table name. So SQL allows us to manage data in a relational database, essentially.
And it's very easy to learn. So you saw that the syntax is simply select columns, and then
from and then the actual table or tables. And being easy. It doesn't mean that you can't
do you know much with it. In contrast, it's very, very powerful. And it's been around
for quite some time now since 1974. And it's very widely used all over the internet. So
it's a very essential programming language for anyone getting into programming. So the
question that you might have is how this data is actually stored. So data is stored in tables.
And these tables are formed by two things. It's formed by columns, and rows, right, so
it's just a regular table. And you might have a table, for example, called person. And you
might have find the columns. So the attributes of a person as columns, right, so these are
the columns. So a person may have an ID, first name, last name, gender, and age. And then
the actual rows is the actual data inside of that table. So you can see that I've got, and Smith, social female, age 44, then you
could have another row, Jade Jones, so on and so forth. So you can see that we have
some rows and some columns. So I've mentioned that SQL allows us to manage data in a relational
database. So what relational database is, it simply a relation between one or more tables,
right. So this is how data might be structured inside of a relational database. So you might
have a table called person. And also you might have a table called car. And basically, these
two tables, they have a relationship between them. So a person may or may not have a car.
So you can see, for example, and Smith, she doesn't have a car. So the car underscore
ID column is blank. But if you look at Jake and Julia, they have a corresponding car that
points to the car table. And this is what a relationship is made of. And don't worry,
we're going to cover lots of this throughout this course. But so that you are aware, a
relational database is when two or more tables have some kind of relationship between them.
Because usually, you know, in these databases, you have lots of information to store, right.
And what you don't want to do is actually have unstructured tables, where you pretty
much have a table that stores everything. And then it makes it very difficult to manage
to query and perform other operations. So what you would do is actually split, you know
your information into tables, and then have some kind of relationship between them. Alright,
so the database that you're going to be learning in this course, it's called PostgreSQL, and
basically is the most advanced open source relational database out there. It's very popular
because it's open source. And it's been active in development for about 30 years. So you
can see right here, and basically, it's very, very popular, reliable, and a lot of new startups
do use Postgres instead of using some other database engine such as Oracle, because they
don't have to pay for a license. Right. So this is one of the advantages over you know,
Oracle. And this is why I chose to teach you PostgreSQL Well, because I think that you
will be in a good place once you learn PostgreSQL or simply Postgres. So as you saw, Postgres
is an object relational database management system. So it allows us to work with relational
databases. It's more than, and it's open source. So, you know, I'm not just saying that both
grits, you know, it's amazing because it's open source. And it's been there for about
30 plus years in active development. But you have other options such as Oracle, which you
have to have license for it. And my sequel, which is owned by Oracle, and then you have
SQL Server owned by Microsoft. And pretty much you have 1000s of these database engines
that you can pick from, but I'm teaching you the most popular one, which is Postgres. All
right, from now on, we're just going to be coding. But first, what we need to do together
is download PostgreSQL. So if you are on a Mac, the easiest way to download Postgres
is simply by going to google.com, and then search for post gress and then app. So for
Windows, I'm going to show you guys exactly how to download this in a second. But if you're
on a Mac, let's go ahead and download Postgres app. And then right here, so if I scroll down,
you can see that they've got, you know, some ways on how to connect depending on on on
a language that you use. But essentially, what we need to do is go to downloads. And
what we're going to do is right here to see that they have additional releases. So right
here, they have the Postgres, 9.5 9.6 10, and 11. So pretty much, if you download this
additional releases version right here, it means that you can spin up all of these databases
with these three, or actually four versions. If you download the very first one, you can
only spin up a database with Postgres 11. So go ahead and download the additional releases. Just give me a second. So that's the now click
on it. Then I want to drag this into applications like that. And he was my password. There we go. There we go. That's done. So I'm going to
close this. Now go to finder, and then applications. And you should see Postgres right here. So
what I'm going to do is simply click on it. Close that. And also, let me inject this,
somebody checked that. So open, and there we go. So you can see that we have Postgres
11 right here. So if we click on this button right here, you can expand that. And basically,
if you click on the plus button right here, you can see that you can pick from all these
four versions that we download. So this is what I was saying that you can pick from these
versions right here. So I'm going to cancel out of that. And basically, I'm going to be
using Postgres 11 as I speak. But you know, everything that we're going to cover in this
course, will work with the previous versions of Postgres. So now, the only thing left to
do is simply to start your server if it's not, up and running. So I'm going to stop
that. And actually, you can see right here, so you can start the server. There we go.
It's running. And basically right here, you can see that you have the same settings. So
you can click on this elephant icon right here. And you can stop. And you can also start
your server from here. This is it. So now you have Postgres 11 up and running in your
machine. Let's go ahead and download Postgres on Windows. So open up Google and pretty much
just type Postgres and then download and go ahead and click on this very first link. And
right here, you can see that you can pick from different operating systems and we want
to download for Windows. So go ahead and pick a Windows, then go ahead and click on download
the installer. And in this page, right here, you can see that you can pick from different
version. So you have Postgres 1110 969594, and nine, three, which is not supported. So
go ahead and download the latest version. So in my case, I've got 11.2. So if we have
11.2 or above, go ahead and download because everything will work the same. So I'm going
to download 11.2, the 64 bit version for Windows. There we go. So now I'm going to save this.
And I'm going to open that in my desktop. So now what I'm going to do is simply double
click on this installer. Alright, so now go ahead and press next. And the next, leave
the installation folder as it is. And right here, you can see that we have some check
boxes. So the first one is the actual SQL Server. Right, so this is the actual server,
then we have the PG admin. So this is the graphical user interface client, stack builder.
So this is for additional drivers. And then we have the command line tools. So go ahead
and select all of those. And the next, next, and right here, add a password for the super
user. And remember this password because you're going to need it later for connecting to your
database, the next and then leave the default port as 5432. Next, and the same for Volkow.
Go ahead and press Next. Next, and now you can see that it's installing Postgres on my
machine. So just give that a second. And there we go. So that's it, we are done. So now uncheck
the checkbox right there, because we don't need to do anything extra with stock builder.
So go ahead and press actually and tick that box. And then finish. There we go. Now go
ahead and click on Windows icon, and then search for the letter P. So Postgres, and
you can see that we have 11. And inside we have the P SQL, so I'm going to grab that
and put it on my desktop. And the same for PG admin for so that's the graphical user
interface client. So just let me drag that. There we go. And let me put them right next
to each other. And that's it, you successfully managed to download Postgres on Windows. Now that we have our database up and running,
we need a way to connect to remember, our computer is serving as a computer server,
ie, a database server, really. And basically, anyone can connect to it, and view the contents,
modify content, and perform all the operations supported by the database. Now, the very first
way that we can connect to the database is by using a GUI client. And this is an application
where it eases the way that you connect to the database. And it makes life easy in terms
of performing, you know, insert into the database, delete, view the data and have like all these
fancy UI elements, that allows you to see database in a much easier way. The second
way is by using the terminal or command line, and this is my preferred choice. And this
is because this is how you get your hands dirty, by learning all the commands that your
database of choice requires you to learn in order to manipulate your database. And once
you learn how to use a terminal or command line, which is not difficult to be honest,
then using a GUI client, it's very trivial. The third way is by using an application.
So this is where for example, you write a server side application where you connect
your database and then you can perform data, and then return that data to your clients
so that the client can make the data look nice on a screen or a mobile application.
So let me quickly show you the actual GUI clients out there. But for this course, we're
going to focus on using terminal because this is how you will master any database. And also
if you ever need to SSH into a remote server, then you will be very comfortable using the
terminal or command line because you're not going to have any GUI client to be honest.
So let me show you the actual clients out there. And what are some of the options that
you can use if you were to use a GUI client so very great. is by far one of the best database
clients. And basically, you can connect to any of these databases. So right here you
see Postgres, MySQL, Oracle, SQL Server, Sybase, Maria dB, so on and so forth. So it's very,
very cool. And you can see right here, the UI is very slick. And basically, it makes
it easy for you to see the data, you know, perform, you know, alterations, inserts, updates,
deletes, on and so forth. But for this application, you need to buy a license, however, you have
a 30 day trial. But once that's over, you need to buy and the price is about 149 per
year. So for those on a budget, you can use post Ico. So post Ico is a similar to, but
less powerful. And essentially, this tool is actually just for Mac users only. So this
tool is free, you can download it, it's amazing. And it pretty much allows you to get your
job done. And for Windows users, you can download PG admin, which does the same thing that's
supposed to go, but the UI is not as nicer as the other ones. So as I said, learning
how to use the terminal, or command line is not scary at all. And I want to make sure
to make it super easy for you. And by the end of this course, you will be so comfortable
using terminal or command line. If you're ready, not. Alright, for Mac users, the way that you connect
your database is by simply open up this elephant icon, making sure that your database is up
and running. Then click on Open Postgres. And right here, you see that you have three
databases by default. So pretty much just click on any of these. And you can see that
my AI term or terminal actually did open. And if I make this bigger, so right here,
you can see that we are inside of this database mode. So this was the actual command that
was invoked. So applications Postgres app contents versions, 11, and then being an n
p SQL, so this was the command invoked, and then minus p for the Port 5432 minus d. And
then this stands for database Postgres. So if I open up the elephant, then open up open
Postgres. So you see that Postgres is the actual database. And if I go to service settings,
you see that it's running on port 5432. Now, I'm gonna come out of that, and then close
this. Now, if I open up a new shell, and then type p SQL, you will see that the command
was not found. And this is because we need to add it to the path. So to do so, I'm going
to simply edit my path. And this assumes that you are using I term as well as Oh, my Zed
sh, if not simply added to your bash profile. So just let me close everything here. And
then quit terminal. And now I'm going to open I turn. And now I'm going to type cd and until
and if I do LS minus a, you can see that I have few files in here. But the one that I'm
interested is this one here. So dot Zed sh RC. So I'm going to do VI, and then dot Zed
sh, RC, just like that. So now I need to add the export right here somewhere, press I,
and then simply say export, and then path equals two, and then dollar sign, and then
path, forward slash. And if I go back to open Postgres, and then open it again, so you see
that I'm after this path right here. So I'm going to grab all of that, Command C, and
then go back, and then this should be colon, and then forward slash, and then paste that
in. And we don't need the actual p SQL. So it's, I mean, add a space right there.
And then remove that. And then to escape out of that, simply press escape. And then column
w There we go. So Now if I say source, so to pick up the changes I made in Zed sh, r
c, there we go. So if I now clear the screen, command L, and then simply type p SQL, you'll
see that we have p SQL working without having to specify the full path. Alright, so previously,
you installed Postgres on Windows. And remember that we added these two icons, so PG admin.
So this is the actual graphical user interface client, and then P SQL. So this is the interactive
shell. In this video, let's go ahead and get the database server up and running. So as
I mentioned before, the best way for learning any database is by using the actual shell
command line. And basically, that's what we're going to do throughout this course. So first,
let me go ahead and show you how to connect to your database with P SQL. And then I'm
going to show you also how to use PG admin, which is a graphical user interface, which
I personally don't use. And you will see that is is not the same. So go ahead and click
on this, a shell. So this shortcut, so P SQL. And right here, you can see that it's prompting
you to enter a server. So if you were to connect to a remote server, this is where you would
add the actual URL. But because we are testing things locally, we will connect to our local
server. So go ahead and press Enter. And this will accept the default. Now by default Postgres
ships with a database called Postgres, so go ahead and also press enter the port, so
the default port for Postgres is 5432, enter, and then the username is also Postgres. So
go ahead and press Enter. And now remember that previously, we added a password. So this
is when we actually use it. So go ahead and use the password that you entered when you
configured. So I've added mine, enter, and there we go. So now you can see that we are
connected. So if I close this, so I'm going to close this, and then open that again. And
let's say that we want to connect to localhost again. And then let's actually connect to
a database that doesn't exist. So let's go ahead and connect to the test, for example.
So press enter, same port, username, and then the password. If I press enter, you see that
database test does not exist, right. So this is also this is how you connect to your local
database. So let me go ahead and connect to it one last time. And there we go. So now
I'm inside of this database. Finally, let me go ahead and show you also how to connect
to your database using PG admin. So this is the graphical user interface client, I'm gonna
click on this icon. Just give me a second. And there we go. And you can see that on the
top left corner, we have service, so I'm going to open that up. And then we have PostgreSQL
11. So go ahead and click on that. And now you can see that it's prompting us for a password.
So enter the same password. And my one was actually password. So obviously, you would
pick something way, way shorter than this, but my one was passed and then word like that.
And I'm going to go ahead and save the password so that I don't have to enter every single
time. Now I'll go ahead and press OK. And there we go. So now you are connected.
And you can see that we have one database right here. So databases, this is the actual
Postgres database, right, so this is the database. And then if I open up Postgres, you can see
that there is a lot going on. But don't worry, because we're going to cover some of these
things throughout this course. And this is pretty much how you connect to your database
using PG admin, which is the graphical user interface client for Windows. So as I said,
throughout this course, we're not going to be using any graphical user interface client,
but instead we're going to be using this shell All right, so P SQL. And because I'm going
to be teaching this course, on a Mac computer, the commands that I show you will actually
be the exact same thing for Windows, because I'm going to be using p SQL as well. Alright,
so in the previous video, we managed to add B SQL to the path. So now we can simply type
p SQL. And there we go. So just let me quit out of this. And basically, if we don't p
SQL, you should go into this mode right here, where you see that, you know, using PL SQL,
the version is 11. And if we need help, you can type help. So help. And there we go. So
you see that we get some help. Now, basically, every single command right here starts with
a backslash. So if I want to quit out of this mode, I can simply type backslash, n and Q.
And then, as you can see, I'm no longer in the actual p SQL mode. So let me go ahead
and do the time p SQL again. And if I clear out of that, and then if I simply type backslash,
and then question mark, you'll see that I get more help. And basically, there is a bunch
of things that we're going to cover in this course. So let me go ahead and press Q. And
if I press help, again, if I press backslash, and then l, and then press enter, you see
that this command simply lists all the databases that we have in our computer. So right here,
you see that I've got four databases, amigos code, Postgres, template, zero, and then template
one. So by default, these are generated for us. But we can create our own database to
do so we need to use a command that creates a fresh database that we can then create all
the tables inside of this database. And to do so we need to use the command create, and
then data base. And then we have to give it a name. So this database must have a name.
So the name can be anything you want. But what comes before it, meaning CREATE DATABASE
a must be exactly exactly like that. So you can either use lowercase or uppercase, so
you can say create and then data base. I personally prefer the database way because that way I
know what is SQL syntax and what is not. So I'm going to use uppercase, so create an database,
and then the name of this database will be test and then make sure to end that with a
semicolon because otherwise it won't Execute the command. So I'm going to press Enter.
And you see that we have this response back, which says create a database. Now, to view
the list of all databases, simply press backslash and then l enter and right here you can see
that this is a database that we've just created. Alright, so in this video, I'm going to show
you how to connect to databases. And there are two ways the first one is if I type p
SQL and Please follow along because this is the way that you will learn. So P SQL and
then dash dash and then help. So right here you see that we we get a bunch of help. So
these are the usages. So you can see that you type p SQL and then some options, and
then the actual DB name followed by the username. So right here you see that for database name,
you simply type dash D and if I scroll down, so you can see that these are the connection
options. So the host is minus or actually dash H or dash dash host equals to host name,
the same for port is dash B, and then dash, capital U for username or dash dash username
minus w or actually dash W and then dash capital W for no password. So let's give it a go.
So P SQL and then the dash and then age and then the host will be local host But if you
were connected to a remote server, then you would type the actual IP address, and then
dash and then you for the actual username, so amigos, and that code, and by default,
you can see that the actual username is amigos code. So before actually giving the username,
I'm gonna say dash and then P, and this is the actual port. So by default, our database
is running on port 5432. And that's default by Postgres. And then we can specify the actual
database name. So our database was called test. And if I press enter, you can see that
we are connected into the test database, if I was to quit out of that, and then write
the same command, but let's say that we want to connect to a database that doesn't exist.
So test one. And you see that we get this P SQL fatal error, saying that the database
test one does not exist. The same for, let's say, the actual user, so amigos codes with,
you know, multiple assets. And you see that the roll doesn't exist. And the same for the
actual port. So if I add one there, and you see that the connection was refused, so let's
go ahead and connect to test. And there we go. So now we are inside of the test database.
So this is one way to connect to this database called test. The other way is, if I press
backslash, n and Q to exit, and then clear the screen, I can type p SQL. And then I can
do backslash, and then l. So this lists the databases. And by the way, if you've missed
where I got this backslash L, command is from P SQL dash dash help. And at the very top,
you'll see that you can list the available databases. So I'm going to press Q, and then
P SQL, clear the screen, and then backslash L. So now you see that I do have this database
called test. Now the way that we can connect from here simply by saying backslash and then
see for Connect, and then I can type test, enter, you can see that now we are connected
to the database test as user amigos code, if I want to connect to a different database.
So let's say that we want to connect to this database called amigos code, simply type C
and then amigos, press tab, and that autocompletes for you, and then enter. And there we go.
So we can switch between databases just like so. So Postgres, enter, and there we go. So
let me go ahead and connect to test because this is the database that we're going to be
using. And there we go. And these are the ways that you can connect to any database. Alright, in this video, I want to share with
you a very, very important command that you should be aware of. And you should take extra
careful when using it. So in the previous video, I've showed you how to create this
database called test. And let's say that for some reason, you want to get rid of this database
called test, ie delete. So to create a database, you simply say create an end database, and
then you give it a name, in our case test. To delete a database, you simply say, drop,
and then database, and then the actual name. So and also make sure to end up with a semicolon.
I'm not just gonna run it yet, because I want to stress out why this is very dangerous.
So it's dangerous, because let's say that you have this database called test with five
or 10 years worth of history. So that could be, you know, if you have a business that
could be, for example, customer information, addresses, emails, you know, the transactions
that they made, login credentials, so on and so forth. So if you were to run this command
on that database, this means that all of its content is lost in a matter of milliseconds,
really is just that quick. So when you have, for example, a production application and
you log in or SSH into your box, you should never run This command because you will lose
pretty much every single data in it. And often, if you SSH or logging into a remote database,
then you should have access or some kind of monitoring to see what people are allowed
to do. And because I'm teaching you Postgres, it's absolutely fine for us to experiment
with this command. After roll, we don't have any important data in that database, right.
So let me go ahead and press Enter. And you can see that the database is gone in a matter
of milliseconds. Let me go ahead and press backslash, and then l. And you can see that
the database is gone. So no data in it. So often, as well, if you have one database,
you have to make sure that you have a backup of your data in case of any eventual accidentally
loss of information. So let's go ahead and recreate this database because we're going
to need it throughout this course. So creating an database, and then test and make sure to
end up with a semicolon, otherwise, the command won't execute. So I'm going to press Enter.
And if I go ahead and press Ctrl, l, and then backslash, and then l, you can see that we
have our database back again. So the point of this video was really to stress out our
dangerous arrays to execute any kind of job command. Alright, in this video, I'm gonna show you
how to create your very first table with Postgres. So to create a table, you need to write a
command such as this one, create table. So this is pure SQL, and then you define the
actual table name. And then inside of parentheses, you can have as many columns as you want.
So the columns have few attributes. So the very first one, that you must have a column
name. And then the second one that you also must have is the data type. So the data type
for that column name. And then if you have any constraints, you should also specify them.
So I'm gonna explain what this means in a second. But for now, let's say that, let's
say that you want to represent people in your database. So you would have a table like this.
So you have a table called person. So you would write CREATE TABLE person. And then
inside, you'll see that I defined the actual columns that this table called person has.
So the first one is ID, and the data type of a is int. So integer, meaning that it's
numbers. And then I have first name, which is var char. So var char is pretty much just
characters. And then 50 means that the maximum length that this column called first name
can have is 50 characters long, the same for last name, then I also have a column called
gender. And this as well is var char, so characters up to length six, and then I have date of
birth, and the data type is timestamp, maybe we don't actually need a timestamp, because
timestamp includes the full date, including our minutes and seconds. So maybe we can use
a date instead of timestamp. But I'm gonna show you that in a second. So this is it.
So let me show you a list of all data types that you might encounter with Postgres. This
is the documentation for data types within Postgres, I'm going to leave a link in the
description below, so you can access this page. But if I scroll down, you can see that
you have a bunch of information, so table of contents. So just let me scroll down because
it will be easy for you to see right here. So you can see that the data types that you
have can be big int. So this is a signed eight byte integer, big serial bullions for true
or false. You have characters you have var char. So this is the alias that I was using.
And then you have date. So this is the one that we should actually use for date of birth.
So as you can see, it only contains year, month and then the actual date and you have
double, you have JSON, you have money. So this is when dealing with with money. So any
currency amount, you have Merrick so four decimal, so this is the actual area so you
can say no metric or decimal, and then you have some other ones. So you have like small
int, you have cereal. So cereal is a four byte integer. But this is special because
he auto increments automatically for you. So I'm gonna show you how to use that in a
second as well. And then you have text. So when you have text, there is no max length.
And then we have others such as time, and then timestamp. So right here, you can see
that the timestamp contains the actual date, plus our minutes and seconds and plus the
actual milliseconds. And we also have view ID, which is a good one for IDs. And then
you can also have XML data. So go ahead and navigate to this page to familiarize yourself
with all of these data types. Let's go ahead and connect to our database
called test and create our very first table. So I'm going to go ahead and press backslash
and then see for Connect, and then test, and then enter, you can see that I'm now connected
to database test as user amigos code. So let me go ahead and clear the screen. So Ctrl
L. and to create our very first table, we need to write this command that I've showed
you previously. So just let me open the docs, watch the the slides, so that you remember
exactly what I have mentioned in the previous video. So create table, the name and then
the actual columns. So in uppercase for SQL commands, create and then table. And then
the actual name was person. And remember, this was singular, and then open parentheses.
And now if I press enter, this command won't be executed until I end up with a semicolon.
So now let's go ahead and have our very first column called ID. And this was int, so uppercase
int. And then we also had the first name. And this was var char and then 50. We also
had last name. And this was var char 52. We also had gender. And this was var char and
I think it was six or seven. But let's go ahead with seven. And then let's go ahead
and finally have the actual date of birth, so date of birth. And we said that instead
of timestamp, right, because we don't really know, you know, the time of when someone you
know, gives birth, we don't usually store that information. So let's go ahead and have
the data type as date. And I'm going to end up with a parenthesis. So open parentheses,
close parentheses, and then end that with a semi column. So if I now execute this command,
so press enter, you see that we have a table. Now the way that we see the list of all the
tables that we have in our database, is simply by pressing backslash, and then D. So D for
describe. So if I press backslash, D, and you can see that we have one table called
person, and you can see the type is table. Now we can even go one step further. And that
is to describe the actual table name. So person, if I press enter, and now you can see that
we have one table called person. And the columns are ID first name, last name, gender and date
of birth. And you can see the type so the data type, integer characters, and then date
right here. And there is some extra information so nullable so these are like constraints.
And you can also have default values when you create a record in this table. And this
is how you create a table using Postgres. So in the previous video, we created the above
table without any constraints whatsoever. So basically, what we can do is specify some
extra constraint into our table creation, twin force that before someone inserts a new
record into this table, it must satisfies these constraints. Because currently in our
table called person, we can go ahead and pretty much just create a person without an ID without
first name without last name, without gender and also without date of birth. So what is
the point of it In a person without any of this information, so to improve on that, what
we can do is simply specify the actual constraint. So on the table below, you can see that the
ID becomes now big zero. So this is an improvement because big zeros do increment by themselves.
And then I'm saying that this column must not be no, so not No. And I'm also specifying
that the ID is the primary key for this table. So the ID is what uniquely identifies a person
in the actual table. So the same for first name. So what I'm saying is not to know. So if you want to insert a person into this
table, you should specify the first name, last name, gender, as well as date of birth.
Let's go ahead and improve our table with these constraints. So what I'm going to do
is go back to iterm, or command line if you are on Windows. And remember a few videos
ago, I showed you how to drop a database, we can also drop tables. Again, you have to
be very careful when you perform this operation. But because I'm showing you how to improve
this table, we can pretty much drop it. And we don't have any data in it as well. So go
ahead and simply say job, and then table. And then the table name is called person,
enter. If I now do backslash, and then D, you see that did not find any relations. So
let's go ahead and improve on the actual table creation. So let me just clear everything.
And let's go ahead and say create, and then table, and then person, parenthesis, and then
inside, let's go ahead and have ID, this will be big, and then cereal. So big cereal means
that it's a signed integer, which auto increments, so big cereal, and then this will be not null.
So we must have one of these. And then primary key. So all of this is SQL syntax. Let's go
ahead and do pretty much the same for first name, var char. And then this will be 50.
Or actually, I do have a mistake. So I forgot to add not No. So I'm going to press column.
And this should break out. So actually, not column. So in that like that. And let's go
ahead and recreate this. There we go. So now not and then now enter. Let's go ahead and
grab last name there. And then this should be not and then no. And let's grab the actual
gender. So gender, this was var char and then seven, I believe, and then this will be not.
No. And let's also have the actual date of birth date. And then this also must be not
not. So you might be saying, okay, Nelson, so everything is not know when should a column
be nullable. So when a new person is added to this table, they may or may not have an
email, right, not everybody has an email. So we can go ahead and add another column
called email. And this will be let's go ahead and say var and then char. And this will be
you know, something a bit bigger. So let's go ahead and say 150 characters. And if I
say not know, so this is not true, because some people don't have an email. So I'm going
to leave this column as nullable. So now I'm going to end up with parentheses, and also
end up with a semicolon. If I now press enter, you can see that we have our table. If I Ctrl
l to clear the screen, and then backslash, D, you'll see that we have this person right
here. But we also have this person ID sequence. And the reason why we have this sequence is
because of the big cereal that we created. So big cereal, as I mentioned is an auto increment
number, so we don't have to keep on remembering the previous Number. So if I go to the docs,
and then you can see that right here if I can find it. So right here, auto increment eight byte integer.
So this person ID sequence is not a table, and you can see right here is simply a sequence.
So we can go ahead and simply say, forward slash, or actually backslash, and then D,
and then person, and then enter. And now look at this. So our table is much better. Because
we have these constraints right here. So not No, for ID for first name, last name, gender,
and date of birth, and also the actual email. So this email right here is because no, everybody
has an email, so it is nullable. In this video, let's go ahead and learn how to insert records
into tables. So far, we have a database called test with one table called person with the
following columns, Id first name, last name, gender, date of birth, and email. So let's
say that we want to insert a new person into this table. So the person will have the ID
of one name, and last name, Smith, gender, female, and the following date of birth, the
ninth of january of 19 8080. And this person does not have an email. So to create this
person into our table, we have to write the following command, insert into, and then the
actual table name. And then we have to specify the columns that we want to insert, in this
case, first name, last name, gender, and date of birth. So remember, this person does not
have an email, therefore, we don't have to specify the email column. And then we have
to save values. And then values takes an array of values, matching the columns data types.
So in our case, and Smith, female, and then the last column, date of birth is actually
date and not a string. So the way that you represent dates is by simply saying date,
and then you have to make sure that the year comes first, then the month and then the actual
day. So there we go. So this is how you insert new records into any table. So let's now say
that we want to insert a second record into this table, I have a second person. So let's
go ahead and simply say, insert into person. So insert into the actual table name called
person. And then the columns that we will insert to our first name, last name, gender,
date of birth. And in this case, this person has an email. So we also specify the actual
email. And then we simply pass all the values. So Jake Jones male, and then the date of birth
is the year is 1990, the month is January, and then the day is the test. And once we
execute that command, we will get a new person with an ID of two into our database. So as
you see, I'm not specifying the actual ID column. And this is because if you remember
correctly, the big serial data type, there's an auto increment for us, which means that
we don't have to manage this ID. So if we insert more people into this table, we simply
get the ID managed for us, I been auto incremented. So you will get 123, and four, so on so forth. Alright, so now I'm inside of P SQL. And I'm
going to press backslash, D, so lowercase D, and you can see the list of relations.
So this is just to refresh your mind. So we have person and then person ID, this is the
sequence for our ID. If you want to see just the tables, press backslash, D, and n t. So
this shows just tables and you can see we only have one table. So let's go ahead and
insert a person into this table. So the command is insert and then into and then the actual
table name so person, and then we have to specify the actual columns. So I'm not going
to specify the ID, because that's managed by us by this sequence right here. So person
ID sequence. So I'm gonna specify first name, last name, gender, and then date of birth.
So let's say that this person does not have an email, right? And that weird parenthesis,
and then if I press enter, this command won't be executed until you end the entire command
with semicolon if you don't remember. And then I want to say values. And inside parenthesis,
the values so it takes an array of values. And they have to match the same order as the
columns names specified right here. So the first name is n. And then the last name is
Smith. So she's a female, so female, and then the date of birth, his date, and then within
quotes, 1988, and then 01, for February, and then the knife. So remember that first comes
a year, month, and then the actual day. And then if I end up with semicolon, and then
enter, you see that we get this message right here, insert 01. So that means that the insert
did work, let's go ahead and pretty much just do the same command. But this time, let's
go ahead and add a male. So this will be male. So I've just press the up arrow, and I got
the same command. So in case you're wondering, so this is Jones, and then Jones. So this
is actually Jake Jones. And if I go back this awful, specify the actual email. So email.
And then if I go all the way down here, let's say that this guy is from 1990. And then let's
say the 12th. And then let's say the 31st. And I also have to specify the email. So let's
say that the email is Jake, and then@gmail.com. If I press enter, you can see that this same
command did work. And this is how you insert into tables. So far, we have two people in our table. And
so right here, and also Jake, in this video, let's go ahead and add 1000 more people in
our table, and also add a column called country of birth. So in order for us to add 1000 more
people into our table, we're going to use this website called maka rule. So this is
simply a data generator, basically, we can generate data in various formats. So right
here, you can see that we can select our fields. And basically, you can then select the types
and have some options. So for our table person, we going to exclude the actual ID because
this is managed by the sequence for us. So we have first name, last name, and then gender.
And we don't actually have the IP address. And let's go ahead and add the date, and then
and then birth. And this will be date. So if we select on the type, and then simply
search for date, and right here, we can actually specify the format so your mouth and day and
less, also configure the actual email. So let's say that 30% of them will be known.
So we want to have some people in our table with nullable email addresses, and 70% of
them will actually have an email. And finally, what we need to do is add another field. So
let's actually call this country of birth. And instead of date, let's go ahead and pick
up country. So there we go country. And I think this is good for now. So now you can
see that we can turn away 1000 rows. And basically if you want more than 1000 you can keep on
downloading 1000 each time, or you can actually sign up and then you can generate more than
1000 rows. So 1000 for us is fine for this course, and format go ahead and change that
to SQL we can see that they have XML Firebase cars Andra, JSON science or fourth. So go
ahead and pick SQL, and then the actual table name. So let's actually change this to person.
And what we're going to do is actually include the CREATE TABLE. Now go ahead and preview.
And you can see that the data contains a bunch of random people, some with email, and others
without email. And if you click on the SQL, you can see that we have a bunch of inserts.
And right here we have the actual create table. So you've learned this in the previous videos.
So now let's go ahead and close out of that, and then simply download the data download.
There we go. So now we have this person dot SQL. And to open this file, I'm going to be
using VS code. So VS code for me is the best ID out there for working with SQL files. And
pretty much like with web development, as well, so this is the one to go. But if I'm
doing like more back end, server side work, then I use IntelliJ. So you can use VS code,
or atom. So atom is actually good as well. Or you can use sublime. So go ahead and pick
your favorite ID. But for this course, I'm going to be using VS code. So I'm going to
open that file with VS code. So I'm going to open VS code, and then file and then open.
And then inside of downloads, this is my person dot SQL. And there we go. So now you can see that we
have the CREATE TABLE. So you've learned about this. And also you learn how to insert into
tables. So insert into person, first name, last name, email, gender, date of birth, and
also our new column called country of birth. And then you can see all the values. So there
is one change that we have to do. And that is to make these fields in other words, so
I'm going to select those comments right there. And then I'm going to say not, and then no,
apart from the actual email, remember, the email is nullable. And also, let's go ahead
and increase the actual size of the email. So let's say 100, or actually 150. And then
the gender should be actually seven. And country of birth, I think 50 should be fine. So I'm
going to save this. Now we could actually grab everything. So copy and paste into our
terminal, and then execute all of these statements. But what we're going to do is something much
more clever than that. So go back to your terminal or command line. And basically, if
you press backslash, and then and then question mark, you can see that if I go back down,
so you can see right here, in the input and output section, we have this command right
here, backslash I, and then you specify the actual file. So this executes commands from
a file. Now, I'm going to come out of that, and then open up a new shell. And if I make
this bigger, so what you need to do is to actually navigate to your downloads, or or
whatever you save the file, so I'm going to navigate to documents, or actually was downloads,
so downloads, there we go. And now if I do an LA, you can see that we have person dot
SQL right here. And to get the actual path, I simply have to type pwd. And you can see
that the path is uses amigos code and download. So I'm going to grab all of that. So Command
C, go back, go back to my main shell. And now we can go ahead and execute that file.
So let's go ahead and press backslash and then I, and then paste the actual file destination,
forward slash and then person dot SQL. If I now press enter, you see that we get errors.
And the error is that the country of birth of relation person does not exist. And that's
correct. So if I clear the screen, and then do a backslash, and then D, and then person,
you can see that we don't have the country of birth column right here. So let's go ahead
and drop this table. That's right, drop. And as I've mentioned before, using the drop command,
it's not ideal, but because I'm just teaching you how to use new commands, and this is just
for illustration purposes, it's absolutely fine. But if you have a production database,
do not run this command. So to drop a table, simply say job and then table and then the
table name so person. So if I execute this All the data that we have, which is to students
will disappear, as well as the actual table. So if I press enter, if I press backslash,
D, you see that did not find any relations. So I'm going to clear the screen. And now
press up a couple of times. And we're going to execute the same command. So backslash
I, the destination to the actual file, and then the file, so person dot SQL, if I press
enter, now, you can see that we have a bunch of inserts, and everything worked as expected.
Now, if I clear the screen, and then type select, and then start from an in person,
semi column, and then enter, and there we go. So you can see that now if I make this
smaller, so fullscreen there, you can see that now we have a bunch of random data. But
there is one thing that we forgot in that then that's the actual ID. So let's actually
fix that cue, and then go back to the actual person dot SQL. And to fix is very easy. So
let's simply add, ID, and this will be a big cereal. So we had a big cereal, and this was
not and then no, and it was also the primary and then key. So now if I save this, remember,
we don't have to add it here, because the big cereal will manage that for us with a
sequence. So what we need to do is actually go back to iterm. So let's again, drop the
table. So drop, table, and then person. There you go. And
now let's run the same command, so backslash I, and in the file, enter, there we go. So
that worked. If I clear the screen, and then select all. From person, Enter. And there
we go. So now we have our ID back, and we have a bunch of people into this new table.
There we go. So I just want to show you how to drop tables, and pretty much just add a
new column called country of birth, to use it for selection purposes. Alright, so let's
go ahead and read all the records stored in our person table. So to grab the records from
this table, we have to issue this SQL command. So we have to say select, and we're going
to select star. So I'm gonna explain what star means in a second, and then say from,
and then the actual table name, so person, and that with a semicolon. So if I clear the
screen first, and then run this command, you can see that we get two people in our table.
So first, you can see that we have n. And then we have Jake and the ID is actually managed
by the sequence. So one and then two. So the actual star key word. So select star means
that you want to select every single column from this table. So if I was simply to say
select, and then nothing, so select from person, and then enter, you see that we get two rows,
but we haven't selected anything. So I can go ahead and say select, and then for example,
first name, and then from person, and that with a semicolon. And if I press enter, now
you can see that we only got back and tricks, or we simply selected the first column. Let's
go ahead and select first and last name. So select first name. And then if you want a
second column, go ahead and press comma, and then say second. And then name. There we go.
So if I press enter, Oh, actually, second name doesn't exist. So it should be a last
name, my bad. Enter. And you can see that we have an end, Dan Smith, Jake, and then
Jones. Now let's go ahead and select Email to see what happens if we select someone that
doesn't have an email. So I'm going to do select, I'm going to say from person, if I
press enter, you can see that the first row is empty, right? It's so it doesn't have a
value and that's true because she didn't have an email right here. So if I now scroll down
Let me simply go ahead and say select. So you see me press up a couple of times. So
select star from person. There we go. And this is how you perform the very basic read
operation, which is selecting everyone from this table. In this video, let's go ahead and learn how
to sort our data using the order by keyword. So the order by keyword takes a column and
an orders the results that we get back by ascending order, or the sending. So ascending
means that if you have numbers is 12345. And this is a sending, so you can see that the
numbers are increasing. Well, the sending is 54321. And you can see that this is this
ending. And these are the actual keywords that we use in conjunction with order by.
So let's go ahead and pretty much just do a select star, and then from person. And then
if we want to order by the actual country. So we can say order, and buy. And then country
have an El birth. And by default, the way that this statement will be sorted is by ascending
order. So I can even include ascending or live it like that. So just let me show you.
So if I press enter, we actually have misspelled country. There we go, press ENTER again. And
now you can see that the results that we get back are sorted by the actual country. So
in ascending order, meaning a, and then B, C, D, so on and so forth. So basically ascending
and descending, they both work for dates, numbers, and strings. So let's go ahead and
quit out of this. So remember, I said that the default is ascending. So if I include
ascending, you can see that the results are the same. If I press Q, and then go back,
and now let's reverse the order. So the sending meaning from Zed to a, I press enter, and
see that now the results are sorted from the last letter of the alphabet to a. So if I
press Q, we can actually sort by the actual ID. So if I do ID, press enter, you can see
that he goes from 1999 all the way to one. And then if I reverse this, so ascending,
e goes from 1234567. So if I press Q, this also order by the actual first name, you can
see we go all the A's. First, let's go ahead and do the sending. We see that we have all
the zetz first, so we can also sort by the email. So instead of first name, last name,
because it's the same as first name, so email, and then descending, enter, you can see that
first we get all the notes, right, so the notes because these are empty. And then if
I keep on scrolling down, so right here, so oops. So right here, you can see that we have
then Zed, right Zed, and then why w so on and so forth. And basically one last thing
is that you can combine multiple columns when you sort. So let's go ahead and order by the
actual ID, so Id and then email. If I press enter, you can see that first we have id 123.
And then we also have the actual email starting from a. So basically, sometimes it's a bit
hard to understand exactly what's going on, because you have for example, five here, but
then you have G and then you have to see, but basically, the rule is when you thought
the rule is when you saw your data use at most one column. And finally, I also forgot
how to sort by the actual date of birth, so by and then date of birth, and then enter
can see that the results are now sorted by the 1920 all the way to 2017. And if I do
the ascending, so descending, you can see that we have 2017 first all the way down to
1920. This is how you saw your data using order by let's go ahead and select the country.
of birth. From an N person, if I press enter, you can see that we're actually let's also
apply some sorting. So order. And then by, and then country of, and then
and that with semicolon, and then enter. In, you can see that we have Afghanistan, Afghanistan,
Albania, Argentina, and so on and so forth. But you can see that we have lots of duplicates,
right? So you can see like Brazil, there's quite a lot of them. And then Bulgaria, Canada,
China, probably the biggest population in the world, so lots of them. So let's say that
we just want to know the unique countries that we have in our table. I, we just want
to see Afghanistan once Albania once Argentina once or mainly once, so on and so forth. To
do that, what we need to do is select and then we can use the distinct and then the
distinct takes the actual columns. So country of birth, and then from person. order by and
then country of birth. If I press enter, and now you can see that we have Afghanistan once
Albania once and or once, Angola, so on and so forth. So I can, you know, scroll down,
and you can see all the countries that we have. So 124 countries in total. So I can
reverse the order as well. So if I do D, E, Sc, for descending, enter, and there we go.
And this is how you use the distinct keyword to remove duplicates from your query. So we
use countries but you can also use it for dates, emails, pretty much any column. Let's
go ahead and learn about the where clause. So the where clause, if I close that, so WHERE
clause allows us to filter the data based on conditions. So the very basic condition
that we can actually do with our table is we can go ahead and say select and then start
from person. And then we can use where so where a column or columns meet certain criterias.
So we can go ahead and simply say, where and then gender equals two and then female, if
I end up with semicolon, and then press enter, there we go. And now you can see that the
results that we go back contains only female, so if I keep on going, you will see that we
only have female. So I can go ahead and also say it, let's go ahead and pick the actual
male. So where the gender is just male, enter, you can see that now we only have male. So
we can use the where clause to filter based on column, or column. So we can actually have
multiple conditions. So if I go back, so if I press Q, and then now, to actually use a
another condition, I can go ahead and say and so we can use the Add keyword. And we
can pretty much just filter for example, where the country and then off and then birth the
horse. And then let's say Poland, if I press semicolon, enter, and you can see that now
we have every single guy that was born in Poland. So we can also say. So we can actually
combine these, so I can say country of birth equals to Poland. Or so I can say
or so in caps, or country of and then birth equals two. And then let's say China, and
then in that weird parenthesis, enter, and I've misspelled birth, wrong. So it's me go
back, so it should be T and then H and then enter. You can see that now we have every
single guy that was born either in China, or Poland. There we go. So we can also filter
a bit more. So let's see if we have anyone with the same last name. So I'm going to grab
that last name. So Peter SMA, I don't know if I spelled that correctly or not. But I'm
going to press Q and then I'm going to add a Nether and so this this, let me just press
enter and and then a last night name equals to Peter's mouth. Sorry, it's a funny surname.
So if I press my column and then enter, you can see that we only have one person with
that surname. So let's actually see if we have any female, change that to female, enter,
and no females with that surname. What operators allows us to do, it allows us to perform arithmetic
operations, comparisons, bitwise, and logical operations. So most of the times, you're going
to be using arithmetic operators and comparison operators. So you still have the bitwise AND
logical operators. But basically, I often don't use those unless I'm doing something
very complex. But for most of the time, you're going to be using the comparison, as well
as the arithmetic operators. So let's go ahead and learn about the comparisons. So basically,
if I go here and say select, and then you can say select one. And now pretty much just
type equal, and then one. And if you end up with a semicolon, and then press enter, you
see that you get this column right here. This is what by default Postgres gives you. But
don't worry about this for now. But basically, you can see that right here, you performed
a select one equals one, and this has given you true. So this is the comparison operator.
So it allows us to perform comparisons based on certain conditions that we want, and then
it will either return true or false. So let's go ahead and say one equals to two. And we
all know that that's false. So you can see right here, it is false, we can also go ahead
and say, one is less than two, which is true. And we can say one is less or equal to two,
which is also true. But if also say one is less or equal, actually, let's go ahead and
say, less than one, this is false. So you can see right here, one is not less than one,
by false to say one is less or equal to one, it is also true. So you can flip the sign.
So this is the less so the way that I always remember these is that the less sign has the
shape of an L. So l for less. So if we'll check whether a number is greater than another,
you can simply type the opposite. So greater goes like that. And one is greater or equal
to one, it is also true, but one is not greater or equal to two. So you can see right here,
this is false. So you've seen equals, you've seen less or equal and greater or equal. So
what about if you want to check whether a number is not equal. So we could simply say
select, and then did not equal is simply this diamond right here. So one is not equal to
two. If I press enter, you see that this is true. So let me go ahead and pretty much just
type one, so one is not equal to one. And this is actually false, or one is equal to
one. And you see them using these comparison operators or numbers. But you can also use
them on strings, dates, and pretty much any data type. So I could go ahead and say select
and then amigos code, and then not equal to, and then let's simply type lowercase version,
amigos code, press enter, you can see that this is true, they are not equal. But if I
was to capitalize the second one, so amigos code and press enter, you can see that now
this is false. So I could also go ahead and pretty much just use the equal and you can
see that it's true. If I type an S down there, it is false. And this is how we use comparison
operators. And basically, you can use these comparisons operators to filter down your
data in the where clause. Alright, in this video, let's go ahead and
learn about the limit keyword as well as the offset. So let's say that we simply want to
select the very first 10 rows from this table. So what we can do is simply say select star
and then use the limit keyword. So I can say Submit, and then 10. So basically, this can
be any value. So what you saying is you want to limit the results returned by this select
query right here, so select star, and you simply want to limit by 10 Records. If I press
enter, you can see that we have the very first 10 people in this table. So I can go ahead
and simply say select. So if I click that, and then select and then start from person
limit, and then five, so you can see that we only have the first five. But we can also
offset the actual limit. Let's say that you want to select the very first five people
after this row right here. So to do that, you can use the offset keyword, so you can
say select, and then start from person have set five rows, and then limit five. So press
on my column. And now you see that we get everyone from six up to 10. So I could actually
go ahead and remove that limit. So this would pick everyone starting from row five, or actually
row six, all the way to the last one. So if I press enter, you see that it starts from
six. Now there is a another key word that allows us to perform the same thing as the
limit. So limit is not a key word by by SQL standards. So it was actually widely used
by different order databases, and then it became a thing. But the official way of actually
limiting the results coming from a query is by using the fetch keyword. So we can go ahead
and say the same thing. So select star from and then person, let's offset this by five.
And then you can say fetch and then the number of rows. So let's say that we want to select
the first and then five, and then row only. If I press a semi column, and then run it,
and you can see that we get the same thing. So I can go ahead and pretty much just select
the first row. So all I need to do is just say first, and then one row, or I can simply
just remove one. So this is the same thing. And basically, this is the same thing as using
the limit. But this is a sequel standard. Let's say that we want to select everyone
from China. So we have China right here. And let's say Brazil, and then France. So we could
write a sequel like this. So select and then star from an in person, where and then country
of birth equals to China. Or, in fact, let me put this on a neon line. So or country
of birth equals to France, finally, or country of birth equals to Brazil. And if I press
enter, you can see that we get everyone from China,
France and Brazil only. So this was actually a lot of code, just to include China, France,
and Brazil. And you can see that we are duplicating country of birth three times. So one there,
another one here and another one there. So we improve on this, we can use the in keyword.
So in keyword takes an array of values, and then returns a query matching those values.
So let's go ahead and improve this query with in keywords or select and then star. And then
from washlet never put it on this line. So from an in person. And then you can say where
country of birth in. And within parenthesis. This is where you pass your values. So China,
Brazil, and finally, France. And this is the exact same thing that we had up here. So right
here. So if I press the semicolon, run the command, and you can see that we get everyone
from China, France and Brazil only. So this makes it easy to add all the countries comma,
and let's also include Mexico, and let's also include Portugal, and one more so let's say
Nigeria. If I press enter, there we go. So you can see that we have China, France, Brazil,
Mexico, Portugal. And to make this easy, let's go ahead and remove that and say order, and
then by country of birth, and then semicolon, enter. Now you can see that we have Brazil,
and then China. So there's quite a lot of Chinese in this table and in France, and Mexico,
right here. And then you can see Nigeria, so only few people from Nigeria, and then
Portugal. In this video, let's go ahead and use the between keyword to select data from
a range. So let's see that we want to find out everyone that was born between 2002 1015,
for example. So we could do that using the between keyword. And basically it goes like
this Select star from so if I put this on in your line, and then I need to use the where,
so where and then date of and then birth. And now I'm going to use the between keywords.
So between. So basically, I'm saying select everyone between. So let's go ahead and say
date. So this is a sequel function. So remember dates, they start with the actual year. So
I said 2000. So 0101. And then and so this is the actual end. So let me explain this
in a second. So 2015, I said 0101. So basically, I'm saying select everyone from person where
the date of birth between these two dates. So this is the start. And then you have to
specify the end, if I press Enter. And this should be between, not between. So I'm just
missing at right there. And then enter. And there we go. This is how you select from a
range where you're specifying the start, and then the end. So in this result right here,
you will not find someone which has a date of birth less than 2000. And someone that
has a date of birth bigger than 2015. All right, in this video, let's go ahead and
learn about the like operator. So the like operator is used to much text values against
a pattern using wildcards. So I think is best for me to show it in action, and then you
will understand it right away. So let's say that we have, you know, this list of emails
right here. So these emails right here, and we want to find every single email that ends
in.com. So the way that we do that with the like keyword is simply by saying select person,
or actually select star from person, and then where and then you specify the column name,
so email, and then you can say email like, and then within quotes, you specify the actual
pattern. Now, I can do any character. So the wild card simply says any character followed
by.com. If I end up with semi column, and end, you can see that I only get emails ending
in.com. Let's say that we want to find if there is anybody with bloomberg.com email.
So let's go ahead and simply say Bloomberg, so at, and then Bloomberg, and then.com. So
basically, any character followed by@bloomberg.com, enter, you see that we have three people,
let's go ahead and try and find anyone with a google.com email. So google.com enter three
people, but there could be a case where we have employees from Google in a different
country. So for example, you know, HK, or France, or any other country, right? So we
can pretty much just remove the.com portion, and then use a another wildcard. So this simply
says any character so any card, any character followed by act, followed by a googled A lot.
So you can see the dot here, and then followed by anything. So this time, we should include
comm plus any other emails. If I press enter, you can see that this time we get a bunch
of more emails from Google. So this is how you use the wildcards. So basically, you specify
any characters preceding your pattern, or any characters before, you know, whatever
pattern you specify, as you saw right here. Now there are two things I want to show you.
So one is, instead of using the actual wildcard we can use underscore. So underscore simply
says that this has to match single characters. So let's say that I've got 12345678 characters,
and then followed by ads. And then if I press enter, you can see that we have a bunch of
emails right here. So you can count the characters. So all of them have eight characters followed
by the actual ad sign. So we could also say that, let's say that we want to find, you
know, any person that has at least three characters, followed by, let's say, Oh, right, and then
followed by at and then anything, if I press enter, there's no one there. If I press age,
let's just keep on trying, there was not, there's no one there. Let's go ahead and type
that. There's no one there also. There's no one there. There's basically there's no one
with this pattern right here. So you get the point. Now, there is one other thing that
I want to show you. And that is a keyword called a like. So let me first run the query.
And then you'll see why this is useful. So let's say that we want to find any email or
any country. So let's go ahead and say country that starts with P. So P, and then wildcards.
So this will match B and then followed by anything. So if I run this query, or actually,
it's not country, so country of no birth, you see that we get no one. But now let's
go ahead and put an uppercase B right here. If I press enter, you can see that now we
have Philippines, Poland, Peru, Portugal, and Paraguay, Pakistan, Palestinian territories,
so on and so forth, right. So you saw that I had to explicitly put an uppercase P. So
the I like keyword simply ignores the case. So it's case sensitive. If I put lowercase
b, and then run this, you'll see that we get the same result. In this video, let's go ahead
and learn how to use the grouping by keyword. So this is very powerful, and basically allows
us to group our data based on a column. So a good candidate for grouping by in our data
set is the actual country, let's say that we want to find out how many people that we
have for each of the countries that we have. So if I pretty much just select and then distinct,
and then country of birth from person, and then press enter, you see that these are all
the countries that we have. So in total 124 countries. But let's say that we want to find
out how many people that we have for each of these countries. For that we can use the
group by so the group by works as follows. Select and then the actual column name. So
let's go ahead and say country. And then of birth. And then if I say from and then person.
And then let's go ahead and say group. So this is the actual key word. So group and
then by, and then the actual column, so country, of and then birth. So if I run this query,
this will not work and I'm gonna explain why in a second. If I run this, you'll see that
we pretty much just grouping by the country of code. So the the country of birth, but
we're not getting the actual number of people for each country. And this is because we have
to select a second column. Now I will give you Second to see if you can pretty much just
guess what column we need to select from here. And it's none of the columns that we've specified.
Can you guess? Well, the column is actually count so we can use count. And this is not
really a column is just a function that counts. So let's go ahead and say star that counts
every single one from this country of birth. Right, so we select the country of birth,
and then it also does a count on the actual country of birth. So if I press enter, now,
you can see that we have Bangladesh, there is only one person, Indonesia, 96 people,
Venezuela, six people, Cameroon, three people, so on and so forth. So in fact, let me go
ahead and sort this. So order, and then by country, of birth. And so my column, enter,
and I can see the data organized. So Afghanistan, three, Argentina, and probably China will
be the biggest one right here. So China 182. And then you can scroll down and see Indonesia
96. So they have a big population there as well. And basically, that's all we have. So,
you know, very, very simple, but very powerful, because sometimes you want to get statistics
out of your database. And this is how you use the group by keyword. Alright, in this video, let's go ahead and
learn about the having keyword. So the Hammond keyword works with group by, and basically
allows you to perform an extra filtering, after you perform the aggregation. In our
case, we have count and count is simply summing up everyone from each country. So Afghanistan,
three people, Albania, three people, right. So if we want to add extra filtering, ie,
let's say that we want to find out, you know, all the countries that have at least five
people, right, we can perform that with having. And if I escape out of that, so the way that
we use the haven't keyword is after grouping by we specify the having, and made sure that
the having keyword must be before order by So literally, right after group by. So now
we can say having. And this now takes a function so we can pass a function. So let's go ahead
and say count. So count. And then. So basically, we do the same thing. So count and then star,
so every column, and an order by so that's like that. And now we can specify the actual
condition. So having count star, and then let's, let's say that the count must be at
least five, right? If I press enter, you can see that now we get every single country where
there is at least five or more people. So if I press Q, and then we're just gonna put
this small as you can see everything in one line. So if I book full screen, and then clear
that, so you can see that the exact statement, select country of birth, comma, count star.
So this is the counting of this column right here, from person group by and then country
of birth, and then having the count bigger. So this is the bigger sign than five, and
then we can perform the actual ordering. Press Enter, if you can see right there. So let's
go ahead and change this to a bigger number. So let's go ahead and say 40. Let's see all
the countries with 40 or more people. And you can see that the list is much smaller.
So Brazil, China, Indonesia, Philippines, Poland and Russia. So we could actually also
say, bigger or equal to 40. All right, let's see if we find anyone. Nope. So if we say
bigger than 41. So let's go ahead and say 41. So, you can see that Russia will be out
of the list. So Russia was here. But if I include equal so greater or equal, you can
see that Russia now is included. Now, if we pull anything above 180, just China will be
in this list. So if I go 180, so 180. And you can see that China is the only country,
I can also perform the opposite. So I can say, any country that has a population less
than was actually less or equal than 180. So China won't be included. So every single
country is included, apart from China. So you can see China is not on the list. So this
is it. So basically, you can use the having, plus, you know, this aggregation right here.
And basically, the way that you find all of these functions is if you go to the docs, and search for aggregate functions, and I'm
gonna leave a link in the description below. So you can easily access this page. And basically,
you can see that the aggregate functions compute a single result from a set of input values.
So you can go through there are tons of these, you can see that the one that we use was count,
so count right here, and then count with an expression. So we use count star. And basically,
this is the input of rows, right, so every single row, and you can use Jason aggregate
max mean, so checking, you know, the minimum age, for example, you also have some, and
if I scroll down, you have a bunch of things. So stat, so aggregate for functions, aggregate
for statistics, standard deviation, also ordered set, aggregate functions, mode, and then percentile
desc. So rank, you can go through, you know, this Doc, and pretty much just read and see
what kind of aggregation that you need for your query. Alright, in this video, let's
go ahead and learn about some of the most useful aggregate functions that you will end
up using, and what is some max and min. So basically, you've seen already count rise
in count already. But I want to show you how to use the max, min, and then some. And once
you know how to use these, then to use the rest is very straightforward. And basically,
once you understand your data, and the information that you want to retrieve out of it, then
you can come to this link right here and see what functions are available. So the first
thing that I want you to do is to create a brand new table using mock rule. And this
will allows us to generate 1000 rows into a table called car. So right here, so go ahead
and add a field called ID, this will be the row number, then make for the actual make
of the car. And the type is car make. And then model. And the type will be car model,
and then a price. So we have a price. And this will be of type money. And finally go
ahead and give the actual table a name. So car and also include the CREATE TABLE. And
the format has to be SQL, go ahead and download. And they can see that we have car dot SQL,
I'm going to open that up, and this will be big, and then C will not know. And then primary
key. And the rest will actually let's increase the the make and model 200. So this will be
100. And both not and now. And for the price, this will be of type numeric. And the precision
will be 19 and then two. And this should also be not an N No. And I'm going to leave the
link in the description of this video. So you can download this file and have the exact
same data as I do. So now I want to go to B SQL. And basically I just want to execute
that file. So if you remember, so if I do backslash and then question mark, you see
that we have this option right here, which executes commands from a file. So I'm going
to press Q, and then backslash and then I and if we don't know the actual path for the
actual file, go ahead and pretty much just type pwd, make sure you navigate to the actual
folder downloads in my case. And then if I do an LA, you can see that I've got this car
dot SQL. So this is what I need. So I'm going to copy This path right here, and then go
back, base dot forward slash and then current dot SQL, Enter. And I can see that we have
a bunch of inserts. If I do select and then start from car. And you can see that now we
have a bunch of cars. And one thing that I actually forgot is, if we go back to moku,
blue, just make sure that the price is between a nice range. So in my case, I've chose between
10,100 1000, it could be way bigger than that, obviously. But just for this video, let's
go ahead and keep the range a bit small. Let's go ahead and find out the most expensive
car that we have in this table. So to perform that, we need to use the max operation. And
to use it is very straightforward. We simply type select, and then the function is Max.
So this is the most expensive, so it's the max price. And then this takes the actual
column. So what we want to pass in all the for this function, determine the max value,
and if you have guessed, is actually the price. And then from an N car, press enter, you can
see that the most expensive car is the one which is almost 100,000. And we could also
get the actual minimum value. So instead of Max, you simply say min, just like that, and
you can see that the minimum car price is 10,000. So we could also get the average of
all car prices. So to get the average, you simply type a V, and then G. And now the average
car price in this table is about 55,000. So you see that we get these numbers right here.
So 55,256, and then dot 657, and then some some numbers, right? So we could actually
around the actual result. So to round the average value or some or even the minimum
value, you can simply say, round. So we're going to round the actual average price. So
if I press enter, now you can see that the actual value is 55,257. Now, we could also
go ahead and pretty much group the information and see the actual minimum car price for each
make. So to do that, we can simply type select, and then we want to get the actual make, and
then model. And what we want is actually the min. So let's go ahead and get the min price.
And from and then car. And now we need to do a group and then by and we have to group
by the actual car. And we're actually sorry, make and model columns. So if I press enter,
watch the semi column. And now you can see the minimum price for each make and model.
So if I go ahead and pretty much just select the actual max price. So change that to Max,
enter, you can see that the max price for the same one. So Oldsmobile silhouette is
85. So basically, this is actually the same as I as I as I can see. And that's because
we only have one make for this car. So we could actually drop the model. So let's actually
drop the model. So drop the model here, and on the Select side as well, and then press
Enter. Now you can see that this is a bit more different. Now you can see for Ford,
so the max value is almost 100,000. And then we have smart 11,000. And if we were to flip
this, so now let's go ahead and find out the min and then press Enter. And now you can
see that for sure, Ford has the minimum car price as then 1022 point 19 penances. So we
can pretty much do the same for average. So let's go ahead and select the average, press
Enter. And this is the average for each make. So let's go ahead and actually around this.
So if I press enter, you can see that now the numbers are nicely rounded. So far, you've
seen max min and average, right? So we still have to learn about the sum operator. Sam
right here. So some allows us to perform really addition of our data set. So let's go ahead
and sum the total price for every single card that we have in our table. So we can go ahead
and simply say select, and then sum, and price. So price, and then from an end car, or press
enter, and post grades is complaining about these commands that I've typed, which you
know, they're not commands really. So let's go ahead and say select again, and then some.
And then price from an end car. If I press enter, and you can see that the sum of all
cars is about 55 million, say dollars or pounds or yen's, depending on the currency that you
actually use. So you can see that it's performing a sum over the entire table. Now let's go
ahead and aggregate this and see the total sum by the actual car make. So go ahead and
simply say select. And then let's go ahead and say Car Wash the make. And then some, the actual price from an in
car, group, buy, and then the actual make. So if I now press semicolon, enter, and now
you can see that the Ford makes or right here has a total sum of 4 million. And you can
see smart right here, which is just I think one car, so 11,000, you have Dodge, who is
which is about two and a half million, Mazar it, so on and so forth. And this is how you
use the sum aggregate function. In this video, let's go ahead and learn some of the arithmetic
operators provided by Postgres. So these allows us to perform maths behind numbers. And basically,
we can use our data set to produce some kind of statistics or any kind of result really,
that you want. So for example, you want to find out the discounted price for a product
given 10%. Right, so you can run the query which selects that column, and then perform
some kind of arithmetic operation, and then produces you a result. And I'm gonna show
you that in a second. But first, let's go ahead and learn the basics of arithmetic operators.
So we can perform addition, so go ahead and simply type select. And then let's go ahead
and say 10, and then plus, and then two. So if I enter a semi column, I can see that the
result is 12. And right now you see that we have this column right here. So question mark
column, and then question mark. So don't worry about this, I'm going to show you exactly
how to rename this in a second, we can see that the result of 10 plus two is 12, we can
also go ahead and perform subtraction. So 10 minus two equals eight, we can also perform
multiplication, so times 10 times two equals 20. And also you can change these, right,
so if I perform 10 plus two, and then plus, and then eight, and see that the result is
28, we can also go ahead and perform division, oops, sorry, if I delete that, we can perform the
efficient. So 10 divided by two, you can see that the result is five right here, we can
also perform the power of a number, so 10 power two, so the way that you you read power
simply by using this hat, and then 10 power to we all know that is 100. So 10 power three
are actually three sorry, so 10 power three, you'll see that it's 1000. So we can also
perform the factorial of a number, so 10. And then the way that you perform factorial
is actually if I perform select, and then five, so I want the factorial of five, and
you simply place a exclamation mark. And if I run this, you can see that the result is
120. And the final operator I'm going to show you is the actual module. So the modulus operator
allows us to get the remainder after a division. So let's go ahead and say select. And then
let's say 10. And then mod, so you simply use the percent sign, so 10 mod three. So
let's think about this for a second. So how many times does three goes into 10 So three
goes into 10, three times. And then the remainder of the result is one. So you can see right
here, one, right. So the same, we could do the same for, let's say, the modules of four,
so 10, modular four. So four goes into 10, two times the remainder is two. And pretty
much if I perform six, so let's go ahead and say six, we're actually five first, so five
goes into 10, two times, so the remainder is zero. But six goes into 10. One time, and
the reminder is for. So this is all for this video. As you can see that the arithmetic
operators are very straightforward to use. And basically, like any other language, like
Java, C, sharp, c++, Python, you know, PHP, they all support arithmetic operators. Let's
go ahead and select every single car from the current table source of length and install
from an end car. So right here, you can see that we have a bunch of cars, and then we
also have the actual price. Now, let's say that we want to run a promotional discount
to every single car that we want. So let's say that we are offering 10% of the original
price. And what we want to do is to have a query that returns the original price, plus
the actual discounted price with 15%. off. So let's go ahead and press Q. And the way
that we will do that is simply by saying select. And let's go ahead and select every single
column by the column name, so Id and then make model and then price from an end card.
So just to see what we're doing so is exact same thing. So now we can go ahead and actually
perform a bit of calculation. So we want to grab the current price, so price, and then
we want to times that by point 10. So this is actually 10%. So if I press enter, you
can see that we have the 10% value of each price. So for example, 87,665 point 88 is
8766 point 53 point at, and you can see the same for all the rest. Now let's go ahead
and pretty much just round this to two numbers. So we can go ahead and use our round function.
So round times the 10% and n comma two. So this is two decimal places, enter, and I can
see the actual value into decimal places. And effectively, we want to know the discounted
price with 10%. So to do that, we need to pretty much just do almost the same thing.
So let's keep the 10%. And now I'm going to round again. So I'm going to round that result.
And within parenthesis, let's go ahead and grab the price. And then we're going to take
away the actual 10%. So make sure you add a another parenthesis and inside simply type
price, and n times point and then 10. And if I press enter, now you can see that and
in fact, we don't even need more Actually, let's add the two decimal places. So if I press enter, you can
see that now this is the value after the 10% off. And in fact, what we just did was the
price. So the price minus this discount right here. And basically this gives us 7898 point
84 for the very first color. And you can see the rest for all of those. So basically, this
is it right? So this is how you use arithmetic operators with your data. Obviously, you could
do much more complex calculations, but I just want to give you a gist of how it looks and
how you actually, you know, grab the column that you want and then perform some arithmetic
upon it. Alright, so to conclude this section, if you look carefully on what we did in the
previous video, ie we got the 10% value, and then this was the price After the 10% of the
original price. So if you look carefully on the actual table, column names, so you can
see that we have Id make model price, and then we have round, and then also round. So
this is not right. So by default, if you don't specify a column name, Postgres will use the
actual function name as the column name, or sometimes it will give you those question
marks, and then column question marks, as we've seen before, so let's go ahead and actually
use the alias keyword to provide a name for these columns. And in fact, you can use the
alias keyword for overriding any column. So let's go ahead and press Q, and pretty much
on the same SQL query. So remember, the first one was the actual 10%. So to override the
name, you simply have to say as, and then give it a name. So I'm gonna say as, and then
10, and then percent. And, actually, let's go ahead and instead of price, let's go ahead
and simply rename that to as and then original price. And for the discounted price, let's
go ahead and rename this too. So go ahead and say as. And if I make this smaller, we
can see properly, so as and then this count, after 10, and then percent, so a very long
column name, but you get the idea. So now if I press enter, and if I make this bigger.
So now you can see that we did override the original price column. So now it's called
original price. And then we have 10%. And then discount after 10%. And in fact, the
names are not consistent. But just let me correct that. And then if I press enter, and now you can
see that we have original price, 10% value, and then this count after 10%. And this is
how you override the original column name. Alright, in this chapter, let's go ahead and
learn how to handle nulls with Postgres, the first keyword I want to teach you is the coalesce
keyword. So basically, the coalesce keyword allows us to have a default value in case
the first one is not present. So go ahead and pretty much type select and then coalesce.
So cool, and then less. And inside of this function right here, simply type one. And
then if I press semicolon, you can see that we have the result, which is one, right. So
in fact, let me go ahead and has and then name, or actually number, right, and then
press Enter. And you can see that the number is one. And when the first parameter for this
function is no, it will simply give us the second value by default. So if I press enter,
you can see that we still get one. So we could also have multiple parameters. So basically,
if the first value is not present, try the second one. If that one is not present, try
the third one, so on and so forth. So if I press enter, you can see that we still get
one. But if I was to have, for example, one and then 10, you see that we still get one
because it finds the very first value, which is present in this entire array of values.
And this is the coalesce. Now, let's go ahead and pretty much just use this coalesce keyword
with our data set. So let's go ahead and select everyone from person. And right here you can
see that we have a bunch of emails, but also we have people without email. So right here
you can see that this person called Omar doesn't have an email. Nichols doesn't have an email
nolley Tynan, both don't have an email. So let's say that we want to select every single
email. And for those people that don't have an email, we simply want to have an email
with the value of email not provided. So to do that, what we're going to do is pretty
much just select just email so select an email from an in person Then enter, you can see
that we only have emails. But obviously we have people right here without emails, right.
So here, here and here. And if I scroll down, you see a lot more. So let's go ahead and
now use the coalesce syntax that we've just learned. So coalesce, so CO, and unless, and
then within parentheses, we will have an email, right. So if I press enter, you see that nothing
changes. But now, if I go ahead and pretty much just write the same command or the same
query, press comma, and then right here, I can specify the default value when the email
is no. So right here, I want to say email, not provided. And then press Enter. And now,
if you look at this, you can see that we have email not provided here, right here, right
here, right here. And if I scroll down, we should see a lot more. So right here, right
here. And that goes forever. And this is it. So coalesce is very powerful. So whenever
you have a column, which is no and you want to have a default value, use coalesce. In
this video, let's go ahead and see how we can tackle division by zero. So if you've done any kind of programming,
so Opie languages, such as Java or c++, if you try to perform a division by zero, that
will blow and throw an exception. So similar Lee, we'd Postgres so if I pretty much just
select, and then one, Washington, let's go ahead and use a bigger number. So 10, and
then divided by zero, right? So 10 divided by zero, it doesn't really make sense. And
it's like me saying to you, I've got 10 apples, and I want to divide it by zero people, right?
It doesn't make sense at all. And this should throw an error. So if I press enter, you can
see that we have this error right here, division by zero. So how do we tackle this? Well, we
have a special keyword. And that is know if and basically know if takes two arguments
and returns the first argument if the second argument is not equal to the first argument.
And let me go ahead and demonstrate this. So if I pretty much just type select, and
then no. And then if and inside of this now a function, it takes two arguments. So we
can pass the first argument as a number. And basically, if the second argument is the same
as the first argument, the result of this query will be no. Otherwise, the result will
be the first argument. So if I say 10, and then 10. So right here, you can see that the
result is now if I go ahead and say 10, and any other numbers, so 10, and one, the result
is 10. So if I go ahead and say 10. and maintain still 10. If I flip this around, so if I say
for example, 100, and then 19, the results would be 100. Because 19 is not equal to 100.
As you can see right here, and if I say 100, and also 100, you can see that the result
is no. So what we can achieve with this is if I select and then 10 divided by No, you
can see that Postgres doesn't throw an error. And that means that we can now safely perform
our division, so select 10. And then know, if, and then pass to any other number, you
see that we get the correct output. So 10 divided by two is actually five. So this means
that now, if I pass zero and zero, and this is what we actually care about, really is,
if there is a division by zero, we don't want to throw an exception or an error, we simply
want to return No. So I can go ahead and press Enter. And you can see that no error, and
we can use the coalesce as we you, as you seen before. So I can say coalesce, and then
select 10 divided by now if 00 and then I can have a default value. So let's go ahead
and press zero, and then enter. And I get a syntax error, and that's because I need
to perform a select before coalesce. And then enter, and my bad. So what I need to do is
really remove this select right here. So if I press enter Now you can see that the result.
So if I run that again, you can see that the result is zero instead of No. So this is how
you handle division by zero. All right, in this video, let's go ahead and
learn how to use dates with Postgres. So date is a useful concept that you must know how
to use with Postgres. Because often when you store results in your tables, you want to
record some kind of timestamp, ie, for example, when the record was originally created, or
when the record was updated, or, for example, dates. So date of birth, as we have in our
table called person. So there is one function that gives us the actual date, if you type
select, and then now so this is a function and press enter, you'll see that this gives
us the actual timestamp. So the timestamp is a combination of the date. So this is the
actual date. And then our minute and millisecond, were actually second plus millisecond, and
also the actual timezone. So this is the actual shift of the actual time. And basically, from
this timestamp, you can actually gather the date if you want. So to get the date, you
can cast this to a date. So simply press double column, and then date and and that would semicolon.
And now you can see they only have the actual date, you can also get the time. So instead
of casting to a date, you can cast to a time. So right here, you can see that the time is
this. So our minute, seconds, and then plus milliseconds. So this is it, really. So if
you know how to use these, then you should be on a very good shape. And if I go to the
docks, right here, you can see that they have some documentation on date and time types.
And I'm going to leave a link in the description below. So you can go through this documentation.
But basically, if you scroll, if I scroll down, you can see that you have date and time
types. And right here you have timestamp. And you can have a timestamp without timezone
or with timezone. So depending on where you live, you can specify the actual timezone,
then you also have date, and then time with timezone and without, and also you have interval,
I'm going to show you guys exactly how to use interval in a second. But if I go back
to P SQL, and describe our table called person. So right here, you can see that we already
have used the date, type, right. So for example, if you wanted to have a timestamp without
time zone, you simply type timestamp without time zone, or actually this one here, or we
timezone simply with time zone. So if I go ahead and pretty much just select and then
now. And you can see that this is my current timestamp right here, so for you will be different.
But now let's say that we want to subtract one year from now. So to do that, we can say
select, and then minus, so we can subtract. And then you simply have to say interval.
So this is a special keyword. And then within single quotes, you can say what you want to
subtract. So you can say one and a half year for end load semi column, you can see that
now we're going back to 2017 I can even go ahead and say take 10 years, so 10 year or
years, and you can see them going back to 2008. And this was about you know, you know
recession so recession time. So basically financial crisis time. Where so you can also
say let's say months, so you can take away months. Right? So, um, when 1010 months back,
so then back to February, you can also go ahead and say 10 days, right? So they are
days they both work. So I'm just taking away We can also add so I can go ahead and add
if I click this Queen, she can see properly. So now plus and then interval 10 days, you
can see right there. So if I go and say 10 and then months We should go to 2019 right here. And this
is useful sometimes when you want to perform addition and subtraction with dates. So I've
selected now, but you can also cast this to a date. So date right there. And basically,
you can simply get the actual date. So what you have to bear in mind is that the whole
thing, so this whole function right here, returns timestamp. So if you simply want the
actual date, and you can see that right here, I've got the hour minute. And second, you
simply have to wrap everything, and then cast it so date here. And if I go back, so I can
remove this casting, and then cast the entire statement, press Enter. Now you can see that
we simply get in the actual date. All right, so we've been working with date so far. And
I want to show you this function that allows us to extract specific values from a date.
So go ahead and say select and then now. And let's say that you simply want to extract
the actual year from this timestamp. So you can go ahead and say select and then extract.
And then within the parentheses, you can say what you want to extract. So I want to extract
the actual year, and then from and then your timestamp. So now I press semicolon, you can
see that now we are extracting the actual year, you can go ahead and extract the actual
month as well as the day. Also the day of the week, so this is down, you can see that
this is Sunday, and I think Sundays is zero if I'm correct. And you can also extract the
actual century, so century. And if I press enter, you can see that we are in the 21st
century. And you can also extract like milliseconds and other things. But basically, I just want
to show you how to extract the essential values from a given date. Alright, let's go ahead
and learn about the age function. So if I describe our table called person, you can
see that we have first name, last name, gender, email, date of birth, and country of birth.
So let's go ahead and perform a select. And then first name, last name, gender, country
of birth, and finally, date of birth. And then from person. I press Enter y actually
country or county, so it's not County, it's country, and then of birth. And if I press
enter, you can see that we have our table with a bunch of people, including first name,
last name, gender, country of birth, and date of birth. So now let's go ahead and have an
additional column with their actual age. So if I press Q, we can go ahead and run the
same command. And I'm going to press Ctrl l to clear the screen. And we can use this
function called age. So age, and then the age takes two arguments. So the very first
argument is the actual current timestamp so or I or a the starting date that you want
to calculate the age. And then the second one is the actual date or date of birth in
our case. So let's go ahead and pass date, and then of birth. And then if I say as an
age, and then press Enter. And you can see that now we have an extra column with the
actual age. And you can see that even includes the actual month, days, and also the actual
timestamp. And obviously, you could go ahead and extract whatever field from this age right
here, but I'll leave that up to you. Let's say that you have a table with two people.
And those two people have the exact same column values for first name, last name, gender,
date of birth, and email. So you can see in this table you have two women called Ann Smith,
with the same date of birth, and an email which is almost the same apart from the actual
domain, one with Gmail, and the other one we'd have gmail.com. Now, if you were to uniquely
identify for example, the First row, how would you do that? Well, in this table, it's impossible
because there is no column that can be uniquely used to identify someone. So if you are given
these two women, to distinguish between them, and this is where primary keys come into play.
In real world example, the way you identify a person, you could be, for example, by using
the passport number. And that's guaranteed to be unique for everyone. There are other
documents that you could use. But let's stick to passport number in this example. Now, the
passport number in this case can be used for our primary key primary key is a value in
our column, which uniquely identifies a record in any table. In our case, he identifies a
person. And in this course, what we are using as primary keys are numbers. So one, two,
so on and so forth. And the way that we are managing those is with a sequence, we could
use a different data type for our column ID, and I'm going to show you the best one, which
guarantees to be unique every time it's generated. But for now, big zero is fine. Alright, in this video, let's go ahead and
understand how to work with primary keys. The first thing that I want you to do is to
describe our person table. So if you remember correctly when we created this table, so just
let me show you the actual command that we used. So that was that was CREATE TABLE person,
and then Id big serum now. And then we can see that we used primary key. And this tells
this column is what uniquely identifies a person in this table. So if I go back to P
SQL, so right here, you can see that we have this person, underscore PK, and then this
is a primary key. So when we created this table, this is already given to us. So also,
you can see that we have this sequence right here. So this big serial type is managed by
us by the actual sequence. So remember, we never managed this, and this is auto incremented
by itself. So what I want to do is actually go ahead and select. So let's go ahead and
select and then start from person. And then let's also add a limit of one, press Enter.
And as you can see, we have this person with an ID of one, and our name is Alfredo. Now,
let's go ahead and insert a person into this table right here with the same ID as Alfredo.
So what I'm going to do is open VS code, and I've got this insert statement. And what I'm
going to do is add ID right here. And all four, I'm going to add one, now I'm gonna
grab this line, Command C or Ctrl. C, if you are on Windows, go back and paste that in.
And now you can see that the insert statement did not work. And this is because we have
already a person with an ID of one. And you can see that the error says duplicate key
value violates unique constraint. Person keys. So basically, this person key right here.
So this one is this one here. It's our primary key. And we can't have someone with the same
ID basically the same thing as if I was to change my passport numbers to be the same
as yours. It doesn't make sense, right? Because otherwise, given a passport number, you could
identify two people, which that will never be the case, because passport numbers are
unique per person. Now, let's go ahead and actually drop this constraint, right because
this is a constraint. And you can see that right here violates unique constraint. So
let's actually drop this and then insert the same person. So the way that you drop the
primary key constraint is by altering the table and then dropping the actual constraint.
So what you need to do is simply type ALTER TABLE, and then the table name his person.
And then what you want to do is to drop and then constrain and then the constraint is
this person key right here. So I'm going to copy that Paste that, and then enter with
semicolon. If I press enter, you can see that that worked. Now if I describe the table again,
if I just clear the screen first, and then person, you can see that now we don't have
a primary key. So if I go ahead now and try to insert that same person with the same ID
as afraid, and in fact, is the same person, but twice, press Enter. Now you can see that
the same command that didn't work when we had the primary key now works. Now if we go
ahead and select an ad star from person, and then where ID equals to one, press semicolon,
you can see that now we have two female with exact same ID. And in fact, with exact same
first name, last name, pretty much everything is the same right? Now, if we want to identify
these two people, basically, it's impossible for us to do so because they have the same
ID. Now, you do understand the importance of having an ID as the primary key. So basically,
IDs allows us to have a unique value that identifies a record in a given table. In the previous video, we dropped the primary
key constraint. And then we added two people with the exact same ID. So you can see right
here, if you perform a select where the ID is equal to one, then you should get two people
back so afraid right here, and also right here. So now let's go ahead and try to add
the primary key back and see what happens. So to add a primary key, we simply have to
alter the table. So ALTER TABLE, and the actual table is person. And remember, when we dropped
the constraint or the primary key constraint, we simply said, drop and then constraint,
and then the actual constraint name. Now to add a primary key, we can simply say add,
and then primary key. And now the primary key receives an array of values. And this
is because you can compose a primary key based on multiple columns. In our case, we only
need the ID to be the primary key. And that's absolutely sufficient. But there are times
where one column is not sufficient. In that case, you can pass multiple values inside
of this parenthesis. But for us, we want to add back our primary key, which was the ID.
So let's go ahead and pass ID. And before I press enter, I want you to have a guess
whether this command will work. So we want to add a unique constraint on the column ID.
So we want the ID to be unique for every single row. So if you have guessed correctly, then
the answer is no. And this is because we cannot add a primary key when the rolls are not unique
in our table. And this is true, right? So you can see right here that if I pretty much
just select so you can see right here, you can see that we have two people with the same
ID, right? So this doesn't work. Now, the way to fix this is to actually delete. So
we have to delete these two people, right. So the way that we delete a record from our
table, and I know that we haven't learned this, I'm going to cover this in a later chapter,
we have to simply say delete, and then from and then the actual table name, so person,
and then we have to use the where clause, because otherwise, we will delete every single
person in this table, which we don't want, and then ID equals to one. If I press on my
column, you can see that the Delete returned to row so you can see right here, and this
is because we had two people with the same ID. Now if I go ahead and try to select so
select where the ID is equal to one, you see that we have zero rows. Now we are absolutely
sure that the ID column is unique in our table called person. And in fact, let's go ahead
and add the actual person with ID of one. So let's go ahead and add. Now if I clear
the screen And then select star from person where d equals one, we should only have one
person. So now what we can do is add the actual primary key constraint. So let's go ahead
and say alter. And in table person, add primary key, and then the actual column name will
be ID. If I press enter, you can see that this time, it works because the IDS were uniquely
in this table. So now we can go ahead and pretty much describe the table, and then person,
Enter. And I can see that we have our primary key back. So remember, if you want to add
a primary key, you have to make sure that the column that you want to be the primary
key is unique in every single row. In this video, let's go ahead and learn about
the unique constraint. The unique constraint allows us to have unique values for a given
column. So what I want to do first is give you the reason why we have to use unique constraint.
And then I'm going to show you how to actually apply the constraint. So let's go ahead and
select and then pretty much just say email. And then let's count star from and then person.
Now let's go ahead and group by an email. And then if I press enter, you can see that
we do get the actual email plus the count. So this is actually grouping by the actual
email. And right here, you can see that we have 292 emails, which are no. So now I'm
actually interested to see whether we have duplicate emails someone say having and then
count, and then star bigger than one, right. So if I press enter, and as you can see, we
got 292 emails, which are known. So now what I want to do actually is if I open up VS code,
and let's grab this insert right here. And instead of operator, let's go ahead and change
the name to Fernanda and then grab this Command C, and then go back to P SQL, paste that.
And you can see that the E cert did work. So now let's go ahead and run the same command.
So we're going to group by the actual email having count bigger than one, press Enter.
And now you can see that we have a duplicate email. Right. And in fact, if I go ahead and
pretty much performance of that, so select, and then start from Bresson where, where in
an email equals to and then paste that in. So my column, you can see that right here
we have two females. So we have a freighter, and we also have Fernanda now let's say that
we want to send an email to our friends. So this offered right here. So we would have
a problem, right, because both Fernanda and afraid have the same email. So we don't know
exactly to which person the actual email belongs. And this is when the unique constraint comes
into play. so unique, which train allows us to have a unique value per column. And it's
not the same as a primary key. Because primary keys are used to identify a unique row in
a table and having an ini constraint, it simply means that you can only have unique values
per column. So this column right here, called email should only have unique values, ie,
we should never get into the scenario where we have two people with the same email. So
to add the actual constraint is very simple. So if I clear the screen, and let's go ahead
and try to add a constraint first. So to add a constraint, you simply have to alter the
actual table, so table and then person. And then we can say, add, and then constraint. And we have to give it a name. So let's go
ahead and say unique, and an email address, and then simply say, unique, so this is the
actual keyword. Now, inside of parentheses, you could actually pass multiple columns to
be unique. And this allows you to have a set of values which are unique per table. But
in our case, we simply want the email to be unique. So if we go Go ahead and try to add
the email, so email, and then semicolon. If I press enter, you can see that we get an
error. And it says that could not create a unique index unique email address. So this
is the actual name that we have given it. And the reason why it can create is because
it found that this email right here is duplicated. And in fact, if I remember correctly, if I
go ahead and select everyone with that email, you can see that we have two people with the
same email. And to fix this, we could actually do two things. One, we can pretty much just
get rid of this person right here. So Fernando right here, or we could actually change the
actual email. So this email right here to something else, or even make it nullable.
But I'm going to show you exactly how to perform the lead updates probably in the next chapter.
But for now, let's go ahead and simply delete this person right here called Fernanda. So
to delete, simply type, delete, and then from person, and then simply say where ID equals
to. And then Fernandez unique identifier is this one right here, which is 1004. So 1004,
and then semicolon, and that was deleted. Now if I go ahead and try and select everyone
with that same email, we should only get afraid. Now let's go ahead and press up a couple of
times. So we want to add the actual index right here. So now we can go ahead and add
the actual index. So ALTER TABLE person, add constraint, and then the actual name, and
then unique right here. So this is the keyword and then we're simply saying that we want
the email to be unique. If I press enter, you can see that that not works. And now if
I go ahead and clear the screen first, and then press backslash, D, and then person.
You can see that we have this unique constraint right here that we've just created. And the
name is Unique email address. Now, let's go ahead and try to add the same person that
we did so Fernanda, with that same email. So let's go ahead and press up a couple of
times, and see if we can find Fernanda so I think this was Fernanda. Yeah, so this is
Fernanda, if I press enter, you can see that the actual insert now fails, which means that
our table is behaving well, according to the given cross training that we've just given.
And finally, let me go ahead and drop this constraint right here that we've just created.
And I want to show you a different way of creating this. So let's go ahead and say alter,
and then table and then the actual table name. So this is person and then drop, and then
constraint, and then the actual name, so unique email address, semi column, and you can see
that that's gone. If I press backslash, d ln person, you can see that the actual constraint
is gone. Now the other way of adding a constraint simply by saying alter, and then table, and
then person, and then add, and you can simply say unique, and then pass the actual column
name, so email. Now the difference between this way and the previous way, is that now
we let the constraint name be defined by Postgres. Press Enter, you can see that works. Now if
I press backslash, D, and then person, enter, you can see that we have this constraint called
person, email and then key, and then it's a unique restraint on email. If you have any
questions on using unique restraints, drop me a message. But as I said, unique constraints
allows us to have a unique value per column is not the same as the primary key because
primary keys job is to identify a record in a table. In this video, let's go ahead and learn about
the trackless train. The checker screen allows us to add a constraint based on a Boolean
condition. So the easiest way for me to explain this is if we go ahead and select everyone
from this table right here called person. And you can see that we have a bunch of people,
and we have this column right here called gender. And currently we have male and female.
So we could technically allow other genders here. We could technically have different
genders here, but let's Say that we want to keep only females and males in this table
right here. Right? So if I open up VS code, and let's actually grab Fernanda right here.
So we have Fernanda, and for the actual gender column. So let's actually change that to Hello,
right. So if I grab this, so if I press Command C, and then go back to P SQL, and press Q,
and then paste that, and it fails, because we have a unique constraint on email. So we've
just added this in the previous video, which is fine. So let me go back to VS code and
actually change the email. So get low. And then let's grab the same row, go back to P
SQL, paste that. And now that works. So now let's go ahead and select and then say unique,
and then gender. From and then person, I press enter, and is not unique is actually distinct.
So select and then distinct, and then gender from person, Enter. And you can see that in
this table, right here, we have three types of gender. So we have male, and then Alo and
then female. So this really doesn't make sense. So what the actual check constraint allows
us to do is to make sure that we can only add a string which matches either male or
female. And to add the actual constraint is very simple. So we have to alter and then
table, person and constraint. And then we have to give it a name, so gender, and then
constraint. And then the actual keyword that we have to use is this one, check. So now
inside of this check constraint, we pass a actual condition. And the condition that we
want is that the gender column equals to female or gender equals any should be just one equal
not double equals, sorry, so equals two, and then right here, male. So if I press semicolon,
and then try to run this constraint, you see that this fails, and it fails because we have
one row, which is violating this constraint, and is this one right here. So there is a
person with a gender of type ello. So let's go ahead and delete this person. So we'll
simply say delete from an in person, where and then gender equals to ello. can see that
that was deleted. Now, if I press up two times, and then try to add the same constraint, you
can see that now this time it works. So just let me go ahead and clear the screen, and
then press backslash, and then D, person. And now you can see that we have this check
constraints. And you can see our gender constraint, so check that the gender is equal to female,
or the gender is equal to male. Now let's go ahead and try and add the same person with
the gender alone. So if I go back, and then grab this line right here, so you can see
that ellos agender. And paste that in, you can see that that fails. So you have an error,
new row for relation person violates check constraint. Now, let's go ahead and change
this to something else. So low, and then grab that. Go back, paste that
in. And you see that that doesn't work. So now our table is enforcing the right constraint,
which is to only have either male or female in the gender column. So check constraints
are really powerful. And basically, you can pretty much just have any condition that you
want, right instead of the check function right here. So for example, for a product,
you could say that a product should have a value amount bigger than zero. So that could
be one question. And there are many many questions depending on your data set. In this video,
let's go ahead and learn how to delete from our table. In the previous videos, you've
seen the importance of primary keys. primary keys allows us to unique Please identify a
record in a table. And when you want to delete a record from a table, you should always or
in most cases, use the primary key in the where clause. So you could delete by the actual
primary key, we could also delete by gender, or by email, or by country of birth pretty
much by anything. But you just have to be careful because for example, if I was to delete
where the gender is male, then we would only be left with female in this table. So let's
go ahead first and actually delete Omar. So Omar is my D have to. So you've seen how to
delete before but I didn't actually explain exactly how it works. So to delete from a
table, you simply type, delete, and then from and then you pass the actual table. So person.
So if I was about to actually execute this script, so let's go ahead and run it so that
you see what is going to happen. If I press enter, you can see that with the lead ID,
every single one from our table called person, if I go ahead and select star from an in person,
you see that no one is there. So let me go ahead and open up a new shell and navigate
to a folder where I have stored the SQL file, so change directory, so CD to downloads. And
then if I perform an LA, you can see that we have this person to SQL. So I need to know
the actual directory. So pwd, grab that, and then go back to P SQL. So you've seen how
to do this before, so backslash I, so for executing from a file, and then paste the
actual directory, and then person dot SQL, I press enter, you can see that we have the
data back. And now if I select everyone, so select star from an in person, you see that
we have everyone back so you can see oh my right here. And one thing that you should
notice is that the actual ID now has changed. So it's no longer one, because we did not
reset the sequence, which is managing this ID, I'm going to show you how to do that later
on. So now let's go ahead and delete Omar. So if I type DELETE FROM and then person.
Now I'm not going to run this because you saw that it deletes everyone from this table,
we can go ahead and simply say where so this is the actual filtering. So we can say where
and then ID equals two. And in that, so 1011. Enter. And you can see that now we've deleted
one record. If I select everyone, you can see that Omar is no longer from this table.
So you could actually go ahead and extend the and condition. So for example, you could
say, let's delete everyone from person where the gender so gender equals two, and then
female. And you could say and, and then country of birth equals to England for example, right.
So if I press enter, so it should be country and not County, and then press enter. So we
haven't gotten no one from England as odd. But let's go ahead and simply change this
to Nigeria. And as you can see that we deleted three females from Nigeria. And if I was to
go ahead and select star from person where gender actually this entire WHERE clause
does me copy that instead of typing. So Command C base that in semi column, you can see that
we haven't got no one. But if I change the gender to male, press enter, you can see that
we have few guys from Nigeria. Now, let's go ahead and delete all the female from this
table. So the lead from person where and then gender equals Why should the list let's delete
the guy's male so if I press semicolon, you can see that we deleted more than half of
our data so 516 guys, we're gone. So if I select everyone, so select star from an in
person, you should see that we only have female from the stable. So, as you see, like delete
is very straightforward. So you can use the where clause to filter to a specific row,
or multiple roles with the Add condition. So bear in mind that using the Delete on its
own as we did, so just saying the lead from person is very, very risky. Again, you never
want to do this in a production database, because otherwise you just wipe out the entire
table. And then you can get into trouble. So it's always best to use with the actual
WHERE clause, and then delete one person or one record from your table, or few records
from a table, depending on your WHERE clause. This is all for now, if you have any questions
on using the Delete keyword, drop me a message. But in the meantime, what I'm gonna do, so
let's go ahead and just simply cancel that. So I'm going to delete everyone, because just
let me cancel that. So delete from an in person, because I want to add everyone back into this
table. So we deleted the remaining females. And now I simply have to find the command
where I execute from the file. So this one right here, Enter. And now if I do a select
star from person, you can see that we have everyone back into this table. Alright, in this video, let's go ahead and
learn about the update command. The update command allows us to update a column or multiple
columns based on our WHERE clause. And also you could update every single row if you don't
provide a where clause. But usually providing the where clause is more sensible, because
you have control of what you actually updating. So let's say that we want to update this person
right here. So Omar, so we want to update his email from now to an actual email. So
the way that we do that is as follows. So we have to use the update command. And then
we have to specify the actual table. And now we have to say set. So this set allows us
to pass an array of columns, including the new values. So right here, we could say email
equals two. And in this would be the actual new value. So I could say, Omar, and in gmail.com,
if I was about to press semicolon, and then execute this command, this would behave the
same way that I've showed you with the Delete command. So this would actually update every
single row in our column with this email, which we technically don't want. So it's always
best to use the where clause someone say where. And right here, you can pass your condition.
And my condition is where the ID so the row identifier equals to 2011. If I press enter
it see that we have one row, which was updated. Now if I do a select and install from an in
person, where and an ID equals 2011. So my column and you can see that now we did update
the actual email. So let me update one more time, so you can see what we're doing. So
let's say that this time is Hotmail, Hotmail, Enter. And you can see that this now was updated
from Gmail to Hotmail. So we could also update multiple columns. Now let's go ahead and simply
update his first name and last name. So we could go ahead and say up date, and then person
set, and then this is the actual array of columns. So now you could say first name equals
two and then they simply say, oh mark with one M. And then if you want to update a second
or third, or more columns, you simply add a comma, followed by the next column. So right
here, let's go ahead and say last, and it name equals true. And unless simply say, Montana,
so Omar Montana, and you could also update the email so let's go ahead and add a comma
and then email equals to and then Omar dot Montana, at and then hotmail.com And remember,
so if we don't provide a where clause, we will update every single row with these updates,
which in theory wouldn't work with email because we already have a unique constraint. But this
is so that you know, so now I want to say where and then ID equals to 2011. If I press
enter, you can see that worked. Now I'm going to select Omar again. And you can see that
now, we have updated his first name, last name, as well as the actual email. And this
is how you use the update command with Postgres. Just bear in mind that whenever you perform
an update, delete, you always want to have a where clause because otherwise, you might
update or modify your entire table. Alright, in this video, let's go ahead and learn how
to deal with duplicate key errors or exceptions. So let's go ahead and select Omar again. So
select Start from, and then person. So actually, let's, let's go ahead and pick someone else.
So let's go ahead and pick this person right here. So Russ, so 2017, that's, that's the
actual ID so and then where ID equals two 2017. Right. So remember, the
ID column. So this ID column right here is the value that uniquely identifies Russ in
this table. So this guy right here called Russ, so meaning that if you were to insert
someone with the exact same ID, your query should never work and throw an exception or
an error, saying that the key is already in use. So let's go ahead and try. Let's go ahead
and simply say insert and then into, and then person, right here, let's go ahead and add
the actual ID, first name, last name, gender, email, date of birth, and finally, country
of an end birth. And then don't press semicolon and press enter. So we're going to continue
on the new line. And now we're going to say values. And then within parenthesis, let's
go ahead and try to add someone with the exact same ID as Russia. So 2017. And let's go ahead
and pretty much just try and give it the same name. So Russ, and then they should be in
quotes. And then last name, I'm not even sure if this is real name, so and then mail, and
then the actual email, just grab it, and then the actual date of birth, so remember, date,
and then first comes the actual year, or 1952, September, and then the 25th. And the country
is Norway. Now if I press semicolon, and I want you to have a guess whether this will
work. So remember, I said that the actual ID is a unique identifier for this column.
So we're trying to add a second person with the exact same information as Russ, including
the actual ID. And in fact, you can see that the error says duplicate value violates unique
constraint. And the constraint is the person primary key. And you can see in the actual
detail, it says key ID 2017 already exists. So there are times where you don't want to
blow with errors or exceptions, right. So basically, you want to handle the case where
you have conflicts. And this is when you use the on conflict keyword. So let's go ahead
and pretty much just press up one time. And instead of running the same command again,
remove the semicolon, and then press Enter. And now we can say on conflict. So this is
the actual keyword that allows us to handle on conflict scenarios. So on conflict, and
then we have to pass the actual column that might be in conflict, and in our case will
be the ID. And then we can say pretty much do and then nothing. So if I press semicolon,
now if I run this, you can see that we have no errors. And right here, you see that no
inserts were performed. So 00 and this is how you handle duplicate key errors. Now,
we created for the actual ID so Id right here, but we could also have an conflict for the
actual email because Because our email has a unique constraint, so if I press backslash,
D, and then the person, you can see that right here we have a person email key, and then
the unique or shrink, right? So if I go ahead and clear the screen, and then instead of
actually saying on conflict ID, I can pretty much say email, right? Do nothing. Enter Nikki,
see that also works. But this will not work if you don't have a unique column, right.
So if I was to pretty much just pass first name here, so first, and name, enter, you
can see that there is no unique or exclusion constraint matching on the on conflict specification.
So whenever you want to use the on conflict, make sure that your column is you link, ie
have a constraint, either a primary key or a unique or shrink. And you can also have
a non conflict on multiple columns, if you wish. In the last video, you saw how to use the
on conflict do nothing. And he saw that if you have a conflict on a unique column, that
means that your query has no effect. But sometimes you pretty much want to do something else
other than to nothing. And a good example is, let's say that, for example, you have
a user registering on your website. And then he performs a request to register to your
server, right. So he performs a request to add his details to your service. Now, it could
be the case that the user submits his information, but then immediately changes his mind and
then updates his email with the exact same detail. Right. So the first request is send
one email, and then in the next request is sent a different email, right. So in this
case, basically, sometimes, you might not use the do nothing keyword. But instead, you
want to take the latest insert that comes from your client. So This usually happens
when you work in a distributed system, where you have two servers sitting above a load
balancer. So just let me show you how this works. So if I press up a couple of times,
so I pretty much just want to select this person right here. So, Russ, and if I go ahead
and perform this request, you see that there is no insert, so 00. Now if I go ahead and
select it again, you see that no information change. So let's say that this was the actual
first request that they added. So they wanted to register with this information. And then
about two seconds later, he decided to add a.gov.uk at the end of his email, right, so
he sent the exact same information but.uk at the end of his previous email. So this
is where you use the on conflict, so on conflict, and instead of saying do nothing, you can
say do and then update. And the do update works pretty much the way that you saw how
to use the update command. So we have to say set. But now this is where the magic happens.
So you're going to say email, so you want to take the email, so the current email, which
is stored in the database, and then you want to say that this is equal to excluded, so
this is a special keyword, then dot and then email. So basically, this email right here,
so this email relates to this one right here, right, this one right here. And then the excluded
dot email refers to this one right here, the one which is about to be inserted. So if I
press semicolon, and then press enter, you see that this time, we did affect one row.
So if I select Russ, again, nothing changes because the email was the exact same thing.
Now, let me just clear the screen, select him again. And let's run the exact same command.
But this time, let's change the email. So I'm going to add a.uk at the end. There we
go. And then press enter. You see that works. Now if I select, you see that now, we had
a conflict but we simply change his email. So this excluded dot email was the one about
to be updated. And pretty much I've simply used email, but you could also add a column.
And then let's say that you want to update pretty much every single value. So if I go
back, and then right here, you could say set. And then if I press enter, so we could say
email. And you could also say, let's say last, and then name equals to, and then excluded.
dot and then last name. And you can pretty much do for the rest, so the order doesn't
really matter. So first name, equals to excluded dot and then first name. And you get the idea.
So if I press semicolon, run that you see that works. Now, if I was about to change
the name to Russell, and then the actual surname to Rudy, press
enter, you see that works. And if I select Russ, so now right, you can see that only
the actual first name and last name were changed. But if you look at the email, the email kept
the same. And this is how you use the on conflict do update. So this allows you to perform an
update or insert, hence the name absurd, and pretty much allows you to override existing
data, If present, otherwise, insert a new row. Alright, in this video, let's go ahead
and learn about foreign key joins and relationships. So far, we have two tables, person and car.
And what we want to be able to do is to have a query that returns a combination of both
person and car details for a single person. ie we want to have a select query, where we
select the person as well as the car. Now, the naive approach for this would be to have
a table called person. And then we could stick every single information inside of a person
table. So right here, you can see that you have the person details as well as the car
information. And for example, if you wanted to store the actual address for that person,
you would stick more data into this table. Now, this is bad, because because we are learning
about Postgres, and Postgres is a relational database, ie you could have multiple tables
and then connect them together based on a foreign key. So and also right here in this
table, you can see that we have a bunch of not nulls, which means that if you were to
insert a new record into this table, you'd also have to insert the actual car information.
And remember, not everyone has a car. So this is a very bad approach. So essentially, what
we want to represent is that a person has a car, the person can only have one car. And
finally a car can belong to only one person. So to achieve that, what we can do is actually
have a relationship. And a relationship looks like this. So right here in this table called
person, you can see that we have a new column called car underscore ID. And this is the
actual foreign key. So this is what a foreign key is. So a foreign key is a column that
references a primary key in another table. So you can see that this foreign key links
to the actual primary key inside of our car table. And in order for this to work, the
types have to be the same. So you can see that this is a big int. So the foreign key
is a big int, as well as the actual primary key inside of the actual car table. And the
syntax goes like this. So car underscore ID, that type. And then you say that your references
or references and then the actual table car. And then you have to specify the actual column
that he references. So right here, you see that we pass the ID, and the ID is this column
right here inside of car. And then I'm also saying that the actual foreign key is unique
in this makes sure that a car can belong to only one person. And also we are saying that
a person may or may not have a car, and finally that a person can only have one car. All right, in this video, let's go ahead and
add a relationship between our two tables. The person called car as well as person so
the idea is That one person can only have one car. And one car can only belong to one
person. So if I describe our tables in our database called test, you can see that we
have two tables, car, and person. So what we're going to do in this video is drop these
two tables, because I want to remove the entire data in it, as well as create the actual tables
from scratch. Now, let's go ahead and drop and then table. So you've seen this before,
person. And let's also drop the cart table. So just like that. And now if I press backslash,
D, and then T, you see that no relations found. Now go ahead and download this file right
here, which you can find in the resources link. And it's called person dash, and then
car dot SQL. So this will be our file that we're going to edit and add the relationship
between person and car. So right now is just what you've seen before, right? So this was
the person table, and this is the actual car table. And what we want to get out from this
is that a person can have one car, and a car can only belong to one person. So to do that,
we need to add a new column. So this column will serve as the referencing column to the
car table, ie the foreign key. So to do that, let's go ahead and simply say car and then
underscore ID. Now I need to specify the actual data type. So I can simply just gonna go ahead
and say big cereal, because big cereal is a special data type, which is managed by a
sequence. And instead of wanting to use is big, and then it, so they are pretty much
the same in terms of the actual size. But the difference is that big cereal is a special
type, which is managed by a sequence. Now we could also go ahead and say not and then
oh, but the reason why we're not doing this in this column, is because a person may not
have a car, right? Not every single person has a car until they become 18, or 16, in
some cases, so I'm going to remove that. And now to add the foreign key or the relationship
between person and car, I need to add references. And then I need to specify the actual table.
And now I need to specify to which column this car ID will reference. So in our case,
so simply within parenthesis, simply say ID. So this ID right here is this one right here.
And this ID is our foreign key. Now remember, I also said that a car can only be owned by
one person, which means that we can add a unique constraint. And the way that you add
unique constraints within your table creation is simply by saying unique and then pass the
actual column. So car underscore ID. And this is all so now go ahead and save this. And
now what we need to do is to execute these two table creations plus the inserts. Now,
if I was about to pretty much just insert this table first, this would fail. And that's
because this cart table doesn't exist. So just let me show you. If I grab that, and
then go back to item, paste that, you can see that right here relation cart does not
exist. So I'm going to clear the screen and then go back to VS code. And what I'm going
to do first is create this table called car. And I'm just going to put it first right here
and make sure you have this exact same setup. So just like that, and then save this, you
could go ahead and pretty much just copy and paste all of that. But what I'm going to do
is execute from a file, so backslash I, and then the destination is users, Ford slash
amigos code and at Ford slash downloads, Ford slash and then person dash and then car dot
SQL number, press Enter. And you can see that no errors, and we have three people and two
cars. So let's go ahead and check so select and install from car can see that we have
two cars, and let's go ahead and select star from an in person. You can see that we have
three people. And right here you see that we have this new column called caller ID Which
we haven't assigned to anybody. Alright, in this video, let's go ahead and
assign two cars to two people. So right here, you can see that the car ID column in person
is completely empty for every single one. So what we're going to do is simply update
this value with these two cars. And you will see that the constraint that we added, so
this one right here, backslash, D and that person. So this one right here, so unique,
and then caller ID is actually working. And in fact, we forgot to add the uniqueness constraint
on the actual email. But you've saw that in the previous video. So let me go ahead and
delete that. And select from person first and then from car. Now let's go ahead and
assign a car to Fernanda, so let's go ahead and say update, and then person, and then
set and then car ID equals two. And let's pick this one right here. So Land Rover equals
to one where I lead ID equals to one. So actually, just let me change this or is not that confusing.
So basically, what I'm saying is, I'm going to set the car ID. So let's actually change
this. So too, and then one. So one is Fernanda, so we're going to change Fernandez car ID
column. So Fernanda ID is one. So this is one right here. And we're going to assign
there, this car right here called GMC. So the car ID is actually two. So this two right
here corresponds to this one. So if I go ahead and press enter, you can see that works. Now
if I select everyone from car, Oh, actually person. You see that? Fernanda Ray here has
a car. So you can see right here. So let's also add a car to Omar. So update person set
car ID. And for now let's actually try and add the same car. So you see that our constraint
is working. So where the ID of Omar is to someone to press Enter. And you can see that
so if I clear the screen and run the same command, again, you can see that our unique
constraint is working. So car ID is already taken. So let's go ahead and select cell from
person and from car so you can see properly. So now what we're going to do is give it this
car right here. So the Land Rover, I'm gonna press Enter. And now if I select everyone
from person, so let's start from the person. You can see that we have Oh my right here,
weed, car ID one. And in fact, let me select all cars, and then car. From and then car.
You can see that Omar has the car ID of one. So you can see that it's a Land Rover. And
then Fernanda right here has car ID two, so which is this one right here, GMC. And this
is how you set a value that corresponds to a key in another table. And if I was about
to actually, so update a person, so let's let's try now. Adriana. So adrena has an ID
of one. But now let's try and add an ID that doesn't exist. So for right or even three,
right? Because there is no ID three in this table. So if I was about to run this, and
in fact, let me run it, you see that insert or update on table person violates foreign
key constraint. And it says that car ID three is not present in table car, which is true.
And that's the power of foreign keys, it means that you can only assign a foreign key when
there is a relation in the other table. Alright, now that we have a foreign key constraint
between our two tables, person and car, let's go ahead and learn about Inner Joins. So Inner
Joins is an effective way of actually combining two tables. And the way it works is that you
have a table a as well as a table B. And what you want to do is actually combine these two
tables. Now the To join takes whatever is common in both tables. So if you have a record
inside of the table a, and also a record inside of the table B. So if you have a foreign key,
which is present in both tables, then it takes those two records, and then gives you the
result of both which we're going to call it C. So a plus b equals to C. And to recap,
an image join takes two tables A and B. And then if we have a foreign key that is present
in both tables, then we have a new record called C. Let's go ahead and learn how to
use Inner Joins with Postgres. Alright, now that you know what a join is, let's go ahead
in this video perform a join between our two tables, car and person. So I'm going to select
star from person, and also select and then star from an in car. So we want to perform
a join based on this foreign key right here. So car ID, links to this ID inside of this
card table. So to perform a join, we simply have to say select, and then star. So we want
to select every single column. And then from and then here is where you specify the first
table. So person, and then don't press semi column, and then on a new line, simply say
join. So this is how you join to another table. Now you specify the actual table that you
want to join in our case is car. And then you need to say on so on, takes a column which
can be used to join these two tables. So in our example, is the foreign key. So car ID
found in person will join to ID found in car. So let's go ahead and say person, dot and
then car underscore ID equals two and then car dot and then ID. If you go ahead and press
semicolon, press enter, and see that we've got two results. And in fact, because you
can't see properly, I'm going to show you a nice trick. So if you press Q and then press
backslash and then x and then enter, you can see that we have expanded display on now if
I perform the same select, you can see that now we have this information that can be easily
read. And there we go. You can see that we performed a join between two tables. And pretty
much this right here is what actually sorry, this entire selection is everything from person.
And then the rest is from car. And you can see the same for Fernanda so Fernanda, right
here, she has the car ID of two. And you can see that this is the actual car. And this
is how you perform joints. So obviously, so if I scroll up, so obviously, Adriana is not
included because she doesn't have a car. So remember, a joint simply links to tables,
where the primary key and the foreign key is found in both tables. So just let me go
ahead and show you one more thing. So you saw that we selected everything. So select
star from person. So what we can do, just let me remove that so so you can see that
we get every single column. So now what we can do is just grab certain columns from each
table. So to do that, let's go ahead and say select. And now I can go ahead and say person
don't and then first name. And then comma, let's go ahead and select the car, dot and
then make comma, car dot and then model and then car dot and then price. And then we can
go ahead on a new line. So if I clear the screen on a new line from and then person and then we're going to join
so let's join and then car on and then person dot car underscore ID equals true car.id semicolon.
If I press enter, you can see that now we selected only the columns that we wanted.
And let me go ahead and remove this expanded display on and to try They have backslash
x is a toggle. Now if I perform the same selection, you can see that if I remove that, you can
see that now we simply selected few columns from each table. And this is how you perform
a join. Let's go ahead and learn about left joins. Left joins allows us to combine two
tables like inner joints. So Table A and table B. And the difference here is that a left
join includes all the rows from the left table, ie Table A, as well as the records from table
B that have a corresponding relationship. And also the ones that don't have a corresponding
relationship. ie returns all the records, even if there isn't a match, and then you
get result. See, let me go ahead and show you exactly how this works. So if I go ahead
and pretty much Select star from person and then join, so let's go ahead and first join
car on person not car ID equals to car door ID, if I press enter, and in fact, let me
go ahead and make this smaller, so you can see everything in one line. So just like that.
And then if I run the same thing, so just let me clear the screen, and then run that.
So now you can see that we only have two people, right two people. And that's because if I
select Start from person, you see that Adriana, she doesn't have a car. So a join simply takes
this condition right here and finds every single row where the ID is equal to the actual
foreign key, and anything else is discarded. But now let's say that we also want to include
people that don't have a car, ie, we want to have this exact same query, including Adriana.
And this is where left join comes into play. So if I go ahead and select and then star
from person, and then I'm going to say left, and then join. And basically now everything
is the same. So we want to left join to car, and then on and then car.id equals to person,
dot and then car underscore ID. If I press semicolon, and now press enter, and this includes
everyone that has a car. So you can see Omar and Fernanda they both have a car. And you
can see that Id make and model and also price are filled with values for both Omar and Fernanda,
so you can see right here, so value one, and then value two. But because we performed a
left to join, meaning that we also wanted people without a car, you can see that these
values for Adriana are no. And this is what a left join is basically means that you want
to join both tables, including Records, which don't have a foreign key relationship. So
now, with this, we can actually find out people that don't have a car, right? We could technically
write something like this. So select and then start from and then person. And then you could
say where ID is no, well actually not ID sorry, car ID sorry. And you can see that we have a DRI honor,
but also with a but also with a left join. So if I clear the screen, you could also do
the same as this. So you could say select star from person, left join. And then you
can say where and then car dot and then star. So star means every single column and then
is no. So if I go ahead and press enter, you can see that now, we include the actual joint
of both tables, but only persons that don't have a car. In our case, only aduana doesn't
have a car. So let me go ahead and select the initial joints. So just like that so you
can see what we're doing. And there we go. So This is giving us everyone, including those
who don't have a relationship constraint. And also, let's go ahead and perform the actual
join. So you can see the difference. So enter. So you can see the join only gives us the
ones, which have a foreign key constraint in both tables. The left join gives us those
who have a foreign key, and also those who don't. And basically, this very first query
is simply finding out those who don't have a foreign key constraint. So this is it for
left joints. It's very powerful. In this example, we simply have two tables. But you could expect
to perform joins on, you know, multiple tables. And this is how you perform left join. Alright, in this video, let's go ahead and
learn how to delete when you have a foreign key constraint. So let me go ahead and first
add this car right here. So Mazda with it 13. So I'm going to copy that, and then go
back to my time, paste ad, you can see that we have a car. And let's also go ahead and
add a person. So let me go back to VS code, and then open that person with SQL. And now
let me go ahead and pretty much just grab this line right here. And what I want to do
is actually format this a little bit. So instead of Omar, let's simply say, john, and then
Smith. And then Mel, and then the ID, let's actually give an ID. So Id and then this will
be for example, 9000, right, and email, so no email. So I'm gonna grab that, and then
go back to iterm. Paste that, and you can see that we inserted john. So if I go ahead
and select star from and then person where ID equals to 9000, you can see that we have
john and select Start from car where and then ID equals two. And the ID was 13. So 13. And
it can see that we have both a a person and a car. And you can see that john doesn't have
a car. So right here, it's no. So let's go ahead and assign this car right here to john.
So let's go ahead and simply say, update, and then person, and then set car underscore
ID equals two, and this will be 13. So 13 is this one right here is this ID for this
car. And I won't say where. And then ID equals to 9000. So this is John's ID, enter, you
can see that worked. So if I select john again. And in fact, just let me clear the screen.
So select john, and also select the actual car. So now you can see that john has a car.
Now, if I go ahead and delete this car right here, this will not work. And in the meantime,
try and guess what's going to happen. So delete, and then from car. And then where ID equals
213. If I press enter, and should be where I'll actually car, I should have car here
and then where. So if I execute this command, you can see that this didn't work. And the
reason why it doesn't work is because we have still one person called john, which has a
foreign key to this car right here with an ID of 13. And the error is up there or the
late table car violates foreign key constraint on table person. And you can see the detail
right here says that Id 13 is still referenced from table person. So this is what I've just
said. So basically, we tried to delete this car right here, but it is still being referenced
by this person called john. So remember, whenever you try to delete individual Records, make
sure that if there is a foreign key constraint, you need to pretty much remove the foreign
key constraint before you perform the actual deletion. ie if I want to delete this car
right here, I first need to remove the car ID from john. And then I can go ahead and
safely delete this car right here. So we have two options. One is
to actually delete john, because there is no foreign key constraint between john and
some other table. So we can delete, or we can update the car ID to now and then delete
the actual car. So let's go ahead and pretty much delete john. So we're going to delete
john. So go ahead and delete from person. Again, if you want, you can update this value
to No. And that would still remove the foreign key constraint. So the lead from person where
and then ID equals to 9000. Enter, you can see that that worked. So if I select and then
start from person, where ID equals to 9000, you can see that we have zeros back. Now I
can go ahead and delete the actual car. So DELETE FROM car, where ID equals 213. Enter,
you can see that the car was deleted, select from car where ID equals 13. And you can see
that the car was deleted. So a very important topic that you should be aware of. And basically,
you could have a cascade on your table creation. And cascade simply ignores the actual foreign
key and goes ahead and removes every single row where that key is referenced. And the
reason why I'm not teaching you cascade is because it's bad practice, you always want
to have full control of your data and know exactly what to delete. Because deleting data
without knowing what you're doing can be very costly. Alright, in this video, let's go ahead
and learn how to generate a CSV with Postgres. So what we want to do is actually select our
data, so perform a selection, and then export that to a CSV file. So let's go ahead and
pretty much select star. And then from person, let's go ahead and perform a join or actually
left join, because we want to include everyone with and without the foreign key constraint.
And then I'm on a left join car on and then car.id equals to person dot car underscore
ID. Press Enter, you can see that we have three rows. Now, to export this to a CSV.
First, I'm going to show you the actual help so backslash and then question mark. And right
here in this section input output, you can see that we have this backslash copy command,
which simply performs a sequel copy with data stream to decline host. And to use it, let's
simply say backslash, and then copy. And then within parentheses, we have to specify what
we want to copy. And we want to copy these three rows right here. So for that, we need
to perform the same query. So select and then start from person left and then join
car on car.id equals to person underscore, y actually person dot, and then car underscore
Id just like that. And now I want to copy the entire query, too. So simply type two,
and then the destination of where you want to save the output in your file system. So
in my case will be Ford slash and then uses Ford slash amigos code, Ford slash and then
desktop. And then we can use a delimiter. And then within quotes, this will be comma.
And now we want this to be as CSV. And we also want to include the actual headers. And
there we go. So if I press semi column, and then enter, and uses amigos code, desktop,
a is a directory. And that's true. So basically, we simply have to give a file name. So let's
go ahead and say results dot and then CSV, Enter. And you can see that we copied three
rows. If I open up my desktop, you can see that we have this file right here called results
dot CSV. And if I press space, you can see that we have our CSV file, including the headers,
as well as the results from our query. So three rows, including Omar, Fernanda, and
Adriana. And this is how you generate CSV files with Postgres. In this video, let's
go ahead and learn about the big serial data type. So, if you remember correctly, when
we created both person and car tables, right here, the ad is actually a big zero, so big
zero. And if I remember correctly, I mentioned that big zero is a special data type, which
auto increments a number, right, and that number is an integer. So if I open up my terminal,
and if I describe both person and car, you can see that the type is actually big int.
So there's no such type as a big zero. But the special thing about it is that right here,
so on this default column, you can see that he has this next vowel. And the next value
is managed by this person ID sequence. The same for car. So you can see right here, so
the type is big int. And the default value is this one right here, which is also managed
by a sequence. So what I want you to do is to go ahead and select so let's go ahead and
select and then start from and then we can actually select from both sequences. So let's
go ahead and say person, and then underscore ID, and then sec four sequence semi column.
If I clear the screen, now you can see that the last value is three. And then the last
count is pretty much how many times it has been invoked. And then right here, you can
see whether it's been called or not. So if I go ahead and select star from person, you
can see that the last ID for this table right here is andriana. So I joined right here is
three. So this three represents this three right here, so is the last value. So if right
now go ahead and describe the person. So let's go ahead and describe person. And you can
see that we have this next vowel, so we can actually grab this because this is simply
a function. And I can go ahead and say select and then paste that in, and then end out semicolon,
Enter. And you can see that the next while is four. So if I clear the screen, run that
again, you see lights, five, again, six, seven, so on and so forth. Now, if I go ahead and
select and then start from an A person, ID and then sequence, you can see that the last
value is eight, right here. And you can see that he represents this one right here. So
this means that if I go ahead and add a new person, so if I go ahead and select star from
person, you can see that Adriana has ID three, but because we invoked this function right
here, so next to Val, right, and the next while now is eight, the next person that we
insert into this table will have the ID of nine. So let's go ahead and try that. So I'm
going to go back to my SQL. And then what we're going to do is grab this, and let's
pretty much just change this to something else. So let's go ahead and say, john, and
then same surname, male. And then let's say just john, and then country. Let's go ahead
and say England. So I'm going to grab that. And then you can see that right here, I'm
not adding the actual ID. So this is managed by the sequence. Now I'm going to go back
to item. And then if I paste that, you can see that the insert did work. But if I now
select, so if I press up two times, select and you can see that now, john, so john right
here has an ID of nine. And this is how you pretty much use sequences.
sequences is simply a big int. So depending on whether you use cereal or big zero, so
if you cereal is an integer, if you use big cereal, it's a big int. So if I describe person,
right here, you can see that the type is big int, which is managed by this sequence right
here. And finally, another thing that we could do with sequences is actually restart the
actual value. So if I pretty much clear the screen, and let me invoke this sequence right
here, so this function right here, so I'm going to call it again, also 10 1112, and
13. If I go ahead and select star, and then from person ID sequence. And you can see that
the last one is 13. So if I select now start from person, let's say that we want to restart
with a value of 10. So basically, the next person that goes into this table should have
the value of 10. And to restart a sequence, we can say alter, and then sequence and then
the name of the sequence or person ID and then seek or sec for sequence. And then you
can simply say restart, and then with and now the actual value. So let's go ahead and
say 10. If I press semicolon, you can see the command worked. If I go ahead and select
from sequence, you can see that now the last value is 10. Right? Or actually, we could
have restarted to nine, right, so if I go ahead and say nine, and nine was the actual
last value right here, so nine, so nine, enter. If I now select star from the sequence, the
last value is nine. If I go ahead and invoke the function, so select Next well, you can
see that now it's 910 11 1213 1415 signs off off. And this is all you need to know how
to work with sequences. Alright, in this video, let's go ahead and learn about Postgres extensions.
So Postgres is designed to be easily extensible. And for this reason, extensions loaded into
the database can function just like features, which are built in. So basically, extensions
are simply functions that can add extra functionality to your database. So to view the list of available
extensions, go ahead and simply say select and then star from and then PG underscore
available. So you can see that press tab, available extensions, by pressing my column,
and right here, you can see the list of all extensions. So basically, you can see for
example, the name, so the name column. So for example, this one ref ENT functions for
implementing referential integrity absolute. You also have, for example, XML tools. So
this is for XPath. querying, you have PG visibility. So right here examine the visibility map.
And page level, II also have a store. So this is simply a data type for storing set of key
value pairs useful. So if I scroll down, you can see that there are a bunch of these guys
right here, and even half, so this one, which is really cool. So PL v eight. So this allows
me to write JavaScript functions. And this is really, really awesome. You also have,
for example, SSL info, so information about SSL certificates, functions for auto incrementing
fields, and you know, a lot more. And this one here, so let's take a closer look on this
one. So you will Oh s SP. So basically, this allows you to generate a universally unique
identifier. So you it's a very interesting data time for primary keys, and pretty much
unique key so as you can see, it generates universally unique identifiers. So this makes
it a good fit for primary keys. Alright, let's go ahead and learn how to use you IDs or universally
unique identifiers with Postgres. So basically you IDs allows us to have a guarantee unique
identifier whenever the identifier is generated and also The cool thing about it is that is
globally unique, which means that collisions is pretty much impossible. And the way that
they achieve this is by using some really cool calculations, basically includes using
a mixture of your mac address, timestamp, and other key factors. But I'm gonna leave
a link where you can access this page and read more about us. But basically, it's very,
very interesting. So also, they have like different versions, you can see, for example,
version one, this consisted of the daytime and the MAC address. And then you have version
two, version three, and version four, so three, and three, and five, and also version four,
which is completely random. So let's go ahead and learn how to use this with Postgres. So
I'm going to go back to my terminal. And remember, in the previous video, I've showed you how
to select and then start from PG and then available extensions. And if we scroll down,
you can see we have this uod Oh, s SP, right. And to use you IDs, we have to add the extension.
So you right here, you can see that we don't have any installed version. So let's go ahead
and install that. So I'm going to press Q, and to install an extension, you simply have
to type create, and then extension. And then if not exists, so if not exists, simply makes
sure that it doesn't install the extension, if already exists. So it's an item potent
command, which means that you can execute as many times as you want. And it will only
have an effect once. And the extension that we want has to be within quotes, and simply
type the extension name. So for us is your wit, and then dash, and then oh, s SP, and
then press semicolon, Enter. And you can see that now we created extension. So if I go
ahead and select star from extensions, or PG available extensions, enter, and you can
see that now we have the version 1.1 installed. Now let's go ahead and learn how to generate
a uod. Somewhere, press Q. And in order for us to generate a u ID, we have to invoke a
function. So if I pretty much just press backslash, and then question mark, and simply search
for function, and then scroll down. So right here, you can see that we have this command.
So backslash, D, F, and then we can see the functions. So let's go ahead and try that.
So I'm going to press Q and then backslash D, F, Enter. And now look at this. So now
we have these functions right here, we are available to us. So remember, so because we
just installed the euid. Extension, we have these functions right here. So prior to that
all of this was empty, which means that now we can pretty much just invoke these functions
right here. So the function that I want is this one right here. So version four. And
if you remember correctly, version four is completely random. So I'm going to go back
and to generate, you will simply type select, and then the function name, so you would underscore
generate, and then the and then four, and then pretty much invoke the function, press semicolon, Enter. And now actually,
just let me make this bigger, so you can see exactly what we're doing. Right, I think this is better. So now you
can see that we randomly generated a unit. And this will be unique every time I invoke
this, which is amazing. So let me just simply run the same command again, you can see this
time is completely different. And I can run this a million times. And basically the UE
will never be the same. And this makes it a good faith for using us as primary keys
in our tables. And one of the benefits of using us as keys is that it makes it very
hard for attackers to try and mine our database. For example, if you had an API forward slash
users, and then the actual user ID, so an attacker could actually exploit all the numbers.
So one, two, I don't know 1 million or you know, any random number and try to delete
everyone or update information, so on and so forth. But with you it's it's very, very
difficult for them. To actually guess which person for example, is in your database. Another
benefit is that because they are globally unique, that means that you can migrate data
across databases without any conflicts. So for example, if you had a database a and database
B, and basically, if you were using big serial, so a big int or an int, then most likely you
would have clashes when adding some data from database A into database B, because of the
actual IDs, right? If you were using big serials, it's auto incremented. And basically, in both
servers, there's no way to actually tell that D IDs are different. And that's definitely
a big advantage of using yo IDs. All right, in this video, let's go ahead and change both
person and card tables to use you IDs instead of being zero as their primary keys. So go
ahead and open a person dash card dash two dot SQL from the Exercise Files folder. And
what we're going to do is actually change the actual ID in both person as well as car.
So just let me show you quickly the data types in case you have forgotten. So I'm inside
of the Postgres data type docs. And you can see that this is a list you've seen at the
beginning of this course. So big int, big cereal. So you've, you've seen this one here,
which is a auto incrementing, eight byte integer. So if I scroll down, you can see that we have
you ID right here. So universally unique identifier. So I'm going to go back to VS code, and it's
actually changing. So instead of being serial, let's go ahead and change this to you ID.
So you wouldn't just like that. And the same for the cart table. So you would just like
that. And one more thing that we're going to do is actually improve upon the actual
name of our primary key. So let's go ahead and pretty much just say that this will be
person, underscore, and then you ID and the same for car. So this will be car underscore
and a new ID. And then what we need to do is in the actual foreign key, so right here,
so this no longer references a big n. So this has to be a yo ID. And this will be car, and
then you it just like that, references and then car underscore you it. So car yo it.
So this is the foreign key that references car, and then car underscore URL, which is
the actual primary key. And now we have to change the actual inserts, right? So before
we weren't including the actual ID because it was managed by the sequence. Now we have
to be explicitly about it. So we have to include ID here, we're actually not ID, so we renamed
it to person, and then underscore u ID. So just like that, and then the actual value
is u ID. And then generate underscore, and then v four. So remember, this is a function
that we saw in the previous video. So I'm going to pretty much do the same for the rest.
So I'm going to copy that. So this should be person and then underscore yo ID and then
paste that in. So invoking the function the same here, again, invoking the function. And
let's do the same for car, so car, and then you ID values, and then we paste that function
there. The same for this next car. So car, underscore u ID and then paste the function
there. Alright, and finally, what we need to do is actually change the order of these
table creation. So remember, because we have a foreign key constraint here to car so car
mess exists first. So it's I mean, add car first here, and then the actual person. And
one last thing that I forgot is that this should be car and then underscore you it just
like that. So I'm going to save this as person and then dash car and then dash and then three
dot SQL in my desktop. So I'm gonna save that and we are good to go. Now open up item, or
terminal or Command if you're on Windows. Now, because we're going to recreate these
two tables, let's go ahead and drop the table called person first because there is a foreign
key constraint between person and car. So go ahead and drop person first. And also drop
car. So we're going to drop car. Now I'm going to go ahead and execute this file right here.
So this file from my shell. So I'm going to do backslash I for execute from a file. And
then the destination of that will be fought slash users, Ford slash amigos code, forward
slash desktop, forward slash, and then person dash car, dash three dot SQL. Now if I go
ahead and execute, you can see that everything works. So we have two creations, so two table
creations, and then few inserts. Now if I clear the screen and do a select star from
person. So this time, let me actually go ahead and press backslash x. So you can see that
the expanded displays on perform the same select. And you can see that the person you
wit is now the primary key. And the actual value is a randomly generated unit. So right
here, you can see that they are absolutely different. So in fact, let me go ahead and
describe person. So person, you can see that the actual type is yo ID. Let's also go ahead
and do the same for car. So select star from an in car. And you can see that we have two
cars right here. So one last thing that we have to do is actually assign some cars. So
let's go ahead and do that. So let's go ahead and update or first, maybe make this smaller
the expanded display and simply type backslash, x. And that toggles it off. So I'm going to
make this a bit smaller, so you can see exactly what we're doing. Now, let me go ahead and
select star from person. And also select star from one actually all uppercase from an in
car. There we go. So now let's go ahead and update and in person set, and then car underscore
you ID equals two. And let's grab this first up, so this one here. And this has to be within
quotes, where and then person underscore us. So we're going to assign this car to, let's
say, let's say this time adrena gets to get the car. So we're going to paste that in,
update that. And also let's go ahead and assign a car to Fernanda, so Fernanda will have a
car. So just let me delete that. And the car will be GMC, and then grab this car you ID and then paste that in. Enter, you can see
that works. Now let's go ahead and perform a join. So select star from and then person
and on a new line join. And then we're going to join car. And then we can say on person
dot and then car and then underscore u it equals to cart and thought car and then you
would if I press semicolon, Enter. And you can see that if I make this smaller so because
we have lots of columns. So press enter. Still not enough, but let me go ahead and add the
expanded toggle, so backslash x and then perform the same query. And now you can see that this
first record so this one right here so this is Adriana and you can see that he has the
person details, as well as card details. So it's a joint between those two tables. And
the same for for Nandan and you've learned this on the join section. So I can make this
bigger now so you can see it properly. So now I want to show you one thing and that
is you see that we perform a join right here right so join car on person car you it and
then car and then car you ID. So because these fields are the same so the keys, ie the foreign
key, and the primary key are this We can pretty much just remove that. And then I can say,
join car and then using so this is using, and then car, you would, because both the
primary key and the foreign key have the same name, instead of you saying car, and then
dot car underscore you ID person dot car you ID, you can simply ditch that and use this
using keyword which is much nicer. So let me go ahead and press enter. You see that
also works. So we can also do a left joins. So we want to grab everyone with and without
a foreign key constraint. So for that, left, and then join, and then press Enter. And now
you can see that we have three people back, and Omar, he doesn't have a car. So right
here, you can see that car information is empty for him. And let me go ahead and perform
a left join. And then where so let me go ahead and say where and in car. dot and enstar is
no. So now we should only get Omar, there we go. And this is how you use you IDs with
Postgres. And also, you saw that this keyword right here using is really useful when both
the foreign key and the actual primary key have the same name. Alright, first, I want
to congratulate you for completing this course. Now you should be aware of how to use Postgres,
you know, we've gone through a lot of important concepts that you must know, in order for
you to be able to open a shell or P SQL, and start writing queries, that really makes sense.
So everything that you've learned in this course, is very valuable. And if you have
any questions, go ahead and drop me a message. And I always say, because I really want you
to engage. And if you feel that there was, for example, a specific topic or concept that
that you have not understood correctly, go ahead and let me know. And I'll make my efforts
to explain it further. So the next step for you now is to either
take one of my courses on Spring Boot, or node j, s, and express. So Spring Boot allows
you to create very fast applications using Java. Or if you are into JavaScript, you can
take the Node JS and express course. And basically, you can take whatever you've learned from
this course, and apply it to create back end applications, right? So SQL, or the actual
database is the foundation of your back end, right. So this is where you take some data
from clients, and then you store that in a database. So you've learned the database part.
Now you should learn the actual back end application, right? So how you process information how
you create restful API's, right? How you can provide clients with services that they can
use and make sure that your system has the right behaviors. And this is when Spring Boot
and no Jess allows us to do so that allows us to create applications that we can deploy
and have clients using. So you've learned the database part. Now it's a way of view,
taking an application and connect to a database and start storing, retrieving and manipulate
data. And finally, if you want to further enhance your skills with Postgres, go ahead
and check my course on advanced Postgres. So this is where we go into much more advanced
topics such as indexes, functions, more complex queries, common table expressions, triggers,
views, and all other important concepts that you must know. Right? So in this course, we
cover the essentials and this allows you to get going. So now you can expand your knowledge
by taking the advice Postgres qL course. So, this is all for now. And I want to thank you
so much for being my shooting. And I'll see you on the next course. Join me there. See
ya.
