SQL interviews cover a wide range — fundamentals and joins, sure, but also window functions, transaction isolation, indexing internals, and query optimization that most day-to-day query-writing never forces you to think about explicitly. This walks through all of it, organized by topic, with real (mostly dialect-neutral ANSI SQL, with call-outs where PostgreSQL/MySQL/SQL Server genuinely diverge) code, a few diagrams, and a mock test at the end.

SQL Fundamentals

Q1. What is SQL and what is it used for?

SQL (Structured Query Language) is the standard language for defining, querying, and manipulating data in a relational database — creating tables, inserting/updating/deleting rows, and asking questions of the data with SELECT. It's declarative: you describe the result you want, and the database's query optimizer figures out how to actually get it, rather than you writing step-by-step retrieval logic yourself.

Q2. Describe the difference between SQL and NoSQL databases.

Q3. What are the different types of SQL commands?

Q4. Explain the purpose of the SELECT statement.

SELECT first_name, last_name, department
FROM employees
WHERE hire_date >= '2024-01-01'
ORDER BY last_name;

SELECT retrieves data from one or more tables without modifying it — the only DML statement that's purely read-only. Its clauses execute in a logical order that's the opposite of how you type them: FROM/JOIN first, then WHERE, then GROUP BY, then HAVING, then SELECT's own column list, then ORDER BY last.

Q5. What is the difference between WHERE and HAVING clauses?

SELECT department, COUNT(*) AS headcount
FROM employees
WHERE active = TRUE          -- filters rows first
GROUP BY department
HAVING COUNT(*) > 10;        -- then filters the resulting groups

Q6. Define what a JOIN is in SQL and list its types.

A JOIN combines rows from two or more tables based on a related column between them — the whole point of a relational model, where data is normalized across tables instead of duplicated in one giant sheet.

Q7. What is a primary key in a database?

A primary key uniquely identifies every row in a table — it can't be NULL and can't repeat, and a table has exactly one (though it may span multiple columns as a composite key). It's also what foreign keys in other tables point back to, forming the actual relationships in a relational schema.

Q8. Explain what a foreign key is and how it is used.

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

A foreign key column references another table's primary key, enforcing referential integrity — the database physically won't let you insert an order for a customer_id that doesn't exist, and by default won't let you delete a customer who still has orders (unless ON DELETE CASCADE/SET NULL says otherwise).

Q9. How can you prevent SQL injections?

-- Vulnerable: string-concatenated user input becomes part of the query text
-- "SELECT * FROM users WHERE username = '" + input + "'"

-- Safe: a parameterized query — the value is bound, never parsed as SQL
SELECT * FROM users WHERE username = ?;   -- or @username / :username depending on driver

• Always use parameterized queries / prepared statements — never string-concatenate user input into SQL text
• Apply the principle of least privilege to the DB account your app connects with (it shouldn't be able to DROP TABLE if it only ever needs SELECT/INSERT)
• Validate and constrain input at the application layer too, as a second line of defense
• Use an ORM's parameter binding rather than its raw-SQL escape hatches for anything touching user input

Q10. What is normalization? Explain with examples.

Normalization organizes data to eliminate redundancy and prevent update anomalies, by progressively splitting data into related tables. 1NF requires atomic values (no comma-separated lists in a cell); 2NF removes partial dependencies (every non-key column depends on the whole primary key, not just part of a composite one); 3NF removes transitive dependencies (a non-key column can't depend on another non-key column) — e.g. storing a customer's city in the orders table when it really belongs to a customers table is a 3NF violation.

Q11. Describe the concept of denormalization and when you would use it.

Denormalization deliberately reintroduces redundancy — duplicating or pre-joining data — to trade write complexity and storage for faster reads, since fewer joins means less work per query. It's the right call for read-heavy reporting/analytics workloads (data warehouses, dashboards) where query speed matters more than avoiding duplicate storage, and the wrong call for transactional systems where consistency on write matters most.

Q12. What are indexes and how can they improve query performance?

An index is a separate, ordered data structure (usually a B-tree) that lets the database locate rows matching a condition without scanning the whole table — the same idea as a book's index letting you jump to a page instead of reading cover to cover. Without one, a WHERE/JOIN condition forces a full table scan; with the right index, it's a fast lookup, at the cost of extra storage and slightly slower writes (the index has to be maintained too).

Q13. Explain the purpose of the GROUP BY clause.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

GROUP BY collapses rows sharing the same value(s) in specified columns into one row per group, so aggregate functions (COUNT, SUM, AVG…) can compute a result per group instead of across the whole table.

Q14. What is a subquery, and when would you use one?

SELECT name
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = 'Remote'
);

A subquery is a query nested inside another — in a WHERE clause (as above), in the FROM clause (a derived table), or even in the SELECT list itself. They're most useful when a value or set of values needed for the outer query has to be computed first, or when a CTE would be overkill for something used just once.

Q15. Describe the functions of the ORDER BY clause.

SELECT name, salary
FROM employees
ORDER BY salary DESC, name ASC;

ORDER BY sorts the final result set — ascending (ASC, the default) or descending (DESC), by one or more columns in priority order, with later columns breaking ties among rows that are equal on the earlier ones. It's the last clause to logically execute, which is why it's the only clause allowed to reference a column alias defined in the SELECT list.

Q16. What are aggregate functions in SQL?

Aggregate functions compute one value from a set of rows — used either across the whole table, or per group when paired with GROUP BY. Every aggregate except COUNT(*) ignores NULLs by default.

Q17. Explain the differences between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

-- INNER: only customers that have at least one order
SELECT c.name, o.id FROM customers c INNER JOIN orders o ON o.customer_id = c.id;

-- LEFT: every customer, orders columns NULL if they have none
SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;

-- RIGHT: every order, customer columns NULL if the FK is somehow orphaned
SELECT c.name, o.id FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;

-- FULL: every customer AND every order, matched where possible
SELECT c.name, o.id FROM customers c FULL JOIN orders o ON o.customer_id = c.id;

The mental model: INNER keeps only matches, LEFT/RIGHT keep everything from one named side and pad the other with NULLs where there's no match, and FULL keeps everything from both sides. MySQL notably has no FULL JOIN keyword — it's emulated with a LEFT JOIN UNION a RIGHT JOIN when needed.

Q18. How do you insert a new row into a database table?

INSERT INTO employees (first_name, last_name, department)
VALUES ('Ada', 'Lovelace', 'Engineering');

-- Multiple rows in one statement:
INSERT INTO employees (first_name, last_name, department) VALUES
    ('Grace', 'Hopper', 'Engineering'),
    ('Alan', 'Turing', 'Research');

Q19. Explain how to update records in a database table.

UPDATE employees
SET department = 'Platform Engineering'
WHERE department = 'Engineering';

UPDATE without a WHERE clause updates every row in the table — a classic, very real production mistake, which is exactly why most teams run a SELECT with the same WHERE first to confirm exactly which rows will be affected before running the UPDATE for real.

Q20. What is a SQL View and what are its advantages?

CREATE VIEW active_employees AS
SELECT id, first_name, last_name, department
FROM employees
WHERE active = TRUE;

SELECT * FROM active_employees WHERE department = 'Engineering';

A view is a saved, named query that behaves like a virtual table — it doesn't store data itself (unless it's a materialized view), just the query definition, re-run each time it's referenced. It's useful for hiding query complexity behind a simple name, enforcing a consistent row/column-level access pattern, and giving consumers a stable interface even if the underlying tables' structure changes.

SQL Data Types and Operators

Q21. List the different data types available in SQL.

Q22. What are the differences between CHAR, VARCHAR, and TEXT data types?

Q23. How do you use the BETWEEN operator in SQL?

SELECT * FROM orders
WHERE order_date BETWEEN '2026-01-01' AND '2026-01-31';

BETWEEN is inclusive on both ends — it's exactly equivalent to order_date >= '2026-01-01' AND order_date <= '2026-01-31', just more readable for range checks.

Q24. Describe the use of the IN operator.

SELECT * FROM employees
WHERE department IN ('Engineering', 'Design', 'Product');

IN checks a value against a list (or a subquery's result set), replacing what would otherwise be a chain of OR conditions — cleaner to write and, in most engines, optimized identically to one.

Q25. Explain the use of wildcard characters in SQL.

Q26. What is the purpose of the LIKE operator?

SELECT * FROM customers WHERE email LIKE '%@gmail.com';
SELECT * FROM products WHERE sku LIKE 'A_23%';  -- 'A', any one char, then '23...'

LIKE does pattern matching using the % and _ wildcards — the trade-off is that a leading % (e.g. '%gmail.com') generally can't use a standard B-tree index efficiently, since the engine can't know where in the string to start looking, forcing a full scan on large tables.

Q27. How do you handle NULL values in SQL?

SELECT * FROM employees WHERE manager_id IS NULL;      -- correct
SELECT * FROM employees WHERE manager_id = NULL;       -- always false — NULL isn't 'equal' to anything, even NULL

NULL means "unknown/absent," not zero or empty string, and it doesn't behave like a normal value in comparisons — you must use IS NULL / IS NOT NULL, never = or !=. Aggregate functions (except COUNT(*)) silently skip NULLs, which is a common source of subtly wrong AVG()/SUM() results if you don't account for it.

Q28. What does the COALESCE function do?

SELECT name, COALESCE(phone, mobile, 'No contact number') AS contact
FROM customers;

COALESCE returns the first non-NULL value from a list of expressions, evaluated left to right — the standard, portable way to supply a fallback/default value, more general-purpose than engine-specific equivalents like ISNULL (SQL Server) or IFNULL (MySQL) which only take two arguments.

Q29. What is the difference between UNION and UNION ALL?

Q30. Describe the use of arithmetic operators in SQL queries.

SELECT name, price, quantity, price * quantity AS line_total
FROM order_items;

+, -, *, / (and % for modulo in most engines) work directly on numeric columns and literals in the SELECT list, WHERE clause, or anywhere else an expression is allowed — with the usual gotcha that integer division truncates in most engines (10 / 3 = 3, not 3.33), so cast at least one operand to a decimal/float when you need a fractional result.

SQL Advanced Queries

Q31. Explain how to use the CASE statement in SQL.

SELECT name, salary,
    CASE
        WHEN salary >= 100000 THEN 'Senior'
        WHEN salary >= 60000  THEN 'Mid'
        ELSE 'Junior'
    END AS band
FROM employees;

CASE is SQL's inline conditional expression — evaluated top to bottom, stopping at the first matching WHEN, with ELSE as the fallback (NULL if omitted and nothing matches). It can appear anywhere an expression is valid: SELECT, WHERE, ORDER BY, even inside an aggregate.

Q32. How would you perform a self JOIN?

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

A self join is just a normal join where both sides are the same table, given two different aliases so the query can distinguish "this row" from "the related row" — the classic example being an employee table where manager_id points back to another row in the same table.

Q33. What is a cross JOIN and when would you use it?

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;
-- every size paired with every color — a Cartesian product

CROSS JOIN pairs every row of one table with every row of the other, with no matching condition — genuinely useful for generating combinations (every size × every color for a product catalog), but an accidental cross join (forgetting a JOIN condition) is one of the most common causes of a query that returns millions of unexpected rows.

Q34. How to implement pagination in SQL queries?

SELECT * FROM products
ORDER BY id
LIMIT 20 OFFSET 40;   -- page 3, 20 rows per page

Always pair OFFSET/LIMIT with an ORDER BY — without a deterministic sort order, the database is free to return rows in a different order each time, which makes pagination silently skip or repeat rows between pages.

Q35. Explain the concept of Common Table Expressions (CTEs) and recursive CTEs.

-- Plain CTE — a named, reusable subquery
WITH high_earners AS (
    SELECT * FROM employees WHERE salary > 100000
)
SELECT department, COUNT(*) FROM high_earners GROUP BY department;

-- Recursive CTE — walks an org chart from the CEO down
WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 1 AS depth
    FROM employees WHERE manager_id IS NULL          -- anchor: the CEO
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id         -- recursive step
)
SELECT * FROM org_chart ORDER BY depth;

A CTE (WITH ...) names a subquery so it can be referenced (even multiple times) later in the same statement, mainly for readability over a deeply nested subquery. A recursive CTE adds an anchor query (the starting rows) UNION ALL'd with a recursive query that references the CTE itself, repeating until the recursive part returns no more rows — the standard way to walk hierarchical data (org charts, category trees, bill-of-materials).

Q36. What are window functions and how are they used?

SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

A window function computes a value across a set of related rows (the "window," defined by PARTITION BY/ORDER BY) without collapsing them into one row the way GROUP BY does — every input row still appears in the output, now with an extra computed column alongside it. This is exactly what makes running totals, rankings-within-groups, and previous/next-row comparisons possible in a single query instead of a self-join or app-side loop.

Q37. How can you concatenate column values in SQL?

Q38. What is the PIVOT operation and how would you apply it?

-- Turn rows of (year, quarter, revenue) into columns per quarter
SELECT year,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS q1,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS q2,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue END) AS q3,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue END) AS q4
FROM sales
GROUP BY year;

PIVOT rotates row values into columns — turning "long" data (one row per year+quarter) into "wide" data (one row per year, one column per quarter). Some engines (SQL Server, Oracle) have a native PIVOT keyword; the conditional-aggregation pattern above works identically on any engine and is often clearer anyway.

Q39. Explain the process of combining a query that uses a GROUP BY with one that uses ORDER BY.

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
ORDER BY headcount DESC;

They compose naturally because of execution order — GROUP BY runs first and produces one row per group, then ORDER BY sorts those already-aggregated rows, so it can freely reference an aggregate like COUNT(*) or a SELECT-list alias.

Q40. How would you find duplicate records in a table?

SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Group by the column(s) that define a "duplicate," then filter groups with HAVING COUNT(*) > 1 — the classic pattern for finding them; deleting all-but-one of each duplicate group typically pairs this with ROW_NUMBER() OVER (PARTITION BY ...) to pick which row survives.

Database Design & Architecture

Q41. What is the Entity-Relationship Model?

The ER model is a conceptual way of designing a database before writing any SQL — entities (things, like Customer or Order) become tables, attributes become columns, and relationships (one-to-many, many-to-many) become foreign keys or junction tables. An ER diagram is the visual artifact of this design phase, and it's the standard way teams agree on a schema before implementation.

Q42. Explain the different types of database schema.

Q43. What are Stored Procedures and how are they beneficial?

CREATE PROCEDURE GetEmployeesByDept (IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM employees WHERE department = dept_name;
END;

CALL GetEmployeesByDept('Engineering');

A stored procedure is a named, precompiled block of SQL stored in the database itself and invoked by name — benefits include reduced network round-trips (one call instead of many statements), centralized logic that every consuming application shares, and a smaller attack surface for SQL injection since parameters are bound, not concatenated.

Q44. What is a trigger in SQL and when should it be used?

CREATE TRIGGER trg_audit_salary_change
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_audit (employee_id, old_salary, new_salary, changed_at)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
END;

A trigger automatically runs in response to an INSERT/UPDATE/DELETE on a table — useful for auditing, enforcing invariants a simple constraint can't express, or keeping a derived/denormalized value in sync. Use them sparingly: triggers hide behavior from anyone just reading application code, and a chain of triggers calling triggers can become genuinely hard to reason about.

Q45. Describe the concept of ACID in databases.

Q46. What is database sharding?

Sharding splits one logical table's data across multiple physical databases/servers (each shard holding a subset of rows, typically by a hash or range of a shard key), so no single machine has to hold or serve the entire dataset. It buys horizontal scalability at real cost: cross-shard joins and transactions become much harder, and picking the wrong shard key can leave you with unbalanced "hot" shards.

Q47. How do database indexes work and what types are there?

Q48. Describe the process of data warehousing.

A data warehouse is a separate, denormalized (often star-schema) database optimized for analytical queries across large historical datasets, fed by an ETL/ELT pipeline pulling from one or more operational (OLTP) systems — the split exists because the query patterns (huge aggregations across millions of rows) and the query patterns of a live transactional app (fast, small, targeted reads/writes) actively fight each other on the same schema and hardware.

Q49. Explain the difference between OLTP and OLAP systems.

Q50. What are materialized views and how do they differ from standard views?

CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
FROM orders
GROUP BY 1;

REFRESH MATERIALIZED VIEW monthly_revenue;   -- has to be refreshed explicitly (or on a schedule)

A standard view re-runs its underlying query every time it's referenced; a materialized view actually stores the computed result and serves reads from that stored copy — much faster for expensive aggregations, at the cost of the data being only as fresh as the last refresh.

SQL Optimization and Performance

Q51. How do you identify and optimize slow-running queries?

• Turn on slow-query logging to find the actual worst offenders, rather than guessing
• Look at the query's execution plan for full table scans where an index lookup was expected
• Check whether WHERE/JOIN/ORDER BY columns are actually indexed, and whether an existing index is even being used
• Watch for functions wrapped around an indexed column (WHERE YEAR(date) = 2026) — that pattern usually defeats the index entirely
• Rewrite correlated subqueries as joins where possible — they often force row-by-row re-execution

Q52. What is query execution plan in SQL?

An execution plan is the query optimizer's chosen strategy for actually running a query — which indexes (if any) it'll use, the join order and join algorithm, and the estimated number of rows at each step. It's the single most useful diagnostic artifact for understanding why a query is slow, because it shows what the database actually decided to do, not what you assumed it would do.

Q53. Explain how to use EXPLAIN or EXPLAIN ANALYZE.

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN shows the planned execution strategy without running the query; EXPLAIN ANALYZE actually executes it and reports real timings and row counts alongside the plan, which is what lets you compare the optimizer's row-count estimate against reality — a big gap between the two is often exactly why a plan looks fine on paper but performs badly in practice.

Q54. How can indexing affect performance both positively and negatively?

Q55. Describe how to measure the performance of SQL queries.

• Wall-clock execution time via EXPLAIN ANALYZE or the client's own timing
• Rows examined vs. rows returned — a huge gap signals a missing or unused index
• I/O metrics (logical/physical reads) — how much data actually had to be touched on disk vs. cache
• Concurrency behavior under realistic load, not just a single run on an idle database

Q56. How would you rewrite a query to improve its performance?

-- Before: function on the indexed column defeats the index
SELECT * FROM orders WHERE YEAR(order_date) = 2026;

-- After: a range condition the index can actually use
SELECT * FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

Q57. What are partitioned tables and how can they optimize performance?

CREATE TABLE orders (
    id INT, order_date DATE, customer_id INT
) PARTITION BY RANGE (order_date) (
    PARTITION p2025 VALUES LESS THAN ('2026-01-01'),
    PARTITION p2026 VALUES LESS THAN ('2027-01-01')
);

Partitioning splits one logical table into physically separate chunks (commonly by date range) while still being queried as a single table — a query filtered to one date range only has to touch the matching partition ("partition pruning"), which can turn a scan of years of data into a scan of one month's worth.

SQL Security

Q58. How do you implement database encryption in SQL?

At rest: Transparent Data Encryption (TDE) encrypts the whole database file transparently to queries, or column-level encryption targets specific sensitive fields (SSNs, payment data) when only some columns need it. In transit: TLS on the connection between application and database, non-negotiable for anything crossing a network you don't fully control.

Q59. What are roles and how do they manage database access?

CREATE ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
GRANT read_only TO analyst_user;

A role bundles a set of permissions under one name, granted to users (or other roles) instead of managing each permission per user individually — change the role's permissions once, and every user holding it is affected, which is far more maintainable than hand-editing grants for dozens of individual accounts.

Q60. Explain the concept of row-level security.

CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::int);

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Row-level security restricts which individual rows a user/role can see or modify, enforced by the database itself rather than trusted-but-forgettable WHERE clauses sprinkled through application code — the standard mechanism for multi-tenant systems where every tenant must only ever see their own rows, even if a query forgets to filter by tenant explicitly.

Q61. Describe how to create and use user-defined functions (UDFs).

CREATE FUNCTION full_name(first VARCHAR, last VARCHAR)
RETURNS VARCHAR AS $$
    SELECT first || ' ' || last;
$$ LANGUAGE SQL;

SELECT full_name(first_name, last_name) FROM employees;

SQL Functions and Expressions

Q62. Describe scalar-valued and table-valued functions.

Q63. How would you define a stored procedure with input and output parameters?

CREATE PROCEDURE GetOrderTotal (
    IN order_id INT,
    OUT total DECIMAL(10,2)
)
BEGIN
    SELECT SUM(price * quantity) INTO total
    FROM order_items WHERE order_items.order_id = order_id;
END;

Q64. What is the difference between a function and a stored procedure?

Q65. How do you use the CAST and CONVERT functions?

SELECT CAST(price AS INT) FROM products;              -- ANSI standard, portable
SELECT CONVERT(INT, price) FROM products;             -- SQL Server style

Both convert a value from one data type to another — CAST is the ANSI-standard, portable syntax supported everywhere; CONVERT is a SQL Server-specific (and MySQL has its own CONVERT too, with different argument order) alternative that additionally supports format codes for things like date-string formatting.

Transaction Control and Locking

Q66. What is a database transaction?

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

A transaction groups multiple statements into one all-or-nothing unit — either every statement in it succeeds and gets committed, or (if anything fails) the whole thing rolls back as if none of it happened. The classic example above (moving money between two accounts) is exactly why atomicity matters: you never want one UPDATE to succeed and the other to fail.

Q67. Explain the concept of locking and its types in SQL databases.

Q68. What are the properties of transactions?

ACID — Atomicity, Consistency, Isolation, Durability (covered in detail above under Database Design & Architecture). They're the formal guarantees a transaction gives you, and the reason transactional databases are trusted for anything involving money, inventory, or anything else where a partial or inconsistent write is unacceptable.

Q69. How do you manage transaction isolation levels?

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Isolation level is a trade-off knob between correctness guarantees and concurrency/throughput — stricter levels prevent more anomalies but hold locks longer and allow less concurrent access, which is exactly why Read Committed (not Serializable) is the practical default in most production systems.

Q70. What does it mean to commit or roll back a transaction?

COMMIT;    -- makes every change in the transaction permanent
ROLLBACK;  -- discards every change in the transaction, as if it never ran

SQL and Modern Data Ecosystems

Q71. How can SQL be integrated with big data technologies?

SQL-on-Hadoop/big-data engines (Hive, Presto/Trino, Spark SQL) expose the same familiar SQL syntax over data that actually lives in distributed storage (HDFS, S3, data lakes) rather than a traditional relational engine — letting analysts query petabyte-scale datasets without learning a completely different query language, even though the execution underneath is a distributed engine, not a single-node database.

Q72. Discuss the interoperability of SQL with cloud-based data stores.

Cloud data warehouses (Snowflake, BigQuery, Redshift) speak SQL (mostly ANSI-standard, with their own extensions) as the primary interface, decoupling compute from storage so you can scale each independently and pay per query/second of compute rather than for a fixed always-on server — a meaningfully different cost and scaling model from a traditional on-prem database.

Q73. What is Data Lake and how can SQL interact with it?

A data lake stores raw data (structured, semi-structured, unstructured — JSON, Parquet, CSV, images) in cheap object storage without forcing a schema up front, the opposite philosophy from a warehouse's structured, pre-modeled tables. A query engine (Athena, Presto, Spark SQL) then applies a schema at query time ("schema-on-read") so you can still run SQL against files that were never loaded into a traditional database at all.

Q74. Explain the interaction between SQL and NoSQL within the same application.

It's common for one system to use both deliberately — a relational database for the transactional core (orders, accounts, anything needing joins/ACID) and a NoSQL store (Redis for caching/sessions, Elasticsearch for full-text search, MongoDB for a flexible document type) for the specific workload each is actually best at, rather than forcing one database to be good at everything.

Q75. How does SQL work within a microservices architecture?

The common pattern is database-per-service — each microservice owns its own database (SQL or otherwise) and nothing else touches it directly, so services stay independently deployable. Cross-service "joins" that a monolith would do with SQL become API calls or asynchronous events between services instead, which is a real added complexity cost microservices accept in exchange for independent scaling and deployment.

SQL Best Practices and Standards

Q76. What are some common SQL coding practices you follow?

• Always specify explicit column names in SELECT and INSERT — never SELECT * or INSERT without a column list in production code
• Format consistently (keyword casing, indentation) so diffs and reviews stay readable
• Alias tables meaningfully in multi-join queries, not just single letters that force the reader to scroll back up
• Comment the why behind a non-obvious query, not the what — the SQL itself already says what it does

Q77. How can you ensure the portability of SQL scripts across different database systems?

• Stick to ANSI-standard SQL where possible, isolating any engine-specific syntax (window functions, JSON operators, pagination) behind clearly marked sections
• Avoid relying on a specific engine's default behavior for things the standard leaves implementation-defined (implicit type coercion, sort collation)
• Test against every target engine, not just the one you develop against day to day

Q78. What methods do you use for version controlling SQL scripts?

• Migration tools (Flyway, Liquibase, EF Core Migrations, Django migrations) that apply numbered, ordered, one-way (or reversible) schema changes
• Every schema change as its own migration file committed to source control alongside the application code that depends on it
• Never hand-edit a production schema directly — every change goes through the same migration pipeline as everywhere else

Q79. What are the benefits of using stored procedures instead of embedding SQL queries in code?

Q80. How do you document SQL code effectively?

• A schema diagram (ER diagram) kept up to date alongside the actual migrations
• Comments on non-obvious business logic embedded in a query or trigger — the reasoning, not a restatement of the SQL
• A data dictionary describing what each table/column actually means, especially for anything not self-evident from its name

Analytical SQL Questions

Q81. How would you find the Nth highest salary from a table?

-- Window function approach — works cleanly with ties, most portable across engines
SELECT DISTINCT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 3;   -- 3rd highest

-- LIMIT/OFFSET shortcut (no tie-handling, dialect-specific)
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;

Q82. How do you count the number of occurrences of a specific value in a column?

SELECT COUNT(*) FROM orders WHERE status = 'cancelled';

-- Counts for every distinct value at once:
SELECT status, COUNT(*) FROM orders GROUP BY status;

Q83. How can you calculate running totals in SQL?

SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM daily_sales;

A window function's SUM() OVER (ORDER BY ...) with no explicit frame defaults to "everything up to and including the current row" — exactly a running total, computed in one pass instead of a self-join or an app-side loop.

Q84. Explain how to reverse the contents of a column without using a reverse function.

WITH RECURSIVE reversed (word, remaining, result) AS (
    SELECT word, word, '' FROM words
    UNION ALL
    SELECT word,
           SUBSTRING(remaining, 1, LENGTH(remaining) - 1),
           SUBSTRING(remaining, LENGTH(remaining), 1) || result
    FROM reversed
    WHERE LENGTH(remaining) > 0
)
SELECT word, result FROM reversed WHERE LENGTH(remaining) = 0;

This is much more of a "can you think recursively in SQL" exercise than something you'd ship — a recursive CTE peeling one character off the end and prepending it to a result string each pass, until nothing's left. In real code, just use REVERSE() where the engine has one.

Q85. What approach do you use for creating a calendar table, and what are its uses?

WITH RECURSIVE calendar (date) AS (
    SELECT DATE '2026-01-01'
    UNION ALL
    SELECT date + INTERVAL '1 day' FROM calendar WHERE date < DATE '2026-12-31'
)
SELECT date, EXTRACT(DOW FROM date) AS day_of_week
FROM calendar;

A calendar (or "date dimension") table is a pre-generated row per date over some range, often with extra columns (day of week, fiscal quarter, is-holiday) precomputed — useful for LEFT JOINing against it to guarantee every date appears in a report even on days with zero underlying activity, which a plain GROUP BY on the activity table alone can never produce.

Data Manipulation and ETL

Q86. What is the process of Extract, Transform, Load (ETL)?

ETL moves data from operational source systems into an analytical target (typically a data warehouse), transforming it along the way — deduplicating, standardizing formats, joining reference data, computing derived fields — so what lands in the warehouse is already clean and query-ready. ELT (load first, transform inside the warehouse using its own compute) has become the more common pattern with cloud warehouses, since they can transform at scale more cheaply than a dedicated ETL server can.

Q87. How do you import/export data from/to a flat file using SQL?

-- PostgreSQL
COPY orders FROM '/path/to/orders.csv' DELIMITER ',' CSV HEADER;
COPY orders TO '/path/to/export.csv' DELIMITER ',' CSV HEADER;

-- MySQL
LOAD DATA INFILE '/path/to/orders.csv' INTO TABLE orders
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

-- SQL Server
BULK INSERT orders FROM 'C:\data\orders.csv' WITH (FIELDTERMINATOR = ',', FIRSTROW = 2);

Q88. Explain the steps for a basic ETL process in a data warehousing environment.

Extract from each source system (often incrementally — only new/changed rows since the last run, tracked via a timestamp or change-data-capture log); transform by cleaning, standardizing types/formats, deduplicating, and joining in reference/dimension data; load into the warehouse's fact/dimension tables, usually into a staging table first so a failed load never corrupts the tables real queries read from.

Q89. How do you cleanse and format data using SQL queries?

UPDATE customers SET email = LOWER(TRIM(email));
UPDATE customers SET phone = REGEXP_REPLACE(phone, '[^0-9]', '', 'g');  -- strip non-digits
DELETE FROM customers WHERE email IS NULL OR email = '';

Q90. What tools do you use for automating data import/export routines?

• SSIS (SQL Server Integration Services) — the classic Microsoft-stack ETL tool
• Apache Airflow — schedules and orchestrates arbitrary data pipelines, not SQL-specific
• dbt (data build tool) — SQL-first transformations, version-controlled, run inside the warehouse
• Azure Data Factory / AWS Glue — cloud-native, managed ETL/ELT services

Domain-Specific SQL Scenarios

Q91. How would you model a many-to-many relationship in SQL?

CREATE TABLE students (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE courses (id INT PRIMARY KEY, title VARCHAR(100));

CREATE TABLE enrollments (           -- the junction/bridge table
    student_id INT REFERENCES students(id),
    course_id INT REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);

A many-to-many relationship needs a third "junction" table in between, holding one row per pairing, with a foreign key to each side — a student can have many rows in enrollments (many courses), and a course can have many rows too (many students), which is exactly the relationship neither table could express with a single foreign key on its own.

Q92. Describe how to manage hierarchical data in SQL.

Q93. How would you approach writing SQL queries for a reporting application?

• Favor read-optimized structures (views, materialized views, a denormalized reporting schema) over hitting the live OLTP schema directly
• Parameterize date ranges and filters rather than hardcoding them, so the same query serves many report variants
• Watch for report queries competing with production transactional load — a heavy report against the live database is a common cause of unrelated production slowdowns

Q94. Explain how to handle temporal data and time zones in SQL.

-- Store timestamps in UTC, convert to a display time zone only at read time
SELECT order_id, created_at AT TIME ZONE 'America/New_York' AS local_time
FROM orders;

The standard practice is storing every timestamp in UTC (TIMESTAMP WITH TIME ZONE, or TIMESTAMPTZ in PostgreSQL) and converting to a local time zone only for display — storing local times directly makes comparisons and arithmetic across records from different zones actively wrong, and daylight saving transitions ambiguous.

Q95. How do you use SQL in financial applications for risk and portfolio analysis?

Window functions are the workhorse here — computing rolling volatility (STDDEV() OVER a trailing window), cumulative returns (running products/sums over time-ordered rows), and drawdown calculations, all directly in SQL against time-series price/position data. Precision matters more than in most domains too: monetary values use DECIMAL/NUMERIC, never FLOAT, to avoid floating-point rounding errors compounding across large aggregations.

Troubleshooting and Debugging

Q96. What steps do you take to troubleshoot a failed SQL query?

• Read the actual error message and line/column it points to first — most syntax errors are exactly where it says
• Isolate the query into its smallest failing piece (comment out joins/conditions one at a time) rather than staring at the whole thing
• Check data assumptions directly — does the table/column actually exist, are the types what you think they are, is there really data matching the filter
• Reproduce against a copy of the real data when the failure is data-dependent, not just a syntax issue

Q97. How can you recover data from a corrupt SQL database?

• Restore from the most recent known-good backup first — this is the actual recovery plan a corruption incident depends on existing
• Use the engine's own repair tooling (e.g. CHECK TABLE/REPAIR TABLE in MySQL, DBCC CHECKDB in SQL Server) for repairable corruption
• Replay transaction logs since the last backup to minimize data loss beyond that point
• Treat this as the reason regular, tested (not just scheduled) backups exist — an untested backup is a hope, not a plan

Q98. What methods do you employ to ensure data integrity?

• Constraints at the schema level — PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK — enforced by the database itself, not just application code
• Transactions around any multi-step write that must succeed or fail together
• Regular validation queries/checks for orphaned rows, impossible values, or drifted denormalized data

Q99. How do you decipher and resolve deadlocks in SQL?

A deadlock happens when two transactions each hold a lock the other is waiting for, so neither can ever proceed — the database detects the cycle and forcibly rolls back one transaction (the "victim") to break it. Reading the engine's deadlock graph/log identifies exactly which statements and rows were involved; the durable fix is almost always accessing tables/rows in a consistent order across every transaction that touches more than one of them, so the circular wait can't form in the first place.

Advanced Data Analysis in SQL

Q100. How do you calculate year-over-year growth or a moving average using window functions?

-- Year-over-year growth, using LAG to look back one period
SELECT year, revenue,
    revenue - LAG(revenue) OVER (ORDER BY year) AS yoy_change,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY year))
        / LAG(revenue) OVER (ORDER BY year), 2
    ) AS yoy_growth_pct
FROM annual_revenue;

-- 3-month moving average, using an explicit frame
SELECT month, revenue,
    AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3mo
FROM monthly_revenue;

LAG() reaches back to a prior row in the same ordered set for period-over-period comparisons, while an explicit frame (ROWS BETWEEN n PRECEDING AND CURRENT ROW) turns AVG()/SUM() into a genuine moving window instead of a running total over everything — between the two, most "trend over time" analytical questions are answerable in a single SQL query, no application-side loop required.

Test yourself

A quick mock test covering the ground above — the rapid-fire round that often opens a SQL interview before the deeper follow-ups.