Transactions & correctness in the SQL assessment

What it covers

Transactions, isolation levels and constraints: keeping data correct when several writes happen at once, and reasoning about a schema's guarantees.

Topics in the practice set for this area

• transactions
• schema reasoning

How it is tested

In the timed assessment, questions in this area use these formats: code you write, multiple choice, short answer. Each attempt draws its own set of questions, and this area gets its own sub-score on the credential when the attempt includes enough questions from it. The assessment questions themselves are never published; the practice set below is written separately for study.

What to practice

• Describe a lost update and a non-repeatable read, and which isolation level prevents each.
• Add primary key, foreign key, unique and check constraints to a small schema and test what each rejects.
• Write a multi-statement change inside a transaction and practise rolling it back.

Try a practice question

From the free practice set (4 questions in this area). Answers and explanations are in the practice set.

• Within a transaction, ROLLBACK does what?
• Which data type is most appropriate for storing a monetary amount in Postgres?
• Adding `ON DELETE CASCADE` to a foreign key means:

More SQL study guides

• How to Pass the Provyn SQL Assessment
• Query construction
• Joins & set logic
• Aggregation & windows
• Performance & indexing