SQL is one of the most frequently tested technical skills in database, software development, backend development, data analysis, and IT support interviews. A candidate may be asked simple definition-based questions as well as practical SQL problems that require writing queries.
Knowing SQL syntax alone is not enough for an interview. You should also understand how tables are related, how constraints protect data, how joins combine information, how transactions work, and how queries can be improved for better performance.
This SQL Interview Questions and Answers guide has been written in a simple and practical format. It starts with fundamental database concepts and gradually moves toward joins, subqueries, normalization, indexes, views, transactions, SQL security, and commonly asked query-based interview problems.
The questions are useful for B.Tech, M.Tech, BCA, MCA, Polytechnic, computer science students, freshers, developers, database professionals, and candidates preparing for technical examinations and placement interviews.
A good SQL interview preparation strategy should combine theoretical concepts with practical query writing. Interviewers often begin with basic questions and then give a small database problem to test how you apply SQL.
SQL stands for Structured Query Language. It is a language used to communicate with relational database systems. Through SQL, users and applications can create database objects, add records, modify existing information, remove records, and retrieve required data.
For example, an application can use a SELECT statement to retrieve student information from a Student table.
SELECT * FROM Student;
SQL is supported by several relational database systems, although the exact syntax and available features can vary between products.
A database is an organized collection of information that can be stored, searched, updated, and managed electronically.
For example, a university database may contain separate tables for students, teachers, courses, fees, attendance, and examination results. These tables can be connected using appropriate relationships.
DBMS stands for Database Management System. It is software that provides facilities for creating, storing, retrieving, modifying, and controlling data in databases.
A DBMS also provides mechanisms for security, concurrency, backup, recovery, and controlled access to data.
Examples include:
RDBMS stands for Relational Database Management System. It is a database management approach in which information is organized into tables containing rows and columns.
Tables can be connected through relationships. Keys such as primary keys and foreign keys are commonly used to identify records and establish relationships between tables.
MySQL, PostgreSQL, Oracle Database, and Microsoft SQL Server are examples of relational database systems.
| DBMS | RDBMS |
|---|---|
| Provides facilities for managing databases. | Manages data using the relational model. |
| Relationship support depends on the database model and product. | Relationships between tables are a central part of the relational model. |
| May use different forms of data organization. | Primarily organizes information using tables. |
| Features vary depending on the DBMS. | Typically provides relational features such as keys and constraints. |
SQL commands are statements used to communicate with a database. They can be grouped according to the type of operation they perform.
| Category | Full Form | Common Commands |
|---|---|---|
| DDL | Data Definition Language | CREATE, ALTER, DROP, TRUNCATE |
| DML | Data Manipulation Language | INSERT, UPDATE, DELETE |
| DQL | Data Query Language | SELECT |
| DCL | Data Control Language | GRANT, REVOKE |
| TCL | Transaction Control Language | COMMIT, ROLLBACK, SAVEPOINT |
DDL means Data Definition Language. It is used for defining or changing the structure of database objects such as tables.
Common DDL statements include CREATE, ALTER, DROP, and TRUNCATE. Exact transactional behavior of individual statements can depend on the database system.
CREATE TABLE Student
(
Student_ID INT,
Student_Name VARCHAR(50),
Age INT
);
DML means Data Manipulation Language. DML statements are used to add, modify, or remove rows from database tables.
Common examples are INSERT, UPDATE, and DELETE.
INSERT INTO Student (Student_ID, Student_Name, Age) VALUES (101, 'Rahul', 20);
DQL is commonly used to describe SQL statements that retrieve data from a database. SELECT is the primary statement used for this purpose.
SELECT Student_ID, Student_Name FROM Student;
A table is a database object that organizes related information into rows and columns. A column describes a particular attribute, while a row represents one stored record.
| Student_ID | Name | Age |
|---|---|---|
| 101 | Amit | 20 |
| 102 | Neha | 21 |
A primary key is a constraint used to identify rows uniquely within a table. A primary key cannot contain NULL values, and the database enforces uniqueness for the key values.
CREATE TABLE Student
(
Student_ID INT PRIMARY KEY,
Name VARCHAR(50)
);
A table has one primary key constraint, although that key can contain more than one column.
A foreign key is a column or group of columns used to create a relationship between tables. It references a candidate key, commonly the primary key, in another table.
CREATE TABLE Customer
(
Customer_ID INT PRIMARY KEY,
Customer_Name VARCHAR(50)
);
CREATE TABLE Orders
(
Order_ID INT PRIMARY KEY,
Customer_ID INT,
FOREIGN KEY(Customer_ID)
REFERENCES Customer(Customer_ID)
);
The foreign key helps maintain referential integrity between the two tables.
A candidate key is a minimal set of attributes that can uniquely identify a row in a relation. A table may have more than one candidate key.
One candidate key can be selected as the primary key, while other candidate keys may be enforced using appropriate uniqueness constraints.
A super key is a set of one or more attributes that can uniquely identify a row.
A candidate key is a minimal super key, meaning that removing any attribute from it would cause it to lose its uniqueness property.
A composite key is a key made up of two or more columns. It is useful when no single column can uniquely identify a record.
CREATE TABLE Enrollment
(
Student_ID INT,
Course_ID INT,
PRIMARY KEY(Student_ID, Course_ID)
);
Here, the combination of Student_ID and Course_ID identifies an enrollment record.
Constraints are rules associated with table columns that help maintain valid and reliable data.
Common constraints include:
The NOT NULL constraint requires a column to contain a value when a row is inserted or updated in a way that would otherwise leave that column NULL.
CREATE TABLE Employee
(
Employee_ID INT,
Employee_Name VARCHAR(50) NOT NULL
);
A UNIQUE constraint prevents duplicate values for the constrained key according to the rules of the particular database system.
For example, an organization may require every employee email address to be unique.
CREATE TABLE Employee
(
Employee_ID INT PRIMARY KEY,
Email VARCHAR(100) UNIQUE
);
A CHECK constraint specifies a condition that values must satisfy.
CREATE TABLE Employee
(
Employee_ID INT,
Age INT CHECK (Age >= 18)
);
The exact behavior and enforcement capabilities of CHECK constraints can vary among older database versions, so always verify the target DBMS.
A DEFAULT value is automatically used when an INSERT statement does not provide a value for that column.
CREATE TABLE Employee
(
Employee_ID INT,
Status VARCHAR(20) DEFAULT 'Active'
);
A JOIN combines rows from two or more tables using a relationship between their columns. Joins are essential because a properly designed relational database often stores different types of information in separate tables.
For example, customer information may be stored in one table while orders are stored in another. A JOIN can combine the two datasets when their customer identifiers match.
| JOIN | Purpose |
|---|---|
| INNER JOIN | Returns rows having matching values in both tables. |
| LEFT JOIN | Returns every row from the left table and matching rows from the right table. |
| RIGHT JOIN | Returns every row from the right table and matching rows from the left table. |
| FULL OUTER JOIN | Returns matched rows together with unmatched rows from both sides where supported. |
| SELF JOIN | Uses a table as both sides of a join. |
| CROSS JOIN | Produces combinations of rows from both tables. |
INNER JOIN returns only the rows for which the join condition finds a match in both tables.
SELECT
Student.Student_Name,
Course.Course_Name
FROM Student
INNER JOIN Course
ON Student.Course_ID = Course.Course_ID;
If a student does not have a matching course record, that student will not appear in the result of this INNER JOIN.
A LEFT JOIN preserves all rows from the left table. When a matching row does not exist in the right table, columns from the right table normally appear as NULL.
SELECT
Employee.Employee_Name,
Department.Department_Name
FROM Employee
LEFT JOIN Department
ON Employee.Department_ID = Department.Department_ID;
This type of query is useful when you want to find employees even when some employees have not yet been assigned to a department.
A SELF JOIN joins a table to itself. It is useful when records within the same table have relationships with one another.
An employee table containing Manager_ID is a common example.
SELECT
E.Employee_Name AS Employee,
M.Employee_Name AS Manager
FROM Employee E
LEFT JOIN Employee M
ON E.Manager_ID = M.Employee_ID;
A subquery is a query placed inside another SQL statement. The result of the inner query can be used by the outer query.
For example, the following query finds employees whose salary is greater than the average salary.
SELECT Employee_Name, Salary
FROM Employee
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employee
);
Subqueries can be categorized according to the number of rows they return and how they interact with the outer query.
A correlated subquery refers to a value from the outer query. Therefore, its result depends on the current row being considered by the outer query.
SELECT
E.Employee_Name,
E.Salary
FROM Employee E
WHERE E.Salary >
(
SELECT AVG(E2.Salary)
FROM Employee E2
WHERE E2.Department_ID = E.Department_ID
);
This query compares each employee's salary with the average salary of that employee's department.
Normalization is a database design process used to organize related information into suitable tables and reduce unnecessary repetition of data.
A normalized design can make data maintenance easier and can reduce certain types of insertion, update, and deletion anomalies.
| Normal Form | Basic Idea |
|---|---|
| 1NF | Values are organized into atomic fields and repeating groups are removed. |
| 2NF | 1NF is satisfied and non-key attributes do not depend on only part of a composite key. |
| 3NF | 2NF is satisfied and non-key attributes do not depend transitively on a key. |
| BCNF | A stronger form of normalization in which every determinant is a candidate key. |
Denormalization is a deliberate database design technique in which some redundancy is introduced to simplify data retrieval or improve performance for particular workloads.
It should not be treated as simply the opposite of good database design. Denormalization is normally considered after understanding query patterns, storage costs, consistency requirements, and performance measurements.
An index is a database structure that helps the database locate rows more efficiently for suitable queries.
An index can be compared to the index section of a book. Instead of scanning every page, the required topic can be located through the index.
CREATE INDEX idx_employee_name ON Employee(Employee_Name);
A view is a named query that can be used as a virtual table. The view normally represents data obtained from one or more underlying tables.
Views are useful for simplifying frequently used queries and controlling which columns or rows are exposed to users.
CREATE VIEW Employee_View AS
SELECT
Employee_ID,
Employee_Name,
Department_ID
FROM Employee;
The exact behavior of views, including whether a particular view can be modified, depends on the database system and query structure.
A stored procedure is a named collection of SQL statements and procedural logic stored in the database and invoked when required.
Stored procedures can be useful for encapsulating database operations and implementing reusable database-side logic. Their syntax and capabilities vary between database products.
DELIMITER //
CREATE PROCEDURE GetEmployees()
BEGIN
SELECT *
FROM Employee;
END //
DELIMITER ;
| Function | Stored Procedure |
|---|---|
| Designed to return a value. | Can perform a broader sequence of operations and may return results in DBMS-specific ways. |
| Often used inside expressions where supported. | Normally invoked as a separate database operation. |
| Rules for modifying data vary by DBMS. | Can contain data modification operations depending on the DBMS. |
| Syntax varies between database systems. | Syntax also varies between database systems. |
A transaction is a logical unit of database work containing one or more operations that should be handled according to a defined transaction policy.
A bank transfer is a common example. Money may need to be deducted from one account and added to another. The application should not leave the database in an incomplete state if one part of the operation fails.
| Property | Explanation |
|---|---|
| Atomicity | The transaction is treated as a unit so its operations are not partially committed. |
| Consistency | A successful transaction should leave the database satisfying its defined rules. |
| Isolation | Concurrent transactions are controlled so their intermediate effects do not incorrectly interfere with one another. |
| Durability | Once a transaction is committed, its changes are preserved according to the database's durability guarantees. |
COMMIT makes the changes of the current transaction permanent according to the database system's transaction rules.
ROLLBACK cancels uncommitted changes and returns the transaction to an earlier valid state.
START TRANSACTION; UPDATE Account SET Balance = Balance - 1000 WHERE Account_ID = 101; UPDATE Account SET Balance = Balance + 1000 WHERE Account_ID = 102; COMMIT;
The exact transaction syntax can vary between database systems.
SQL Injection is a security vulnerability that can occur when an application constructs SQL statements by directly combining untrusted user input with SQL code.
A malicious input may alter the intended meaning of a query and potentially expose, modify, or destroy data depending on the application's privileges and database configuration.
SQL optimization means improving the efficiency of a query while preserving the required result. Optimization should normally be based on actual execution plans and workload measurements rather than assumptions alone.
| DELETE | TRUNCATE | DROP |
|---|---|---|
| Removes rows from a table. | Removes rows from a table according to the DBMS's TRUNCATE semantics. | Removes the database object itself. |
| Can normally use a WHERE condition. | Does not use a WHERE condition. | No row filtering is performed. |
| Table structure remains. | Table structure remains. | Table structure is removed. |
| Transaction and rollback behavior depends on the DBMS. | Transaction behavior varies considerably between DBMS products. | Transaction behavior varies by database system. |
| WHERE | HAVING |
|---|---|
| Filters rows. | Filters grouped results. |
| Usually applied before GROUP BY processing. | Used after grouping to filter groups. |
| Can be used without GROUP BY. | Commonly used with GROUP BY. |
SELECT Department_ID, AVG(Salary) FROM Employee WHERE Salary > 20000 GROUP BY Department_ID HAVING AVG(Salary) > 40000;
| UNION | UNION ALL |
|---|---|
| Combines result sets and removes duplicate rows. | Combines result sets without removing duplicate rows. |
| May require additional work to eliminate duplicates. | Usually avoids the duplicate-elimination step. |
| Used when duplicate results are not required. | Useful when duplicate rows should be preserved. |
One common approach is to find the highest salary below the overall maximum salary.
SELECT MAX(Salary) AS Second_Highest_Salary
FROM Employee
WHERE Salary <
(
SELECT MAX(Salary)
FROM Employee
);
This approach returns the second distinct highest salary when such a value exists.
GROUP BY and HAVING can be used to identify values appearing more than once.
SELECT
Employee_Name,
COUNT(*) AS Total_Count
FROM Employee
GROUP BY Employee_Name
HAVING COUNT(*) > 1;
The exact columns used for identifying duplicates should depend on what makes a record logically duplicate in the application.
A LEFT JOIN can be used when we want to keep all employees and identify those without a matching department.
SELECT
E.Employee_Name
FROM Employee E
LEFT JOIN Department D
ON E.Department_ID = D.Department_ID
WHERE D.Department_ID IS NULL;
SELECT MAX(Salary) AS Highest_Salary FROM Employee;
If the requirement is to return the complete employee record having the highest salary, a different query using ORDER BY, a subquery, or a ranking function may be more appropriate depending on whether ties should be included.
SELECT
Employee_Name,
Salary
FROM Employee
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employee
);
The inner query calculates the average salary, and the outer query selects employees whose salary is greater than that value.
SELECT
Department_ID,
COUNT(*) AS Employee_Count
FROM Employee
GROUP BY Department_ID;
GROUP BY creates one result group for each department identifier.
SELECT
Employee_Name,
Salary
FROM Employee
ORDER BY Salary DESC;
DESC displays higher salary values first. ASC can be used when ascending order is required.
No. A primary key is designed to uniquely identify rows and does not allow NULL values.
A table has one primary key constraint. However, that primary key can consist of multiple columns, which is called a composite primary key.
Yes. A table can contain multiple foreign keys when it needs to reference different tables or different candidate keys.
Referential integrity ensures that relationships represented by foreign keys remain valid according to the rules defined for the database.
NULL represents the absence of a known value. It is not the same as zero, an empty string, or the word "NULL".
SELECT * FROM Employee WHERE Department_ID IS NULL;
The IS NULL and IS NOT NULL operators should be used for testing NULL values.
An aggregate function processes multiple rows and produces a summarized result.
Common aggregate functions include:
GROUP BY divides rows into groups based on one or more columns. Aggregate functions can then be applied to each group.
SELECT
Department_ID,
AVG(Salary) AS Average_Salary
FROM Employee
GROUP BY Department_ID;
ORDER BY is used to arrange the rows returned by a query according to one or more expressions.
SELECT * FROM Employee ORDER BY Salary DESC;
| CHAR | VARCHAR |
|---|---|
| Designed for fixed-length character values. | Designed for variable-length character values. |
| Suitable when values generally have a consistent length. | Suitable when stored text lengths vary. |
| Storage behavior depends on the database system. | Storage behavior also depends on the database system. |
Freshers should focus on understanding concepts instead of memorizing one-line definitions. During an interview, an interviewer may ask a follow-up question immediately after your first answer.
| Topic | Preparation Status |
|---|---|
| SQL Fundamentals | Understand SQL and relational databases |
| DDL and DML | Practice common commands |
| Keys | Learn Primary, Foreign, Candidate, Super and Composite Keys |
| Constraints | Practice PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and NOT NULL |
| Joins | Practice INNER, LEFT, RIGHT, FULL, SELF and CROSS JOIN concepts |
| Subqueries | Practice nested and correlated queries |
| Normalization | Understand 1NF, 2NF, 3NF and BCNF |
| Indexes | Understand advantages and maintenance cost |
| Transactions | Learn COMMIT, ROLLBACK and ACID |
| Practical Queries | Practice salary, duplicate, grouping and join problems |
SQL interviews test much more than the ability to write a SELECT statement. A strong candidate should understand how relational databases organize information and how SQL can be used to retrieve, modify, validate, and analyze that information.
The most important areas to practice include SQL commands, keys, constraints, joins, subqueries, aggregate functions, GROUP BY, normalization, indexes, views, transactions, and practical query-solving problems.
Database systems differ in syntax and behavior, so candidates should also identify which database product an interviewer expects, such as MySQL, PostgreSQL, Oracle Database, or SQL Server. This is especially important for advanced features and transaction-related behavior.
Regular query practice is the best way to improve SQL interview performance. Instead of memorizing solutions, try to understand the tables, identify the required result, and then build the query step by step.