Question 1

What is the difference between SQL and NoSQL databases?

Accepted Answer

SQL databases are relational, using structured tables with predefined schemas, ACID transactions, and SQL query language (MySQL, PostgreSQL). NoSQL databases are non-relational, offering flexible schemas for unstructured data—types include document (MongoDB), key-value (Redis), column-family (Cassandra), and graph (Neo4j). SQL excels at complex queries and data integrity; NoSQL at scalability and flexibility.

Question 2

What is a primary key and why is it important?

Accepted Answer

A primary key is a column (or combination) that uniquely identifies each row in a table. It enforces entity integrity—values must be unique and cannot be NULL. Primary keys enable efficient row access, serve as reference points for foreign keys establishing relationships, and typically have clustered indexes for fast lookups. Every table should have a primary key.

Question 3

What are the different types of JOINs in SQL?

Accepted Answer

INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table plus matches from right (NULL if no match). RIGHT JOIN returns all from right plus matches from left. FULL OUTER JOIN returns all rows from both tables. CROSS JOIN produces Cartesian product of all combinations. Self-join joins a table to itself.

Question 4

What is the difference between WHERE and HAVING clauses?

Accepted Answer

WHERE filters rows before grouping—it works on individual records and cannot use aggregate functions. HAVING filters groups after GROUP BY—it works on aggregated results and can use aggregate functions. Example: WHERE salary > 50000 filters employees; HAVING COUNT(*) > 5 filters departments with more than 5 employees. Use WHERE when possible (more efficient).

Question 5

What are aggregate functions in SQL?

Accepted Answer

Aggregate functions perform calculations on multiple rows and return a single value. Common functions: COUNT() counts rows, SUM() adds values, AVG() calculates average, MAX()/MIN() find highest/lowest values. Used with GROUP BY to aggregate per group. COUNT(*) counts all rows; COUNT(column) counts non-NULL values. They ignore NULL values except COUNT(*).

Question 6

What is database normalization?

Accepted Answer

Normalization organizes data to reduce redundancy and improve integrity through normal forms. 1NF: atomic values, no repeating groups. 2NF: 1NF + no partial dependencies on composite keys. 3NF: 2NF + no transitive dependencies (non-key columns depend only on the key). Benefits: less redundancy, easier updates, better integrity. Trade-off: more joins needed for queries.

Question 7

What is a foreign key and referential integrity?

Accepted Answer

A foreign key is a column referencing another table's primary key, establishing relationships between tables. Referential integrity ensures foreign key values must exist in the referenced table—you can't insert invalid references or delete referenced rows. CASCADE options automatically propagate changes: ON DELETE CASCADE removes child rows; ON UPDATE CASCADE updates foreign key values.

Question 8

What is the difference between clustered and non-clustered indexes?

Accepted Answer

A clustered index determines the physical order of data in the table—only one per table, typically on the primary key. Data rows are stored in index order. Non-clustered indexes are separate structures with pointers to data rows—multiple allowed per table. Clustered is faster for range queries; non-clustered for specific lookups on non-primary columns.

Question 9

What is a subquery and what types exist?

Accepted Answer

A subquery is a query nested inside another query. Types: scalar (returns single value), row (returns one row), table (returns multiple rows/columns). Non-correlated subqueries execute once independently. Correlated subqueries reference outer query and execute per outer row (slower). Subqueries appear in SELECT, FROM, WHERE, or HAVING clauses. CTEs often provide better readability.

Question 10

What are window functions?

Accepted Answer

Window functions perform calculations across related rows without collapsing them into groups (unlike aggregates). They use OVER() clause with optional PARTITION BY and ORDER BY. Common functions: ROW_NUMBER() (sequential), RANK()/DENSE_RANK() (handle ties differently), LAG()/LEAD() (access previous/next rows), running totals with SUM() OVER(). Essential for analytics and reporting.

Question 11

What is the difference between DELETE, TRUNCATE, and DROP?

Accepted Answer

DELETE removes specific rows (can use WHERE), logs each deletion, fires triggers, can be rolled back, slower for large tables. TRUNCATE removes all rows quickly, minimal logging, resets identity, cannot use WHERE, faster but less flexible. DROP removes the entire table structure and data permanently. Use DELETE for selective removal, TRUNCATE to empty tables, DROP to remove tables entirely.

Question 12

What are ACID properties in database transactions?

Accepted Answer

ACID ensures reliable transactions. Atomicity: transactions complete entirely or not at all (all-or-nothing). Consistency: transactions move database from one valid state to another, enforcing constraints. Isolation: concurrent transactions don't interfere with each other. Durability: committed transactions persist even after system failures. ACID is fundamental to relational database reliability.

Question 13

What are transaction isolation levels?

Accepted Answer

Isolation levels control visibility of uncommitted changes between concurrent transactions. READ UNCOMMITTED: sees uncommitted changes (dirty reads). READ COMMITTED: sees only committed data (default in many databases). REPEATABLE READ: consistent reads within transaction. SERIALIZABLE: highest isolation, transactions appear sequential. Higher isolation = more consistency but less concurrency and performance.

Question 14

What is SQL injection and how do you prevent it?

Accepted Answer

SQL injection is an attack where malicious SQL is inserted through user input, potentially exposing or destroying data. Prevention: use parameterized queries/prepared statements (never concatenate user input), validate and sanitize inputs, use ORMs, apply least privilege database permissions, escape special characters. Example: Use 'SELECT * FROM users WHERE id = ?' with bound parameter, not string concatenation.

Question 15

How do you optimize SQL query performance?

Accepted Answer

Key optimizations: create appropriate indexes on frequently queried columns, analyze execution plans (EXPLAIN), avoid SELECT * (specify needed columns), use JOINs instead of subqueries when possible, avoid functions on indexed columns in WHERE, use LIMIT for large results, ensure up-to-date statistics, consider query caching and connection pooling. Profile before optimizing—measure actual bottlenecks.

SQL Interview Questions - Fundamentals(7 questions)

What is SQL and what does it stand for?

What is the difference between SQL and NoSQL databases?

What are the main types of SQL commands (DDL, DML, DCL, TCL)?

Explain the difference between a database, schema, table, and record.

What is a primary key and why is it important?

What is the difference between CHAR and VARCHAR data types?

What are NULL values and how do they behave in SQL operations?

Basic Queries(6 questions)

Write a query to select all columns from a table named "employees".

How do you select specific columns from a table?

What is the purpose of the WHERE clause?

How do you sort results using ORDER BY?

What is the difference between WHERE and HAVING clauses?

How do you limit the number of results returned by a query?

Data Filtering and Manipulation(5 questions)

What are the different comparison operators available in SQL?

How do you use the LIKE operator for pattern matching?

What is the difference between IN and EXISTS operators?

How do you handle case-insensitive searches in SQL?

What are wildcards and how are they used?

Joins and Relationships(7 questions)

What are the different types of JOINs in SQL?

Explain the difference between INNER JOIN and LEFT JOIN.

When would you use a RIGHT JOIN vs LEFT JOIN?

What is a FULL OUTER JOIN and when is it useful?

What is a CROSS JOIN and what are its use cases?

How do you join a table to itself (self-join)?

What is the difference between JOIN and UNION?

Aggregate Functions and Grouping(6 questions)

What are aggregate functions? Name the most common ones.

How does the GROUP BY clause work?

What is the purpose of the HAVING clause?

How do you count distinct values in a column?

What is the difference between COUNT(*) and COUNT(column_name)?

How do you find the maximum, minimum, and average values in a dataset?

Schema Design Interview Questions - Professional Answers(6 questions)

What is database normalization and why is it important?

Explain the first, second, and third normal forms.

What is denormalization and when might you use it?

What is a foreign key and how does it enforce referential integrity?

What are composite keys and when are they used?

What is the difference between a clustered and non-clustered index?

Constraints and Data Integrity(5 questions)

What are database constraints and why are they important?

Explain the different types of constraints (NOT NULL, UNIQUE, CHECK, etc.).

What is referential integrity?

How do you handle cascading updates and deletes?

What are triggers and when should they be used?

Subqueries and CTEs(5 questions)

What is a subquery and what are the different types?

What is the difference between correlated and non-correlated subqueries?

What are Common Table Expressions (CTEs) and how do they differ from subqueries?

When would you use a recursive CTE?

How do you optimize queries with multiple subqueries?

Window Functions(5 questions)

What are window functions and how do they differ from aggregate functions?

Explain the difference between ROW_NUMBER(), RANK(), and DENSE_RANK().

What is the purpose of the PARTITION BY clause in window functions?

How do you use LAG() and LEAD() functions?

What are running totals and how do you calculate them?

Advanced Data Manipulation(6 questions)

What is the difference between UNION and UNION ALL?

How do you perform conditional logic in SQL queries (CASE statements)?

What are pivot and unpivot operations?

How do you handle duplicate records in a dataset?

What is the difference between DELETE, DROP, and TRUNCATE?

References

Query Performance(7 questions)

What is a database index and how does it improve query performance?

What are the trade-offs of using indexes?

How do you identify slow-performing queries?

What is query execution plan and how do you analyze it?

What are some common causes of poor query performance?

How do you optimize JOIN operations?

What is the impact of using functions in WHERE clauses?

Database Optimization(5 questions)

What is database partitioning and when is it useful?

What are materialized views and when should they be used?

How do you handle large dataset queries efficiently?

What is the difference between `CHAR` and `VARCHAR` data types?

What are `NULL` values and how do they behave in SQL operations?

What is the purpose of the `WHERE` clause?

How do you sort results using `ORDER BY`?

What is the difference between `WHERE` and `HAVING` clauses?

How do you use the `LIKE` operator for pattern matching?

What is the difference between `IN` and `EXISTS` operators?

What are the different types of `JOIN`s in SQL?

Explain the difference between `INNER JOIN` and `LEFT JOIN`.

When would you use a `RIGHT JOIN` vs `LEFT JOIN`?

What is a `FULL OUTER JOIN` and when is it useful?

What is a `CROSS JOIN` and what are its use cases?

What is the difference between `JOIN` and `UNION`?

How does the `GROUP BY` clause work?

What is the difference between `COUNT(*)` and `COUNT(column_name)`?

Explain the different types of constraints (`NOT NULL`, `UNIQUE`, `CHECK`, etc.).

Explain the difference between `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()`.

What is the purpose of the `PARTITION BY` clause in window functions?

How do you use `LAG()` and `LEAD()` functions?

What is the difference between `UNION` and `UNION ALL`?

How do you perform conditional logic in SQL queries (`CASE` statements)?

What is the difference between `DELETE`, `DROP`, and `TRUNCATE`?

How do you optimize `JOIN` operations?

What is the impact of using functions in `WHERE` clauses?

When should you use `COMMIT` and `ROLLBACK`?