Introduction

Database testing is crucial for ensuring that data integrity and consistency are maintained as expected. CRUD operations—Create, Read, Update, and Delete—are fundamental to database functionality and are widely used in database testing to verify that data is being handled correctly. This lesson covers each CRUD operation, provides SQL examples, and discusses how to validate these operations to ensure data integrity.

1. CRUD Operations Overview

CRUD operations are the foundation of data manipulation in databases:

• Create: Adds new data to the database.
• Read: Retrieves data from the database.
• Update: Modifies existing data in the database.
• Delete: Removes data from the database.

2. Setting Up for Database Testing

1. Select a Database: This example uses a SQL-based database like MySQL or PostgreSQL. However, the same principles apply to any relational database.
2. Create a Test Database: For testing purposes, create a separate database to ensure that tests don’t interfere with production data.

3. CRUD Operation Examples

To illustrate each CRUD operation, we’ll use SQL statements with a sample table, users, which has columns id, name, email, and age.

A. CREATE Operation

The CREATE operation adds new records to a database.

1. SQL Statement:

   INSERT INTO users (name, email, age) VALUES ('Alice', 'alice@example.com', 30);
   

2. Validation:

   • Run a SELECT statement to confirm that the new record exists in the database.

   SELECT * FROM users WHERE email = 'alice@example.com';
   

   • Ensure the record’s data matches what was inserted.

B. READ Operation

The READ operation retrieves data from the database.

1. SQL Statement:

   SELECT * FROM users WHERE age > 25;
   

2. Validation:

   • Verify that the returned data meets the query criteria (e.g., only users older than 25 are returned).
   • Check the total count of results if necessary.

C. UPDATE Operation

The UPDATE operation modifies existing records.

1. SQL Statement:

   UPDATE users SET age = 31 WHERE name = 'Alice';
   

2. Validation:

   • Use a SELECT statement to confirm the update took effect.

   SELECT * FROM users WHERE name = 'Alice';
   

   • Verify that the age field has been updated to the correct value.

D. DELETE Operation

The DELETE operation removes records from the database.

1. SQL Statement:

   DELETE FROM users WHERE email = 'alice@example.com';
   

2. Validation:

   • Run a SELECT statement to confirm that the record no longer exists.

   SELECT * FROM users WHERE email = 'alice@example.com';
   

   • The query should return zero results if the delete was successful.

4. Summary

In this lesson, you learned the basics of database testing using CRUD operations. Each operation serves a specific function in database management:

• CREATE to add data
• READ to retrieve data
• UPDATE to modify data
• DELETE to remove data

By using these operations and validating the results, you can ensure data integrity and the proper functioning of your database.

Quiz: Database Testing Knowledge Check

1. Which SQL command is used to add a new record to a table?

   • SELECT
   • INSERT
   • UPDATE
   • DELETE

2. What operation would you use to modify an existing record?

   • CREATE
   • READ
   • UPDATE
   • DELETE

3. If you want to remove a record from a database, which command would you use?

   • SELECT
   • INSERT
   • UPDATE
   • DELETE

4. What does the READ operation correspond to in SQL?

   • INSERT
   • SELECT
   • UPDATE
   • DELETE

With this knowledge, you’re now ready to begin database testing and understand the role of CRUD operations in ensuring data integrity and functionality.