SQL and Database Transactions: Commands, Joins and ACID

Understand SQL and Database Transactions

Key rules to remember

Basic query structure

SELECT columns FROM table WHERE condition GROUP BY column HAVING condition ORDER BY column

WHERE filters rows before grouping. HAVING filters groups after aggregation.

Insert, update, delete

INSERT INTO t (c1, c2) VALUES (v1, v2); UPDATE t SET c1 = v WHERE condition; DELETE FROM t WHERE condition;

Without WHERE, UPDATE and DELETE affect every row.

Inner join

SELECT … FROM A INNER JOIN B ON A.key = B.key

Returns only rows that match in both tables.

Outer joins

LEFT JOIN: all of A + matches in B; RIGHT JOIN: all of B + matches in A; FULL JOIN: all of both

Unmatched side shows NULL.

ACID

Atomicity, Consistency, Isolation, Durability

Learn each with a one-line meaning and a bank-transfer example.

Transaction control

COMMIT saves changes; ROLLBACK undoes uncommitted changes; SAVEPOINT marks a point to roll back to

These are TCL commands.

Two-phase locking (2PL)

Growing phase: acquire locks only; shrinking phase: release locks only

Guarantees serialisable schedules. It can still cause deadlock.

Serialisability

A concurrent schedule is acceptable if its result equals that of some serial execution

This is the correctness test for concurrency control.

Deadlock

Transactions wait for each other's locks in a cycle

Handled by prevention, detection (wait-for graph) or timeout, then rolling back a victim.

How to solve SQL and Database Transactions questions

1. 1Identify the type of question: define or explain, write a query, compare, or apply to facts.
2. 2For a definition, give the meaning first, then the components or types, then a short practical example.
3. 3For a query, list the tables and the common column. Decide the clause order: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY.
4. 4Choose the join by asking which rows must appear: only matches (INNER) or all rows from one side (LEFT or RIGHT).
5. 5For a transaction scenario, name the ACID property that is at risk and say why.
6. 6For concurrency or recovery, name the problem (lost update, dirty read, deadlock, crash) and then the mechanism that solves it (locks, timestamps, logs, rollback, redo).
7. 7Close with a conclusion tied to the facts, such as data integrity, reliability or security obligations of the company.

Quickest way: Four-line answer pattern

Common mistakes in SQL and Database Transactions

• Confusing DELETE, TRUNCATE and DROP.

  All three seem to remove things.

  Fix: DELETE removes selected rows and is DML. TRUNCATE removes all rows but keeps the table structure. DROP removes the table itself. TRUNCATE and DROP are DDL.

• Using WHERE to filter on aggregate results.

  Students treat WHERE and HAVING as the same.

  Fix: WHERE filters individual rows before grouping. Use HAVING for conditions such as COUNT(*) > 5 or SUM(amount) > 1,00,000.

• Writing an UPDATE or DELETE without WHERE.

  Students focus on the change and forget the condition.

  Fix: Always write the WHERE clause first. State in your answer that omitting it affects all rows.

• Mixing up atomicity and consistency.

  Both relate to a transfer completing correctly.

  Fix: Atomicity is about all steps happening or none. Consistency is about the database obeying its rules and constraints before and after.

• Mixing up isolation and durability.

  Both sound like protection of data.

  Fix: Isolation concerns concurrent transactions not seeing each other's partial work. Durability concerns committed data surviving a failure.

• Saying locking removes deadlock.

  Locking is taught as the solution to concurrency problems.

  Fix: Locking, especially 2PL, ensures serialisability but can create deadlock. Add that deadlock is handled by prevention, detection or timeout and rollback of one transaction.

Worked examples