Advanced DBMS, SQL, and PLSQL Interview Questions for Experts

Showing 43 of 43 flashcards

Difficulty: HARD

Type: Other

Compare optimistic and pessimistic locking

Optimistic assumes low conflict and checks at commit - pessimistic locks resources early to avoid conflict

Difficulty: HARD

Type: Other

Difference between RANK - DENSE_RANK - ROW_NUMBER window functions

ROW_NUMBER gives unique numbers - RANK skips on ties - DENSE_RANK does not skip

Difficulty: HARD

Type: Other

Difference between row-level and table-level locking

Row-level allows high concurrency by locking only affected rows - table-level locks entire table

Difficulty: HARD

Type: Other

Difference between sharding and partitioning

Sharding spreads data across databases or servers - partitioning splits it within one database

Difficulty: HARD

Type: Other

Difference between shared and exclusive locks

Shared allows concurrent reads - exclusive allows only one write and blocks others

Difficulty: HARD

Type: Other

Difference between stored procedure and function

Procedure performs actions and may not return value - function returns a result and is often used in queries

Difficulty: HARD

Type: Other

Explain how a FULL OUTER JOIN works and how to emulate it in databases that do not support it

FULL OUTER JOIN returns all rows from both tables with NULLs where no match exists - emulate using LEFT JOIN UNION RIGHT JOIN

Difficulty: HARD

Type: Other

How can wrapping a column in a function in WHERE clause prevent index usage

Functions on columns prevent index usage as they disable direct lookup - rewrite using range conditions

Difficulty: HARD

Type: Other

How can you delete duplicate rows in a SQL table while keeping one instance

Use ROW_NUMBER() OVER(PARTITION BY column_name ORDER BY id) AS rn in CTE and DELETE WHERE rn > 1

Difficulty: HARD

Type: Other

How can you use a recursive CTE to get a manager hierarchy from an Employee table

WITH EmpTree AS (SELECT id - name - manager_id - 1 AS level FROM Employee WHERE manager_id IS NULL UNION ALL SELECT e.id - e.name - e.manager_id - et.level + 1 FROM Employee e JOIN EmpTree et ON e.manager_id = et.id) SELECT * FROM EmpTree

Difficulty: HARD

Type: Other

How do you create a series of numbers from 1 to N using SQL without a numbers table

Use recursive CTE - WITH nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n < N) SELECT n FROM nums

Difficulty: HARD

Type: Other

How do you find all employees who worked on all projects of type X

Use GROUP BY emp_id HAVING COUNT(DISTINCT project_id) = (SELECT COUNT(*) FROM Projects WHERE type = 'X')

Difficulty: HARD

Type: Other

How do you handle errors in a SQL stored procedure

Use TRY CATCH or EXCEPTION blocks to catch and handle errors during procedure execution

Difficulty: HARD

Type: Other

How to handle cross-shard joins

Avoid them using application logic or duplicate lookup tables - else query each shard and merge results

Difficulty: HARD

Type: Other

How to optimize access to recent data in a large log table

Partition by date and prune partitions during queries

Difficulty: HARD

Type: Other

How would you find duplicate values in a table and their counts

SELECT column_name - COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1

Difficulty: HARD

Type: Other

How would you generate a 7-day moving average of sales from a Sales table

SELECT date - AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg FROM Sales

Difficulty: HARD

Type: Other

In a LEFT JOIN - what is the effect of moving a condition from the WHERE clause to the ON clause

Putting a condition on the right table in WHERE may filter out unmatched rows - effectively making it an INNER JOIN - whereas putting it in ON clause preserves unmatched rows with NULLs

Difficulty: HARD

Type: Other

List the four isolation levels and what anomalies they prevent

Read Uncommitted - Read Committed - Repeatable Read - Serializable - each progressively prevents dirty - non-repeatable - phantom reads

Difficulty: HARD

Type: Other

What are the ACID properties of a transaction

Atomicity - Consistency - Isolation - Durability - ensuring reliable and predictable transaction behavior in databases

Difficulty: HARD

Type: Other

What is MVCC and how does it help concurrency

MVCC allows readers to access old versions of rows while writers update - avoiding locks

Difficulty: HARD

Type: Other

What is MVCC in databases and how does it improve concurrency

MVCC allows readers to access consistent snapshots without blocking writers by storing multiple versions of rows - reducing locking and increasing concurrency

Difficulty: HARD

Type: Other

What is a Common Table Expression and how is it different from a subquery or a view

A CTE is a temporary named result set defined using WITH clause - exists only during query execution and improves readability - unlike views which are permanent

Difficulty: HARD

Type: Other

What is a bitmap index and when is it preferred over B-tree

Bitmap is efficient for low-cardinality columns in read-heavy scenarios - not suitable for high-concurrency writes

Difficulty: HARD

Type: Other

What is a composite index and when does it help

A composite index covers multiple columns - effective when queries filter using the leading column

Difficulty: HARD

Type: Other

What is a correlated subquery - and how is it different from a regular subquery

A correlated subquery uses values from the outer query and is re-evaluated for each row - unlike regular subqueries that are self-contained

Difficulty: HARD

Type: Other

What is a covering index and how can it improve query performance

A covering index includes all columns needed by a query - allowing it to be satisfied using only the index without accessing the base table

Difficulty: HARD

Type: Other

What is a cursor and why is it discouraged

A cursor processes rows one by one - slower than set-based SQL operations

Difficulty: HARD

Type: Other

What is a database deadlock and how is it handled

When two transactions wait on each other's locks - system aborts one to break the cycle

Difficulty: HARD

Type: Other

What is a database deadlock and how is it resolved

A deadlock occurs when two or more transactions wait on each other - database resolves it by aborting one transaction to break the cycle

Difficulty: HARD

Type: Other

What is a database deadlock and how is it resolved

A deadlock occurs when two or more transactions wait on each other - database resolves it by aborting one transaction to break the cycle

Difficulty: HARD

Type: Other

What is a database trigger and one drawback

A trigger executes automatically on data changes - can add hidden complexity and performance cost

Difficulty: HARD

Type: Other

What is a semi join and how can it be implemented in SQL

A semi join returns rows from A where a match exists in B - implemented using EXISTS or IN

Difficulty: HARD

Type: Other

What is an execution plan and how do you use it to optimize queries

It shows how the database runs the query including index usage - scans - sorts - helps identify bottlenecks

Difficulty: HARD

Type: Other

What is index selectivity and why does it matter

It is the ratio of distinct values to total rows - high selectivity means better performance - low selectivity may lead to full scans

Difficulty: HARD

Type: Other

What is the difference between UNION and UNION ALL in SQL

UNION removes duplicates while UNION ALL includes all results including duplicates - UNION ALL is faster as it skips duplicate checks

Difficulty: HARD

Type: Other

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

Clustered index defines physical order of table rows - non-clustered is a separate lookup structure

Difficulty: HARD

Type: Other

What is the downside of too many indexes on a table

Each index slows down writes - consumes space - and can confuse the optimizer

Difficulty: HARD

Type: Other

Why is SELECT * discouraged in performance-sensitive environments

SELECT * fetches unnecessary columns - prevents index-only scans and increases I O

Difficulty: HARD

Type: Other

Will indexing a low-cardinality column like a status flag help

Usually no - unless combined with other columns - because of low selectivity

Difficulty: HARD

Type: Other

Write a SQL query to find departments whose average salary is higher than the overall average salary of the company

SELECT dept_id FROM Employee GROUP BY dept_id HAVING AVG(salary) > (SELECT AVG(salary) FROM Employee)

Difficulty: HARD

Type: Other

Write a SQL query to retrieve the top 3 highest salaries in each department from Employee table

SELECT id - name - salary - dept_id FROM (SELECT id - name - salary - dept_id - ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rn FROM Employee) AS sub WHERE rn <= 3

Difficulty: HARD

Type: Other

Write a query to compute 7-day moving average of sales in SQL

SELECT date - AVG(sales) OVER(ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS mov_avg FROM Sales

We use cookies to improve your experience. By clicking “Accept” you consent to the use of cookies. Read our Privacy Policy.