Top 35 SQL Interview Questions and Answers 2024

SQL Interview Questions and Answers

If you are Preparing for SQL Interview and looking for SQL Interview Questions with Answers then you are at the right place. Here, you will get 35+ SQL interview questions with detailed answers. Covering basics to advanced Questions, these Questions help you Revise Concepts and deep your SQL knowledge to boost your interview confidence.

SQL, or Structured Query Language, is a standardized programming language used for managing and manipulating relational databases. It allows users to create, read, update, and delete data within a database. Preparing for SQL interviews involves understanding key concepts, practising queries, and familiarizing yourself with common questions.

Basic SQL Interview Questions:

Q1. What is SQL?

SQL, or Structured Query Language, is a programming language designed for managing data in relational database management systems (RDBMS).

Q2. Explain the difference between SQL and MySQL.

The Difference between SQL and MySQL are:

Q3. What are the different types of SQL statements?

SQL statements can be broadly categorized into several types based on their purpose and functionality:

1. Data Definition Language (DDL):
   • CREATE: Creates database objects like tables, views, indexes, etc.
   • ALTER: Modifies the structure of existing database objects.
   • DROP: Deletes database objects like tables, views, indexes, etc.
   • TRUNCATE: Deletes all records from a table, but keeps the table structure.
2. Data Manipulation Language (DML):
   • SELECT: Retrieves data from a database.
   • INSERT: Adds new records into a table.
   • UPDATE: Modifies existing records in a table.
   • DELETE: Removes records from a table.
3. Data Control Language (DCL):
   • GRANT: Gives user access privileges to database objects.
   • REVOKE: Withdraws user access privileges granted with the GRANT command.
4. Transaction Control Language (TCL):
   • COMMIT: Saves all transactions to the database.
   • ROLLBACK: Reverts the database to its state before the start of the transaction.
   • SAVEPOINT: Sets a point within a transaction to which you can later roll back.

Q4. What is a primary key in SQL?

In SQL, a primary key is a column or a set of columns that uniquely identifies each row in a table.

Q5. How do you select all records from a table in SQL?

To select all records from a table in SQL, you use the SELECT statement without any conditions. Here’s how you would do it:

SELECT * FROM table_name;

Q6. Explain the use of the WHERE clause in SQL.

The WHERE clause in SQL is used to filter records returned by a SELECT, UPDATE, DELETE, or INSERT statement based on specified conditions. Here’s how it works and its key features:

• Filtering Rows: The WHERE clause is placed after the FROM clause in a SELECT statement and specifies a condition that rows must satisfy to be included in the result set.
• Syntax: The basic syntax of a SELECT statement with a WHERE clause is:

SELECT column1, column2, ...
FROM table_name
WHERE condition;

• Conditions: Conditions in the WHERE clause are typically composed of comparisons using operators such as =, !=, <, >, <=, >=, LIKE, IN, BETWEEN, etc. You can also use logical operators like AND, OR, and NOT to combine conditions.

Q7. What is a foreign key in SQL?

In SQL, a foreign key is a column or a set of columns in one table that references the primary key (or a unique key) of another table.

Q8. How do you delete records from a table in SQL?

To delete records from a table in SQL:

1. Use the DELETE statement.
2. Specify the table from which you want to delete records.
3. Optionally, use a WHERE clause to specify which records to delete based on certain conditions.

Q9. What is the difference between DELETE and TRUNCATE commands?

The difference between DELETE and TRUNCATE commands in SQL are:

Q10. Explain the concept of normalization in databases.

Normalization in databases is the process of organizing data to minimize redundancy and dependency by splitting large tables into smaller, related tables and defining relationships between them. Its goal is to improve data integrity and reduce anomalies like redundancy and inconsistency.

Intermediate SQL Interview Questions:

Q11. What is an SQL JOIN? Explain different types of SQL JOINs.

An SQL JOIN combines rows from two or more tables based on a related column between them.

1. INNER JOIN: Returns rows when there is a match in both tables.
2. LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
3. RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
4. FULL JOIN: Returns rows when there is a match in either table.
5. SELF JOIN: Joins a table to itself, useful for hierarchical data.

Q12. How do you use GROUP BY and HAVING clauses in SQL?

1. GROUP BY clause: It groups rows that have the same values into summary rows, typically to apply aggregate functions (like COUNT, SUM, AVG) to each group.

Example:

SELECT department, COUNT(employee_id)
FROM employees
GROUP BY department;

1. HAVING clause: It filters groups based on a specified condition, similar to the WHERE clause but used with aggregate functions.

Example:

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

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

The difference between INNER JOIN and OUTER JOIN are:

Q14. Explain the difference between UNION and UNION ALL in SQL.

Key Points:

• UNION: Combines the result sets of two or more SELECT statements and removes duplicates.
• UNION ALL: Combines the result sets of two or more SELECT statements and includes all rows, including duplicates.

Q15. How do you use subqueries in SQL?

To use subqueries in SQL:

1. Selecting Data: Write the main query where you want to use a subquery.
2. Embedding Subquery: Enclose the subquery within parentheses and place it where you would normally put a value or condition.
3. Syntax: Ensure the subquery returns a single value or set of values compatible with the operator or function you’re using.

Q16. What is the purpose of the ORDER BY clause in SQL?

The Purpose of ORDER BY clause in SQL is to sort the result set returned by a query in ascending or descending order based on one or more columns. Its primary purposes are:

1. Sorting: Arrange the rows in a specified order (ascending or descending) based on the values in one or more columns.
2. Customization: Allow flexibility in sorting criteria, such as sorting alphabetically, numerically, or by date.
3. Pagination: Facilitate pagination by combining with LIMIT or OFFSET clauses to retrieve a subset of ordered results.

Q17. Explain the concept of indexes in databases.

In databases, indexes are data structures that optimize the retrieval of records from a table. They work like a book’s index, allowing quick access to specific data based on key columns.

Q18. How do you create a new table in SQL?

To create a new table in SQL, you use the CREATE TABLE statement followed by the table name and column definitions. Here’s a basic example:

CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    ...
);

Replace TableName, Column1, Column2, and DataType with your actual table name, column names, and respective data types.

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

The main difference between CHAR and VARCHAR data types in SQL lies in how they store data:

In summary, CHAR is used for fixed-length data where the length is always the same, while VARCHAR is used for variable-length data where the length may vary.

Q20. How do you handle NULL values in SQL?

In SQL, you handle NULL values using the IS NULL and IS NOT NULL operators to check if a column contains NULL values or not. When querying, you can use functions like COALESCE() or ISNULL() (depending on the SQL dialect) to replace NULL values with another specified value. It’s important to handle NULLs appropriately to avoid unexpected query results or errors.

Q21. What is a stored procedure? How do you create and execute it in SQL?

A stored procedure in SQL is a precompiled collection of SQL statements stored under a name and executed as a single unit. Here’s how you create and execute a stored procedure in SQL:

Creating a Stored Procedure:

CREATE PROCEDURE procedure_name
AS
BEGIN
    -- SQL statements
END;

Executing a Stored Procedure:

EXEC procedure_name;

Stored procedures help modularize SQL code, improve performance, and enhance security by controlling access to data and operations.

Q22. How do you perform transactions in SQL?

In SQL, transactions ensure that a group of SQL operations are treated as a single unit of work that either completes entirely or fails entirely (ACID properties). Here’s how you perform transactions in SQL:

1. Begin a Transaction: BEGIN TRANSACTION;
2. Execute SQL Statements: Perform your SQL operations (e.g., INSERT, UPDATE, DELETE, etc.) within the transaction scope.
3. Commit the Transaction (if successful): COMMIT TRANSACTION; This commits all changes made within the transaction to the database.
4. Rollback the Transaction (if unsuccessful or to undo changes): ROLLBACK TRANSACTION; This undoes all changes made within the transaction since the BEGIN TRANSACTION.

Example:

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountNumber = 123;

INSERT INTO TransactionHistory (AccountNumber, Amount, TransactionType)
VALUES (123, 100, 'Withdrawal');

COMMIT TRANSACTION;

Transactions are crucial for maintaining data integrity and consistency, especially in complex database operations involving multiple updates or inserts.

Q23. Explain the difference between ROLLBACK and COMMIT commands.

The difference between ROLLBACK and COMMIT commands are:

In summary, COMMIT finalizes changes, while ROLLBACK cancels them.

Q24. What is the purpose of the DISTINCT keyword in SQL?

The DISTINCT keyword in SQL serves the purpose of ensuring that the result set of a query only contains unique rows. It filters out duplicate rows from the result set based on the specified columns in the SELECT statement.

• Purpose:
  • Ensure Uniqueness: It removes duplicate rows from the query result.
  • Filtering: Applies uniqueness based on the specified columns.

Q25. How do you use the CASE statement in SQL?

The CASE statement in SQL is used to provide conditional logic within a SQL query. It allows you to perform different actions based on different conditions. Here’s how you can use it:

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE else_result
END

• CASE: Begins the CASE statement.
• WHEN condition THEN result: Checks each condition sequentially and returns the corresponding result if the condition is true.
• ELSE else_result: Optional. Provides a default result if none of the conditions are true.
• END: Ends the CASE statement.

Notes:

• CASE statements can be nested within each other for more complex conditions.
• CASE statements can also be used in the UPDATE and INSERT statements to conditionally modify or insert data based on specific criteria.

This flexibility makes the CASE statement a powerful tool for conditional logic in SQL queries.

Advanced SQL Interview Questions:

Q26. Explain the concept of ACID properties in SQL transactions.

ACID properties in SQL transactions ensure reliability and consistency:

1. Atomicity: Transactions are treated as a single unit, either fully completed or fully rolled back.
2. Consistency: Transactions bring the database from one consistent state to another.
3. Isolation: Transactions operate independently of and unaware of each other’s existence.
4. Durability: Once a transaction is committed, changes are permanent even in case of system failures.

These properties ensure reliable and secure data transactions in SQL databases.

Q27. What are triggers in SQL? How do you use them?

Triggers in SQL are special stored procedures that automatically execute in response to specific events on a table or view. Here’s how you use them:

1. Creating Triggers: Define a trigger using CREATE TRIGGER followed by the trigger name, event (like INSERT, UPDATE, DELETE), and action (SQL statements to execute).
2. Event Types: Triggers can be fired BEFORE or AFTER the triggering event.
3. Example Usage: A trigger can be set to update a timestamp (BEFORE INSERT), enforce business rules (AFTER UPDATE), or maintain data integrity (AFTER DELETE).
4. Syntax Example:

CREATE TRIGGER trigger_name
BEFORE INSERT ON table_name
FOR EACH ROW
BEGIN
    -- SQL statements
END;

Triggers help automate actions based on database events, improving data consistency and application efficiency.

Q28. How do you optimize SQL queries for better performance?

To optimize SQL queries for better performance:

1. Use Indexes: Identify and apply indexes to columns frequently used in WHERE, JOIN, and ORDER BY clauses.
2. Limit Returned Data: Fetch only necessary columns and rows using SELECT and WHERE clauses.
3. *Avoid SELECT ***: Explicitly specify required columns to avoid unnecessary data retrieval.
4. Use Joins Carefully: Prefer INNER JOIN over OUTER JOIN when possible, and avoid Cartesian joins.
5. Optimize Subqueries: Rewrite subqueries to use efficient JOIN operations.
6. Avoid Cursors: Use set-based operations instead of cursors for better performance.
7. Update Statistics: Regularly update table statistics to help the query optimizer generate efficient execution plans.
8. Avoid Nested Queries: Simplify complex queries by breaking them into simpler parts.
9. Use EXISTS Instead of IN: Prefer EXISTS clause over IN clause for better performance with large datasets.
10. Consider Query Execution Plan: Analyze and optimize query execution plans using EXPLAIN (for MySQL) or equivalent tools.

Implementing these strategies can significantly enhance SQL query performance.

Q29. Explain the concept of query optimization in SQL.

Query optimization in SQL refers to the process of improving the performance of SQL queries by choosing the most efficient way to execute them. The goal is to minimize the query execution time and resource consumption while maximizing throughput.

Q30. What are CTEs (Common Table Expressions) in SQL? How do you use them?

CTEs (Common Table Expressions) in SQL are temporary result sets that are defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. They provide a way to write more readable and maintainable SQL queries, especially for complex queries that involve subqueries or self-joins.

Syntax of CTEs:

WITH cte_name (column1, column2, ...) AS (
    -- CTE query definition
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
)
-- Main query that uses the CTE
SELECT *
FROM cte_name;

Explanation:

• WITH: This keyword introduces the CTE.
• cte_name: The name assigned to the CTE.
• column1, column2, …: Optional column list specifying the columns in the CTE.
• AS: Indicates the start of the CTE query definition.
• SELECT: The query that defines the CTE.
• FROM table_name: The table from which data is selected for the CTE.
• WHERE condition: Optional condition to filter rows in the CTE query.

Using CTEs enhances SQL query readability, simplifies maintenance, and can improve performance by optimizing query execution plans.

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

A clustered index determines the physical order of data rows in a table based on the indexed column’s values. In contrast, a non-clustered index creates a separate structure with pointers to the data rows, leaving the physical order of rows unchanged.

Q32. How do you handle concurrency in SQL?

Concurrency in SQL is managed through techniques like locking, isolation levels, and transactions. Use locking to control access to data, choose appropriate isolation levels to balance consistency and performance, and use transactions to group operations into atomic units to maintain data integrity.

Q33. Explain the concept of materialized views in SQL.

Materialized views in SQL are precomputed result sets stored as physical database objects. They capture the result of a query, enabling faster access and reduced computation when querying the same data frequently. Materialized views are refreshed periodically to reflect changes in underlying data, providing significant performance benefits for complex queries and analytics tasks.

Q34. What are recursive queries in SQL?

Recursive queries in SQL, often implemented with Common Table Expressions (CTEs), allow querying hierarchical data structures. They enable a query to reference its own output, iterating over the result set until a specific condition is met, useful for tasks like navigating tree structures or organizational hierarchies.

Q35. How do you use the PIVOT and UNPIVOT operators in SQL?

In SQL, the PIVOT and UNPIVOT operators are used for transforming data between row and column formats:

1. PIVOT: Transforms unique values from a column into multiple columns in the output, aggregating data if necessary. It involves specifying the values to pivot on, the aggregation function (if needed), and the new column headers.

Example:-

SELECT *
FROM (
    SELECT category, amount
    FROM sales
) AS SourceTable
PIVOT (
    SUM(amount)
    FOR category IN ([Category1], [Category2], [Category3])
) AS PivotTable;

1. UNPIVOT: Converts columns into rows. It works by specifying which columns to unpivot and optionally renaming the resulting columns.

Example:-

SELECT product, category, amount
FROM (
    SELECT *
    FROM sales
) AS SourceTable
UNPIVOT (
    amount FOR category IN ([Category1], [Category2], [Category3])
) AS UnpivotTable;

These operators are useful for dynamic data analysis and reporting where data transformation between wide and narrow formats is necessary.

SQL Interview Questions FAQs