Showing 43 of 43 flashcards
Difficulty: HARD
Type: Other
Optimistic assumes low conflict and checks at commit - pessimistic locks resources early to avoid conflict
Difficulty: HARD
Type: Other
ROW_NUMBER gives unique numbers - RANK skips on ties - DENSE_RANK does not skip
Difficulty: HARD
Type: Other
Row-level allows high concurrency by locking only affected rows - table-level locks entire table
Difficulty: HARD
Type: Other
Sharding spreads data across databases or servers - partitioning splits it within one database
Difficulty: HARD
Type: Other
Shared allows concurrent reads - exclusive allows only one write and blocks others
Difficulty: HARD
Type: Other
Procedure performs actions and may not return value - function returns a result and is often used in queries
Difficulty: HARD
Type: Other
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
Functions on columns prevent index usage as they disable direct lookup - rewrite using range conditions
Difficulty: HARD
Type: Other
Use ROW_NUMBER() OVER(PARTITION BY column_name ORDER BY id) AS rn in CTE and DELETE WHERE rn > 1
Difficulty: HARD
Type: Other
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
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
Use GROUP BY emp_id HAVING COUNT(DISTINCT project_id) = (SELECT COUNT(*) FROM Projects WHERE type = 'X')
Difficulty: HARD
Type: Other
Use TRY CATCH or EXCEPTION blocks to catch and handle errors during procedure execution
Difficulty: HARD
Type: Other
Avoid them using application logic or duplicate lookup tables - else query each shard and merge results
Difficulty: HARD
Type: Other
Partition by date and prune partitions during queries
Difficulty: HARD
Type: Other
SELECT column_name - COUNT(*) FROM table_name GROUP BY column_name HAVING COUNT(*) > 1
Difficulty: HARD
Type: Other
SELECT date - AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg FROM Sales
Difficulty: HARD
Type: Other
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
Read Uncommitted - Read Committed - Repeatable Read - Serializable - each progressively prevents dirty - non-repeatable - phantom reads
Difficulty: HARD
Type: Other
Atomicity - Consistency - Isolation - Durability - ensuring reliable and predictable transaction behavior in databases
Difficulty: HARD
Type: Other
MVCC allows readers to access old versions of rows while writers update - avoiding locks
Difficulty: HARD
Type: Other
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
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
Bitmap is efficient for low-cardinality columns in read-heavy scenarios - not suitable for high-concurrency writes
Difficulty: HARD
Type: Other
A composite index covers multiple columns - effective when queries filter using the leading column
Difficulty: HARD
Type: Other
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
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
A cursor processes rows one by one - slower than set-based SQL operations
Difficulty: HARD
Type: Other
When two transactions wait on each other's locks - system aborts one to break the cycle
Difficulty: HARD
Type: Other
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
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
A trigger executes automatically on data changes - can add hidden complexity and performance cost
Difficulty: HARD
Type: Other
A semi join returns rows from A where a match exists in B - implemented using EXISTS or IN
Difficulty: HARD
Type: Other
It shows how the database runs the query including index usage - scans - sorts - helps identify bottlenecks
Difficulty: HARD
Type: Other
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
UNION removes duplicates while UNION ALL includes all results including duplicates - UNION ALL is faster as it skips duplicate checks
Difficulty: HARD
Type: Other
Clustered index defines physical order of table rows - non-clustered is a separate lookup structure
Difficulty: HARD
Type: Other
Each index slows down writes - consumes space - and can confuse the optimizer
Difficulty: HARD
Type: Other
SELECT * fetches unnecessary columns - prevents index-only scans and increases I O
Difficulty: HARD
Type: Other
Usually no - unless combined with other columns - because of low selectivity
Difficulty: HARD
Type: Other
SELECT dept_id FROM Employee GROUP BY dept_id HAVING AVG(salary) > (SELECT AVG(salary) FROM Employee)
Difficulty: HARD
Type: Other
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
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.