SQL · Theory

Table vs View

Theory

266/439

Table vs View

A table stores rows on disk. A view stores a query. SELECT from both looks similar. INSERT goes to a table (and only some views). Indexes live on tables. DROP TABLE destroys data. DROP VIEW destroys the saved query, not the base data.

Trap — ‘view is a virtual table’ without saying the data still lives in base tables.

On the example next to this theory — Table vs View — a view is a stored SELECT you can query like a table.

Exam tip

Table holds data. View holds a query.

Table vs View — sample query

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER);
INSERT INTO employees VALUES (1, 'Asha', 70000), (2, 'Ravi', 50000);
CREATE VIEW high_pay AS
  SELECT name, salary FROM employees WHERE salary >= 60000;
SELECT * FROM high_pay;

Table vs View — a view is a stored SELECT you can query like a table.

Short notes

DefTable = stored rows. View = stored SELECT.
RuleChanging a view’s base table changes the view.
TrapThinking DROP VIEW deletes student rows.

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, salary INTEGER); INSERT INTO employees VALUES (1, 'Asha', 70000), (2, 'Ravi', 50000); CREATE VIEW high_pay AS SELECT name, salary FROM employees WHERE salary >= 60000; SELECT * FROM high_pay;

Table vs View

Table vs View — sample query

Short notes

Questions

Table vs View

Table vs View — sample query

Short notes

Questions