Oracle Database Data Manipulation Language (DML) Commands

In this section, we will try to explain about important database DML commands that are used by a data modeler.

Insert statements:

Insert Values into GENDER_LKP:

Insert statements are used to insert data into the table. In our example, we have used SYSDATE, an Oracle’s function, which is used to insert the date and time into the column “DTTM_STMP”.

INSERT INTO GENDER_LKP VALUES ( ‘M’, ‘MALE’, SYSDATE);
INSERT INTO GENDER_LKP VALUES (‘F’, ‘FEMALE’, SYSDATE);

Insert Values into DEPARTMENT_LKP:

INSERT INTO DEPARTMENT_LKP VALUES (10, ‘IT’, SYSDATE);
INSERT INTO DEPARTMENT_LKP VALUES (20, ‘HR’, SYSDATE);

Insert Values into EMPLOYEE_DTL:

INSERT INTO EMPLOYEE_DTL(
GNDR_CD, DPTMT_NUM, FRST_NM, LST_NM, MDLE_NM, BRTH_DT, SSN, SLRY_AMT, DTTM_STMP)
VALUES
(‘M’, 10,’Kevin’,’A’,’Schulte’,
TO_DATE(’13-OCT-1969′,’DD-MON-YYYY’),’123-45-67′, 5000,SYSDATE);
INSERT INTO EMPLOYEE_DTL(
GNDR_CD, DPTMT_NUM, FRST_NM, LST_NM, MDLE_NM, BRTH_DT, SSN, SLRY_AMT, DTTM_STMP)
VALUES
(‘F’, NULL,’Valencia’,’D’,’Schipper’,
TO_DATE(’20-APR-1973′,’DD-MON-YYYY’),’765-43-21′, 5000,SYSDATE);
INSERT INTO EMPLOYEE_DTL(
GNDR_CD, DPTMT_NUM, FRST_NM, LST_NM, MDLE_NM, BRTH_DT, SSN, SLRY_AMT, DTTM_STMP)
VALUES
(‘M’, 10,’Chris’,’A’,’HERIER’,
TO_DATE(’10-JUN-1963′,’DD-MON-YYYY’),’795-82-63′, 6000,SYSDATE);

Insert Values into EMPLOYEE_DTL_COPY:

This statement will copy all records from table “EMPLOYEE_DTL” to “EMPLOYEE_DTL_COPY”.

INSERT INTO EMPLOYEE_DTL_COPY SELECT * FROM EMPLOYEE_DTL;

Update statements:

Update statements are used to update records with/without conditions. The following example uses a condition in where clause. Update statements can be committed to the database by using explicit “commit” command or it can be rolled back by using “rollback” command.

UPDATE EMPLOYEE_DTL SET DPTMT_NUM=20 WHERE EMP_DTL_ID=2;

Delete statements:

Delete statements are used to delete records with/without conditions. The following example uses some condition in where clause. Delete statements can be committed to the database by using explicit “commit” command or it can be rolled back by using “rollback” command.

DELETE FROM EMPLOYEE_DTL WHERE EMP_DTL_ID=2;

Select all rows:

Select statements are used to retrieve records from the database with/without conditions. Select statements are the most powerful commands, which you have to learn since you can avoid unnecessary “PLSQL” in many cases.

SELECT * FROM EMPLOYEE_DTL;

Select rows by using a where clause:

SELECT * FROM EMPLOYEE_DTL WHERE EMP_DTL_ID=1;

Select few columns:

SELECT EMP_DTL_ID, FRST_NM, SLRY_AMT FROM EMPLOYEE_DTL WHERE SSN=’123-45-67′;

Select records by sorting(asc = ascending and DESC = descending):

SELECT * FROM EMPLOYEE_DTL ORDER BY EMP_DTL_ID ASC;
SELECT * FROM EMPLOYEE_DTL ORDER BY EMP_DTL_ID DESC;

Select records by grouping and having clause:

SELECT DPTMT_NUM, COUNT(DPTMT_NUM) FROM EMPLOYEE_DTL GROUP BY DPTMT_NUM;
SELECT DPTMT_NUM, COUNT(DPTMT_NUM) FROM EMPLOYEE_DTL GROUP BY DPTMT_NUM HAVING COUNT(DPTMT_NUM) > 1;
SELECT DPTMT_NUM, COUNT(DPTMT_NUM) FROM EMPLOYEE_DTL GROUP BY DPTMT_NUM HAVING COUNT(DPTMT_NUM) < 2;

Select records by using a sub query:

SELECT * from EMPLOYEE_DTL WHERE DPTMT_NUM IN(SELECT DPTMT_NUM FROM DEPARTMENT_LKP WHERE DPTMT_DESC=’IT’);

Select distinct records:

SELECT DISTINCT(DPTMT_NUM) FROM EMPLOYEE_DTL;

Select count of records:

SELECT COUNT(*) FROM EMPLOYEE_DTL;