Manipulating Data using DML

Data Manipulation Language :
A DML statement is executed when you :

• Add new rows to a table
• Modify existing rows in a table
• Remove existing rows from a table

• A transaction consists of a collection of DML statements that form a logical unit of work.

INSERT Statement Syntax :
Add new rows to a table by using the INSERT statement :

• INSERT INTO table [(column [, column...])] VALUES (value [, value...])[,(value [,value..])];
  

• With this syntax, only multiple rows are inserted at a time.

Inserting New Rows :

• Insert a new row containing values for each column.
• List values in the default order of the columns in the table.
• Optionally, list the columns in the INSERT clause.
• Enclose character and date values in single quotation marks.
• INSERT INTO dept(deptno, dname, loc) VALUES (50, 'Public Relations',’EDMONTON’);

Inserting Rows with Null Values :
Implicit method :

• Omit the column from the column list.
• INSERT INTO dept (deptno, dname ) VALUES (60, 'Purchasing');

Explicit method :

• Specify the NULL keyword in the VALUES clause.
• INSERT INTO dept VALUES (70, 'Finance', NULL);

Multiple-Row Subqueries

• Return more than one row
• Use multiple-row comparison operators

Using the ANY Operator in Multiple-Row Subqueries :

SELECT empno, ename, job, sal FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'SALESMAN') AND job <> 'SALESMAN';

Using the ALL Operator in Multiple-Row Subqueries :

SELECT empno, ename, job, sal FROM emp WHERE sal < ALL (SELECT sal FROM emp WHERE job = 'SALESMAN') AND job <> 'SALESMAN';
Null Values in a Subquery :

SELECT emp.ename FROM emp emp WHERE emp.empno NOT IN (SELECT mgr.mgr FROM emp mgr);

• The SQL statement in the slide attempts to display all the emp who do not have any subordinates. Logically, this SQL statement should have returned 12 rows. However, the SQL statement does not return any rows. One of the values returned by the inner query is a null value, and hence the entire query returns no rows
• Here we have to use IS NOT NULL in the inner query