Sarkari Result

India's Most Trusted Education Portal

Latest Jobs Results Admit Cards Answer Keys Current Affairs

💾 Databases & SQL Interview Questions 2026

SQL Query Interview Questions, Joins, Indexing, Normalization, and ACID properties.

All (20) 🟢 Easy 🟡 Medium 🔴 Hard
1
What is the difference between SQL and NoSQL databases?
Easy ▼
SQL (Relational) Databases:
  • Structured data with predefined schema
  • Tables with rows and columns
  • ACID properties
  • Supports complex queries with JOINs
  • Vertically scalable
  • Examples: MySQL, PostgreSQL, Oracle, SQL Server


NoSQL Databases:
  • Flexible/dynamic schema
  • Types: Document, Key-Value, Column, Graph
  • BASE properties (Eventually Consistent)
  • Better horizontal scalability
  • Examples: MongoDB, Redis, Cassandra, Neo4j
2
What are the different types of SQL JOINs?
Easy ▼
INNER JOIN: Returns rows where there's a match in BOTH tables.
SELECT * FROM A INNER JOIN B ON A.id = B.a_id;

LEFT JOIN: All rows from left table + matched rows from right (NULL for no match).
SELECT * FROM A LEFT JOIN B ON A.id = B.a_id;

RIGHT JOIN: All rows from right table + matched rows from left.

FULL OUTER JOIN: All rows from both tables (NULL where no match).

CROSS JOIN: Cartesian product of both tables.

SELF JOIN: Table joined with itself.
SELECT e.name, m.name AS manager
FROM employees e JOIN employees m ON e.manager_id = m.id;
3
What is database normalization? Explain 1NF, 2NF, 3NF.
Medium ▼
Normalization: Process of organizing database to reduce redundancy and improve integrity.

1NF (First Normal Form):
  • Each column has atomic (indivisible) values
  • No repeating groups or arrays
  • Each row is unique


2NF (Second Normal Form):
  • Must be in 1NF
  • No partial dependencies (non-key columns must depend on FULL primary key)
  • Applies when composite primary key exists


3NF (Third Normal Form):
  • Must be in 2NF
  • No transitive dependencies (non-key column should not depend on another non-key column)
4
What is the difference between WHERE and HAVING clause?
Easy ▼
WHERE:
  • Filters ROWS before grouping
  • Used with SELECT, UPDATE, DELETE
  • Cannot use aggregate functions

SELECT * FROM employees WHERE salary > 50000;

HAVING:
  • Filters GROUPS after GROUP BY
  • Used only with GROUP BY
  • CAN use aggregate functions

SELECT department, AVG(salary) as avg_sal
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

Both together:
SELECT dept, COUNT(*) FROM emp
WHERE status = 'active'
GROUP BY dept
HAVING COUNT(*) > 5;
5
What is an index in a database? When should you use it?
Medium ▼
Index: A data structure (B-Tree, Hash) that speeds up data retrieval by creating quick lookup paths, similar to a book's index.

Benefits: Faster SELECT queries and WHERE/ORDER BY/JOIN operations.

Drawbacks: Slows INSERT/UPDATE/DELETE. Takes extra disk space.

Create Index:
CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_unique_email ON users(email);
CREATE INDEX idx_comp ON orders(user_id, created_at);

Use index when:
  • Column frequently used in WHERE
  • Column used in JOINs
  • High-cardinality columns (many unique values)
  • Avoid on small tables or columns with few unique values
6
What are ACID properties in databases?
Medium ▼
ACID ensures reliable database transactions:

A — Atomicity: Transaction is all-or-nothing. If any step fails, entire transaction is rolled back.

C — Consistency: Database moves from one valid state to another. All rules/constraints satisfied.

I — Isolation: Concurrent transactions do not interfere with each other. Isolation levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE.

D — Durability: Once committed, changes are permanent even after system failure (written to disk).

Example: Bank transfer — debit A and credit B must both succeed or both fail.
7
What is the difference between primary key and unique key?
Easy ▼
Primary Key:
  • Uniquely identifies each row
  • Only ONE per table
  • Cannot be NULL
  • Automatically creates clustered index

CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100)
);

Unique Key:
  • Enforces uniqueness of column values
  • MULTIPLE unique keys allowed per table
  • CAN have one NULL value
  • Creates non-clustered index

CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(100) UNIQUE,
phone VARCHAR(15) UNIQUE
);
8
What is a stored procedure? What are its advantages?
Medium ▼
A stored procedure is a precompiled set of SQL statements saved in the database that can be executed with a single call.

Create and Call:
CREATE PROCEDURE GetUserOrders(IN user_id INT)
BEGIN
SELECT * FROM orders
WHERE orders.user_id = user_id;
END;

CALL GetUserOrders(42);

Advantages:
  • Precompiled — faster execution
  • Reduces network traffic (multiple statements in one call)
  • Code reusability
  • Security (users access data through procedures, not directly)
  • Easier maintenance


Disadvantages: Hard to debug, database-specific syntax, can cause performance issues if complex.
9
What is a subquery? What are correlated subqueries?
Hard ▼
Subquery: A query nested inside another SQL query.

In WHERE clause:
SELECT name FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

In FROM clause:
SELECT dept, avg_sal FROM
(SELECT dept, AVG(salary) avg_sal FROM emp GROUP BY dept) AS dept_avg
WHERE avg_sal > 50000;

Correlated Subquery: References columns from the outer query. Executes ONCE PER ROW of outer query.
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
SELECT AVG(salary) FROM employees
WHERE department = e.department -- references outer query
);
10
What is the difference between TRUNCATE, DELETE, and DROP?
Easy ▼
DELETE:
  • Removes specific rows (with WHERE) or all rows
  • Can be rolled back (logged)
  • Triggers fire
  • Slower (row by row)
  • Table structure remains


TRUNCATE:
  • Removes ALL rows from table
  • Cannot be rolled back (in most databases)
  • Triggers do NOT fire
  • Faster (deallocates data pages)
  • Table structure remains, identity resets


DROP:
  • Completely removes the table (structure + data)
  • Cannot be rolled back
  • All indexes, constraints, triggers also deleted
11
What are window functions in SQL? Give examples.
Hard ▼
Window functions perform calculations across a set of rows related to the current row WITHOUT collapsing rows (unlike GROUP BY).

Syntax: function() OVER (PARTITION BY col ORDER BY col)

Ranking functions:
SELECT name, dept, salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as rank,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rnk,
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as d_rank
FROM employees;

Aggregate window functions:
SELECT name, salary,
AVG(salary) OVER (PARTITION BY dept) as dept_avg,
SUM(salary) OVER () as company_total
FROM employees;
12
What is the difference between UNION and UNION ALL?
Easy ▼
UNION:
  • Combines results of two SELECT statements
  • Removes DUPLICATE rows
  • Slower (needs to sort and remove duplicates)

SELECT city FROM customers
UNION
SELECT city FROM suppliers;
-- Returns distinct cities

UNION ALL:
  • Combines results of two SELECT statements
  • KEEPS all duplicate rows
  • Faster (no deduplication)

SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;
-- Returns all cities including duplicates

Rule: Both SELECT statements must have same number of columns with compatible data types.
13
What are database triggers? Give an example.
Medium ▼
A trigger is a stored procedure that automatically executes in response to INSERT, UPDATE, or DELETE events on a table.

Types: BEFORE, AFTER, INSTEAD OF

Example — Audit log trigger:
CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_log(table_name, record_id, changed_at, changed_by)
VALUES('employees', NEW.id, NOW(), USER());
END;

Use cases:
  • Audit trails and logging
  • Enforcing business rules
  • Maintaining derived data
  • Cascading changes


Caution: Overuse makes debugging difficult.
14
What is query optimization? How do you optimize slow SQL queries?
Hard ▼
Steps to optimize a slow query:

1. Use EXPLAIN:
EXPLAIN SELECT * FROM orders WHERE user_id = 5;
Look for: Full Table Scan vs Index usage

2. Add proper indexes on WHERE, JOIN, ORDER BY columns

3. Avoid SELECT * — select only needed columns

4. Avoid functions on indexed columns:
-- Bad: WHERE YEAR(created_at) = 2024
-- Good: WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'

5. Use JOINs instead of subqueries where possible

6. Limit rows: Use LIMIT, pagination

7. Avoid N+1 queries in application code
15
What is the difference between a view and a table in SQL?
Medium ▼
Table:
  • Physical storage of data on disk
  • Can INSERT, UPDATE, DELETE data
  • Always contains actual data


View:
  • Virtual table — stored SQL query
  • No physical storage (just the query)
  • Data is fetched fresh each time view is queried
  • Simplifies complex queries
  • Provides security (hide sensitive columns)


Create View:
CREATE VIEW active_employees AS
SELECT id, name, department, salary
FROM employees
WHERE status = 'active';

SELECT * FROM active_employees;

Materialized View: Physically stores the result (PostgreSQL, Oracle) — needs manual refresh.
16
Explain transaction isolation levels in SQL.
Hard ▼
Isolation levels control how transaction changes are visible to other concurrent transactions.

Problems isolation levels prevent:
  • Dirty Read: Reading uncommitted data from another transaction
  • Non-repeatable Read: Same row gives different values in same transaction
  • Phantom Read: New rows appear in repeated query


Levels (least to most isolated):
  • READ UNCOMMITTED: All problems possible
  • READ COMMITTED: Prevents dirty reads (default in PostgreSQL, Oracle)
  • REPEATABLE READ: Prevents dirty + non-repeatable reads (default in MySQL)
  • SERIALIZABLE: Prevents all problems (slowest)
17
What are aggregate functions in SQL?
Easy ▼
Aggregate functions operate on multiple rows and return a single value:

SELECT
COUNT(*) as total_rows,
COUNT(salary) as non_null_salaries,
SUM(salary) as total_payroll,
AVG(salary) as avg_salary,
MAX(salary) as highest_salary,
MIN(salary) as lowest_salary,
GROUP_CONCAT(name SEPARATOR ', ') as all_names -- MySQL
FROM employees
WHERE department = 'Engineering';

With GROUP BY:
SELECT department, COUNT(*), AVG(salary)
FROM employees
GROUP BY department
ORDER BY AVG(salary) DESC;
18
What is a foreign key and what is referential integrity?
Medium ▼
Foreign Key: A column that references the primary key of another table, creating a link between tables.

CREATE TABLE orders (
id INT PRIMARY KEY,
user_id INT,
total DECIMAL(10,2),
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);

Referential Integrity: Ensures that relationships between tables remain consistent. You cannot add an order with a user_id that doesn't exist.

ON DELETE options:
  • CASCADE: Delete child records too
  • SET NULL: Set FK to NULL
  • RESTRICT: Prevent deletion if children exist
  • NO ACTION: Similar to RESTRICT
19
What is the difference between CHAR and VARCHAR in SQL?
Easy ▼
CHAR(n):
  • Fixed-length character string
  • Always uses exactly n bytes (pads with spaces if shorter)
  • Faster retrieval (fixed width)
  • Good for fixed-length data: country codes, zip codes, MD5 hashes

CHAR(5): "AB" stored as "AB " (3 spaces padded)

VARCHAR(n):
  • Variable-length character string
  • Uses only actual characters + 1-2 bytes for length
  • Slightly slower (variable width)
  • Good for variable data: names, emails, descriptions

VARCHAR(100): "Hello" stored as "Hello" (5 bytes + 1)

Rule: Use CHAR for fixed-size, VARCHAR for variable-size data.
20
What is SQL injection and how do you prevent it?
Hard ▼
SQL Injection: Attacker inserts malicious SQL code into a query via user input, potentially exposing, modifying, or deleting data.

Vulnerable code:
$query = "SELECT * FROM users WHERE username = '" . $_GET['user'] . "''";
-- Input: ' OR 1=1 --
-- Becomes: SELECT * FROM users WHERE username = '' OR 1=1 --'

Prevention Methods:
  • Prepared Statements (Best): $stmt = $pdo->prepare("SELECT * FROM users WHERE username = ?");
    $stmt->execute([$username]);
  • Input validation/sanitization
  • Stored Procedures
  • Least privilege DB accounts
  • Web Application Firewall (WAF)