SQL interview Questions and Answer

on
Top 50 SQL Interview Questions with Answers

Top 50 SQL Interview Questions with Answers

1. What is SQL?

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

2. What are the different types of SQL statements?

SQL statements are categorized into the following types:

  • DDL (Data Definition Language): Commands like CREATE, ALTER, DROP, etc.
  • DML (Data Manipulation Language): Commands like SELECT, INSERT, UPDATE, DELETE.
  • DCL (Data Control Language): Commands like GRANT, REVOKE.
  • TCL (Transaction Control Language): Commands like COMMIT, ROLLBACK, SAVEPOINT.

3. What is a Primary Key?

A Primary Key is a column or group of columns in a table that uniquely identifies each row in that table. A table can only have one Primary Key, and it cannot have NULL values.

4. What is a Foreign Key?

A Foreign Key is a field (or collection of fields) in one table that refers to the Primary Key in another table. It establishes a relationship between two tables.

5. What is a JOIN in SQL?

A JOIN clause is used to combine rows from two or more tables based on a related column between them. The different types of JOINS include:

  • INNER JOIN: Returns records that have matching values in both tables.
  • LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table.
  • RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table.
  • FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table.

6. What is an Index in SQL?

An Index is a database object that improves the speed of data retrieval operations on a table. Indexes can be created using one or more columns of a table, and they work like pointers to the data.

7. What is the difference between DELETE and TRUNCATE commands?

DELETE is used to remove specific rows from a table. It can be rolled back and can also have a WHERE clause.

TRUNCATE removes all rows from a table but cannot be rolled back. It also resets the identity column and does not trigger any DELETE triggers.

8. What is the difference between SQL and NoSQL?

SQL databases are relational, table-based databases that use SQL for defining and manipulating data. NoSQL databases are non-relational and are designed for large-scale data storage, using key-value pairs, document storage, and other methods.

8. What is the difference between SQL and NoSQL?

SQL databases are relational, table-based databases that use SQL for defining and manipulating data. NoSQL databases are non-relational and are designed for large-scale data storage, using key-value pairs, document storage, and other methods.

9. What is a Unique Key?

A Unique Key is a constraint that ensures all values in a column are unique. Unlike the Primary Key, a table can have multiple Unique Keys, and they allow NULL values.

10. What is a View in SQL?

A View is a virtual table based on the result of an SQL query. It does not store data itself but provides a way to query and present data from one or more tables.

Example:

CREATE VIEW EmployeeView AS
SELECT EmployeeID, FirstName, LastName FROM Employees WHERE Department = 'HR';

11. What is normalization in SQL?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing a database into two or more tables and defining relationships between them.

12. What are the different normal forms?

The different normal forms are:

  • 1NF (First Normal Form): Ensures that the table has no duplicate rows and each cell contains atomic values.
  • 2NF (Second Normal Form): Satisfies 1NF and ensures that each non-key attribute is fully functionally dependent on the primary key.
  • 3NF (Third Normal Form): Satisfies 2NF and ensures no transitive dependencies.

13. What is Denormalization?

Denormalization is the process of combining normalized tables to improve database read performance by reducing joins. It is often used to optimize database performance at the cost of redundancy.

14. What is a Stored Procedure?

A Stored Procedure is a set of SQL statements that can be executed as a single unit. It can accept input parameters, execute SQL logic, and return results.

Example:

CREATE PROCEDURE GetEmployeeDetails @EmployeeID INT
AS
BEGIN
SELECT FirstName, LastName, Department FROM Employees WHERE EmployeeID = @EmployeeID;
END;

15. What is a Trigger in SQL?

A Trigger is a special type of Stored Procedure that automatically executes when an event occurs in the database, such as INSERT, UPDATE, or DELETE.

Example:

CREATE TRIGGER trgAfterInsert ON Employees
AFTER INSERT
AS
BEGIN
PRINT 'New record inserted';
END;

16. What are Aggregate Functions in SQL?

Aggregate functions are used to perform calculations on a set of values and return a single value. Common aggregate functions include:

  • COUNT(): Returns the number of rows.
  • SUM(): Returns the sum of values.
  • AVG(): Returns the average value.
  • MAX(): Returns the maximum value.
  • MIN(): Returns the minimum value.

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 to retrieve data for further use.

Example:

SELECT EmployeeID, FirstName FROM Employees WHERE DepartmentID = (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'HR');

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

A Common Table Expression (CTE) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Example:

WITH EmployeeCTE AS (SELECT EmployeeID, FirstName, LastName FROM Employees)
SELECT * FROM EmployeeCTE WHERE EmployeeID = 1;

19. What is a transaction in SQL?

A transaction is a sequence of operations performed as a single logical unit of work. It follows the ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure the integrity of data.

20. What is the ACID property in SQL?

The ACID properties ensure that transactions are processed reliably in a database:

  • Atomicity: All parts of a transaction must be completed; otherwise, the transaction fails.
  • Consistency: The database must be in a consistent state before and after the transaction.
  • Isolation: Each transaction should be isolated from others.
  • Durability: Once a transaction is committed, it must remain in the system permanently.

Comments

Post a Comment