Data Modifications in SQL/Oracle

Data Modifications in SQL

We can create a table using ‘create’ command. Now we can see how rows are inserted int the table, deleted from the table and modified within the table.

Insertions

Insert statement look like this ,

important.if no column name is speciafied the values must be given in the same order as the columns created in the table.

Eg: insert into emp(emp_Code,emp_name) values(10001,’JAYA’);

We can also insert data to a table from another table with querry.

Updates

We can use ‘update’ statement for modifying the attribute values of a tuple.

The new value to assign to column i must a matching data type. If we exclude the the where condition then the update statement updates all records of the table.

Eg: update emp set department-id=10 where emp_Code=70754;

Like in the case of insert statement other tables can be used to retrieve data that are used as new values. In this case we can use querry instead of expression.

Deletions

Using the delete command all or selected tuples can be deleted from a table.

If the where clause is omitted, all tuples are deleted from the table. The alternative command

for deleting all tuples from a table is the truncate table[ table] command. However, in this

case, the deletions cannot be undone.

Eg : delete from emp where emp_code=23564

Commit and Rollback

Transaction : A sequence of database modifications, i.e., a sequence of insert, update, and delete statements. Modifications of tuples are temporarily stored in the database system. They become permanent only after the statement commit; has been issued. So as long as the user has not issued the commit statement, it is possible to undo all modifications made since the last commit. To undo modifications, one has to issue the statement rollback.so to complete each modification of the database with a commit..

Any data definition command such as create table results in an internal commit. Also commit is implicitly executed when the user terminates an Oracle session.