Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Explain the use of delete command in DBMS
Delete command is a data manipulation command which is used to remove records from a table. All records may be removed in one go, or a set of records may be deleted based on a condition.
Remove specific rows from table
To remove a specific row in the table, we need to mention where condition. Based on the condition, that particular row is deleted from the table.
The syntax is as follows −
delete from tablename where condition
For example,
Delete from employee where name=’sneha’;
Example
Given below is an example to remove specific rows from table −
create table employee(ename varchar(30),department varchar(20), age number(30), salary number(20));
insert into employee values('ram','projectmanager',40,50000);
insert into employee values('priya','assistant director',45,45000);
insert into employee values('hari','developer',46,30000);
select * from employee;Output
You will get the following output −
ram|projectmanager|40|50000 priya|assistant director|45|45000 hari|developer|46|30000
Now, use the program given below −
Example
delete from employee where ename='priya'; select * from employee;
Output
You will get the following output −
ram|projectmanager|40|50000 hari|developer|46|30000
Removing data based on condition
For example,
delete from employee where age>45;
Example
Given below is an example to delete the rows of employees whose age is greater than 45, rest will be stay in database −
create table employee(ename varchar(30),department varchar(20), age number(30), salary number(20));
insert into employee values('ram','projectmanager',40,50000);
insert into employee values('priya','assistant director',45,45000);
insert into employee values('hari','developer',46,30000);
select * from employee;Output
You will get the following output −
ram|projectmanager|40|50000 priya|assistant director|45|45000 hari|developer|46|30000
Now, use the program given below −
delete from employee where age>45; select * from employee;
Output
You will get the following output −
ram|projectmanager|40|50000 priya|assistant director|45|45000
Remove data based on the result of sub query
Delete may also be done based on the result of a subquery.
For example,
delete from employee where age in (select age from employee where age>45)
Remove all rows from a table
If we want to delete entire table we can use the following −
delete from employee; (or) delete * from employee;
To remove all rows from a table we can also use the TRUNCATE command.
The syntax is as follows −
TRUNCATE table tablename;
For example,
TRUNCATE table employee
TRUNCATE removes all rows from a table without backup.
Example
Given below is an example to remove all rows from a table −
create table employee(ename varchar(30),department varchar(20), age number(30), salary number(20));
insert into employee values('ram','projectmanager',40,50000);
insert into employee values('priya','assistant director',45,45000);
insert into employee values('hari','developer',46,30000);
delete from employee;
select * from employee;Output
You will get the following output −
no rows displayed
3K+ Views
