DML

Manipulating Data Language (DML)

Before execute DML statements, you must have INSERT,UPDATE, and DELETE privilege.

INSERT EXAMPLES

1-   INSERT INTO NEWEMP(EMPNO,ENAME,DEPTNO,SAL)

VALUES (1800,’ASD’, 10, 900);

2-   INSERT INTO NEWEMP(EMPNO,ENAME,DEPTNO,SAL)

VALUE (1600,NULL,10,100);

Be sure that before inserting null value in column check if it accepting null value or not.

----

You can insert a special values like user name (must be as character data type) and SYSDATE

For example:

Assume you want insert SYSDATE as using TO_DATE function to change a format

Type

Insert into EMP (ename, hiredate)

Values (‘MEGA’, TO_DATE (’01-JAN-2011 10:00’,’DD-MON-RR HH24: MI’), 20);

------------------

Copy data from another table using SUPUERY

You can insert data from another table using subquery

For example

Assume that you want copy data from dept =10 in emp table to a newemp table

Type

Insert into newemp

Select empno,ename,deptno

From emp

Where deptno=10;

------------

UPDATE

Also you can modify existing rows by using UPDATE statement .

For example

Assume that you want to change SMITH job and his salary

Type

Update EMP

Set job=’PROGRAMMER’, SAL=2500

WHERE ENAME=’SMITH’;

-----------------------------

DELETE

You can delete all rows in the table or delete one row or more by specifying a condition in the where clause rows.

For example

Assume that we have TRAINING table including data for students

If you want delete all rows in table

Type

Delete from TRAINING;

If you want delete specified row assume for trainer id =1700

Type

Delete from TRAINING

Where TI=1700;

NOTE: that if you want deletes row content a primary key referenced as a foreign key in another table the server not accepted.