Introduction to SQL
CRUD is an acronym representing the four fundamental database operations: Create, Read, Update, and Delete.
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.
Identify the data state: The customer record already exists, so this is not a Create operation.
Identify the action: The application is changing existing data rather than only viewing it or removing it.
Match the CRUD operation: Changing an existing record is the Update operation.
Match 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
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
Treating every interaction with a record as Update
Viewing or retrieving data is Read, not Update.
Fix:
Use Read and SELECT when the application retrieves data without modifying it.Confusing adding a record with changing a record
A record that did not previously exist is being created.
Fix:
Use Create and INSERT for a new record. Reserve Update and UPDATE for an existing record.Choosing Delete because data is no longer wanted by a user
The operation must be identified by what happens to the stored data, not by the user's reason for requesting it.
Fix:
When 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.
- CRUD stands for Create, Read, Update, and Delete.
- Create maps to INSERT, Read maps to SELECT, Update maps to UPDATE, and Delete maps to DELETE.
- CRUD describes the complete lifecycle of managing a database record.
- Classify 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.