Question 1

What is SQL and what are its main components?

Accepted Answer

SQL (Structured Query Language) is a standard programming language for managing relational databases. Its main components include: DDL (Data Definition Language - CREATE, ALTER, DROP), DML (Data Manipulation Language - SELECT, INSERT, UPDATE, DELETE), DCL (Data Control Language - GRANT, REVOKE), and TCL (Transaction Control Language - COMMIT, ROLLBACK, SAVEPOINT). SQL is used for querying, updating, and managing data in RDBMS.

Question 2

Explain the difference between SQL and NoSQL databases.

Accepted Answer

SQL databases are relational, table-based, use structured schemas, and support ACID transactions. They are ideal for structured data and complex queries. NoSQL databases are non-relational, schema-flexible, and store data in various formats (document, key-value, graph). They prioritize scalability, performance, and flexibility over strict consistency. Choose SQL for transactional applications, NoSQL for big data and flexible schemas.

Question 3

What is the difference between 'DELETE' and 'TRUNCATE' commands?

Accepted Answer

DELETE removes rows based on a condition; it can be rolled back, triggers are fired, and it's slower for large tables. TRUNCATE removes all rows from a table, resets identity columns, cannot be rolled back (in most databases), and is faster because it deallocates data pages. TRUNCATE is DDL; DELETE is DML.

Question 4

Explain the difference between 'DROP' and 'TRUNCATE'.

Accepted Answer

DROP removes the entire table structure and data from the database; it cannot be rolled back. TRUNCATE removes all rows but keeps the table structure; it's faster and cannot be rolled back in most databases. DROP is irreversible; TRUNCATE preserves the table definition.

Question 5

What are the different types of SQL joins?

Accepted Answer

INNER JOIN: returns rows with matching values in both tables. LEFT JOIN (LEFT OUTER): returns all rows from the left table, with matching rows from the right. RIGHT JOIN: all rows from the right, with matches from the left. FULL JOIN: all rows from both tables. CROSS JOIN: Cartesian product of both tables. SELF JOIN: joining a table to itself.

Question 6

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

Accepted Answer

INNER JOIN returns only rows where there is a match in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right; non-matching rows from the right return NULL. Use LEFT JOIN when you want all left table records regardless of a match.

Question 7

What is a subquery and what are its types?

Accepted Answer

A subquery is a query nested inside another query. Types: correlated (references outer query, executed per row) and non-correlated (independent, executed once). Subqueries can be used in SELECT, FROM, WHERE, HAVING, and EXISTS clauses. They are powerful for complex data retrieval.

Question 8

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

Accepted Answer

WHERE filters rows before grouping (used with GROUP BY). HAVING filters groups after GROUP BY. WHERE cannot use aggregate functions; HAVING can. Example: SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 50000.

Question 9

Explain the difference between 'UNION' and 'UNION ALL'.

Accepted Answer

UNION combines result sets from two or more SELECT statements and removes duplicate rows. UNION ALL combines result sets without removing duplicates, making it faster. Use UNION ALL when duplicates are acceptable for better performance.

Question 10

What is the purpose of the 'GROUP BY' clause?

Accepted Answer

GROUP BY groups rows that have the same values in specified columns, allowing aggregate functions (SUM, COUNT, AVG, MAX, MIN) to be applied per group. It's essential for generating summary reports. Example: SELECT department, COUNT(*) FROM employees GROUP BY department.

Question 11

What are aggregate functions in SQL?

Accepted Answer

Aggregate functions perform calculations on a set of values and return a single value. Common ones: COUNT (number of rows), SUM (total), AVG (average), MAX (maximum), MIN (minimum). They are used with GROUP BY or with the OVER clause for window functions.

Question 12

Explain the concept of normalization in SQL.

Accepted Answer

Normalization is the process of organizing data to reduce redundancy and improve data integrity. Normal forms: 1NF (atomic values), 2NF (no partial dependency), 3NF (no transitive dependency), BCNF (3NF plus no overlap). Higher normal forms exist but are rarely used in practice.

Question 13

What is denormalization and why is it used?

Accepted Answer

Denormalization is the process of adding redundancy to a normalized database to improve read performance. It reduces the number of joins needed for queries. It's used in data warehouses, reporting databases, and read-heavy applications. Trade-off: increased storage and update anomalies.

Question 14

Explain the difference between 'PRIMARY KEY' and 'UNIQUE KEY'.

Accepted Answer

A PRIMARY KEY uniquely identifies each row in a table; it cannot contain NULL values and there can be only one per table. A UNIQUE KEY enforces uniqueness but allows one NULL value; there can be multiple UNIQUE keys per table. Both create indexes.

Question 15

What is a foreign key and why is it important?

Accepted Answer

A foreign key is a column or set of columns that references the PRIMARY KEY of another table. It enforces referential integrity, ensuring that relationships between tables are valid. It prevents orphan records and maintains data consistency.

Question 16

Explain the concept of indexing and its types.

Accepted Answer

Indexes are data structures that improve query performance by allowing faster data retrieval. Types: Clustered (data physically sorted, one per table), Non-clustered (separate structure with pointers, multiple per table), Unique (enforces uniqueness), Composite (multiple columns). Proper indexing is critical for performance.

Question 17

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

Accepted Answer

A clustered index determines the physical order of data in a table; there can be only one. A non-clustered index stores a separate structure with pointers to the data; you can have many. Clustered is faster for range queries; non-clustered is faster for specific lookups.

Question 18

What is the purpose of the 'EXPLAIN' or 'EXPLAIN PLAN' command?

Accepted Answer

The EXPLAIN command shows the execution plan of a query, including how tables are accessed, which indexes are used, and the cost of operations. It is used for query optimization and performance tuning. It helps identify bottlenecks and optimize SQL queries.

Question 19

Explain the concept of a transaction and ACID properties.

Accepted Answer

A transaction is a logical unit of work that groups multiple operations as a single unit. ACID properties: Atomicity (all or nothing), Consistency (valid state before/after), Isolation (concurrent transactions don't interfere), Durability (changes persist after commit). Transactions ensure data integrity.

Question 20

What are isolation levels in SQL transactions?

Accepted Answer

Isolation levels define how transaction changes are visible to other transactions. Levels: READ UNCOMMITTED (dirty reads allowed), READ COMMITTED (only committed data), REPEATABLE READ (consistent reads, prevents non-repeatable reads), SERIALIZABLE (highest isolation, prevents phantom reads). Higher isolation reduces concurrency.

Question 21

Explain the difference between 'COMMIT' and 'ROLLBACK'.

Accepted Answer

COMMIT permanently saves all changes made in the current transaction to the database. ROLLBACK undoes all changes made in the current transaction, reverting to the previous state. Both are used with transactions to control data integrity.

Question 22

What is a view in SQL and why is it used?

Accepted Answer

A view is a virtual table based on the result of a SELECT query. It does not store data physically; it provides a customized presentation of data. Uses: security (restrict access to specific columns), simplify complex queries, and provide data abstraction. Views can be materialized (stored) for performance.

Question 23

What is the difference between a view and a materialized view?

Accepted Answer

A view is virtual and executes the query each time it's accessed. A materialized view stores the result of the query physically, providing faster access but requiring refresh for updates. Materialized views are used for performance in reporting and data warehousing.

Question 24

Explain the concept of stored procedures and their advantages.

Accepted Answer

Stored procedures are pre-compiled SQL code stored in the database. Advantages: performance (pre-compiled), security (controlled access), reduced network traffic (server-side execution), and code reuse. They can accept parameters and return result sets.

Question 25

What is the difference between stored procedures and functions?

Accepted Answer

Functions must return a value and cannot modify database state; procedures can modify data and do not require a return value. Functions can be used in SELECT statements; procedures cannot. Functions are deterministic or non-deterministic; procedures always run as defined.

Question 26

What are triggers in SQL and when are they used?

Accepted Answer

Triggers are stored procedures that automatically execute in response to events (INSERT, UPDATE, DELETE) on a table. Uses: auditing, enforcing business rules, logging, and maintaining denormalized data. Use with caution as they can impact performance and be hard to debug.

Question 27

Explain the difference between 'CASCADE' and 'RESTRICT' in foreign keys.

Accepted Answer

CASCADE: when a referenced row is deleted/updated, the dependent rows are also deleted/updated. RESTRICT (or NO ACTION): prevents deletion/update of the referenced row if dependent rows exist. CASCADE maintains referential integrity automatically; RESTRICT prevents accidental data loss.

Question 28

What is a 'deadlock' and how can it be prevented?

Accepted Answer

A deadlock occurs when two or more transactions wait indefinitely for each other to release locks. Prevention: use consistent locking order, limit transaction size, use lower isolation levels, use SELECT FOR UPDATE with caution, and implement deadlock detection and retry logic.

Question 29

Explain the concept of query optimization.

Accepted Answer

Query optimization is the process of writing efficient SQL queries and ensuring proper indexing and database design. Techniques: use EXPLAIN to analyze execution plans, avoid SELECT *, use appropriate indexes, limit result sets with WHERE, avoid functions on indexed columns, and optimize joins.

Question 30

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

Accepted Answer

IN checks if a value exists in a subquery result; it can be used with a list of values. EXISTS checks if the subquery returns any rows; it's a boolean operator. EXISTS is generally faster for large datasets because it stops at the first match; IN can be inefficient with large subqueries.

Question 31

Explain the concept of 'window functions' in SQL.

Accepted Answer

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output row. They use the OVER clause. Examples: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER, AVG OVER. They are used for running totals, moving averages, and ranking.

Question 32

What is the 'ROW_NUMBER()' function and how is it used?

Accepted Answer

ROW_NUMBER() assigns a unique sequential number to each row in a partition, starting from 1. Example: ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank. It's used for pagination, deduplication, and ranking within groups.

Question 33

Explain the difference between 'RANK' and 'DENSE_RANK'.

Accepted Answer

RANK assigns the same rank to equal values, leaving gaps (1,2,2,4). DENSE_RANK assigns the same rank to equal values without gaps (1,2,2,3). Use RANK for competition rankings; use DENSE_RANK when you need contiguous ranks.

Question 34

What are common table expressions (CTEs) and why are they useful?

Accepted Answer

CTEs are temporary result sets defined using the WITH clause, available for a single query. They improve readability and allow recursive queries. Example: WITH cte AS (SELECT ...) SELECT * FROM cte. Recursive CTEs are powerful for hierarchical data (org charts, bill of materials).

Question 35

Explain recursive CTE and its use cases.

Accepted Answer

A recursive CTE refers to itself in the FROM clause. It consists of an anchor (base case) and a recursive part (self-reference). Use cases: organizational charts, category trees, bill of materials, and path finding in graphs. Example: WITH RECURSIVE cte (id, parent_id) AS (anchor UNION recursive).

Question 36

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

Accepted Answer

CHAR is fixed-length; it stores exactly the specified length, padding with spaces. VARCHAR is variable-length; it stores only the characters used, saving space. CHAR is faster for fixed-length data; VARCHAR is more space-efficient. Use CHAR for codes and fixed IDs; VARCHAR for variable text.

Question 37

What are the differences between 'DATETIME' and 'TIMESTAMP' in SQL?

Accepted Answer

DATETIME stores date and time without timezone, has a larger range (1000-9999), and takes 8 bytes. TIMESTAMP stores date and time with timezone awareness (UTC), has a smaller range (1970-2038), takes 4 bytes, and automatically updates on row changes. Use TIMESTAMP for timezone-sensitive data.

Question 38

Explain the concept of 'partitioning' in SQL databases.

Accepted Answer

Partitioning divides a large table into smaller, more manageable pieces (partitions) based on a partition key (e.g., date range). Benefits: improved query performance (partition pruning), easier data management (drop partitions), and faster maintenance (index rebuild). Types: range, list, hash, and composite partitioning.

Question 39

What is the difference between 'sharding' and 'partitioning'?

Accepted Answer

Sharding distributes data across multiple servers (horizontal scaling) for scalability. Partitioning divides data within a single database server (vertical scaling) for manageability and performance. Both use similar concepts but at different infrastructure levels.

Question 40

Explain the CAP theorem in the context of distributed databases.

Accepted Answer

CAP theorem states that a distributed system can only provide two of three guarantees: Consistency (all nodes see the same data), Availability (every request gets a response), and Partition tolerance (system continues to operate despite network partitions). SQL databases prioritize consistency; NoSQL often prioritize availability.

Question 41

What is the purpose of the 'COALESCE' function?

Accepted Answer

COALESCE returns the first non-NULL value from a list of expressions. Example: COALESCE(col1, col2, 'default'). It is useful for providing fallback values when dealing with NULLs. Equivalent to NVL in Oracle or ISNULL in SQL Server.

Question 42

Explain the concept of 'NULL' in SQL and how to handle it.

Accepted Answer

NULL represents missing or unknown data. It is not a value; comparisons with NULL using = or != return UNKNOWN. Handle NULLs using IS NULL, IS NOT NULL, COALESCE, and NULLIF. Aggregate functions ignore NULLs except COUNT(*). Proper NULL handling is crucial for data integrity.

Question 43

What is the difference between 'CAST' and 'CONVERT'?

Accepted Answer

Both are used for data type conversion. CAST is ANSI standard and has a simpler syntax: CAST(value AS type). CONVERT is database-specific (SQL Server) and supports additional style parameters for formatting: CONVERT(type, value, style). Use CAST for portability, CONVERT for specific formatting needs.

Question 44

Explain the concept of 'optimistic locking' vs 'pessimistic locking'.

Accepted Answer

Optimistic locking assumes conflicts are rare; it uses versioning (e.g., timestamp) to detect changes at update time. Pessimistic locking assumes conflicts are common; it locks the row for the transaction's duration. Optimistic is better for read-heavy apps; pessimistic for write-heavy.

Question 45

What is a 'database migration' and why is it important?

Accepted Answer

Database migration is the process of moving data and schema from one database to another or between versions. It includes schema changes, data transformations, and ensuring compatibility. Tools: Flyway, Liquibase, Alembic. It's essential for version control and continuous deployment.

Question 46

Explain the concept of 'ACID' compliance in SQL databases.

Accepted Answer

ACID compliance ensures reliable transaction processing. Atomicity: transactions are all-or-nothing. Consistency: transactions maintain database invariants. Isolation: concurrent transactions don't interfere. Durability: committed changes persist. ACID is a key feature of relational databases.

Question 47

What are the common performance tuning techniques for SQL?

Accepted Answer

Techniques: create appropriate indexes, use covering indexes, avoid SELECT *, use WHERE filters, optimize joins (use INNER JOIN over correlated subqueries), use EXISTS over IN for large datasets, analyze query plans, partition large tables, and use query hints cautiously.

Question 48

Explain the concept of 'temporary tables' and their use cases.

Accepted Answer

Temporary tables are session-scoped tables that store intermediate results. They are created with CREATE TEMP TABLE or #table. Use cases: complex multi-step queries, performance optimization (break down complex queries), and data transformation. They are automatically dropped at session end.

Question 49

What is the future of SQL and relational databases?

Accepted Answer

SQL continues to evolve with features like JSON support, window functions, CTEs, and better performance. Cloud databases (AWS RDS, Azure SQL) are growing. SQL remains the dominant language for data management. Trends: integration with AI/ML, serverless, automated optimization, and hybrid SQL/NoSQL architectures.

SQL Interview Q&A