Interview Corner

A: Database testing is the process of verifying the integrity, accuracy, and correctness of data stored in the database. It covers three areas: structural testing (schema, constraints, indexes), functional testing (CRUD, triggers, stored procedures), and non-functional testing (performance, security, scalability). It is important because many bugs are invisible in the UI — data truncation, orphan records, broken constraints, and wrong calculations can only be detected by querying the database directly.

A: I have performed all three types. Structural testing — verifying table schemas, data types, constraints (PK, FK, NOT NULL, UNIQUE, CHECK), and indexes after migrations. Functional testing — verifying CRUD operations by performing UI actions and then checking the database, testing triggers and stored procedures with valid and invalid inputs. Non-functional testing — using EXPLAIN ANALYZE to identify slow queries, testing SQL injection on login forms in staging, and verifying role-based access control at the database level.

A: I use JOIN queries to verify referential integrity. For example, I LEFT JOIN the child table with the parent table and check for orphan records (WHERE parent.id IS NULL). I also verify that CASCADE DELETE and CASCADE UPDATE rules work correctly by deleting a parent record and checking if child records were handled properly. For business logic integrity, I write aggregate queries to verify that calculated totals match, like checking that order_total equals the sum of line_item amounts.

A: DELETE removes rows one by one and can be filtered with WHERE. It fires triggers and can be rolled back. TRUNCATE removes all rows at once, is faster, resets auto-increment, and cannot be easily rolled back. For test data cleanup, DELETE with a WHERE clause is safer because you can target specific test records without affecting real data. TRUNCATE is useful only when you want to completely empty a test table.

A: I test stored procedures with three categories of inputs: valid data (happy path), boundary values (edge cases), and invalid data (error handling). For each test, I check the return value AND the database state. A procedure might return "success" but leave the data in a broken state. For example, a money transfer procedure should debit one account and credit another atomically. I verify both balances changed by the correct amount, a transaction record was created, and the total money in the system remained constant.

A: For manual testing, I use DBeaver as my primary SQL client — it connects to MySQL, PostgreSQL, Oracle, and SQL Server. For automated testing, I use JDBC with Java in my TestNG framework to verify database state after UI or API actions. For performance analysis, I use EXPLAIN ANALYZE in PostgreSQL and look at execution plans. For schema migration testing, I verify changes using information_schema queries after each deployment.

A: In every sprint, I review the ER diagram changes and migration scripts as part of the story. I write database test cases alongside UI test cases in the test plan. During testing, I verify CRUD operations for every feature at the database level — not just the UI. I maintain a set of "health check" queries that I run after every deployment to verify schema integrity, constraint existence, and data consistency. For regression, I automate database checks using JDBC in our CI/CD pipeline.

A: SQL injection is an attack where a malicious user inserts SQL code into input fields to manipulate database queries. For example, entering admin' OR '1'='1 as a username can bypass login authentication. I test for it by entering common injection payloads in all input fields — login forms, search bars, URL parameters. I check if the application uses parameterized queries or prepared statements (safe) versus string concatenation (vulnerable). I always test this in a controlled staging environment, never in production.