SQL & Database Testing Interview Q&A

📌 SQL Foundation (SELECT & Filters)

1. Which SQL command is used to retrieve data from a database?

English: The SELECT statement is used to fetch data from one or more tables.
Hinglish: Database se data nikalne ke liye SELECT command use karte hain.
Example: SELECT * FROM Customers;

2. Which SQL command is used to add new rows of data to a table?

English: The INSERT INTO statement is used to add new records.
Hinglish: Table mein naya data daalne ke liye INSERT INTO use karte hain.
Example: INSERT INTO Products (Name, Price) VALUES ('Laptop', 1200);

3. Which SQL command is used to modify existing data in a table?

English: The UPDATE statement is used to modify the existing records.
Hinglish: Table mein maujood data ko badalne ke liye UPDATE use karte hain.
Example: UPDATE Customers SET City = 'New York' WHERE CustomerID = 1;

4. Which SQL command is used to delete rows of data from a table?

English: The DELETE FROM statement is used to remove existing records.
Hinglish: Table se data ki rows mitane ke liye DELETE FROM use karte hain.
Example: DELETE FROM Orders WHERE OrderID = 101;

5. What is the difference between DELETE and TRUNCATE?

English: DELETE is a DML command that removes rows one by one and can be rolled back. TRUNCATE is a DDL command that removes all rows by deallocating pages, is faster, but cannot be rolled back easily.
Hinglish: DELETE row-by-row data hatata hai aur undo ho sakta hai. TRUNCATE pura table turant khali kar deta hai aur fast hota hai par wapas nahi aa sakta.

6. What is the difference between DELETE and DROP?

English: DELETE removes rows but keeps the table structure; DROP removes the entire table structure along with its data.
Hinglish: DELETE data hatata hai par table rehta hai; DROP table aur uske data dono ko khatam kar deta hai.

7. What is a Primary Key?

English: A unique identifier for each record in a table. It cannot contain NULL values.
Hinglish: Table ki har row ki ek unique ID jo kabhi khali (NULL) nahi ho sakti.

8. What is a Foreign Key?

English: A field that links two tables together. it is a primary key in another table.
Hinglish: Do tables ko jodne waali key jo doosri table mein primary key hoti hai.

9. What is the purpose of the DISTINCT keyword?

English: It is used to return only unique (different) values from a column, removing duplicates from the results.
Hinglish: Query results mein se duplicates hata kar sirf unique values dikhane ke liye.

10. Explain the 'LIKE' operator and wildcards (% and _).

English: Used for pattern matching. % represents zero or more characters, while _ represents exactly one character.
Hinglish: Text patterns dhoondhne ke liye. % matlab kai characters aur _ matlab sirf ek character.

11. What is the BETWEEN operator used for?

English: Selects values within a specific range (inclusive).
Hinglish: Do limits ke beech ki values ko select karne ke liye. Example: WHERE Price BETWEEN 10 AND 20;

📌 Joins & Relationships

12. What is an INNER JOIN?

English: Returns records that have matching values in both tables.
Hinglish: Sirf matching rows dikhata hai jo dono tables mein common hon.

13. What is a LEFT (OUTER) JOIN?

English: Returns all records from the left table, and the matched records from the right table.
Hinglish: Left table ka poora data dikhaega, bhale hi right table mein match na ho.

14. What is a RIGHT (OUTER) JOIN?

English: Returns all records from the right table, and the matched records from the left table.
Hinglish: Right table ka poora data dikhaega, bhale hi left table mein match na ho.

15. What is a FULL (OUTER) JOIN?

English: Returns all records when there is a match in either left or right table.
Hinglish: Dono tables ka poora data dikhaega jahan bhi match mile.

16. What is a SELF JOIN?

English: A regular join, but the table is joined with itself. Useful for hierarchy data.
Hinglish: Ek hi table ko apne aap se jodna (e.g., manager and employee relationship).

17. What is a CROSS JOIN?

English: Produces a Cartesian product (every row of table A with every row of table B).
Hinglish: Ek table ki har row ko doosri table ki har row se multiply/join karna.

📌 Aggregates & Grouping

18. Name the common SQL Aggregate Functions.

English: SUM (total), AVG (average), COUNT (number of rows), MIN (lowest), MAX (highest).
Hinglish: Calculations ke functions: SUM, AVG, COUNT, MIN, MAX.

19. What is the purpose of GROUP BY?

English: Groups rows that have the same values into summary rows.
Hinglish: Ek jaise data wali rows ko ek group mein ikatha karne ke liye.

20. Difference between WHERE and HAVING clause?

English: WHERE filters rows before grouping. HAVING filters groups after the GROUP BY clause is applied.
Hinglish: WHERE normal records pe chalta hai, aur HAVING tab jab results group ho chuke hon.

📌 Advanced SQL & DB Testing

21. What is a Subquery?

English: A query nested inside another SQL query (e.g., in WHERE or FROM).
Hinglish: Ek query ke andar doosri query.

22. Describe ACID properties in a Database.

English: Atomicity (all or nothing), Consistency (data integrity), Isolation (independent), Durability (permanent).
Hinglish: Database transactions ke 4 main rules: Atomicity, Consistency, Isolation, Durability.

23. What is an Index and why is it used?

English: A data structure used to speed up the retrieval of data from a database table.
Hinglish: Database se data jaldi dhoondhne ke liye banaya gaya pointer.

24. What is Database Normalization?

English: Organizing data to avoid redundancy/duplicates (1NF, 2NF, 3NF).
Hinglish: Tables ko aise design karna taaki data faltu repeats na ho (Duplicates kam karna).

25. Difference between View and Materialized View?

English: View: Virtual, runs every time. Materialized View: Stores result physically for faster access.
Hinglish: View sirf save ki gayi query hai; Materialized view us query ka data bhi store karta hai taaki processing fast ho.

26. What are Stored Procedures?

English: Pre-compiled SQL code that can be reused and called with parameters.
Hinglish: Save kiya hua SQL code jise baar-baar run kiya ja sakta hai.

27. What is Database Trigger?

English: A piece of code that automatically executes on a specific event like INSERT or DELETE.
Hinglish: Table mein kuch hone par (जैसे insert) apne aap piche chalne wala code.

28. Difference between UNION and UNION ALL?

English: UNION: Combines results and removes duplicates. UNION ALL: Combines all results including duplicates.
Hinglish: UNION unique results deta hai, UNION ALL sab kuch (duplicates samet).

29. What is COMMIT and ROLLBACK?

English: COMMIT: Saves changes permanently. ROLLBACK: Undoes changes to the last save point.
Hinglish: COMMIT changes ko pakka (save) karta hai, ROLLBACK galti hone par purane state pe wapas le jata hai.

30. What is a Deadlock in Databases?

English: A situation where two transactions wait for each other to release locks forever.
Hinglish: Jab do kaam ek doosre ka rasta rok kar hamesha ke liye atak jayein.

31. What is Query Optimization?

English: Improving the performance of a query by using indexes, avoiding SELECT *, and better joins.
Hinglish: Query ko fast banane ka tareeka (jaise index use karna).

32. What is SQL Injection?

English: A security vulnerability where attackers insert malicious SQL code into input fields.
Hinglish: Hacker dwara galat SQL code daal kar database hack karne ki koshish.

33. How to prevent SQL Injection?

English: Use Prepared Statements (Parameterized Queries) and proper input validation.
Hinglish: Parameterized queries aur sahi input validation use karke.

34. What is 'Data Migration Testing'?

English: Verifying that data remains intact while moving from an old system/database to a new one.
Hinglish: Ek DB se doosre mein data shift karte waqt ye check karna ki koi galti ya loss toh nahi hua.

35. What is 'Dirty Read' in Databases?

English: When a transaction reads data that has been modified by another transaction but not yet committed.
Hinglish: Aisi information padhna jo badal toh gayi hai par abhi tak DB mein pakki save (commit) nahi huwi.

36. How to find the Nth highest salary? (Restored)

English: Using subqueries or DENSE_RANK(). Example: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk FROM Emp) WHERE rnk = N;
Hinglish: DENSE_RANK() use karke har salary ko number do aur fir N number wala utha lo.

37. What are CTEs (Common Table Expressions)? (Restored)

English: A temporary named result set (using WITH clause) that makes complex queries easier to read and maintain.
Hinglish: Ek temporary result set jo lambi queries ko saaf suthra aur asaan banane ke liye use hota hai.

38. Explain ROW_NUMBER() vs RANK() vs DENSE_RANK(). (Restored)

English: ROW_NUMBER: Unique index. RANK: Skips numbers on ties. DENSE_RANK: No skipping on ties.
Hinglish: Ranking dene ke 3 tareeke: ROW_NUMBER saaf ginti deta hai, RANK ties pe numbers kudata hai, DENSE_RANK gap nahi chhodta.

📌 Database Testing Scenarios

39. How do you test a Database Migration?

English: Compare row counts, check data integrity, verify schemas, and test performance in the new environment.
Hinglish: Dono systems mein row count aur data ki quality match karke check karna.

40. What is ETL Testing?

English: Extract, Transform, Load testing. Ensuring data is correctly pulled, changed as required, and saved in destination.
Hinglish: Data ko shahi tareeke se nikalne, badalne aur save karne ki testing.

41. What is Negative Testing in Databases?

English: Entering invalid data (e.g., character in numeric field) to see if DB constraints prevent it.
Hinglish: Galat data daal kar check karna ki DB usko reject karta hai ya nahi.

42. What is a "Constraint" in SQL?

English: Rules applied to columns (like NOT NULL, UNIQUE, CHECK).
Hinglish: Columns pe lagaye gaye rules taaki galat data na aaye.

43. How to check for NULL values in SQL?

English: Use IS NULL or IS NOT NULL instead of = NULL.
Hinglish: Khali values dhoondhne ke liye IS NULL use hota hai.

44. What is the difference between a Table and a View?

English: Table stores actual data; View is a virtual table that shows data from one or more tables.
Hinglish: Table mein data physically hota hai, View sirf ek 'khidki' (virtual table) hai.

45. What is the purpose of the COALESCE function?

English: Returns the first non-null value in a list. Great for handling default values.
Hinglish: List mein se pehli 'non-null' value nikaalne ke liye.

46. How to find duplicate records in a table?

English: Use GROUP BY on all columns and HAVING COUNT(*) > 1.
Hinglish: Group by karke jin rows ka count 1 se zyada hai, wo duplicates hain.

47. What is an Auto-Increment field?

English: Automatically generates a unique numeric value for new rows (like IDENTITY in SQL Server).
Hinglish: Nayi row aate hi ID apne aap 1, 2, 3... badhne wala system.

48. What is the difference between Character and Varchar?

English: CHAR is fixed length; VARCHAR is variable length (saves space).
Hinglish: CHAR ki length fix hoti hai, VARCHAR jitna text ho utni hi jagah leta hai.

49. What is a Composite Key?

English: A primary key consisting of two or more columns.
Hinglish: Jab do ya zyada columns milkar ek unique ID banayein.

50. How to delete all records but keep the table structure?

English: Use TRUNCATE TABLE tablename;
Hinglish: Table structure bachate hue saara data saaf karne ke liye TRUNCATE use karein.

51. What is the default port for MySQL and PostgreSQL?

English: MySQL: 3306, PostgreSQL: 5432.
Hinglish: MySQL 3306 use karta hai aur PostgreSQL 5432.

52. What is Data Integrity?

English: Ensures that data remains accurate and consistent throughout its lifecycle.
Hinglish: Ye dhyan rakhna ki data hamesha sahi aur constant rahe, bina kisi galti ke.