SQL and MySQL course Β· Module 10: Transactions and advanced SQL

Views (VIEW) - a saved look

1 min read Β·
In this lesson4

Sometimes we repeat a certain query over and over - for example "show available books". Instead of writing it each time, we can save it under a name. That is a view (VIEW).

What is a view?

A view is a saved SELECT query that can be used as if it were an ordinary table. A view stores no data - each time it reaches into the real tables and shows the current result.

Creating a view

CREATE VIEW dostepne_ksiazki AS
SELECT * FROM ksiazki WHERE dostepna = 1;

From now on the Keeper can ask the Library briefly:

SELECT * FROM dostepne_ksiazki;

and will always get a fresh list of books ready to be borrowed, without repeating the condition WHERE dostepna = 1.

Why do we need views?

  • Readability - a complex query is hidden behind a simple name.
  • Convenience - written once, used many times.
  • Security - we can show a reader only the view, hiding sensitive columns.

Dropping a view

DROP VIEW dostepne_ksiazki;

A view is like a ready spyglass on the tower - once set, it always shows what is needed.

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 is a view (VIEW) in MySQL?

  2. 2. Which syntax creates a view in MySQL?

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

Hands-on tasks in the game

  • Code editor

    Create a view named dostepne_ksiazki that shows all columns from the ksiazki table, but only those rows where the dostepna column equals 1. Use CREATE VIEW ... AS SELECT ...

  • Horizontal ordering

    Arrange in one line the beginning of the dostepne_ksiazki view definition:

  • Code editor

    Create a view named drogie_ksiazki that shows the tytul and cena columns from the ksiazki table, but only for rows where cena is greater than 50. Use CREATE VIEW ... AS SELECT ...

  • Code editor

    Write a query that retrieves all columns and all rows from the dostepne_ksiazki view.

  1. QuestionWhat is a view
  2. QuestionCreating a view
  3. Coding taskA view of available books
  4. Put in orderThe start of the dostepne_ksiazki view
  5. QuestionA view and its data
  6. Coding taskA view of expensive books
  7. Coding taskUsing a view

Useful articles