>_ IzzyLab

Hands-on task

This task is step 14 of the course “SQL with SQLite”

SQLite: create a view (VIEW)

medium~7 min

The database /root/shop.db has a table sales(id, category, amount). Create a view (VIEW) named category_totals with two columns: category and total — the total revenue for each category.

The view must stay in the database (not a file with the result, but a view object), so that it can be queried like a table: SELECT * FROM category_totals.

What you'll practise:
  • sqlite3
  • create view
  • views
  • named query
  • sqlite_master

Topic breakdown

A view (VIEW) is a named SELECT stored in the schema: you query it like an ordinary table, but it stores no data — it recomputes the query beneath it every time. It can contain any logic, including grouping with aggregates; it is convenient to give columns explicit names via AS. SQLite lists all database objects (tables, indexes, views) in the system table sqlite_master with a type field.

What's checked

  1. the view category_totals is created
  2. the view returns category and total for each category

What it looks like