Original subtitles

Okay, now that we have the PG admin with us, let us create a new database.

This will be a test database.

To create this database, we will go to the database option.

Right click on it and click on the Create database option.

We'll just give a name to this database, which will be list.

And we will save this database.

Once this database is created, we will get a notification saying Database connected.

So the database is now connected and we have a new database.

Within this database, we can add tables.

But since this is a test database, we'll look at how to delete this database.

Also, we'll just right click on this database and click on the delete option.

Okay.

This we saw is the graphical way of doing this.

We can also create a database using SQL queries, but that we will not be covering right now.

We'll start with creating a new database, which we'll be using for writing further queries and we'll

be adding tables and datas into that database.

So let's create a database called training.

The second line is the owner.

This is the user which is creating this database.

There are other users also to whom you can give special rights to access their database.

We'll be learning about creating different users and assigning them rights in the later part of this

course.

For now, the owner is the sole user that we have for this database, which is Postgres.

Save it.

Now you can see that training databases created.

In this database.

Next, what we will do is add tables.

So this training database is going to be a database of customers and products and the transactions that

those customers are doing.

So we'll have three different tables in this database.

First will be of customers, which will contain customer data.

Second will be of products which will contain product data.

And third will be of the transactions.

So, for example, customer is buying product B, So the third table will have information like customer

A bought this much quantity of product B for this much amount.

So we'll create these three tables and add data to it.

So to create a table, we'll be using create table command.

So before writing this one, let's look at the syntax and example of this command.

So when we are creating a table, it is important that we design it very carefully.

A better design table will be taking up only required space and it will be quick in giving back the

response to queries.

So to create a table using the create command, we need to name the table and also give me the names

of all the columns and the data types.

If you look at the syntax of this command, this starts with create table.

After that.

You mentioned the name of that table.

And after that we have a bracket.

Within this bracket, you will mention all the columns.

For each column, you need to mention the data type, which this column will contain.

We will be discussing these data types in the later part of the course.

Next part is defining column constraints.

This I have put in square brackets as it is optional.

You need not give all the constraints while defining the table.

So you list down all the columns, give the table constraints if any.

You close the command using a semicolon.

When writing a query, you can have any number of spaces.

These spaces are immaterial.

You can use multiple lines so you can press enter after any word.

And it is very important that you close the query using a semicolon.

You did not remember this.

When you practice writing queries, this will become your habit while writing a query.

There are some good practices.

For example, some developers write all the key words in capital letters.

It is not mandatory.

You did not write create table in capital letters.

To just to segregate and make it more readable.

You can keep all the SQL commands as capital and other as normal case.

Now we look at what all constraints are available to be put on the columns.

Majorly, there are these six types of constraints.

First is not null.

If you define any column to be not null, it cannot have blank value.

It cannot be null.

So this check will ensure that whichever column is mandatory for you while entering data, you don't

miss out that value.

If you miss that value while storing the data, it will give you back an error.

Default constraint can be defined to store the default value if any input value is missing for that

particular set.

Unique constraint will ensure that that particular cell has a unique value within that column.

Using the check constraint, you can specify a certain criteria against which the data inputted will

be checked.

The last two are the key constraints.

You can specify any column to be a primary key or a foreign key.

Let us look at these two types of keys.

A primary key is a column or a set of columns which makes the values in a table unique.

Primary key therefore ensures that there are no duplicate records in the database.

If there are more than two columns used as a primary key, it is called a composite.

The foreign key is usually a column that references another column of another table.

Let us look at this from an example.

So suppose we have one table, which is a customer table, and it is storing customer ID, customer's

first name and customer's last name.

We define customer ID to be primary key to identify each individual customer.

Customer ID will not be duplicate.

Each customer will have only one customer ID.

Now in the second table, which is the order table and contains the description of all the orders that

we are getting.

Order ID is the primary key.

For each individual order we will have one unique order ID.

But the customer ID is a foreign key.

What this means is this column will have values which are available only in the customer ID column of

the customer table.

It will not have any other value to this foreign key column is related to the primary key of the customer

table and it is restricting the values in this column by the superset of values in the customer ID of

customer table.

So if you can imagine the values in the order table, there would be several orders which will have

same customer ID.

So if the same customer is ordering different products for that order, the customer ID will be same.

So the primary key of orders will be unique, but the foreign key will be having duplicate values within

a table.

So having duplicate values for foreign key is allowed, but it is not allowed for primary key.

Secondly, when you want to find out first name and last name of a particular customer with a particular

order ID.

So, for example, if I want to find out the customer who ordered with the order ID one, I'll see the

customer ID of the customer in the order table, and then I'll find out what is the first name and last

name of that customer with that particular customer ID in the customer table.

So this is the concept of keys.

It is very important to specify which will be your primary key and which will be your foreign key.

With this information.

Let us now create the first table, the customer table in our database to create a table.

Will start writing the SQL query.

If you remember how we got this screen.

We clicked on the database and selected the query tool.

So right click on the training database and.

Select the query tool.

So this is where we write the query.

Start writing create table.

Clear table is the command.

After this, we will specify the table name.

Which is customer table.

After this, we'll start a bracket.

And within this bracket, we will list on all the columns.

First is the cost ID.

Then we write data type, which will be integer in.

And next will be my next column.

First name.

This will have.

Pitiable characters were Cat.

Last name as.

Variable characters.

Then we'll have age.

Integer.

Then you will have.

Emily.

This variable characters.

That's a.

So we close the bracket.

And remember to put Semicolon to finish this query.

To run this query.

You'll need to either click on this button, which is execute refresh, or you can see that F five is

also highlighted.

You can just click on F five to execute the query.

Here.

You can see that Curry returned successfully in 172 seconds.

So you're good.

He has run to see the table.

Open the database.

Go to schemas within public.

Inside these tables, you'll be able to see a table.

But if it is not refreshed, click on Refresh first.

There'll be one table created, which is the customer table.

If you want to look at the columns, also, if you open the customer table.

We you click on this plus sign.

There's the column option, Customer ID, first name, last name, age, email ID.

These are the five columns that we created.