SQL Interview Questions with Answers

1. What is a primary key and why is it important in a database?
   - A primary key is a unique identifier for each record in a database table. It is important because it ensures that each record can be uniquely identified and helps maintain data integrity by preventing duplicate or null values.

2. Can you explain the difference between INNER JOIN and OUTER JOIN in SQL?
   - INNER JOIN returns only the rows that have matching values in both tables, while OUTER JOIN returns all rows from one table and the matched rows from the other table (or null values if there is no match).

3. How do you optimize a SQL query for better performance?
   - To optimize a SQL query, you can use indexes, avoid using SELECT *, limit the number of columns selected, use appropriate data types, and avoid using functions in WHERE clauses.

4. What is normalization and why is it important in database design?
   - Normalization is the process of organizing data in a database to reduce redundancy and dependency. It is important because it helps improve data integrity, reduce storage space, and make data maintenance easier.

5. How do you handle missing data in SQL queries?
   - You can handle missing data in SQL queries by using functions like COALESCE or IFNULL to replace null values with a default value, or by using the IS NULL or IS NOT NULL operators to filter out records with missing data.

6. Can you explain the difference between GROUP BY and HAVING clauses in SQL?
   - GROUP BY is used to group rows that have the same values into summary rows, while HAVING is used to filter groups based on specified conditions after the GROUP BY clause has been applied.

7. How do you identify and remove duplicate records from a database table?
   - You can identify duplicate records by using the DISTINCT keyword or by using the GROUP BY clause with COUNT() function. To remove duplicate records, you can use the DELETE statement with a subquery that identifies the duplicates.

8. How do you write a subquery in SQL?
   - A subquery is a query nested within another query. You can write a subquery by enclosing the inner query within parentheses and using it as a part of the outer query's WHERE, FROM, or SELECT clause.

9. What is the difference between a view and a table in SQL?
   - A table stores actual data in a database, while a view is a virtual table that displays data from one or more tables based on a predefined query. Views do not store data themselves but provide a way to present data in a specific format.

10. How do you use indexes to improve query performance in SQL?
    - Indexes are used to speed up data retrieval in SQL queries by creating an ordered list of values for one or more columns in a table. You can create indexes on columns frequently used in WHERE, JOIN, or ORDER BY clauses to improve query performance.

Hope it helps :)