All language subtitles for 1. VIEWS

Hi everyone
1

In this video we will learn about views
2

View
3

Is a query
4

It is not a table
5

But it behaves like a table
6

Whenever you will look at a view
7

It will contain contents as a table
8

But it'll actually be a query
9

So this what is the use of this Virtual Table
10

This Virtual Table is used
11

Because
12

This table gives us ease of use
13

Space savings
14

And data security
15

Since it is not an actual table
16

It will not take up space in our database
17

And
18

You can give access of this view
19

To particular users, to particular teams
20

So that they do not look at entirety of your data
21

They just look at only those parts of your data
22

Which are part of this view and you can control the access of every table and every view
23

Which we'll discuss later how to do that
24

The syntax for creating a view is
25

You write create view
26

Name the view
27

And then you write AS keyword
28

After that you specify
29

The select statement, you select the columns from
30

You can have single table multiple tables you can use joins here
31

Whatever is the output of this statement
32

Will be saved
33

As a view
34

And that view will have a name
35

Let's look at an example
36

Suppose that within our supermart we have a Logistics team also
37

Which will be shipping all the orders that we have received
38

To the respective customers
39

To that Logistics team
40

We want to give the data of all the order IDs and the customer details so customer name City
41

state country
42

In a separate table
43

We do not want to show them everything in our database
44

So we create a view specially for them
45

Let us create this view as Logistics
46

And we'll define the columns as order line
47

Order ID
48

Customer name city state country
49

These will be taken from the sales table which is named as a
50

And we left join this
51

Against the customer table which is b
52

The common condition will be customer ID should be same in both the tables
53

And we'll order this by the order line
54

Let us go and create this view
55

Create
56

view name logistics as
57

And start writing select
58

Order line
59

order ID
60

Customer name
61

City
62

State
63

and Country
64

From
65

our sales table
66

Left join with the customers table
67

Common condition will be customer ID
68

And ordered by
69

The order line
70

Select this and run
71

Query was successful you can see the components of this view by
72

Writing select star from the view. so it is behaving
73

just like a table but it is not a table
74

It is a query the query that we have written above
75

The select part
76

Whenever you run the select star, it will give the output of that query
77

So you can see
78

We have this view which contains
79

Order lines order IDs
80

Customer name city state and country
81

So you can give the control of this view
82

the access of this view
83

to
84

Your logistics team
85

Also note that instead of using just create you can write here create or replace view
86

What
87

Benefit that will have is
88

If that view is already
89

Present in your database
90

It will be updated with the new definition that you have given
91

So
92

If it is already present and you have written
93

only create view it will return back
94

as an error
95

The query will fail
96

So instead of writing just create view
97

Probably you should
98

Write create or replace view
99

So that it does not give you an error
100

Now let's see how to
101

Delete a view that you have already created
102

Or if you want to update a view
103

That you are created
104

To delete drop view will be used
105

And to update update command will be used let us go and write these
106

We have created this view. we will delete
107

The logistics view drop view
108

Logistics
109

And this will delete the view
110

This view is gone
111

We do not get any output from this select query since
112

Logistics is not existing anymore
113

If we update a view the data that that particular view has
114

Will be updated. That data is coming from a Table
115

So that particular table will also be updated
116

so writing an update command on a view is similar to updating a table however it is not advisable
117

If you want to update a table you should not go through the view you should directly go and update the table
118

So we'll not update the table using our views right now
119

However if you still want to update a view here are some of the conditions
120

Which your view should meet
121

So when you are defining the view while creating a view
122

The select statement that you have
123

It should meet certain conditions those conditions are listed here I do not advise that you update a view
124

However if you want to, you should look at these conditions once, It'll be part of the presentation
125

Attached in the resources part of this lecture
126

So that is all in the view
127

In the next video we will learn about indexes