Hi. In this lesson, we talk about how to manipulate data using DML commands. There are three DML commands insert to insert rows into the table. That's about adding new rows to an existing table. update is to modify existing data in the row delete is to delete rows. These three commands are very widely used, and they are called data manipulation language commands.

The first one is insert. This is user to insert a row into a table. The syntax is you give the table name, you give the values that are to be inserted into new row. But at times, you might not be interested in giving values for all the columns in the row. Then you can list out which columns you want to consider. So the first example is where I'm talking about inserting a row into jobs table.

And I'm giving all four values because there are four columns in job stable. And you should also give values in the same order. So this is job ID, this is job title, minimum salary, maximum salary. But if you're not sure about the order of the columns, or if you're not planning to give all then you can give exactly which columns you want to consider. So this is where I give values only for two columns, and that two in this order. So this goes to job ID, the second value goes to job title.

What about the remaining two? Well, they're set to null values, of course, with the exam And that null values are permitted in those two columns, otherwise, this insert would fail. You cannot insert anything that is contradicting the constraints. We'll discuss more about constraints later. But if there is a column that is supposed to be unique, you can't have a duplicate value. If a column is supposed to be not null, you can't have a null value.

And similarly, if a column is supposed to represent a row in another table, I mean foreign key than the value you give must be present in parent table. So insert is to insert a row into an existing table and row is typically placed at the end. But in relational model, the position of the role is not to be taken seriously. Well The next one is update, which is updating an existing role. So which columns are you planning to update? And what values are you planning to give all that you have to give here.

So the table that is being updated, and what columns you want to update, and very importantly, the condition, the where class, because if you fail to give this, every row in the table is updated. That's most unlikely. That's not what you intend to do in general. So you have to be careful about this where class, though, is this optional. If you don't give this a day is going to affect every row in the table. So be careful and don't forget to give where because beginners often forget to give this and end up updating every row in the table.

So here the first command is updating the salary of employees whose salary is less than 5000. So we change the salary to this new value, which is a hike of 10%. So salary is set to the new value, but only for those employees where the salary is less than 5000. And the next example is where we change the hire date of employ 100 to a new date, and this is the date we want to give and using to date, giving a day as well as the time I'm giving both date and time. So we need to mention how racquel should interpret these values by using the format and this we have already discussed. We are talking about to date.

So this is how you update. And when you want to update just one row, make sure your condition is satisfied only in one row. The next one is delete, which is used to delete a row, you can delete a row by explicitly selecting the row. So this is where I want to delete employ 100. And this is where I want to delete the job with the job ID. That is by programmer.

Of course, you can have a condition that is satisfied in more than one row. For example, if you say something like employee ID less than 110 all the employees where employee ID is less than 110 will be deleted. But can we really delete all the rows or any row Can you delete or are there any The restrictions, well, you cannot delete a parent row if there are child rows. And that's what the point is clearly mentioning. It's not possible to delete parent row. If parent row is having child rows.

I mean, I can't delete a job if some employees are doing this job. That means my employees table is still referencing this value as the parent and you can't delete job if that is the case. Well, either you have to delete the child rows before you delete the parent Are you have to use on Delete cascade option at the time of creating tables about on Delete cascade. I will explain more when we talk about creating tables. But for now, this is how you can delete rows from table. And then the next command of interest is not exactly a DML command.

It's called DDL and it just takes the table name and it clears everything from the table. So it's going to remove all rows from the table. And it only leaves the table structure. It's not going to remove the table, it's removing all the rows from the table. So truncate table followed by the table name. Well, what's the big deal then is it not like a delete without any condition because even that is supposed to delete all rows from the table.

Not quite, because this cannot be undone. Once you say truncate table, you can't go back and undo this. Whereas the remaining three commands the DML commands can be undone. How Well, that brings us to transaction management concept. Well here is what we mean by a transaction. Every operation in real world is associated with a couple of steps.

For example, if I want to deposit amount into my bank account, that may mean I'm inserting a row into transactions table and I'm updating accounts table. So it is performing two operations. First a 10 sets arrow into transactions table with all the details about the deposit, and it is going to increase my balance in accounts table by the amount I'm depositing. So there are two steps step one, step two. Now only after these two steps are completed. I can say the job is done.

Done. But in some cases, they might be five steps 10 steps. So you got to make sure all the steps are taken successfully, only then these changes are to be made permanent. That's exactly what a transaction is all about. So you as a programmer should know what changes will make up a transaction. So if you start your SQL developer or SQL plus or a Java program, that's where you start the process.

And whatever commands you execute, they all make up a transaction, of course all your DML commands. And once you feel you're done with the job, you have to then issue commit to make these changes permanent. And until and unless you give commit, your changes are not permanent. Neither your insertion is permanent. Not your deletion. So, make sure you give commit at the end of the task to make sure all the changes are made permanently in the database.

And also be careful once you say commit, there is no way you can go back there is no way you can undo the changes. So, you have to understand what changes are to be made to complete a task. So, perform all the changes make all the changes to database and then issue commit, there is a command called commit this is to be explicitly given. Of course, there are implicit commits, so commit is automatically given in certain cases, but do not take a chance. Once the job is done, you give commit and commit as got a lot of other implications related to locking and concurrency also But they are a little advanced for this course. So all that I want you to do is remember you have to commit at the end of the job.

In case you're decided to roll back means undo the changes you have made, then the command to be used this roll back and if you say rollback then all the changes you have made or undone. For example, after this and after this, you have decided to roll back. So maybe you say something like roll back here. If you say roll back, it is going to roll back all the changes you have made from the previous commit point. That means this is undone. This is undone.

Well, it's your choice, you as a programmer should know which changes are to be undone, whether to be undone or not. But this is the concept I want you to remember. So that's about commit to make changes permanent, and then rollback to undo the changes. And then there are as I told you, implicit commits, sometimes you don't know you're committing, but you are actually committing so you got to be careful. And you don't want to end up a pending change committed unintentionally. So if you don't intend to commit, but if you commit that is disastrous, so just be aware of it.

So all DDL and dcl commands automatically commit the changes and also when you exit from SQL plus, then you are automatically committing, but any abnormal termination of SQL plus or SQL developer would also mean you are not committing the changes and you are automatically rolling back The changes. So those are the commands and those are the concepts. That's what we discussed in this lesson. So we understood there are three important commands related to manipulation, insert, update, and delete, and truncate is also to truncate the table means empty the table, but it's not a DML command. It is a DDL command, so you don't generally use it that regularly. transaction is a very important concept.

It's a collection of statements to perform a single task, like placing an order or cancelling an order depositing into your account. All these are called transactions. every transaction is a set of statements a set of data manipulation steps and if you are done with the transactions successfully then you should commit otherwise you have to roll back. So those are the commands you got to be familiar with. So this is about data manipulation language commands. So we have already seen how to query the data, how to manipulate the data was discussed in this lesson.

And now we have a lot more to discuss. See you in the next lesson.