Top 50 Intermediate SQL Interview Questions You Must Practice [With Examples]

by
11. What are the types of user-defined functions in SQL?

Answer:

  • Scalar Functions — return a single value.

  • Table-Valued Functions — return a table.

12. What is the difference between Scalar and Table-Valued Functions?

Answer:

  • Scalar Function returns one single value (like int, varchar).

  • Table-Valued Function returns a full table.

Example:

-- Scalar Function

CREATE FUNCTION GetTodayDate()
RETURNS DATE
AS
BEGIN
RETURN GETDATE()
END;
-- Table-Valued Function

CREATE FUNCTION GetEmployees()
RETURNS TABLE
AS
RETURN (SELECT * FROM Employees);
13. What is a Materialized View?

Answer:

  • A Materialized View stores the query result physically and can be refreshed periodically.

Example:

CREATE MATERIALIZED VIEW EmployeeSalarySummary AS
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department;

14. What is the purpose of the WITH (NOLOCK) hint?

Answer:

  • It allows reading data without locking, reducing blocking — but may read dirty (uncommitted) data.

Example: SELECT * FROM Employees WITH (NOLOCK);

15. What is a Deadlock? How can you prevent it?

Answer:

  • A deadlock occurs when two or more processes hold locks and wait for each other endlessly.

  • Prevention:

    • Always access tables in the same order.

    • Keep transactions short.

16. What is the difference between Primary Key and Unique Key?

Answer:

  • Primary Key: No NULL values allowed. Only one per table.

  • Unique Key: Allows one NULL value. Multiple unique keys allowed.

Example:

— Primary Key
ALTER TABLE Employees ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

— Unique Key
ALTER TABLE Employees ADD CONSTRAINT UQ_EmployeeEmail UNIQUE (Email);

17. Can a table have multiple primary keys?

Answer:

  • No, a table can only have one primary key, but it can consist of multiple columns (composite primary key).

Example:

ALTER TABLE EmployeeProjects
ADD CONSTRAINT PK_EmpProj PRIMARY KEY (EmployeeID, ProjectID);
18. What is Referential Integrity?

Answer:

  • Referential Integrity ensures that foreign key values always match primary key values in another table, maintaining consistency.

19. How do you implement many-to-many relationships in SQL?

Answer:

  • By creating a junction table with foreign keys from both related tables.

Example:

CREATE TABLE StudentCourse (
StudentID INT,
CourseID INT,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

20. What is the difference between DELETE, TRUNCATE, and DROP commands?

Answer:

Command Description
DELETE Removes rows one by one. Can use WHERE. Transactional (rollback possible).
TRUNCATE Removes all rows quickly. Cannot use WHERE. Resets identity column.
DROP Removes table structure completely.

Examples:

DELETE FROM Employees WHERE Department = ‘HR’;

TRUNCATE TABLE Employees;

DROP TABLE Employees;

 

Page : 1 2 3 4 5

Top 50 Basic Level SQL Interview Questions and Answers with Examples

Top 50 Advanced SQL Questions asked in Top Companies (with Answers + Examples)

2 thoughts on “Top 50 Intermediate SQL Interview Questions You Must Practice [With Examples]”

  1. Pingback: Top 50 Intermediate SQL Interview Questions You Must Practice [With Examples] - Telugu Data Hub
  2. Pingback: Top 50 Intermediate SQL Interview Questions You Must Practice [With Examples] - Telugu Data Hub

Leave a Comment