❔Interview Questions

1. What is database testing? Database testing involves checking a database works as expected by validating functionality, performance, security, etc.

2. What are some examples of test cases for database testing? CRUD operation testing, schema validation, testing triggers, stored procedures, SQL queries, joining tables, concurrency testing, etc.

3. What types of bugs can occur in databases? Data corruption, performance issues, crashes, incorrect query results, deadlocks, race conditions, constraint violations, etc.

4. What is functional testing in databases? Testing database components and features work per the specifications and requirements. Includes testing CRUD operations, queries, schema, server-side programming, etc.

5. What is non-functional testing for databases? Performance testing, security testing, reliability testing, scalability testing, failover testing, recovery testing, etc.

6. What is unit testing in database development? Testing individual database components like functions, stored procedures, triggers, etc. Helps validate pieces before integration.

7. What is integration testing for databases? Testing interactions between database components and ensuring different parts work together properly.

8. What types of structural testing apply to databases? Schema testing, referential integrity testing, distributed database testing, etc.

9. How can you setup test data for database testing? Anonymized production data copies, DB backups, custom test data generation scripts, random data generation tools, etc.

10. What is negative testing in database testing? Testing with invalid data, SQL injection attempts, missing required values, excessive size inputs, concurrent users, etc. to see how system responds.

11. What is security testing for databases? Testing database permissions, encryption, access control, firewall settings, vulnerability scanning, SQL injection, etc.

12. How can you automate database testing? Unit test frameworks like JUnit for database components. DB testing tools like Selenium and DbUnit for automated functional testing.

13. What is regression testing for databases? Re-running previous tests on a database after modifications to catch defects and validate intended changes.

14. What is concurrency testing in databases? Testing application behavior with multiple concurrent users and transactions. Identifies issues like deadlocks, race conditions, consistency errors, etc.

15. What is load testing for databases? Testing database performance under expected production-like workload. Measuring response times, resource usage, and system behavior.

16. What is stress testing for databases? Testing beyond normal usage levels to find maximum capacity. Increasing factors like users, data volume, queries, etc. until failure points.

17. What are some examples of database performance testing? SQL query performance, database server performance, caching, network utilization, etc. Identifying bottlenecks.

18. What is volume testing for databases? Testing ability to handle large amounts of data through very large database size, rows, queries, concurrent users, etc.

19. How can you optimize SQL query performance? Query tuning, indexing correctly based on usage, optimizing joins, simplifying subqueries, using profiling tools, etc.

20. What are some common database bottlenecks? CPU, memory, disk I/O, blocking queries, deadlocks, expensive joins, network, etc.

21. What are some database testing tools? JMeter, LoadRunner, HammerDB, Apache JUnit, Selenium, QAF, DbUnit, SQLImport, etc.

22. How do you monitor database issues when load testing? Database logs, OS monitoring, network sniffing, synthetic transaction monitoring, query analyzers, etc.

23. What are some warning signs of database performance issues? Slow queries, excessive lock waits, growing memory usage, deadlocks, bottle-necked resources like CPU or I/O, etc.

24. What is failover testing for databases? Testing automatic switching to standby database when primary database fails. Verifying continued availability.

25. How do you test disaster recovery for databases? Simulating different failures like storage, network, server, etc. Testing backup and restores. Testing redo logs and recovery.

26. How would you test replication in a distributed database? Disconnecting nodes and verifying data sync on reconnection. Testing data consistency across nodes.

27. What steps are important when migrating a database? Analyzing compatibility, schema and functionality validation before and after, cutover testing, maintaining access during migration, etc.

28. What is referential integrity in a database? Consistency between related data across tables, based on foreign keys and constraints. Important to test.

29. What are ACID properties in database testing? Atomicity, Consistency, Isolation, Durability. Testing transaction compliance with ACID.

30. What are the typical databases isolation levels? Dirty Read, Non-Repeatable Read, Phantom Read. Testing concurrency issues at each level.

31. What are some data integrity constraints you would test? Not Null, Unique Key, Foreign Key, Check, Primary Key, Default Value constraints.

32. What is CRUD testing? Testing database Create, Read, Update, Delete operations.

33. What are some differences between OLTP and OLAP databases? OLTP optimizes transaction processing. OLAP optimizes analytics. Different designs, usage, and testing strategies.

34. What is ETL testing? Testing data extraction, transformation, loading between databases. Verifying data completeness and accuracy.

35. What are some key areas of ETL testing? Schema validations, row counts across systems, duplicate detection, null values, transformations, etc.

36. How can you verify test database restores properly? Restoring backups and verifying data integrity. Testing logs/recovery after simulated failures.

37. How do you maintain test databases? Scripted DB setup, test data generators, wiping data between tests, restoring from backup, etc.

38. What is data masking in database testing? Altering sensitive data like PII for use in lower environments. Testing masking to verify proper anonymization.

39. How can you generate test data for databases? Production dataset copies, custom data generators, DB backups, masking techniques, etc. Represent realistic scenarios.

40. Why validate database schemas? Confirm database structure matches requirements. Identify schema defects early.

41. How do you validate test data? Schema compliance, mandatory values, domain checks, reference and foreign key validation, duplicate detection, etc.

42. How would you test a database upgrade? Run regression tests, validate data, test rollback, performance test, configure cutover plan, etc.

43. How can you optimize automation for database testing? Parameterizing tests, managing test data, integrating with CI/CD, unit testing, cross-browser testing, etc.

44. What are some challenges with database testing? Test data generation, lack of logging, complex schemas, too many queries, performance resources, etc.

45. What are some important metrics for database performance testing? Throughput, response time, CPU usage, memory, disk I/O, locks, network I/O, etc.

46. Why is tuning the test database important? Represent production environment and optimize bottlenecks affecting tests.

47. What are some best practices for database testing? Automate testing, use realistic test data, test failure paths, validate schemas, monitor thoroughly, etc.

48. How would you start database performance testing? With key queries and usage scenarios. Then expand from there.

49. When would you recommend load testing a database? Before major releases. When adding capacity, resources, or load. When performance complaints occur.

50. What are some challenges with database regression testing? Time required, test data management, validating all queries/functionality, wide range of possiblities, etc.

Here is a list of 30 basic to medium level SQL interview questions and answers:

1. What is SQL?

SQL (Structured Query Language) is a standardized programming language used for managing relational databases and performing various operations on the data in them.

2. What are the different types of SQL commands?

SQL commands can be classified into 3 categories:

• DDL (Data Definition Language) commands like CREATE, ALTER, DROP are used to define data structures.

• DML (Data Manipulation Language) commands like SELECT, INSERT, UPDATE, DELETE are used to manipulate data.

• DCL (Data Control Language) commands like GRANT, REVOKE are used to control access to data.

3. What is a database table and how is it represented?

A database table organizes data in rows and columns with defined data types. A table has a primary key to identify unique rows. Columns have constraints to validate data like NOT NULL, UNIQUE, DEFAULT.

4. What is a primary key?

A primary key is a column or set of columns in a table that uniquely identifies each row in the table. Primary key values cannot be null, must be unique and remain constant. A table can only have one primary key.

5. What is a foreign key?

A foreign key establishes a relationship between two tables by referencing the primary key of another table. Rows in a table are related to rows in another table through foreign keys.

6. What are joins in SQL?

SQL joins are used to combine rows from two or more database tables based on a related column. Different types of joins are INNER join, LEFT join, RIGHT join, FULL join.

7. What is the difference between UNION and UNION ALL?

UNION combines the results of two or more SELECT statements removing duplicates. UNION ALL combines results including duplicates.

8. What is the difference between WHERE and HAVING clause?

WHERE filters rows before aggregation. HAVING filters aggregated rows. WHERE is used with SELECT, UPDATE, DELETE statements. HAVING is only used with SELECT statement along with the GROUP BY clause.

9. What are aggregate functions in SQL?

Aggregate functions like SUM, COUNT, MIN, MAX, AVG perform operations on multiple values and return a single value. They are used with the GROUP BY clause.

10. What are subqueries in SQL?

A subquery is a query nested within another SQL query. The outer query is dependent on the subquery result. Subqueries are used with SELECT, INSERT, UPDATE, DELETE, IN, EXISTS statements.

11. How can you retrieve data from a SQL database?

The SELECT statement is used to retrieve data from one or more tables. The basic syntax is:

SELECT column1, column2 FROM table_name;

12. How can you filter results in a SELECT query?

The WHERE clause is used to filter records that meet a specified condition.

For example:

SELECT column1 FROM table WHERE condition;

13. How are NULL values handled in SQL?

NULL represents missing or unknown data. SQL provides IS NULL and IS NOT NULL operators to check for NULL values. COALESCE returns the first non-NULL value from a list.

14. What is the difference between TRUNCATE and DELETE statements?

TRUNCATE removes all table rows by deallocating data pages. DELETE removes rows based on a condition without deallocating space. TRUNCATE is faster than DELETE statement.

15. What are triggers in SQL?

Triggers are procedures automatically executed in response to insert, update, delete operations on a table. Triggers enforce business rules, data integrity and referential integrity in databases.

16. How can you insert rows into a table?

The INSERT statement is used to insert new rows into a table. The basic syntax is:

INSERT INTO table (column1, column2) VALUES (value1, value2);

17. How can you update existing records in a table?

The UPDATE statement is used to modify existing records in a table. The basic syntax is:

UPDATE table SET column1 = value1, column2 = value2 WHERE condition;

18. How can you delete records from a table?

The DELETE statement is used to delete records from a table based on a condition. The basic syntax is:

DELETE FROM table WHERE condition;

19. What are constraints in SQL?

Constraints like NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY enforce data validation rules on columns when inserting or updating records in a table. They enforce data integrity.

20. What are table aliases?

Table aliases assign temporary names to tables to shorten queries and avoid confusion when self joining or joining multiple tables. Eg:

SELECT c.name FROM customer c JOIN order o ON c.id = o.cust_id

21. What are views in SQL?

Views are virtual tables representing data from one or more tables. Views contain rows and columns retrieved by a SELECT query. Views do not store actual data.

22. What is an index and how does it make retrieval faster?

Database indexes provide faster data retrieval from tables. Indexes can be created on one or more columns to avoid full table scans. Indexes use data structures like B-trees and hashes.

23. What are transactions in SQL?

A transaction is a sequence of SQL commands that perform a logical unit of work. All commands succeed or fail as a unit. Transactions follow ACID properties for data consistency - Atomicity, Consistency, Isolation, Durability.

24. What ACID properties guarantee transaction reliability?

Atomicity - all or nothing execution Consistency - valid transaction state change Isolation - interference free execution Durability - permanent changes

25. What are normalized databases?

Normalized databases divide tables into smaller related tables without redundant data to avoid data anomalies like insert, update and delete anomalies. 1NF, 2NF, 3NF, BCNF are normal forms based on dependencies.

26. What is denormalization and when is it used?

Denormalization combines normalized related tables into fewer tables with some redundant data for performance benefits. It improves read performance in databases using ad-hoc joins avoiding expensive joins.

27. What is the difference between CHAR and VARCHAR data types?

CHAR has a fixed length while VARCHAR has variable length. CHAR pads blanks and truncates if exceeds length. VARCHAR does not pad blanks and only stores characters entered.

28. What are the different types of SQL joins?

• (INNER) JOIN: Matches rows from both tables if the join condition is met.

• LEFT (OUTER) JOIN: Returns all rows from left table and matched rows from right table.

• RIGHT (OUTER) JOIN: Returns all rows from right table and matched rows from left table.

• FULL (OUTER) JOIN: Returns all rows combined from both tables where the condition is met or not.

29. How can you optimize SQL query performance?

Using indexes, reducing table scans, optimizing joins, minimizing large transactions, avoiding unnecessary queries, optimizing subqueries, tuning and updating database statistics can improve SQL query performance.

30. What are the differences between SQL and NoSQL databases?

SQL databases are relational, use structured query language, have predefined schemas, are vertically scalable, and support transactions. NoSQL databases are non-relational, have dynamic schemas, and are horizontally scalable.