SQL Interview Questions and Answers

1. What is SQL?

  • SQL (Structured Query Language) is a standardized language used to manage and manipulate relational databases.
  • It allows users to perform tasks such as querying, updating, inserting, and deleting data, as well as creating and modifying database structures.

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

  • INNER JOIN returns only the rows that have matching values in both tables.
  • OUTER JOIN includes rows that do not have matching values in both tables.
  • It can be further classified into:
    • LEFT JOIN (or LEFT OUTER JOIN): returns all rows from the left table and the matched rows from the right table. If no match is found, NULL values are returned for columns from the right table.
    • RIGHT JOIN (or RIGHT OUTER JOIN): returns all rows from the right table and the matched rows from the left table. If no match is found, NULL values are returned for columns from the left table.
    • FULL JOIN (or FULL OUTER JOIN): returns rows when there is a match in one of the tables. If there is no match, the result is NULL on the side without a match.

3. What are primary keys and foreign keys?

  • A primary key is a unique identifier for a record in a table, ensuring that no two rows have the same key. It uniquely identifies each record in a database table.
  • A foreign key is a field in one table that uniquely identifies a row of another table. The foreign key establishes a relationship between the two tables, ensuring referential integrity of the data.

4. Explain the difference between WHERE and HAVING clauses.

  • The WHERE clause is used to filter rows before any groupings are made. It is used in SELECT, UPDATE, DELETE statements.
  • The HAVING clause is used to filter groups after the GROUP BY clause has been applied. It is used in conjunction with GROUP BY to filter out groups based on aggregate functions.

5. What is normalization?

  • Normalization is the process of organizing data in a database to minimize redundancy and improve data integrity.
  • It involves dividing a database into two or more tables and defining relationships between them.
  • The goal is to ensure that each table contains data related to a single subject or theme.

6. What are indexes and how do they improve query performance?

  • Indexes are special data structures that store a small portion of the table’s data in an easy-to-search format.
  • They improve query performance by allowing the database to find rows more quickly than searching the entire table.
  • Indexes can be created on one or more columns of a table to speed up retrieval of rows.

7. Explain ACID properties.

  • Atomicity: Ensures that all operations within a transaction are completed successfully; if not, the transaction is aborted.
  • Consistency: Ensures that a transaction brings the database from one valid state to another.
  • Isolation: Ensures that the operations of one transaction are isolated from those of other transactions.
  • Durability: Ensures that once a transaction is committed, it remains so, even in the event of a system failure.

8. What is a view in SQL?

  • A view is a virtual table based on the result set of an SQL query.
  • It can contain rows and columns, just like a real table, and is used to simplify complex queries, encapsulate logic, and restrict access to certain data.
  • Views do not store data physically but display data stored in other tables.

9. What is a stored procedure?

  • A stored procedure is a set of SQL statements that can be stored in the database and executed as a single program.
  • Stored procedures can accept parameters, perform complex operations, and return results.
  • They are used to encapsulate repetitive tasks, ensure consistent implementation of logic, and improve performance.

10. What is the difference between DELETE, TRUNCATE, and DROP statements?

  • DELETE: Removes rows from a table based on a condition. Each row deletion is logged, and it can be rolled back if within a transaction.
  • TRUNCATE: Removes all rows from a table without logging individual row deletions. It is faster than DELETE and cannot be rolled back once executed.
  • DROP: Deletes the entire table (or database), including its structure and data. It is a DDL statement and cannot be rolled back once executed.

Related articles

Famous Interviews