Hi. In this lesson, we understand how to create a database object called table. database is a collection of objects. The most important object is table, because that's where we stored the data. We also have other objects, like views, sequences, indexes, and synonyms. We'll talk about those objects later.

In this lesson, we learned how to create a table. We have been using tables, which are provided by Oracle in HR account, like employees, jobs, and so on. But now we understand how to create our own tables. It all actually begins with the table because without a table, the rest of the objects make no sense but to Create the table, we need to take so many decisions up friend, we need to decide what name you want to give to table. It depends of course, on what you store in the table. Then you need to decide what columns you want to have.

And for each column, what name you want to give, what data type, what are the constraints, so on. To start with. Every object is to be given a valid name. And these are the simple rules you have to follow at the time of giving a name to an object. So nothing new bought it, it must begin with a letter. It can have alphabets, deserts, and a few special characters like underscore, hash and dollar.

And in the same account, also known as Kima. You can't have duplicate names and it can Not be an Oracle reserved word. And names are not case sensitive. So you can use either uppercase or lowercase doesn't matter. Now, when we create a table, you talk about columns. And for each column, we have to provide some details.

Like, what is the data that you want to store? Based on that the column is given an appropriate name? And what type of data are you going to store and the most common options or number and the character where character date? So you have to specify what type of data you want to store in a column. And if you're planning to store some character data, the options are character or bar character. What is the difference?

There is a subtle difference, but that's an important difference. If I say I want to have a column that is of type character five, it means I can store up to five characters. If I give ABC, Oracle stores it as ABC followed by two spaces, it always paths it with spaces on the right. You have to be aware of that. Because this could really make a difference at the time of searching. If you're looking for just ABC that won't be a match to this, because it is supposed to be ABC followed by two spaces.

Whereas in case of a bad character to well, when our character and our character two or almost the same, for now, they are one and the same, but Oracle recommends where character two because it can change the way our character works. So we stick to our game actor to where character to is saying if you give ABC it'll store only ABC no padding is done on the right. So, that is a subtle difference between character and our character, it has impact on the amount of space occupied and also the way the comparison is done. So you have to be careful. Now, coming back to data types, these are the data types, and we need to decide what type of data we want to store in a particular column. Of course, there are ways and means to change a few things later, but the earlier you decide what is to be done, and if you can stick to that, that's always better.

Because changing the structure of the table after table is created is possible but there are ifs and buts Now, here is CREATE TABLE command. Using CREATE TABLE command we can create a table and it is a DDL command, data Definition Language command. And the things you need to decide at the time of creating the table. The first and foremost the table name, always give a meaningful name. Then for each column, you have to decide the column name, the data type of that column, how many characters or desserts the column is expected to accommodate, and what is the default value. That means if no value is provided, what value you want the column to take automatically, and the rules called constraints are very important.

Using these constraints, we achieve data in T grippy means data validity. So, these constraints are related to a single column. But there are also constraints are rules that are related to multiple columns. And such constraints are to be given at the table level, there are called table level constraints. So here is the CREATE TABLE command, CREATE TABLE followed by the name of the table, then for each column give the data type and the rest of the details are optional, like default value the constraint, but in most of the cases you gave constraints and in some cases even default value. And finally, you also need to give table constraints.

So, the default value can be any constant or it can also be an expression and it can also be a SQL function. So, these are the values that can be given you cannot refer to other columns or you cannot refer to anything like a pseudo columns, you can't use something like a row num or any of those columns. And then we are talking about something like the value whatever you give must be matching the data type of the column. And in the recent past Oracle also allows you to use something called a sequence, but that you understand after we understand what is a sequence for now, do not worry about it. So here is about the rules. The rules or constraints are very, very important because they make sure the data is always valid.

Primary key constraint is ensuring The column is unique and not null. You can never have duplicate value once you designate a column as primary key references is used to implement foreign key. It says a column is a reference to another column in the parent table. Unique a saying it is supposed to be unique, but it can have null values. But if you don't want null value in any particular column, you should use not null. Check constraint is meant to perform some elementary validations.

Like the amount should not be less than zero, or value cannot be more than 100, things like that. We'll see some examples. So here is the syntax for column constraint. You give the constraint name, every constraint better have a name. And if you don't give a name to a constraint And then Oracle will automatically assign a name. But that is not a name, you can easily remember because it's a cryptic name.

So we strongly recommend you always give a name a meaningful name to every constraint. So these are the options like primary key or unique, you can't use both at the same time, because both are in a way similar and not null is another constraint. References is about foreign key. But while you're creating references, or foreign key, you also need to specify whether you want to delete all the child rows when parent row is deleted. That is called on Delete cascade. It says, if the parent row is deleted, I want all the child rows to be automatically deleted.

If you don't give this then Oracle does not allow you to delete a parent when child rows are present. So if you want child rows to be deleted automatically when parent is deleted, you need to give on Delete cascade. Other option available to you is set. Now, this says don't delete the row, but set that foreign key value to null. So you get the foreign key set to null value when the parent row is deleted. This can be an another option, but if you don't get one of them, then there is no way you can delete the parent row when child rows are present.

Of course, you can delete the child rows first and then go and delete parent but not when you have child rows. You can delete the parent row then the check constraint is saying it is just taking a simple condition. Now, let us see how we create a table. Here is my first table. This is called courses table, I want to have a very simple table with course code and course name. And course code can take up to five characters, and it is supposed to be unique and not null.

So we say it's a primary key. And we also give a meaningful name. This is actually optional. If you're not interested, you can leave it out, but it's strongly recommended. So give constraint that's a keyword followed by the name any meaningful name will do. It's up to you.

I give table name followed by the type of the constraint. So PK for primary key, and this is the constraint. The next column is to accommodate costs. Name, which is up to 30 characters, and it is not supposed to have any null values. So it is marked as not null. So no null value can be stored.

And it is given a name like courses underscore the column name followed by n. That's just my convention. And you can follow your own convention so that you have a meaningful name for every table, you create an every constraint you create. Now let us go and run this. And here is my command. I already placed it in my SQL Developer. And if you go and check my tables right now, I don't have any table called courses.

I don't have any table called students but let's go and run this and then run this it creates a table so table courses create created. And if you go and check that you are going to see that I just need to refresh this. And now it's loading gate. And here is your courses table. Of course, it contains no data right now, because we did not insert anything. But if you want, you can so this is the way you insert, insert into courses values, and I'm giving the course called as Aria and I say Oracle Database 18. c is the name of the course I can just select this and run and it's going to insert a row into it.

But if you are not giving the value for name, and you may recollect, you can give the column names and give values for that. If you try to ever do this, for example, I'm talking about Java, se but I can't do this because I given only five characters for code, so I can cut it short to j S II, this is fine. Now if you try to execute this command, you're going to get an exception and that says cannot insert null into this courses table and name. That's what it is clearly telling because you said that is a not null column. And also, if you try to use any duplicate value, like Aria, it's going to be an error. So if I try to execute this again, it's going to throw another error and this time unique constraint violated.

And there you can see the constraint name also. That's the reason it's important to give a valid name for every constraint. So we got our first table created and we also inserted some data, you can insert a lot more data. Let's go and then insert something like a Java s EE, and let's say 12. And this is fine, Eddie, go ahead. And anytime you can see what details you have Select star from courses, and then you get those two.

Okay, that's about our first table. This is about how you insert and this is about how you retrieve. Now, this is my second table. Now here is my students table. Before we execute it, let us understand what is what students table has admission number, which is primary key. Then it has name which is not null.

I don't want null value to be there in student name, joined on a saying itself type date and if you do Don't give any value explicitly at the time of insertion, then it is going to take system date. And then cause is a foreign key This is important. So I'm saying students table has a column which is referencing courses table. So course is a foreign key course is referencing courses table and in that the column called code you got to mention the column name explicitly because the column name in the courses table, the column name in students table are not having the same name. So you need to explicitly mention the name. In case if they have the same name, you can drop this then it is understood.

Well, we give a name to the constraint and then we are using a references cut And then total fee cannot be less than zero. So I'm saying it should be greater than or equal to zero. Well, there might be courses without any course fee. So, you are allowed to have zero for course fee, but it cannot be negative. So, is the case with the students fee paid fee paid also cannot be less than zero. And another very important constraint here is table constraint.

This is saying the fee paid should be less than or equal to total fee, it cannot be more than total fee. Now, this has to be given as a table constraint, because it deals with the two columns and this cannot be done with any column constraint. So this is the table and in this we have information about Students, and we refer to the course the student had joined in. And then other details. Now let's go and run this. So this is the table, I'm going to create it, just select that command, go run it.

So it's saying students table created, we can clear the stuff from there, there is an option like clear, remove that. Now what you can do, you can now insert some rows. So insert into students. And if you are planning to give values, you can accept the primary key and then name the rest of them can be left out. So I can say insert into students admission number comma name, and I say values one, and I say, Bill, let's go run this and it's going to insert a row and if you're curious to see what data you get Select star from students. And that's going to give you the data like this.

It's saying one bill joined on fourth, September 2019. That's system date. And courses now total fee is null and the fee paid is null. Well can cause be null. Yes, because we just said it's a foreign key, but we didn't say it cannot have null value. So foreign keys can have null values.

So this is the way and if you want to later change it, you know what to do it UPDATE students and then set for example codes to array and then we are talking about to where admission number is one, this is what we need to give. Go ahead and run this command. It's going to update Now if you go and check this, it's going to be oriented there. But be careful, you got to be very accurate with the codes. If I try to give lowercase o RA, and try to proceed, it's going to throw an error and the error is saying you have an integrity constraint violated. And that is students cause foreign key.

So parent key not found, because the commands are not case and still, but strings are always case sensitive. Be aware of that. So when you're trying to compare or when you're trying to assign you have to be careful with the case it has to be capital or a otherwise it is not going to be found in parent table. That's what is happening and it says parent key not found. So you have to create tables like this. A lot of design goes into it.

In real world, you have dozens and sometimes hundreds of tables, you have to understand what data you want to store in each table, what columns, you want to have the constraints, a lot of stuff. So this is what I want you to do create courses table, and then insert some rows, I suggest you go ahead and try to insert at least two five rows. Then you try to create the students and insert at least 210 rows and try to update the data see how constraints are working. So that's about how we create the tables. That's the summary of this lesson. We just created table.

We understood data types. We understood mainly character where character number and date and we understood CREATE TABLE command using video We can create a table, and column constraints and table constraints are to ensure data is always valid. And we created two tables. And I suggest you think of more tables and give a shot, try to create more tables on your own. But we will be using these two tables every now and then. But we mostly stick to the tables that are already present in our account.

So we learned how to create tables, and you know already how to retrieve data update. So keep playing with these tables with more about the tables and altering the structure of the table, what options we have etc. We will review in the next lesson.