Experiment No :- 2
Title : Implementation of DML commands of SQL with suitable examples
Insert table
Update table
Delete Table
2.1 Objective:
To understand the different issues involved in the design and implementation of a database
system
To understand and use data manipulation language to query, update, and manage a database
2.2 DATA MANIPULATION LANGUAGE (DML):
The Data Manipulation Language (DML) is used to retrieve, insert and modify database information.
These commands will be used by all database users during the routine operation of the database. Let's
take a brief look at the basic DML commands:
1. INSERT
2. UPDATE
3. DELETE
2.2.1 INSERT INTO:
This is used to add records into a relation. These are three type ofINSERT INTO queries which
are as
[Link] Inserting a single record
Syntax: INSERT INTO < relation/table name> (field_1,field_2……field_n)VALUES
(data_1,data_2, data_n);
Example: SQL>INSERT INTO student(sno,sname,class,address)VALUES
(1,’Ravi’,’[Link]’,’Palakol’);
[Link] Inserting a single record
Syntax: INSERT INTO < relation/table name>VALUES (data_1,data_2, data_n);
Example: SQL>INSERT INTO student VALUES (1,’Ravi’,’[Link]’,’Palakol’);
[Link] Inserting all records from another relation
Syntax: INSERT INTO relation_name_1 SELECT Field_1,field_2,field_n FROM
relation_name_2 WHERE field_x=data;
Example: SQL>INSERT INTO std SELECT sno,sname FROM student WHERE name =
‘Ramu‘;
[Link] Inserting multiple records
Syntax: INSERT INTO relation_name field_1,field_2, field_n) VALUES
(&data_1,&data_2, &data_n);
Example: SQL>INSERT INTO student (sno, sname, class,address) VALUES
(&sno,’&sname’,’&class’,’&address’);
Enter value for sno: 101 Enter value for name: Ravi Enter value for class: [Link] Enter value for
name: Palakol
2.2.2 UPDATE-SET-WHERE:
This is used to update the content of a record in a relation.
Syntax: SQL>UPDATE relation name SET Field_name1=data,field_name2=data,
WHERE field_name=data;
Example: SQL>UPDATE student SET sname = ‘kumar’ WHERE sno=1;
2.2.3 DELETE-FROM:
This is used to delete all the records of a relation but it will retain thestructure of that relation.
[Link] DELETE-FROM: This is used to delete all the records of relation.
Syntax:SQL>DELETE FROM relation_name;
Example:SQL>DELETE FROM std;
[Link] DELETE -FROM-WHERE:
This is used to delete a selected record from a relation.
Syntax: SQL>DELETE FROM relation_name WHERE condition;
Example: SQL>DELETE FROM student WHERE sno = 2;
2.2.4 TRUNCATE:
This command will remove the data permanently. But structure will not be removed.
[Link] Difference between Truncate & Delete:-
By using truncate command data will be removed permanently & will not get back where as by
using delete command data will be removed temporally & get back by using roll back
command.
By using delete command data will be removed based on the condition where as by using
truncate command there is no condition.
Truncate is a DDL command & delete is a DML command.
Syntax: TRUNCATE TABLE <Table name>
Example: TRUNCATE TABLE student;
2.2.5 To Retrieve data from one or more tables.
[Link] SELECT FROM:
To display all fields for all records.
Syntax : SELECT * FROM relation_name;
Example : SQL> select * from dept;
DEPTNO DNAME LOC
-------- ----------- ----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
[Link] SELECT FROM:
To display a set of fields for all records of relation.
Syntax: SELECT a set of fields FROM relation_name;
Example: SQL> select deptno, dname from dept;
DEPTNO DNAME
10 ACCOUNTING
20 RESEARCH
30 SALES
[Link] SELECT - FROM -WHERE:
This query is used to display a selected set of fields for aselected set of records of a relation.
Syntax: SELECT a set of fields FROM relation_name WHERE condition;
Example: SQL> select * FROM dept WHERE deptno<=20;
DEPTNO DNAME LOC
------ ----------- ------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
2.3 LAB PRACTICE ASSIGNMENT:
Create a table EMPLOYEE with following schema:
(Emp_no, E_name, E_address, E_ph_no, Dept_no, Dept_name,Job_id , Salary)
2.3.1 Write SQL queries for following question:
1. Insert aleast 5 rows in the table.
2. Display all the information of EMP table.
3. Display the record of each employee who works in department D10.
4. Update the city of Emp_no-12 with current city as Nagpur.
5. Display the details of Employee who works in department MECH.
6. Delete the email_id of employee James.
7. Display the complete record of employees working in SALES Department.