Complete SQL Testing Guide:
Queries, JOINs, Nth Salary Logic & DB Verification

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;

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);

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;

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;

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.

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.

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.

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.

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.

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.

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;

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

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

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.

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.

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.

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.

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

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

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

English: An INNER JOIN selects records that have matching values in both tables. If a row in the left table does not have a matching value in the right table, it is excluded from the result.
Hinglish: INNER JOIN dono tables mein se sirf wahi rows dikhata hai jo aapas mein match karti hon. Agar koi match nahi hai, to wo data result mein nahi aayega.
Query:

SELECT Users.Name, Orders.OrderID, Orders.Amount
FROM Users
INNER JOIN Orders ON Users.UserID = Orders.UserID;

Result Output:

+-------+---------+--------+
| Name  | OrderID | Amount |
+-------+---------+--------+
| Ram   | 101     | 500    |
| Priya | 102     | 300    |
+-------+---------+--------+

English: A LEFT JOIN returns all records from the left table (Users), and the matched records from the right table (Orders). If there is no match, the result is NULL on the right side.
Hinglish: LEFT JOIN left side waali table ka saara data dikhayega, aur right side waali table se matching data laayega. Agar match nahi hoga, to right side ke columns mein NULL dikhayega.
Query:

SELECT Users.Name, Orders.OrderID, Orders.Amount
FROM Users
LEFT JOIN Orders ON Users.UserID = Orders.UserID;

Result Output:

+-------+---------+--------+
| Name  | OrderID | Amount |
+-------+---------+--------+
| Ram   | 101     | 500    |
| John  | NULL    | NULL   | (No order matched for John)
| Priya | 102     | 300    |
+-------+---------+--------+

English: A RIGHT JOIN returns all records from the right table (Orders), and the matched records from the left table (Users). If there is no match, the result is NULL on the left side.
Hinglish: RIGHT JOIN right side waali table ka saara data dikhayega, aur left table se matching data laayega. Agar match nahi hoga, to left table ke columns mein NULL dikhayega.
Query:

SELECT Users.Name, Orders.OrderID, Orders.Amount
FROM Users
RIGHT JOIN Orders ON Users.UserID = Orders.UserID;

Result Output:

+-------+---------+--------+
| Name  | OrderID | Amount |
+-------+---------+--------+
| Ram   | 101     | 500    |
| Priya | 102     | 300    |
| NULL  | 103     | 150    | (OrderID 103 has UserID 9, which isn't in Users)
+-------+---------+--------+

English: A FULL OUTER JOIN returns all records when there is a match in either left or right table. It combines the results of both LEFT JOIN and RIGHT JOIN.
Hinglish: FULL JOIN dono tables ka poora ka poora data dikhayega. Jahan matches milenge wahan connect karega, jahan nahi milenge wahan nulls daal dega.
Query:

SELECT Users.Name, Orders.OrderID, Orders.Amount
FROM Users
FULL OUTER JOIN Orders ON Users.UserID = Orders.UserID;

Result Output:

+-------+---------+--------+
| Name  | OrderID | Amount |
+-------+---------+--------+
| Ram   | 101     | 500    |
| John  | NULL    | NULL   |
| Priya | 102     | 300    |
| NULL  | 103     | 150    |
+-------+---------+--------+

English: A SELF JOIN is a regular join, but the table is joined with itself. It is extremely useful for querying hierarchical data such as organizational charts where employees reference their managers in the same table.
Hinglish: Jab kisi table ko uski khud ki table se join kiya jaaye, to use SELF JOIN bolte hain. Jaise employee table mein manager aur employee ki data hierarchy nikalne ke liye.
Example Query (Employee & Manager):

SELECT e.Name AS Employee, m.Name AS Manager
FROM Employees e
LEFT JOIN Employees m ON e.ManagerID = m.EmployeeID;

English: A CROSS JOIN returns the Cartesian product of the two tables. Every row from the first table is combined with every row from the second table. If table A has 5 rows and table B has 4 rows, the result is 20 rows.
Hinglish: CROSS JOIN Cartesian product dikhata hai. Pehli table ki har row doosri table ki har row se multiply hoti hai. Isme ON condition nahi hoti.
QA Use Case: Test Data Generation. For example, if you have a table of Sizes (S, M, L) and a table of Colors (Red, Blue, Green), a CROSS JOIN will generate all possible product combinations for QA testing verification.

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).

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

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.

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.

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).

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

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

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.

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).

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.