1. Managing views
Welcome back! Now that we've got a grasp on what views are, let's dig deeper.
2. Creating more complex views
We've kept our views simple. It's worth pointing out that views can get as complicated and creative as you choose.
Think of the aggregation, join, and conditional functions and clauses available to you in SQL.
Of course, the query to create the view still has to run, so you need to be aware of long query execution time.
3. Granting and revoking access to a view
In the last video, we talked about how views are helpful for access control.
To give and remove user permissions, we use the SQL GRANT and REVOKE command.
The syntax is as follows. First, you list the relevant privileges after the GRANT and the REVOKE command. There are several types of privileges users can execute, and here I list the most common.
Then you indicate on which object and for which role. You use the TO clause and FROM clause, respectively, for grant and revoke.
In chapter 4, we'll go more in-depth about different types of roles and privileges. For now, we just need a high-level understanding.
4. Granting and revoking example
Here's an example.
The update privilege on an object called ratings is being granted to public.
PUBLIC is a SQL term that encompasses all users. All users can now use the UPDATE command on the ratings object.
In the second line, the user db_user will no longer be able to INSERT on the object films.
5. Updating a view
A user can update a view if they have the necessary privilege.
If you need a refresher, here's a simple example of the UPDATE command.
You may be wondering, how is it even possible to update a view?
That's a good question because if you remember correctly, a view isn't a physical table. Therefore, when you run an update, you are updating the tables behind the view.
Hence, only particular views are updatable. There are criteria for a view to be considered updatable.
The criteria depend on the type of SQL being used. Generally, the view needs to be made up of one table and can't rely on a window or aggregate function.
6. Inserting into a view
The INSERT command is in a similar case as the UPDATE command. When you run an insert command into a view, you're again really inserting into the table behind it. The criteria for inserting is usually very similar to updatable views.
7. Inserting into a view
Generally, avoid modifying data through views. It's usually a good idea to use views for read-only purposes only.
8. Dropping a view
Dropping a view is straightforward with the DROP command.
There are two useful parameters to know about: CASCADE and RESTRICT. Sometimes there are SQL objects that depend on views. For example, it's not unusual for views to build off of other views in larger databases.
The RESTRICT parameter is the default and returns an error when you try to drop a view that other objects depend on.
The CASCADE parameter will drop the view and any object that depends on that view.
9. Redefining a view
Say you want to change the query a view is defined by.
To do this, you can use the CREATE OR REPLACE command. If a view_name exists, it is replaced by the new_query specified. However, there are limitations to this. The new query must generate the same column names, column order, and column data types as the existing query.
The column output may be different, as long as those conditions are met.
New columns may be added at the end.
If this criteria can't be met, the solution is to drop the existing view and create a new one.
10. Altering a view
Last but not least, the auxiliary properties of a view can be altered. I list the various options here.
This includes changing the name, owner, and schema of a view.
11. Let's practice!
Ok, let's see what you've learned!