DELETE Query in DBMS

DELETE Query

On this page we will discuss about DELETE Query in DBMS. DELETE statement is used to delete single or multiple records present in the existing database table based on a specific condition
DELETE Query in DBMS

In  Real world scenario if the user wants to delete his Facebook account the user just click the delete option but at the backend, DELETE command is executed and the record with particular details is been deleted

DELETE statement is used to delete  single or multiple records present in the existing database table based on a specific condition

Basic Syntax for the DELETE  statement

DELETE FROM table_name
WHERE condition;

Deleting a single record

Where clause is used to provide a condition for deleting a particular record in the table

Consider the sample table EMP

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20

Delete the record of employee number  7698

delete from emp
where empno=7698;
O/P
1 ROW DELETED
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
  • A single employee present in the company with employee number 7698 will be deleted from the database table
  • The previous table we have 7 rows but here we are having only 6 records i.e  one record has been deleted from the database table.

Deleting multiple records

Delete statement can be used to delete multiple rows at a time in a table

Delete the records of all employees whose salary is greater than 2500 expect the president

delete from emp 
where sal>2500  and job!=  'PRESIDENT';
O/P
4 ROWS DELETED
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
In the previous table, we have 7 records but after the execution of this query we are left with only three records i.e all those employees who are having a salary greater than 1000 except president will be deleted.

Deleting all records

  • All the rows  present in the table will be deleted if there is no condition specified by using where clause  
  • In this type of delete, the table will become empty and no records will be present to display
delete from emp;
O/P
7 ROWS DELETED
  • All the seven rows present in the emp table  will be deleted and row-count becomes zero i.e  nothing to display.

DELETE V/S TRUNCATE

  • Both these commands delete and truncate  make a table empty but there are certain differences where a super learner must be aware of

    TRUNCATE command usage

    truncate table emp;
  • It removes all rows from a Table and makes a table empty
  • This operation cannot be rolled back (table cannot be restored) and no triggers will be fired
  • You cannot use a where clause and delete particular records you need to delete enter table data
  • Truncate is faster and does not use a match under space as delete

DELETE command usage

delete from emp;
  • Delete command is also used to remove rows from a table
  • You can delete whether only particular records by using a condition with where a clause or entire rows can be deleted without using where class
  • After performing delete operation the transactions can be rollbacked i.e that is a table can be restored after deleting 
  • To make the changes permanent in a delete operation need to commit the transaction commit or rollback

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

Delete keyword in C++

Delete Operator in C++

Delete keyword in C++ is very helpful while dynamically allocate memory. Dynamic memory allocation enables a programmer to create and delete memory as per the requirement at any point of time other result memory wastage can be reduced. The 2 keywords new and delete are used for allocation and deallocation of memory respectively.
Delete keyword in C++

Delete keyword in C++

Already learned about the new keyword in detail now will see how delete keyword  works

Delete: Delete keyword in C++ is used to erase the memory allocated dynamically memory that is created using the new keyword and give back to the heap free store

  • Memory allocated dynamically using a new keyword is not deleted automatically user explicitly needs to delete it using the delete operator
  • Delete Keyword cannot delete memories that are created statically(compiler allocated memory in the stack)

Syntax:

delete ;

Example

delete p; //delete individual object

delete []p;// delete entire array

C++ program demonstrates the usage of delete keyword

Run
#include <iostream> 
using namespace std;

int main() {
    // Allocate memory for an array of integers
    int* arr = new int[5]; // Assign values to the array
    
    for(int i = 0; i < 5; i++) {
        arr[i] = i;
    } 
    // Print the values of the array
    for(int i = 0; i < 5; i++) {
        cout << arr[i] << " ";
    }
    cout << endl;    // Deallocate the memory
    
    delete[] arr;
    
    for(int i=0; i<5; i++){
    cout << arr[i] << " ";
    }
    return 0;
}

Output

0 1 2 3 4 
1533846632 5 -1838056283 -1071475926 4

How delete works?

The new operator takes memory from freestore and allocates in the program whenever we delete the memory using  delete keyword it will return the memory of the object allocated to the free store so that the free store can  use it for another new allocation request

Difference between malloc()/free() and new/delete 

  • both malloc()/free and new/delete is used for dynamic memory management
  • The new and delete operators are having simple Syntax compare with these functions, and functions require  to perform explicit Type casting whereas new/delete  implicitly typecast based upon the data

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription

WordPress › Error

There has been a critical error on this website.

Learn more about troubleshooting WordPress.