Create and Manage SQL Database Views

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.

End-to-End Database Development Courses

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;

Creating and Querying a View

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.

Dropping a View in SQL Server

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

End-to-End Database Development Courses

Renaming View

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   

Altering or Modifying a View

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;

Conclusion

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.