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.

Constraints in SQL/ORACLE

Constraints

Two types of constraints are provided: column constraints and table constraints. Column constraints are associated with a single column but table constraints are associated with more than one column. Any column constraint can also be formulated as a table constraint.

The specification of a (simple) constraint has the following form:

[constraint ] primary key | unique | not null

A constraint can be named. The most important constraints are not null, unique, primary key constraints.The primary key constraint enables a unique identification of each tuple in a table.

With a primary key, the database system ensures that no duplicates appear in a table.

For example, for our EMP table, the specification

create table EMP

(

emp_code number(4) constraint pk emp primary key,

. . .

);

In this case emp_code must be unique in this table.a table must have onlyone primary key, but more than one unique keys. Null values are not allowed for primary keys.

Oracle/SQL

Data Definition in SQL

Creating Tables

The SQL command for creating table is

create table <table>

(

<column 1> <data type> [not null] [unique] [<column constraint>],

. . . . . . . . .

<column n> <data type> [not null] [unique] [<column constraint>],

[<table constraint(s)>]

);

Each column must have a name and data type. Column name must be unique within the table definition. The column definition must be separated by comma. Column names are not case sensitive. ‘Not null’ constraint is directly specified after the data type of the column. The constraint requires defined attribute values for that column, different from null.

Eg: The create table statement for our EMP table has the form

create table EMP

(

emp_code number(5) not null,

emp_name varchar2(30) not null,

father_name varchar2(10),

Join_date date,

Basic_pay number(7,2),

Department_no number(2)

);

Remark: Except for the columns emp_code and emp_name null values are allowed.