How is database testing performed from a tester's perspective?

Database testing from a tester's perspective includes several key aspects:

• Data validation: checking the correctness and integrity of data being written and read from the database.
• CRUD operations testing: testing create, read, update, and delete operations through the application interface or directly via queries.
• Relationship testing: verifying the correctness of relationships between tables (e.g., foreign keys).
• Index and performance testing: assessing query execution speed and proper use of indexes.
• Transaction testing: checking atomicity, consistency, isolation, and durability (ACID).

Example of testing via SQL queries:

-- Check that the record was successfully added
SELECT * FROM users WHERE id = 123;

-- Check relationships
SELECT orders.id, users.name FROM orders JOIN users ON orders.user_id = users.id WHERE orders.id = 456;

Automated tests can also be used, which execute queries against the database and compare results with expected outcomes.