Prepare for your SQL and database technical interviews with this comprehensive collection of 100 SQL interview questions and answers. Master essential concepts like database design, queries, joins, indexing, normalization, performance optimization, and stored procedures. Whether you're preparing for database developer roles or backend engineering positions, this guide covers everything from basic SQL syntax to advanced database optimization techniques. Boost your confidence with detailed explanations and practical examples for each question.Q1: What is SQL? basics
Database Fundamentals
SQL (Structured Query Language) is a standard language for querying and manipulating relational databases, supporting CRUD operations, filtering, joins, and aggregations.
Q2: What is a database? basics
Database Fundamentals
A database is an organized collection of structured data stored and accessed electronically, managed by a DBMS for efficient retrieval and manipulation.
Q3: What is a DBMS? basics
Database Fundamentals
A Database Management System (DBMS) is software that manages databases, handling data storage, retrieval, security, and concurrent access.
Q4: What are the types of databases? database-types
Database Fundamentals
Main types include relational (SQL), NoSQL (document, key-value, column-family), graph databases, and search engines.
Q5: What is a table? schema
Database Fundamentals
A table is a collection of related data organized in rows and columns, with each column representing an attribute and each row a record.
Q6: What is a schema? schema
Database Fundamentals
A schema is a logical container defining the structure of a database including tables, columns, data types, constraints, and relationships.
Q7: What is a primary key? constraints
Database Fundamentals
A primary key uniquely identifies each record in a table and must contain unique, non-null values.
Q8: What is a foreign key? constraints
Database Fundamentals
A foreign key is a column referencing the primary key of another table, establishing relationships between tables.
Q9: What is a unique constraint? constraints
Database Fundamentals
A unique constraint ensures all values in a column are unique, allowing NULL values but preventing duplicates.
Q10: What is a not null constraint? constraints
Database Fundamentals
A NOT NULL constraint ensures a column must always contain a value, never allowing NULL entries.
Q11: What is a check constraint? constraints
Database Fundamentals
A CHECK constraint validates that column values meet specific conditions before inserting or updating records.
Q12: What is a default constraint? constraints
Database Fundamentals
A DEFAULT constraint automatically assigns a default value to a column if no value is provided during insertion.
Q13: What is normalization? design
Database Fundamentals
Normalization is a database design technique organizing data to eliminate redundancy and maintain data integrity through normal forms.
Q14: What is 1NF? normalization
Database Fundamentals
First Normal Form (1NF) requires that all columns contain atomic (indivisible) values with no repeating groups.
Q15: What is 2NF? normalization
Database Fundamentals
Second Normal Form (2NF) requires 1NF compliance plus non-key attributes must be fully dependent on the entire primary key.
Q16: What is 3NF? normalization
Database Fundamentals
Third Normal Form (3NF) requires 2NF compliance plus non-key attributes must depend only on the primary key, not on other non-key attributes.
Q17: What is BCNF? normalization
Database Fundamentals
Boyce-Codd Normal Form (BCNF) is stricter than 3NF, ensuring every determinant is a candidate key.
Q18: What is a JOIN? joins
Database Fundamentals
A JOIN combines rows from two or more tables based on related columns, used to retrieve correlated data.
Q19: What is an INNER JOIN? joins
Database Fundamentals
INNER JOIN returns rows where values match in both tables, excluding unmatched rows from either table.
Q20: What is a LEFT JOIN? joins
Database Fundamentals
LEFT JOIN returns all rows from the left table and matching rows from the right table, with NULLs for unmatched right records.
Q21: What is a RIGHT JOIN? joins
Database Fundamentals
RIGHT JOIN returns all rows from the right table and matching rows from the left table, with NULLs for unmatched left records.
Q22: What is a FULL OUTER JOIN? joins
Database Fundamentals
FULL OUTER JOIN returns all rows from both tables, with NULLs where records don't match.
Q23: What is a CROSS JOIN? joins
Database Fundamentals
CROSS JOIN produces a Cartesian product, combining each row from the first table with every row from the second.
Q24: What is a SELF JOIN? joins
Database Fundamentals
A SELF JOIN joins a table to itself, useful for comparing rows within the same table.
Q25: What is a subquery? advanced
Database Fundamentals
A subquery (inner query) is a query nested inside another query, useful for filtering based on results of another query.
Q26: What is a correlated subquery? advanced
Database Fundamentals
A correlated subquery references columns from the outer query, executing once per row, used for row-by-row comparisons.
Q27: What is an aggregate function? functions
Database Fundamentals
Aggregate functions (COUNT, SUM, AVG, MIN, MAX) perform calculations on groups of rows returning a single result.
Q28: What is GROUP BY? advanced
Database Fundamentals
GROUP BY groups rows sharing the same values in specified columns, often used with aggregate functions.
Q29: What is HAVING? advanced
Database Fundamentals
HAVING filters grouped results after GROUP BY, similar to WHERE but for aggregate functions.
Q30: What is ORDER BY? basic-operations
Database Fundamentals
ORDER BY sorts query results by one or more columns in ascending (ASC) or descending (DESC) order.
Q31: What is DISTINCT? basic-operations
Database Fundamentals
DISTINCT removes duplicate rows from query results, returning only unique values.
Q32: What is LIMIT? basic-operations
Database Fundamentals
LIMIT restricts the number of rows returned in a query result set.
Q33: What is OFFSET? basic-operations
Database Fundamentals
OFFSET skips a specified number of rows before returning results, commonly used with LIMIT for pagination.
Q34: What is a view? objects
Database Fundamentals
A view is a virtual table created from a query, providing a simplified interface to underlying tables.
Q35: What is a stored procedure? plsql
Database Fundamentals
A stored procedure is a precompiled collection of SQL statements stored in the database, executed as a single unit.
Q36: What is a trigger? plsql
Database Fundamentals
A trigger is a special program automatically executing in response to specified events on a particular table.
Q37: What is a function? plsql
Database Fundamentals
A function is a reusable code block accepting parameters and returning a single computed value.
Q38: What is a cursor? plsql
Database Fundamentals
A cursor is a database object allowing row-by-row processing of query results in PL/SQL.
Q39: What is transaction? transactions
Database Fundamentals
A transaction is a logical unit of database work consisting of one or more SQL statements.
Q40: What is ACID? transactions
Database Fundamentals
ACID (Atomicity, Consistency, Isolation, Durability) are properties ensuring reliable database transactions.
Q41: What is atomicity? transactions
Database Fundamentals
Atomicity ensures transactions are all-or-nothing: either all changes commit or all rollback.
Q42: What is consistency? transactions
Database Fundamentals
Consistency ensures database transitions from one valid state to another, maintaining data integrity.
Q43: What is isolation? transactions
Database Fundamentals
Isolation ensures concurrent transactions don't interfere, preventing dirty reads and phantom reads.
Q44: What is durability? transactions
Database Fundamentals
Durability ensures committed data persists even after failures like power outages.
Q45: What is a commit? transactions
Database Fundamentals
COMMIT permanently saves changes to the database, making them visible to other users.
Q46: What is a rollback? transactions
Database Fundamentals
ROLLBACK undoes uncommitted changes, reverting the database to its previous state.
Q47: What is locking? concurrency
Database Fundamentals
Locking prevents concurrent users from conflicting access, controlling read/write access to data.
Q48: What is deadlock? concurrency
Database Fundamentals
A deadlock occurs when two transactions wait indefinitely for each other, requiring manual intervention.
Q49: What is indexing? optimization
Database Fundamentals
Indexing creates data structures speeding up data retrieval operations at the cost of slower writes.
Q50: What is a primary index? indexing
Database Fundamentals
A primary index is automatically created on primary key columns for fast lookup and sorting.
Q51: What is a unique index? indexing
Database Fundamentals
A unique index ensures all values in indexed columns are unique, preventing duplicates.
Q52: What is a composite index? indexing
Database Fundamentals
A composite index spans multiple columns, optimizing queries filtering on several columns together.
Q53: What is a full-text index? indexing
Database Fundamentals
A full-text index optimizes text searching across large text columns.
Q54: What is query optimization? optimization
Database Fundamentals
Query optimization improves SQL performance through execution plans, indexing, and algorithmic improvements.
Q55: What is an execution plan? optimization
Database Fundamentals
An execution plan shows how a database executes a query, revealing performance bottlenecks.
Q56: What is explain plan? optimization
Database Fundamentals
EXPLAIN PLAN displays the execution strategy for a query without executing it.
Q57: What is a window function? advanced
Database Fundamentals
Window functions perform calculations across rows related to the current row without collapsing groups.
Q58: What is CTE? advanced
Database Fundamentals
Common Table Expression (CTE) creates temporary named result sets with WITH clause, improving query readability.
Q59: What is a recursive CTE? advanced
Database Fundamentals
A recursive CTE references itself to process hierarchical or tree-like data.
Q60: What is a case statement? functions
Database Fundamentals
CASE statement provides conditional logic in SQL, returning different values based on conditions.
Q61: What is NULL? data-types
Database Fundamentals
NULL represents missing or unknown data, different from zero or empty string.
Q62: What is string function? functions
Database Fundamentals
String functions manipulate text data: CONCAT, SUBSTRING, LENGTH, UPPER, LOWER, TRIM, etc.
Q63: What is date function? functions
Database Fundamentals
Date functions manipulate dates: GETDATE, DATEADD, DATEDIFF, EXTRACT, DATE_FORMAT.
Q64: What is math function? functions
Database Fundamentals
Math functions perform calculations: ROUND, FLOOR, CEILING, ABS, SQRT, POWER.
Q65: What is UNION? advanced
Database Fundamentals
UNION combines results from multiple queries, removing duplicates.
Q66: What is UNION ALL? advanced
Database Fundamentals
UNION ALL combines results from multiple queries, keeping duplicates.
Q67: What is INTERSECT? advanced
Database Fundamentals
INTERSECT returns rows common to both query results.
Q68: What is EXCEPT? advanced
Database Fundamentals
EXCEPT returns rows from the first query that don't appear in the second query.
Q69: What is NoSQL? nosql
Database Fundamentals
NoSQL encompasses non-relational databases (document, key-value, graph, column-family) offering flexibility and scalability.
Q70: What is MongoDB? nosql
Database Fundamentals
MongoDB is a document-oriented NoSQL database storing data in JSON-like BSON format.
Q71: What is a collection? nosql
Database Fundamentals
A collection in MongoDB is a grouping of documents, analogous to tables in relational databases.
Q72: What is a document? nosql
Database Fundamentals
A document is a data structure in MongoDB containing field-value pairs, similar to JSON objects.
Q73: What is Redis? nosql
Database Fundamentals
Redis is an in-memory key-value store used for caching, sessions, and real-time applications.
Q74: What is Cassandra? nosql
Database Fundamentals
Cassandra is a distributed column-family NoSQL database for high-scalability and availability.
Q75: What is Elasticsearch? nosql
Database Fundamentals
Elasticsearch is a search and analytics engine for full-text search, logging, and analytics.
Q76: What is sharding? scaling
Database Fundamentals
Sharding horizontally partitions data across multiple servers, improving scalability and performance.
Q77: What is replication? scaling
Database Fundamentals
Replication copies data across multiple servers for redundancy, availability, and read scalability.
Q78: What is backup? maintenance
Database Fundamentals
Backup creates copies of database data for recovery from failures or disasters.
Q79: What is recovery? maintenance
Database Fundamentals
Recovery restores database to a consistent state after failures using backups and logs.
Q80: What is SQL injection? security
Database Fundamentals
SQL injection is a security attack inserting malicious SQL code through user input, prevented with parameterized queries.
Q81: What is parameterized query? security
Database Fundamentals
A parameterized query uses placeholders for user input, preventing SQL injection attacks.
Q82: What is encryption? security
Database Fundamentals
Encryption converts data into unreadable format, protecting sensitive database information.
Q83: What is authentication? security
Database Fundamentals
Authentication verifies user identity through credentials before granting database access.
Q84: What is authorization? security
Database Fundamentals
Authorization controls which database resources authenticated users can access.
Q85: What is a data warehouse? advanced-concepts
Database Fundamentals
A data warehouse consolidates data from multiple sources for analytics and reporting.
Q86: What is ETL? advanced-concepts
Database Fundamentals
ETL (Extract, Transform, Load) processes data from sources, transforms it, and loads into target systems.
Q87: What is OLAP? advanced-concepts
Database Fundamentals
OLAP (Online Analytical Processing) analyzes multidimensional data for reporting and decision-making.
Q88: What is OLTP? advanced-concepts
Database Fundamentals
OLTP (Online Transaction Processing) manages day-to-day transactional operations with emphasis on consistency.
Q89: What is a data mart? advanced-concepts
Database Fundamentals
A data mart is a subset of a data warehouse focused on specific business areas or users.
Q90: What is a dimension table? advanced-concepts
Database Fundamentals
A dimension table contains descriptive attributes (dimensions) in a data warehouse.
Q91: What is a fact table? advanced-concepts
Database Fundamentals
A fact table contains measurable facts (metrics) with foreign keys to dimension tables.
Q92: What is the difference between SQL and NoSQL? comparison
Database Fundamentals
SQL is structured, ACID-compliant, schema-enforced; NoSQL is flexible, eventually-consistent, schema-less.
Q93: What is data redundancy? design
Database Fundamentals
Data redundancy occurs when the same data exists in multiple locations, causing inconsistency issues.
Q94: What is data consistency? design
Database Fundamentals
Data consistency ensures all copies of data across the database remain synchronized and valid.
Q95: What is referential integrity? constraints
Database Fundamentals
Referential integrity ensures foreign key values reference existing primary key values in related tables.
Q96: What is domain integrity? constraints
Database Fundamentals
Domain integrity ensures data conforms to defined format, type, and range constraints.