Concepts / DELETE: Removing Data from Tables

DELETE: Removing Data from Tables

CRUD is an acronym representing the four fundamental database operations: Create, Read, Update, and Delete.

  • Programming

Why CRUD Matters

A database application constantly manages data. It may add new data, retrieve existing data, modify stored data, or remove data that is no longer needed. These four fundamental operations are grouped under the acronym CRUD: Create, Read, Update, and Delete. CRUD provides a simple framework for understanding what a database application is doing whenever it works with data.

CRUD is not limited to one type of application. A social media platform, banking system, contact manager, e-commerce database, or other database application relies on the same four fundamental operations.

maps tomaps tomaps tomaps toCreateINSERTReadSELECTUpdateUPDATEDeleteDELETE
Which SQL command corresponds to Create, Read, Update, and Delete?

The Four Operations

CRUD operationPurposeSQL command
CreateAdds new dataINSERT
ReadRetrieves dataSELECT
UpdateModifies existing dataUPDATE
DeleteRemoves dataDELETE

Each CRUD operation maps to a specific SQL command.

The CRUD names describe the data-management goal, while the SQL command names identify the command associated with that goal. If an application needs to add a new product record, the operation is Create and the corresponding SQL command is INSERT. If it needs to retrieve temperature data, the operation is Read and the corresponding command is SELECT. Modifying an existing customer record is Update, which maps to UPDATE. Removing an email record is Delete, which maps to DELETE.

data existsdata may changedata may be removedCreateINSERTReadSELECTUpdateUPDATEDeleteDELETE
How are Create, Read, Update, and Delete connected as the complete lifecycle of managing data?

A Customer Record Through CRUD

One record, four possible operations

Trace how a customer record in an e-commerce database can move through the CRUD lifecycle.

Create: A new customer record is added to the e-commerce database. This is the Create operation, which maps to INSERT.

Read: The application retrieves the customer record when it needs to use the stored data. This is the Read operation, which maps to SELECT.

Update: The existing customer record is modified. This is the Update operation, which maps to UPDATE.

Delete: The customer record is removed when the data is no longer retained in the table. This is the Delete operation, which maps to DELETE.

The same record can experience Create, Read, Update, and Delete during its existence. Together, these operations describe its complete data-management lifecycle.

addingretrievingmodifyingremovingData taskAdd new dataCreate / INSERTRetrieve dataRead / SELECTModify existing dataUpdate / UPDATERemove dataDelete / DELETE
Given a data-management task, how do I determine whether it requires Create, Read, Update, or Delete?

What DELETE Represents

DELETE is the CRUD operation used when data is being removed from a table. In the CRUD-to-SQL mapping, Delete corresponds directly to the SQL command DELETE. The important distinction is the intended action: the application is not adding data, retrieving it, or modifying an existing record. It is removing data.

remainsDELETECustomer recordCustomer recordEmail recordEmail record
What changes in a table before and after a DELETE operation removes a row?

Removing an email record is a Delete scenario. The task is not to retrieve the email or modify its contents; the data-management goal is to remove it, so the CRUD operation is Delete and the corresponding SQL command is DELETE.

Choosing the Correct Operation

EASY

Classify each scenario as Create, Read, Update, or Delete, then identify its SQL command: retrieving a user's balance; adding a new product record; modifying an existing customer record; removing an email record; retrieving temperature data; adding a new patient record.

Hints
  • Ask whether the scenario adds, retrieves, modifies, or removes data.
  • After identifying the CRUD operation, use the CRUD-to-SQL mapping.
ScenarioCRUD operationSQL command
Retrieving a user's balanceReadSELECT
Adding a new product recordCreateINSERT
Modifying an existing customer recordUpdateUPDATE
Removing an email recordDeleteDELETE
Retrieving temperature dataReadSELECT
Adding a new patient recordCreateINSERT

Classifications for the practice scenarios.

  • Treating CRUD names and SQL command names as unrelated concepts.

    Each CRUD operation maps directly to a specific SQL command.

    Fix: Classify the task first, then apply the mapping: Create to INSERT, Read to SELECT, Update to UPDATE, and Delete to DELETE.

  • Calling a retrieval task Delete because the application is working with stored data.

    Retrieving data is the Read operation, not the Delete operation.

    Fix: Use Read and SELECT when the task retrieves data.

  • Calling a modification task Create because the application is producing a changed result.

    The record already exists; the task changes existing data.

    Fix: Use Update and UPDATE for an existing record that is being modified.

CRUD Takeaways

  1. CRUD stands for Create, Read, Update, and Delete.
  2. The four operations map to INSERT, SELECT, UPDATE, and DELETE respectively.
  3. CRUD describes the complete framework and lifecycle of managing data.
  4. Choose the operation by identifying whether the task adds, retrieves, modifies, or removes data.
  5. DELETE is the SQL command associated with removing data from tables.

Key Takeaways

  • CRUD groups the four fundamental database operations: Create, Read, Update, and Delete.
  • The matching SQL commands are INSERT, SELECT, UPDATE, and DELETE.
  • Together, CRUD operations describe how data is added, retrieved, modified, and removed throughout its lifecycle.
  • A task involving removal of data is Delete in CRUD terms and uses DELETE in SQL terms.

Related Topics

  • SQL commands
  • Database management
  • Data lifecycle