SQL and MySQL course Β· Module 11: Capstone project

The Hall of Seals - transactions and views

2 min read Β·
In this lesson2

In the finale you will learn two tools of a master: transactions that protect data, and views that bring order to queries.

Transaction - all or nothing

A transaction joins several operations into one indivisible whole. Either all of them run, or none. We begin it with START TRANSACTION, confirm with COMMIT, and in case of an error undo with ROLLBACK:

START TRANSACTION;
UPDATE ksiazki SET cena = 25.00 WHERE id = 3;
COMMIT;

If something went wrong before COMMIT, a simple ROLLBACK returns the database to the state before the transaction. Thanks to this we never leave data in a half-finished, broken state.

View (VIEW) - a saved query

A view is a saved query that behaves like a virtual table. We create it once, then query it like an ordinary shelf:

CREATE VIEW dostepne_ksiazki AS
SELECT tytul, cena FROM ksiazki WHERE dostepna = 1;

From now on SELECT * FROM dostepne_ksiazki; will return only available books - without repeating the whole condition. Views bring order to frequently used questions, just as the Pinakes brought order to the scrolls.

These are your last new spells. The seal is pressed - the data is safe!

Spotted a mistake in this lesson?

Check yourself

Answer the questions from this lesson. Pick an answer to see right away whether it is correct.

  1. 1. What does a transaction in a database guarantee?

  2. 2. Which statement permanently confirms the changes made in a transaction?

These are 2 of 3 questions for this lesson. Solve the rest in the game.

Hands-on tasks in the game

  • Code editor

    Write three statements: start a transaction with START TRANSACTION;, then set cena to 25.00 for the book with id 3 (UPDATE ksiazki SET cena = 25.00 WHERE id = 3;), and finally confirm the changes with COMMIT;.

  • Code editor

    Write a CREATE VIEW statement that creates the dostepne_ksiazki view, which retrieves tytul and cena from the ksiazki table for available books (dostepna = 1).

  • Click in order

    Click the statements in the order of running a safe transaction:

  • Horizontal ordering

    Arrange in one line the beginning of the statement that creates a view:

  1. QuestionWhat a transaction guarantees
  2. QuestionCommitting a transaction
  3. Coding taskA safe change inside a transaction
  4. Coding taskCreate a view of available books
  5. QuestionWhat a view is
  6. Click in orderThe order of a safe transaction
  7. Put in orderThe start of the dostepne_ksiazki view

Useful articles