SQL Views

How to create views and interact with them in a SQL database

An interesting thing you can do with SQL is to create a view.

A view is like a table, except instead of being a real table, on its own, it is dynamically built by the result of a SELECT query.

Let’s use the example we used in the joins lesson:

CREATE TABLE people (
  age INT NOT NULL,
  name CHAR(20) NOT NULL PRIMARY KEY
);

CREATE TABLE cars (
  brand CHAR(20) NOT NULL,
  model CHAR(20) NOT NULL,
  owner CHAR(20) NOT NULL PRIMARY KEY
);

We add some data:

INSERT INTO people VALUES (37, 'Flavio');
INSERT INTO people VALUES (8, 'Roger');
INSERT INTO cars VALUES ('Ford', 'Fiesta', 'Flavio');
INSERT INTO cars VALUES ('Ford', 'Mustang', 'Roger');

We can create a view that we call car_age that always contains the correlation between a car model and its owner’s age:

CREATE VIEW car_age AS SELECT model, age AS owner_age FROM people JOIN cars ON people.name = cars.owner;

Here is the result we can inspect with SELECT * FROM car_age:

        model         | owner_age 
----------------------+-----------
 Fiesta               |        37
 Mustang              |         8

The view is persistent, and will look like a table in your database. You can delete a view using DROP VIEW:

DROP VIEW car_age