Top 10 most asked SQL interview questions along with their answers

1. What is SQL?
Answer: SQL stands for Structured Query Language. It is a domain-specific language used for managing and manipulating relational databases. SQL is used for querying data, updating data, defining schema, and managing permissions within a database system.

2. What are the different types of SQL commands?
Answer: SQL commands are broadly classified into four categories:

• Data Definition Language (DDL): Used for defining and modifying database structure (e.g., CREATE, ALTER, DROP).
• Data Manipulation Language (DML): Used for managing data within tables (e.g., INSERT, UPDATE, DELETE).
• Data Query Language (DQL): Used for querying data from tables (e.g., SELECT).
• Data Control Language (DCL): Used for managing permissions and access control (e.g., GRANT, REVOKE).

3. Explain the differences between SQL JOINs: INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.
Answer:

• 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 matching records from the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and matching records from the left table.
• FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either the left or right table.

4. What is the difference between SQL WHERE and HAVING clauses?
Answer:

• WHERE clause is used to filter records before grouping or aggregating data.
• HAVING clause is used to filter records after grouping or aggregating data.

5. What is a primary key and a foreign key in SQL?
Answer:

• Primary Key: A primary key is a unique identifier for each record in a table. It ensures that each row in a table is uniquely identified.
• Foreign Key: A foreign key is a field in one table that refers to the primary key in another table. It establishes a relationship between two tables.

6. What is the difference between DELETE and TRUNCATE commands?
Answer:

• DELETE command is used to remove one or more records from a table based on a condition.
• TRUNCATE command is used to remove all records from a table, resetting the table to its original state.

7. Explain the ACID properties of a transaction in SQL.
Answer:

• ACID stands for Atomicity, Consistency, Isolation, and Durability.
• Atomicity ensures that either all the operations in a transaction are completed successfully or none of them are.
• Consistency ensures that the database remains in a consistent state before and after the transaction.
• Isolation ensures that the operations in one transaction are isolated from other concurrent transactions.
• Durability ensures that once a transaction is committed, its changes are permanently saved even in the event of a system failure.

8. What is a subquery in SQL?
Answer:
A subquery is a query nested within another query. It can be used to retrieve data from one or more tables based on the results of the outer query.

9. Explain the difference between UNION and UNION ALL operators in SQL.
Answer:

• UNION operator is used to combine the result sets of two or more SELECT statements and removes duplicate rows.
• UNION ALL operator is used to combine the result sets of two or more SELECT statements, including duplicate rows.

10. What is normalization in SQL?
Answer:
Normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves breaking down large tables into smaller tables and defining relationships between them to minimize data redundancy and ensure data integrity.