Top 100 SQL Interview Questions and Answers (Beginner to Advanced) - Textnotes

Top 100 SQL Interview Questions and Answers (Beginner to Advanced)

Cover foundational concepts including data types, DDL/DML commands, primary keys, and simple queries with WHERE clauses. Expect questions on basic joins (INNER, LEFT) and aggregate functions like COUNT or SUM. These build core database manipulation skills tested in entry-level interviews.

Basics of SQL (Q1–Q20)

Q1. What is SQL?

A: SQL (Structured Query Language) is a standard language used to interact with relational databases for querying, updating, and managing data.

Q2. What are the types of SQL commands?

A: SQL commands are categorized as:

  1. DDL (Data Definition Language): CREATE, ALTER, DROP
  2. DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE
  3. DCL (Data Control Language): GRANT, REVOKE
  4. TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

Q3. What is a primary key?

A: A primary key uniquely identifies each record in a table. It cannot be NULL.

Q4. What is a foreign key?

A: A foreign key is a column that creates a relationship between two tables, referencing the primary key of another table.

Q5. What is the difference between SQL and NoSQL?

A:

  1. SQL: Relational, structured schema, supports JOINs, uses SQL queries.
  2. NoSQL: Non-relational, schema-less, stores JSON/key-value/document data, horizontally scalable.

Q6. How to create a database in SQL?


CREATE DATABASE CompanyDB;

Q7. How to create a table in SQL?


CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(50),
Salary DECIMAL(10,2)
);

Q8. How to insert data into a table?


INSERT INTO Employees (ID, Name, Department, Salary)
VALUES (1, 'John Doe', 'IT', 60000);

Q9. How to retrieve all records from a table?


SELECT * FROM Employees;

Q10. How to update a record in SQL?


UPDATE Employees
SET Salary = 65000
WHERE ID = 1;

Q11. How to delete a record in SQL?


DELETE FROM Employees WHERE ID = 1;

Q12. What is the difference between DELETE and TRUNCATE?

  1. DELETE: Removes rows with a WHERE clause; can be rolled back.
  2. TRUNCATE: Removes all rows, faster, cannot be rolled back in some databases.

Q13. What is the difference between CHAR and VARCHAR?

  1. CHAR: Fixed-length string.
  2. VARCHAR: Variable-length string.

Q14. What are constraints in SQL?

A: Constraints ensure data integrity: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.

Q15. How to select unique values in SQL?


SELECT DISTINCT Department FROM Employees;

Q16. What is the difference between WHERE and HAVING?

  1. WHERE: Filters rows before aggregation.
  2. HAVING: Filters groups after aggregation.

Q17. What are aggregate functions?

A: Functions that summarize data: COUNT(), SUM(), AVG(), MIN(), MAX().

Q18. How to count employees in a department?


SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;

Q19. What is the difference between INNER JOIN and LEFT JOIN?

  1. INNER JOIN: Returns only matching rows from both tables.
  2. LEFT JOIN: Returns all rows from the left table, matching rows from right table or NULL.

Q20. What is a subquery?

A: A query nested inside another query. Can be in SELECT, FROM, or WHERE clauses.

Intermediate SQL (Q21–Q50)

Q21. What is a UNION?

A: Combines results of two queries, removing duplicates.

Q22. What is a UNION ALL?

A: Combines results of two queries including duplicates.

Q23. What is the difference between INTERSECT and EXCEPT?

  1. INTERSECT: Returns common rows between two queries.
  2. EXCEPT/MINUS: Returns rows in first query not in second.

Q24. What is a view?

A: A virtual table based on a SELECT query. Can simplify complex queries.

Q25. What is a stored procedure?

A: A precompiled SQL code block that can take parameters and execute complex operations.

Q26. What is a function in SQL?

A: A routine that returns a single value (scalar) or a table (table-valued).

Q27. What is a trigger?

A: A special procedure that automatically executes in response to certain events (INSERT, UPDATE, DELETE).

Q28. What is a transaction?

A: A sequence of SQL statements executed as a single unit of work, ensuring ACID properties.

Q29. What are ACID properties?

  1. Atomicity: All or nothing.
  2. Consistency: Database remains consistent.
  3. Isolation: Transactions do not interfere.
  4. Durability: Changes are permanent after commit.

Q30. How to implement a transaction in SQL Server?


BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance - 100 WHERE ID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE ID = 2;
COMMIT;

Q31. What are indexes?

A: Structures that speed up data retrieval. Can be clustered or non-clustered.

Q32. What is a clustered index?

A: Index that sorts the actual data rows. Only one per table.

Q33. What is a non-clustered index?

A: Index that stores pointers to data without changing the table order.

Q34. How to create an index?


CREATE INDEX idx_Name ON Employees(Name);

Q35. What are temporary tables?

A: Tables that exist only during a session and are dropped automatically.

Q36. What is a CTE (Common Table Expression)?

A: A temporary result set used within a query, improving readability.


WITH DeptSalary AS (
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
)
SELECT * FROM DeptSalary WHERE AvgSalary > 50000;

Q37. What are window functions?

A: Functions that perform calculations over a set of rows related to the current row without collapsing results. Examples: ROW_NUMBER(), RANK(), SUM() OVER().

Q38. Difference between RANK() and DENSE_RANK()?

  1. RANK(): Skips numbers if there are ties.
  2. DENSE_RANK(): No gaps in ranking sequence.

Q39. What is a pivot table in SQL?

A: Converts rows into columns for reporting purposes.

Q40. What is normalization?

A: Process of organizing tables to reduce data redundancy (1NF, 2NF, 3NF, BCNF).

Q41. What is denormalization?

A: Combining tables to reduce JOINs, often improving read performance.

Q42. Difference between DELETE and TRUNCATE?

(Already explained in Q12; emphasize DELETE is logged, TRUNCATE is faster)

Q43. Difference between CHAR and VARCHAR?

(Already explained in Q13; emphasize CHAR fixed, VARCHAR variable)

Q44. How to prevent SQL injection?

A: Use parameterized queries or stored procedures instead of concatenating strings.

Q45. What is a materialized view?

A: A view that stores precomputed results for faster query performance.

Q46. What is partitioning?

A: Splitting large tables into smaller partitions for performance and manageability.

Q47. Difference between INNER JOIN and OUTER JOIN?

  1. INNER JOIN: Returns only matching rows.
  2. OUTER JOIN: Returns all rows from one/both tables, filling NULLs for unmatched rows.

Q48. Difference between UNION and JOIN?

  1. UNION: Combines rows from two separate queries.
  2. JOIN: Combines columns from two tables based on a relationship.

Q49. Difference between WHERE and ON clause?

  1. WHERE: Filters rows after JOIN (or single table).
  2. ON: Defines JOIN condition between tables.

Q50. How to optimize a slow query?

A:

  1. Use proper indexes.
  2. Avoid SELECT *.
  3. Filter early using WHERE.
  4. Analyze execution plan.
  5. Partition large tables if needed.

Advanced SQL Queries (Q51–Q65)

Q51. What are window functions in SQL?

A: Window functions perform calculations across a set of table rows related to the current row without collapsing rows. Examples: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), SUM() OVER().

Q52. What is the difference between ROW_NUMBER() and RANK()?

  1. ROW_NUMBER(): Sequential numbering, no ties.
  2. RANK(): Assigns same rank to tied values, skips subsequent numbers.

Q53. What is DENSE_RANK()?

A: Similar to RANK(), but does not skip numbers for ties.

Q54. What is NTILE()?

A: Divides rows into N approximately equal buckets.

Q55. What is a CTE (Common Table Expression)?

A: A temporary named result set for use within a SELECT, INSERT, UPDATE, or DELETE query. Improves readability and supports recursive queries.

Q56. What are recursive queries?

A: Queries where a CTE references itself to process hierarchical or tree-structured data.

Q57. What is PIVOT in SQL?

A: Converts rows into columns for reporting purposes.

Q58. What is UNPIVOT in SQL?

A: Converts columns into rows, the inverse of PIVOT.

Q59. What is a materialized view?

A: A view that stores query results physically for faster retrieval.

Q60. What are ranking and aggregate window functions?

  1. Ranking: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
  2. Aggregate: SUM(), AVG(), MIN(), MAX() over partitions.

Q61. Difference between GROUP BY and window functions?

  1. GROUP BY collapses rows into single aggregate rows.
  2. Window functions calculate aggregates without collapsing rows.

Q62. How to calculate running totals in SQL?


SELECT Name, Salary,
SUM(Salary) OVER (ORDER BY Name) AS RunningTotal
FROM Employees;

Q63. How to calculate moving average in SQL?


SELECT SaleDate, Amount,
AVG(Amount) OVER (ORDER BY SaleDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MovingAvg
FROM Sales;

Q64. Difference between UNION and UNION ALL?

  1. UNION: Removes duplicates.
  2. UNION ALL: Includes duplicates.

Q65. Difference between correlated and non-correlated subqueries?

  1. Correlated: Depends on outer query row for evaluation.
  2. Non-correlated: Independent, can run alone.

Transactions & Concurrency (Q66–Q75)

Q66. What are ACID properties?

  1. Atomicity, Consistency, Isolation, Durability.

Q67. How to use transactions in SQL Server?


BEGIN TRANSACTION;
-- SQL statements
COMMIT;

Q68. How to rollback a transaction?


ROLLBACK;

Q69. What is a savepoint?

A: A point in a transaction to which you can rollback without undoing the entire transaction.

Q70. What are isolation levels in SQL?

  1. READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE, SNAPSHOT

Q71. Difference between pessimistic and optimistic concurrency?

  1. Pessimistic: Locks data to prevent conflicts.
  2. Optimistic: Checks conflicts at commit time.

Q72. What is deadlock?

A: A situation where two transactions wait on each other indefinitely.

Q73. How to prevent deadlocks?

  1. Access tables in the same order.
  2. Keep transactions short.
  3. Use proper indexing.

Q74. Difference between COMMIT and ROLLBACK?

  1. COMMIT: Saves all changes permanently.
  2. ROLLBACK: Reverts changes made in the transaction.

Q75. Difference between autocommit and manual commit?

  1. Autocommit: Each SQL statement is automatically committed.
  2. Manual commit: Explicitly commit using COMMIT command.

Indexes, Performance & Optimization (Q76–Q85)

Q76. What is an index?

  1. Speeds up data retrieval.

Q77. Difference between clustered and non-clustered index?

  1. Clustered: Sorts actual data rows.
  2. Non-clustered: Stores pointers to rows.

Q78. How to create an index?


CREATE INDEX idx_Name ON Employees(Name);

Q79. What is query optimization?

  1. Process of improving SQL query performance using indexing, proper joins, and execution plan analysis.

Q80. What is an execution plan?

  1. Step-by-step strategy used by the database engine to execute a query.

Q81. How to analyze a slow query?

  1. Check execution plan, indexes, join order, and query structure.

Q82. What is table partitioning?

  1. Dividing large tables into smaller partitions for better performance.

Q83. What are statistics in SQL?

  1. Metadata about table data distribution used for query optimization.

Q84. Difference between SQL hints and optimizer?

  1. Optimizer: Automatic query plan selection.
  2. Hints: Explicit instructions to influence execution plan.

Q85. How to avoid SQL performance issues?

  1. Proper indexing, avoid SELECT *, minimize joins on large tables, use appropriate data types.

NoSQL & Big Data SQL (Q86–Q95)

Q86. What is NoSQL?

  1. Non-relational database storing key-value, document, or column-family data.

Q87. Types of NoSQL databases?

  1. Document: MongoDB, CouchDB
  2. Key-value: Redis, DynamoDB
  3. Column-family: Cassandra, HBase
  4. Graph: Neo4j

Q88. How to query JSON data in NoSQL?

  1. Use aggregation pipelines (MongoDB) or SQL-like functions (BigQuery JSON functions).

Q89. What is HiveQL?

  1. SQL-like language to query Hadoop data stored in HDFS.

Q90. What is BigQuery SQL?

  1. Google Cloud SQL-like engine to query petabyte-scale datasets using SQL syntax.

Q91. Difference between SQL and BigQuery SQL?

  1. BigQuery SQL can handle massive datasets, uses standard SQL extensions, and is cloud-based.

Q92. How to integrate SQL with Python?

  1. Use pandas.read_sql_query() or SQLAlchemy for database connections and analytics.

Q93. How to integrate SQL with R?

  1. Use DBI and dplyr to query SQL databases and process results in R.

Q94. What are JSON functions in BigQuery?

  1. JSON_EXTRACT(), JSON_EXTRACT_SCALAR(), JSON_VALUE() to query nested JSON data.

Q95. How to aggregate large datasets efficiently?

  1. Use GROUP BY, partitions, materialized views, and BigQuery/Hive parallel processing.

Reporting & Analytics SQL (Q96–Q100)

Q96. What is a running total?

  1. A cumulative sum of values over a sequence of rows using SUM() OVER().

Q97. How to compute moving average?

  1. Average of current and previous N rows using AVG() OVER(ROWS BETWEEN N PRECEDING AND CURRENT ROW).

Q98. What is a ranking query?

  1. Assigns ranks to rows based on sorting criteria using RANK(), DENSE_RANK(), or ROW_NUMBER().

Q99. What are GROUPING SETS, ROLLUP, and CUBE?

  1. GROUPING SETS: Multiple groupings in one query
  2. ROLLUP: Hierarchical totals
  3. CUBE: All possible combinations of aggregates

Q100. How to use SQL for business analytics?

  1. Combine aggregation, window functions, ranking, and pivot/unpivot to create reports, dashboards, and KPIs.