55 SQL Interview Questions And Answers

Here is a list of 55 SQL interview questions for freshers and experienced candidates with brief answers for each. This will help you prepare thoroughly for your SQL interview.

Basic SQL Interview Questions

1. What is SQL?

   • Answer: SQL (Structured Query Language) is a language used for managing and manipulating relational databases. It is used to create, modify, query, and manage data in databases.

2. What are the different types of SQL statements?

   • Answer: SQL statements are categorized into:
     • DDL (Data Definition Language) – CREATE, ALTER, DROP
     • DML (Data Manipulation Language) – SELECT, INSERT, UPDATE, DELETE
     • DCL (Data Control Language) – GRANT, REVOKE
     • TCL (Transaction Control Language) – COMMIT, ROLLBACK, SAVEPOINT

3. What is the difference between DDL, DML, DCL, and TCL in SQL?

   • Answer:
     • DDL defines the structure (e.g., CREATE, ALTER).
     • DML handles data manipulation (e.g., SELECT, INSERT).
     • DCL controls access permissions (e.g., GRANT, REVOKE).
     • TCL manages transactions (e.g., COMMIT, ROLLBACK).

4. What is a Primary Key?

   • Answer: A Primary Key is a column (or a set of columns) in a table that uniquely identifies each row in that table. It cannot contain NULL values.

5. What is a Foreign Key?

   • Answer: A Foreign Key is a column in one table that uniquely identifies a row of another table. It is used to establish a link between the two tables.

6. What is a Unique Key?

   • Answer: A Unique Key is a column (or combination of columns) that ensures all values in that column are unique, but unlike Primary Key, it allows NULL values.

7. What is a Composite Key?

   • Answer: A Composite Key is a combination of two or more columns in a table that together uniquely identify a row.

8. What is a NULL value in SQL?

   • Answer: NULL represents a missing or undefined value. It is different from zero or an empty string.

9. What is the difference between WHERE and HAVING?

   • Answer:
     • WHERE is used to filter rows before grouping.
     • HAVING is used to filter groups after the GROUP BY clause.

10. What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?

    • Answer:
      • INNER JOIN returns matching rows from both tables.
      • LEFT JOIN returns all rows from the left table, and matching rows from the right table.
      • RIGHT JOIN returns all rows from the right table, and matching rows from the left table.
      • FULL JOIN returns all rows from both tables, matching where possible.

11. What is the difference between JOIN and UNION?

    • Answer:
      • JOIN is used to combine rows from two or more tables based on a related column.
      • UNION combines the results of two or more queries, removing duplicates.

12. What are Aggregate Functions in SQL? Provide examples.

    • Answer: Aggregate functions perform a calculation on a set of values and return a single value, such as:
      • COUNT(), SUM(), AVG(), MIN(), MAX()

13. What is the GROUP BY clause used for?

    • Answer: GROUP BY groups rows that have the same values into summary rows, like finding the sum or average of a column.

14. How does ORDER BY work in SQL?

    • Answer: ORDER BY sorts the result set in ascending or descending order based on one or more columns.

15. What are subqueries? Give an example.

    • Answer: A subquery is a query inside another query. Example:
      SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);


16. What is the difference between a subquery and a join?

    • Answer:
      • A subquery is a query within another query.
      • A join combines rows from two or more tables based on a related column.

17. What is the IN operator in SQL?

    • Answer: The IN operator is used to filter records that match any value in a list or subquery. Example:
      SELECT * FROM employees WHERE department IN ('HR', 'Finance');


18. What is the LIKE operator in SQL?

    • Answer: The LIKE operator is used to search for a specified pattern in a column. Example:
      SELECT * FROM employees WHERE name LIKE 'A%';


19. What is the BETWEEN operator in SQL?

    • Answer: The BETWEEN operator is used to filter the result set within a certain range. Example:
      SELECT * FROM products WHERE price BETWEEN 100 AND 500;


20. How do you use the DISTINCT keyword in SQL?

    • Answer: The DISTINCT keyword is used to return unique values from a column. Example:
      SELECT DISTINCT department FROM employees;


21. What are indexes in SQL? Why are they used?

    • Answer: Indexes are used to speed up the retrieval of data from a database by providing quick access to rows based on indexed columns.

22. What are the types of indexes in SQL?

    • Answer: The two primary types of indexes are:
      • Clustered Index: Reorganizes the data to follow the order of the index.
      • Non-Clustered Index: A separate structure from the data that points to the rows.

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

    • Answer:
      • UNION removes duplicates.
      • UNION ALL includes duplicates.

24. What is a view in SQL?

    • Answer: A view is a virtual table that consists of a stored query. It allows users to view data from one or more tables without modifying the actual data.

25. What is a stored procedure in SQL?

    • Answer: A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a unit.

26. What is a trigger in SQL?

    • Answer: A trigger is a set of SQL statements that automatically executes when a specified event occurs on a specified table or view.

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

    • Answer:
      • CHAR is a fixed-length data type.
      • VARCHAR is a variable-length data type.

28. What is the difference between DELETE, TRUNCATE, and DROP in SQL?

    • Answer:
      • DELETE removes records from a table but can be rolled back.
      • TRUNCATE removes all records from a table but cannot be rolled back.
      • DROP deletes a table or database permanently.

29. What is the purpose of the ALTER command in SQL?

    • Answer: The ALTER command is used to modify an existing database object (e.g., table) structure, such as adding or deleting columns.

30. What is the CASE statement in SQL?

    • Answer: The CASE statement allows conditional logic in SQL queries. Example:
      SELECT name,
CASE
WHEN salary > 5000 THEN 'High'
ELSE 'Low'
END as salary_range
FROM employees;


Intermediate SQL Interview Questions

31. What is the COALESCE function in SQL?

    • Answer: The COALESCE function returns the first non-null value in a list of expressions. Example:
      SELECT COALESCE(name, 'Unknown') FROM employees;


32. What is the NULLIF function in SQL?

    • Answer: The NULLIF function returns NULL if the two arguments are equal, otherwise, it returns the first argument. Example:
      SELECT NULLIF(salary, 0) FROM employees;


33. How do you find the total number of records in a table?

    • Answer:
      SELECT COUNT(*) FROM table_name;


34. What is normalization? Explain the different normal forms.

    • Answer: Normalization is the process of organizing data to reduce redundancy. The normal forms are:
      • 1NF: Eliminate duplicate columns.
      • 2NF: Eliminate partial dependencies.
      • 3NF: Eliminate transitive dependencies.
      • BCNF: A stricter version of 3NF.

35. What is denormalization?

    • Answer: Denormalization is the process of combining tables or adding redundant data to improve query performance, usually at the expense of data integrity.

36. What is the difference between a Clustered and Non-Clustered index?

    • Answer:
      • Clustered Index: Data is stored in the order of the index.
      • Non-Clustered Index: Data is stored separately from the index.

37. What are constraints in SQL? Give examples.

    • Answer: Constraints are rules applied to columns in a table to enforce data integrity. Examples include:
      • PRIMARY KEY
      • FOREIGN KEY
      • NOT NULL
      • CHECK

38. What is a transaction in SQL?

    • Answer: A transaction is a sequence of SQL operations that are executed as a single unit, ensuring data integrity.

39. What is ACID in SQL?

    • Answer: ACID stands for:
      • Atomicity: All operations are completed or none.
      • Consistency: Data is valid before and after the transaction.
      • Isolation: Transactions do not affect each other.
      • Durability: Changes are permanent after the transaction.

40. What is SELECT INTO in SQL?

    • Answer: SELECT INTO creates a new table and inserts the result of a query into it. Example:
      SELECT * INTO new_table FROM old_table;


Advanced SQL Interview Questions

41. What is query optimization in SQL?

    • Answer: Query optimization involves rewriting SQL queries to improve performance, like using indexes, avoiding subqueries, and reducing the number of joins.

42. What are database locks and how do they work in SQL?

    • Answer: Database locks prevent multiple transactions from conflicting with each other. Locks can be row-level, table-level, or database-level.

43. How do you implement a many-to-many relationship in SQL?

    • Answer: A many-to-many relationship is implemented using a junction table that contains foreign keys referencing the two tables.

44. What is partitioning in SQL? How is it used?

    • Answer: Partitioning divides a large table into smaller, more manageable pieces called partitions, usually for performance optimization.

45. Explain the concept of a Foreign Key constraint in SQL.

    • Answer: A Foreign Key is a column in a table that refers to the primary key in another table to establish a relationship.

46. What is a Database Trigger? Explain the types of triggers in SQL.

    • Answer: A trigger is a stored procedure that automatically executes when a specified event occurs (e.g., INSERT, UPDATE, DELETE).

47. How do you implement pagination in SQL queries?

    • Answer: Pagination can be implemented using LIMIT and OFFSET in SQL.
      SELECT * FROM employees LIMIT 10 OFFSET 20;


48. How can you perform a pivot operation in SQL?

    • Answer: Pivoting is performed using CASE statements or PIVOT operator to transform data rows into columns.

49. What is the MERGE statement in SQL?

    • Answer: The MERGE statement combines INSERT, UPDATE, and DELETE operations based on matching conditions.

50. What is the difference between RAID and Clustering in SQL databases?

    • Answer:
      • RAID (Redundant Array of Independent Disks) improves storage performance and redundancy.
      • Clustering is a method to group multiple database instances together for improved performance and availability.

51. What is the purpose of using TEMPORARY tables in SQL?

    • Answer: Temporary tables are used for storing intermediate results and are automatically dropped at the end of the session.

52. What is the difference between a Function and a Stored Procedure in SQL?

    • Answer:
      • A Function returns a single value and can be used in SQL expressions.
      • A Stored Procedure may or may not return a value and is typically used for executing multiple operations.

53. How do you check the database performance using SQL?

    • Answer: Performance can be checked using SQL Server tools like EXPLAIN, SQL Profiler, or Performance Monitor. Index optimization and query analysis can also help improve performance.

54. What are SQL injection attacks and how do you prevent them?

    • Answer: SQL injection is an attack where malicious SQL code is injected into a query. It can be prevented by using parameterized queries or prepared statements.

55. Explain the concept of Sharding in SQL databases.

    • Answer: Sharding is the process of distributing data across multiple servers to improve performance and scalability.