Structured Query Language (SQL) is the backbone of database management and one of the most in-demand skills for data analysts, backend developers, and database administrators. Whether you're a fresher preparing for your first job or an experienced professional brushing up for a technical interview, mastering SQL is crucial to your success.
In this article, we've compiled 50 of the most commonly asked SQL interview questions and answers, ranging from basic queries to advanced concepts like window functions, joins, subqueries, and performance tuning. These questions not only test your theoretical knowledge but also reflect real-world scenarios you'll encounter on the job.
1. What is SQL?
SQL (Structured Query Language) is a standardized programming language used for managing and manipulating relational databases. It allows users to perform various operations such as querying data, updating records, inserting new data, and deleting existing data. SQL is essential for database management systems (DBMS) and is widely used in applications that require data storage and retrieval.
2. What is the difference between SQL and MySQL?
SQL (Structured Query Language) is a standardized programming language used for managing and manipulating relational databases. It provides the syntax and commands for tasks such as querying data, updating records, and defining database structures.
MySQL, on the other hand, is an open-source relational database management system (RDBMS) that uses SQL as its query language. It is one of the most popular implementations of SQL and is widely used for web applications and data storage.
In summary, SQL is the language used to interact with databases, while MySQL is a specific database management system that utilizes SQL to perform its operations.
3. What are the different types of SQL commands?
SQL commands can be categorized into several types based on their functionality. Here are the main types of SQL commands:
1. DML (Data Manipulation Language): These commands are used to manage and manipulate data within schema objects. Common DML commands include:
- SELECT: Retrieve data from one or more tables.
- INSERT: Add new records to a table.
- UPDATE: Modify existing records in a table.
- DELETE: Remove records from a table.
2. DDL (Data Definition Language): These commands are used to define and manage all database objects. Common DDL commands include:
- CREATE: Create new tables, views, or databases.
- ALTER: Modify existing database objects (e.g., adding or dropping columns).
- DROP: Delete tables, views, or databases.
- TRUNCATE: Delete all records from a table.
3. DCL (Data Control Language): These commands are used to control access to data within the database. Common DCL commands include:
- GRANT: Provide specific privileges to users.
- REVOKE: Remove specific privileges from users.
4. TCL (Transaction Control Language): These commands are used to manage transactions in a database. Common TCL commands include:
- COMMIT: Save all changes made during the current transaction.
- ROLLBACK: Undo changes made during the current transaction.
- SAVEPOINT: Set a point within a transaction to which you can later roll back.
These categories help organize SQL commands based on their purpose and functionality in database management.
4. Difference between the DELETE and TRUNCATE commands.
Both DELETE and TRUNCATE are used to remove data from a table, but they differ in how they work and their impact:
| DELETE | TRUNCATE |
|---|---|
| DELETE is a DML (Data Manipulation Language) command. | TRUNCATE is a DDL (Data Definition Language) command. |
| It removes rows one at a time and logs each deletion. | It removes all rows from a table without logging individual row deletions. |
| You can use a WHERE clause with DELETE to remove specific rows. | You cannot use a WHERE clause with TRUNCATE. |
| Triggers do fire on DELETE. | Triggers do not fire on TRUNCATE. |
| Slower compared to TRUNCATE due to row-level logging. | It's faster and uses fewer system resources. |
| It can be rolled back if used within a transaction. | It can’t be rolled back in some databases if not inside a transaction. |
5. What is a primary key? How is it different from a foreign key?
A Primary Key is a column or a set of columns in a table that uniquely identifies each row.
- It must be unique and cannot contain NULL values.
- A table can have only one primary key.
- It ensures entity integrity.
Example:
CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(100) );
- It is used to establish and enforce a relationship between two tables.
- It can contain duplicates and NULLs, depending on the relationship.
- It ensures referential integrity.
Example:
CREATE TABLE Enrollments ( EnrollmentID INT PRIMARY KEY, StudentID INT, FOREIGN KEY (StudentID) REFERENCES Students(StudentID) );
6. What is a Unique Key in SQL?
A Unique Key is a constraint that ensures all values in a column or set of columns are unique across the table.
- It prevents duplicate values.
- Unlike a Primary Key, a Unique Key can allow a single NULL (depending on the database system).
- A table can have multiple unique keys, but only one primary key.
Example:
CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Email VARCHAR(100) UNIQUE );
7. What is a Composite Key?
A Composite Key is a primary key that consists of two or more columns used together to uniquely identify a row in a table.
- No single column in the composite key is sufficient to uniquely identify a row on its own.
- The combination of values in all columns must be unique.
Example:
CREATE TABLE CourseRegistrations ( StudentID INT, CourseID INT, RegistrationDate DATE, PRIMARY KEY (StudentID, CourseID) );
8. Explain the concept of normalization and denormalization.
Normalization
Normalization is the process of organizing data in a database to:
- Reduce redundancy
- Improve data integrity
- Minimize data anomalies
This is done by dividing tables into smaller related tables and establishing relationships using foreign keys.
Normalization follows a series of normal forms (1NF, 2NF, 3NF, etc.), each with specific rules.
Example: Instead of storing employee and department data in one table, we separate them:
-- Employee Table EmployeeID | Name | DepartmentID -- Department Table DepartmentID | DepartmentName
Denormalization
Denormalization is the process of combining normalized tables to improve read performance by:
- Reducing the number of joins
- Storing redundant data
It is often used in reporting systems, data warehouses, or read-heavy applications.
Example: You might combine employee and department data into one table:
EmployeeID | Name | DepartmentName
In summary:
- Normalization is for efficiency and consistency.
- Denormalization is for performance and simplicity when reading data.
9. How to Improve Data Integrity?
Data Integrity means ensuring the accuracy, consistency, and reliability of data throughout its life cycle.
Normalization improves data integrity by:
- Storing each fact only once (in the right table)
- Avoiding duplicate or conflicting data
Example: Without Normalization.
Employee Table: ID | Name | Department 1 | Alice | HR 2 | Bob | HR 3 | Carol | H.R.
10. What are the different normal forms?
The goal of normal forms is to structure a relational database to minimize redundancy and avoid anomalies. The most commonly used normal forms are:
1. First Normal Form (1NF)
A table is in 1NF if:
- All columns contain atomic (indivisible) values.
- Each column contains values of the same type.
- There are no repeating groups or arrays.
Example (Not 1NF):
StudentID | Name | Subjects
1 | Alice | Math, Science
StudentID | Name | Subject 1 | Alice | Math 1 | Alice | Science
2. Second Normal Form (2NF)
A table is in 2NF if:
- It is already in 1NF
- And every non-key column is fully functionally dependent on the entire primary key (i.e., no partial dependency)
Applies only when there is a composite key
Example (Not 2NF):
(StudentID, CourseID) → InstructorName InstructorName depends only on CourseID, not the full key.
3. Third Normal Form (3NF)
A table is in 3NF if:
- It is in 2NF
- And no transitive dependency exists (i.e., non-key columns don’t depend on other non-key columns)
Example (Not 3NF):
EmployeeID | Name | DepartmentID | DepartmentName
Fix: Move department info to a separate table.
Boyce-Codd Normal Form (BCNF)
A stricter version of 3NF:
- A table is in BCNF if every determinant is a candidate key.
- Fixes certain rare anomalies not handled by 3NF.
4NF – Fourth Normal Form:
- Removes multi-valued dependencies.
- A table should not have two independent multi-valued facts about an entity.
5NF – Fifth Normal Form:
- Deals with join dependencies.
- Ensures that a table cannot be broken into smaller tables without losing data.
Summary Table:
| Normal Form | Goal | Key Rule |
|---|---|---|
| 1NF | Eliminate repeating groups | Atomic values only |
| 2NF | Eliminate partial dependencies | Full key dependency |
| 3NF | Eliminate transitive dependencies | Non-key depends only on key |
| BCNF | Stronger version of 3NF | Every determinant is a candidate key |
| 4NF | Eliminate multi-valued dependencies | One fact per table |
| 5NF | Eliminate join dependencies | Lossless join decomposition |
11. What are SQL Constraints, and why are they important?
SQL Constraints are rules applied to table columns to enforce data integrity, accuracy, and consistency in a database. They restrict the type of data that can be stored in a column, ensuring that only valid data is entered.
Types of SQL Constraints:
| Constraint | Description |
|---|---|
| PRIMARY KEY | Uniquely identifies each row; cannot be NULL or duplicate. |
| FOREIGN KEY | Ensures a value matches one in another table; maintains referential integrity. |
| UNIQUE | Ensures all values in a column are unique (except NULLs). |
| NOT NULL | Prevents NULL values from being stored in a column. |
| CHECK | Validates data based on a logical expression. |
| DEFAULT | Sets a default value for a column if no value is provided. |
Example:
CREATE TABLE Employees ( ID INT PRIMARY KEY, Name VARCHAR(100) NOT NULL, Email VARCHAR(100) UNIQUE, Age INT CHECK (Age >= 18), JoinDate DATE DEFAULT GETDATE() );
- Enforce business rules at the database level
- Prevent invalid data entry
- Maintain relationships between tables
- Improve data reliability and quality
In short, Constraints are the backbone of data integrity in relational databases.
12. What is a join? Explain different types of joins.
A JOIN in SQL is used to combine rows from two or more tables based on a related column between them, typically using primary and foreign keys. It allows us to retrieve meaningful information spread across multiple tables.
Types of JOINS:
1. INNER JOIN
- Returns only matching rows from both tables.
- Non-matching rows are excluded.
Example:
SELECT A.Name, B.DepartmentName FROM Employees A INNER JOIN Departments B ON A.DepartmentID = B.DepartmentID;
- Returns all rows from the left table and matched rows from the right.
- If no match, NULLs are returned for rthe ight table columns.
Example:
SELECT A.Name, B.DepartmentName FROM Employees A LEFT JOIN Departments B ON A.DepartmentID = B.DepartmentID;
- Returns all rows from the right table and matched rows from the left.
- If no match, NULLs are returned for left table columns.
Example:
SELECT A.Name, B.DepartmentName FROM Employees A RIGHT JOIN Departments B ON A.DepartmentID = B.DepartmentID;
- Returns all rows when there is a match in either the left or right table.
- Non-matching rows are filled with NULLs.
Example:
SELECT A.Name, B.DepartmentName FROM Employees A FULL OUTER JOIN Departments B ON A.DepartmentID = B.DepartmentID;
5. CROSS JOIN
- Returns the Cartesian product of both tables.
- Each row in the first table joins with every row in the second table.
Example:
SELECT A.Name, B.DepartmentName FROM Employees A CROSS JOIN Departments B;
- A table is joined with itself to compare rows within the same table.
Example:
SELECT A.EmployeeID, A.Name, B.Name AS ManagerName FROM Employees A JOIN Employees B ON A.ManagerID = B.EmployeeID;
Summary Table:
| Join Type | Returns |
|---|---|
| INNER JOIN | Only matching rows from both tables |
| LEFT JOIN | All rows from the left table + matched rows from the right |
| RIGHT JOIN | All rows from the right table + matched rows from the left |
| FULL JOIN | All rows from both tables, matched or not |
| CROSS JOIN | All possible combinations (Cartesian product) |
| SELF JOIN | Matches rows within the same table |
In interviews, focus on INNER, LEFT, and FULL joins with examples—they're the most used.
13. What is a subquery? Provide an example.
A subquery (also called an inner query or nested query) is a query inside another SQL query. It is used to perform operations that depend on the results of another query.
Subqueries are often used in:
- WHERE clause
- FROM clause
- SELECT clause
Example: Subquery in WHERE Clause
Find employees who earn more than the average salary.
SELECT Name, Salary FROM Employees WHERE Salary > ( SELECT AVG(Salary) FROM Employees );
- The inner query (SELECT AVG(Salary)) calculates the average salary.
- The outer query returns employees whose salary is greater than that value.
Subqueries have two types:
1. Non-Correlated Subquery: A non-correlated subquery is self-contained and can run independently of the outer query. It is executed once, and its result is used by the outer query.
Example:
SELECT Name FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);
2. Correlated Subquery: A correlated subquery refers to a column from the outer query, meaning it depends on each row processed by the outer query. It is executed once per row of the outer query.
Example:
SELECT e1.Name FROM Employees e1 WHERE Salary > ( SELECT AVG(Salary) FROM Employees e2 WHERE e1.Department = e2.Department );
A subquery is a powerful way to write complex filtering and data comparison logic using nested SQL statements.
14. What is a View? How is it different from a table?
A View is a virtual table in SQL. It is based on the result of a SELECT query and doesn’t store data itself. Instead, it shows data from one or more real tables.
You can query a view just like a table.
Example: Creating a View
CREATE VIEW ActiveEmployees AS SELECT Name, Department FROM Employees WHERE Status = 'Active';
SELECT * FROM ActiveEmployees;
Difference Between Table and View:
| Feature | Table | View |
|---|---|---|
| Storage | Physically stores data | Does not store data (virtual) |
| Created by | CREATE TABLE | CREATE VIEW |
| Data Source | Independent data | Based on query from other tables |
| Performance | Faster (data is stored) | May be slower (computed at runtime) |
| Usage | For storing and managing data | For simplifying complex queries, security, and abstraction |
View is used to:
- To simplify complex joins
- To restrict access to certain columns or rows
- To create reusable query logic
In short: A table holds real data; a view provides a window into that data using a query.
15. Explain the use of indexes in SQL.
An index in SQL is a performance optimization tool that helps speed up data retrieval operations on a table. It works like an index in a book; instead of scanning every row, the database can quickly jump to the data using the index.
Key Uses of Indexes:
- Faster SELECT queries.
- Improve JOIN and WHERE performance.
- Support for ORDER BY and GROUP BY.
- Used automatically by the query optimizer.
Important Notes:
- Indexes speed up reads but can slow down writes (INSERT, UPDATE, DELETE) because the index must be updated.
- Use indexes on columns that are frequently searched, joined, or filtered.
- Avoid indexing columns with many duplicate values (like gender or status) — it’s less effective.
In Short, Indexes are used to speed up data retrieval in SQL, especially in search-heavy or join-heavy queries, but they come with a trade-off in write performance and storage.
16. Explain Different Types of Indexes.
Indexes improve the performance of read operations by allowing the database engine to locate data more efficiently. SQL supports several types of indexes, each with specific use cases.
1. Primary (or Clustered) Index
- Automatically created on the primary key.
- Physically orders the data in the table based on the indexed column.
- Only one clustered index per table.
Example:
CREATE CLUSTERED INDEX idx_emp_id ON Employees(EmployeeID);
2. Non-Clustered Index
- A separate structure that stores pointers to the actual rows.
- Does not change the physical order of data.
- You can have multiple non-clustered indexes on a table.
Example:
CREATE NONCLUSTERED INDEX idx_emp_name ON Employees(Name);
3. Unique Index
- Ensures that the indexed column(s) have unique values.
- Prevents duplicate entries.
- Automatically created on UNIQUE constraints.
Example:
CREATE UNIQUE INDEX idx_email ON Employees(Email);
4. Composite Index (Multi-Column Index)
- Index created on two or more columns.
- Useful for queries filtering or sorting on multiple fields.
Example:
CREATE INDEX idx_name_dept ON Employees(Name, DepartmentID);
5. Full-Text Index
- Used for text searching like CONTAINS, FREETEXT.
- Useful when dealing with large text fields or documents.
Example:
CREATE FULLTEXT INDEX ON Articles(Content) KEY INDEX PK_Articles;
- Optimized for geographic or spatial data types (e.g., geometry, geography).
- Used in location-based applications.
Example:
CREATE SPATIAL INDEX idx_location ON Locations(GeoData);
17. Difference Between Clustered Index and Non-Clustered Index.
The main difference between a clustered and a non-clustered index lies in how data is stored and accessed.
Clustered Index
- Physically sorts and stores the actual data rows in the table.
- A table can have only one clustered index.
- The rows are stored in the same order as the clustered index.
- Faster for range-based queries and queries that return large result sets.
Non-Clustered Index
- Stores a separate structure from the data.
- Contains pointers (row locators) to the actual data rows.
- A table can have multiple non-clustered indexes.
- Ideal for search/filter operations on frequently queried columns.
In short:
- A clustered index defines the physical layout of the table.
- A non-clustered index is a separate lookup structure that speeds up queries without changing data order.
18. What is the purpose of the GROUP BY clause?
The GROUP BY clause is used in SQL to group rows that have the same values in specified columns into summary rows. It is commonly used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN() to perform calculations on each group of rows. Instead of applying an aggregate to the whole table, GROUP BY allows you to apply it to subsets of data grouped by one or more columns. This is especially useful for creating reports or analyzing data patterns.
Group BY Clause:
- Group data based on one or more columns.
- Always used with aggregate functions.
- Returns one result per group.
- Columns in the SELECT clause must be part of the group or aggregated.
Example:
SELECT Department, COUNT(*) AS TotalEmployees FROM Employees GROUP BY Department;
In short: GROUP BY helps summarize and analyze data by grouping similar rows and applying aggregate functions to them.
19. Explain the HAVING clause and how it differs from WHERE.
The HAVING clause is used in SQL to filter groups of rows created by the GROUP BY clause. It is similar to the WHERE clause, but while WHERE filters individual rows before grouping, HAVING filters groups after aggregation.
Key Differences Between WHERE and HAVING:
| Feature | WHERE | HAVING |
|---|---|---|
| Used On | Individual rows | Groups of rows |
| When Applied | Before grouping (GROUP BY) | After grouping |
| Aggregates | Cannot use aggregate functions | Can use aggregate functions |
Example: Suppose you want to find departments with more than 5 employees:
SELECT Department, COUNT(*) AS TotalEmployees FROM Employees GROUP BY Department HAVING COUNT(*) > 5;
Example: Combine WHERE and HAVING.
SELECT Department, COUNT(*) AS TotalEmployees FROM Employees WHERE Status = 'Active' GROUP BY Department HAVING COUNT(*) > 5;
- WHERE filters rows before grouping (Status = 'Active').
- HAVING filter groups after counting employees.
20. What is an Auto Increment or Identity column in SQL Server?
In SQL Server, an Identity column (also known as an Auto Increment column) is used to automatically generate unique numeric values for a column, usually the primary key.
When a new row is inserted, SQL Server automatically increases the value based on a specified seed (starting point) and increment.
Example:
CREATE TABLE Employees ( EmployeeID INT IDENTITY(1,1) PRIMARY KEY, Name VARCHAR(100) );
21. What is a Stored Procedure in SQL Server, and how is it different from a Function?
Stored Procedure:
A Stored Procedure is a precompiled collection of SQL statements that can include logic, conditions, loops, and parameters. It is stored in the database and can be executed whenever needed, which improves code reusability, security, and performance.
Example (Stored Procedure):
CREATE PROCEDURE GetEmployeeByDept @DeptID INT AS BEGIN SELECT * FROM Employees WHERE DepartmentID = @DeptID; END
EXEC GetEmployeeByDept @DeptID = 2;
A Function in SQL Server is a reusable database object that accepts input parameters, performs a calculation or operation, and returns a single value or a table.
Functions are typically used for computations, data formatting, or returning small result sets. Unlike stored procedures, functions cannot modify data (no INSERT, UPDATE, or DELETE).
Example (Function):
CREATE FUNCTION GetFullName( @FirstName VARCHAR(50), @LastName VARCHAR(50) ) RETURNS VARCHAR(100) AS BEGIN RETURN @FirstName + ' ' + @LastName; END
SELECT dbo.GetFullName('John', 'Doe');
Difference Between Stored Procedure and Function.
| Feature | Stored Procedure | Function |
|---|---|---|
| Purpose | Perform actions like insert, update, delete, and select | Perform calculations or return computed values |
| Return Type | Can return zero, one, or multiple values using OUT or RETURN | Must return a single scalar value or a table |
| Usage in SQL Statements | Cannot be used directly in SELECT, WHERE, or JOIN | Can be used inside SELECT, WHERE, JOIN, etc. |
| DML Operations | Can perform INSERT, UPDATE, and DELETE | Cannot perform DML operations (read-only) |
| Transaction Control | Can manage transactions using BEGIN TRAN, COMMIT, ROLLBACK | Cannot manage transactions |
| Error Handling | Supports TRY...CATCH for error handling | Limited or no error handling support |
| Output Parameters | Supports input, output, and input-output parameters | Supports only input parameters |
| Usage Scenario | Complex business logic, batch processing, CRUD operations | Reusable calculations, formatting, data transformation |
22. What are triggers in SQL?
A Trigger in SQL is a special type of stored procedure that automatically executes in response to certain events on a table or view. These events are usually INSERT, UPDATE, or DELETE operations.
Triggers are used to enforce business rules, maintain audit logs, or automatically update related data without requiring explicit calls in application code.
Types of Triggers in SQL Server:
- AFTER Trigger: Executes after an INSERT, UPDATE, or DELETE action is completed.
- INSTEAD OF Trigger: Executes in place of the triggering action (commonly used on views).
- DDL Trigger: Fires in response to DDL events like CREATE, ALTER, and DROP.
- LOGON Trigger: Fires when a user logs into SQL Server.
Use triggers carefully, as they can impact performance and lead to recursive or unexpected behavior.
23. How can you handle NULL values in SQL?
In SQL, NULL represents a missing, unknown, or undefined value. It is not the same as zero or an empty string. Handling NULL properly is important to avoid unexpected results in queries, especially in comparisons and aggregations.
Ways to Handle NULL Values in SQL:
1. IS NULL / IS NOT NULL
Used to check if a value is NULL or not.
SELECT * FROM Employees WHERE ManagerID IS NULL;
2. COALESCE()
Returns the first non-null value from a list.
SELECT Name, COALESCE(Email, 'No Email') AS ContactEmail FROM Employees;
3. ISNULL() (SQL Server specific)
Replaces NULL with a specified value.
SELECT ISNULL(PhoneNumber, 'Not Provided') AS Phone FROM Customers;
Returns NULL if the two expressions are equal; otherwise, returns the first one.
SELECT NULLIF(Salary, 0) AS ValidSalary FROM Employees;
Custom handling of NULL values using conditional logic.
SELECT Name, CASE WHEN Email IS NULL THEN 'Missing Email' ELSE Email END AS EmailStatus FROM Employees;
Best Practices:
- Never use = or <> to compare with NULL; always use IS NULL or IS NOT NULL.
- Use COALESCE for multi-value fallback.
- Use CASE for flexible, readable control.
24. What is a CTE (Common Table Expression)?
A CTE (Common Table Expression) is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It's defined using the WITH keyword and improves the readability and structure of complex queries, especially when using recursive logic or subqueries.
Syntax of CTE:
WITH CTE_Name AS ( SELECT column1, column2 FROM SomeTable WHERE condition ) SELECT * FROM CTE_Name;
Example:
WITH ActiveEmployees AS ( SELECT EmployeeID, Name FROM Employees WHERE Status = 'Active' ) SELECT * FROM ActiveEmployees;
25. Explain the concept of window functions.
Window functions in SQL allow you to perform calculations across a set of rows that are related to the current row, without collapsing the result set like GROUP BY does. They operate over a "window" of rows, defined by the OVER() clause, and return a value for each row in the result.
Example: Rank Employees by Salary Within Each Department.
SELECT Name, Department, Salary, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS SalaryRank FROM Employees;
- PARTITION BY Department divides the data into groups by department.
- ORDER BY Salary DESC ranks employees from highest to lowest salary within each department.
Output:
| Name | Department | Salary | SalaryRank |
|---|---|---|---|
| Alice | HR | 80000 | 1 |
| Bob | HR | 70000 | 2 |
| Carol | IT | 95000 | 1 |
| Dave | IT | 90000 | 2 |
26. What is the difference between UNION and UNION ALL?
Both UNION and UNION ALL are used to combine the results of two or more SELECT statements, but the key difference is how they handle duplicate rows.
UNION
- Combines results and removes duplicate rows.
- Performs sorting internally to eliminate duplicates, which may impact performance.
Example:
SELECT City FROM Customers UNION SELECT City FROM Suppliers;
UNION ALL
- Combines results and includes all rows, including duplicates.
- Faster than UNION because it doesn’t check for duplicates.
Example:
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers;
27. How do you perform error handling in SQL?
In SQL Server, error handling is done using the TRY...CATCH block. It allows us to catch runtime errors that occur during the execution of a SQL statement and handle them gracefully without terminating the entire batch or stored procedure.
Syntax:
BEGIN TRY -- SQL statements that might cause an error END TRY BEGIN CATCH -- Error handling logic END CATCH
Example:
BEGIN TRY -- Attempt to divide by zero (will cause error) DECLARE @x INT = 1, @y INT = 0, @result INT; SET @result = @x / @y; END TRY BEGIN CATCH PRINT 'An error occurred:'; PRINT ERROR_MESSAGE(); -- Returns the actual error message END CATCH;
- ERROR_NUMBER() Returns the error number
- ERROR_MESSAGE() Returns the error message text
- ERROR_LINE() Returns the line number where error occurred
- ERROR_SEVERITY() Returns severity of the error
- ERROR_PROCEDURE() Returns the name of the stored proc
Use Cases:
- Prevent application crashes due to SQL errors
- Log error details into an audit table
- Handle divide-by-zero, constraint violations, deadlocks, etc.
28. Explain ACID properties.
The ACID properties are a set of principles that ensure reliable, consistent, and safe transaction processing in a relational database. ACID stands for:
A – Atomicity
Atomicity ensures that a transaction is treated as a single unit, which either completes entirely or does not happen at all. If any part of the transaction fails, all changes are rolled back.
Example: If ₹500 is debited from one bank account but not credited to another, the debit must be undone.
C – Consistency
Consistency ensures that a transaction brings the database from one valid state to another, maintaining all data integrity rules, constraints, and relationships.
Example: If a constraint says Salary > 0, inserting -100 must be rejected to maintain consistency.
I – Isolation
Isolation ensures that multiple transactions can occur concurrently without interfering with each other, and the final result is the same as if the transactions ran one at a time.
Example: If two users try to buy the last product at the same time, isolation ensures only one succeeds.
D – Durability
Durability ensures that once a transaction is committed, the changes are permanent and persist even in the case of a system crash or power failure.
Example: If you book a movie ticket and the server crashes right after, the booking remains confirmed.
29. How can you optimize a slow-running query?
To optimize a slow-running SQL query, I would follow a structured approach that focuses on improving execution time, resource usage, and query logic. Here's how:
1. Use Indexes to Improve Query Performance.
One of the most effective ways to speed up a slow-running SQL query is to use indexes. Indexes are data structures that allow the database engine to quickly locate and access the data without scanning the entire table. If your query involves filtering (WHERE), sorting (ORDER BY), joining (JOIN), or grouping (GROUP BY) on certain columns, make sure those columns are properly indexed.
2. Avoid Using SELECT * in Queries
Using SELECT * in your SQL queries might seem convenient, but it can hurt performance and clarity. When you use SELECT *, the database retrieves all columns from the table, even if you only need a few.
3. Optimize JOIN Operations in Your Query
JOIN operations are often necessary but can be a major cause of performance issues, especially when dealing with large datasets. To optimize joins, ensure that the columns used in the JOIN condition are indexed and of the same data type.
4. Use WHERE Clauses Efficiently
Efficient use of the WHERE clause is essential for filtering data early in the query execution process. Always avoid using functions or expressions directly on columns in the WHERE clause, as they can prevent the database from using indexes.
5. Avoid Correlated Subqueries
Correlated subqueries are subqueries that depend on values from the outer query. They are executed once for every row in the outer query, which can lead to significant performance issues, especially with large datasets.
To optimize a slow-running query, I focus on indexing, query structure, execution plan analysis, and minimizing unnecessary operations. Good design + smart tuning = better performance.
30. What are the different types of relationships in databases?
In relational database design, relationships define how data in one table is related to data in another. These relationships are crucial for maintaining data integrity and for structuring efficient queries using primary keys and foreign keys. There are three main types of relationships:
1. One-to-One (1:1) Relationship
In a one-to-one relationship, each record in Table A is related to only one record in Table B, and vice versa. This is less common but used when you want to split rarely-used or sensitive information into a separate table for security or organization.
- Example: A User table and a UserProfile table, where each user has exactly one profile.
- Enforced By: Primary Key in both tables with a Foreign Key referencing it.
2. One-to-Many (1:N) Relationship
This is the most common relationship. In a one-to-many relationship, a single record in Table A can be related to multiple records in Table B, but each record in Table B is related to only one record in Table A.
- Example: A Customer can place many Orders, but each Order is placed by only one Customer.
- Enforced By: A Foreign Key in the "many" side (Orders) referencing the Primary Key in the "one" side (Customer).
3. Many-to-Many (M: N) Relationship
In a many-to-many relationship, multiple records in Table A can be associated with multiple records in Table B, and vice versa. This relationship is implemented using a junction (or bridge) table that contains Foreign Keys referencing both related tables.
- Example: Students can enroll in multiple Courses, and each Course can have many Students. It is implemented via a third table like StudentCourses(StudentID, CourseID).
- Enforced By: Two One-to-Many relationships with a junction table in between.
31. Explain DeadLock in SQL Database.
A deadlock in SQL occurs when two or more transactions block each other by holding locks on resources the other transactions need, and neither can proceed unless one is aborted. It's a classic case of a circular wait that causes both processes to be stuck indefinitely.
Let's say:
- Transaction A locks Table1 and wants to update Table2.
- Transaction B locks Table2 and wants to update Table1.
Now both are waiting for each other to release the lock on the other table — this causes a deadlock.
How to Prevent Deadlocks?
Use row-level locking rather than table-level if possible.
Most databases, like SQL Server or MySQL, have a deadlock detection mechanism.
They will automatically choose one of the transactions as a “victim”, terminate it, and roll it back so the other transaction can continue. Or to prevent deadlock, follow the points below:
- Access resources in the same order in all transactions.
- Keep transactions short and avoid long-running locks.
- Use lower isolation levels when appropriate (e.g., READ COMMITTED).
- Avoid user interaction (like manual approval) inside transactions.
32. Explain the concept of data warehousing.
A Data Warehouse is a centralized repository that stores large volumes of historical, structured data collected from various heterogeneous sources like transactional databases, CRM systems, or flat files. It is specifically designed for querying, analysis, and reporting — not for day-to-day operations.
The main goal of a data warehouse is to support business intelligence (BI) and decision-making by providing a unified, consistent view of the data.
Example: A retail company may store daily sales data from all its stores in a data warehouse. Analysts can then query this data to generate monthly sales reports, identify trends, or forecast demand.
33. What is the difference between CHAR and VARCHAR2 in SQL?
Both CHAR and VARCHAR2 are used to store character strings in SQL (especially in Oracle), but they differ in how they handle storage and memory.
CHAR (Character)
- CHAR is a fixed-length data type.
- When you define CHAR(n), it always uses exactly n bytes, even if the actual value is shorter.
- If you store a shorter string, Oracle pads the remaining space with spaces.
VARCHAR2 (Variable Character)
- VARCHAR2 is a variable-length data type.
- When you define VARCHAR2(n), it uses only as much space as needed, up to n.
- It does not pad with spaces, making it more efficient in most cases.
34. How to create a new table in SQL?
We use the CREATE statement with column names and matching datatypes to create a new Table in SQL.
SQL Query:
CREATE TABLE Employees ( ID INT PRIMARY KEY, Name VARCHAR(50), Department VARCHAR(50), Salary DECIMAL(10,2), JoinDate DATE );
35. How to insert data into a table?
We use the INSERT INTO statement to insert data into an existing table. We can insert a single record or multiple records at a time.
SQL Query to Insert a single record:
INSERT INTO Employees (ID, Name, Department, Salary, JoinDate) VALUES (1, 'Alice', 'HR', 60000, '2022-01-15');
SQL Query to Insert multiple records:
INSERT INTO Employees (ID, Name, Department, Salary, JoinDate) VALUES (2, 'Bob', 'IT', 70000, '2022-03-01'), (3, 'Carol', 'Finance', 65000, '2022-02-20');
36. How to create a Foreign Key in a table?
To mark any column as a Foreign key, we use the FOREIGN KEY and REFERENCE Keywords.
SQL Query to add foreign key:
CREATE TABLE Departments ( DeptID INT PRIMARY KEY, DeptName VARCHAR(50) ); ALTER TABLE Employees ADD CONSTRAINT FK_Department FOREIGN KEY (Department) REFERENCES Departments(DeptName);
37. How do you select all columns from a table named employees?
The asterisk (*) selects all columns from the specified table.
SELECT * FROM employees;
38. How do you select specific columns from the employees table?
This query retrieves only the specified columns from the table.
SELECT first_name, last_name FROM employees;
39. How can you count the number of records in the employees table?
The COUNT(*) function counts all rows in the table.
SELECT COUNT(*) FROM employees;
40. How to retrieve a Unique value from a specific Column?
We use the DISTINCT keyword to select unique records from the table.
SELECT DISTINCT department FROM employees;
41. How to update an existing record in the table?
We use the UPDATE keyword to update the data of an existing record.
UPDATE employees SET salary = salary * 1.1 WHERE performance_rating = 'Excellent';
42. How to delete an existing row from the table?
We use the DELETE keyword with a condition to find the record that needs to be deleted.
DELETE FROM employees WHERE termination_date IS NOT NULL;
43. How to add a new Column in an existing table?
Query to add a new column to an existing table:
ALTER TABLE employees ADD COLUMN date_of_birth DATE;
44. How to perform pagination in SQL?
Pagination is the process of dividing a large result set into smaller chunks (pages). It's commonly used in applications to show, for example, 10 records per page.
The approach varies slightly depending on the SQL database you are using.
SQL Server (Using OFFSET-FETCH):
SELECT * FROM Employees ORDER BY ID OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Explanation:
- OFFSET 20: Skip the first 20 rows.
- FETCH NEXT 10 ROWS ONLY: Return the next 10 rows.
- This gives you Page 3 (if Page 1 = 0–9, Page 2 = 10–19, Page 3 = 20–29).
MySQL / PostgreSQL (Using LIMIT and OFFSET):
SELECT * FROM Employees ORDER BY ID LIMIT 10 OFFSET 20;
- LIMIT 10: Return 10 rows.
- OFFSET 20: Skip the first 20 rows.
45. How do you find the second-highest salary in the employees table?
This query finds the maximum salary that is less than the highest salary, effectively retrieving the second-highest salary. It uses a subquery for comparison.
SQL Query:
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
46. How do you find employees with salaries above the average salary?
This query filters employees whose salaries exceed the average salary calculated from the entire table. It helps identify high earners.
SQL Query:
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
47. How can you find the highest salary in each department?
This query groups employees by department and retrieves the maximum salary for each department. It helps identify top earners in each area.
SQL Query:
SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;
48. How can you find the total number of projects each employee is working on?
This query groups by employee and counts their assigned projects.
SQL Query:
SELECT employee_id, COUNT(project_id) FROM project_assignments GROUP BY employee_id;
49. What is SQL injection, and how can it be prevented?
SQL Injection is a security vulnerability that occurs when untrusted input is inserted into an SQL query without proper validation or sanitization, allowing attackers to manipulate the query and potentially access, modify, or delete database data.
1. Use Parameterized Queries / Prepared Statements
- This ensures input values are treated strictly as data, not executable code.
2. Use ORM Frameworks
- Tools like Entity Framework, Hibernate, or Sequelize automatically parameterize queries.
3. Input Validation & Escaping
- Always validate and sanitize user input, especially when it’s used in dynamic SQL.
4. Limit Database Permissions
- Do not use admin-level users for database access from applications.
5. Use Stored Procedures Safely
- Even stored procedures can be vulnerable if they concatenate user input. Always use parameters.
50. How would you implement user access control in a database?
User access control in a database involves managing who can access what data and what actions they are allowed to perform. It is essential for data security, compliance, and ensuring users only interact with the data they are authorized to see or modify.
Steps to Implement User Access Control:
🔹 1. Create Database Users/Roles
Define users or roles for different levels of access.
🔹 2. Grant Required Permissions
Grant only the necessary permissions to each user or role based on the principle of least privilege.
🔹 3. Use Roles for Grouped Access
Instead of assigning permissions to individual users, assign them to roles and assign users to those roles.
🔹 4. Revoke Permissions When Needed
Remove access when it's no longer needed or when roles change.
🔹 5. Implement Auditing (Optional but Recommended)
Track who accessed or changed what data using audit logs or triggers.
Best Practices:
- Follow the least privilege principle (give minimal required access).
- Use roles instead of individual permissions for scalability.
- Keep an audit trail of changes and accesses.
- Review access regularly and revoke unused or risky privileges.
- Secure administrative accounts with strong passwords and multi-factor authentication.
Conclusion.
SQL interviews are designed to test your ability to think logically, solve problems efficiently, and write clean, optimized queries. By practicing these top 50 questions and answers, you're equipping yourself with the foundational and intermediate-level knowledge needed to confidently tackle any SQL interview.
Trends is an amazing magazine Blogger theme that is easy to customize and change to fit your needs.