_edited_edited.png)
Introduction
I have given a basic understanding of views from my previous blog. please go and read that before going into complex of views. So next we are going to see some complex example for creating the view.
Creating the views
The word itself says that it can be view the data's in certain condition but its not like table. In my previous blog, we saw some simple example for creating the views here we are going to see some more examples, they are,
Query 1:
We need to view the name having maths marks are greater than the average maths marks, for that the query is,
create view highmark as
select Name, English, Maths, Science
from marks
where maths > (select avg (maths )from marks);
Run the above query and the image is give below,
The query runs successfully then we need to check the output, for that we need the below syntax and query,
Syntax
select * from view_name;
Query
select * from highmark;
Then the output is shown below,
Here we are able to see the names whose marks in maths is greater than the whole maths mark average.
Query 2- Order by
Order by is used to arrange the order of the column in ascending order. the query for the order by is given below,
create view orders as
select Name, English ,Maths, Science
from marks
order by science;
from this we are going to arrange the view as an ascending of the science marks and its given below,
we got our output as successful , then we need to know the output by using the same below query,
Query
select * from orders;
while running this query, the output is shown below,
Here we are able to see the science marks are arranged in the ascending order.
Insert values using view
Insert values in the view is as same as the insert table, so directly am going to jump into the example query to insert one more row in that order view,
Query
insert into Orders (name, English , maths, science)
values('f',67,88,99);
then we run that query and the result is given below,
Here its successful, so we are going to get the output by using the below query,
Query
select * from orders;
and the Output is shown below,
Here we are able to see the that the ' f' row is added in the orders view,
so this is the use of the insert into.
Delete
Delete is used to delete certain data's from the views. Here is the syntax for the delete,
Syntax
delete from view_name
where condition;
Query
Example
delete from orders
where english>80;
Here , what we need is deleting the row which has English value greater than 80. Now we are going to run this query and this below screen will appear,
Here it run successfully, so next we are going to check the Output by using the below query,
Query
select * from orders;
when we run it, the output is shown below,
Here, we are able to see the English marks greater 80 are deleted from the output. The whole rows got deleted.
Next we are going to see about clause,
Check option with clause
Check option is a condition which we we can use to check the check the condition before insertion. if the condition fails, it will say error to us. let me explain you with an syntax and example,
Syntax
create view view_name as
select column name
from table name
where condition
with check option;
the above one is the syntax, before going there, we need to create table and insert values,
Query to create table
create table colleges(
Name varchar(255),
grade varchar(255),
class int,
zip int);
Query to insert data's
insert into colleges
values('a','b',3,123),
('b','a',1,456),
('c','e',6,789),
('d','c',7,101);
Already i gave the detailed explanation about creation and insertion in my previous blog, so that i directly started with the queries,
Next we are going to see the query for the check option with clause,
Query for check option with clause
create view studentdetail as
select name, grade
from colleges
where name is not null
with check option;
what i gave here is don't allow any values to create a row with null values in name, so here i run the query and it got successful. so next i need to check the output by using the below query,
Query
select * from studentdetail;
then the output is shown below,
so this check option checked if there is any null values in the name column it will remove, we dont have anything like that in our rows. Next thing we can try to insert values with null values in name, and the query is ,
Query
insert into studentdetail( grade)
values ('r');
Here i am going to insert grade values only, which other than grade all are null, probably data in the name is also null ,we will run it and check the output, and the output is given below,
Here from the output ,we are able to see that error says this rows violates the check option, we clearly see, whatever the condition we are giving ,the rows needs to satisfy the condition or else the program never allow us to create the row. With this clause we can prevent to insert the rows which doesn't satisfy the views filtering condition.
these are all the uses of the views in different situation.
Uses
Its an additional security for the predefined rows and columns in the table,
If we don't know how to use the join then we can use this view to view the data from different tables.
We can rename the columns and rows according to our comfort but at the same time it wont affect the original table.
It can easily hide the complex in the multiple joined table.
We can create as many as views for the single table but for the different users.
These are all the uses of views.
Conclusion
In this blog we will come to know about the the views which is used in different situation. It is a beginner friendly. If you are the beginner, you can use my previous and this blog for better understanding .and also you can try the same queries to get the basic knowledge of sql. We will see in the another blog to get deep dive in the SQL.
References
Thank you!
Comments