Explore topic-wise InterviewSolutions in .

Following are the SQL statements CATEGORIES that can be used in database testing:

• DDL – Data Definition Language:
  • CREATE: Used for creating databases and their objects LIKE tables, views etc.
  • ALTER: Used for altering the properties of database objects.
  • DROP: Used for dropping the database and objects of the database.
  • TRUNCATE: Used for removing all records from a table without touching its structure.
  • RENAME: Used for renaming the database objects.
• DML – Data Manipulation Language:
  • SELECT: Used for selecting specific records or data from the database objects.
  • INSERT: Used for inserting new data or records into the database tables.
  • UPDATE: Used for updating existing records.
  • DELETE: Used for deleting existing records.
• DQL – Data Query Language:
  • SELECT queries come under data query language to query the data based on any given conditions.
• DCL – Data Control Language:
  • Used for creating ROLES, GRANTING permissions and controlling ACCESSES to the database and the objects of the database. The commands that fall under DCL are:
    • GRANT: Used for providing the user access permissions.
    • DENY: Used for denying the user permissions.
    • REVOKE: Used for removing the user access.
• TCL – Transaction Control Language:
  • COMMIT: Used for writing and storing changes to the database.
  • ROLLBACK: Used for restoring the changes to the database to the last commit.

To know if a TRIGGER is fired, we will have to enable the AUDIT LOGS of the table. You can refer to the STEPS to access logs here.

Stored Procedures are similar to user-defined functions that are invoked using Cal PROCEDURE or Execute Procedure statements. The output of these procedures is GENERALLY in the form of result sets. The stored procedures are stored in RDBMS and are READILY available for the applications. These can be tested using the following techniques:

• White box TESTING: Here, the stubs are used for invoking stored procedures and the results of those are validated against our expected results.
• Black box testing: We perform operations on the front-end view of our application in a way that the stored procedures are invoked and we validate the result by observing the changes in the view.

This depends on the front-end APPLICATION’s interface which is being used. We can test by USING the FOLLOWING ways:

• Check for the updated data in the front-end application’s view. The view should be updated with the new VALUE post successful insertion. This kind of testing constitutes black BOX testing.
• If there is no view available, then we can go to the database system, run the select query and check for the data manually.
• We can also use the WinRunner or QTP for checking the database update.

DATABASE testing can be done manually as well. This requires human effort in observing the behaviour of the application when subjected to different manual test cases drafted by the testers. This can also involve observation of the behaviour in FRONT-end applications when the backend database has changed. We can also manually check whether the record has been created, updated or deleted in the database tables post the execution of the queries.

Consider an example of a database system that KEEPS track of student details. The operations RUN on the Student tables database can be validated manually by looking at the state of the RECORDS post query execution or can be validated in how the front end interface looks like.

Retesting refers to the METHOD of executing the same test script using different input data every TIME. It helps in finding faults in the system when subjected to different inputs. The faults can be fixed and once again the system can be subjected to testing using the same SCRIPTS with the same or different inputs to check if it works fine.

Retesting is different from data-driven testing. This is because retesting is mostly a manual process that involves using a whole new data input set. Data-driven testing is an automated testing FRAMEWORK that has the test data and the scripts take the data and run it automatically.

ACID stands for Atomicity, Consistency, Isolation and Durability. Everything refers to a property of DATABASE transactions.

• Atomicity – This refers to that the transactions are ATOMIC which means that the result of the transaction can either be a success or a failure and nothing in between.
• Consistency – This property implies that the state of the database has to remain VALID after the completion of the transactions.
• Isolation – This implies that the presence of multiple transactions does not hinder each other as well as the state of the database.
• Durability – This states that post-commit of a transaction, the data should not be lost EVEN if there is a POWER failure.

We can use the statements like INSERT, UPDATE, SELECT, DELETE for manipulating the table CONTENT. Statements like ALTER TABLE, DELETE TABLE, CREATE TABLE are used for creating and MANAGING the tables.

Non-functional testing in terms of testing databases INVOLVES various testing methods like stress testing, load testing, usability testing, security testing, etc that tests and validate the non-functional business requirements. It helps in the quantification of risks with regards to the database system in terms of how the system performs under HEAVY load. It addresses the following QUESTIONS which help to mitigate the risks if detected earlier:

• Does the system break?
• Does the system slow down?
• Are there any risks to the system as the load or stress on the system increases?
• Is the system scalable and usable whenever new business requirements come in?

Non-functional testing also helps in identifying what are the minimum system requirements to RUN effectively without any limitations on hardware or software.

The keys and indexes testing in a database is a form of database testing which ANSWERS the below questions:

• Have the REQUIRED primary keys and foreign keys constraints been created on the targetted tables?
• Are the foreign key references valid?
• Is the data type of the primary key of one table and the corresponding foreign key in the other table the same?
• Are the naming conventions being followed for all indexes and keys?
• Are the LENGTHS and sizes of the required indexes and FIELDS expected?
• Are the indexes clustered or non-clustered working as expected and is according to the business requirements?

A database transaction is DEFINED as a task sequence that is to be performed on the database to achieve the REQUIRED results logically. The task can include creating, DELETING, updating the records in the database tables. The tasks upon successful completion affect the records in the database. A transaction provides 4 controls as listed below:

• COMMIT: This is used for saving all changes done utilizing the transaction. We generally use this post INSERT, UPDATE, DELETE statements.
• ROLLBACK: This is used for rolling back the transaction to the previous state before it was operated. We generally use this post INSERT, UPDATE, DELETE statements.
• SAVEPOINT: This is used for setting the point where the transaction is supposed to ROLL back.
• SET TRANSACTION: This is used for setting the name of the transaction.

Database STRESS TESTING is one of the methods used for gauging the database performance by SUBJECTING it to the heavy load that would potentially cause the system to fail at some point. This is used for identifying the breakdown points of the database application. Before performing this testing, proper planning needs to be done for avoiding the wastage of resources. This testing is also known as fatigue testing. It uses tools like LoadRunner and JMeter for accomplishing this.

Trigger testing INVOLVES testing the database triggers that answers the following questions:

1. Are the coding conventions followed while defining and coding the triggers?
2. Are the triggers executing the DML transactions successfully by satisfying all conditions?
3. Are the triggers updating the data correctly post the execution of QUERIES?
4. Is the application functionality working fine post the data updation, INSERTION or DELETION?

Data-driven testing is an automation testing framework that involves testing data stored in a spreadsheet or a table and using that stored data as input values to the test scripts. This is done to avoid WRITING INDIVIDUAL test cases of the same functionalities with different inputs. The inputs to the test scripts can be stored in XML, CSV, XLS and other data formats.

There are 4 main types of data-driven testing BASED on the source of test data inputs:

• Key-driven: Here, dynamic data is provided as inputs to the test cases by employing a keyboard to retest the application and validate the business logic.
• Flat FILES: Application retesting is done by taking input data stored in flat files like .doc or .txt.
• Front-end object: The test scripts use data from front-end objects like list, table, menu, data window etc.
• Spreadsheet/Excel: Here, the test data is taken from data stored in excel sheets or SPREADSHEETS.

Database testing is a procedure of validating data stored in a database and its RELATED functionality and objects controlling database entities like tables, stored procedures, views, triggers and functions. This testing is widely used in applications that USE databases. With MODERN technologies and advancements in software development, databases are widely used to store data which is why checking the correctness and quality of data matters. It is divided into 4 categories such as:

• Data Validity Testing: In this testing, testers NEED to know SQL queries to retrieve data from the database and validate it.
• Data Integrity Testing: Here, the testers need to validate data against various constraints and referential integrity in the database.
• Database Performance Testing: Here, the testers need to validate the performance of various triggers, indices, procedures to see how effectively the operations occur. The system also needs to execute database TRANSACTIONS efficiently. Good knowledge of database structures needs to be there while doing this.
• Testing triggers, functions and procedures present in the database to validate the logic associated with them.