SQL Interview Questions

SQL, or Structured Query Language, is a powerful domain-specific language used for managing and manipulating relational databases. It serves as a standard interface for interacting with databases, allowing users to define, query, and manipulate data within a relational database management system (RDBMS). SQL is designed to be both declarative and procedural, providing a flexible and efficient means of handling various database operations.

SQL is characterized by its ability to perform a wide range of tasks, including data retrieval with SELECT statements, data modification with INSERT, UPDATE, and DELETE statements, and schema definition with CREATE, ALTER, and DROP statements. It enables users to define the structure of a database, create tables to store data, establish relationships between tables, and enforce data integrity through constraints. SQL’s versatility makes it a fundamental tool for database administrators, developers, and analysts who need to interact with and manage data in a structured, organized manner within relational databases.

SQL Interview Questions For Freshers

1. What is SQL?

SQL stands for Structured Query Language. It is a domain-specific language used for managing and manipulating relational databases.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT
);

2. Differentiate between SQL and MySQL?

SQL is a language used for managing databases, while MySQL is an open-source relational database management system (RDBMS) that uses SQL as its query language.

3. Explain the SELECT statement?

The SELECT statement is used to retrieve data from one or more tables in a database. It allows you to specify the columns you want to retrieve and the conditions that must be met for the rows to be included in the result set.

4. What is the purpose of the WHERE clause?

The WHERE clause is used in SQL queries to filter the rows returned by a SELECT statement based on a specified condition or set of conditions.

5. Describe the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only the rows where there is a match in both tables, while LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for columns from the right table.

6. What is normalization?

Normalization is the process of organizing data in a database to reduce redundancy and dependency by dividing large tables into smaller, related tables.

7. Explain the primary key?

A primary key is a unique identifier for a record in a table. It ensures the uniqueness of each row and is used to establish relationships between tables.

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

CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type. CHAR pads values with spaces to the specified length, while VARCHAR only stores the actual characters entered.

CHAR example:

-- Create a table with a CHAR column
CREATE TABLE sample_table (
    id INT PRIMARY KEY,
    name CHAR(10)
);

-- Insert data into the table
INSERT INTO sample_table (id, name) VALUES
(1, 'John'),
(2, 'Jane'),
(3, 'Bob');

-- Retrieve data from the table
SELECT * FROM sample_table;

VARCHAR Example:

-- Create a table with a VARCHAR column
CREATE TABLE sample_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Insert data into the table
INSERT INTO sample_table (id, name) VALUES
(1, 'John Doe'),
(2, 'Jane Smith'),
(3, 'Bob Johnson');

-- Retrieve data from the table
SELECT * FROM sample_table;

9. What is an index, and why is it important?

An index is a database object that improves the speed of data retrieval operations on a table. It is important because it allows the database engine to locate and access the rows more quickly.

10. 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 the two tables, enforcing referential integrity and creating a relationship between them.

11. Explain the GROUP BY clause?

The GROUP BY clause is used in SQL to group rows that have the same values in specified columns. It is often used in conjunction with aggregate functions like COUNT, SUM, AVG, etc., to perform operations on each group of rows.

12. What is the purpose of the HAVING clause?

The HAVING clause is used in SQL queries to filter the results of a GROUP BY clause based on a specified condition. It is similar to the WHERE clause but is applied after the grouping has occurred.

13. Differentiate between UNION and UNION ALL?

UNION is used to combine the results of two or more SELECT statements, and it removes duplicate rows from the result set. The columns in the SELECT statements must be in the same order, have the same data types, and the number of columns must be the same.

-- Combine and get unique employees from both tables
SELECT employee_id, first_name, last_name
FROM employees_2022

UNION

SELECT employee_id, first_name, last_name
FROM employees_2023;

UNION ALL is also used to combine the results of two or more SELECT statements, but it includes all rows, even if they are duplicates. Unlike UNION, UNION ALL does not remove duplicate rows from the result set.

-- Combine and get all employees (including duplicates) from both tables
SELECT employee_id, first_name, last_name
FROM employees_2022

UNION ALL

SELECT employee_id, first_name, last_name
FROM employees_2023;

14. Explain the concept of ACID properties in the context of databases?

ACID (Atomicity, Consistency, Isolation, Durability) properties ensure the reliability of database transactions. Atomicity ensures that transactions are treated as a single, indivisible unit; Consistency maintains the integrity of the database; Isolation ensures transactions are independent of each other, and Durability guarantees that committed transactions persist even after system failures.

15. What is a subquery?

A subquery is a query nested within another query. It can be used to retrieve data that will be used in the main query’s conditions or to perform calculations. Subqueries can appear in the SELECT, FROM, WHERE, or HAVING clauses.

16. Explain the purpose of the ORDER BY clause?

The ORDER BY clause is used to sort the result set of a query in ascending or descending order based on one or more columns. It is often used with the SELECT statement to present data in a specific order.

17. What is the difference between a stored procedure and a function?

A stored procedure is a set of SQL statements that can be executed, while a function returns a value. Functions can be used in SELECT statements, whereas stored procedures are called using the EXECUTE statement.

18. What is the purpose of the NULL value in databases?

NULL represents the absence of a value in a database. It is used to indicate that a data field has no assigned value. Columns can be defined to allow or disallow NULL values.

19. How do you update data in a table using SQL?

You can update data in a table using the UPDATE statement. For example:

-- Original data in the employees table
SELECT * FROM employees;

-- Update the salary for employee with employee_id 1
UPDATE employees
SET salary = 75000
WHERE employee_id = 1;

-- Verify the updated data
SELECT * FROM employees;

20. How do you add a new record to a table?

You can add a new record to a table using the INSERT statement.For example:

-- Original data in the employees table
SELECT * FROM employees;

-- Insert a new record into the employees table
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (4, 'Alice', 'Johnson', 60000);

-- Verify the updated data
SELECT * FROM employees;

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

The DELETE statement is used to delete specific rows from a table based on a condition. It is more flexible than TRUNCATE because it allows you to specify a condition for which rows to delete.

-- Original data in the employees table
SELECT * FROM employees;

-- Delete a specific record from the employees table
DELETE FROM employees
WHERE employee_id = 3;

-- Verify the updated data
SELECT * FROM employees;

The TRUNCATE statement is used to remove all rows from a table. Unlike DELETE, it does not use a condition, and it is generally faster because it deallocates the data pages used by the table. However, TRUNCATE cannot be used if the table is referenced by a foreign key constraint.

-- Original data in the employees table
SELECT * FROM employees;

-- Truncate all records from the employees table
TRUNCATE TABLE employees;

-- Verify the updated data (empty table)
SELECT * FROM employees;

22. What is a stored procedure?

A stored procedure is a precompiled collection of one or more SQL statements that can be executed with a single call. It is stored in the database and can be reused, reducing the need to rewrite the same code.

23. Explain the concept of indexing?

Indexing is a database optimization technique that improves the speed of data retrieval operations on a table. It creates a data structure that allows the database engine to locate and access rows more efficiently.

24. What is a SQL injection, and how can it be prevented?

SQL injection is a security vulnerability where an attacker can insert malicious SQL code into a query. It can be prevented by using parameterized queries or prepared statements, which ensure that user input is treated as data and not executable code.

25. Explain the concept of a self-join?

A self-join is a regular join, but the table is joined with itself. It is often used when a table has a hierarchical structure, and you need to relate rows within the same table.

Sql Interview Questions For 3 Years Experience

1. Explain the purpose of an index and how it improves query performance?

An index is a database structure that improves the speed of data retrieval operations on a table. It works by creating a sorted data structure, allowing the database engine to quickly locate and access the rows that meet the search criteria, thus enhancing query performance.

2. What are the differences between INNER JOIN, LEFT JOIN, and RIGHT JOIN?

INNER JOIN returns only the matching rows from both tables, LEFT JOIN returns all rows from the left table and the matching rows from the right table, and RIGHT JOIN returns all rows from the right table and the matching rows from the left table.

3. Explain the concept of a transaction in the context of a database?

A transaction is a sequence of one or more SQL statements that are executed as a single unit. It follows the ACID properties (Atomicity, Consistency, Isolation, Durability) to ensure the reliability of database operations even in the presence of failures.

4. How do you optimize a SQL query for better performance?

Optimization techniques include creating indexes, using appropriate JOIN types, avoiding SELECT *, optimizing WHERE clauses, and analyzing the query execution plan.

5. What is a correlated subquery?

A correlated subquery is a subquery that refers to columns of the outer query, making the subquery dependent on the outer query’s results. It executes once for each row processed by the outer query.

6. How can you handle NULL values in SQL?

NULL represents the absence of a value. You can use the IS NULL or IS NOT NULL conditions in WHERE clauses, or use the COALESCE or ISNULL functions to handle NULL values in queries.

7. Describe the purpose of the GROUP BY and HAVING clauses?

GROUP BY is used to group rows based on specified columns, and HAVING is used to filter the results of a GROUP BY based on a specified condition.

8. Explain the purpose of the stored procedure and when you would use one?

A stored procedure is a precompiled collection of one or more SQL statements. It is used for encapsulating business logic, improving code reusability, and enhancing security by controlling access to data.

9. What is the purpose of the SQL CASE statement?

The CASE statement is used to perform conditional logic in SQL queries. It allows you to define different outcomes based on specified conditions.

10. How can you ensure data integrity in a database?

Data integrity can be ensured through the use of constraints (such as primary and foreign keys), normalization, and transactions that follow the ACID properties.

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

A clustered index determines the physical order of data in a table. The data rows in the table are stored on disk in the same order as the index. A table can have only one clustered index, and it directly affects the order of the data on disk.

-- Create a clustered index on the employee_id column
CREATE CLUSTERED INDEX idx_employee_id
ON employees (employee_id);

A non-clustered index does not affect the physical order of data on disk. Instead, it creates a separate structure that includes a sorted list of references to the actual data rows. A table can have multiple non-clustered indexes.

-- Create a non-clustered index on the last_name column
CREATE NONCLUSTERED INDEX idx_last_name
ON employees (last_name);

12. Explain the concept of normalization and denormalization?

Normalization is the process of organizing data to minimize redundancy and dependency, whereas denormalization involves combining tables or introducing redundancy to improve query performance.

13. How do you find and remove duplicate rows in a table?

Duplicate rows can be identified using the DISTINCT keyword or GROUP BY clause. To remove duplicates, you can use the DELETE statement with a self-join or utilize the ROW_NUMBER() window function.

14. What is a view, and how is it different from a table?

A view is a virtual table based on the result of a SELECT query, while a table is a physical storage structure. Views do not store data but provide a way to represent the data stored in one or more tables.

15. How can you backup and restore a database in SQL Server?

Backing up a database can be done using the BACKUP DATABASE command, and restoring is performed using the RESTORE DATABASE command. Management Studio and T-SQL commands can be used for these operations.

16. Explain the concept of a trigger?

A trigger is a set of instructions that are automatically executed (or “triggered”) in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table. Triggers can be used to enforce business rules or maintain data integrity.

17. How do you handle errors in SQL procedures or queries?

Errors can be handled using TRY…CATCH blocks in stored procedures, where potential errors are wrapped in a TRY block, and specific error handling is done in a CATCH block.

18. What is the purpose of the SQL Server Agent?

SQL Server Agent is a component of Microsoft SQL Server that automates administrative tasks, such as database backups, job scheduling, and monitoring.

Sql Interview Questions For 5 Years Experience

1. Explain the purpose and benefits of using stored procedures?

Stored procedures offer encapsulation of business logic, enhancing code modularity and security. They reduce network traffic by executing a single procedure call rather than multiple SQL statements.

2. Describe the differences between a primary key and a unique key constraint?

Both ensure uniqueness, but a primary key also enforces not null, and there can be only one per table. Unique key constraints allow null values, and multiple unique keys can exist in a table.

3. What are window functions in SQL, and provide an example of their usage?

Window functions perform calculations across a specified range of rows related to the current row. Example:

-- Sample data in the sales table
CREATE TABLE sales (
    product_id INT,
    sale_date DATE,
    amount DECIMAL(10, 2)
);

INSERT INTO sales (product_id, sale_date, amount)
VALUES
    (1, '2022-01-01', 100.50),
    (1, '2022-01-02', 150.25),
    (2, '2022-01-01', 75.60),
    (2, '2022-01-03', 200.00),
    (2, '2022-01-05', 120.75);

-- Using ROW_NUMBER() window function to assign a unique row number within each product category based on the sale date
SELECT
    product_id,
    sale_date,
    amount,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num
FROM
    sales;

4. Explain the concept of a foreign key and its role in maintaining referential integrity?

A foreign key is a column or set of columns in a table that refers to the primary key of another table. It enforces referential integrity by ensuring that values in the foreign key column match existing values in the referenced table’s primary key.

5. What is the purpose of the SQL Server Execution Plan?

The Execution Plan is a detailed map of how a SQL query will be executed, showing the operations, their sequence, and the access methods. It’s crucial for analyzing and optimizing query performance.

6. How do you optimize a query involving multiple joins on large tables?

Use proper indexing, consider denormalization for frequently used queries, and analyze and understand the query execution plan. Additionally, ensure statistics are up-to-date and use appropriate join types.

7. Explain the purpose of the SQL Server Profiler tool?

SQL Server Profiler is used to capture and analyze SQL Server events, helping to diagnose performance issues, monitor queries, and identify areas for optimization.

8. What is the purpose of the NOLOCK hint in SQL Server?

NOLOCK is a query hint that allows reading uncommitted data, providing a potential performance gain by avoiding locking resources. However, it can lead to reading inconsistent or dirty data.

9. How can you implement paging in a SQL query?

Implementing paging in a SQL query is commonly done using the OFFSET and FETCH clauses, which are part of the SQL standard introduced in SQL:2008. These clauses allow you to skip a certain number of rows and fetch a specified number of rows, enabling easy implementation of pagination.

-- Assuming page number is 2 and 10 rows per page
DECLARE @PageNumber INT = 2;
DECLARE @PageSize INT = 10;

-- Calculate OFFSET and FETCH values
DECLARE @Offset INT = (@PageNumber - 1) * @PageSize;

-- Query using OFFSET and FETCH for paging
SELECT employee_id, first_name, last_name
FROM employees
ORDER BY employee_id -- Specify the ordering column
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY;

10. Explain the concept of an execution plan cache?

The execution plan cache stores compiled and optimized query plans, reducing the need for recompilation when the same query is executed multiple times, improving performance.

11. What is the purpose of the SQL Server Agent Job and how can you schedule a job?

SQL Server Agent Jobs automate administrative tasks. To schedule a job, use SQL Server Management Studio or T-SQL commands like sp_add_job and sp_add_schedule.

12. How do you handle slow-performing queries, and what tools would you use for performance tuning?

Identify bottlenecks using tools like SQL Server Profiler, Database Engine Tuning Advisor, and Execution Plans. Optimize queries, ensure proper indexing, and analyze statistics.

13. Explain the concept of database normalization and denormalization?

Normalization is the process of organizing data to reduce redundancy and dependency. Denormalization involves introducing redundancy to improve query performance.

14. What is the purpose of the WITH clause in SQL, and how does it differ from a subquery?

The WITH clause, also known as Common Table Expressions (CTE), provides a named temporary result set for use within a larger query. It enhances code readability and reusability compared to subqueries.

15. How do you handle transactions spanning multiple SQL statements?

Use the BEGIN TRANSACTION, COMMIT, and ROLLBACK statements. Ensure proper error handling and use SAVEPOINTs for more granular control within a transaction.

16. Explain the concept of an “execution plan” and why it is essential for performance optimization?

An execution plan is a detailed map of the operations SQL Server performs to execute a query. It is crucial for performance optimization as it helps identify resource-intensive operations and optimize them for efficiency.

17. What is the purpose of the MERGE statement in SQL, and example code?

The MERGE statement in SQL is used to perform an “upsert” operation, combining the functionality of both INSERT and UPDATE. It allows you to conditionally insert new rows, update existing rows, or delete rows from a target table based on a specified condition. This is particularly useful when dealing with data synchronization between source and target tables.

-- Sample data in source_table
CREATE TABLE source_table (
    id INT PRIMARY KEY,
    value VARCHAR(50)
);

INSERT INTO source_table (id, value)
VALUES
    (1, 'Value 1'),
    (2, 'Value 2'),
    (3, 'Value 3');

-- Sample data in target_table
CREATE TABLE target_table (
    id INT PRIMARY KEY,
    value VARCHAR(50)
);

INSERT INTO target_table (id, value)
VALUES
    (1, 'Existing Value 1'),
    (3, 'Existing Value 3'),
    (4, 'Existing Value 4');

-- Using MERGE to synchronize data
MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
    UPDATE SET target.value = source.value
WHEN NOT MATCHED BY TARGET THEN
    INSERT (id, value) VALUES (source.id, source.value)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- Verify the updated data in target_table
SELECT * FROM target_table;

18. Explain the concept of database sharding and its advantages?

Database sharding involves horizontally partitioning a database into smaller, more manageable pieces. It improves scalability, performance, and distribution of data across multiple servers.

19. What is the purpose of the SQL Server Agent and how can it be used for job scheduling?

SQL Server Agent is a tool used to automate administrative tasks, such as job scheduling, alerting, and notifications. It uses jobs to execute sets of predefined steps.

20. How can you improve the performance of a query containing multiple OR conditions in the WHERE clause?

Rewrite the query using UNION or UNION ALL, utilize indexing on the columns involved, and consider creating a filtered index for specific conditions.

Key Aspects Of SQL

SQL, or Structured Query Language, is a domain-specific programming language used for managing and manipulating relational databases. It provides a standardized way to interact with databases, allowing users to perform various operations such as querying, updating, and managing data. Here are key aspects of SQL:

Basic Syntax: SQL commands are written in a structured format. Common commands include SELECT for querying data, INSERT for adding new records, UPDATE for modifying existing records, and DELETE for removing records.

Data Querying: The primary purpose of SQL is to retrieve data from relational databases. The SELECT statement is used for querying data, and it allows users to specify conditions, filter results, and sort data.Example:

SELECT column1, column2 FROM table_name WHERE condition;

Data Modification: SQL provides commands to modify data within a database. The INSERT statement adds new records, the UPDATE statement modifies existing records, and the DELETE statement removes records. Example:

INSERT INTO table_name (column1, column2) VALUES (value1, value2); UPDATE table_name SET column1 = new_value WHERE condition; DELETE FROM table_name WHERE condition;

Data Definition: SQL is used to define and manage the structure of databases. The CREATE statement is used to create tables, indexes, and other database objects. The ALTER statement is used to modify existing structures, and the DROP statement is used to delete them. Example:

CREATE TABLE table_name (column1 datatype, column2 datatype, ...); ALTER TABLE table_name ADD COLUMN new_column datatype; DROP TABLE table_name;

Data Integrity and Constraints: SQL supports the enforcement of data integrity through constraints. Common constraints include primary keys, foreign keys, unique constraints, and check constraints. Example:

CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), department_id INT, FOREIGN KEY (department_id) REFERENCES departments(department_id) );

Transaction Control: SQL provides commands for managing transactions, ensuring that a series of operations are treated as a single unit. This includes the COMMIT statement to save changes, the ROLLBACK statement to undo changes, and the SAVEPOINT statement for creating intermediate points in a transaction. Example:

BEGIN TRANSACTION; -- SQL statements COMMIT;

Security and Access Control: SQL supports user authentication and authorization, allowing administrators to control access to databases. The GRANT statement is used to provide specific permissions, and the REVOKE statement is used to revoke them. Example:

GRANT SELECT, INSERT, UPDATE ON table_name TO user; REVOKE DELETE ON table_name FROM user;

Data Aggregation and Grouping: SQL provides functions for aggregating and summarizing data. The SUM, AVG, COUNT, and GROUP BY statements are used for these purposes. Example:

SELECT department_id, AVG(salary) FROM employees GROUP BY department_id;

Indexing and Optimization: SQL allows the creation of indexes on columns to improve query performance. The CREATE INDEX statement is used for this purpose, and the query optimizer automatically determines the most efficient way to execute a query.Example:

CREATE INDEX idx_department_id ON employees(department_id);

SQL is a powerful language that is widely used in the field of database management. It is employed by developers, database administrators, data analysts, and other professionals to interact with and manage relational databases efficiently. SQL is standardized by the American National Standards Institute (ANSI), but different database management systems (DBMS) may implement slight variations or extensions to the standard.

Frequently Asked Questions