25 SQL Queries Interview Questions

SQL (Structured Query Language) is a fundamental tool for managing and interacting with relational databases. As the backbone of data retrieval, manipulation, and storage in modern applications, SQL skills are highly sought after in the tech industry.

During job interviews, candidates may face a series of SQL queries interview questions to assess their proficiency and problem-solving abilities.

In this article, we will explore 25 common SQL interview questions and provide detailed answers to help aspiring developers and database administrators prepare for their interviews with confidence.

SQL Queries Interview Questions and Answers

1. What is SQL?

SQL (Structured Query Language) is a programming language designed for managing and manipulating relational databases. It is used for tasks such as data retrieval, insertion, deletion, and updating in a database management system.

2. What are the different types of SQL commands?

SQL commands are categorized into four types: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL).

3. Explain the difference between primary key and unique key.

Both primary key and unique key are used to enforce uniqueness in a column. However, a primary key is used to identify a unique row in a table, and it cannot contain NULL values, whereas a unique key allows one NULL value.

4. What is the difference between INNER JOIN and OUTER JOIN?

 INNER JOIN returns only the matching rows from both tables, whereas OUTER JOIN returns matching rows and non-matching rows from one or both tables, depending on the type of OUTER JOIN (LEFT, RIGHT, FULL).

5. How can you eliminate duplicate rows from a table?

To eliminate duplicate rows, you can use the DISTINCT keyword in the SELECT statement, which returns only the unique rows from the result set.

6. What is a subquery?

A subquery is a query embedded within another query. It is used to retrieve data that will be used by the main query to perform further operations.

7. Explain the ACID properties in the context of database transactions.

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

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

 UNION combines the result sets of two or more SELECT queries, removing duplicate rows, while UNION ALL also combines result sets but retains all rows, including duplicates.

9. What is a foreign key?

 A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a link between two tables and enforces referential integrity.

10. How can you sort the result set in descending order?

 To sort the result set in descending order, you can use the ORDER BY clause followed by the column name and the keyword DESC.

11. What is the HAVING clause used for?

The HAVING clause is used with the GROUP BY clause to filter the results of a query based on aggregate functions. It operates on grouped data.

12. What is a self-join?

A self-join is a type of join where a table is joined with itself. It is used when you need to compare rows from the same table based on some condition.

13. Explain the use of the CASE statement in SQL.

The CASE statement is used to perform conditional logic in SQL. It allows you to evaluate conditions and return different values based on those conditions.

14. How do you add a new column to an existing table?

 You can use the ALTER TABLE statement to add a new column to an existing table. The syntax is: `ALTER TABLE table_name ADD column_name datatype;`

15. What is the difference between TRUNCATE and DELETE statements?

 TRUNCATE is a DDL command used to remove all rows from a table quickly, whereas DELETE is a DML command used to remove specific rows based on a condition. TRUNCATE is faster but cannot be rolled back, while DELETE can be rolled back within a transaction.

16. How can you find the second highest salary from an employee table?

You can use the ORDER BY clause with the DESC keyword and the LIMIT clause to retrieve the second-highest salary.

17. What is a view in SQL?

 A view is a virtual table created from a SELECT statement that allows you to retrieve data from one or more tables. Views provide a way to simplify complex queries and control access to data.

18. Explain the difference between a clustered and a non-clustered index.

 A clustered index determines the physical order of data in a table, whereas a non-clustered index creates a separate structure that points to the data rows. A table can have only one clustered index but multiple non-clustered indexes.

19. What is the purpose of the GROUP BY clause?

 The GROUP BY clause is used to group rows with similar values in a specified column. It is often used in combination with aggregate functions like COUNT, SUM, AVG, etc.

20. What are correlated subqueries?

 Correlated subqueries are subqueries that depend on the outer query for their values. They execute once for each row in the outer query, making them less efficient than non-correlated subqueries.

21. Explain the concept of database normalization.

Database normalization is the process of organizing data in a database to reduce data redundancy and improve data integrity. It involves breaking down tables into smaller, related tables and establishing relationships between them.

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

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

23. What is the purpose of the WHERE clause?

The WHERE clause is used to filter rows in a SELECT, UPDATE, or DELETE statement based on a specified condition.

24. How can you calculate the average of a column in SQL?

    You can use the AVG() function to calculate the average of a column.

25. Explain the difference between a LEFT JOIN and a RIGHT JOIN.

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. A RIGHT JOIN, on the other hand, returns all rows from the right table and the matching rows from the left table. If there are no matches, the result will contain NULL values for columns from the table without a matching row.

Conclusion

Mastering SQL is crucial for anyone working with databases, and a solid understanding of its principles is essential for excelling in job interviews and professional roles.

The 25 SQL queries interview questions covered in this article encompass a wide range of topics, from basic syntax to complex query optimization. By studying these questions and answers, aspiring candidates can build a strong foundation and approach their interviews with a sense of assurance. Whether seeking a position as a software developer, data analyst, or database