Here are 100 SQL interview questions and answers, covering foundational concepts, queries, joins, subqueries, indexes, normalization, stored procedures, performance, and database design. Each question is in bold, followed by a detailed answer. No dividing lines.
What is SQL and what are its main components?
Answer: SQL (Structured Query Language) is a standard language for managing and querying relational databases. Main components: 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).
What is the difference between DELETE, TRUNCATE, and DROP?
Answer: DELETE removes rows one by one, can have a WHERE clause, triggers fire, and can be rolled back. TRUNCATE removes all rows instantly, resets identity/auto-increment, is minimally logged, cannot have WHERE, and cannot be rolled back in some databases. DROP removes the entire table (structure and data). TRUNCATE is faster than DELETE without conditions.
What is a primary key?
Answer: A primary key is a column (or set of columns) that uniquely identifies each row in a table. It must contain unique, non-null values, and each table can have only one primary key. It automatically creates a clustered index (in most databases).
What is a foreign key?
Answer: A foreign key is a column (or set of columns) that references the primary key or a unique key in another table. It enforces referential integrity: a value in the foreign key must exist in the referenced table.
What is the difference between UNIQUE and PRIMARY KEY?
Answer: Both enforce uniqueness. A primary key cannot have NULL values; a unique constraint can have one NULL value (depending on the database). A table can have only one primary key but multiple unique constraints.
What is a composite key?
Answer: A composite key is a primary or foreign key that consists of two or more columns. Example: PRIMARY KEY (order_id, product_id).
What is an index and why is it used?
Answer: An index is a database structure (usually B-tree or hash) that improves the speed of data retrieval (SELECT queries). It is created on columns that are frequently searched, joined, or sorted. Indexes slow down INSERT, UPDATE, DELETE operations because they must also be updated.
What are clustered and non-clustered indexes?
Answer: A clustered index determines the physical order of data rows in a table. A table can have only one clustered index (usually the primary key). Non-clustered indexes are separate structures that point to the data rows; a table can have many non-clustered indexes. Clustered index is faster for range queries.
What is normalization? Name its normal forms.
Answer: Normalization is the process of organizing data to reduce redundancy and improve integrity. Normal forms: 1NF (atomic values, no repeating groups), 2NF (1NF + no partial dependency on composite key), 3NF (2NF + no transitive dependency), BCNF (stronger 3NF), 4NF, 5NF.
What is denormalization and why might you use it?
Answer: Denormalization intentionally adds redundancy to a database (e.g., adding a derived column) to improve read performance. Used in data warehouses, reporting systems, or highly read-intensive applications where joins are expensive.
What is the difference between INNER JOIN and LEFT JOIN?
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 table; if no match, the right table columns are NULL. There are also RIGHT JOIN and FULL OUTER JOIN.
What is a self-join? Give an example.
Answer: A self-join is when a table is joined with itself. It requires using aliases. Example: finding employees and their managers from the same employee table: SELECT e.name, m.name FROM employees e LEFT JOIN employees m ON e.manager_id = m.id.
What is a cross join?
Answer: A cross join (Cartesian product) returns the combination of every row from the first table with every row from the second table. No join condition. Useful for generating all combinations, but can produce huge result sets.
What is the difference between WHERE and HAVING?
Answer: WHERE filters rows before grouping. HAVING filters groups after GROUP BY. HAVING is used with aggregate functions (e.g., HAVING COUNT(*) > 5). WHERE cannot contain aggregate functions.
What is the order of execution of a SQL query?
Answer: Logical order: FROM (including JOINs) → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT/OFFSET. The database may reorder for performance, but this is the conceptual order.
What are aggregate functions? Give examples.
Answer: Aggregate functions perform a calculation on a set of values and return a single value. Examples: COUNT(), SUM(), AVG(), MAX(), MIN(), GROUP_CONCAT() (MySQL), STRING_AGG() (SQL Server, PostgreSQL).
What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?
Answer: COUNT(*) counts all rows, including those with NULLs. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts distinct non-NULL values.
What is a subquery? Give an example.
Answer: A subquery is a query nested inside another query. It can be used in SELECT, FROM, WHERE, or HAVING clauses. Example: SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales').
What is a correlated subquery?
Answer: A correlated subquery references a column from the outer query and is executed once for each row processed by the outer query. It is less efficient than a join. Example: SELECT e.name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id).
What is the difference between IN and EXISTS?
Answer: IN compares a value to a list (or subquery result). EXISTS checks for existence of at least one row in a subquery. EXISTS often performs better for large subqueries because it stops processing after the first match, while IN may evaluate all results. IN can work with constant lists.
What is a UNION vs UNION ALL?
Answer: UNION combines results from two or more queries and removes duplicate rows (requires a distinct sort). UNION ALL includes all rows, including duplicates, and is faster. Column count and data types must match.
What is a view? Why use it?
Answer: A view is a virtual table based on a stored query. It does not store data physically (unless materialized). Uses: simplify complex queries, restrict access to specific columns/rows, and present consistent data abstraction. Some views are updatable.
What is an indexed view (materialized view)?
Answer: An indexed view (materialized view in Oracle/PostgreSQL) stores the query result physically on disk, improving read performance. Requires maintenance (refreshing). Useful for data warehousing.
What is a stored procedure?
Answer: A stored procedure is precompiled SQL code saved in the database that performs one or more operations. It can accept parameters, contain procedural logic (IF, loops), and return result sets. Improves performance (compiled once) and reusability.
What is a function in SQL? How is it different from a stored procedure?
Answer: A function returns a single value (scalar) or a table. It cannot modify database state (no INSERT/UPDATE/DELETE). A stored procedure can modify data, does not require a return value, and is called with CALL or EXEC. Functions are used in expressions (e.g., SELECT myFunction(col)).
What is a trigger?
Answer: A trigger is a stored procedure that automatically executes in response to an event (INSERT, UPDATE, DELETE) on a specific table. Used for auditing, enforcing complex business rules, or updating derived columns.
What is a cursor? When would you use it?
Answer: A cursor allows row-by-row processing of a result set. Use when you need to iterate over rows and perform operations not possible with set-based SQL. Cursors are slower than set‑based operations; avoid them when possible.
What are ACID properties?
Answer: Atomicity (transaction is all or nothing), Consistency (transaction brings database from one valid state to another), Isolation (concurrent transactions do not interfere), Durability (committed changes persist after system failure).
What are transaction isolation levels?
Answer: Read Uncommitted (dirty reads allowed), Read Committed (no dirty reads, but non-repeatable reads possible), Repeatable Read (no dirty or non-repeatable reads, but phantom reads possible), Serializable (highest isolation, complete isolation). Each level trades off consistency for performance.
What is a dirty read?
Answer: A dirty read occurs when a transaction reads data that has been written by another uncommitted transaction. If the other transaction rolls back, the read data is invalid. Prevented by isolation levels Read Committed and above.
What is a deadlock and how can you prevent it?
Answer: A deadlock occurs when two transactions each hold a lock and wait for the other to release a lock. Prevent by: accessing resources in the same order, using shorter transactions, using row-level locking, and using deadlock detection (database kills one transaction).
What is the difference between CHAR and VARCHAR?
Answer: CHAR(n) is fixed-length, always stores n characters (padding with spaces). VARCHAR(n) is variable-length, stores only the actual characters plus overhead. VARCHAR saves space; CHAR can be faster for fixed-length data (e.g., country codes, hashes).
What is the difference between DATETIME and TIMESTAMP?
Answer: DATETIME stores a date and time without timezone. TIMESTAMP (in MySQL) stores a value that converts to UTC on storage and back to current timezone on retrieval. TIMESTAMP has a smaller range (1970-2038) and updates automatically. In PostgreSQL, TIMESTAMP without time zone is like DATETIME.
What is the LIKE operator? Give examples of wildcards.
Answer: LIKE is used for pattern matching in strings. Wildcards: % matches any sequence of characters, _ matches any single character. Example: WHERE name LIKE 'J%' returns names starting with J.
What is the difference between BETWEEN and IN?
Answer: BETWEEN tests for a range (inclusive) on a single column: WHERE age BETWEEN 18 AND 65. IN tests for membership in a discrete list: WHERE status IN ('active', 'pending').
What is a window function? Give an example.
Answer: Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output row. Example: ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC). Other functions: RANK(), DENSE_RANK(), LEAD(), LAG(), SUM() OVER().
What is a common table expression (CTE)?
Answer: A CTE is a temporary result set defined using WITH clause, used within a single SQL statement. Improves readability and allows recursion. Example:
sql
WITH dept_avg AS ( SELECT department_id, AVG(salary) as avg_salary FROM employees GROUP BY department_id ) SELECT e.name, e.salary, d.avg_salary FROM employees e JOIN dept_avg d ON e.department_id = d.department_id;
What is a recursive CTE?
Answer: A recursive CTE references itself, used for hierarchical queries (e.g., organizational charts, tree structures). Syntax: WITH RECURSIVE cte AS (base query UNION ALL recursive query). Must have termination condition.
What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?
Answer: ROW_NUMBER() assigns a unique sequential integer (1,2,3,4). RANK() assigns the same rank for ties but leaves gaps (1,2,2,4). DENSE_RANK() assigns same rank for ties without gaps (1,2,2,3).
How do you find duplicate rows in a table?
Answer: Use GROUP BY and HAVING COUNT(*) > 1. Example:
sql
SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 HAVING COUNT(*) > 1;
To delete duplicates, keep one row using ROW_NUMBER().
How do you delete duplicate rows while keeping one?
Answer: Using a CTE with ROW_NUMBER():
sql
WITH cte AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY id) as rn FROM table ) DELETE FROM cte WHERE rn > 1;
What is the difference between VARCHAR and TEXT?
Answer: VARCHAR(n) has a maximum length (e.g., 255, 65535). TEXT (or CLOB) has no explicit length limit (up to database limits). VARCHAR is often stored inline, TEXT may be stored off-page. Indexing limitations differ.
What is the COALESCE function?
Answer: COALESCE returns the first non-NULL value from the list. Example: SELECT COALESCE(phone, mobile, 'No contact') FROM contacts;. Useful for providing default values.
What is the NULLIF function?
Answer: NULLIF(expression1, expression2) returns NULL if the two expressions are equal, otherwise returns expression1. Example: SELECT NULLIF(divisor, 0) prevents division by zero.
How do you perform a pivot in SQL?
Answer: Use conditional aggregation with CASE and an aggregate function. Example:
sql
SELECT year, SUM(CASE WHEN quarter = 1 THEN revenue ELSE 0 END) as Q1, SUM(CASE WHEN quarter = 2 THEN revenue ELSE 0 END) as Q2 FROM sales GROUP BY year;
Some databases have PIVOT keyword.
What is an execution plan?
Answer: An execution plan shows how the database optimizer would execute a query (table access order, join methods, index usage). Use EXPLAIN (MySQL, PostgreSQL) or SET SHOWPLAN_XML (SQL Server) to view it. Helps identify slow queries.
How do you optimize a slow query?
Answer: Analyze execution plan. Add missing indexes, avoid SELECT *, use appropriate joins, filter with WHERE early, avoid functions on indexed columns, use EXISTS instead of IN for correlated subqueries, normalize or denormalize as needed, and rewrite suboptimal logic.
What is the difference between INNER JOIN and OUTER JOIN performance?
Answer: INNER JOIN can be faster because it discards non-matching rows early. OUTER JOIN (especially FULL OUTER) may need to handle NULLs and preserve unmatched rows. Indexes help both.
What is a covering index?
Answer: A covering index includes all columns needed for a query, so the database can satisfy the query entirely from the index without accessing the table (index-only scan). This improves performance significantly.
What is a foreign key constraint’s benefit beyond referential integrity?
Answer: It can improve query optimizer’s decisions (e.g., joins between parent-child). It also enables cascading updates/deletes (ON DELETE CASCADE).
How do you handle hierarchical data in SQL?
Answer: Use recursive CTEs (PostgreSQL, SQL Server, MySQL 8+), or adjacency list with self-join, or materialized path (string), or nested set model. Recursive CTE is the most common modern approach.
What are the differences between MySQL, PostgreSQL, and SQL Server?
Answer: MySQL is popular for web applications, easy replication, but weaker compliance. PostgreSQL is advanced, standards-compliant, with JSON support, ACID, and extensibility. SQL Server is Microsoft’s RDBMS, integrates deeply with .NET, SSRS, SSIS. Differences also in syntax (e.g., LIMIT vs TOP).
What is AUTO_INCREMENT (or SERIAL, IDENTITY)?
Answer: It generates unique sequential numbers automatically for a column, often used for primary keys. In MySQL: AUTO_INCREMENT, PostgreSQL: SERIAL or IDENTITY, SQL Server: IDENTITY(1,1).
What is the difference between WHERE and ON in a join?
Answer: ON specifies the join condition before the join is performed. WHERE filters rows after the join. For INNER JOIN, they are equivalent. For OUTER JOIN, ON affects which rows are joined, WHERE affects final result after join (and can convert OUTER to INNER).
What is a CROSS APPLY (SQL Server) or LATERAL (PostgreSQL)?
Answer: CROSS APPLY (or LATERAL JOIN) evaluates a right-side subquery for each row of the left side, allowing correlation. Useful for functions or subqueries that reference left table columns.
How do you get the second highest salary from an employees table?
Answer: Using OFFSET or LIMIT:
sql
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
Or subquery:
sql
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
What is the difference between UNPIVOT and PIVOT?
Answer: PIVOT converts rows to columns (wide format). UNPIVOT converts columns to rows (long format). Supported in SQL Server, Oracle, and other databases via functions or CASE expressions.
What is a schema in a database?
Answer: A schema is a logical container for database objects (tables, views, stored procedures). It provides namespace separation and access control. In MySQL, schema is synonymous with database. In PostgreSQL, a database can have multiple schemas.
What are the advantages of using database views?
Answer: Security (hide columns/rows), simplify complex queries, ensure consistent logic, and provide a level of abstraction. Views can also be materialized for performance.
What is a database lock?
Answer: A lock prevents concurrent access to data to maintain integrity. Shared locks (read) and exclusive locks (write). Lock levels: row, page, table, database. Deadlocks occur when two transactions hold locks needed by each other.
How do you avoid implicit conversion that harms index usage?
Answer: Use the correct data type in comparisons. For example, if a column is VARCHAR, compare with string literal, not integer. Implicit conversion (e.g., comparing VARCHAR and INT) may prevent index usage.
What are statistics in a database?
Answer: Statistics are metadata about data distribution (histograms) used by the query optimizer to estimate cardinality and choose optimal execution plans. Outdated statistics cause poor performance. Update them periodically.
What is a heap table?
Answer: A heap table has no clustered index; rows are stored in no particular order. Inserts are faster, but range queries slower. Common in MySQL with InnoDB (clustered by primary key) or tables without primary key.
What is the difference between RANGE and ROWS in window functions?
Answer: ROWS defines the window based on physical row count relative to current row. RANGE defines based on logical range of values. Example: ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING includes previous, current, next row. RANGE would include rows with same value.
What is an EXPLAIN ANALYZE (PostgreSQL)?
Answer: EXPLAIN ANALYZE executes the query and returns the actual execution plan with runtime statistics (actual vs estimated row counts). Helps identify plan misestimates.
How do you copy a table structure without copying data?
Answer: CREATE TABLE new_table LIKE original_table; (MySQL). CREATE TABLE new_table AS SELECT * FROM original_table WHERE 1=0; (generic, but may copy constraints differently). In PostgreSQL: CREATE TABLE new_table (LIKE original_table INCLUDING ALL);
What is the difference between CAST and CONVERT?
Answer: CAST is ANSI standard: CAST(column AS new_type). CONVERT is database-specific (SQL Server: CONVERT(new_type, column, style)). Use CAST for portability.
What is a surrogate key vs natural key?
Answer: Surrogate key is an artificially generated unique identifier (e.g., auto‑increment ID). Natural key is a column that already uniquely identifies a row (e.g., SSN, ISBN). Surrogate keys are preferred for joins (stable, simple, no business meaning).
How do you generate a sequential number in a query without a table?
Answer: Use a WITH RECURSIVE CTE or a series-generating function: GENERATE_SERIES(1,100) in PostgreSQL, WITH RECURSIVE cte(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM cte WHERE n<100). In MySQL 8+: WITH RECURSIVE seq (n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n<100 ).
What are the different types of table joins?
Answer: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN, SELF JOIN, NATURAL JOIN (not recommended), and SEMI JOIN (using IN or EXISTS).
What is the difference between a temporary table and a table variable (SQL Server)?
Answer: Temporary tables (#temp) are physical tables stored in tempdb, can have indexes, persist for session. Table variables (@table) are in memory, have limited scope, no statistics, smaller overhead. Choose based on row count and need for indexes.
What is a MERGE statement?
Answer: MERGE (also called upsert) performs INSERT, UPDATE, DELETE on a target table based on a source table. Example: synchronizing tables. Not all databases support it; use INSERT ... ON DUPLICATE KEY UPDATE (MySQL) or INSERT ... ON CONFLICT (PostgreSQL).
What is the difference between BETWEEN and >= and <=?
Answer: They are equivalent for numeric and date types. BETWEEN is inclusive. Some databases treat BETWEEN with date times edge cases differently; otherwise, no performance difference.
What is a cardinality estimate?
Answer: The optimizer’s guess of how many rows will match a condition. Accurate cardinality estimates are essential for good execution plans. Outdated statistics cause wrong estimates.
What is a covering index?
Answer: (Already covered earlier) An index that contains all columns needed for a query, enabling index‑only scan.
How do you find tables without primary keys?
Answer: Query information schema:
sql
SELECT table_name FROM information_schema.tables t WHERE table_schema = 'public' AND NOT EXISTS ( SELECT 1 FROM information_schema.table_constraints WHERE constraint_type = 'PRIMARY KEY' AND table_name = t.table_name );
What is the difference between UNION and JOIN?
Answer: UNION combines rows vertically (from two or more result sets). JOIN combines columns horizontally (based on related key columns). Different purposes.
What is a pivot table in SQL?
Answer: A pivot table transforms data from rows to columns (aggregation). Implemented with conditional aggregation (CASE) or PIVOT keyword.
What is the difference between CHARINDEX and PATINDEX (SQL Server)?
Answer: CHARINDEX(substring, string) finds position of a substring. PATINDEX('%pattern%', string) uses wildcards. In MySQL, LOCATE and INSTR.
What is a database transaction?
Answer: A transaction is a logical unit of work that consists of one or more SQL statements, treated atomically. Begins with BEGIN TRANSACTION (implicit start), ends with COMMIT or ROLLBACK.
How do you implement row-level security?
Answer: Using views that filter rows based on user context, or database‑specific row-level security (PostgreSQL RLS, SQL Server RLS, Oracle VPD). Policies can restrict access by user role or session variable.
What is a function‑based index?
Answer: An index based on an expression or function, not just column values. Example: CREATE INDEX idx_lower_name ON users(LOWER(name)). Useful for case‑insensitive searches.
What is the difference between SQL and NoSQL?
Answer: SQL databases are relational, structured schema, ACID, support joins. NoSQL databases are non-relational (document, key-value, graph, column-family), schema-flexible, often prioritize scalability and availability over consistency.
What is a lateral join?
Answer: A lateral join allows a subquery in the FROM clause to reference columns from preceding tables. Similar to CROSS APPLY in SQL Server. Example: SELECT * FROM employees e, LATERAL (SELECT * FROM tasks WHERE employee_id = e.id ORDER BY due_date LIMIT 3) t.
What is the difference between EXCEPT and NOT IN?
Answer: EXCEPT (or MINUS in Oracle) returns distinct rows from the first query that are not in the second. NOT IN also returns rows not in a list, but behaves differently with NULLs (if subquery returns NULL, NOT IN returns empty). Use NOT EXISTS for safety.
What is the purpose of the INTERSECT operator?
Answer: INTERSECT returns distinct rows that are common to two queries. Example: (SELECT id FROM orders) INTERSECT (SELECT id FROM returns).
What are bridge tables (associative tables)?
Answer: Bridge tables resolve many-to-many relationships. They contain foreign keys to the two related tables. Example: Student_Courses with student_id and course_id.
What is a check constraint?
Answer: A check constraint validates that column values meet a specified condition (e.g., CHECK (age >= 18)). It prevents invalid data insertion.
What is a default constraint?
Answer: A default constraint provides a default value for a column when no value is supplied in an INSERT. Example: ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending'.
How do you rename a column in SQL?
Answer: MySQL: ALTER TABLE table CHANGE old_name new_name datatype; or RENAME COLUMN. PostgreSQL: ALTER TABLE table RENAME COLUMN old TO new;. SQL Server: EXEC sp_rename 'table.old', 'new', 'COLUMN';.
What is the difference between TRUNCATE and DELETE in terms of transaction log?
Answer: DELETE logs each row deletion, making it slower but allowing rollback. TRUNCATE logs only deallocation of pages, uses less log space, and cannot be rolled back (in most databases without explicit transaction wrapping).
What is a schema binding (in views/functions) and why use it?
Answer: Schema binding prevents underlying tables from being altered or dropped in a way that would break the view/function. It improves performance because the database can resolve objects earlier. Example: CREATE VIEW v WITH SCHEMABINDING AS ....
How do you identify missing indexes?
Answer: Query dynamic management views (SQL Server: sys.dm_db_missing_index_details), use pg_stat_statements in PostgreSQL, or performance_schema in MySQL. Also analyze execution plans for table scans.
What is an index hint?
Answer: An index hint forces the optimizer to use a specific index. Syntax varies: SELECT /*+ INDEX(employees idx_salary) */ ... (Oracle), SELECT ... USE INDEX (idx_salary) (MySQL). Use hints as a last resort.
What is the difference between HAVING and WHERE with aggregate?
Answer: WHERE filters rows before grouping, so aggregates cannot be used. HAVING filters after grouping, so aggregates can be used.
What is the WITH TIES clause?
Answer: WITH TIES (used with ORDER BY and FETCH) returns additional rows that tie with the last row in the result set. Example: SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS WITH TIES;
How do you create an index on a large table without blocking writes?
Answer: Use CREATE INDEX CONCURRENTLY in PostgreSQL, or online index operations in SQL Server (ONLINE=ON), or MySQL with ALGORITHM=INPLACE. These allow concurrent DML but take longer.
What are the advantages of using EXISTS over IN?
Answer: EXISTS can stop processing after the first matching row, while IN may evaluate all results. EXISTS handles NULL values correctly (subquery returning NULL still works). Often faster for large subqueries.
What is a seek vs a scan?
Answer: A seek uses an index to efficiently locate rows (fewer I/O operations). A scan reads entire table or index (sequential). Seeks are preferred for selective queries. Scans are necessary for retrieving most rows.
What is the difference between SERIALIZABLE and REPEATABLE READ?
Answer: REPEATABLE READ prevents dirty reads and non‑repeatable reads, but phantom reads (new rows inserted by other transactions) may appear. SERIALIZABLE prevents phantoms by locking or using range locks, but reduces concurrency.
What is the purpose of the WITH (NOLOCK) hint (SQL Server)?
Answer: NOLOCK applies a dirty read (Read Uncommitted isolation) to that specific table, allowing reads without acquiring shared locks. It can return uncommitted data and cause inconsistencies; use only for reporting where consistency is not critical.
What is a partial index?
Answer: A partial index (filtered index) includes only rows that satisfy a condition. Example: CREATE INDEX idx_active ON users(email) WHERE active = true. Reduces index size and improves performance for specific queries.
What is a function‑based index?
Answer: (Already covered) Index on expression.
How does GROUP BY work with ROLLUP and CUBE?
Answer: ROLLUP produces subtotals and a grand total for hierarchical grouping (e.g., year → quarter). CUBE produces all possible combinations of grouping columns. Useful for reporting.
What is the difference between WHERE and QUALIFY (in some databases like Snowflake)?
Answer: QUALIFY filters the results of window functions after they are computed, unlike WHERE which cannot reference window functions. Example: SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary) rn FROM employees QUALIFY rn = 1.
What is an implicit transaction?
Answer: In some databases (Oracle), every SQL statement is an implicit transaction unless explicitly started. In others (SQL Server with IMPLICIT_TRANSACTIONS ON), a transaction is automatically started.
How do you handle large result sets efficiently?
Answer: Use pagination (LIMIT OFFSET or keyset pagination), use server‑side cursors, or fetch in batches. Avoid SELECT * ; only retrieve needed columns. For reporting, consider materialized views or data warehousing.
Why should we hire you as a SQL developer?
Answer: I have strong knowledge of relational theory, efficient query writing, indexing strategies, and performance tuning. I can design normalized schemas, write complex joins and subqueries, and optimize slow queries. I also understand transaction isolation, database administration tasks (backup, restore), and can work across multiple RDBMS (MySQL, PostgreSQL, SQL Server). I focus on data integrity and maintainability.