This article walks us through creating and managing these constructs.
Views are virtual tables that display data from one or more tables. It is a database object that does not store data itself, but executes a query in the background and gives users a read-only representation of this data. A case study for the use of Views can be security, where a user who has access to a view is restricted from interacting with the data being displayed.
There are no restrictions to how many tables that can be used in a query and by extension a view. Below, we follow up on how we can create a view in a database.
Use the following query to generate a database called tv_shows
create database tv_shows go -- Needed for MSSQL. Remove for MySQL use tv_shows; CREATE TABLE reviewers ( id INT identity PRIMARY KEY, first_name NVARCHAR(100), last_name NVARCHAR(100) ); CREATE TABLE series( id INT identity PRIMARY KEY, title NVARCHAR(100), released_year nvarchar(4), genre NVARCHAR(100) ); CREATE TABLE reviews ( id INT identity PRIMARY KEY, rating DECIMAL(2,1), series_id INT, reviewer_id INT, FOREIGN KEY(series_id) REFERENCES series(id), FOREIGN KEY(reviewer_id) REFERENCES reviewers(id) ); INSERT INTO series (title, released_year, genre) VALUES ('Archer', 2009, 'Animation'), ('Arrested Development', 2003, 'Comedy'), ('Bob''s Burger', 2011, 'Animation'), ('Bojack Horseman', 2014, 'Animation'), ('Breaking Bad', 2008, 'Drama'), ('Curb Your Enthusiasm', 2000, 'Comedy'), ('Fargo', 2014, 'Drama'), ('Freaks and Geeks', 1999, 'Comedy'), ('General Hospital', 1963, 'Drama'), ('Halt and Catch Fire', 2014, 'Drama'), ('Malcolm In The Middle', 2000, 'Comedy'), ('Pushing Daisies', 2007, 'Comedy'), ('Seinfeld', 1989, 'Comedy'), ('Stranger Things', 2016, 'Drama'); INSERT INTO reviewers (first_name, last_name) VALUES ('Thomas', 'Wilson'), ('Wyatt', 'Skaggs'), ('Kimbra', 'Masters'), ('Domingo', 'Cortes'), ('Trevor', 'Rhone'), ('Brian', 'Long'), ('Marlon', 'Crawford'); INSERT INTO reviews(series_id, reviewer_id, rating) VALUES (1,1,8.0),(1,2,7.5),(1,3,8.5),(1,4,7.7),(1,5,8.9), (2,1,8.1),(2,4,6.0),(2,3,8.0),(2,6,8.4),(2,5,9.9), (3,1,7.0),(3,6,7.5),(3,4,8.0),(3,3,7.1),(3,5,8.0), (4,1,7.5),(4,3,7.8),(4,4,8.3),(4,2,7.6),(4,5,8.5), (5,1,9.5),(5,3,9.0),(5,4,9.1),(5,2,9.3),(5,5,9.9), (6,2,6.5),(6,3,7.8),(6,4,8.8),(6,2,8.4),(6,5,9.1), (7,2,9.1),(7,5,9.7), (8,4,8.5),(8,2,7.8),(8,6,8.8),(8,5,9.3), (9,2,5.5),(9,3,6.8),(9,4,5.8),(9,6,4.3),(9,5,4.5), (10,5,9.9), (13,3,8.0),(13,4,7.2), (14,2,8.5),(14,3,8.9),(14,4,8.9);
This database gives us a tv show rating system. We have series, reviewers and the reviews they have left.
If we wanted to see all the series currently in the system, we need only execute a SELECT statement as follows:
use tv_shows SELECT * FROM series;
Now this looks good. the only problem now, is that granting access to the table allows me to manipulate the data in the table, causing potentially undesirable outcomes. So in order to facilitate allowing someone to see the data in the table, we create a view. The syntax for creating this view is as follows:
CREATE VIEW All_Series AS SELECT * FROM series;
This code does not execute our select query again, but instead creates a VIEW structure in the database called ‘All_Series’ that can be queried each time, instead of checking the original table each time. Querying the view looks very similarly to query the table, as follows:
use tv_shows -- Query the table SELECT * FROM series; -- Query the View SELECT * FROM All_Series
A more practical use of a view is to facilitate more complex queries. Lets say that we needed to see all the movies and their average ratings. This would require us to JOIN tables and GROUP data in order to facilitate the math required for an average. A query for this would look something like this:
-- Generate a report showing titles and the average rating, ordered by the rating SELECT title, AVG(rating) as avg_rating FROM series INNER JOIN reviews ON series.id = reviews.series_id GROUP BY title ORDER BY avg_rating;
As you can see, this query took quite a bit of work to produce. A VIEW would make it much easier to retrieve this data, without needing to repeat this query easier. We can create the view as follows:
-- Create view as follows CREATE VIEW Movie_Averages AS SELECT title, AVG(rating) as avg_rating FROM series INNER JOIN reviews ON series.id = reviews.series_id GROUP BY title ORDER BY avg_rating;
After this, you can query this data at anytime by selecting from the VIEW:
-- Generate a report showing titles and the average rating, ordered by the rating SELECT * FROM Movie_Averages;
In a nutshell, a view can be created to retrieve data from any type of SELECT query, no matter how simple or complicated the query might be.
We can use the Drop command to drop a view. To drop the All_Movies view , we can use the following statement.
DROP VIEW All_Movies
We can use the sp_rename system procedure to rename a view, as seen below:
-- Sp_Rename CurrentName , NewName Sp_Rename Movie_Averages , Series_Reviews
We can alter the query the VIEW is based on as follows:
-- Alter view to include column aliases ALTER VIEW Series_Reviews AS SELECT title as [Series Title], AVG(rating) as [Average Rating] FROM series INNER JOIN reviews ON series.id = reviews.series_id GROUP BY title ORDER BY avg_rating;
VIEWS are a good way to encapsulate complex queries in a singular query-able construct. They provide security, simplicity and flexibility to database and application developers.