W5 - SQL Finish + Transactions & Concurrency Control

Nested SQL queries

Nesting a query in another query

SQL Operators

• Use IN and NOT IN

• EXISTS returns TRUE if the set is nonempty (NOT IN does the opposite)

• UNIQUE can be used, but not for checking tuple-by-tuple, only for an entire subquery. (Can also use NOT UNIQUE)

• ANY and ALL

  • ANY returns true if there exist tuples which obey condition

  • ALL returns true if all tuples obey condition

SQL Aggregate Operators

COUNT (*) - Counts everything returned

COUNT ( [DISTINCT] A ) - Counts set

AVG ( [DISTINCT] A) - Returns average of a set

SUM ( [DISTINCT] A) - Finds sum of set

MAX (A) - Finds highest value

MIN (A) - Finds smallest value

GROUP BY, HAVING

GROUP BY sorts a set by an attribute in ascending order

HAVING further sorts a set by a value of that attribute

What counts as a transaction?

Each movement of money

• If PLAYER1 gives PLAYER2 100, that is one transaction

  • PLAYER1 bal -100
    PLAYER2 bal +100

  • /\ One transaction

Concurrency

Interleaving execution of operations such as money transfer and account sum

• Each transaction is seen as a sequence of read and write

Schedules

List of actions from a set of transactions

• No guarantee that T1 will execute before T2 if both are submitted concurrently

Types of schedules

Complete - A schedule that contains an abort or commit action for every transaction that occurs in the schedule

Serial - A schedule where the actions of different transactions are not interleaved

Conflicting Operations

Two operations conflicting if:

• They belong to different transactions

• They operate on same data item

• At least one of them is a write operation