Data Manipulation Language (DML)

DATA MANIPULATION LANGUAGE
------------------------------------------

INTRODUCTION
By data manipulation language we mean:
->The retrieval of information stored in the database.
->The insetion of new information into the database.
->The deletion of information from the database.
->The modification of data stored in the database.
Thus, it is a language that enables users to access or manipulate data as organised by the appropriate data model.
There are basically two types of DML
i>Procedural DMLs
->It requires a user to specify what data is needed and how to get it.
ii>Non-Procedural DMLs
->It requires a user to specify what data is needed without specifying how to get it.
As SQL is Non-Procedural language we will switch on to Non-Procedural DMLs as it is easy to understand and became very efficient for new users to begin with it.
The category of DML contains four basic statements:
i>select - which retrieves rows from a table.
ii>Insert - Which adds rows to a table.
iii>Update - Which modifies existing rows in a table.
iv>Delete - Which removes rows from a table.

SELECT
->To view all rows or some of the rows from a table or more than one table depending upon a userdefined criteria,this command is used.
->By default, select statement display all rows which matches the criteria but we can get the unique records by using keyword distinct with it.
syntax:
SELECT [DISTINCT ALL] FROM
WHERE
->keyword ALL is optional by default it consider ALL rows in table.
example:
1)SQL>select * from emp;
->It will display all rows of emp table including all feilds.we can customize the output by selecting the rows which are needed to us.
2)SQL>select empno,ename from emp;
->It will display all rows of emp table including empno and employee_name detail for an employee.
3)SQL>select * from emp
where
job = 'clerk';
->It will display all details of employee whose status is clerk.
4)SQL>select distinct ename from emp;
->It will display all unique name of employee if employee_name is repeated twice than it will display only ones.Thus it discards the duplicate records.

INSERT
->INSERT command is used to insert one or more rows in a table.
(There are many of syntax for insert command but one mentioned as under is the only standard way to insert multiple rows.)
syntax:-
INSERT INTO (........)
VALUES (<&Fieldname1>......<&FieldnameN>);
->If the Fieldname should be valid field for a table.
->Field having datatype char,varchar2 and Date kind of data should be written in single quota.
examples:
1)SQL>Insert into emp (empno,ename,job)
values ('1','SHREY','MANAGER');
->Above command will insert data for one record, here as data are mentioned directly, so we have made use of single quota.
2)SQL>Insert into emp (empno,job)
values (&empno,'&job');
->Here we have customize the insert command to take data for only two field that is empno and job for multiple records.
->When you don't want to type the command which is used last than simply press the slash to activate the command which is used by you last.
3)SQL>Insert into emp
values (&empno,'&ename','&job');
->Note in Above command we haven't declare the field in which data is to be entered, it is optional when we have to enter data for all fields in column.
4)SQL>Insert into emp (empno,ename,job)
values ('5','VRAJ',NULL);
->The above command will create a row and assign value 5 for empno and vraj for ename and leave the column job.
->If we doesn't want to enter value for a particular field we can just type NULL in it place during defining the INSERT command. And just press enter while entering the value.
5)SQL>Insert into emp_bkup
(select * from emp);
->The above command will copies all the rows from table emp and insert it into the table emp_bkup, provided that the structure of emp and emp_bkup is same.
->The above command is efficient to run when we want to create backup copy of our data.

UPDATE
->Update command is used to modify content of table, that to under satisfaction of particular criteria.
syntax:
UPDATE
SET =
WHERE ;
->Where Clause is optional.
->Fieldname is name of column whose contents are to be manipulated.
example:
1)SQL>Update emp
set job = 'ACCOUNTANT'
where job = 'MUNIM';
->Above sql statement will Modify Job field for employee whose status is munim, it will update the status munim with accountant.
2)SQL>Update emp
set salary = salary * 10
where job = 'PROGRAMMER';
->Above statement will increase salary of employee by 10% whose status is programmer.

DELETE
->DELETE command is used to delete one or more rows from the table.
Note:-No warnings are given before deleting rows so be careful while performing this operation.
syntax:
DELETE FROM
WHERE
;
->Table_name is name of table from which you want to delete record/s.
->Criteria is condition under which you want to delete record/s.
example:
1)SQL>Delete from emp
Where empno = 4;
->Above statement remove record of empno 4.
->Only one record is deleted.
2)SQL>Delete from emp
Where job = 'OPERATOR';
->Above statement remove record/s of those employee whose status is operator in the company.