SQL Tutorial - 49: Views in SQL (Part-2) Video Lecture | Microsoft SQL for Beginners - Database Management

hello guys welcome to my channel this is
the 49th tutorial in this course and in
this tutorial we are going to continue
our discussion on views and we're going
to check out how we can insert data in
views or you know basically insert data
in tables through views right and just
as the insert statement is used to
insert data in a table may be used to
insert data in a view so I just
demonstrate how that works and if you
guys remember in the last tutorial we
created a view for the employee table in
our test database and I name the view
EMP underscore view and you know it
basically had three fields from the
table the ID field the name field and
the email field so to insert data in a
view to insert a row I'll type in insert
into and then the name of the view which
is EMP underscore view and then on the
next line I'll type in values and within
parenthesis I'll type in you know three
values for the three fields that the
view holds right so I type in 1917 for
the ID field and you know this number
has to be different from you know all
the other ID values in the table because
ID field for that table you know that
that field is the primary key for the
table so this value has to be different
from the rest of the values and then you
know name in single code so I'll just
type in something like Craig Craig would
do and then I'll put a comma and then
you know within single quotes again I'll
type in a value for the email address
something like Craig 0:07 ad gmail.com
right put in the semicolon and when I
execute the statement I see that in the
output section I get the message that
the row was inserted successfully in the
table and you know I'll insert another
row because I basically you know the
objective that I had before making this
tutorial was to demonstrate to you guys
that you know if there is a view and if
there is a row in a table that objects
the condition you know that was used to
create the view then the row is not
returned when you execute a select star
on the view right so the condition that
we had when we created the view was that
you know the
value.if if the value is null for the
name field then the row will not be
returned right so I will insert a
statement into a row sorry into the view
that values that basically has null
value for the name field so I'll type in
1 9 1 8 here and I put a comma type in
null for the name field and just type in
something random for the email field
like not a variable at gmail.com imagine
there will be lot of takers for that
email address when I execute that query
I see again that the row was inserted
successfully but then when I would run a
select star on the view EMP underscore
view I would see that I get the row with
ID 1917 but I do not get the row with ID
1918 and if you guys are wondering what
happened then
you know this row violates the condition
of the view right because the value for
the name field is null here so that's
why this row won't be returned but this
row is definitely in the employee table
right so the row was inserted in the
table but it just wasn't you know
obtained in the result set as a part of
this query right so if you if you guys
want to see you know what happened to
the employee table and of course you can
run the select star from employee query
and when I execute this I see that two
fresh Rose have been inserted so 1917
and 1918 both rows with the two IDs have
been inserted and the name the name
value for the row with 1980
1918 ID is null and that's why this
wasn't returned with the EMP underscore
view select star statement right so what
we going to do now is we are going to
create another view and it's going to be
you know just a little different from
you know the EMP underscore view but
it's going to be a view on the same
table so
this one is going to have the check
option on right and I'll explain what
that does or what difference that makes
but I first get in the basic stuff to
create the view create view and then I
will call this one EMP underscore view
underscore new and then the as keyword
and then select and I'll you know still
select the same column so ID name and
email right and then from and then the
name of the table which is employee and
then where name is not null the same
condition but I type in three more
keywords there with check option and
what this is going to do is it's going
to make sure that whenever we try to
insert a row in this view with the null
value for the name field then we get an
error and the row is not inserted in the
table at all right so whereas in the
last case the row was inserted in the
table but it wasn't returned in the
views results said in this case we won't
be able to insert the view at all and
the insert into statement will result
into an error right so let's execute
this first to create the view and then
let's try to run an insert into query
and see what difference has been made so
EMP underscore view underscore new and
then values and then within parentheses
type in 1919 as the ID value and then
null for name and then the email address
field I try something I try this at
gmail.com and when I execute this query
you see that in the output section I get
an error message and the message is the
check option failed test EMP underscore
underscore new so you know that's the
significance of having the check option
and this tutorial is running a little
long so you know I'll discuss the rest
of the stuff about views in the next
tutorial maybe and thank you so much for
watching this tutorial and you may
subscribe to my channel and I'll see you
in the next tutorial