EXP:4 DML commands
AIM: Implementation of DML commands of SQL with suitable
examples
1.Insert
2.Update
3. Delete
Theory
Data Manipulation Language ( DML )
It provides the following commands to update the data in the database tables
1) INSERT To Insert the records into the database table
2) UPDATE To Modify the already existing record in the table
3) DELETE To Delete the already existing record in the table
Create the some table and test it
SQL> CREATE TABLE emp_test ( empno number(10),
ename varchar2(30),
salary number(10,2),
deptno number(10)
);
INSERTING RECORDS INTO THE TABLE
Syntax:
INSERT INTO <table_name> VALUES (value1, value2, value3, ….., …….., ….. );
SQL> SELECT * FROM emp_test;
no rows selected
SQL> INSERT INTO EMP_TEST VALUES(1001, 'MNRAO', 5500, 20);
SQL> SELECT * FROM EMP_TEST;
(OR)
Inserting records dynamically
Oracle provides substation variables method to insert the records dynamically
Syntax :
INSERT INTO <table_name> values( &col1, &col2, &col3, …, …., …. ) (or)
&Var1, &var2, &var3, …, …., …. )
Note : The column or variable name should be in single quotations for char and date type data
UPDATE : to modify the records in a table
Syntax :
UPDATE <table_name> SET Col1 = value, Col2=value, Col3=val
Update statement is used to update rows in existing tables which is in your
own schema or if you have update privilege on them.
For example to raise the salary by Rs.500 of employee number 104. You can
give the following statement.
Ex:
mysql> update emp set sal=sal+500 where empno = 104;
In the above statement if we did not give the where condition then all
employees salary will be raised by Rs. 500. That’s why always specify proper
WHERE condition if don’t want to update all employees.
For example We want to change the name of employee no 102 from ‘Sami’
to ‘Mohd Sami’ and to raise the salary by 10%. Then the statement will be.
Ex:
mysql> update emp set name=’Mohd Sami’,
sal=sal+(sal*10/100) where empno=102;
DELETING RECORDS FROM THE TABLE
Syntax:
DELETE FROM <table_name> ; to delete all records from the table
Ex:
mysql> Delete from emp;
it will delete all records from table
Use the DELETE statement to delete the rows from existing tables which are
in your schema or if you have DELETE privilege on them.
Mysql> Deleting particular records
Sql provides the where clause to delete particular records. It will be covered as part of oracle
clauses.
For example to delete the employee whose empno is 102.
Ex:
mysql> delete from emp where empno=102;
If you don’t mention the WHERE condition then all rows will be deleted.
Suppose we want to delete all employees whose salary is above 2000. Then
give the following DELETE statement.
Mysql> delete from emp where salary > 2000;
LAB PRACTICE:
EXP:4 DML commands
Step1: connecting to database
open terminal and enter following command
mysql -u root -p
enter password: root
Step2: creating a database
syntax:
mysql> create database dml_db;
mysql>use dml_db;
mysql>
create table dept(
deptno int(2),
dname varchar(14),
loc varchar(13),
constraint pk_dept primary key (deptno)
);
mysql>desc dept;
mysql> create table emp(empno int(4), ename varchar(10),job
varchar(9),mgr int(4), hiredate date, sal
int(7),comm int(7),deptno int(2),constraint pk_emp primary
key(empno),constraint fk_deptno foreign key (deptno) references
dept(deptno));
mysql>desc dept;
Inserting values into dept table
mysql>insert into dept (deptno, dname, loc)
values(10, 'ACCOUNTING', 'NEW YORK');
mysql> insert into dept
values(20, 'RESEARCH', 'DALLAS');
mysql>insert into dept
values(30, 'SALES', 'CHICAGO');
insert into dept
values(40, 'OPERATIONS', 'BOSTON');
Inserting data into emp table
insert into emp
values(
7839, 'KING', 'PRESIDENT', null,
'1981-11-17',
5000, null, 10
);
insert into emp
values(
7698, 'BLAKE', 'MANAGER', 7839,
'1981-5-1',
2850, null, 30
);
insert into emp
values(
7782, 'CLARK', 'MANAGER', 7839,
'1981-6-9',
2450, null, 10
);
insert into emp
values(
7566, 'JONES', 'MANAGER', 7839,
'1981-4-2',
2975, null, 20
);
insert into emp
values(
7788, 'SCOTT', 'ANALYST', 7566,
'1987-7-13',
3000, null, 20
);
insert into emp
values(
7902, 'FORD', 'ANALYST', 7566,
'1981-12-3',
3000, null, 20
);
insert into emp
values(
7369, 'SMITH', 'CLERK', 7902,
'1980-12-17',
800, null, 20
);
insert into emp
values(
7499, 'ALLEN', 'SALESMAN', 7698,
'1981-2-20',
1600, 300, 30
);
insert into emp
values(
7521, 'WARD', 'SALESMAN', 7698,
'1981-2-22',
1250, 500, 30
);
insert into emp
values(
7654, 'MARTIN', 'SALESMAN', 7698,
'1981-9-28',
1250, 1400, 30
);
insert into emp
values(
7844, 'TURNER', 'SALESMAN', 7698,
'1981-9-8',
1500, 0, 30
);
insert into emp
values(
7876, 'ADAMS', 'CLERK', 7788,
'1987-7-13',
1100, null, 20
);
insert into emp
values(
7900, 'JAMES', 'CLERK', 7698,
'1981-12-3',
950, null, 30
);
insert into emp
values(
7934, 'MILLER', 'CLERK', 7782,
'1982-1-23',
1300, null, 10
);
