SQL Views: Creating and Utilizing Views in a SQL Database

When working with SQL, one powerful feature you can utilize is the creation of views. Unlike regular tables, views are dynamically generated based on the result of a SELECT query. This allows for flexible data retrieval and analysis within your database.

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

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
);

To populate the tables, we can insert 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');

Now, let’s create a view named car_age that displays the correlation between a car model and the respective owner’s age:

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

To inspect the result, simply run SELECT * FROM car_age:

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

As a persistent object, the view will appear as a table in your database. If you no longer need the view, you can delete it using DROP VIEW:

DROP VIEW car_age;

Creating and utilizing views provides a flexible and efficient way to retrieve specific data from your SQL database.

Tags: SQL, SQL Views, Database Management