As SQL remains a cornerstone of data management in 2025, recruiters need to identify professionals who can efficiently design, query, and optimize relational databases. With businesses relying on data-driven decision-making, SQL skills are essential for database administrators, backend developers, and data analysts.
This resource, "100+ SQL Interview Questions and Answers," is designed to help recruiters streamline candidate evaluations. It covers topics ranging from fundamentals to advanced concepts, including joins, indexing, query optimization, stored procedures, transactions, and database security.
Whether hiring junior analysts or experienced database architects, this guide enables you to assess a candidate’s:
For a streamlined assessment process, consider platforms like WeCP, which allow you to:
✅ Create customized SQL-based assessments with hands-on query tasks.
✅ Include real-world database challenges to assess problem-solving skills.
✅ Conduct remote proctored tests to ensure integrity.
✅ Use AI-driven evaluation for faster and more accurate hiring decisions.
Save time, enhance your recruitment process, and confidently hire SQL experts who can manage and optimize databases effectively from day one.
SQL (Structured Query Language) is a standard programming language used to manage and manipulate relational databases. It allows you to query, update, insert, and delete data, as well as define, manipulate, and control access to the database schema.
SQL commands are divided into five categories:
Example:
SELECT department, COUNT(*)
FROM employees
WHERE salary > 30000
GROUP BY department
HAVING COUNT(*) > 5;A primary key is a column (or a combination of columns) in a table that uniquely identifies each row in that table. It must contain unique, non-null values.
A foreign key is a column or a set of columns that establishes a link between two tables. It refers to the primary key in another table, enforcing a relationship between the tables.
The SELECT statement is used to retrieve data from one or more tables in a database. It can include filtering, sorting, and grouping data.
Example:
SELECT name, age FROM students;You can use the DISTINCT keyword to retrieve unique values from a column.
Example:
SELECT DISTINCT city FROM customers;The ORDER BY clause is used to sort the result set in either ascending (ASC, default) or descending (DESC) order.
Example:
SELECT name, age FROM students ORDER BY age DESC;The LIMIT (or TOP in some databases) clause restricts the number of rows returned by the query.
Example (MySQL):
SELECT * FROM employees LIMIT 5;Example (SQL Server):
SELECT TOP 5 * FROM employees;A JOIN is used to combine rows from two or more tables based on a related column between them. Common types of joins are:
To filter records, you use the WHERE clause to specify conditions that must be met.
Example:
SELECT * FROM employees WHERE department = 'HR';NULL represents missing or unknown data in SQL. It is different from zero or empty string. You cannot use equality (=) to compare NULL values; instead, use IS NULL or IS NOT NULL.
The DISTINCT keyword is used to remove duplicates and return only unique values.
Example:
SELECT DISTINCT department FROM employees;The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It often uses % for wildcards (any number of characters) and _ for a single character.
Example:
SELECT * FROM employees WHERE name LIKE 'A%'; -- Starts with 'A'An index improves the speed of data retrieval on a database table. Indexes can be created on one or more columns to optimize query performance, but they may slow down INSERT and UPDATE operations due to the overhead of maintaining the index.
To add a new column, use the ALTER TABLE statement.
Example:
ALTER TABLE employees ADD birth_date DATE;The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, often used with aggregate functions like COUNT, SUM, AVG, etc.
Example:
SELECT department, COUNT(*) FROM employees GROUP BY department;Use the UPDATE statement to modify existing records in a table.
Example:
UPDATE employees SET salary = 50000 WHERE id = 101;To insert data into a table, use the INSERT INTO statement.
Example:
INSERT INTO employees (name, department, salary) VALUES ('John', 'HR', 60000);Use the CREATE TABLE statement to create a new table.
Example:
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);Aggregate functions perform calculations on multiple rows and return a single value. Examples: COUNT, SUM, AVG, MAX, MIN.
A VIEW is a virtual table based on the result of a SELECT query. It does not store data but presents data from one or more tables.
Example:
CREATE VIEW employee_view AS
SELECT name, department FROM employees WHERE salary > 40000;Use the DELETE statement to remove specific rows.
Example:
DELETE FROM employees WHERE id = 101;Use the DROP TABLE statement to remove a table and its data.
Example:
DROP TABLE students;Use an INNER JOIN to join two tables based on the foreign key relationship.
Example:
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;Aliases are temporary names given to tables or columns to make queries more readable.
Example:
SELECT e.name AS employee_name, d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;The MOD function returns the remainder of a division operation.
Example:
SELECT MOD(10, 3); -- Output: 1Use the ORDER BY clause.
Example:
SELECT name FROM students ORDER BY age ASC;Use the EXISTS or IN clause.
Example:
SELECT * FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE id = employees.department_id);The IN operator is used to filter rows where a column's value matches any value in a list.
Example:
SELECT * FROM employees WHERE department IN ('HR', 'IT', 'Sales');The BETWEEN operator filters rows where a column's value lies within a specified range (inclusive).
Example:
SELECT * FROM employees WHERE salary BETWEEN 30000 AND 50000;Use the CONCAT function (or || in some databases).
Example:
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;Use the ALTER TABLE statement.
Example:
ALTER TABLE old_table_name RENAME TO new_table_name;Constraints are rules enforced on data columns to maintain data integrity. Examples include:
Use LIMIT in MySQL or TOP in SQL Server.
Example (MySQL):
SELECT * FROM employees LIMIT 10;Example (SQL Server):
SELECT TOP 10 * FROM employees;A subquery (or inner query) is a query nested inside another query. Subqueries are used to retrieve data that will be used by the main (outer) query.
Example:
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);In this example, the subquery (SELECT AVG(salary) FROM employees) calculates the average salary, and the outer query selects employees whose salary is greater than the average.
Example:
SELECT name FROM employees1
UNION
SELECT name FROM employees2; -- Duplicates removed
SELECT name FROM employees1
UNION ALL
SELECT name FROM employees2; -- Duplicates includedThe CASE statement allows you to perform conditional logic in SQL, similar to IF-THEN-ELSE in other languages.
Example:
SELECT name,
salary,
CASE
WHEN salary > 50000 THEN 'High'
WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_category
FROM employees;In this example, the CASE statement categorizes the salary into 'High', 'Medium', and 'Low'.
Some ways to optimize SQL queries include:
The EXISTS clause is used to check if a subquery returns any rows. It returns TRUE if the subquery returns at least one row. IN checks if a value exists in a list or result set.
Difference:
Example:
SELECT * FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE departments.id = employees.department_id);COALESCE returns the first non-NULL value from a list of expressions. It's useful for handling NULL values in queries.
Example:
SELECT name, COALESCE(phone, 'No Phone') AS phone
FROM employees;In this query, if the phone column has a NULL value, it will return 'No Phone' instead.
Window functions perform calculations across a set of rows related to the current row without collapsing them into a single result. Examples include ROW_NUMBER(), RANK(), LEAD(), and LAG().
Example:
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;Here, the RANK() function assigns a rank to each employee based on their salary, without collapsing the result set.
Example:
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;A CROSS JOIN returns the Cartesian product of two tables, i.e., it pairs each row from the first table with every row from the second table. It's rarely used directly because it results in large datasets.
Example:
SELECT * FROM employees CROSS JOIN departments;This query returns all possible combinations of rows from employees and departments.
The MERGE statement allows you to perform INSERT, UPDATE, or DELETE operations in a single statement based on conditions, typically used for upserting data.
Example:
MERGE INTO employees AS target
USING new_employees AS source
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET target.salary = source.salary
WHEN NOT MATCHED THEN
INSERT (id, name, salary) VALUES (source.id, source.name, source.salary);You can use the LIMIT or ROW_NUMBER() functions to find the second-highest salary.
Example (using LIMIT):
SELECT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1; -- Skip the highest salary and get the second-highestExample (using ROW_NUMBER()):
WITH RankedSalaries AS (
SELECT salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees
)
SELECT salary FROM RankedSalaries WHERE rank = 2;The WITH clause (also known as Common Table Expressions, or CTEs) allows you to define temporary result sets that can be referenced within the main query.
Example:
WITH AvgSalary AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT * FROM employees e
JOIN AvgSalary a ON e.department = a.department
WHERE e.salary > a.avg_salary;To remove duplicates, use the DISTINCT keyword or identify and delete them using ROW_NUMBER() or other ranking/window functions.
Example (using DISTINCT):
SELECT DISTINCT name, department FROM employees;Example (deleting duplicates using ROW_NUMBER()):
WITH CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY name, department ORDER BY id) AS row_num
FROM employees
)
DELETE FROM CTE WHERE row_num > 1;A CTE is a temporary result set that is defined within a WITH clause and can be referenced later in the query. It makes complex queries more readable and reusable.
Example:
WITH EmployeeSalary AS (
SELECT name, salary FROM employees
)
SELECT * FROM EmployeeSalary WHERE salary > 50000;You can use the COUNT(*) function to calculate the total number of records.
Example:
SELECT COUNT(*) FROM employees;The ROW_NUMBER() function assigns a unique sequential integer to rows within a result set. It is often used for pagination or identifying duplicates.
Example:
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;Use the CAST() or CONVERT() functions to convert a column's data type.
Example:
SELECT name, CAST(salary AS VARCHAR) FROM employees;or
SELECT name, CONVERT(VARCHAR, salary) FROM employees;Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;Use the ALTER TABLE statement.
Example:
ALTER TABLE employees ADD birth_date DATE;The GROUP BY clause groups rows that have the same values in specified columns into aggregated data. It is commonly used with aggregate functions.
Example:
SELECT department, COUNT(*) FROM employees GROUP BY department;A self-join is a join where a table is joined with itself. It is useful when comparing rows within the same table.
Example:
SELECT e1.name, e2.name
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;Use ROW_NUMBER() to identify duplicates and then delete them.
Example:
WITH CTE AS (
SELECT name, department, ROW_NUMBER() OVER (PARTITION BY name, department ORDER BY id) AS row_num
FROM employees
)
DELETE FROM CTE WHERE row_num > 1;Scalar functions operate on a single value and return a single value. Examples include LEN(), UPPER(), LOWER(), ROUND().
Example:
SELECT UPPER(name) FROM employees;A correlated subquery is a subquery that depends on the outer query for its values. It is evaluated once for each row processed by the outer query.
Example:
SELECT name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);A materialized view stores the result of a query physically, unlike a regular view, which only stores the query. This improves performance for complex queries but requires periodic refreshes.
Use an UPDATE with a JOIN.
Example:
UPDATE e
SET e.salary = n.salary
FROM employees e
JOIN new_salaries n ON e.id = n.id;A PIVOT table converts rows into columns. It’s often used for summarizing data.
Example:
SELECT *
FROM (SELECT department, salary FROM employees) AS SourceTable
PIVOT (SUM(salary) FOR department IN ([HR], [IT], [Sales])) AS PivotTable;Example (ANY):
SELECT name FROM employees WHERE salary > ANY (SELECT salary FROM managers);Example (ALL):
SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM managers);The LEAD() function provides access to a row at a specific physical offset following the current row in the result set.
Example:
SELECT name, salary, LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary
FROM employees;NULLIF() returns NULL if the two expressions are equal, otherwise it returns the first expression.
Example:
SELECT NULLIF(salary, 0) FROM employees;Use the CREATE INDEX statement to add an index.
Example:
CREATE INDEX idx_salary ON employees(salary);The ROLLUP operator creates subtotals and a grand total across multiple levels of aggregation.
Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY ROLLUP(department);Use the SUM() window function with OVER().
Example:
SELECT name, salary,
SUM(salary) OVER (ORDER BY salary) AS cumulative_salary
FROM employees;A FULL OUTER JOIN returns all rows when there is a match in either table. If there is no match, NULL values are returned.
Example:
SELECT * FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;Use LIMIT and OFFSET for MySQL, or ROW_NUMBER() for other databases.
Example (MySQL):
SELECT * FROM employees LIMIT 10 OFFSET 20; -- Page 3, with 10 rows per pageExample (SQL Server):
WITH EmployeeCTE AS (
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees
)
SELECT * FROM EmployeeCTE WHERE row_num BETWEEN 21 AND 30; -- Page 3, with 10 rows per pageUse the ALTER TABLE statement with the DROP COLUMN clause.
Example:
ALTER TABLE employees DROP COLUMN birth_date;Use GROUP BY with HAVING to find duplicate records.
Example:
SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;Database normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller, more manageable ones and defining relationships between them to ensure the database is structured efficiently.
The main forms of normalization are:
Example:
-- Non-1NF table
| ID | Name | Phone Numbers |
|-----|-------|-----------------------|
| 1 | John | 123-456, 789-123 |
-- 1NF table (atomic values)
| ID | Name | Phone Number |
|-----|-------|-----------------------|
| 1 | John | 123-456 |
| 1 | John | 789-123 |Example:
-- Non-2NF table
| StudentID | CourseID | CourseName | Grade |
|-----------|----------|------------|-------|
-- 2NF tables (removing partial dependency)
| CourseID | CourseName |
|----------|------------|
| StudentID | CourseID | Grade |Example:
-- Non-3NF table
| EmployeeID | DepartmentID | DepartmentName | EmployeeName |
-- 3NF tables (eliminating transitive dependency)
| DepartmentID | DepartmentName |
|--------------|----------------|
| EmployeeID | DepartmentID | EmployeeName |Normalization helps to avoid data anomalies such as update, delete, and insert anomalies by organizing data logically.
2. What are indexes in SQL? Explain the types of indexes.
Indexes are special lookup tables that the database management system uses to speed up data retrieval. They are created on columns to reduce the time required to search for rows. An index is like a book’s index, which helps to quickly locate specific data without having to search through every page.There are two main types of indexes:
Example:
CREATE CLUSTERED INDEX idx_employee_id ON employees(id);Example:
CREATE NONCLUSTERED INDEX idx_employee_name ON employees(name);Other types of indexes include:
Indexes improve query performance, but they come at the cost of slower data modifications (like INSERT, UPDATE, and DELETE), as the index needs to be updated.
3. Explain the ACID properties in SQL.
ACID properties (Atomicity, Consistency, Isolation, Durability) are a set of properties that ensure reliable processing of database transactions. They maintain the integrity of a database, even in the event of errors, power failures, or crashes.
Example:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT; -- If either UPDATE fails, the entire transaction is rolled back.Example:
-- If a transaction violates a constraint (e.g., unique or foreign key), the transaction is rolled back.Example:
-- Two transactions cannot read the same data until the first transaction is complete, depending on the isolation level.Example:
-- After COMMIT, the changes made by the transaction are saved permanently in the database.The ACID properties guarantee the reliability and consistency of databases, especially in multi-user environments.
4. What are SQL transactions? Explain COMMIT, ROLLBACK, and SAVEPOINT.
A transaction in SQL is a sequence of one or more SQL operations treated as a single unit of work. A transaction ensures data consistency and integrity by following ACID properties. COMMIT: Finalizes a transaction by making all the changes permanent in the database.
Example:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT; -- Makes all changes permanent.ROLLBACK: Undoes all the changes made in the current transaction, reverting the database to its previous state before the transaction started.
Example:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
ROLLBACK; -- Reverts all changes made during the transaction.SAVEPOINT: Creates intermediate points within a transaction where you can roll back to a specific savepoint instead of the entire transaction.
Example:
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SAVEPOINT before_transfer;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
ROLLBACK TO before_transfer; -- Rolls back only to the savepoint.5. What is a deadlock in SQL, and how can it be resolved?
A deadlock occurs when two or more transactions block each other by holding locks on resources that the other transactions need. Each transaction is waiting for the other to release the lock, resulting in a situation where neither transaction can proceed.Example of a deadlock:
Resolution strategies: Deadlock detection: The database detects the deadlock automatically and aborts one of the transactions, rolling back its changes. The other transaction can then proceed.
Example:
-- SQL Server automatically detects deadlocks and resolves them by terminating one of the processes.Timeout: The database aborts a transaction if it is waiting too long for a lock.
Example:
SET LOCK_TIMEOUT 10000; -- Sets the lock timeout to 10 seconds.6. Explain the difference between TRUNCATE, DELETE, and DROP.
DELETE: Removes rows from a table based on a condition. It can be used with a WHERE clause to delete specific records. It logs individual row deletions and can be rolled back if the transaction is not committed.
Example:
DELETE FROM employees WHERE department = 'HR';TRUNCATE: Removes all rows from a table but does not log individual row deletions. It is faster than DELETE because it does not generate as much logging and cannot be rolled back.
Example:
TRUNCATE TABLE employees;DROP: Removes a table or database entirely, along with all its data and structure. It cannot be rolled back.
Example:
DROP TABLE employees;Key differences:
7. What is partitioning in SQL, and what are its types?
Partitioning is the process of dividing a large table into smaller, more manageable pieces, called partitions. It helps improve query performance by limiting the amount of data that needs to be scanned and simplifies data management for large datasets.Types of partitioning:
Example:
CREATE TABLE sales (
id INT,
sale_date DATE,
amount DECIMAL
)
PARTITION BY RANGE (sale_date) (
PARTITION p1 VALUES LESS THAN ('2022-01-01'),
PARTITION p2 VALUES LESS THAN ('2023-01-01')
);Example:
CREATE TABLE employees (
id INT,
department VARCHAR(50)
)
PARTITION BY LIST (department) (
PARTITION p1 VALUES IN ('HR', 'Finance'),
PARTITION p2 VALUES IN ('IT', 'Sales')
);Example:
CREATE TABLE users (
id INT,
name VARCHAR(100)
)
PARTITION BY HASH (id);Partitioning helps in optimizing query performance, especially for large datasets, by allowing the database to search only in specific partitions instead of the entire table.
8. What are SQL triggers? Explain their types.
A trigger is a special type of stored procedure in SQL that automatically executes (or “fires”) when certain events occur in the database, such as INSERT, UPDATE, or DELETE operations on a table.Types of triggers:
Example:
CREATE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
SET NEW.salary = IF(NEW.salary < 1000, 1000, NEW.salary);Example:
CREATE TRIGGER after_update_salary
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO audit_log (employee_id, old_salary, new_salary)
VALUES (OLD.id, OLD.salary, NEW.salary);Example:
CREATE TRIGGER instead_of_insert
INSTEAD OF INSERT ON employee_view
FOR EACH ROW
INSERT INTO employees (name, department)
VALUES (NEW.name, NEW.department);Triggers are useful for enforcing business rules, logging changes, and maintaining data integrity, but they can impact performance if not used carefully.
9. What is query optimization, and why is it important?
Query optimization is the process of improving the performance of SQL queries to ensure they execute in the most efficient way possible. The goal is to minimize resource usage (such as CPU, memory, and I/O) and reduce the execution time of queries.Reasons for query optimization:
Common techniques for query optimization:
Query optimization is crucial for maintaining fast, scalable, and efficient database systems.
10. Explain SQL Server isolation levels.
Isolation levels in SQL Server determine how transaction integrity is maintained and how locks are managed between concurrent transactions. They control the visibility of uncommitted changes made by other transactions.The five isolation levels are:
Example:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;Example:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;Example:
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;Example:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;Example:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;Example:
SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
ON employees.department_id = departments.id;Example of LEFT OUTER JOIN:
SELECT employees.name, departments.name
FROM employees
LEFT OUTER JOIN departments
ON employees.department_id = departments.id;12. What is a correlated subquery? How is it different from a regular subquery?
A correlated subquery is a subquery that references columns from the outer query. It is executed once for every row selected by the outer query.
Example:
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);Example:
SELECT name
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);Key differences:
13. What is a SQL MERGE statement, and when would you use it?
The MERGE statement allows you to perform INSERT, UPDATE, or DELETE operations in a single SQL statement based on the matching condition between two tables. It’s often used in data warehousing and data integration scenarios where data from one table (source) needs to be merged into another table (target).
Syntax:
MERGE INTO target_table AS target
USING source_table AS source
ON target.id = source.id
WHEN MATCHED THEN
UPDATE SET target.column = source.column
WHEN NOT MATCHED THEN
INSERT (column1, column2)
VALUES (source.column1, source.column2);When to use it:
Example:
MERGE INTO employees AS target
USING new_employees AS source
ON target.employee_id = source.employee_id
WHEN MATCHED THEN
UPDATE SET target.salary = source.salary
WHEN NOT MATCHED THEN
INSERT (employee_id, name, salary)
VALUES (source.employee_id, source.name, source.salary);The WITH (NOLOCK) hint allows a query to read uncommitted data, effectively bypassing locks on the table. This can improve query performance by reducing wait times for locks, but it comes with certain risks.
Advantages:
Disadvantages:
Example:
SELECT * FROM employees WITH (NOLOCK);This query will return data from the employees table, but it might include uncommitted changes that could be rolled back later.
Performance tuning involves optimizing SQL queries to ensure they run efficiently, especially for large datasets. Some common techniques include:
Example of using indexes:
CREATE INDEX idx_employee_department ON employees(department_id);Concurrency issues arise when multiple transactions access the same data simultaneously, leading to conflicts such as dirty reads, non-repeatable reads, or phantom reads. These issues can be mitigated using transaction isolation levels and locking mechanisms.
Example of setting the isolation level:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;Using appropriate isolation levels based on your use case can help balance between data consistency and system performance.
Window functions perform calculations across a set of table rows related to the current row without collapsing them into a single result like aggregate functions. They are used for tasks like running totals, moving averages, and ranking.
Key types of window functions:
ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition.
Example:
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;RANK(): Similar to ROW_NUMBER(), but rows with equal values get the same rank, and gaps appear in the sequence.
Example:
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;SUM(): Returns the running total of a column over a partition.
Example:
SELECT name, salary, SUM(salary) OVER (ORDER BY salary) AS running_total
FROM employees;LEAD()/LAG(): Retrieves the value of a column from a subsequent or preceding row.
Example:
SELECT name, salary, LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary
FROM employees;18. How do you design a database schema for high availability?
Designing a database schema for high availability ensures that the database remains accessible and operational even during hardware or software failures. Common strategies include:
19. What is database normalization? Explain the different normal forms.
Normalization is the process of organizing data to reduce redundancy and improve data integrity. It involves breaking down tables into smaller tables and defining relationships between them.Normal Forms:
Example:
| OrderID | Product |
|---------|---------|
| 1 | A |
| 1 | B |Example:
| OrderID | ProductID | Quantity |
|---------|-----------|----------|Example:
| CustomerID | CustomerName | OrderID |20. What is the difference between GROUP BY and ORDER BY in SQL?
GROUP BY: Groups rows that have the same values in specified columns into summary rows (e.g., total sales per customer). It is used with aggregate functions like COUNT(), SUM(), AVG(), etc.
Example:
SELECT department, COUNT(*)
FROM employees
GROUP BY department;ORDER BY: Sorts the result set based on one or more columns. It does not perform any aggregation.
Example:
SELECT name, salary
FROM employees
ORDER BY salary DESC;A Common Table Expression (CTE) is a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. CTEs make complex queries easier to read and manage by breaking them into simpler parts.
Syntax:
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name;Example:
WITH EmployeeCTE AS (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
)
SELECT department_id, avg_salary
FROM EmployeeCTE;Key differences between CTE and subquery:
A recursive CTE is a CTE that references itself. It is used to work with hierarchical data, such as organizational structures or tree structures.
Syntax:
WITH RECURSIVE cte_name AS (
-- Anchor member: the base query
SELECT column1, column2
FROM table_name
WHERE condition
UNION ALL
-- Recursive member: references the CTE itself
SELECT column1, column2
FROM table_name
INNER JOIN cte_name
ON table_name.column = cte_name.column
)
SELECT *
FROM cte_name;Example: Retrieving an employee hierarchy (managers and subordinates):
WITH RECURSIVE EmployeeHierarchy AS (
SELECT employee_id, name, manager_id
FROM employees
WHERE manager_id IS NULL -- Anchor: Top-level manager
UNION ALL
SELECT e.employee_id, e.name, e.manager_id
FROM employees e
INNER JOIN EmployeeHierarchy eh
ON e.manager_id = eh.employee_id -- Recursion: Subordinates
)
SELECT *
FROM EmployeeHierarchy;This recursive CTE will list all employees and their reporting hierarchy.
A materialized view is a physical copy of a result set, stored in a database as a separate object. Unlike regular views, which are virtual and always reflect the latest data from their base tables, materialized views store data persistently and must be manually refreshed.
Key differences:
Example of creating a materialized view:
CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id;Indexing is a technique used to improve the speed of data retrieval operations on a database. An index creates a data structure (usually a B-tree or hash) that provides faster access to the rows in a table.
Types of indexes:
Example:
CREATE CLUSTERED INDEX idx_employee_id ON employees(employee_id);Example:
CREATE NONCLUSTERED INDEX idx_employee_name ON employees(name);Example:
CREATE UNIQUE INDEX idx_unique_email ON employees(email);Example:
CREATE INDEX idx_employee_department ON employees(department_id, salary);Indexes improve query performance but can slow down INSERT, UPDATE, and DELETE operations because the index must also be updated.
25. What is database sharding ? When would you use it?
Database sharding is the process of splitting a large database into smaller, more manageable pieces (called shards), which are distributed across multiple servers. Each shard contains a subset of the total data, and together, they represent the entire dataset.When to use it:
Example of sharding by user ID:
Advantages:
Disadvantages:
26. How does SQL Server manage transaction logs, and why are they important?
In SQL Server, the transaction log records all changes made to the database. It ensures that each transaction is either fully completed (committed) or fully undone (rolled back) in case of failure, maintaining the integrity of the database.Key roles of the transaction log:
Example of a full backup with transaction logs:
BACKUP DATABASE db_name TO DISK = 'db_backup.bak';
BACKUP LOG db_name TO DISK = 'db_log.trn';The transaction log is crucial for data consistency and recovery but must be managed carefully to avoid growing too large.
All three are window functions that assign a rank to rows within a partition, but they behave differently when encountering ties (rows with the same value).
Example:
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;Example:
SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;Example:
SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;Differences:
28. What is the difference between replication and mirroring in SQL?
Replication and mirroring are both techniques used to enhance database availability and reliability, but they serve different purposes and work in different ways. Replication
Mirroring
Summary of Differences:
29. What is a deadlock in SQL, and how can it be resolved?
A deadlock occurs when two or more transactions block each other by holding locks on resources that the others need to complete. This situation leads to a standstill where each transaction is waiting for the other to release its lock.Example of a Deadlock:
Deadlock Detection and Resolution:
Deadlock Prevention Strategies:
By understanding and implementing these strategies, you can mitigate the risk of deadlocks in SQL databases and ensure smoother transaction processing.
30. How do you implement security in SQL databases?
Implementing security in SQL databases involves multiple strategies and best practices to protect sensitive data from unauthorized access and breaches.Key Security Measures:
31. What is the purpose of the EXPLAIN command in SQL? How can it help optimize queries?
The EXPLAIN command provides insight into how SQL queries are executed by the database engine. It generates a query execution plan, detailing how tables are scanned, the order of operations, join methods, and the indexes used.
How it helps optimize queries:
Example of using EXPLAIN:
EXPLAIN SELECT name, salary FROM employees WHERE department_id = 5;Triggers are special types of stored procedures that automatically execute (or "fire") in response to specific events on a table or view, such as INSERT, UPDATE, or DELETE operations. They help maintain data integrity, enforce business rules, and automatically perform actions when data changes.
Key differences from stored procedures:
Example of a trigger:
CREATE TRIGGER trg_after_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_table (employee_id, action)
VALUES (NEW.employee_id, 'Inserted');
END;A cursor is a database object that allows you to retrieve and manipulate rows returned by a query one at a time. Cursors are useful when you need to process data row by row rather than all at once.
Types of Cursors:
Using a Cursor:
Example of using an explicit cursor:
DECLARE my_cursor CURSOR FOR
SELECT employee_id, name FROM employees;
OPEN my_cursor;
FETCH NEXT FROM my_cursor INTO @employee_id, @name;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Process each row here
FETCH NEXT FROM my_cursor INTO @employee_id, @name;
END;
CLOSE my_cursor;
DEALLOCATE my_cursor;SQL injection is a code injection technique that exploits vulnerabilities in an application by allowing an attacker to manipulate SQL queries. By inserting malicious SQL code into an input field, an attacker can gain unauthorized access to data, modify or delete records, or execute administrative operations.
Prevention methods:
Parameterized Queries: Use prepared statements with parameters instead of concatenating user inputs into SQL queries.
SELECT * FROM users WHERE username = ? AND password = ?;35. What is the difference between a primary key and a foreign key?
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
department_id INT
);Example:
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100)
);
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
department_id INT,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);Key Differences:
36. Explain how to handle performance tuning in SQL. What are some common techniques?
Performance tuning in SQL involves optimizing database queries and schema to improve the speed and efficiency of database operations. Common techniques include:
37. What are the differences between INNER JOIN, LEFT JOIN, and RIGHT JOIN?
Joins are used to combine rows from two or more tables based on a related column. The key types of joins include:
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments ON employees.department_id = departments.department_id;Summary:
38. What are SQL constraints? Explain the different types of constraints.
Constraints are rules enforced on the data in a table to maintain integrity and consistency. They restrict the types of data that can be entered into a table. Common types of SQL constraints include:
CREATE TABLE employees (
employee_id INT NOT NULL,
name VARCHAR(100) NOT NULL
);CREATE TABLE users (
user_id INT UNIQUE,
email VARCHAR(255) UNIQUE
);CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);CREATE TABLE products (
product_id INT PRIMARY KEY,
price DECIMAL CHECK (price >= 0)
);CREATE TABLE employees (
employee_id INT PRIMARY KEY,
status VARCHAR(10) DEFAULT 'active'
);Both UNION and UNION ALL are used to combine the results of two or more SELECT statements, but they differ in how they handle duplicates:
SELECT city FROM customers
UNION
SELECT city FROM suppliers;SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;Key Differences:
Window functions are a type of function that performs calculations across a set of table rows that are related to the current row. Unlike regular aggregate functions, window functions do not collapse the result set into a single row; instead, they return a value for each row.
Commonly used window functions:
ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition.
SELECT name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;RANK(): Similar to ROW_NUMBER(), but it gives the same rank to identical values, skipping the subsequent ranks.
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;DENSE_RANK(): Similar to RANK(), but does not skip ranks for ties.
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;SUM(), AVG(), etc.: You can perform aggregate calculations over a specific range of rows.
SELECT name, salary,
SUM(salary) OVER (PARTITION BY department_id) AS department_total
FROM employees;How to Use Window Functions:
Window functions are powerful tools for analytics and reporting, enabling more complex queries without the need for subqueries or joins.
