Top 50 SQL Interview Questions for Freshers (With Answers & Real Examples)

This article covers the SQL Interview Questions for Beginners to Advanced (Top Queries with Answers & Examples), designed for beginners and experienced professionals. It includes commonly asked SQL topics such as joins, indexes, subqueries, performance tuning, and best practices to help you crack database interviews with confidence.

These SQL interview questions are frequently asked in companies like TCS, Infosys, Wipro, Accenture, and product-based companies.

Basic :

  1. What is the difference between clustered and non-clustered index?

Answer :  Clustered index defines the physical order of data (only one per table).
Non-clustered index stores a separate structure with pointers (multiple allowed).

Real world : Clustered index on OrderId, non-clustered on CustomerId.

    2.Can a table have multiple clustered indexes?

Answer : No. A table can have only one clustered index.

   3. Difference between DELETE and TRUNCATE in production?

Answer:
 1. DELETE is row-by-row.

 2. Delete is slower than truncate.

 3. Rollback possible.

1. TRUNCATE is minimally logged.

2. Truncate is faster than delete .

3. No rollback possible in truncate.

Delete Query Syntax:

DELETE  FROM  TableName  WHERE  condition;
Delete from Employee where Emp_id = 106;

Truncate Query Syntax:

TRUNCATE TABLE TableName;
TRUNCATE TABLE Employee;

  4. Difference between CHAR and VARCHAR?

Answer:
  1. fixed length

  2. Slightly faster for fixed-size data.

  3. Data with fixed length

 1. variable length .

 2. Slightly slower due to length tracking.

 3. Data with varying length.

Example

CREATE TABLE Orders (
OrderId INT,
OrderStatus CHAR(1),
PaymentMode CHAR(2),
ShippingAddress VARCHAR(300),
Remarks VARCHAR(500) );

 5. What is normalization?

Answer:

 1. Process of reducing redundancy by dividing tables logically.

 2. Easy maintenance.

 3. Data is stored only once 

 6. What is CTE ?

Answer :

  1. CTE is a temporary result set used to simplify complex SQL queries and improve readability.

 2. Easy to use.

 3. Reusable Logic

Query : 

WITH cte_name AS (
SELECT columns
FROM table
WHERE condition)

SELECT *
FROM cte_name;

 7. What is Indexing in SQL ?

Answer :

Indexing in SQL is a performance-optimization technique that helps the database find data faster.

Real- World Example :

You have an Employee table with 1 million records, and users frequently search by EmailId.

Query :

CREATE INDEX id_employee_email
ON Employee(EmailId);

 8. Types of join in SQL ?

Answer :

1️⃣ INNER JOIN

 Returns only matching records from both tables.

SELECT e.EmpName, d.DeptName
FROM Employee e
INNER JOIN Department d
ON e.DeptId = d.DeptId;

2️⃣ LEFT JOIN 

 Returns all records from left table + matched data from right table.

SELECT e.EmpName, d.DeptName
FROM Employee e
LEFT JOIN Department d
ON e.DeptId = d.DeptId;
 

3️⃣ RIGHT JOIN 

 Returns all records from right table + matched data from left table.

SELECT e.EmpName, d.DeptName
FROM Employee e
RIGHT JOIN Department d
ON e.DeptId = d.DeptId;

4️⃣ FULL JOIN

 Returns all records from both tables, matched or not.

SELECT e.EmpName, d.DeptName
FROM Employee e
FULL JOIN Department d
ON e.DeptId = d.DeptId;

5️⃣ CROSS JOIN

 Returns Cartesian product (every row × every row).

SELECT e.EmpName, d.DeptName
FROM Employee e
CROSS JOIN Department d;

 9. How to optimize SQL query?

Answer :

 1. Use proper Indexing.

 2. Avoid Select *

 3. For Large data use Exist instead of IN.

 4. Use proper joins.

 5. Use where before group By

 6. Check Execution plan.

 10. Difference between Primary Key and Foreign Key? 

Answer :

Primary Key uniquely identifies each record in a table. It does not allow NULLs and must be unique.

Foreign Key creates a relationship between two tables by referring to the Primary Key of another table. It can have duplicates and NULLs (if not restricted).

 11. How to Insert  Identity Column in SQL?

Answer :

Yes, you can insert a 7th record into a table with an IDENTITY column. The ID will auto-increment even if there are gaps.

— Insert without specifying ID
INSERT INTO TableName (OtherColumn) VALUES (‘Value’);

If needed to explicitly insert identity:

SET IDENTITY_INSERT TableName ON;
INSERT INTO TableName (ID, OtherColumn) VALUES (7, ‘Value’);
SET IDENTITY_INSERT TableName OFF;

 12. Insert DepartmentId from Employees to Department table

Answer :

UPDATE D
SET D.DepartmentId = E.DepartmentId
FROM Department D
JOIN Employees E ON D.DepartmentName = E.DepartmentName
WHERE D.DepartmentId IS NULL;

 13. Top 1 Salary from Employee Table 

Answer :

SELECT TOP 1 Salary FROM Employees ORDER BY Salary DESC;

 14. What is User Defined Function (UDF) in SQL?

Answer :

A UDF is a SQL function you define yourself to return a single value or table. It’s useful for reusable logic in queries..

 15. Copy data from one table to another in SQL ?

Answer :

INSERT INTO NewTable (Id, Name)
SELECT Id, Name FROM OldTable;

 16. SQL Query to Get Student with Maximum Average Marks

Answer :

SELECT TOP 1 StudentId, AVG(Marks) AS AverageMarks
FROM StudentMarks
GROUP BY StudentId
ORDER BY AverageMarks DESC;

 17. Get Employees Who Earn More Than Their Department Average

Answer :

SELECT EmpName, Salary, DeptId
FROM Employee e
WHERE Salary >
( SELECT AVG(Salary)
FROM Employee
WHERE DeptId = e.DeptId );

 18. What is composite Key ?

Answer :

A Composite Key is a primary key made up of two or more columns that together uniquely identify a row in a table.

Query :

CREATE TABLE OrderItems (
OrderId INT,
ProductId INT,
Quantity INT,
CONSTRAINT PK_OrderItems
PRIMARY KEY (OrderId, ProductId)
);

 19. What is trigger in SQL ?  

Answer :

A Trigger in SQL is a special stored program that automatically executes when a specific event occurs on a table or view. Trigger fire on Insert, Update and delete 

 20. When Should You Use a Trigger in SQL?

Answer :

 1. Whenever an employee’s salary is updated, store the old and new salary for auditing.

 2. Auditing & Logging.

 3. Maintaining Data Consistency

Advanced :

21. What is an SQL INDEX?

Answer : SQL Index is a database object used to speed up data retrieval from a table. It works like an index in a book, the database engine uses the index to quickly locate the required data. Index improves query performance.

Real-World Example :  Searching employee by EmpID in a company with 1 lakh records.

SQL Query :  CREATE INDEX idx_empid ON Employees(EmpID);

22. What is a View?

Answer : A View in SQL is a virtual table that is created using a SELECT query. It does not store data physically; instead, it stores the query definition and displays data dynamically from one or more tables.

Real-World Example :  HR sees employee salary, but normal users don’t.

SQL Query : 

CREATE VIEW HR_View AS

SELECT Name, Salary FROM Employees;

23. What is a Stored Procedure?

Answer :

A Stored Procedure is a precompiled set of SQL statements stored in the database and executed as a single unit.
It is used to perform reusable business logic, improve performance, and enhance security.

Real-World Example : Monthly salary calculation, Approval workflows, .

SQL Query : 

CREATE PROCEDURE GetEmployeeById
@EmpId INT
AS
BEGIN

SELECT *
FROM Employees
WHERE EmployeeID = @EmpId;
END;

Stored Procedure with Multiple Operations : 

 
CREATE PROCEDURE InsertEmployee
@Name VARCHAR(50),
@Salary DECIMAL(10,2)
AS
BEGIN
INSERT INTO Employees
(Name, Salary)
VALUES (@Name, @Salary);
END;

24. Why We use Indexing ?

Answer : 

  1. To improve SELECT query performance
  2. To avoid Full Table Scans
  3. To speed up WHERE, JOIN, ORDER BY, GROUP BY
  4. To improve JOIN performance

25. When to Use an Indexing ?

Answer : used in:

  • WHERE

  • JOIN

  • ORDER BY

  • GROUP BY

26. Why we  use a View ?

Answer : 

  • To simplify complex queries
  • For security
  • To improve code reusability
  • To reduce developer mistakes.

27. When we  use a View ? 

Answer: 

  1. You want to hide complexity

  2. You need security at column/row level

  3. Same query is used in multiple reports.

28. Why Stored Procedures Are Used ?

Answer : 

  1. Faster execution (precompiled)
  2.  Reusable logic
  3.  Better security (no direct table access)
  4. Reduced network traffic.
  5.  Centralized business rules

29. When to use store procedure ?

Answer : 

  • Multiple SQL statements

  • IF / ELSE conditions

  • Loops, calculations, validation.

30.What is GROUP BY?

Answer : Group By is an SQL clause used to group rows that have the same values in one or more columns and apply aggregate functions (COUNT, SUM, AVG, MAX, MIN) on each group.

 

SQL Query :

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;
 

Real-World Example : 

Total salary by department.

31. Why do we use GROUP BY?

Answer : 

  • Get summary reports

  • Perform calculations on grouped data

  • Analyze data (totals, counts, averages)

32. When should you use GROUP BY?

Answer : 

  • You need reports

  • You use aggregate functions

  • You want data summarized

33. Difference between DELETE and  TRUNCATE ? 

Answer : 

      DELETE

 Delete is a DML (Data Manipulation Language) command used to remove specific rows from a table.

  • use WHERE clause

  • Deletes rows one by one

  • Rollback is possible

  • Fires DELETE triggers

  • Slower than TRUNCATE

     
    SQL Query : 
    DELETE FROM Employees WHERE DepartmentID = 10;
     
     
    TRUNCATE

     truncate is a DDL (Data Definition Language) command used to remove all rows from a table instantly.

    • Cannot use WHERE

    • Deletes all rows at once

    • Rollback not possible

    • Does not fire triggers

    • Faster and uses less logging.

    SQL Query : 

    TRUNCATE TABLE Employees;

34. Why use DELETE?

Answer : 

  • Remove specific records

  • Need rollback

  • Trigger logic required

35. Why use TRUNCATE?

Answer :

  • Clear entire table

  • Performance is critical

  • No rollback needed

36. When to use Delete and Truncate?

Answer :

DELETE :

  • Conditional deletion is needed

  • Data recovery may be required

TRUNCATE :

  • Remove all data quickly

  • Reset identity column

37. Difference between DELETE and DROP ?

Answer :

DELETE

It is a DML (Data Manipulation Language) command used to remove rows from a table.

  • Removes data only, not table structure

  • Can delete specific rows using  Where 

  • Rollback is possible

  • Triggers are fired

  • Table remains usable

 
SQL Query :
DELETE FROM Employees WHERE EmpId = 101;
 
 

DROP

 It is a DDL (Data Definition Language) command used to remove the entire database object.

  • Deletes table + structure + data

  • Cannot use WHERE

  • Rollback not possible

  • Table is permanently removed

  • All indexes, constraints are deleted

 
SQL Query:  
DROP TABLE Employees;

38. Why use DROP?

Answer :

  • Table no longer required

  • Free database space completely

  • Permanent deletion

39. When to use Delete And Drop?

Answer :

Delete

  • You want to remove selected rows

  • Table structure is still needed

DROP 

  • Table is no longer required

  • You want permanent removal

40. ACID properties ? 

Answer: 

  • Atomicity

  • Consistency

  • Isolation

  • Durability

41. What is COMMIT?

Answer: Saves changes permanently.

42. What is ROLLBACK?

Answer : Undoes changes.

43. What is Deadlock?

Answer :Two transactions waiting for each other forever.

44. What is SQL Injection?

Answer : A security attack using malicious SQL.

45. What is Transaction?

Answer : A group of SQL operations executed as a single unit.

46. When use a Trigger?

Answer : 

  • Auditing

  • Logging

  • Automatic updates

47. Difference between WHERE and HAVING

Answer :

WHERE 

It is used to filter rows before grouping and aggregation.

  • Filters individual rows

  • Used before GROUP BY

  • Cannot use aggregate functions (SUM, COUNT, AVG)

SQL Query : 
SELECT * FROM Employees WHERE DepartmentID = 10;
 

HAVING 

  It is used to filter groups after GROUP BY and aggregation.

  • Filters grouped results

  • Used after GROUP BY

  • Can use aggregate functions

SQL Query :
SELECT DepartmentID, COUNT(*) AS EmpCount FROM Employees GROUP BY DepartmentID HAVING COUNT(*) > 5;

48. What is Distinct ?

Answer :  It is an SQL keyword used to remove duplicate values from the result set and return only unique records.

49. What is a UNIQUE constraint?

Answer : Ensures all values in a column are different.

50. What is a Function?

Answer : A Function returns a single value and can be used in SELECT.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top