Chapter•480 Minutes

Items per page

Complete Interview Questions & Answer Bank

50 curated SQL technical interview questions ranging from fundamental syntax and database design to advanced analytical window functions, query execution plans, and concurrency control.

Q1: What is the difference between SQL (Relational) and NoSQL (Non-Relational) databases?

Difficulty:EasyTopic:Database Architecture

SQL databases are relational, structured, and enforce a predefined schema. They store data in tables composed of rows and columns, leveraging ACID properties to guarantee transactional consistency.

NoSQL databases are non-relational, schema-flexible, and designed for horizontal scalability across distributed clusters. They utilize dynamic data models including Key-Value pairs, Document stores (JSON/BSON), Wide-Column stores, and Property Graphs.

SQL vs NoSQL Architectural Comparison

Data Model: Relational Tables vs Key-Value / Document / Graph

SQL uses strict normalized schema tables; NoSQL uses flexible dynamic documents.

Scaling: Vertical (Scale-Up) vs Horizontal (Scale-Out)

SQL scales via larger hardware resources; NoSQL scales by adding nodes to a cluster.

Guarantees: ACID vs BASE (Eventually Consistent)

SQL focuses on immediate consistency; NoSQL often prioritizes high availability.

Key Takeaways

SQL databases excel at complex JOIN queries and strict transactional consistency (ACID).
NoSQL databases excel at handling unstructured or rapidly changing data at massive distributed scale.

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

Difficulty:EasyTopic:Core Concepts

SQL statements are categorized into five sublanguages based on operational scope:

DDL (Data Definition Language): Defines and alters schema structures. Commands: CREATE, ALTER, DROP, TRUNCATE, RENAME.
DML (Data Manipulation Language): Modifies stored data rows. Commands: INSERT, UPDATE, DELETE, MERGE.
DQL (Data Query Language): Retrieves records from schemas. Command: SELECT.
DCL (Data Control Language): Manages permissions and administrative privileges. Commands: GRANT, REVOKE.
TCL (Transaction Control Language): Manages atomic transactional changes. Commands: COMMIT, ROLLBACK, SAVEPOINT.

Key Takeaways

DDL alters schema metadata; DML and DQL operate directly on table data rows.
TCL manages state persistence across transactions; DCL enforces security permissions.

Q3: What are the differences between Primary Key, Foreign Key, and Unique Key constraints?

Difficulty:EasyTopic:Schema Design

Keys enforce entity integrity and relationship constraints within relational schemas:

Primary Key: Uniquely identifies each record in a table. Enforces non-nullability and uniqueness. A table can have only one Primary Key (which can be composite).
Unique Key: Ensures all values in a column or column group are distinct. Allows one or more NULL values (database dependent).
Foreign Key: Maintains referential integrity by linking a column in a child table to the Primary Key or Unique Key of a parent table.

Key Takeaways

A table has exactly one Primary Key, which strictly forbids NULL values.
Foreign Keys enforce referential integrity across relational boundaries.

Q4: What is the logical execution order of an SQL query?

Difficulty:MediumTopic:Engine Internals

Although an SQL query is written starting with SELECT, the database engine processes clauses in a distinct logical order to construct result sets.

Logical SQL Execution Pipeline

Key Takeaways

WHERE evaluates before GROUP BY and aggregations, whereas HAVING evaluates after GROUP BY.
Column aliases defined in SELECT cannot be used directly in WHERE or HAVING clauses.

Q5: What is the difference between the WHERE and HAVING clauses?

Difficulty:EasyTopic:Core Filtering

The fundamental difference lies in aggregation pipeline execution timing:

WHERE: Filters individual records before group aggregations occur. It cannot evaluate aggregate functions like SUM(), AVG(), or COUNT().
HAVING: Filters aggregated groups after the GROUP BY step finishes computing group statistics.

Combining WHERE and HAVING in a Single Query

SELECT department_id, COUNT(*) AS employee_count
FROM employees
WHERE status = 'ACTIVE' -- Filters raw rows first
GROUP BY department_id
HAVING COUNT(*) > 5;   -- Filters aggregated groups

💡 WHERE filters out inactive employees before computing department counts; HAVING filters out departments with 5 or fewer active employees.

Key Takeaways

WHERE operates on individual records; HAVING operates on aggregated groups.
Filtering records as early as possible with WHERE improves performance by reducing group calculation payload size.

Q6: Explain INNER, LEFT, RIGHT, FULL, and CROSS JOINs with examples.

Difficulty:EasyTopic:Relational Operations

JOIN clauses combine columns from one or more tables using related attributes:

INNER JOIN: Returns only matching records existing in both tables.
LEFT (OUTER) JOIN: Returns all records from the left table and matching records from the right table. Unmatched right values return NULL.
RIGHT (OUTER) JOIN: Returns all records from the right table and matching left records.
FULL (OUTER) JOIN: Returns all records when a match exists in either left or right table.
CROSS JOIN: Produces the Cartesian product of both tables (N × M rows).

Key Takeaways

LEFT JOIN guarantees all rows from the primary left table are retained.
CROSS JOIN produces N * M rows and should be used with caution on large dataset sizes.

Q7: What is the difference between DELETE, TRUNCATE, and DROP commands?

Difficulty:EasyTopic:Data Management

These commands clear records or schema definitions with varying performance and transactional characteristics.

DELETE vs TRUNCATE vs DROP

DELETE: DML | Row-by-Row | Slow | Rollbackable

Fires triggers, logs individual row deletions in transaction WAL.

TRUNCATE: DDL | Page Allocation Reset | Fast

Deallocates data pages directly, bypasses row triggers.

DROP: DDL | Schema Removal | Destructive

Removes data, indexes, constraints, and table metadata from dictionary.

Key Takeaways

TRUNCATE is a high-performance DDL operation that resets identity seeds and deallocates data pages.
DELETE supports conditional WHERE filtering and logs individual row removals.

Q8: How does SQL evaluate NULL values and Three-Valued Logic?

Difficulty:MediumTopic:Core Engine

NULL in SQL represents an unknown or missing value, not zero or an empty string ('').

Because NULL is unknown, direct equality comparisons (field = NULL or NULL = NULL) evaluate to UNKNOWN, not TRUE or FALSE. SQL utilizes Three-Valued Logic (TRUE, FALSE, UNKNOWN).

To check for missing values, use IS NULL or IS NOT NULL. Functions like COALESCE(col, fallback) or NVL() replace NULL values with predefined defaults.

Handling NULL safely with COALESCE

-- Incorrect: evaluates to UNKNOWN for nulls
SELECT * FROM employees WHERE commission_pct = NULL;

-- Correct: handles null check explicitly
SELECT * FROM employees WHERE commission_pct IS NULL;

-- Replacing null values in calculations
SELECT employee_id, salary + COALESCE(commission_pct, 0) AS total_comp
FROM employees;

💡 COALESCE returns the first non-null argument, guaranteeing arithmetic operations do not resolve to NULL.

Key Takeaways

Arithmetic operations involving NULL return NULL (100 + NULL = NULL).
Always use IS NULL or COALESCE() when comparing columns that permit nullability.

Q9: How do GROUP BY and Aggregate Functions work, and how are NULLs handled?

Difficulty:EasyTopic:Aggregations

GROUP BY collapses set rows sharing common values into summary groups. Aggregate functions (SUM(), AVG(), COUNT(), MIN(), MAX()) summarize each group.

NULL Handling in Aggregates

COUNT(*) counts all rows, including rows containing NULL values.
COUNT(column_name) counts only non-null values in that column.
SUM(), AVG(), MIN(), and MAX() automatically ignore NULL values during calculation.

Key Takeaways

COUNT(*) includes NULL rows; COUNT(column) ignores NULL entries.
All non-aggregated columns listed in the SELECT clause must be included in the GROUP BY clause.

Q10: What is the difference between UNION and UNION ALL?

Difficulty:EasyTopic:Set Operations

Both operators merge result sets from multiple SELECT queries into a single output set. They require matching column counts and compatible data types across positional indices.

UNION: Combines result sets and performs an implicit duplicate elimination pass (sorting or hashing output records).
UNION ALL: Concatenates result sets directly without deduplication, preserving all records.

High-Performance Set Merging

-- UNION ALL avoids expensive sorting overhead
SELECT customer_id, city FROM domestic_customers
UNION ALL
SELECT customer_id, city FROM international_customers;

💡 Use UNION ALL when datasets are known to be mutually exclusive or when keeping duplicate rows is acceptable, avoiding costly distinct sorting operations.

Key Takeaways

UNION performs duplicate removal; UNION ALL retains all rows directly.
UNION ALL offers superior performance because it avoids expensive memory sorting or hash deduplication operations.

Q1: What is the difference between SQL (Relational) and NoSQL (Non-Relational) databases?

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

Q3: What are the differences between Primary Key, Foreign Key, and Unique Key constraints?

Q4: What is the logical execution order of an SQL query?

Q5: What is the difference between the WHERE and HAVING clauses?

Q6: Explain INNER, LEFT, RIGHT, FULL, and CROSS JOINs with examples.

Q7: What is the difference between DELETE, TRUNCATE, and DROP commands?

Q8: How does SQL evaluate NULL values and Three-Valued Logic?

Q9: How do GROUP BY and Aggregate Functions work, and how are NULLs handled?

NULL Handling in Aggregates

Q10: What is the difference between UNION and UNION ALL?

STAY UPDATED WITH BYTES!