Virtual tables called views

We have learned how to filter tables using the SELECT statement with the condition clause WHERE. When we run a query it is designed to be a temporary return of results. In SQL we can create a VIEW to be a more permanent set of results. Views actually work and feel like a table but actually the results of a SQL query.

Let’s say we have a table that includes sensitive information, like a Social Security Number (SSN). If we want our end users to have access to the table but want to hide this field, we can simply create a view that does not include the SSN field and give them access to that view. In a database we want to avoid having duplicate information, and this is not duplicating the results in a database, but rather creating an object similar to a table based on the table and it’s data.

Creating a view

If you make a change to the view, the table it is based on will also reflect the changes. Below is the syntax for creating a view. Usually views are created with a conditional WHERE clause so that is also included in the syntax.

CREATE VIEW view_name AS
SELECT field_name(s)
FROM table_name
WHERE condition

Let’s say we have a student aid whose job is to look up student IDs for students. That student aid would not need to know anything more than just the students name and student ID number. We could create a view for that student aid to use. Here is an example using the student table.

CREATE VIEW student_id_lookup AS
SELECT student_id, last_name, first_name
FROM student;

We can now perform queries on the view just as we would a table. Here is a basic SELECT query:

SELECT *
FROM student_id_lookup;

You would use the SHOW TABLES; statement to view a list of tables and views.

Updating a view

We can update an existing view if conditions change. We add OR REPLACE to the statement and use the existing name of the view. Below is the syntax for updating a view.

CREATE OR REPLACE VIEW view_name AS
SELECT field_name(s)
FROM table_name
WHERE condition

If we want to modify the student_id_lookup view from above to include only students with last names between A and M we could write the query:

CREATE OR REPLACE VIEW student_id_lookup AS
SELECT student_id, last_name, first_name
FROM student
WHERE last_name BETWEEN "A" and "M";

Dropping a view

We can remove views from the database by using the DROP command. Below is the syntax for dropping views.

DROP VIEW view_name

To drop the student_id_lookup view we would write:

DROP VIEW student_id_lookup;

Lesson Activities

  1. Create a view called student_id_lookup that uses the student_id, last_name, first_name, and phone fields of the student table.
    Click to see code
    CREATE VIEW student_id_lookup AS
    SELECT student_id, last_name, first_name, phone
    FROM student;
  2. Perform a basic SELECT query on the view student_id_lookup
    Click to see code
    SELECT *
    FROM student_id_lookup;
  3. Using the view, update the record for Clara Oswald and change her phone number to (616) 444-3333
    Click to see code
    UPDATE student_id_lookup
    SET phone="(616) 444-3333"
    WHERE last_name="Oswald" AND first_name="Clara";
  4. Check the student table to see if the update is reflected on this table.
    Click to see code
    SELECT *
    FROM student;
  5. Modify the view student_id_lookup to remove the phone field.
    Click to see code
    CREATE OR REPLACE VIEW student_id_lookup AS
    SELECT student_id, last_name, first_name
    FROM student;
  6. Now use the DESCRIBE statement to view the structure of the view student_id_lookup.
    Click to see code
    DESCRIBE student_id_lookup;
  7. Modify the view student_id_lookup to only list the female students
    Click to see code
    CREATE OR REPLACE VIEW student_id_lookup AS
    SELECT student_id, last_name, first_name
    FROM student
    WHERE sex = "F";
  8. Now view the results of the student_id_lookup view.
    Click to see code
    SELECT * FROM student_id_lookup;

Questions