SQL Interview Questions and Answers

Akib Sayyad 0

1. What is the difference between a Stored Procedure and a Function?

Stored ProcedureFunction
Can have both input and output parameters.Can only have input parameters.
Can use DML (INSERT, UPDATE, DELETE) statements.Cannot use DML statements.
Cannot be used in a SELECT statement.Can be used in a SELECT statement.
Supports transaction management.Does not support transaction management.
Can use Try-Catch blocks for error handling.Cannot use Try-Catch blocks.
Can return 0 or more values.Must return exactly one value.
Cannot be called from a function.Can be called from a stored procedure.
2. What is the difference between a Clustered Index and a Non-Clustered Index?
Clustered IndexNon-Clustered Index
Physically sorts and stores data rows in the table based on the index key.Creates a separate structure to store index keys and pointers to data rows.
Only one clustered index per table.Multiple non-clustered indexes can be created per table.
Faster for retrieving large ranges of data.Faster for retrieving single rows or small ranges of data.
Leaf nodes contain actual data rows.Leaf nodes contain pointers to data rows.
Default index for primary keys.Default index for unique keys.
3. What is the difference between DELETE and TRUNCATE commands?
DELETETRUNCATE
Removes rows one by one and logs each deletion in the transaction log.Removes all rows at once and logs only the deallocation of data pages.
Can use a WHERE clause to filter rows.Cannot use a WHERE clause.
Does not reset the identity column.Resets the identity column to its seed value.
Can activate triggers.Does not activate triggers.
Slower for large datasets.Faster for large datasets.
4. What is the difference between the WHERE clause and the HAVING clause?
WHERE ClauseHAVING Clause
Filters rows before grouping.Filters groups after grouping.
Cannot use aggregate functions.Can use aggregate functions.
Used with SELECT, UPDATE, and DELETE statements.Used only with SELECT statements.
Applies to individual rows.Applies to summarized rows (groups).
5. What is the difference between a Primary Key and a Unique Key?
Primary KeyUnique Key
Only one primary key per table.Multiple unique keys per table.
Cannot contain NULL values.Can contain one NULL value.
Creates a clustered index by default.Creates a non-clustered index by default.
Used to uniquely identify each row in a table.Ensures that all values in a column are unique.

6. What are Super, Primary, Candidate, and Foreign Keys?

  • Super Key: A set of attributes that can uniquely identify a row in a table.
  • Candidate Key: A minimal super key (no subset of it is a super key).
  • Primary Key: One of the candidate keys chosen to uniquely identify rows in a table.
  • Foreign Key: A column or set of columns that refers to the primary key of another table.

7. What is Database Normalization?

Database normalization is the process of organizing data to minimize redundancy and improve data integrity. It involves dividing a database into tables and defining relationships between them. The main normal forms are:

1NF: Eliminate Repeating Groups Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain. 

2. 2NF: Eliminate Redundant Data If an attribute depends on only part of a multi valued key, remove it to a separate table. 

3. 3NF: Eliminate Columns Not Dependent On Key If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key. 

4. BCNF: Boyce-Codd Normal Form If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables. 

5. 4NF: Isolate Independent Multiple Relationships No table may contain two or more 1:n or n:m relationships that are not directly related. 

6. 5NF: Isolate Semantically Related Multiple Relationships There may be practical constrains on information that justify separating logically related many-to-many relationships. 

7. ONF: Optimal Normal Form A model limited to only simple (elemental) facts, as expressed in Object Role Model notation. 

8. DKNF: Domain-Key Normal Form A model free from all modification anomalies is said to be in DKNF. 

8. What is SQL?

SQL (Structured Query Language) is a standard language for managing and manipulating relational databases. It is used for tasks such as querying data, updating data, and managing database structures.

9. What are the Advantages of using CTE? 

1. Using CTE improves the readability and makes maintenance of complex queries 
easy.  
2. The query can be divided into separate, simple, logical building blocks which can be 
then used to build more complex CTEs until final result set is generated.  
3. CTE can be defined in functions, stored procedures, triggers or even views.  
4. After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data. 

10. What are the differences between DDL, DML, and DCL?

DDL (Data Definition Language)DML (Data Manipulation Language)DCL (Data Control Language)
Used to define and modify database structures (e.g., CREATE, ALTER, DROP).Used to manipulate data (e.g., SELECT, INSERT, UPDATE, DELETE).Used to control access to data (e.g., GRANT, REVOKE).

11. What is a Join? Explain different types of Joins.

A Join is used to combine rows from two or more tables based on a related column. Types of Joins:

  1. INNER JOIN: Returns rows with matching values in 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.
  5. CROSS JOIN: Returns the Cartesian product of both tables.

12. What is a View?

A View is a virtual table based on the result set of a SQL query. It does not store data but provides a way to simplify complex queries.

Example:

CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName
FROM Employees;

13. What is a Trigger?

A Trigger is a special type of stored procedure that automatically executes in response to certain events (e.g., INSERT, UPDATE, DELETE) on a table.

Example:

CREATE TRIGGER UpdateEmployee
ON Employees
FOR UPDATE
AS
BEGIN
    PRINT 'Employee record updated.';
END;

14. What is a Transaction? What are ACID Properties?

A Transaction is a sequence of operations performed as a single logical unit of work. ACID properties ensure transaction reliability:

  1. Atomicity: All operations in a transaction are completed successfully, or none are.
  2. Consistency: The database remains in a valid state before and after the transaction.
  3. Isolation: Transactions are isolated from each other until they are completed.
  4. Durability: Once a transaction is committed, it remains permanent.

15. What are Indexes?

Indexes are database objects used to improve the speed of data retrieval operations. Types of Indexes:

  1. Clustered Index: Determines the physical order of data in a table.
  2. Non-Clustered Index: Creates a separate structure to store index keys and pointers to data rows.

16. What is a Cursor?

A Cursor is a database object used to retrieve and manipulate data row by row. It is typically used in stored procedures and triggers.

Example:

DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, FirstName FROM Employees;

OPEN EmployeeCursor;
FETCH NEXT FROM EmployeeCursor;
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

17. What is a Subquery?

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

Example:

SELECT EmployeeID, FirstName
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

18. What is a Self-Join?

A Self-Join is a join of a table to itself. It is useful for comparing rows within the same table.

Example:

SELECT A.EmployeeID, A.FirstName, B.FirstName AS ManagerName
FROM Employees A, Employees B
WHERE A.ManagerID = B.EmployeeID;

19. What is a Deadlock?

A Deadlock occurs when two or more transactions block each other by holding locks on resources that the other transactions need. SQL Server detects deadlocks and terminates one of the transactions.

20. What is a Stored Procedure?

A Stored Procedure is a precompiled collection of SQL statements that can be executed as a single unit. It can accept input parameters and return output parameters.

Example:

CREATE PROCEDURE GetEmployee
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

21. What is a CTE (Common Table Expression)?

A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH clause.

Example:

WITH EmployeeCTE AS (
    SELECT EmployeeID, FirstName FROM Employees
)
SELECT * FROM EmployeeCTE;

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

UNIONUNION ALL
Combines the result sets of two or more SELECT statements and removes duplicates.Combines the result sets of two or more SELECT statements without removing duplicates.

23. What is a Database Lock?

A Database Lock is a mechanism to prevent multiple transactions from accessing the same data simultaneously. Types of Locks:

  1. Shared Lock: Allows concurrent transactions to read a resource.
  2. Exclusive Lock: Prevents other transactions from accessing the resource.

24. What is a Composite Key?

A Composite Key is a combination of two or more columns that uniquely identify a row in a table.

Example:

CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    PRIMARY KEY (OrderID, ProductID)
);

25. What is a Foreign Key?

A Foreign Key is a column or set of columns in one table that refers to the primary key in another table. It enforces referential integrity.

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID)
);

26. What is a Unique Constraint?

A Unique Constraint ensures that all values in a column are unique. Unlike a primary key, it can contain one NULL value.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(255) UNIQUE
);

27. What is a Check Constraint?

A Check Constraint limits the values that can be placed in a column.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Age INT CHECK (Age >= 18)
);

28. What is a Default Constraint?

A Default Constraint provides a default value for a column when no value is specified.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    HireDate DATE DEFAULT GETDATE()
);

29. What is a NOT NULL Constraint?

A NOT NULL Constraint ensures that a column cannot contain NULL values.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(255) NOT NULL
);

30. What is a Database Transaction?

A Database Transaction is a sequence of operations performed as a single logical unit of work. It ensures data integrity and consistency.

Example:

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT TRANSACTION;

31. What is a Database Trigger?

A Database Trigger is a special type of stored procedure that automatically executes in response to certain events (e.g., INSERT, UPDATE, DELETE) on a table.

Example:

CREATE TRIGGER UpdateEmployee
ON Employees
FOR UPDATE
AS
BEGIN
    PRINT 'Employee record updated.';
END;

32. What is a Database View?

A Database View is a virtual table based on the result set of a SQL query. It does not store data but provides a way to simplify complex queries.

Example:

CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName
FROM Employees;

33. What is a Database Index?

A Database Index is a data structure that improves the speed of data retrieval operations on a database table. It can be clustered or non-clustered.

Example:

CREATE INDEX idx_EmployeeName
ON Employees (FirstName);

34. What is a Database Cursor?

A Database Cursor is a database object used to retrieve and manipulate data row by row. It is typically used in stored procedures and triggers.

Example:

DECLARE EmployeeCursor CURSOR FOR
SELECT EmployeeID, FirstName FROM Employees;

OPEN EmployeeCursor;
FETCH NEXT FROM EmployeeCursor;
CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

35. What is a Database Subquery?

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

Example:

SELECT EmployeeID, FirstName
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

36. What is a Database Self-Join?

A Database Self-Join is a join of a table to itself. It is useful for comparing rows within the same table.

Example:

SELECT A.EmployeeID, A.FirstName, B.FirstName AS ManagerName
FROM Employees A, Employees B
WHERE A.ManagerID = B.EmployeeID;

37. What is a Database Deadlock?

A Database Deadlock occurs when two or more transactions block each other by holding locks on resources that the other transactions need. SQL Server detects deadlocks and terminates one of the transactions.

38. What is a Database Stored Procedure?

A Database Stored Procedure is a precompiled collection of SQL statements that can be executed as a single unit. It can accept input parameters and return output parameters.

Example:

CREATE PROCEDURE GetEmployee
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

39. What is a Database CTE (Common Table Expression)?

A Database CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH clause.

Example:

WITH EmployeeCTE AS (
    SELECT EmployeeID, FirstName FROM Employees
)
SELECT * FROM EmployeeCTE;

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

UNIONUNION ALL
Combines the result sets of two or more SELECT statements and removes duplicates.Combines the result sets of two or more SELECT statements without removing duplicates.

41. What is a Database Lock?

A Database Lock is a mechanism to prevent multiple transactions from accessing the same data simultaneously. Types of Locks:

  1. Shared Lock: Allows concurrent transactions to read a resource.
  2. Exclusive Lock: Prevents other transactions from accessing the resource.

42. What is a Database Composite Key?

A Database Composite Key is a combination of two or more columns that uniquely identify a row in a table.

Example:

CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    PRIMARY KEY (OrderID, ProductID)
);

43. What is a Database Foreign Key?

A Database Foreign Key is a column or set of columns in one table that refers to the primary key in another table. It enforces referential integrity.

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    EmployeeID INT FOREIGN KEY REFERENCES Employees(EmployeeID)
);

44. What is a Database Unique Constraint?

A Database Unique Constraint ensures that all values in a column are unique. Unlike a primary key, it can contain one NULL value.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(255) UNIQUE
);

45. What is a Database Check Constraint?

A Database Check Constraint limits the values that can be placed in a column.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Age INT CHECK (Age >= 18)
);

46. What is a Database Default Constraint?

A Database Default Constraint provides a default value for a column when no value is specified.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    HireDate DATE DEFAULT GETDATE()
);

47. What is a Database NOT NULL Constraint?

A Database NOT NULL Constraint ensures that a column cannot contain NULL values.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(255) NOT NULL
);

48. What is a Database Transaction?

A Database Transaction is a sequence of operations performed as a single logical unit of work. It ensures data integrity and consistency.

Example:

BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT TRANSACTION;

49. What is a Database Trigger?

A Database Trigger is a special type of stored procedure that automatically executes in response to certain events (e.g., INSERT, UPDATE, DELETE) on a table.

Example:

CREATE TRIGGER UpdateEmployee
ON Employees
FOR UPDATE
AS
BEGIN
    PRINT 'Employee record updated.';
END;

50. What is a Database View?

A Database View is a virtual table based on the result set of a SQL query. It does not store data but provides a way to simplify complex queries.

Example:

CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName
FROM Employees;

Tags

Post a Comment

Post a Comment

Previous Post Next Post