1. What is a Database? A database is a collection of information in an organized form for faster and better access, storage and manipulation. It can also be defined as a collection of tables, schema, views and other database objects.
2. What is Database Testing?
- Data validity testing
- Data integrity testing
- Database performance testing
- Testing of procedures, triggers and functions
- Structural Database Testing
- Functional Database Testing
- Non-functional Database Testing
- Scope of the testing is large
- Scaled down test database
- Changes in database structure
- Complex test plans
- Good understanding of SQL
- Setting up an environment
- Run a test
- Verify test results
- Validate actual results and expected results
- Report it to the reporting head
- Field size validation
- Check constraints
- Check Stored procedures
- Check indexes in terms of verifying performance related issues
- Check the field size defined in the application is matching with that in the database
- Data driven tests through flat files (.txt, .docx)
- Data driven tests through front end objects (list, menu, etc.,)
- Data driven tests through excel sheets or database (.xlsx, .db)
- Data driven tests by keying (through keyboard)
10. What is a trigger? How do you verify if a trigger is fired or not? A Trigger is a SQL procedure that initiates an action in response to an event (Insert, Delete or Update) occurs. When a new Employee is added to an Employee_Details table, new records will be created in the relevant tables such as Employee_Payroll, Employee_Time_Sheet etc., Triggers in SQL are used to maintain the integrity of the data present in the database. By querying the common audit logs, we can verify whether a trigger is fired or not.
11. How do you verify whether a database is updated when passing the data through front end?Blackbox testers usually verify whether the data is available or not in the frontend by going through reports or some other screen where the data can be viewable. If there is no page in the front end to view the data, then there is an option to verify the data in the back end by running SQL queries.
12. What are the steps to test data loading in database testing? Steps to test data loading in database testing are as follows
- Get Source database
- Get Target database
- Compatibility between source and destination database should be checked
- Run the DTS package in the corresponding DTS package
- Compare the columns of source and target database
- Verify the number of rows of source and target database
- Verify whether the changes have been reflected on target database after updating data in the source.
- Verify null values and junk characters
13. What are the necessary things needed for writing database test cases? To write database test cases, you need to have knowledge on following First step to do this is to know the functional requirement of the application Next step is to check back end database tables, joins, cursors, triggers, stored procedures, input and output parameters.
14. How to test database manually? Testing database manually involves verifying the data entered in the front end is available in the back end or not. The same verification process applies for delete, update, insert etc.,
15. How to test procedures and triggers of a database? To test procedures and triggers of database, we need to have knowledge on input and output parameters. EXEC statement is helpful to run the procedure and examine the behavior of the tables. Let’s see how to test procedures and triggers of a database
- Open database project in solution explorer
- Go to view, Click on database schema
- Open the project folder from the schema view menu
- Right click on the object that has to be testing and click on the dialog box say Create Unit Tests
- Create a new language test project
- Choose insert the unit test or create a new test and then click OK
- Project that has to be configured will be done by clicking on the Project Configuration dialog box
- Finally, configure the project and click on OK
16. What is a View? A view is like a subset of a table which is stored logically in a database. A view is a virtual table. It contains rows and columns similar to a real table. The fields in the view are fields from one or more real tables. Views do not contain data of their own. They are used to restrict access to the database or to hide data complexity.
CREATE VIEW view_name AS SELECT column_name1, column_name2 FROM table_name WHERE CONDITION;
17. What are the advantages of Views? Some of the advantages of Views are
- Views occupy no space
- Views are used to simply retrieve the results of complicated queries that need to be executed often
- Views are used to restrict access to the database or to hide data complexity
18. What is Normalization? Normalization is the process of table design to minimize the data redundancy. There are different types of Noramalization forms in SQL.
- First Normal Form
- Second Normal Form
- Third Normal Form
- Boyce and Codd Normal Form
19. What is a Join? Join is a query, which retrieves related columns or rows from multiple tables.
20. What are the different types of joins?Types of Joins are as follows:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- OUTER JOIN
21. What is an Index? An index is used to speed up the performance of queries. It makes faster retrieval of data from the table. The index can be created on one column or a group of columns.
22. What are the different types of indexes? Different types of indexes are as follows
- B-Tree index
- Bitmap index
- Clustered index
- Covering index
- Non-unique index
- Unique index
23. What is DBMS? Database Management System is a collection of programs that enables a user to store, retrieve, update and delete information from a database.
24. Which SQL statements can be used in Database Testing? SQL commands are segregated into following types:
- DDL – Data Definition Language
- DML – Data Manipulation Language
- DQL – Data Query Language
- DCL – Data Control Language
- TCL – Transaction Control Language
25. What are the different DDL commands in SQL? DDL commands are used to define or alter the structure of the database.
- CREATE: To create databases and database objects
- ALTER: To alter existing database objects
- DROP: To drop databases and databases objects
- TRUNCATE: To remove all records from a table but not its database structure
- RENAME: To rename database objects
26. What are the different DML commands in SQL? DML commands are used for managing data present in the database.
- SELECT: To select specific data from a database
- INSERT: To insert new records into a table
- UPDATE: To update existing records
- DELETE: To delete existing records from a table
27. What are the different DCL commands in SQL? DCL commands are used to create roles, grant permission and control access to the database objects.
- GRANT: To provide user access
- DENY: To deny permissions to users
- REVOKE: To remove user access
28. What are the different TCL commands in SQL? TCL commands are used to manage the changes made by DML statements.
- COMMIT: To write and store the changes to the database
- ROLLBACK: To restore the database since the last commit
29. What is RDBMS? RDBMS stands for Relational Database Management System. RDBMS is a database management system (DBMS) that is based on the relational model. Data from relational database can be accessed using Structured Query Language (SQL)
30. What are Operators available in SQL?SQL Operator is a reserved word used primarily in an SQL statement’s WHERE clause to perform operations, such as arithmetic operations and comparisons. These are used to specify conditions in an SQL statement. There are three types of Operators.
- Arithmetic Operators
- Comparison Operators
- Logical Operators
31. What is the difference between Union and Union All command? This is one of the tricky SQL Interview Questions. Interviewer may ask you this question in another way as what are the advantages of Union All over Union. Both Union and Union All concatenate the result of two tables but the way these two queries handle duplicates are different. Union: It omits duplicate records and returns only distinct result set of two or more select statements. Union All: It returns all the rows including duplicates in the result set of different select statements. Performance wise Union All is faster than Union, Since Union All doesn’t remove duplicates. Union query checks the duplicate values which consumes some time to remove the duplicate records. Assume: Table1 has 10 records, Table2 has 10 records. Last record from both the tables are same. If you run Union query.
SELECT * FROM Table1
SELECT * FROM Table2
SELECT * FROM Table1
SELECT * FROM Table2