1z0-071 Oracle Database SQL – MANIPULATING DATA

1. INSERT – VARIOUS METHODS OF INSERTING DATA

Using an insert statement we can insert a row or using a combination of an insert and select statement, we can insert multiple rows. Let us explore. Select call one, call two from tab one. Now I am going to insert arrow with value eleven for call one and A for call two. Since I know the order of the columns in the table, I am just using this SQL. Insert into tab one values eleven a. Let us verify it, but this is not a good idea. If the tables structure changes, then you would have to modify your SQL codes also. So let us be more specific this time. Insert into tab one, call one, call two values 22 BB here.

I am doing the positional matching by specifying call one and call two here as well as 22 and b b here. I am telling that 22 must go into call one and BB must go into call two. Okay, we can also add partial data in a row. Insert into tab one, call one values 33. This adds 33 to call one and null to call two. Of course, this will work only if the other columns can accept null values or they are non null columns with default values. We can also insert multiple rows with a combination of insert and select select Star from tab two which has call three and call four.

Insert into tab one. Select Star from tab two. Notice that when we use a select statement, the values keyword should not be used. Here we use the values keyword, right? So that should not be used. So this will select data from tab two and we’ll insert them into tab one. Let us verify it. The number of columns should match and the data type also should be compatible. Otherwise you would need to be specific about the columns like this. OK, I’m going to roll back everything for further lessons.

2. UPDATE – VARIOUS METHODS OF UPDATING DATA

The existing data using update statements, select call three. Call four from tab two shows its rows. Here I can change all the values of call three to four by using update tab two. Set call three equals to four. Since there is no condition on this SQL, it will go and update all the rows for call three. Okay, rollback. Verify the rollback. Now I can modify the values of call four where its call three counterpart is two. This statement would change the values of call four to ABC, where call three is two. Update tap to set call four equals to ABC where call three equals to two. Let’s verify ABC. ABC. Rollback.

Okay, we can update the values by doing mathematical operations on themselves. Two. Update tap. Two set call three equals to call three times two would multiply all the values of call three by two and update them with new values. Okay, rollback. Or I can use conditional update on mathematical operation. Two. Let’s verify the rollback. Update tap. Two. Set call three equals to call three times two where call three equals to two. This will update the values of call three by multiplying with two. However, that will happen only on values which are two. Roll back. Verify.

3. DELETE – VARIOUS METHODS OF DELETING DATA

Using delete statements, we can delete rows from a table. Select call three. Call four from tab two. We see the rows. Now a simple delete from tab two. Command will delete all the rows from the table. Tap two. Since there is no condition passed, it will delete all the rows. Let us verify it. No rows. Roll back. Verify the rollback. We got the rows back. We can also pass in the conditions delete from tab two where call three equals to two will delete rows from the table. Tap two, where the values of call three equals to two. So these two rows should be deleted. Verify it. Roll back. Verify the rollback.