1. Find All Employees

Question:
Retrieve all employee records.

SELECT *
FROM Employees;

---

2. Find Employees from a Specific Department

Question:
Retrieve employees who belong to the 'QA' department.

SELECT *
FROM Employees
WHERE Department = 'QA';

---

3. Find Employees with Salary Greater Than ₹50,000

SELECT *
FROM Employees
WHERE Salary > 50000;

---

4. Find Duplicate Records

Question:
Find duplicate email addresses.

SELECT Email,
       COUNT(*) AS Total
FROM Employees
GROUP BY Email
HAVING COUNT(*) > 1;

Explanation:

• GROUP BY groups identical email addresses.

• HAVING filters groups with more than one occurrence.

---

5. Delete Duplicate Records

DELETE e1
FROM Employees e1
JOIN Employees e2
ON e1.Email = e2.Email
AND e1.EmployeeID > e2.EmployeeID;

---

6. Find the Second Highest Salary

Method 1 (Using MAX)

SELECT MAX(Salary)
FROM Employees
WHERE Salary <
(
    SELECT MAX(Salary)
    FROM Employees
);

Method 2 (Using DENSE_RANK)

SELECT Salary
FROM
(
    SELECT Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) AS RankNo
    FROM Employees
) T
WHERE RankNo = 2;

---

7. Find the Third Highest Salary

SELECT Salary
FROM
(
    SELECT Salary,
           DENSE_RANK() OVER (ORDER BY Salary DESC) RankNo
    FROM Employees
) T
WHERE RankNo = 3;

---

8. Find Employees Whose Name Starts with 'A'

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

---

9. Find Employees Whose Name Ends with 'n'

SELECT *
FROM Employees
WHERE Name LIKE '%n';

---

10. Count Total Employees

SELECT COUNT(*) AS TotalEmployees
FROM Employees;

---

11. Count Employees Department-Wise

SELECT Department,
       COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

---

12. Find Maximum Salary

SELECT MAX(Salary)
FROM Employees;

---

13. Find Minimum Salary

SELECT MIN(Salary)
FROM Employees;

---

14. Find Average Salary

SELECT AVG(Salary)
FROM Employees;

---

15. Retrieve Employees in Descending Salary Order

SELECT *
FROM Employees
ORDER BY Salary DESC;

---

16. Find Employees Who Don't Have a Manager

SELECT *
FROM Employees
WHERE ManagerID IS NULL;

---

17. Difference Between INNER JOIN and LEFT JOIN

INNER JOIN

Returns only matching records.

SELECT e.Name,
       d.DepartmentName
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

LEFT JOIN

Returns all employees, even if they don't belong to any department.

SELECT e.Name,
       d.DepartmentName
FROM Employees e
LEFT JOIN Departments d
ON e.DepartmentID = d.DepartmentID;

---

18. Find Employees Without Orders

SELECT e.EmployeeID,
       e.Name
FROM Employees e
LEFT JOIN Orders o
ON e.EmployeeID = o.EmployeeID
WHERE o.OrderID IS NULL;

---

19. Difference Between DELETE, TRUNCATE, and DROP

Command	Deletes Data	Removes Table	Can Use WHERE	Rollback*
DELETE	Yes	No	Yes	Usually Yes (within a transaction)
TRUNCATE	Yes	No	No	Database-dependent
DROP	Yes	Yes	No	No

---

20. Find the Top 5 Highest Salaries

SELECT *
FROM Employees
ORDER BY Salary DESC
LIMIT 5;

For Microsoft SQL Server:

SELECT TOP 5 *
FROM Employees
ORDER BY Salary DESC;

---

Bonus Questions Frequently Asked in QA Interviews

Find NULL Values

SELECT *
FROM Employees
WHERE Email IS NULL;

---

Find Non-NULL Values

SELECT *
FROM Employees
WHERE Email IS NOT NULL;

---

Retrieve Distinct Departments

SELECT DISTINCT Department
FROM Employees;

---

Update Employee Salary

UPDATE Employees
SET Salary = 60000
WHERE EmployeeID = 101;

---

Delete an Employee

DELETE FROM Employees
WHERE EmployeeID = 101;

---

QA-Based SQL Scenario

Scenario: A user registers on an application.

Step 1: Verify the record exists.

SELECT *
FROM Users
WHERE Email = 'john@example.com';

Step 2: Verify the account is active.

SELECT Status
FROM Users
WHERE Email = 'john@example.com';

Step 3: Verify only one record exists.

SELECT COUNT(*)
FROM Users
WHERE Email = 'john@example.com';

Expected Result: The count should be 1, and the account status should be Active.

---

Interview Tips

• Practice writing SQL queries without relying on autocomplete.

• Understand the purpose of each clause (SELECT, WHERE, GROUP BY, HAVING, ORDER BY).

• Learn joins thoroughly (INNER, LEFT, RIGHT, and FULL OUTER where supported).

• Become comfortable with aggregate functions and window functions like ROW_NUMBER, RANK, and DENSE_RANK.

• Be prepared to explain why a query works, not just how to write it.

• Practice SQL using realistic datasets (Employees, Customers, Orders, Products) to simulate interview scenarios.