Top 50 SQL Interview Questions And Answers

1. What is SQL?

Answer:
SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases. It allows users to perform operations such as creating tables, inserting data, updating records, retrieving data, and deleting records. SQL is used by database systems like MySQL, PostgreSQL, SQL Server and Oracle.

Example:
To retrieve all records from a Students table:

SELECT * FROM Students;

This query will fetch all rows and columns in the Students table.

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

Answer:
These commands are used to remove data or database objects, but they differ in functionality and impact.

  • DELETE: Deletes specific rows based on a condition in the WHERE clause. The table structure remains intact, and the operation can be rolled back if part of a transaction. DELETE FROM Students WHERE Grade = ‘F’;
  • TRUNCATE: Removes all rows from a table without logging individual row deletions. It is faster than DELETE but cannot be rolled back. The table structure remains. TRUNCATE TABLE Students;
  • DROP: Deletes the entire table, including its structure, and all associated data. It cannot be rolled back. DROP TABLE Students;

3. What is a Primary Key in SQL?

Answer:
A Primary Key is a column or a set of columns in a table that uniquely identifies each row. It ensures uniqueness and does not allow NULL values. A table can have only one Primary Key.

Example:
Creating a table with a Primary Key:

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);

Here, the StudentID column is the Primary Key, ensuring each student has a unique ID.

4. What is a Foreign Key in SQL?

Answer:
A Foreign Key is a column in a table that establishes a relationship with the Primary Key of another table. It is used to maintain referential integrity between the two tables.

Example:

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
StudentID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

In this case, the StudentID column in the Orders table refers to the StudentID column in the Students table.

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

Answer:

  • WHERE: Filters rows before any grouping is applied. It is used with SELECT, UPDATE and DELETE statements.
  • HAVING: Filters groups after the GROUP BY operation. It is used with aggregate functions.

Example:

-- Using WHERE
SELECT * FROM Students WHERE Age > 18;

-- Using HAVING
SELECT Department, COUNT(*) AS Total
FROM Students
GROUP BY Department
HAVING COUNT(*) > 5;

The first query filters individual students, while the second filters groups of students by department.

6. What is a Self Join?

Answer:
A Self Join is a join operation where a table is joined with itself. It is useful for finding relationships within the same table, such as hierarchical data.

Example:

SELECT A.EmployeeName AS Manager, B.EmployeeName AS Subordinate
FROM Employees A, Employees B
WHERE A.EmployeeID = B.ManagerID;

This query finds relationships between managers and subordinates within the Employees table.

7. What is an Index in SQL?

Answer:
An Index is a database object that improves the speed of data retrieval operations on a table by creating a data structure based on one or more columns. However, it can slow down write operations like INSERT and UPDATE because the index also needs to be updated.

Example:

CREATE INDEX idx_name ON Students(Name);

This creates an index on the Name column of the Students table, improving the performance of queries filtering by name.

8. What are the different types of joins in SQL?

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

  • INNER JOIN: Returns rows with matching values in both tables.
  • LEFT JOIN (OUTER): Returns all rows from the left table and matching rows from the right table.
  • RIGHT JOIN (OUTER): Returns all rows from the right table and matching rows from the left table.
  • FULL JOIN (OUTER): Returns all rows from both tables, with NULLs where there is no match.

Example:

SELECT Students.Name, Orders.OrderID
FROM Students
INNER JOIN Orders ON Students.StudentID = Orders.StudentID;

This query fetches students and their corresponding orders.

9. What is Normalization in SQL?

Answer:
Normalization is the process of organizing database tables to reduce redundancy and dependency. It involves dividing a large table into smaller tables and defining relationships between them.

Example:
A table with both student details and course details can be split into two normalized tables:

  1. Students(StudentID, Name, Age)
  2. Courses(CourseID, CourseName, StudentID)

10. What is Denormalization?

Answer:
Denormalization is the process of combining normalized tables into a single table to improve read performance. It may introduce redundancy for faster querying.

Example:
Combining Students and Courses into one table:
Students_Courses(StudentID, Name, Age, CourseID, CourseName)

11. What are the different types of Normalization?

Answer:
Normalization has various forms to organize data efficiently:

  1. 1NF (First Normal Form): Ensures each column contains atomic values (no repeating groups).
  2. 2NF (Second Normal Form): Removes partial dependencies; every non-key column is fully functionally dependent on the Primary Key.
  3. 3NF (Third Normal Form): Removes transitive dependencies; non-key columns depend only on the Primary Key.
  4. BCNF (Boyce-Codd Normal Form): A stricter version of 3NF ensuring no overlapping candidate keys.

Example:
A table storing student details and courses:

StudentID | Name   | Course1 | Course2  
--------------------------------------
101 | John | Math | Science

After 1NF:

StudentID | Name   | Course  
--------------------------
101 | John | Math
101 | John | Science

12. What is a Subquery in SQL?

Answer:
A subquery is a query nested inside another query. It is used to perform operations that depend on the result of another query. Subqueries can return a single value, a list of values, or a complete table.

Example:
Find students enrolled in the same course as student ID 101:

SELECT Name  
FROM Students
WHERE CourseID IN (SELECT CourseID FROM Students WHERE StudentID = 101);

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

Answer:

  • UNION: Combines results from two queries and removes duplicate rows.
  • UNION ALL: Combines results from two queries without removing duplicates.

Example:

-- UNION removes duplicates
SELECT Name FROM Students_A
UNION
SELECT Name FROM Students_B;

-- UNION ALL includes duplicates
SELECT Name FROM Students_A
UNION ALL
SELECT Name FROM Students_B;

14. What are Triggers in SQL?

Answer:
Triggers are automatic actions executed in response to specific database events, such as INSERT, UPDATE, or DELETE. They are used to enforce business rules or maintain audit trails.

Example:
Create a trigger to log data changes:

CREATE TRIGGER LogChanges  
AFTER UPDATE ON Students
FOR EACH ROW
INSERT INTO AuditLog(StudentID, OldValue, NewValue)
VALUES(OLD.StudentID, OLD.Name, NEW.Name);

15. What are Views in SQL?

Answer:
A View is a virtual table that provides a specific representation of data from one or more tables. Views do not store data physically; they retrieve data dynamically from the underlying tables.

Example:
Create a view to display student names and their courses:

CREATE VIEW StudentCourses AS  
SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.StudentID = Courses.StudentID;

Query the view:

SELECT * FROM StudentCourses;

16. What is the difference between CHAR and VARCHAR?

Answer:

  • CHAR: A fixed-length data type. It always uses the defined length, padding with spaces if necessary.
  • VARCHAR: A variable-length data type. It uses only as much space as needed for the data.

Example:

CREATE TABLE ExampleTable (  
FixedData CHAR(10),
VariableData VARCHAR(10)
);

In this case, inserting ABC into FixedData will store ABC (with spaces), while VariableData stores just ABC.

17. What is a Clustered Index in SQL?

Answer:
A Clustered Index determines the physical order of data in a table. A table can have only one clustered index because the data rows are stored in the order of the index.

Example:

CREATE CLUSTERED INDEX idx_students ON Students(StudentID);

This creates a clustered index on the StudentID column, making data retrieval faster when searching by StudentID.

18. What is a Non-Clustered Index?

Answer:
A Non-Clustered Index stores pointers to the actual data rows instead of determining the physical order. A table can have multiple non-clustered indexes.

Example:

CREATE NONCLUSTERED INDEX idx_name ON Students(Name);

This creates an index on the Name column without altering the physical order of the data.

19. What is the difference between DELETE and TRUNCATE?

Answer:

  • DELETE: Removes specific rows based on a condition and logs each deleted row for rollback.
  • TRUNCATE: Removes all rows without logging individual deletions and cannot be rolled back.

Example:

DELETE FROM Students WHERE Grade = 'F';  
TRUNCATE TABLE Students;

20. What is a Transaction in SQL?

Answer:
A Transaction is a sequence of operations performed as a single logical unit of work. Transactions ensure database consistency through ACID properties: Atomicity, Consistency, Isolation and Durability.

Example:

BEGIN TRANSACTION;  
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 101;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 102;
COMMIT;

If any step fails, you can use ROLLBACK to undo changes.

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

Answer:

  • WHERE: Filters rows before any groupings are made in the query.
  • HAVING: Filters data after the grouping operation, usually used with aggregate functions.

Example:

-- Using WHERE before grouping
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
WHERE Salary > 50000
GROUP BY Department;

-- Using HAVING after grouping
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department
HAVING COUNT(*) > 5;

22. What is the difference between PRIMARY KEY and UNIQUE constraints?

Answer:

  • PRIMARY KEY: Uniquely identifies each record in a table and cannot have NULL values.
  • UNIQUE: Ensures all values in a column are unique but allows a single NULL value.

Example:

CREATE TABLE Students (  
StudentID INT PRIMARY KEY,
Email VARCHAR(50) UNIQUE
);

23. What is a Foreign Key in SQL?

Answer:
A Foreign Key is a column or set of columns in one table that establishes a relationship with a Primary Key in another table. It ensures referential integrity by enforcing valid relationships.

Example:

CREATE TABLE Orders (  
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

24. What is a Self-Join in SQL?

Answer:
A Self-Join is a join where a table is joined with itself. It is used to compare rows within the same table.

Example:
Find employees who have the same manager:

SELECT A.EmployeeID, A.ManagerID, B.EmployeeName AS ManagerName  
FROM Employees A
JOIN Employees B ON A.ManagerID = B.EmployeeID;

25. What is the LIKE operator? How is it used?

Answer:
The LIKE operator is used to search for a specified pattern in a column. It supports wildcards:

  • %: Represents zero or more characters.
  • _: Represents a single character.

Example:
Find names starting with ‘A’:

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

Find names ending with ‘n’:

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

26. What is the JOIN clause in SQL? List its types.

Answer:
The JOIN clause combines rows from two or more tables based on related columns. Types of joins:

  1. INNER JOIN: Returns matching rows from both tables.
  2. LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
  3. RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
  4. FULL JOIN: Returns all rows when there is a match in either table.

Example:

-- INNER JOIN example
SELECT Students.Name, Courses.CourseName
FROM Students
INNER JOIN Courses ON Students.StudentID = Courses.StudentID;

27. What are Aggregate Functions in SQL?

Answer:
Aggregate Functions perform calculations on multiple rows and return a single value. Common functions include:

  • COUNT(): Counts rows.
  • SUM(): Sums up values.
  • AVG(): Calculates the average.
  • MIN(): Finds the minimum value.
  • MAX(): Finds the maximum value.

Example:

SELECT COUNT(*) AS TotalStudents, AVG(Salary) AS AvgSalary FROM Employees;

28. What is the difference between INNER JOIN and OUTER JOIN?

Answer:

  • INNER JOIN: Returns rows with matching values in both tables.
  • OUTER JOIN: Returns all rows from one table and the matched rows from the other. Types of outer joins:
    • LEFT JOIN: All rows from the left table.
    • RIGHT JOIN: All rows from the right table.

Example:

-- LEFT JOIN example
SELECT Students.Name, Courses.CourseName
FROM Students
LEFT JOIN Courses ON Students.StudentID = Courses.StudentID;

29. What is a Stored Procedure in SQL?

Answer:
A Stored Procedure is a precompiled set of SQL statements that can be executed as a single unit. It helps improve performance and maintain consistency.

Example:

CREATE PROCEDURE GetStudentDetails  
AS
BEGIN
SELECT * FROM Students;
END;

EXEC GetStudentDetails;

30. What is a Cursor in SQL?

Answer:
A Cursor is a database object used to retrieve, manipulate, and traverse through a result set row by row. It is often used in scenarios requiring row-by-row operations.

Example:

DECLARE cursor_name CURSOR FOR  
SELECT Name FROM Students;

OPEN cursor_name;
FETCH NEXT FROM cursor_name INTO @Name;
CLOSE cursor_name;
DEALLOCATE cursor_name;

31. What is the difference between a TRIGGER and a STORED PROCEDURE?

Answer:

  • TRIGGER: Automatically executes in response to specific events in a table (like INSERT, UPDATE, or DELETE).
  • STORED PROCEDURE: Must be explicitly invoked by the user or application.

Example of a Trigger:

CREATE TRIGGER UpdateAudit  
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
INSERT INTO AuditLog(EmployeeID, Action, ActionDate)
VALUES (NEW.EmployeeID, 'Updated', NOW());
END;

Example of a Stored Procedure:

CREATE PROCEDURE GetEmployees()  
AS
BEGIN
SELECT * FROM Employees;
END;

32. What is the purpose of the GROUP BY clause in SQL?

Answer:
The GROUP BY clause is used to group rows with the same values in specified columns. It is commonly used with aggregate functions like SUM(), COUNT(), AVG(), etc., to perform operations on each group.

Example:

SELECT Department, AVG(Salary) AS AvgSalary  
FROM Employees
GROUP BY Department;

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

Answer:

  • UNION: Combines the result sets of two queries and removes duplicate rows.
  • UNION ALL: Combines the result sets of two queries but retains duplicates.

Example:

-- UNION
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

-- UNION ALL
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;

34. What is the COALESCE function in SQL?

Answer:
The COALESCE function returns the first non-NULL value from the list of expressions. It is used to handle NULL values in queries.

Example:

SELECT Name, COALESCE(PhoneNumber, 'Not Provided') AS Contact  
FROM Customers;

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

Answer:

  • DELETE: Removes specific rows from a table and can be rolled back.
  • TRUNCATE: Removes all rows from a table but cannot be rolled back. It resets identity columns.
  • DROP: Deletes the table structure and its data permanently.

Example:

DELETE FROM Employees WHERE Department = 'HR';  
TRUNCATE TABLE Employees;
DROP TABLE Employees;

36. What is an Index in SQL and why is it used?

Answer:
An Index is a database object that improves query performance by allowing faster retrieval of rows. It works like a table of contents in a book.

  • Clustered Index: Sorts and stores data rows in the table based on key values.
  • Non-Clustered Index: Creates a separate structure for the index, leaving the table data unchanged.

Example:

CREATE INDEX idx_name ON Employees (LastName);

37. What is a Subquery in SQL?

Answer:
A Subquery is a query nested inside another query. It can be used in SELECT, INSERT, UPDATE, or DELETE statements.

Example:

SELECT Name  
FROM Employees
WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'HR');

38. What is the difference between CHAR and VARCHAR in SQL?

Answer:

  • CHAR: Fixed-length string. If the stored data is shorter than the defined length, extra spaces are padded.
  • VARCHAR: Variable-length string. It only uses space required by the actual data.

Example:

CREATE TABLE Test (  
FixedField CHAR(10),
VariableField VARCHAR(10)
);

39. What are SQL Constraints? List some examples.

Answer:
Constraints are rules applied to columns in a table to enforce data integrity. Examples include:

  • NOT NULL: Ensures a column cannot have NULL values.
  • UNIQUE: Ensures all values in a column are unique.
  • PRIMARY KEY: Combines NOT NULL and UNIQUE.
  • FOREIGN KEY: Ensures referential integrity.
  • CHECK: Ensures values satisfy a specific condition.

Example:

CREATE TABLE Students (  
ID INT PRIMARY KEY,
Age INT CHECK (Age >= 18)
);

40. What is Normalization in SQL? Explain its types.

Answer:
Normalization organizes data to reduce redundancy and improve integrity. Types:

  1. 1NF (First Normal Form): Ensures atomic values (no repeating groups or arrays).
  2. 2NF (Second Normal Form): Removes partial dependency.
  3. 3NF (Third Normal Form): Removes transitive dependency.

41. What is Denormalization in SQL?

Answer:
Denormalization is the process of combining tables to optimize query performance by introducing redundancy. It is used when read operations are frequent, and speed is critical.

42. What is the difference between HAVING and WHERE clauses in SQL?

Answer:

  • WHERE: Filters rows before any grouping or aggregation is performed.
  • HAVING: Filters groups after the grouping operation is applied.

Example:

-- Using WHERE to filter rows before grouping
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
WHERE Salary > 3000
GROUP BY Department;

-- Using HAVING to filter groups
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 5000;

43. What are the different types of Joins in SQL?

Answer:
Joins are used to combine rows from two or more tables. Types include:

  1. INNER JOIN: Returns matching rows from both tables.
  2. LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and matching rows from the right.
  3. RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and matching rows from the left.
  4. FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in either table.
  5. CROSS JOIN: Returns the Cartesian product of two tables.

Example:

-- INNER JOIN example
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

44. What is the difference between a VIEW and a TABLE in SQL?

Answer:

  • TABLE: A physical structure that stores data in a database.
  • VIEW: A virtual table based on the result of a query. Views do not store data physically but provide a way to look at data from one or more tables.

Example of a View:

CREATE VIEW EmployeeView AS  
SELECT Name, Salary
FROM Employees
WHERE Department = 'IT';

45. What is the purpose of the INDEX in SQL?

Answer:
Indexes are used to improve the performance of queries by reducing the amount of data scanned. They allow the database to find data more quickly than scanning the entire table.

Example:

CREATE INDEX idx_employee_name ON Employees (Name);

46. What is a CURSOR in SQL?

Answer:
A Cursor is a database object used to retrieve, manipulate, and traverse records one row at a time. It is used in scenarios where row-by-row processing is required.

Example:

DECLARE Cursor_Example CURSOR FOR  
SELECT Name FROM Employees;

OPEN Cursor_Example;

FETCH NEXT FROM Cursor_Example;

CLOSE Cursor_Example;
DEALLOCATE Cursor_Example;

47. What is the difference between PRIMARY KEY and UNIQUE key?

Answer:

  • PRIMARY KEY: Ensures each row in a table is uniquely identified and cannot have NULL values. A table can have only one primary key.
  • UNIQUE: Ensures all values in a column are unique but allows one NULL value.

Example:

CREATE TABLE Students (  
StudentID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE
);

48. What are ACID properties in SQL?

Answer:
ACID properties ensure database transactions are processed reliably:

  1. Atomicity: Ensures all operations of a transaction are completed or none are.
  2. Consistency: Ensures data remains in a valid state before and after a transaction.
  3. Isolation: Ensures transactions do not interfere with each other.
  4. Durability: Ensures changes made by a transaction are permanent.

49. What is a Stored Procedure in SQL?

Answer:
A Stored Procedure is a precompiled set of SQL statements stored in the database. It can accept parameters, perform operations, and return results.

Example:

CREATE PROCEDURE GetEmployeeByID (IN EmployeeID INT)  
BEGIN
SELECT * FROM Employees WHERE ID = EmployeeID;
END;

50. What is the difference between DROP, DELETE and TRUNCATE in SQL?

Answer:

  • DELETE: Removes specific rows based on a condition. The table and structure remain, and the operation can be rolled back.
  • TRUNCATE: Removes all rows from a table, resets identity columns, and cannot be rolled back.
  • DROP: Deletes the table structure and data permanently.

Example:

DELETE FROM Employees WHERE Department = 'HR';  
TRUNCATE TABLE Employees;
DROP TABLE Employees;

Leave a Comment

BoxofLearn