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 car_age