DDL and DML

DDL(CREATE,ALTER,DROP,RENAME,TRUNCATE)

DML(INSERT,UPDATE,DELETE,MERGE)

Create table

sql>create table stud(name varchar2(10),roll number(5),DOB date);

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

Insert

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

NORMAL INSERT

insert into stud(name,roll,DOB) values('ARKA',1,'12-DEC-1985');

ANOTHER METHOD

insert into stud values('DIBYENDU',2,'16-DEC-1983');

INSERT WITH NULL VALUE

insert into stud values('DIBYENDU',2,NULL);

INSERT ROWS FROM ANOTHER TABLE

INSERT INTO STUD(NAME,ROLL,DOB) SELECT ENAME,EMPNO,HIREDATE FROM EMP WHERE DEPTNO=10;

INSERTING SPECIAL CHARECTER

INSERT INTO STUD VALUES('RONIT',56,SYSDATE);

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

UPDATING

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

UPDATING SINGLE ROW

UPDATE STUD SET ROLL=10 WHERE NAME='ARKA';

UPDATING ALL ROW IN A TABLE

UPDATE STUD SET ROLL=10;

UPDATING THROUGH SUBQUERY

UPDATE STUD

SET ROLL=(SELECT MIN(EMPNO) FROM EMP WHERE DEPTNO=10),

DOB=(SELECT MAX(HIREDATE) FROM EMP WHERE DEPTNO=10)

WHERE NAME='DIBYENDU';

delete ROWS FROM table

delete from stud WHERE DOB='09-JUN-81';

DELETE ALL ROWS FROM A TABLE

DELETE FROM STUD;

DEFAULT CLAUSE

INSERT INTO STUD VALUES('SAUMYA',DEFAULT,DEFAULT);

UPDATE STUD SET ROLL=DEFAULT WHERE NAME='RONIT';

PREPARATION BEFORE WATCH THE

MERGE COMMAND

ALTER TABLE STUD ADD (EMPNO NUMBER(5));

UPDATE STUD SET EMPNO=(SELECT EMPNO FROM EMP WHERE ENAME='KING') WHERE NAME='KING';

UPDATE STUD SET EMPNO=(SELECT EMPNO FROM EMP WHERE ENAME='MILLER') WHERE NAME='MILLER';

UPDATE STUD SET EMPNO=(SELECT EMPNO FROM EMP WHERE ENAME='ALLEN') WHERE NAME='RONIT';

*********************************************************************

MERGE ROWS

MERGE INTO STUD C

USING EMP E

ON (C.EMPNO=E.EMPNO)

WHEN MATCHED THEN

UPDATE SET

C.NAME=E.ENAME,

C.ROLL=E.EMPNO,

C.DOB=E.HIREDATE

WHEN NOT MATCHED THEN

INSERT VALUES(E.ENAME,E.EMPNO,E.HIREDATE);

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

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

ALTER

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

alter table

ADDING A TABLE

alter table stud add(gender char);

MODIFY A TABLE

alter table stud modify (gender varchar2(1));

alter table stud add(class number(2));

SETTNG A COLUMN UNUSED

alter table stud set unused(class);

insert into stud(class) values(12);

alter table stud drop unused columns;

RENAME A OBJECT(TABLE)

rename stud to student;

rename STUDENT to stud;

TRUNCATE A TABLE

TRUNCATE TABLE STUD;

-------A faulty condition--------

after "delete from stud" command

insert into stud values('DIBYENDU',2,'16-DEC-1983');

above command will not run. But this comand run

insert into stud values('DIBYENDU',2,'16-DEC-1983','m');