Hello, my name is Typhoon.
And in this lecture of our course, we will add rows into a table and we will use also insert statement
and other statements as well.
So to insert some data into the table, you first need to urge the create table statements.
Just run in previous lectures.
So firstly we will start our.
Sqlplus or Pgadmin.
Of course.
Pgadmin for this is the latest version of Pgadmin.
And here you will see.
Our database that we created in previous lecture.
And here we're going to enter our master password.
In this case, my password is one, two, three and click on.
Okay.
And we are seeing our database is in it.
So here.
Password.
And here we have we have com and servers and.
Databases.
Database as you can.
As catalogues.
Faces.
And here we create some Postgres database.
And you will learn all of this.
What do you mean?
Database.
This is.
Bruce lecture and here publications schemes and tied this we have tables and here as you can see we
have.
Cables here.
So now.
Um.
We will.
Following the same steps you did in previous lectures.
You did create a database and tables and here.
So I'm going to first fix that because I see that our contrasts and saturation of the video is much
high and I will make it lower.
So I think it's.
Yes.
So.
In previous lectures.
As I said, you did create the database and table here.
So with into your Pgadmin query tool, as you can see here.
So now.
We will add some values in it, right?
Firstly, we're going to go to Pgadmin query tool here.
And here we go from.
And truncate we have we have several options here.
And here you go to tools and here query tool.
And make sure you are using on Oxley Postgres that we just created.
On in Oxley.
If you click on this Postgres and click on Tools.
For it all.
As you can see here, we are seeing different here, right?
Postgres.
These are two different databases.
Anything you write here will affect anything you write here will affect Postgres.
So now.
We will write our code insert.
Insert into teachers.
Teachers.
And we will add the first name.
As you can see here, we.
Because we have.
Because alarms, as you can see, we have several options here that we created.
And here we have first name and we will also inside this here, we will also add the first name last.
Name.
Cool.
Air date.
Rate and salary.
Audit and salary.
After that, we will add some information in it.
So in this case, we will add into and we will syntax here add into values.
Alias here.
Now, the first name in the phone here and the phone first name.
Let's be a myth here.
And.
The.
Yes.
First we have first name, last name school.
Here we have the alias date.
University.
We have the after school, we have hire date and salary.
Right.
In a statement versus hire date, let's make it 2015 and here hire that.
So we will have to right hire that the dates in SQL in format here.
So firstly year.
2015, and after that we will enter the month.
So it's going to zero nine and after that we will light the day in this case, 25.
After that we have after that we will also have salary and in this case we will add salary for.
We can also make it measure is yearly, monthly, weekly, whatever you want.
In this case, we will make it monthly.
We will see it monthly, but we will just add some value in it.
Right?
So SQL doesn't know if it's monthly or weekly, right.
Or yearly.
So.
Here we had 5000.
And after that, let's add another with comma.
Let's add comma.
And after that we will add a new here, parenthesis here, and we will add sample.
Let's make.
Gone.
And here.
After John.
The short name John Smith.
And here?
Cambridge.
Cambridge State University?
I don't know.
University is correct, but it's just a drink here that we are using.
So now.
First name.
Last name here.
First name?
Don.
Last name Don.
We have school, Don.
And we also we need to just add hire date and salary so we can also use some random name generator,
right?
So here we will add random name generator.
Writer random name generator.
Also known name.
Surname generator.
And.
The name generator.
Here.
So we have this year, right?
Bill will be president.
Fake identity.
Generator.
You know the vision here.
As you can see here, we will regenerate.
So now the run, the message has to run the bases.
So let's first complete the John Smith and here we will add doesn't hire.
It is going to be 2002.
Uh, here.
12.
And salary is going to be.
It's a 9000.
And after that we will add here.
So during the missions.
During the missions.
Green.
Here to the US.
And here, 27 years old, we have Guzman online finance and physical characteristics, tracking numbers.
As you can see here, this is actually pretty nice, right?
Just created a fake identity for us here.
We will.
And we will also.
I think it made us the university here.
Let's go to advanced options.
So make sure you make it.
Employment finance.
Email.
Birthday on mother's maiden name and so on.
And.
Physical characteristics.
Employment.
So.
Yes it didn't gives gave us the.
They were here.
The Messiahs and here.
After last name, we will add school.
Oxford.
Oxford and.
University.
And after that, we'll add date.
It doesn't.
12.
To.
Four zero, five and ten.
And after that we will add the salary.
Salary is 15,000.
Come again.
And we we will also need to add like, let's add two more person and that's okay for us enough.
And here we will add.
Ahmet and.
Ahmet.
Till this year and after we have Istanbul.
Istanbul.
University or Istanbul Technical University.
Technical University.
And after that we will add the higher rate.
Ah, this is going to be 1820.
Then.
Of.
And after that we will add salary.
Or let's make it 9000.
That's it.
And after that we'll it the for entity for variable.
Okay so now.
Add this code block inserts names and data for one, two, three, four for teachers.
Right.
So here the post cure PostgreSQL syntax follows the ANSI SQL standard.
So after insert into let's actually let me get my pen.
So after insert into.
Right.
Um.
This after inserting the keywords is the name of the table which in this case teachers.
This is the name of the table.
So and the parentheses here, the parentheses are the columns to be filled.
Right.
First name.
Last name.
School hire.
Date salary.
And.
In the next row are the values.
This is values, keyword and the data to insert each column in each row here.
So.
One, two, three.
Or that's it.
So you need to enclose the data for each row in a set of parentheses, as we did here.
And inside each set of parentheses, use a comma to separate each value.
Like this.
And the order of the values must also match the order of the columns specified after the table name.
So each row of the data ends with a comma, except the last row.
As you can see here, which ends the entire statement with the semicolon and ends with the semicolon
here.
So notice that the certain values that we are inserting are enclosed in a single quotes, but some are
not.
Right, as you can see here.
So.
Yeah, that's because.
That that.
So some are not, but this is the standard requirement, right?
So text and datas require ports.
So numbers, including integers and decimals, don't require codes.
As you can see, we didn't write the codes here, so I will highlight this requirement as it comes up
in examples.
So also note that the date format we are using a four digit year is followed by the month and date and
each part is joined by a hyphen.
So this is the international standard for data formats.
Now using it will help you avoid confusion.
Right?
Why is the best to use format year, month and then day.
So here we will we can check the this website to I think this was a 11 nine.
Yeah.
So there's a here there's a public service announcement joke about the comic about this.
So public service announcement are different ways of writing days as a numbers can lead to online confusion.
That's why in nine 1988 ISO set global standard numeric date format.
So this is the correct way to write numeric dates and the following formats are therefore discouraged.
Here.
This is the normal dates, normal dates with words and so on.
And here we have cats cat here.
So this was a comic about that made in early days of internet.
Here, close this and you might be wondering about the ID column here.
You see?
See ID, ID column, which is the first column in the table.
So when you created the table, your script specified that the.
Bigger serial data types.
So as the PostgreSQL inserts row, it automatically fills the ID column with auto incrementing integer,
remember?
Arthur Incrementing Integer.
So here we have ID properties, definition, distinct constraints, variables, security, SQL updates
for now, but it will update now.
So now we will execute that command with F5 or clicking on this execute refresh button.
And as you can see, religion teachers does not exist.
Features.
Because we write down PostgreSQL.
Let's copy this.
I always say that make sure you're writing in a correct database.
So here we will run this and as you can see here, Query returned successfully.
And here we will.
Uh, can we here so you can take a.
Also the last of the two numbers I want to.
Here.
So the last of the two numbers are the insert keyword reports to how many rows are affected inserted.
And in this case, as you can see here, it's 404.
So the first number you might you might be wondering what this first number is, is an unused legacy
PostgreSQL.
So this is the value that is returned only to maintain wire protocol so you can safely ignore it and
you can make attention to second number in this case four, which means the inserted four rows.
You can also take a quick look at the data you just loaded into the features table using the pgadmin
also.
So in the object browser, locate the table and right click.
So in the pop up menu here, you will see the view edit data and after that you will select the all
rows.
Here.
Now let's come to here.
Teachers inside, no tables and teachers.
And here we will click on right, right click on the table name.
And after that we will use the view edit data, all roles.
Here.
As you can see here, this is our database.
It is the big, big integer.
As I said, it will increment one by one.
We have a first name, Typhoon Joan, Doreen Ahmed and Smith.
And we have a last name, Smith Smith, that the Messiah's illness.
And we have schools here.
And as you can see, Ilia State University, Cambridge State University, Oxford University, Istanbul
Technical University as well.
And we also have higher dates and salary numeric.
So notice that even though you didn't insert a value for the ID column, each teacher has an assigned,
has an ID number assigned.
Also, each column either displays the data type you defined when creating the table.
And in this example varchar character Varang character Varang character Varang actually means varchar
so.
Character varying.
So actually, let me take one here.
So this character varying means actually means the.
Here.
So this means for.
Car that we defined in previous lectures when we created our tables and database.
So seeing the data type in the results will help later.
So when you decide how to write queries that handle different depending on its type, so you can view
the data using the Pgadmin interface in a few ways, but we will focus on writing the SQL handle use
tasks.