Top SQL Interview Questions and Answers

Here are top SQL interview questions,

1. What is SQL?

SQL stands for Structured Query Language. It is a domain-specific language used for managing and manipulating relational databases.

 

2. What are the different types of SQL statements?

The main types of SQL statements are:

- Data Manipulation Language (DML): Used for retrieving, inserting, updating, and deleting data (e.g., SELECT, INSERT, UPDATE, DELETE).

- Data Definition Language (DDL): Used for defining and managing the structure of the database (e.g., CREATE, ALTER, DROP).

- Data Control Language (DCL): Used for managing user access and permissions (e.g., GRANT, REVOKE).

 

3. Explain the difference between INNER JOIN and OUTER JOIN?

- INNER JOIN: Returns only the rows that have matching values in both tables.

- OUTER JOIN: Returns all the rows from one table and the matching rows from the other table. There are three types of outer joins: LEFT JOIN, RIGHT JOIN, and FULL JOIN.

 

4. What is a primary key?

A primary key is a unique identifier for a row in a table. It ensures that each row in the table can be uniquely identified and helps maintain data integrity.

 

5. What is a foreign key?

A foreign key is a field or a set of fields in a table that refers to the primary key of another table. It establishes a relationship between two tables.

 

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

- UNION: Combines the results of two or more SELECT queries, removing duplicate rows.

- UNION ALL: Also combines the results of two or more SELECT queries but keeps all rows, including duplicates.

 

7. Explain the ACID properties in the context of databases.

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are reliable and maintain data integrity.

 

8. What is a subquery?

A subquery (also known as an inner query or nested query) is a query within another query. It is used to retrieve data based on the results of another query.

 

9. What is a view in SQL?

A view is a virtual table based on the result of a SELECT query. It allows users to simplify complex queries and restrict access to certain data.

 

10. What is the HAVING clause used for?

The HAVING clause is used with the GROUP BY clause and filters data after grouping. It is used to specify conditions for aggregations.

 

11. What is the difference between CHAR and VARCHAR data types?

- CHAR: Fixed-length character data type. It pads the data with spaces to fill the specified length.

- VARCHAR: Variable-length character data type. It only uses as much space as required for the actual data without padding.

 

12. Explain the concept of normalization in databases.

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

 

13. What is the difference between a clustered and a non-clustered index?

- Clustered Index: Determines the physical order of data rows in a table based on the indexed column(s). Each table can have only one clustered index.

- Non-Clustered Index: Creates a separate structure with a pointer to the actual data rows. A table can have multiple non-clustered indexes.

 

14. What is a self-join?

A self-join is a type of join where a table is joined with itself. It is typically used when there is a relationship between rows within the same table.

 

15. Explain the difference between a stored procedure and a function.

- Stored Procedure: A pre-compiled set of one or more SQL statements that are stored in the database and can be called and executed multiple times.

- Function: A pre-compiled set of one or more SQL statements that return a single value and can be used within SQL queries.

 

16. How do you find the second-highest salary from an Employee table?

You can use the following SQL query:

```sql

SELECT MAX(Salary)

FROM Employee

WHERE Salary < (SELECT MAX(Salary) FROM Employee);

```

 

17. How do you remove duplicate rows from a table?

You can use the DISTINCT keyword or the GROUP BY clause to remove duplicate rows from a table.

 

18. What is a correlated subquery?

A correlated subquery is a subquery that depends on the outer query for its values. It is executed for each row processed by the outer query.

 

19. Explain the difference between TRUNCATE and DELETE statements.

- DELETE: Removes rows from a table and logs individual row deletions, making it slower. It can be rolled back.

- TRUNCATE: Removes all rows from a table and deallocates the space, making it faster. It cannot be rolled back.

 

20. How do you find the number of rows in a table?

You can use the COUNT() function to find the number of rows in a table:

```sql

SELECT COUNT(*) FROM TableName;

```

Above are few top SQL interview questions for your reference.

Remember to prepare and expand on these answers. 

Good luck with your interview!  👍