C112: Views in databases – Kimmer's Course Notes

When we write a SELECT FROM WHERE query, we’re telling the database how to build a table that contains the information we need. As we use INNER JOIN and other more advanced operations, our SELECT FROM WHERE queries get more and more complex. If we need to re-use the tables made by complex queries, it sure would be convenient to not have to always copy-paste the same, complicated SQL each time.

Similar to how we can use variables in Java to save and re-use information in a program, we can create views in a database to let us easily access information. We access the information as if we were using a table, but a view is not a table in a database. It is more like a virtual table that is dynamically generated from the result of its SELECT FROM WHERE query every time it is needed. Since it is the result of a SELECT FROM WHERE query, the view doesn’t have to be one of the basic tables in a database that were made using CREATE TABLE. Views can instead be the customized, specialized combination of multiple tables we can now make with a SELECT FROM WHERE statement. We’re able to re-use these custom tables in other queries without copy-pasting all the SQL that made them. Instead we just use the name of the view in any place we’ve been able to use a table. Just like we can re-use data in a Java program by using the name of a variable instead of the actual value that variable holds. In this last part of the course, we’ll see how to use the CREATE VIEW statement to make views and then use them in SELECT FROM WHERE queries just like we use the original tables in the database.

The syntax of the CREATE VIEW STATEMENT is

CREATE VIEW NameOfView AS
SELECT ...
FROM ...
WHERE ...

We can write any SELECT FROM WHERE query that is valid on the database we’re using. We’ve seen that the result of a SELECT FROM WHERE query is a table that is returned to the user running the query. Views are not tables, though! They are placeholders for that SELECT FROM WHERE query that can be used in other SELECT FROM WHERE queries or SQL statements in the database. We can use that as if they were a table, but they’re not tables. The result of the query gets generated on demand when the name of the view is used, and those dynamic rows and columns of data then become available to use.

To illustrate all this, let’s go back to the Sailors Reserves Boats database and add a view to it that contains the name and rating of all sailors. We’re creating this view because we’ve determined the ID number and the age are sensitive information, so we can give certain end users access to the view and hide that information from those that do not need to see it or use it. The view will be called OpenAccessSailors, and the two columns in it will be sname and rating.. The SELECT FROM WHERE query that gives us the name and rating of all sailors is

SELECT sname, rating
FROM Sailors

The CREATE VIEW statement can use that SELECT FROM statement (the WHERE clause wasn’t needed here since we were needing all rows from the Sailors table) as-is:

CREATE VIEW OpenAccessSailors AS
SELECT sname, rating
FROM Sailors

When we have created that view, we can treat it like the original tables in the database. We could see its entire contents.

SELECT *
FROM OpenAccessSailors

We could look at the names of sailors whose rating is above 7.

SELECT sname
FROM OpenAccessSailors
WHERE rating > 7

We could even use an UPDATE statement (which we do not cover in this course) to change the data in the view. If we did, we would see that the data in the underlying Sailors table changed, too.

If at any time we need to remove the view (because it will no longer be used, was created in error, etc.), we can use the DROP VIEW statement to remove it by name.

DROP VIEW OpenAccessSailors;

What we are not able to do with this view is INNER JOIN it to find out which OpenAccessSailors have reserved boats. We cannot INNER JOIN it because it does not have an sid column. That’s not a defect of views, though. It’s just a consequence of the columns in this particular one. If we have another view PartialAccessSailors that contains everything but the age column, we can use it in INNER JOINs just like we would use the Sailors table.

CREATE VIEW PartialAccessSailors AS
SELECT sid, sname, rating
FROM Sailors

And then use that view to find the names of all Sailors that have reserved a boat.

SELECT sname
FROM PartialAccessSailors P INNER JOIN Reserves R ON P.sid = R.sid