Introduction to SQL
Each CRUD operation maps directly to a SQL command.
Why CRUD Matters
Every database application manages data through four fundamental operations. A record can be added, viewed, changed, or removed. These operations are so universal that they are represented by the acronym CRUD: Create, Read, Update, and Delete. Whether the application is a social media platform, a banking system, or a contact manager, CRUD provides a simple framework for understanding how data is managed.
CRUD is not a particular database product. It is a way to describe the four fundamental operations performed on data.
CRUD and SQL Commands
Each CRUD operation maps directly to a SQL command. Create uses INSERT to add data. Read uses SELECT to retrieve data. Update uses UPDATE to modify existing data. Delete uses DELETE to remove data. This mapping connects the general idea of data management with the concrete commands that databases understand and execute.
| CRUD operation | Meaning | SQL command |
|---|---|---|
| Create | Add a new record | INSERT |
| Read | Retrieve or view data | SELECT |
| Update | Modify an existing record | UPDATE |
| Delete | Remove a record | DELETE |
The direct mapping between the CRUD framework and SQL commands.
A Customer Record Through Time
Consider a customer record in an e-commerce database. When the customer first registers, the application creates a new record. When the application displays the customer's details, it reads the record. If the customer changes information, the application updates the existing record. If the record must be removed, the application deletes it. The record's lifecycle can therefore involve all four CRUD operations.
Classifying a Customer-Record Action
An e-commerce application changes an existing customer's information.
- 1Identify the data state
The customer record already exists, so this is not a Create operation.
- 2Identify the action
The application is changing existing data rather than only viewing it or removing it.
- 3Match the CRUD operation
Changing an existing record is the Update operation.
- 4Match the SQL command
The SQL command associated with Update is UPDATE.
The correct classification is Update, which maps to the SQL command UPDATE.
What do you think happens?
A user opens a page to retrieve their current bank balance. Which CRUD operation is being performed?
Reveal answer
Read
The balance is being retrieved for viewing. It is not being added, changed, or removed.
Choosing the Correct Operation
Classify a scenario by focusing on what happens to the data. If a new record is added, choose Create and INSERT. If existing data is retrieved, choose Read and SELECT. If an existing record is changed, choose Update and UPDATE. If a record is removed, choose Delete and DELETE. The subject of the scenario may vary, but the data action determines the classification.
| Scenario | CRUD operation | SQL command |
|---|---|---|
| A new product record is added | Create | INSERT |
| An existing customer record is modified | Update | UPDATE |
| An email record is removed | Delete | DELETE |
| Temperature data is retrieved | Read | SELECT |
| A new patient record is added | Create | INSERT |
Classifying common data-management scenarios by the action performed.
Classify this scenario: an application displays an existing customer's information without changing or removing it. Name the CRUD operation and its corresponding SQL command.
Hints
- Ask whether the application is adding, viewing, changing, or removing data.
- Use the CRUD-to-SQL mapping table.
Common Classification Mistakes
Common mistake Treating every interaction with a record as Update
What it looks likeA user views an existing record, but no information is changed.
Why it failsViewing or retrieving data is Read, not Update.
Do this insteadUse Read and SELECT when the application retrieves data without modifying it.
Common mistake Confusing adding a record with changing a record
What it looks likeA new product record is added to the database.
Why it failsA record that did not previously exist is being created.
Do this insteadUse Create and INSERT for a new record. Reserve Update and UPDATE for an existing record.
Common mistake Choosing Delete because data is no longer wanted by a user
What it looks likeAn email record is removed.
Why it failsThe operation must be identified by what happens to the stored data, not by the user's reason for requesting it.
Do this insteadWhen a record is removed, classify the action as Delete and map it to DELETE.
CRUD as a Complete Framework
CRUD gives you a complete way to think about the lifecycle of data. Create brings a record into the database, Read retrieves it, Update changes it, and Delete removes it. The four operations are fundamental across database applications, from small personal projects to large enterprise applications. Learning this framework helps you recognize the role of a data-management action before choosing the corresponding SQL command.
- 1CRUD stands for Create, Read, Update, and Delete.
- 2Create maps to INSERT, Read maps to SELECT, Update maps to UPDATE, and Delete maps to DELETE.
- 3CRUD describes the complete lifecycle of managing a database record.
- 4Classify a scenario by asking whether data is being added, retrieved, changed, or removed.
Key Takeaways
- CRUD is the acronym for the four fundamental database operations: Create, Read, Update, and Delete.
- The corresponding SQL commands are INSERT, SELECT, UPDATE, and DELETE.
- A record can move through a lifecycle in which it is created, read, updated, and eventually deleted.
- The correct operation is determined by what happens to the data: it is added, retrieved, changed, or removed.