Explain ACID
ACID stands for atomicity, consistency, isolation, and durability.
- Atomicity means all or nothing
- Isolation controls concurrency visibility
- Durability survives crashes after commit
Explain ACID
Tagged with sql
13 cards
Explain SQL joins
Joins combine rows from tables based on a related key, with inner and outer joins deciding what unmatched rows survive.
- INNER keeps matches
- LEFT keeps all left rows
- Join condition matters
Explain SQL joins
HAVING vs WHERE in SQL
WHERE filters rows before grouping, while HAVING filters groups after aggregation.
- WHERE happens earlier
- HAVING works on grouped results
- Common with aggregate conditions
HAVING vs WHERE in SQL
Primary key vs foreign key
A primary key uniquely identifies a row in its own table, while a foreign key points to a related row in another table.
- Primary key is table identity
- Foreign key creates relationship
- Helps enforce referential integrity
Primary key vs foreign key
Subquery vs join
Joins combine tables directly, while subqueries nest one query inside another; either can be correct depending on clarity and plan quality.
- Joins are often easier to optimize
- Subqueries can express stepwise logic
- Check the execution plan
Subquery vs join
What are common SQL join types and when do you use them?
Inner joins return matches, outer joins preserve unmatched rows from one or both sides depending on the type.
- INNER for only matches
- LEFT when you need all left rows
- FULL when you need everything from both sides
What are common SQL join types and when do you use them?
What are transactions?
Transactions group multiple statements into one atomic unit so partial updates do not leak through on failure.
- Begin commit rollback
- Protects consistency
- Works with isolation levels
What are transactions?
What are window functions?
Window functions compute values across a related set of rows without collapsing them into one row per group.
- Use OVER clause
- Great for ranking and running totals
- Keeps detail rows
What are window functions?
What does GROUP BY do?
GROUP BY collects rows by one or more columns so aggregate functions can run per group.
- Works with count sum avg
- Every selected non-aggregate column must be grouped
- HAVING filters groups
What does GROUP BY do?
What is an execution plan?
An execution plan shows how the database intends to run a query, including scans, joins, sorts, and index usage.
- Look for full scans
- Check join strategy
- Used to explain slow queries
What is an execution plan?