SQL ORDER by

DML Statements in SQL

DML Statements in SQL

DML stands for Data Manipulation Language used for inserting , updating and deleting data and controlling transaction in database.

Basically DML can be categorize into following statements:

  • INSERT
  • UPDATE
  • DELETE

INSERT Statement

Insert statement is used to insert data into:

  • Relational Table
  • Views of a Table
  • Partition of a Partition Table
  • An Object Table

Syntax :

SQL > INSERT  INTO  <Table_Name>(col1, col2.......)   VALUES(val1, val2......); 

Different Situation of INSERT Statement

Suppose we have Table with four column Emp_ID, Emp_Name, Gender and DOJ.

Inserting Data into all Column of a Table.

SQL > INSERT INTO  Employee  Values(101,'Subham','M','17-Sep-2015');

In the above case  Values should be provided to all columns, Order should match with  columns in table.

Inserting Data into required Columns

SQL > INSERT INTO Employees(EMP_ID,Emp_Name,DOJ) 
                     VALUES(103,'Sam','02-Jul-2015');

In the above case rest of the column will fill with Null values until the NOT NULL constraint is declared.

Inserting  NULL  values into Table

It can be done in two ways :

  • IMPLICIT : Omit the column from list.
  • EXPLICIT : Specify the NULL Keyword.
SQL > INSERT INTO Employees(EMP_ID,Emp_Name,DOJ,Gender) 
                     VALUES(103,'RAJAT',NULL,'M');

Inserting Special Values

We can store SYSDATE function and USER function.

  • SYSDATE : To store current date and time from the System clock
  • USER : To store the current user name.
SQL > INSERT INTO Employees(EMP_ID,Emp_Name,DOJ,Gender,InserBy)
                     VALUES(104,'SAURAV',SYSDATE,'M'USER);

Inserting specific Date and Time

INSERT INTO employee(EMP_ID,Emp_Name,DOJ,Gender) 
                    VALUES(111,'Dany', TO_DATE('FEB 3, 2014', 'MON DD, YYYY'),'M');

Insert by Copying rows from other Table

INSERT INTO  SALES_REP(id, name, DOJ,)
                                 SELECT  Emp_ID, Emp_Name, DOJ
                                 FROM  EMPLOYEES
                                 WHERE  Job_ID LIKE '%REP%';

Populating Data at Run-Time

We can provide data at run time by using Substitution Variable. Substitution Variable used to store values temporarily.

The value can be stored temporarily through  :

  • Single Ampersand (&) — We have to pass values each time, when statement is executed.
  • Double Ampersand (&&) — Applies for all instance until that SQL is existing.
  • DEFINE and ACCEPT Commands

& Substitution Variable

SQL > INSERT INTO Employees(EMP_ID,Emp_Name,DOJ,Fees,Gender) 
                     VALUES(103,'king',SYSDATE,&fees,'M');

&& Substitution Variable

SQL > INSERT INTO Employees(EMP_ID,Emp_Name,DOJ,Salary,Gender) 
                     VALUES(103, 'king', SYSDATE, &&Sal, 'M');

UPDATE Statement in SQL

It is used to modify the existing value in a table.

Syntax :

UPDATE table
SET column= value [, column = value, ...]
[WHERE condition];

Example :

UPDATE EMPLOYEES
SET Emp_Name = Kundan
WHERE Emp_ID = 103;

Update two columns with a sub query

UPDATE EMPLOYEES
SET job_id = (SELECT job_id FROM employees WHERE employee_id = 105),
    salary = (SELECT salary FROM employees WHERE employee_id = 105)
WHERE employee_id = 113;

DELETE  Statement in SQL

It is used to remove one or entire rows from the table.

Syntax :

 DELETE FROM table
 [WHERE condition];

Example :

DELETE FROM EMPLOYEES
WHERE EMP_Name = ‘SAM';

Note : Above query deletes whole record if you will not use where clause.

Next read about…

DDL Statements in SQL

Leave a Reply