1. What is SQL, and why is it used?

Answer:
SQL (Structured Query Language) is a standard language for interacting with relational databases. It is used to perform various operations like creating, reading, updating, and deleting data (CRUD). Test automation often uses SQL to validate database states, verify test results, and fetch required test data.

---

2. Write a query to fetch the second-highest salary from the Employee table.

Answer:

SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary < (SELECT MAX(Salary) FROM Employee);

---

3. What are the different types of SQL commands?

Answer:

• DDL (Data Definition Language): CREATE, ALTER, DROP
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE
• DQL (Data Query Language): SELECT
• TCL (Transaction Control Language): COMMIT, ROLLBACK
• DCL (Data Control Language): GRANT, REVOKE

---

4. Write a query to find duplicate records in a table.

Answer:

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

---

5. What is the difference between DELETE and TRUNCATE?

Answer:

• DELETE removes specific rows based on a condition and can be rolled back.
• TRUNCATE removes all rows from a table, resets auto-increment counters, and cannot be rolled back.

---

6. Write a query to get the department with the highest number of employees.

Answer:

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employee
GROUP BY DepartmentID
ORDER BY EmployeeCount DESC
LIMIT 1;

---

7. What are joins in SQL? Name the types of joins.

Answer:
Joins are used to combine rows from two or more tables based on related columns.
Types:

• INNER JOIN
• LEFT JOIN (OUTER JOIN)
• RIGHT JOIN (OUTER JOIN)
• FULL OUTER JOIN
• CROSS JOIN

---

8. Write a query to fetch records where the name starts with ‘A’.

Answer:

SELECT * 
FROM Employee
WHERE Name LIKE 'A%';

---

9. What is a primary key, and how is it different from a unique key?

Answer:

• Primary Key: Uniquely identifies each record in a table and cannot contain NULL values.
• Unique Key: Ensures all values in the column are unique, but can contain one NULL value.

---

10. Write a query to fetch employees who earn more than the average salary.

Answer:

SELECT *
FROM Employee
WHERE Salary > (SELECT AVG(Salary) FROM Employee);

---

11. What is a foreign key, and why is it important?

Answer:
A foreign key is a column in one table that establishes a relationship with the primary key of another table. It enforces referential integrity between the tables.

---

12. Write a query to get the top 3 highest salaries in the Employee table.

Answer:

SELECT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 3;

---

13. What is the difference between WHERE and HAVING clauses?

Answer:

• WHERE: Filters rows before grouping.
• HAVING: Filters groups after aggregation.

---

14. Write a query to fetch common records from two tables.

Answer:

SELECT *
FROM Table1
INNER JOIN Table2
ON Table1.id = Table2.id;

---

15. What is normalization? Explain its types.

Answer:
Normalization is the process of organizing database structures to reduce redundancy and improve data integrity.
Types:

• 1NF (First Normal Form): Removes duplicate columns.
• 2NF (Second Normal Form): Ensures all non-key attributes depend on the entire primary key.
• 3NF (Third Normal Form): Ensures non-key attributes depend only on the primary key.

---

16. Write a query to create a table with constraints (primary key, unique, and foreign key).

Answer:

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(100) UNIQUE,
    DepartmentID INT,
    FOREIGN KEY (DepartmentID) REFERENCES Department(DeptID)
);

---

17. What are indexes in SQL, and what are their types?

Answer:
Indexes improve the speed of data retrieval.
Types:

• Clustered Index
• Non-Clustered Index

---

18. Write a query to count the number of employees in each department.

Answer:

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employee
GROUP BY DepartmentID;

---

19. What is the difference between clustered and non-clustered indexes?

Answer:

• Clustered Index: Alters the table’s physical order and is faster for range-based queries.
• Non-Clustered Index: Does not change physical order; uses pointers.

---

20. Write a query to find employees who have not been assigned a department.

Answer:

SELECT *
FROM Employee
WHERE DepartmentID IS NULL;

---

21. What are aggregate functions in SQL? Give examples.

Answer:
Aggregate functions perform calculations on a group of values. Examples:

• SUM(), AVG(), COUNT(), MAX(), MIN()

---

22. Write a query to combine the results of two tables using UNION.

Answer:

SELECT column_name1, column_name2
FROM Table1
UNION
SELECT column_name1, column_name2
FROM Table2;

---

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

Answer:

• UNION: Removes duplicate records.
• UNION ALL: Includes all records, including duplicates, for faster performance.

---

24. Write a query to fetch the nth highest salary in a table.

Answer:

SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET n-1;

Replace n-1 with the desired rank minus one.

---

25. What is a self-join, and when would you use it?

Answer:
A self-join joins a table to itself. It is useful for hierarchical or comparative data, such as finding employees who report to the same manager.
Example:

SELECT A.EmployeeID, A.Name, B.Name AS ManagerName
FROM Employee A
JOIN Employee B
ON A.ManagerID = B.EmployeeID;

---

26. Write a query to get the total salary paid to employees in each department.

Answer:

SELECT DepartmentID, SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY DepartmentID;

---

27. What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

Answer:

• RANK(): Provides a rank with gaps if there are ties.
• DENSE_RANK(): Provides consecutive ranks without gaps.
• ROW_NUMBER(): Assigns a unique number to each row, even for ties.
  Example:

SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employee;

---

28. Write a query to update the salary of employees by 10% in the Employee table.

Answer:

sqlCopy codeUPDATE Employee
SET Salary = Salary * 1.10;

---

29. What are ACID properties in a database?

Answer:
ACID ensures reliable database transactions:

• Atomicity: All operations succeed or none.
• Consistency: Ensures database validity before and after a transaction.
• Isolation: Concurrent transactions do not interfere with each other.
• Durability: Data is saved permanently even after a failure.

---

30. Write a query to delete duplicate records from a table while keeping one instance.

Answer:

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name) AS RowNum
    FROM table_name
)
DELETE FROM table_name
WHERE RowNum > 1;