A VIEW in SQL is a virtual table that was created based on a SQL statement that was predefined. For example, lets say we had the following table:
site_user | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
If we wanted to retrieve all the records with the firstname 'Steve', we can do that with the following statement: SELECT * FROM site_user WHERE firstname = 'Steve';
steve_view | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
|
What we can do is put this virtual table in a view, so we can query data based on the virtual table above instead of the actual table in the database.
The syntax for creating a view is as follows:
CREATE VIEW view_name AS sql_statement;
So the syntax for creating the 'steve view' table above would be:
CREATE VIEW steve_view AS SELECT * FROM site_user WHERE firstname = 'Steve';
This is beneficial because we can now execute SQL queries on this view
instead of the entire table. For example if I wanted to get the average
ages of all the steves, I can do that with this new view and the
aggregate function AVG() like so: SELECT AVG(age) from steve_view;
For the following problems, consider the site_user table below:
site_user | ||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
Create a view called "firstname_lastname" from the site_user table that only has the firstname and lastname columns.
NOTE: This view should NOT contain the id and age columns.
NOTE2: please write the SQL statement on one line for this lab.