41. What is a self-join?
Answer: A self-join is when a table joins to itself to compare rows within the same table.
Example:
SELECT A.Name AS Employee1, B.Name AS Employee2
FROM Employees A
JOIN Employees B ON A.ManagerID = B.EmployeeID;
42. What is the difference between UNION and UNION ALL?
Answer:
-
UNION removes duplicate records.
-
UNION ALL keeps all duplicates.
Example:
— UNION
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;
— UNION ALL
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;
43. What is a materialized view?
Answer: A materialized view stores the query result physically and updates it periodically.
Example:
CREATE MATERIALIZED VIEW emp_mv AS
SELECT * FROM Employees WHERE Department = ‘Sales’;
44. What are triggers in SQL?
Answer: A trigger is a set of actions automatically invoked when certain events (INSERT, UPDATE, DELETE) happen on a table.
Example:
CREATE TRIGGER trg_after_insert
AFTER INSERT ON Employees
FOR EACH ROW
INSERT INTO AuditTable (EmployeeID, ActionType) VALUES (NEW.EmployeeID, ‘Inserted’);
45. What is the difference between DELETE and TRUNCATE?
Answer:
| DELETE | TRUNCATE |
|---|---|
| Removes specific rows with a WHERE clause | Removes all rows without a WHERE |
| Slower (logs each row) | Faster (logs minimal information) |
| Can be rolled back | Cannot rollback easily |
46. How to find duplicate rows in a table?
Answer: You can find duplicates using GROUP BY and HAVING COUNT(*) > 1.
Example:
47. How to delete duplicate rows in SQL?
Answer: Using CTE and ROW_NUMBER().
Example:
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY EmployeeID) AS rn
FROM Employees
)
DELETE FROM CTE WHERE rn > 1;
48. What is an alias in SQL?
Answer: An alias gives a temporary name to a column or table for readability.
Example:
SELECT Name AS EmployeeName FROM Employees;
SELECT E.Name, D.DepartmentName
FROM Employees E
JOIN Departments D ON E.DepartmentID = D.DepartmentID;
49. What is a stored procedure?
Answer: A stored procedure is a group of SQL statements saved and executed together to perform a task.
Example:
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT * FROM Employees;
END;
50. What is a view in SQL?
Answer: A view is a virtual table based on the result set of a query.
Example:
CREATE VIEW EmployeeView AS
SELECT Name, DepartmentID
FROM Employees
WHERE Salary > 50000;
Top 50 Basic Level SQL Interview Questions and Answers with Examples
Top 50 Advanced SQL Questions asked in Top Companies (with Answers + Examples)
1 thought on “Top 50 Intermediate SQL Interview Questions You Must Practice [With Examples]”