DML(data Manipulation Language):
**********************************
-->It is used to handle the data in database object.
-->DML commands interact with the buffer first and then COMMIT on a database.
-->Explicit commit will fire.
-->It is used to perform manipulations on database.
-->DML commands deal with the data only.
-->We can undo (rollback) the changes.
-->DML commands are slower.
DML commands:
*************
INSERT
UPDATE
DELETE
MERGE
INSERT:
*******
-->It is used to add new rows of data to an existing database table.
-->It is used to insert the data into a table.
-->To Insert data,table should be first available in the database and below are the ways to insert data into a table.
INSERT command supports all columns.
************************************
SYNTAX:
*******
INSERT INTO <TABLENAME> VALUES (<VALUE1,VALUE2,VALUE3,....>)
EX:
***
CREATE TABLE XX_STU_DETAILS
(
SNO NUMBER,
SNAME VARCHAR2(20),
BRANCH VARCHAR2(20),
JDATE DATE,
MOBILE NUMBER(10),
EMAIL VARCHAR2(20)
);
/
DESC XX_STU_DETAILS;
/
INSERT INTO XX_STU_DETAILS VALUES(1,'A','CSE',SYSDATE,9898989898,'ABC@GMAIL.COM');
/
-->After suceesfully executing the above statements then we will receive a message saying "1 row inserted."
/
INSERT INTO XX_STU_DETAILS VALUES(2,'B','EEE','03-SEP-2022','7777777777','DEF@GMAIL.COM');
/
INSERT INTO XX_STU_DETAILS VALUES(3,'C','ECE','03-SEP-22',8888888888,'XYZ@GMAIL.COM');
/
INSERT INTO XX_STU_DETAILS VALUES(12345,'D','MECH',SYSDATE,6666666666,'A@GAMIL.COM');
/
COMMIT; --TO MAKE THE CHANGES PERMANENTLY STORED DATA INTO DATABASE.
/
SELECT *
FROM XX_STU_DETAILS;
/
--'A' --If you are inserting any character data using single quote.
its treat as character only.
/
CREATE TABLE XX_INS_EMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(20),
JOB VARCHAR2(10),
HIREDATE DATE,
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER
);
/
DESC XX_INS_EMP;
/
INSERT INTO TABLENAME VALUES(VAL1,VAL2,......,VALN);
/
INSERT INTO XX_INS_EMP VALUES(1,'A','HR',SYSDATE,1000,35,10);
/
SELECT * FROM XX_INS_EMP;
/
INSERT INTO XX_INS_EMP VALUES(2,'B','SALES','29-JUL-2022',2000,25,20);
/
SELECT * FROM XX_INS_EMP;
/
INSERT INTO XX_INS_EMP VALUES(3,'C','MARKETING',SYSDATE,3000,45,10);
/
COMMIT; --TCL
/
SELECT * FROM XX_INS_EMP;
/
INSERT command supports specific columns.
******************************************
SYNTAX:
*******
INSERT INTO <TABLENAME> (<COLUMN1>,<COLUMN2>,<COLUMNN>) VALUES (<VALUE1>,<VALUE2>,<VALUEN>);
EX:
***
/
INSERT INTO XX_STU_DETAILS(SNO,SNAME,BRANCH) VALUES(5,'F','IT');
/
INSERT INTO XX_STU_DETAILS(SNO,JDATE) VALUES(6,'03-SEP-22');
/
COMMIT;
/
SELECT * FROM XX_STU_DETAILS;
/
INSERT INTO TABLENAME(COL1,COL2,...,COLN) VALUES(VAL1,VAL2,.....,VALN);
/
INSERT INTO XX_INS_EMP(EMPNO,SAL,DEPTNO) VALUES(4,4000,30);
/
SELECT * FROM XX_INS_EMP;
/
EMPNO ENAME HIREDATE SAL
/
INSERT INTO XX_INS_EMP(EMPNO,ENAME,HIREDATE,SAL) VALUES(5,'D',SYSDATE,5000);
/
COMMIT;
/
SELECT * FROM XX_INS_EMP;
/
CREATE TABLE XX_PERSON
(
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(20),
ADRESS VARCHAR2(20),
MOBILE NUMBER
);
/
INSERT INTO XX_PERSON(FIRST_NAME,LAST_NAME)VALUES('MOHAN','REDDY');
/
INSERT INTO XX_PERSON(FIRST_NAME,LAST_NAME,MOBILE) VALUES('RAJA','RANI',9876666);
/
COMMIT;
/
SELECT *
FROM XX_PERSON;
/
--INSERTING RECORDS USING RECORDS OF ANOTHER TABLE:
***************************************************
CREATE TABLE XX_EMP_TEST100
(
EMPNO NUMBER(10),
ENAME VARCHAR2(20),
JOB VARCHAR2(10),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(10),
COMM NUMBER(10),
DEPTNO NUMBER(2)
);
--TABLE CREATED
/
SELECT * FROM XX_EMP_TEST100;
/
SYNTAX:
*******
INSERT INTO <NEWTABLENAME> SELECT * FROM <EXISTINGTABLENAME OR OLD TABLENAME>
/
INSERT INTO <NEWTABLENANME> SELECT * FROM <EXISTINGTN>
/
INSERT INTO XX_EMP_TEST100 SELECT * FROM EMP; --ALL COLUMNS DATA WILL BE INSERTED
/
COMMIT;
--to check the data existing table records are inserted or not.
/
SELECT * FROM XX_EMP_TEST100;
/
CREATE TABLE XX_EMP_TEST200
(
EMPNO NUMBER(10),
ENAME VARCHAR2(20),
JOB VARCHAR2(10),
HIREDATE DATE,
SAL NUMBER(10),
DEPTNO NUMBER(2)
);
/
SELECT *
FROM XX_EMP_TEST200;
/
-- TO INSERT SPECIFIC COLUMN DATA INTO NEWTABLE FROM EXISTING TABLE
/
INSERT INTO XX_EMP_TEST200(EMPNO,ENAME,JOB,HIREDATE,SAL,DEPTNO) SELECT EMPNO,ENAME,JOB,HIREDATE,SAL,DEPTNO FROM EMP;
INSERT INTO XX_EMP_TEST200(EMPNO,ENAME,SAL,DEPTNO) SELECT EMPNO,ENAME,SAL,DEPTNO FROM EMP; --SPECIFIC COLUMNS DATA WILL BE INSERTED
/
CREATE TABLE XX_INS_EMP_TEMP
(
EMPNO NUMBER(4),
ENAME VARCHAR2(20),
JOB VARCHAR2(20),
HIREDATE DATE,
SAL NUMBER,
COMM NUMBER,
DEPTNO NUMBER
);
/
SELECT * FROM XX_INS_EMP_TEMP;
/
INSERT INTO NEWTABLENAME SELECT * FROM EXISTING TABLENAME;
/
INSERT INTO XX_INS_EMP_TEMP SELECT * FROM XX_INS_EMP;
/
COMMIT;
/
SELECT * FROM XX_INS_EMP_TEMP;
/
TRUNCATE TABLE XX_INS_EMP_TEMP;
/
EMPNO ENAME JOB SAL DEPTNO
/
INSERT INTO NEWTABLENAME(COL1,COL2,COL3,...,COLN) SELECT COL1,COL2,COL3,...,COLN FROM EXISTINGTABLENAME;
/
INSERT INTO XX_INS_EMP_TEMP(EMPNO,ENAME,JOB,SAL,DEPTNO) SELECT EMPNO,ENAME,JOB,SAL,DEPTNO FROM XX_INS_EMP;
/
SELECT * FROM XX_INS_EMP_TEMP;
/
--I don't want create a table and i don't want insert the data.I want to copy the table data and structure from another table.
--I want to create a new table which is copied from existing table.
--i WANT TO CREATE A TABLE AND COPY THE DATA FROM ANOTHER TABLE WHILE EXECUTING A SINGLE QUERY?
SYNTAX:
*******
CREATE TABLE NEWTABLENAME
AS
SELECT *
FROM OLDTABLENANE OR EXISTING TABLENAME;
/
CREATE TABLE XX_EMP_TD
AS
SELECT *
FROM EMP;
/
SELECT * FROM XX_EMP_TD;
/
CREATE TABLE XX_EMP_TD1
AS
SELECT *
FROM EMP
WHERE 1=1;
/
SELECT * FROM XX_EMP_TD1;
/
--I WANT TO COPY THE STRUCTURE OF THE TABLE WITHOUT DATA WHILE EXECUTING A QUERY.
CREATE TABLE XX_EMP_TS
AS
SELECT *
FROM EMP
WHERE 1=2;
/
SELECT *
FROM TABLE XX_EMP_TS;
/
DESC XX_EMP_TS;
/
NULL:
*****
-->It is used to represent empty value into table column.
-->It supports with all types of data.
-->NULL values represent missing or unknown data.
-->NULL values are not an integer,a character,or any other specific data type.
-->NULL is not same as the numerical value '0'
Using NULL:
***********
CREATE TABLE XX_STD10
(
SNO NUMBER,
SNAME VARCHAR2(20),
FEE NUMBER
);
/
INSERT INTO XX_STD10 VALUES (1,'A',100); --all columns data
/
INSERT INTO XX_STD10(SNAME,FEE) VALUES ('MOHAN',2000); --specific columns
/
INSERT INTO XX_STD10 VALUES(NULL,'RAJA',3000); --null keyword
/
INSERT INTO XX_STD10 VALUES (3,'',200); --single quote treat as also null
/
COMMIT;
/
UPDATE:
*******
-->it is used to change the existing information in database.
-->It is used to modify the existing table data.
SYNTAX:
*******
UPDATE <TABLENAME>
SET <COLUMN1>=<VALUE1>
[<COLUMN2>=<VALUE2>,....,<COLUMNN>=<VALUEN> WHERE <CONDITON>]
-->Specifying condition is not mandatory and if condition is not satisfied then system will consder all records in the table for update.
EX:
***
UPDATE XX_STU_DETAILS
SET EMAIL='ABC@GMAIL.COM'; --SINGLE COLUMN UPDATE WITHOUT CONDITION
/
--MULTIPLE COLUMNS UPDATE WITHOUT CONDITION
UPDATE XX_STU_DETAILS
SET EMAIL='DEF@GMAIL.COM',
MOBILE=2222222222,
JDATE=SYSDATE;
/
-->We didn't specify any condition in the above statement will update the entire table.
-->After updating the table now let's see the difference selecting the details of student.
/
SELECT * FROM XX_STU_DETAILS;
/
UPDATNG TABLE WITH WHERE CLAUSE:
**********************************
CREATE TABLE XX_EMP10
AS
SELECT *
FROM EMP;
/
--BEFORE UPDATE:
SELECT EMPNO,SAL FROM XX_EMP10 WHERE EMPNO=7839;
/
UPDATE XX_EMP10
SET SAL=SAL+2000
WHERE EMPNO=7839;
/
-->The above statement will update only the employee records whose EMPNO=7839;
AFTER UPDATE:
*************
SELECT EMPNO,SAL FROM XX_EMP10 WHERE EMPNO=7839;
/
CREATE TABLE XX_DEPT10
AS
SELECT *
FROM DEPT;
/
--BEFORE UPDATE
SELECT *
FROM XX_DEPT10
WHERE DEPTNO=10;
/
UPDATE XX_DEPT10
SET LOC='PUNE'
WHERE DEPTNO=10;
/
--AFTER UPDATE
SELECT *
FROM XX_DEPT10
WHERE DEPTNO=10;
/
create table employee1 as select * from emp;
/
update employee1
set comm=nvl(comm+500,500)
where (comm is not null or comm is null);
/
UPDATING MULTIPLE COLUMNS DATA:
*******************************
BEFORE UPDATE:
**************
SELECT ENAME,JOB,COMM
FROM XX_EMP10
WHERE EMPNO=7839;
/
UPDATE XX_EMP10
SET ENAME='KING1',
JOB='MANAGER',
COMM=6000
WHERE EMPNO=7839;
/
--after update
SELECT ENAME,JOB,COMM
FROM XX_EMP10
WHERE EMPNO=7839;
/
CREATE TABLE EMP1 AS SELECT * FROM EMP;
/
UPDATE EMP1
SET ENAME=(CASE WHEN EMPNO=7839 THEN 'KING1'
WHEN EMPNO=7782 THEN 'CLARK1'
END)
WHERE EMPNO IN (7839,7782);
/
--SINGLE COLUMN
UPDATE XX_INS_EMP
SET COMM=1000;
/
--MULTIPLE COLUMNS
UPDATE XX_INS_EMP
SET COMM=1000,
SAL=3000,
DEPTNO=30;
/
SELECT * FROM XX_INS_EMP;
/
ROLLBACK;
/
UPDATE XX_INS_EMP
SET COMM=100
WHERE EMPNO=4;
/
UPDATE XX_INS_EMP
SET JOB='HR',
HIREDATE='30-JUL-2022'
WHERE EMPNO=4;
/
UPDATE XX_INS_EMP
SET ENAME='A1'
WHERE EMPNO IN (1,2,3);
/
UPDATE XX_INS_EMP
SET ENAME=CASE WHEN EMPNO=1 THEN 'A1'
WHEN EMPNO=2 THEN 'B1'
WHEN EMPNO=3 THEN 'C1'
END;
/
CREATE TABLE STUDENT_DETAILS
(
SNO NUMBER,
SNAME VARCHAR2(20),
BRANCH VARCHAR2(20),
FEE NUMBER,
JDATE DATE
);
/
INSERT INTO STUDENT_DETAILS VALUES(1,'A','CSE',1000,SYSDATE);
/
INSERT INTO STUDENT_DETAILS VALUES(2,'B','EEE',1000,'27-NOV-22');
/
INSERT INTO STUDENT_DETAILS VALUES(3,'C','CSE',2000,SYSDATE);
/
INSERT INTO STUDENT_DETAILS VALUES(4,'D','EEE',800,'27-NOV-22');
/
INSERT INTO STUDENT_DETAILS VALUES(5,'E','MECH',1200,SYSDATE);
/
INSERT INTO STUDENT_DETAILS VALUES(6,'F','CSE',NULL,SYSDATE);
/
INSERT INTO STUDENT_DETAILS VALUES(7,'G','',2000,SYSDATE);
/
INSERT INTO STUDENT_DETAILS VALUES(8,'H','',3000,SYSDATE);
/
UPDATE STUDENT_DETAILS
SET FEE=DECODE(BRANCH,'CSE',5000,'ECE',3000,'EEE',2500,'MECH',2000,1000)
/
SELECT *
FROM STUDENT_DETAILS;
/
ROLLBACK;
/
UPDATE STUDENT_DETAILS
SET FEE=CASE WHEN BRANCH='CSE' THEN '5000'
WHEN BRANCH='ECE' THEN '3000'
WHEN BRANCH='EEE' THEN '2500'
WHEN BRANCH='MECH' THEN '2000'
ELSE '1000'
END;
/
SELECT * FROM STUDENT_DETAILS;
/
DELETE:
*******
-->It is used to remove the rows from table.
-->It is used to delete the data in a table.
SYNTAX:
*******
DELETE FROM <TABLENAME>
[WHERE <CONDITION>];
DELETE FROM XX_STU_DETAILS;
DELETE FROM XX_STU_DETAILS WHERE SNO=1;
DELETE FROM XX_STU_DETAILS WHERE SNO IN (6,7,8);
DIFFERENCES BETWEEN TRUNCATE AND DELETE:
========================================
TRUNCATE (DELETE+COMMIT) DELETE
*******************************************************************************************
DDL COMMAND DML COMMAND
ROLLBACK IS NOT APPLICABLE ROLLBACK IS APPLICABLE
IMPLCIT COMMIT WILL BE FIRE EXPLICIT COMMIT WILL BE FIRE
WHERE CLAUSE IS NOT APPLICABLE WHERE CLAUSE IS APPLICABLE
ALL ROWS OF THE TABLE CAN BE TRUNCATED ALL ROWS OF THE TABLE CAN BE DELETED.
SINGLE ROW OF THE TABLE CAN NOT BE TRUNCATED SINGLE ROW OF THE TABLE CAN BE DELETED
MULTIPLE ROWS OF THE TABLE CAN NOT BE TRUNCATED MULTIPLE ROWS OF THE TABLE CAN BE DELETED.
PERFORMANCE WILL BE FASTER PERFORMANCE WILL BE SLOWER
DDL DML
***********************************************************************************************
1)DDL statements are used for defining and DML statements are used to manage the data
managing data structures like table,view, stored into inside the database.
synonym,sequence etc.
2)DDL statements work on the whole object. DML statements are work on the selected data of table.
No where condition can be used along with "WHERE" condition can be used to filter and work on
DDL statements. selected dataset.
3)All the DDL statements are committed All changes done by DML's need explicit commit or rollback
automatically at end of statement execution. to make changes permanent or revertback.
No comments:
Post a Comment