Original subtitles

0 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