Top 30 SQL Interview Questions and Answers for Fresher and Experienced

Sure! Here are the top 30 SQL interview questions and answers for both fresher and experienced candidates:

What is SQL?

SQL stands for Structured Query Language, and it is a programming language designed for managing and manipulating relational databases.

What are the different types of SQL statements?

There are four main types of SQL statements:
Data Manipulation Language (DML) statements (e.g., SELECT, INSERT, UPDATE, DELETE)
Data Definition Language (DDL) statements (e.g., CREATE, ALTER, DROP)
Data Control Language (DCL) statements (e.g., GRANT, REVOKE)
Transaction Control Language (TCL) statements (e.g., COMMIT, ROLLBACK)

What is a primary key?

A primary key is a column or a combination of columns that uniquely identifies each row in a table. It enforces uniqueness and allows for fast data retrieval.

What is a foreign key?

A foreign key is a column or a combination of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables.

What is a join?

A join is used to combine rows from two or more tables based on a related column between them. It allows you to retrieve data from multiple tables in a single query.

What are the different types of joins in SQL?

The main types of joins are:
Inner join: Returns only the matching rows from both tables.
Left join: Returns all rows from the left table and the matching rows from the right table.
Right join: Returns all rows from the right table and the matching rows from the left table.
Full outer join: Returns all rows when there is a match in either the left or right table.

What is normalization?

Normalization is the process of organizing data in a database to eliminate redundancy and dependency issues. It involves breaking down larger tables into smaller ones and establishing relationships between them.

What is a subquery?

A subquery is a query that is nested within another query. It is used to retrieve data based on the results of another query.

What is a view?

A view is a virtual table derived from the result of a query. It does not store any data itself but retrieves data from one or more underlying tables. Views provide an additional layer of abstraction and security.

What is the difference between UNION and UNION ALL?

UNION combines the result sets of two or more SELECT statements, removing duplicate rows. UNION ALL, on the other hand, combines the result sets without removing duplicates.

What is the difference between DELETE and TRUNCATE?

DELETE is a DML statement used to remove specific rows from a table. TRUNCATE is a DDL statement used to remove all rows from a table, but it also resets identity columns and cannot be rolled back.

What is the difference between CHAR and VARCHAR data types?

CHAR is a fixed-length data type that stores a specific number of characters. VARCHAR is a variable-length data type that stores a varying number of characters, up to a maximum limit.

What is the difference between clustered and non-clustered indexes?

A clustered index determines the physical order of data rows in a table. There can be only one clustered index per table. A non-clustered index does not affect the physical order of the data and can be created on multiple columns.

What is ACID in database transactions?

ACID stands for Atomicity, Consistency, Isolation, and Durability. It is a set of properties that guarantee reliable processing of database transactions.

What is the difference between a function and a stored procedure?

A function returns a value and is used in SQL statements. A stored procedure does not return a value but can perform multiple operations, including data manipulation.

What is the difference between a candidate key and a composite key?

A candidate key is a column or a combination of columns that can uniquely identify a row in a table. A composite key is a combination of two or more columns that together form a unique identifier.

What is a self-join?

A self-join is a join where a table is joined with itself. It is used to combine rows within a single table based on a related column.

What is the HAVING clause?

The HAVING clause is used in conjunction with the GROUP BY clause to filter the results of a query based on a condition applied to a group of rows.

What is the difference between a database and a schema?

A database is a collection of tables, views, procedures, and other objects. A schema is a logical container within a database that holds these objects.

Explain the concept of index fragmentation.

Index fragmentation occurs when the physical order of data in an index does not match the logical order. It can degrade database performance and is resolved by rebuilding or reorganizing indexes.

What is the difference between UNION and UNION ALL?

UNION combines the result sets of two or more SELECT statements and removes duplicate rows. UNION ALL, on the other hand, combines the result sets without removing duplicates.

What is the purpose of the GROUP BY clause?

The GROUP BY clause is used to group rows based on one or more columns. It is typically used with aggregate functions (such as SUM, COUNT, AVG) to perform calculations on each group.

What is a stored procedure?

A stored procedure is a named set of SQL statements that are stored in the database and can be invoked repeatedly. It allows for code reusability, improved performance, and easier maintenance.

What is a trigger?

A trigger is a database object that is associated with a table and is automatically executed when a specific event (such as INSERT, UPDATE, DELETE) occurs on that table. Triggers are used to enforce business rules or perform additional actions.

What is the difference between a correlated subquery and a nested subquery?

A correlated subquery is a subquery that refers to a column from the outer query. It executes once for each row processed by the outer query. A nested subquery, on the other hand, is an independent subquery that can be executed on its own.

What is the difference between a unique key and a primary key?

Both unique keys and primary keys enforce uniqueness in a column or a combination of columns. However, a table can have multiple unique keys, but only one primary key. Additionally, a primary key cannot contain NULL values.

What is the difference between a view and a table?

A table is a physical structure that stores data, whereas a view is a virtual table derived from a query. Views do not store data themselves but provide an alternative way to present the data stored in tables.

What is the purpose of the ORDER BY clause?

The ORDER BY clause is used to sort the result set of a query based on one or more columns. It allows for ascending or descending sorting and is often used with the SELECT statement.

What is the difference between the WHERE clause and the HAVING clause?

The WHERE clause is used to filter rows based on a condition during the retrieval of data. The HAVING clause, however, is used to filter rows based on a condition after the data has been grouped using the GROUP BY clause.

What is the purpose of the COMMIT and ROLLBACK statements?

The COMMIT statement is used to permanently save the changes made in a transaction. It makes the changes visible to other users. The ROLLBACK statement, on the other hand, is used to undo all the changes made in a transaction and restore the database to its previous state.
I hope these questions and answers are helpful for your SQL interview preparation! Let me know if you need more.