If you’re preparing for your next tech interview, mastering the basic SQL interview questions is crucial.

SQL, or Structured Query Language, is a powerful and standardized programming language designed for managing and manipulating relational databases. It serves as a means to interact with databases, enabling users to create, retrieve, update, and delete data. SQL is widely used in various industries for tasks ranging from simple data queries to complex database management and administration.

Databases, organized in a tabular format, store information that can be easily accessed and managed using SQL. SQL provides a set of commands that allow users to interact with the database, making it a fundamental skill for anyone working with data or involved in software development.

These questions cover some fundamental aspects of SQL and are commonly asked in interviews to assess a candidate’s understanding of database management and SQL syntax.

Basic SQL interview questions and answers

What is SQL?

SQL (Structured Query Language) is a standard language used to access and manipulate relational databases. It allows users to perform tasks like retrieving, inserting, updating, and deleting data.

What are the different types of SQL commands?

• SQL commands are categorized as:
• DDL (Data Definition Language): CREATE, ALTER, DROP
• DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

What is a primary key in SQL?

A primary key uniquely identifies each row in a table. It must contain unique values and cannot have NULLs. Each table can have only one primary key.

What is a foreign key?

A foreign key is a field in one table that refers to the primary key in another. It establishes a link between the two tables and maintains referential integrity.

What is the difference between WHERE and HAVING clause?

• WHERE filters rows before aggregation.
• HAVING filters groups after aggregation.

SELECT department, COUNT(*) 
FROM employees 
GROUP BY department 
HAVING COUNT(*) > 5;

What are joins in SQL? Name the types.

Joins combine rows from two or more tables. Types include:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• CROSS JOIN
• SELF JOIN

What is an INNER JOIN?

It returns only the matching rows from both tables.

SELECT e.name, d.dept_name 
FROM employees e 
INNER JOIN departments d 
ON e.dept_id = d.id;

What is normalization?

• Normalization is the process of organizing data to minimize redundancy and dependency. Common normal forms include 1NF, 2NF, 3NF, and BCNF.

Goals of Normalization:

• Eliminate redundant (duplicate) data.

• Ensure data dependencies make sense.

• Simplify data maintenance and updates.

What is denormalization?

Denormalization is the process of introducing redundancy into a database by combining tables or adding duplicated data, which was originally removed during normalization. It is typically done to improve read performance and speed up complex queries, especially in relational databases used for analytics or reporting.

What is a constraint?

Constraints enforce rules on data columns. Types include:

• NOT NULL
• UNIQUE
• CHECK
• DEFAULT
• PRIMARY KEY
• FOREIGN KEY

Difference between DELETE, TRUNCATE, and DROP?

What is a View in SQL?

A View in SQL is a virtual table based on the result of a predefined SQL query. It does not store data physically, but instead presents data from one or more underlying tables in a customized format. Think of a view as a saved SQL SELECT statement that you can treat like a table.

How to fetch the second highest salary?

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

What is an Index in SQL?

An Index in SQL is a database object that helps speed up the retrieval of rows from a table. Think of it like an index in a book – instead of reading the whole book to find a topic, you go straight to the indexed page.

SQL Syntax to Create an Index:

CREATE INDEX idx_customer_name
ON Customers (CustomerName);

CREATE CLUSTERED INDEX idx_customer_id
ON Customers (CustomerID);

What is a subquery?

A subquery (also known as a nested query or inner query) is a SQL query embedded inside another query. It is used to perform operations that depend on the results of another query.

Types of Subqueries:

• Single-row subquery – returns one row

• Multi-row subquery – returns multiple rows

• Correlated subquery – uses values from the outer query

• Nested subquery – subqueries within subqueries

Difference between CHAR and VARCHAR?

The difference between CHAR and VARCHAR in SQL lies mainly in how they store data and handle storage space

How to find duplicates?

To find duplicates in SQL, you typically use the GROUP BY clause along with the HAVING clause to identify rows that occur more than once based on specific column(s).

SELECT column_name, COUNT(*) 
FROM table_name 
GROUP BY column_name 
HAVING COUNT(*) > 1;

What is a Trigger?

A Trigger in SQL (Structured Query Language) is a special kind of stored procedure that automatically executes (or “fires”) in response to certain events on a particular table or view.

🔹 Common Events that Trigger a Trigger:

• INSERT — when a new row is added.

• UPDATE — when a row is modified.

• DELETE — when a row is removed.

What are aggregate functions?

Aggregate functions are special functions in SQL (Structured Query Language) used to perform a calculation on a set of values and return a single summarizing result. They are commonly used with the GROUP BY clause to group rows that have the same values in specified columns.

Common Aggregate Functions in SQL:

What is the default order of sorting in SQL?

In SQL, the default order of sorting when using the ORDER BY clause is:

Ascending (ASC) order.

This means:

• Numbers are sorted from smallest to largest (e.g., 1, 2, 3…)

• Text values (strings) are sorted in alphabetical order (A → Z)

• Dates are sorted from earliest to latest

If you want descending order:

Use DESC explicitly

What is GROUP BY?

The GROUP BY clause in SQL is used to group rows that have the same values in specified columns into summary rows, like “total sales per customer” or “number of students per department.” It is typically used with aggregate functions such as:

• COUNT() – to count items

• SUM() – to add values

• AVG() – to find the average

• MAX() – to find the maximum

• MIN() – to find the minimum

Difference between IS NULL and = NULL?

The difference between IS NULL and = NULL in SQL is very important, and misunderstanding it can lead to incorrect query results.

IS NULL

• Correct way to check if a value is NULL.

• Used to test whether a column or expression contains a NULL value.

=NULL

• Incorrect and invalid usage.

• In SQL, NULL means “unknown”, and comparisons with NULL using = will always return false or unknown, not the desired result.

What is a Stored Procedure?

A Stored Procedure is a precompiled collection of one or more SQL statements that are stored in the database and can be executed as a single unit. It allows you to encapsulate repetitive or complex SQL operations, making your code more reusable, organized, and efficient.

Key Features of Stored Procedures:

• Reusable: Once created, it can be called multiple times.

• Precompiled: Executed faster as it is compiled and stored in advance.

• Parameter Support: Can accept input, output, or both types of parameters.

• Improves Security: Reduces SQL injection by avoiding dynamic queries.

• Encapsulation: Hides the implementation details and promotes modular design.

What are wildcards?

Wildcards are special symbols used in search operations or pattern matching to represent one or more characters. They allow users to perform flexible and dynamic searches, especially when the exact term is not known or when searching for variations of a term.

Common Wildcards:

1. Asterisk (*)

• Represents: Zero or more characters.

• Example:

  • doc* matches doc, document, doctor, etc.

2. Question Mark (?)

• Represents: A single character.

• Example:

  • te?t matches text, test, tent, but not teest.

3. Character Sets (used in RegEx)

• [abc] matches any one of the characters a, b, or c.

• [a-z] matches any lowercase letter from a to z.

4. Negation (used in RegEx)

• [^abc] matches any character except a, b, or c.

What is a transaction?

A transaction in the context of databases (especially relational databases like MySQL, SQL Server, Oracle, etc.) is a sequence of one or more SQL operations executed as a single logical unit of work.

A transaction ensures that either all operations within it are executed successfully (committed) or none are applied (rolled back), maintaining data integrity and consistency.

What are ACID properties?

Atomicity

• Definition: A transaction must be treated as a single, indivisible unit. Either all operations within the transaction are executed successfully, or none are.

• Example: If a bank transfer involves debiting one account and crediting another, both operations must succeed; if one fails, both must be rolled back.

Consistency

• Definition: A transaction must take the database from one valid state to another, maintaining the integrity of the database.

• Example: If there’s a rule that an account balance can’t go below zero, a transaction that violates this rule will not be committed.

Isolation

• Definition: Transactions must execute independently of one another. Intermediate states of a transaction should not be visible to others.

• Example: If two users transfer money at the same time, each transaction should execute as if it’s the only one happening.

Durability

• Definition: Once a transaction is committed, its changes are permanent, even in the case of system crashes.

• Example: After transferring money and getting a success message, even a power outage won’t undo the transfer.

What is the IN clause?

SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, value3, ...);

What is the difference between UNION and UNION ALL?

The difference between UNION and UNION ALL in SQL lies in how they handle duplicate rows when combining the results of two or more SELECT queries:

Union

• Removes duplicate rows from the result set.

• Performs an implicit DISTINCT, so only unique rows are returned.

• Slightly slower due to the extra step of removing duplicates.

Union All

• Keeps all rows, including duplicates.

• Does not remove repeated values.

• Faster because it skips the duplicate-checking step.

What is a temporary table?

A temporary table is a special type of table in a database that is created and used temporarily during a session or transaction. It stores intermediate results or temporary data that doesn’t need to be kept permanently in the database.

Key Features of Temporary Tables:

1. Short-lived: Exists only for the duration of a session or until it’s manually dropped.

2. Session-specific: Usually only accessible to the session that created it.

3. Stored in TempDB (in SQL Server): The data is physically stored in a special system database.

4. Use Cases:

   • Storing intermediate results during complex queries

   • Simplifying joins and data processing

   • Improving performance for batch processing

Types of Temporary Tables in SQL:

• Local Temporary Table
• Global Temporary Table

Difference between EXISTS and IN?

The difference between EXISTS and IN in SQL lies in how they work internally and their performance characteristics. Both are used to filter data based on subqueries, but they behave differently.

• Use IN for comparing values directly from a list or a small subquery.

• Use EXISTS when you’re testing for row existence and especially when dealing with correlated subqueries or large tables.

How to use CASE?

In SQL, the CASE statement is used to apply conditional logic within queries — similar to if-else logic in programming.

Basic Syntax of CASE (Simple CASE)

SELECT column1,
       CASE column2
           WHEN 'value1' THEN 'Result1'
           WHEN 'value2' THEN 'Result2'
           ELSE 'DefaultResult'
       END AS alias_name
FROM table_name;

What is a correlated subquery?

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.

Key Characteristics:

• It references columns from the outer query.

• It is executed repeatedly, once for each row in the outer query.

• Cannot be executed independently of the outer query.

What is an alias?

In SQL, an alias is a temporary name that you assign to a table or a column to make your queries easier to read or more concise.

• Aliases are temporary and only exist for the duration of the query.

• The AS keyword is optional; you can write SELECT first_name Name instead of AS Name, but using AS improves readability.

• Useful in complex queries for better understanding and maintenance.

Table Alias

• Used to give a temporary name to a table, often used in JOINs to simplify references.

What is DISTINCT?

What is a schema?

A schema is like a map or layout for a database. It tells the database what kind of data can be stored, how the data is structured, and how different pieces of data relate to each other.

Key Components of a Schema:

A database schema typically includes:

• Tables (e.g., Users, Orders)

• Columns (e.g., name, email, order_date)

• Data Types (e.g., VARCHAR, INT, DATE)

• Keys:

  • Primary Key (uniquely identifies a row)

  • Foreign Key (links to another table)

• Constraints (e.g., NOT NULL, UNIQUE)

• Relationships (e.g., one-to-many, many-to-many)

Types of Schemas:

1. Physical Schema: Defines how data is stored physically on disk.

2. Logical Schema: Describes tables, columns, data types, and relationships—more relevant to developers and designers.

What is a recursive CTE?

A recursive CTE (Common Table Expression) is a special type of CTE in SQL that references itself in order to perform repetitive operations, typically used to query hierarchical or tree-structured data, such as organizational charts, file systems, or bill-of-materials structures.

A recursive CTE has two parts:

1. Anchor member: The base result set. This is the starting point of the recursion.

2. Recursive member: A query that refers to the CTE itself and builds on the anchor member.

What is a composite key?

A composite key is a combination of two or more columns in a database table that together uniquely identify a record (row) in that table.

Key Points:

• Used when no single column is sufficient to uniquely identify a record.

• All parts of the composite key are required to enforce uniqueness.

• Often used in junction tables (many-to-many relationships).

What is the use of LIMIT?

The LIMIT clause is used in SQL (Structured Query Language) to restrict the number of rows returned by a query. It’s especially useful when:

• You want to preview just a few rows from a large dataset.

• You’re implementing pagination (e.g., showing 10 results per page).

• You only need the top N records (like highest scores, recent orders, etc.).

What are SET operators?

• Union

• Union All

• Intersect

• Except

What is COALESCE()?

The COALESCE() function in SQL is used to return the first non-null value from a list of expressions.

How It Works:

• COALESCE() checks each expression in the list from left to right.

• It returns the first value that is not NULL.

• If all expressions are NULL, it returns NULL.

How do you prevent SQL injection?

Use Prepared Statements (Parameterized Queries)

• This is the most effective and recommended way to prevent SQL injection.
• Prepared statements separate SQL logic from data, so even if user input contains SQL code, it will be treated as plain text.

Use Stored Procedures (with parameters)

Stored procedures can also prevent injection when implemented correctly.

Avoid Dynamic SQL

Don’t build SQL queries by concatenating strings or user input directly.

Input Validation and Escaping

• Allow only expected characters using whitelisting.

• Use built-in validation libraries to ensure correct formats (e.g., emails, phone numbers).

• Escaping input is a last resort and not foolproof—prefer parameterized queries.

What is NULLIF()?

The NULLIF() function in SQL is used to compare two expressions and return NULL if they are equal. Otherwise, it returns the first expression.

Syntax:

NULLIF(expression1, expression2)

NULLIF() is often used in data transformation, cleaning, and error handling scenarios, especially where conditional nulls are useful.

What are common SQL data types?

• Numeric Data Types
• Character/String Data Types
• Date and Time Data Types
• Boolean Data Type

How do you update multiple rows?

UPDATE table_name
SET column1 = value1,
    column2 = value2,
    ...
WHERE condition;

What is the difference between RANK() and ROW_NUMBER()?

ROW_NUMBER()

• Assigns a unique sequential number to each row.

• No gaps in numbering.

• Even if rows have the same value, they get different row numbers.

RANK()

• Assigns the same rank to rows with equal values.

• Skips the next rank(s) after a tie, which causes gaps in ranking.

What is the difference between LEFT JOIN and RIGHT JOIN?

LEFT JOIN (or LEFT OUTER JOIN)

• Returns all rows from the left table, and the matching rows from the right table.

• If there is no match in the right table, NULLs are returned for columns from the right table.

RIGHT JOIN (or RIGHT OUTER JOIN)

• Returns all rows from the right table, and the matching rows from the left table.

• If there is no match in the left table, NULLs are returned for columns from the left table.

How do you back up a database in SQL Server?

Backing up a database in SQL Server is a critical task to ensure data safety. You can back up a database using SQL Server Management Studio (SSMS) or T-SQL commands.

BACKUP DATABASE dbname TO DISK = 'C:\backup.bak'

What is an identity column?

An identity column is a special type of column in a database table that automatically generates a unique numeric value for each new row inserted into the table. It’s often used to create unique primary keys.

Key Features of an Identity Column:

• Auto-incremented values: You don’t need to insert a value manually; the database automatically increments it.

• Starts from a seed value (e.g., 1), and increases by an increment (e.g., 1).

• Used to uniquely identify rows (commonly as the primary key).

• Only one identity column per table is allowed.

What are advantages of SQL?

• Easy to learn

• Powerful querying capabilities

• Widely used and supported

• High performance for large datasets

Additional Bonus Basic SQL interview questions and answers

What is the BETWEEN operator in SQL?

The BETWEEN operator in SQL is used to filter the result set within a certain range of values. It checks if a given value falls between two values (inclusive).

Syntax:

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

What is DDL?

DDL stands for Data Definition Language. It is a subset of SQL (Structured Query Language) used to define and manage the structure of database objects such as tables, schemas, indexes, and views.

Key Features of DDL

• Defines database schema and structure

• Automatically commits the changes (cannot be rolled back)

• Affects how data is stored, not the data itself

What is DML?

DML (Data Manipulation Language) is a subset of SQL (Structured Query Language) used to manipulate data stored in relational databases. DML commands are used to insert, update, delete, and retrieve data from tables.

Common DML Commands in SQL: