The PostgreSQL program you installed in previous lectures.
The database management system, a software package that allows you to define, manage and query data
stored in databases.
A database, as I said, is a collection of objects that includes tables, functions and much more.
So when you install PostgreSQL, it created a database server, an instance of the application running
on your computer that includes the default database called PostgreSQL.
As you can see here, this is our default database that we.
Created in our installation process.
So we will create a new database to use for the examples on this course rather than use the default
so we can keep objects related to the particular topic or application organized together.
So this is a good practice.
It helps avoid a pileup of tables in a single database that have no relation to each other, and it
ensures that if your data will be used to power an application such as mobile application, then the
application database will contain only relevant information.
So to create a database, you just need one line of SQL code which.
And also you can find this code on our downloadable, on our lectures, downloadable section.
You can download the code here in the text format here.
So now we will go to servers and we will click on Object, Create or here tools.
We will open the query tool here.
So we'll go to Databases tool.
Just one click on databases.
And query tool.
So now.
We will use the code.
Create database.
I'm sorry.
Yeah.
So create database.
Database and name this database, for example.
In this case, let's actually name it Oxley.
And here.
So this statement creates a in this case, the statement creates a database named Oxley on our server
using the PostgreSQL settings.
So I want to also note that the code consists of two keywords.
In this case, it's create and database, followed by the name of new database.
In this case it's Oxley.
So you end the statement with a semicolon here.
Which signals the end of the command.
So you must end all PostgreSQL statements with a semicolon as part of the ANSI SQL standard.
In some circumstance, your queries will work even if you omit the semicolon.
But not always.
So use the semicolon.
Using the semicolon is a good habit to form.
So now let's execute the SQL in pgadmin.
So you install the graphical administrative tool Pgadmin.
If you didn't, go ahead and do that now in and I explained that in previous lectures.
So for much of your work you will use Pgadmin to run the SQL statements you write knowing as executing
the code.
Later in this course, I will show you how to run SQL statements in a terminal window using the PostgreSQL
command line using the SQL here SQL.
Uh, here.
Here.
Ksql.
So buy things, get things, starting with it easier with a graphical interface, of course.
So here we will use the Pgadmin to run the SQL statement that creates database.
Then we will connect to the new database and create a table.
So now let's run a PostgreSQL.
So if you are using the Windows installer says PostgreSQL launch, every time you boot up on macOS,
you must double click PostgreSQL application in your application folder.
So now.
Let's click on this run button or F5.
And as you can see here, our database is query returned successfully.
And here, if you are not seeing any database here, just click on right click on the databases or your
server and click on refresh.
And here, as you can see here, our database is come up here.
So now let's click on that.
And here that's it.
This is our auxiliary database.
So.
Now.
What are we going to do here?
As you can see, we have databases here.
We have Oxley and PostgreSQL inside this PostgreSQL server.
So now.
We will open up Oxley here and now we will use the create.
As you can see, there's a several options we can do with this.
And after that, after right clicking on Oxley, we will select the query tool.
And as you can see here, our database name is changed.
As you can see here in previous here, we had something named Postgres here, which is this is associated
that every command we type in here goes to here Postgres.
But in this case, as you can see, the name changed and it actually is obsolete.
So that's it.
And let's connect to the database now.
Here.
So before you create a table, we also need to create a table, right?
Because, uh, and we created our database firstly, you know, have a database called Oxalate, which
you can use for the majority of the exercises in this course in your own work.
It's generally best practice to create a new database for each project to keep tables with a related
data together.
And before you create a table, you must ensure that the PGADMIN is connected to the Oxley database,
as I showed you, rather than to the default Postgres database so we can actually close this.
Don't say Yeah, this is now we are connected to Oxley database.
That's it.
So.
Now, let's as I mentioned, tables are where data lives and it's a relationship with are defined.
So when you create a table, you assign a name to each column, sometimes referred to as a field or
attribute, and it's assign each column a data type.
These are the values with the columns we accept, such as text, integers, decimals and dates.
And the definition of the data type is one way.
SQL enforces the integrity of data.
For example, a column defined as a date will accept data in only one of several standards formats such
as years and months and days.
Right?
So if you try to enter characters not in a date format, for instance, the word.
The word apple, you will receive an error.
So data stored in a table can be accessed and analyzed or queried.
So which SQL statements?
Of course you can sort, edit and view data as well as easily alter the table later if your needs change.
So now let's make a table in our database.
So now we will use the create the Create table for this exercise.
We will often discuss a piece of data and teacher salaries.
And here we will create a create table.
Great.
Actually, if we would increase this font size a little bit, it will be awesome.
So you can see more.
And here.
Object.
No file.
References.
And here it has somewhere here.
Debugger area graphs.
MySQL stream user language is here.
Query tool display.
Editor.
Font size to maybe.
Oh, it's so big.
It's too big here.
Maybe we will put one and a three.
Are.
Browser.
This dashboard graphs Miscellaneous path Query Tool and Editor one and three.
Perfect.
Now we will create a table.
We will write this code and I will explain all of this code.
Now, in this lecture, we will create a table table and we will name this table.
Teachers.
And here we will have a columns here.
And now we will ID, we will give it an ID, which is this ID is going to be datatype of big cereal.
And we will also do, of course, the teacher also has an first name and last name.
We'll write that now.
I the big serial.
First name.
First name is going to be our character.
Which is going to be 25.
And the last name.
Last name.
Let's actually make it our character as well.
50 plus names might be longer in some cases.
Schools.
Cool.
And our character is also the character type and it's going to be 50.
The school name might even longer than last name, and we will also set a higher date, that higher
date that our teacher started his or her job and what teacher has.
His salary.
We will also add the salary, which is numeric.
Numeric here.
That's it.
And after that, we will close that.
As you can see here, you can also close that.
Right here or here, it doesn't matter in some cases.
And that's it.
And I'll add the semicolon here.
So the table definition is a far from comprehensive.
For example, it's missing several constraints that would ensure that the columns that must be filled
do indeed have data or that are not inadvertently entering duplicate values.
So I covered constraint in detail in next lectures, but these early lectures, I'm omitting them to
focus on getting you started on exploring data.