SQL Interview Questions

SQL interview questions commonly test database fundamentals, query writing, table relationships, data integrity, aggregation, joins, subqueries, transactions, normalization, indexes, and database-specific features. A strong answer should explain the concept clearly, mention relevant differences between database systems, and use a valid SQL example where appropriate.

The questions below cover foundational and intermediate SQL topics for software developers, database developers, data analysts, testers, and data engineering candidates. SQL syntax and behavior can vary among Oracle Database, Microsoft SQL Server, MySQL, PostgreSQL, and other database systems, so database-specific differences are identified where they matter.

Basic SQL Interview Questions and Answers

What are data, a database, and a database management system?

Data consists of facts or observations that can be stored and processed, such as a customer name, transaction date, or product price.

A database is an organized collection of related data.

A Database Management System (DBMS) is software used to create, store, retrieve, update, secure, and administer databases. Examples of relational database management systems include Oracle Database, Microsoft SQL Server, MySQL, and PostgreSQL.

What is SQL?

SQL stands for Structured Query Language. It is a declarative language used to define database structures, query relational data, insert and modify rows, manage transactions, and control access to database objects.

What is the difference between SQL and SQL Server?

SQL is a language used to communicate with relational databases. Microsoft SQL Server is a relational database management system developed by Microsoft that implements SQL along with Microsoft-specific extensions such as Transact-SQL.

What is the difference between DBMS and RDBMS?

DBMS is a broad term for software that manages databases. An RDBMS, or Relational Database Management System, organizes data into related tables and supports relational concepts such as rows, columns, keys, constraints, and joins.

Most systems discussed in SQL interviews, including Oracle Database, SQL Server, PostgreSQL, and MySQL, are relational database management systems.

What are DDL, DML, DQL, DCL, and TCL commands in SQL?

Category	Purpose	Common commands
DDL	Defines or changes database objects	CREATE, ALTER, DROP, TRUNCATE
DML	Modifies table data	INSERT, UPDATE, DELETE
DQL	Retrieves data	SELECT
DCL	Controls privileges	GRANT, REVOKE
TCL	Controls transactions	COMMIT, ROLLBACK, SAVEPOINT

SQL Keys and Referential Integrity Interview Questions

What is a database key?

A key is one column or a combination of columns used to identify rows, enforce uniqueness, or establish relationships between tables.

• Super key: Any set of columns that uniquely identifies a row.
• Candidate key: A minimal super key with no unnecessary column.
• Primary key: The candidate key selected as the main row identifier.
• Alternate key: A candidate key that was not selected as the primary key.
• Composite key: A key formed from two or more columns.
• Foreign key: A column or group of columns that references a candidate or primary key in another table, or sometimes the same table.

What is the difference between a primary key and a unique key?

Primary key	Unique constraint
Identifies each row as the table’s primary identifier	Enforces uniqueness for another candidate key
A table has one primary key constraint	A table can have multiple unique constraints
Primary-key columns cannot contain NULL	Handling of NULL values varies by database system
May contain one or multiple columns	May contain one or multiple columns

Avoid stating that every database allows exactly one NULL in a unique column. Unique-constraint behavior for NULL values is database-specific.

What is a foreign key and what does it do?

A foreign key creates a referential relationship between tables. It prevents a child row from referencing a parent value that does not exist, subject to the constraint’s configured update and delete actions.

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INTEGER,
    CONSTRAINT fk_employee_department
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

SQL Constraints Interview Questions

What are the common SQL constraints?

• NOT NULL prevents a column from storing NULL.
• UNIQUE enforces uniqueness for a column or column combination.
• PRIMARY KEY uniquely identifies each row.
• FOREIGN KEY enforces referential integrity.
• CHECK restricts accepted values using a condition.
• DEFAULT supplies a value when an insert does not provide one.

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    branch VARCHAR(20) DEFAULT 'CSE',
    marks INTEGER CHECK (marks >= 0 AND marks <= 100),
    email VARCHAR(255) UNIQUE
);

DELETE, TRUNCATE, and DROP Interview Questions

What is the difference between DELETE, TRUNCATE, and DROP?

Command	What it removes	WHERE supported	Table structure retained
DELETE	Selected rows or all rows	Yes	Yes
TRUNCATE	All rows	No	Yes
DROP	The database object itself	No	No

Transaction behavior, identity reset behavior, logging, trigger execution, and permission requirements for these commands vary among database systems. An interview answer should avoid claiming that TRUNCATE can never be rolled back without naming the database platform and transaction context.

DELETE FROM students
WHERE student_id = 10;

TRUNCATE TABLE students;

DROP TABLE students;

SQL SELECT and Filtering Interview Questions

What is the purpose of the DISTINCT keyword?

DISTINCT removes duplicate combinations from the selected columns in a query result. It does not permanently delete duplicate rows from the table.

SELECT DISTINCT branch
FROM students;

When several columns appear after DISTINCT, uniqueness applies to the complete selected combination.

SELECT DISTINCT branch, graduation_year
FROM students;

How does the LIKE operator perform pattern matching?

The LIKE operator compares text with a pattern. In common SQL implementations, % represents zero or more characters and _ represents exactly one character.

SELECT *
FROM students
WHERE student_name LIKE 'M%';

The query returns names that begin with M. Whether matching is case-sensitive depends on the database system, data type, and collation.

What is the difference between IN and BETWEEN?

IN checks whether a value equals one of several listed values or values returned by a subquery. BETWEEN checks whether a value falls within an inclusive range.

SELECT *
FROM students
WHERE marks IN (85, 92);

SELECT *
FROM students
WHERE marks BETWEEN 85 AND 92;

Is NULL the same as zero or an empty string?

No. NULL represents missing, unknown, or inapplicable data. Zero is a numeric value, and an empty string is a text value with no characters. Use IS NULL or IS NOT NULL rather than = NULL.

SELECT *
FROM students
WHERE email IS NULL;

SQL Aggregate Functions, GROUP BY, WHERE, and HAVING

What are the common aggregate functions in SQL?

• COUNT() counts rows or non-NULL expressions.
• SUM() calculates a total.
• AVG() calculates an average.
• MIN() returns the minimum value.
• MAX() returns the maximum value.

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts result rows. COUNT(column) counts rows where the specified expression is not NULL.

SELECT
    COUNT(*) AS total_rows,
    COUNT(email) AS rows_with_email
FROM students;

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and aggregation. HAVING filters groups after GROUP BY and can evaluate aggregate expressions.

SELECT branch, AVG(marks) AS average_marks
FROM students
WHERE marks IS NOT NULL
GROUP BY branch
HAVING AVG(marks) >= 75;

Here, WHERE removes rows with missing marks before grouping, while HAVING retains only branches whose average is at least 75.

What is the difference between GROUP BY and ORDER BY?

GROUP BY combines rows into groups, usually for aggregate calculations. ORDER BY sorts the final result set and does not group rows.

SELECT branch, COUNT(*) AS student_count
FROM students
GROUP BY branch
ORDER BY student_count DESC;

SQL Join Interview Questions

What is a SQL join?

A join combines rows from two or more table references according to a join condition. Joins are normally based on related key columns, although SQL also permits other predicates.

• INNER JOIN: Returns rows that satisfy the join condition in both inputs.
• LEFT JOIN: Returns every row from the left input and matching rows from the right input. Unmatched right-side columns are returned as NULL.
• RIGHT JOIN: Returns every row from the right input and matching rows from the left input.
• FULL OUTER JOIN: Returns matched rows and unmatched rows from both inputs.
• CROSS JOIN: Returns the Cartesian product of both inputs.
• SELF JOIN: Joins a table to another reference of itself.

SELECT
    s.student_name,
    d.department_name
FROM students AS s
INNER JOIN departments AS d
    ON d.department_id = s.department_id;

What is the difference between an INNER JOIN and a LEFT JOIN?

An INNER JOIN excludes rows that do not have a match. A LEFT JOIN retains all left-table rows, even when no matching right-table row exists.

SELECT
    s.student_name,
    d.department_name
FROM students AS s
LEFT JOIN departments AS d
    ON d.department_id = s.department_id;

SQL Subquery and Duplicate-Row Interview Questions

What is a subquery?

A subquery is a query nested inside another SQL statement. It may return a scalar value, one row, one column, or a table-shaped result, depending on where and how it is used.

What is the difference between a non-correlated and correlated subquery?

A non-correlated subquery can run independently of the outer query. A correlated subquery references a column from the outer query and is logically evaluated in relation to each outer row. Database optimizers may transform the physical execution plan, so it is more accurate to describe logical dependency than to claim a fixed execution order.

SELECT s.student_id, s.student_name, s.marks
FROM students AS s
WHERE s.marks > (
    SELECT AVG(s2.marks)
    FROM students AS s2
    WHERE s2.branch = s.branch
);

How do you find duplicate values in SQL?

Group by the column or column combination that should be unique, then retain groups whose count is greater than one.

SELECT email, COUNT(*) AS duplicate_count
FROM students
GROUP BY email
HAVING COUNT(*) > 1;

SELECT student_name, date_of_birth, COUNT(*) AS duplicate_count
FROM students
GROUP BY student_name, date_of_birth
HAVING COUNT(*) > 1;

How do you find the third-highest distinct value?

A ranking window function provides a clear solution when the database supports it. Use DENSE_RANK() when equal marks should share the same rank.

WITH ranked_students AS (
    SELECT
        student_name,
        marks,
        DENSE_RANK() OVER (ORDER BY marks DESC) AS mark_rank
    FROM students
)
SELECT student_name, marks
FROM ranked_students
WHERE mark_rank = 3;

SQL Set Operations Interview Questions

What are SQL set operators?

• UNION combines compatible query results and removes duplicates.
• UNION ALL combines compatible query results and retains duplicates.
• INTERSECT returns rows present in both results.
• EXCEPT returns rows from the first result that do not occur in the second result.

Oracle Database traditionally uses MINUS for the operation commonly called EXCEPT. Support for individual set operators varies by database system. The participating queries must return compatible column counts and data types.

SQL Transactions and ACID Interview Questions

What are COMMIT and ROLLBACK?

COMMIT makes the current transaction’s changes permanent. ROLLBACK reverses changes in the current transaction that have not been committed. Exact behavior depends on transaction boundaries, autocommit settings, and the database system.

UPDATE accounts
SET balance = balance - 500
WHERE account_id = 10;

UPDATE accounts
SET balance = balance + 500
WHERE account_id = 20;

COMMIT;

What are the ACID properties of a database transaction?

• Atomicity: The transaction completes as one unit or its effects are undone.
• Consistency: A valid transaction moves the database from one valid state to another while respecting defined rules and constraints.
• Isolation: Concurrent transactions are controlled so that intermediate effects do not cause prohibited interference.
• Durability: Committed changes survive failures according to the guarantees of the database system.

SQL Normalization Interview Questions

What is database normalization?

Normalization is a database-design process that organizes attributes into related tables to reduce avoidable redundancy and prevent insertion, update, and deletion anomalies. It is based on keys, functional dependencies, and other dependency rules.

When is a table in First Normal Form?

A relation is in First Normal Form when each attribute value is atomic within the relational model. Repeating groups or multiple independently searchable values should not be stored in one field.

When is a table in Second Normal Form?

A table is in Second Normal Form when it is in First Normal Form and every non-prime attribute is fully functionally dependent on every candidate key. The rule primarily matters when a candidate key contains multiple columns.

When is a table in Third Normal Form?

A table is in Third Normal Form when it is in Second Normal Form and non-key attributes do not depend transitively on a candidate key through another non-key attribute.

When is a table in Boyce-Codd Normal Form?

A relation is in Boyce-Codd Normal Form when, for every non-trivial functional dependency X → Y, X is a super key. BCNF is stricter than Third Normal Form.

When is a table in Fourth Normal Form?

A relation is in Fourth Normal Form when it is in BCNF and every non-trivial multivalued dependency has a super key as its determinant.

SQL Index Interview Questions

Why are indexes used in SQL databases?

An index is a database structure designed to help locate rows without scanning every row of a table. Depending on the query and database engine, an index may improve filtering, joining, ordering, grouping, and uniqueness enforcement.

Indexes also have costs: they consume storage and must be maintained during inserts, updates, and deletes. An index is not automatically useful merely because a column appears in a query.

What is the difference between clustered and nonclustered indexes?

The exact meaning is database-specific. In Microsoft SQL Server, a clustered index determines how the table’s data rows are organized through the clustered index structure, while a nonclustered index is a separate structure containing index keys and row locators. A table can have only one clustered index because its rows can be organized in only one clustered order.

Do not assume every database product uses SQL Server’s clustered-index terminology or implementation.

SQL Views, Stored Procedures, Triggers, and Cursors

What is a SQL view?

A regular view is a named query whose result can be referenced similarly to a table. It usually stores the query definition rather than a separate copy of the result rows. Materialized or indexed views are different because they may physically store derived results.

Views can simplify complex queries, present a stable interface, restrict exposed columns or rows, and support logical abstraction. They do not automatically provide security unless permissions and ownership rules are configured correctly.

What is a stored procedure?

A stored procedure is a named program stored in the database and executed by the database server. Depending on the platform, it can accept parameters, run multiple SQL statements, implement control flow, manage transactions, and return result sets or output values.

What is a database trigger?

A trigger is database code that runs automatically in response to a configured event, such as an insert, update, delete, schema change, or logon event. Supported events and timing options differ by database system.

Triggers can enforce complex rules or record audits, but hidden side effects can make applications harder to understand and troubleshoot. Declarative constraints are usually clearer when they can express the same rule.

What is a cursor?

A cursor provides controlled access to rows in a query result, often one row at a time. Procedural database languages may provide implicit and explicit cursors with operations such as OPEN, FETCH, and CLOSE.

Set-based SQL is generally preferred when it can express the operation because row-by-row processing may add overhead. Cursors remain useful when each row requires procedural handling that is difficult to express as a set operation.

SQL Security and Privilege Interview Questions

What are GRANT and REVOKE?

GRANT assigns privileges or roles to a user or role. REVOKE removes previously granted privileges. Available privilege types and cascading behavior differ among database systems.

GRANT SELECT, INSERT
ON students
TO application_user;

REVOKE INSERT
ON students
FROM application_user;

What is SQL injection?

SQL injection occurs when untrusted input changes the structure or meaning of a database command. It commonly results from building SQL by concatenating user-controlled text.

The primary defense is to use parameterized queries or prepared statements. Applications should also validate input, use least-privilege database accounts, handle errors safely, and avoid exposing database details.

SELECT student_id, student_name
FROM students
WHERE email = ?;

The placeholder value is supplied separately through the application’s database driver instead of being concatenated into the SQL text.

Practical SQL Query Interview Questions

How do you use a CASE expression in SQL?

A CASE expression returns a value based on the first matching condition. It can be used in clauses such as SELECT, ORDER BY, and aggregate expressions.

SELECT
    student_name,
    marks,
    CASE
        WHEN marks >= 75 THEN 'Distinction'
        WHEN marks >= 50 THEN 'Pass'
        ELSE 'Needs improvement'
    END AS result_category
FROM students;

What is the written and logical processing order of a SELECT query?

A common written order is:

SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...;

A simplified logical processing order is commonly described as FROM, joins, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, and ORDER BY, followed by row-limiting operations. This explains why a select-list alias is often unavailable in WHERE but available in ORDER BY. Optimizers may use a different physical execution plan while preserving query semantics.

How do you create an empty table based on another table?

The syntax varies by database system. The following SQL Server example copies selected column definitions without copying rows:

SELECT *
INTO student_copy
FROM students
WHERE 1 = 0;

The following commonly supported form creates a table from a query result, but exact support and copied metadata vary:

CREATE TABLE student_copy AS
SELECT *
FROM students
WHERE 1 = 0;

These techniques may not copy indexes, constraints, defaults, triggers, identity properties, or permissions. A schema-generation tool or explicit CREATE TABLE statement is safer when an exact structural copy is required.

How do you display the current date in different SQL databases?

Database	Example
Standard-style date expression	SELECT CURRENT_DATE;
PostgreSQL	SELECT CURRENT_DATE; or SELECT CURRENT_TIMESTAMP;
MySQL	SELECT CURRENT_DATE; or SELECT NOW();
Microsoft SQL Server	SELECT GETDATE();
Oracle Database	SELECT SYSDATE FROM DUAL;

SQL Interview Preparation by Role

SQL topics for data analyst interviews

• Filtering, sorting, and handling NULL
• Joins and join-cardinality problems
• Aggregations, GROUP BY, and conditional aggregation
• Common table expressions and subqueries
• Window functions such as ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), and running totals
• Date grouping, cohort calculations, and duplicate detection

SQL topics for software developer interviews

• Keys, constraints, transactions, and isolation
• Parameterized queries and SQL injection prevention
• Indexes and query-plan fundamentals
• Normalization and schema design
• Pagination and concurrency-safe updates
• Database portability and vendor-specific syntax

SQL topics for tester interviews

• Validating row counts, duplicates, and missing values
• Comparing source and target datasets
• Testing primary-key and foreign-key constraints
• Verifying insert, update, delete, and rollback behavior
• Finding orphaned records and invalid ranges
• Checking boundary conditions and date transformations

SQL topics for data engineering interviews

• Large joins, partitioning, and data distribution
• Incremental loads, deduplication, and idempotency
• Window functions and analytical transformations
• Execution plans and performance bottlenecks
• Slowly changing dimensions and warehouse modeling
• Transaction boundaries, late-arriving data, and data-quality checks

Frequently Asked SQL Interview Questions

What basic SQL questions are commonly asked in interviews?

Common questions cover SQL versus an RDBMS product, primary and foreign keys, constraints, DELETE versus TRUNCATE versus DROP, joins, aggregate functions, WHERE versus HAVING, subqueries, normalization, indexes, transactions, and NULL handling.

What is the difference between a primary key and a unique key?

A primary key is the table’s selected row identifier and cannot contain NULL. A table has one primary key constraint, which may include several columns. A table can have multiple unique constraints. The treatment of NULL under a unique constraint depends on the database system.

What is a foreign key used for?

A foreign key enforces referential integrity by requiring child-table values to reference an existing candidate or primary-key value in the parent table, unless the foreign-key column is nullable and contains NULL.

Why is WHERE different from HAVING?

WHERE filters rows before grouping. HAVING filters grouped results and is commonly used with aggregate expressions such as COUNT() or AVG().

How should SQL query questions be answered during an interview?

State assumptions about table names, columns, duplicate handling, NULL values, ties, and database platform. Write a correct query, explain its main clauses, and mention alternative behavior when the requirement is ambiguous. For performance questions, ask about row counts, indexes, selectivity, and the execution plan instead of assuming one universal optimization.

SQL Interview Questions Editorial QA Checklist

• Verify that database-specific syntax is labelled as Oracle, SQL Server, MySQL, PostgreSQL, or standard-style SQL where applicable.
• Check that the primary-key and unique-key comparison does not make a universal claim about how unique constraints handle NULL.
• Confirm that WHERE is described as row filtering before aggregation and HAVING as group filtering after aggregation.
• Ensure that examples use straight SQL string quotes and internally consistent table and column names.
• Check that duplicate-record queries group by the same columns selected as duplicate identifiers.
• Verify that third-highest-value examples state whether ties should share a rank.
• Confirm that SQL injection guidance recommends parameterized queries rather than input escaping alone.
• Check that COUNT(*) and COUNT(column) are distinguished correctly for NULL values.
• Ensure that claims about indexes, views, triggers, procedures, and transaction rollback are not presented as identical across every database product.