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