0:02
[Music]
0:06
thank you
0:09
[Music]
0:11
Hello friends welcome back to our
0:13
channel so in today's session we'll
0:15
discuss about One More Concept in dbms
0:18
that is a views
0:23
so what is a view
0:25
so here A View is a virtual table
0:29
it is a virtual table
0:32
thank you
0:34
constructed
0:37
from existing tables
0:39
from existing
0:42
table so this existing table can be from
0:46
the single table
0:47
or from multiple tables so we can create
0:51
a view we can create a view
0:55
from a single table or a multiple way
0:58
tables
0:59
with required attributes with as
1:03
required attributes
1:06
attributes that means
1:09
it doesn't mean
1:11
all the attributes should be there right
1:13
so we can
1:14
create a view with the required
1:16
attributes from a single table or a
1:18
multiple tables
1:20
right so
1:22
we if you modify any data in this view
1:25
that will be reflected on the original
1:28
table and if you do any modification on
1:30
the original table that will be
1:32
reflected on this view so that means
1:35
these two are correlated right so
1:37
changes
1:40
thank you
1:41
made
1:43
in
1:46
table
1:48
reflects on
1:52
View and also
1:55
changes
1:58
made
2:00
in a view
2:03
reflects
2:06
on table
2:08
the corresponding table
2:10
right but the view is a virtual table so
2:14
what is the purpose of creating these
2:17
views so we are having some tables so we
2:20
can access the data with uh from this
2:23
particular table so why we are going
2:25
with the views see an example
2:30
so take the college database
2:33
so College will be having
2:35
different departments let it be some CAC
2:38
Department
2:40
some ECE Department
2:42
some triply Department
2:45
and
2:46
mechanical Department
2:50
civil Department
2:52
right so there will be a student details
2:55
which will be maintained by the college
2:57
as well as in the CSE
3:00
right so
3:02
this college will be this database will
3:05
be having all the details of all the
3:08
branches okay all the branches details
3:10
all the students details will be
3:12
maintained in the main database called
3:14
College database and actually the CSC
3:18
student should be maintained here
3:20
similarly easy student details should be
3:22
maintained here triple students
3:23
mechanical students and civil students
3:26
so if there are any modifications done
3:29
on some CSE students and automatically
3:32
that should be reflected on the college
3:34
dataway that means a original database
3:36
because here also the college will also
3:38
maintain the database of all the
3:40
Departments right and individually the
3:43
department will maintain the student
3:45
details
3:46
so hope you're honest with this one so
3:48
if you maintain two different tables one
3:50
is for college one is for CSE and one
3:53
use for AC one is for Tripoli mechanical
3:55
and civil there are different tables
3:58
right so
4:00
College table
4:03
CSE table ECE table
4:06
and so on so if if all these are the
4:09
individual tables if you made any change
4:12
in the CSC table that should be
4:15
reflected on the college that that
4:18
cannot be done automatically so we need
4:20
to update the CSC student details in the
4:23
college database also that means we have
4:25
doing the same work multiple times so
4:28
instead of doing that we'll create a
4:30
database for college and we'll create a
4:33
view
4:35
for CAC
4:38
view for ECE
4:41
view for Tripoli and a view for
4:44
mechanical and view for cement so that
4:48
these are the virtual tables distributed
4:50
to each and every department so that if
4:54
there are any changes made in this
4:55
particular Department that will
4:57
automatically reflects on the main table
4:59
so this is the main advantage and the
5:03
purpose why we are creating this views
5:06
so that's why we are extracting some
5:09
portion of data and representing that
5:12
portion in a table so that is called a
5:14
view so that's why view is a virtual
5:17
table constructed
5:19
from existing table that may be either
5:22
or single or a multiple with the
5:24
required attributes so it doesn't mean
5:26
that we have to get all the attributes
5:28
right so
5:30
so all the student details from the main
5:32
database will be maintained in the CSC
5:34
view all the ESC students will be
5:36
maintained in the EC view simply view
5:39
mechanical review and a civil view so
5:41
that the reflex if there are any changes
5:43
made in this original table that will
5:45
also be reflected on this individual
5:48
tables because these are all the views
5:51
right so that is the main advantage why
5:54
we are not creating multiple tables we
5:56
are creating simply a views right so
5:59
changes made in table reflects on view
6:02
and changes made in viewer effects on
6:04
the table
6:05
right so this is the purpose of views
6:08
now we'll see the syntax now and then I
6:12
will show you the execution part in the
6:14
MySQL right
6:19
so we'll create a one table and we'll
6:22
create different views and also I will
6:24
show you the updations if the updation
6:27
is done on the table whether they are
6:29
reflecting on The View and if you are
6:30
doing any updations in the view whether
6:32
they are reflecting on the table so that
6:34
we will see practically in the MySQL now
6:37
I will show you the
6:40
Syntax for creating The View
6:45
syntax
6:47
so SQL prompt
6:52
so
6:55
create
6:57
view give the view name similar to our
7:01
table name we have to give the view name
7:03
as
7:06
write down the query here select
7:10
column name one
7:12
comma column name two
7:18
Etc
7:20
from
7:23
table 1 comma
7:26
table two so if it is only one table we
7:29
can write here only one table if you
7:32
want to extract the portions from
7:33
multiple tables we can get these
7:35
multiple tables
7:37
so let us take with the single table
7:40
from table 1
7:43
where
7:45
right on the condition
7:49
so this is the syntax to create a view
7:53
from a single table so if you go with
7:56
the multiple tables so use the same
7:58
syntax
8:01
create
8:03
View
8:07
name as
8:11
select
8:14
column name one
8:19
column name two
8:24
Etc
8:26
from
8:28
table 1
8:30
comma table 2
8:32
Etc
8:33
where
8:36
so give the join operation right
8:41
so table one dot column name one
8:46
is equal to
8:48
table 2 dot column name
8:53
column name
8:56
and
8:58
condition so whatever the condition we
9:00
have to write that condition should be
9:02
written here so this is for creating a
9:04
view of from the single table and this
9:07
is a syntax to create a view from
9:10
multiple tables
9:12
so now let me stop here so let us move
9:15
on to the MySQL I will execute each and
9:18
every query and I will explain you
9:20
everything right so let's move on to the
9:22
MySQL
9:23
Hello friends so just now we have seen
9:26
the syntax and complete introduction
9:28
about the views in dbms now uh let me
9:32
explain You by executing this
9:34
views in MySQL so let me open the MySQL
9:38
command line
9:39
so I'm getting the password
9:42
so yes so we we are creating the tables
9:46
in a one database called a YouTube so
9:49
let me
9:50
go to YouTube so now we are in YouTube
9:53
database so let me check for the tables
9:56
so here we are having a different tables
9:59
and here you can see there is a table
10:01
called student
10:04
right so
10:07
select star from student
10:12
so here we are having uh total seven
10:14
students with the different branches so
10:16
CAC EC civil right now
10:21
uh let us create a view for individual
10:24
departments okay so because so if if you
10:28
if individual departments maintain this
10:30
different tables if one student details
10:34
has been changed that should be
10:35
reflected in all the tables right so in
10:37
order to avoid that thing so we'll we
10:40
are creating a view
10:41
for this particular master table to
10:45
require with the required or the
10:47
attributes right so let me create
10:49
individual tables for individual groups
10:52
right so that means creating a view so
10:56
the syntax create
11:01
View
11:02
name view name let us take it as a CSC
11:05
underscore
11:07
students
11:09
students as
11:11
as give the query so select star from
11:16
student
11:18
where branch is equal to CSE
11:24
so query okay zero rows affected now let
11:27
us check with the tables
11:29
so show tables here you got the name CAC
11:32
underscore student now select star from
11:36
CSC underscore students
11:41
see there are three students
11:44
in the CSC Department
11:47
so let us check whether there are only
11:49
three or more than three see
11:52
so there are three one two and three
11:56
three students
11:58
now create a one more View
12:04
as a ECE students ECE
12:08
students as give a query select
12:12
so you can select a complete table or I
12:15
mean all the attributes or require
12:16
attributes I am taking all the
12:18
attributes select star from
12:20
student
12:22
so from this table I'm extracting
12:26
where branch is equal to easy I'm
12:30
extracting only the EC students so it
12:33
was also created now let us check with
12:35
that one
12:36
show tables
12:38
so select star from ECE
12:42
underscore students
12:46
so there are three students yes let us
12:48
check with the EC civil also
12:52
okay let us create a civil also create
12:55
a view as view name so civil students
13:02
as select star from
13:06
student
13:08
where
13:09
branch is equal to
13:12
civil
13:19
I think
13:24
yes let us check once again so select
13:29
star from student
13:33
so we yes we have given civil itself
13:36
right once again we'll check so
13:40
as select
13:45
star from
13:47
student
13:49
where
13:51
branch is equal to
13:54
7.
13:59
once again I'm getting the error
14:06
sorry syntax wrong
14:09
create View
14:12
name should not give you as right view
14:16
view name
14:18
as here we need to give you a query
14:20
select star from student
14:22
where branch is equal to
14:25
7 right now we'll get yes so you can
14:28
observe show
14:31
tables
14:33
see we are getting different so
14:36
the master table is a student and from
14:39
this student the views are civil student
14:41
CS student easy student now
14:45
so select
14:48
star from
14:52
student
14:53
right
14:55
now check I just want to update the
14:58
percentage of a civil student here in
15:00
the master table so which query so
15:03
update
15:04
student
15:07
set
15:10
percentage
15:11
is equal to
15:13
90
15:16
where
15:20
Branch or simply we can say Sid is equal
15:23
to one zero five
15:26
so one row affected now you can observe
15:28
so select star from student
15:32
see now we got a 90 for civil previously
15:35
it was 88 okay now we'll check with the
15:38
Civil student so select
15:41
star
15:42
from
15:44
civil underscore students
15:48
so you got Knight here so when we are
15:51
updating the attribute from the master
15:53
table it should be reflected on The View
15:56
also so here civil student is the view
15:59
so we are changing the value in the
16:01
master table it was reflected on The
16:03
View now let us change the view
16:07
and we'll check whether it was reflected
16:09
in the master table so we'll update the
16:11
same thing so update
16:14
civil student so view name civil
16:17
students
16:22
set
16:24
percentage is equal to
16:28
95 where Sid is equal to
16:35
one zero five
16:37
so it was changed in the view now we
16:40
will
16:41
see the L attributes of civil students
16:44
star from
16:47
civil students
16:51
see here 95 previously it was 90 now we
16:54
are updated here now we'll check in the
16:56
master table right so
16:59
select star from student
17:03
so here in the master table it was
17:05
reflected so if you do any changes in
17:07
the view it will be reflected on the
17:10
master table and if you are doing any
17:12
manipulations in the master table it
17:16
will be reflected on The View right so
17:19
this is the
17:21
view which we have created from the
17:23
single table now we'll check how to
17:27
create a view
17:28
from multiple tables now let us check so
17:32
tables so here we are having some
17:34
contact and we are having some student
17:38
so we know the details of a student
17:41
table what are the available things so
17:45
we can also have this one see select
17:47
star from contact
17:50
so this is a contact only three student
17:52
contacts now we'll create a view by
17:55
combining these two things
17:57
okay see what we have to do first we
18:00
need to create
18:02
a view
18:04
name so student contact student contact
18:10
as now we have to write the query so
18:13
select
18:17
student dot Sid
18:22
give the attributes which we required in
18:24
the to you so student dot s name
18:28
and see contact dot email so I'll go
18:33
with only these things these three these
18:36
four things contact Dot mobile
18:39
right from right on the tables so one is
18:43
the student table one is a contact table
18:46
where Q the condition so we have to join
18:49
this one so the join will be with
18:52
respect to the common attribute so where
18:55
student
18:58
Dot
19:00
Sid is equal to
19:04
contact dot Sid
19:06
so this is the natural join so equal
19:10
attribute
19:11
see query okay 0 0 is affected now you
19:13
can observe the short tables
19:20
so here you can observe
19:22
student contact so now you can go with
19:25
this one select star from
19:28
student
19:30
contact so we are having only three
19:32
terms one zero one one zero two one zero
19:35
six
19:36
with the email and mobile
19:38
so if you change name here automatically
19:40
that will be reflected on the
19:42
Master tab
19:44
right so hope you understood this one
19:47
so how can we respond and the next one
19:50
is how can we delete the views we can
19:52
also delete the views those are similar
19:54
to our table so drop command is used to
19:56
delete the table so if you use a
20:00
drop table
20:03
a drop table student contact
20:07
so we'll get an error so if you use a
20:10
drop
20:10
View
20:12
student
20:15
contact
20:16
C query okay zeros was affected If You
20:19
observe the tables list the student
20:22
contact is not available because the
20:24
Syntax for deleting the tab view is a
20:27
prop view instead of table we have to
20:29
give the view that's the only difference
20:32
right so hope you understood this views
20:35
concept how can what is the purpose of
20:37
use and how can we create a view from
20:40
single table how can we create a view
20:42
for from the multiple tables and how can
20:45
we delete the views
20:47
so I'll stop here and if you are having
20:49
any doubts regarding this one please
20:51
post your doubts in the comment section
20:52
definitely I will try to clarify all
20:54
your doubts and if you really enjoyed my
20:55
session like my session share my session
20:57
with your friends and don't forget to
20:58
subscribe to our Channel thanks for
21:00
watching thank you very much