How are some best practices you follow for testing a database, that should every one follow?

• Create your own queries: In order to test the operation in a database in a proper and accurate manner, first a tester must have very good knowledge of SQL and especially DML (Data Manipulation Language). Some CRUD operation will be performed with the user interface of the application, and the result will be verified using an SQL query . This is the best and most robust way to do tests in a database, especially for applications with low and medium level of complexity. However, the two prerequisites described are necessary. On the other hand, if the application is quite complex, then it may be difficult or impossible for the tester to write all the necessary SQL statements. However, for some complex queries, the tester can get help from the developer as well.
• Test each table in the database: If the tester is not good in SQL, then he or she can verify the result of the CRUD operation, by viewing the (related) tables in the database. However, in this way it can be a bit tedious and cumbersome, especially when the database and tables have lots of data.
• Obtain developer queries: This is the easiest way to test the database. Perform any CRUD operation through the graphical user interface and verify its impact by executing the SQL query that is obtained from the developer.
• One instance for each tester: Provide one instance of DB for each tester. This avoids interference between them.
• Initialize common data: Initialize common data only once for all tests. If there is data that is read only, we do not have to touch them if we make sure the tests do not modify them.

Testing in a database therefore, this method seems easy and good choice for testing the database. However, its drawback is chaos. What if the query proposed by the developer is semantically wrong or does not meet the requirement of the user correctly? In this situation, the client must report the problem and demand its correction as the best. Meanwhile, the worst case scenario is that the client may refuse to accept the request.