SQL Views Tutorial | VIEWS in SQL Complete Tutorial

this video is sponsored by skillshare hey guys i'm tawfiq in this video let's talk about weaves in sql now weaves are one of the most simplest of concepts that you can learn in sql but still views are very widely used in sql so in this video i'm going to start by explaining you what a weave is i'm also going to explain you how sql will process a weave we'll then look at how to create a weave and how to modify a weave i'm then going to explain you what are the different advantages of using a viv that is what is the main purpose of using a vf in our projects finally i am going to talk about the updatable views that is how can you create a view which can be updated that is what are the different rules that you need to consider in order to make a viv updatable hopefully by the end of this video you should be able to get complete understanding about what a web is and how to effectively use them now before i can start i would like to thank the sponsor of today's video that is skillshare now skillshare is an online learning community where you can find thousands of different classes related to almost any concept that you can think of on skillshare you would find some fantastic classes related to productivity photography video creation data science data analytics sql python and so many more now personally one of my favorite classes on skillshare has to be the youtube success class conducted by mkbhd now mkbhd is one of the most reputed creators on youtube and his class on skillshare has to be a masterpiece in his class he talks about everything that you need to know to make youtube videos he starts by talking about how to get video ideas and then how to script your videos and then how to shoot your videos the different camera settings the different lighting setup and then how to engage your audience and then also how to edit videos some really good tips on how to edit videos and also then finally how to grow your audience i have definitely benefited from the class conducted by mkbhd on skillshare and i definitely recommend it to anyone who wants to make better youtube videos but of course there are so many other fantastic classes on skillshare that you can take as well now the best part about skillshare is that you can join skillshare for free so if you click on the link that i will be leaving in the description below you will get one month free access to all the different classes that are available on skillshare so definitely check that out and thank you skillshare for sponsoring this video okay so let's start by understanding what a weave is weave is a database object which is created over an sql query meaning we will just represent the data that is returned by an sql query so we've does not store any data but every time you call a wave it just executes the sql query underlying it so we can basically say that weave is similar to a table and can be treated as a virtual table but the difference between a weave and a table is that table can store data whereas weave can never store data wave is just a representative of an underlying sql query so every time you call a vf it internally just executes the underlying sql query okay now let's try to better understand this with some examples so to explain the different concepts of weave i have created three different tables so i have the customer table product table and the order table now the customer table i'm calling it like tb underscore customer data the product table i'm calling it like tb underscore product info and order table i'm calling it like tb underscore order details okay now in the customer table as you can see i have few information about the customer like customer id their name their phone their email and their address okay and then in the product table i have information about the product id the product name which brand they belong to and their price and finally in the orders table you can find information about the order id and the product which was basically purchased from a particular order and the quantity of that product and the customer who purchased this order and what was the discount that was given to him and finally the date when this order actually got processed now let's say you're working on a project where you are recording these information about the customers products and the different orders and let's say your supervisor comes to you and tells you to generate a report to gather the order summary so that this can be shared with the clients or some vendors or some external parties who are not part of your organization but you're basically working with them on some common projects okay now when you have this kind of a requirement to gather the order summary you first need to understand what is the data that you need to provide so basically you will need to provide something like an order id the date when the order happened what was the product that was purchased who is the customer who purchased it and finally what is the cost of it okay so i have already written the query so i'm not going to be writing this query in this video now i'll just quickly explain you what this query is doing so i'm joining these three table the customer order and the product table because all the information that i need to get the order summary is present in these three tables and then i'm fetching the all the required columns so i'm fetching the order id the date the product name and the customer name and finally i'm calculating the cost using this formula okay now i'm not going to be explaining you uh this formula or how i have written this query because this video is more about views and not about how i have written this query okay so let me just execute this query just to see what is the order summary that we can get and you can see that i'm getting the output now and i think this output is pretty fine now let's say if your supervisor had told you that you just need to generate this report and then extract it into an excel file or some sort of file and then share this file with your client or vendor or some external parties then you could just share that file but let's say your supervisor tells you that you need to basically give a code or an sql query with which the client or the vendor or the external party can then use that query to execute it whenever they want and on any date they want okay so let's say they have that kind of requirement where the client wants to execute this report every single day and multiple times a day uh just they want to see what was the changes in the order uh summary that is happening every day or every few hours so whenever you have this kind of a requirement you basically will have two options the first option is that you can just share the sql query that you have just written and share it with your clients vendors or external parties right but the problem with that approach is that you will basically be sharing all the confidential information because you will be sharing the table names in your database which you never want to do so whenever you are working on a project you would never want any external party or client or any vendors that you are working with to know what your internal database structure is or what is the logic that you have used to generate a report or you do not want to share any confidential information because when you share this query the client or external parties would come to know about all these customer tables and all the other tables and this customer table has some confidential information like you can see that this customer table here has the phone number their email id address etc and there can be many other confidential information as well which you do not want to share it with anyone outside your organization right so the first option is not fine what would be the second option the second option is that we can just create something called as a beef okay now in order to create a weave i can just use the command like create weave and give a name for that view the name can be anything but it's always better to keep it meaningful so since this is an order summary report i'm just going to name the view like order summary and then you need to specify this clause that is as okay and then i can just run this whole command here so if i run this now you can see that the view is created so the view is created with this name okay so in order to execute this view or see the result of that weave i can just use the query like select star from this weave name and now if i execute this query you can see that i'm getting the data that basically i got from this query as well so that is 12 records here okay so what actually happened was sql created a view for this particular query and then every time you execute or call this wave internally it will just execute the query that was used to create this wave okay so this exactly is what a view does you can see that we have basically used a query which we would generally use to query from a table right but in this case order summary is not a table it's a weave okay so we can use a select statement to not only query the data from a table but also we can query the data from a brief but as i told you previously weave is different from a table weave does not store data so even though i have created this view and i'm executing it like similar to a table this order summary view does not store any data the only thing that it will store is the structure of this wave and i'm going to talk about that in a short while okay but what happens is every time i call this wave internally sql will just execute the query that was used to create that wave so whether i am executing this query here that you get the output here or whether i am executing this wave here they basically are the same for sql because internally the execution of a web will just execute the query that was used to create that wave okay so you can say that view basically does not improve any performance because it's exactly the same as executing the query so if you call the view 100 times it's going to execute the query 100 times okay but there is one type of view which is called as a materialized view which can improve performance but i'm not going to talk about that in this video i'll be making a separate video about materialized beef okay so now you understood how to create a view now there is a lot more things that we can cover about creating a view and i'm going to explain that in a short while but before that i just want to answer some of the questions that you might have now if i was in your place i'd basically be thinking how is actually weave useful or what is the actual purpose of using a weave or what are its advantages because since i am able to view the data from the wave i should also be able to see the query that was used to create this view right because if i go to the left if i go to my database if i go to yes my databases demo here if i go to the schema that is public if i go to the views and if i right click on my weave and if i click script and if i click create script you can basically see the query that was used to create this vf right in this case how is it actually useful because whether i give my client the sql query or whether i give him the view he should still be able to see the sql query that was used to create this view so how is it actually useful now it's a valid question but the thing is when we want to have this kind of a requirement where we want to share a weave or some data with our clients and we should give them access to execute a particular table or a wave what we generally do is we create a new user okay for that particular client or for the external party whoever it is and that user will only have specific access for the data that we want them to have access to so let me explain that in more detail but anyways the thing that i'm going to explain here is basically explaining you what is the main purpose of using a view i can think of a few different advantages but mainly i would say that there are two main purpose of why we use views so widely in our projects okay the first one is security and the second one is to simplify complex sql queries okay so let's first look at the first one that is the security and this is exactly the question that i just mentioned now okay whether you share an sql query or whether you share a wave if you share a view since i am able to see the structure of the view in my database the client should be able to see the structure of the view right but in reality we do not share the database credentials with any external party the database credentials are only for our team who are basically working on the project right to any third party vendors or anyone external to our project or to our company will never share our database credentials with them okay what we do is we create a user for the client and then that user will only have access to specific database objects which we would like them to have access to okay so let me try to give you an example so i'm going to create a specific user for let's say some vendor and let's say that vendor is called as james okay so i'm just going to create a user but in postgresql user is same as role so i can either use create user and then username or i can just mention create role and a username so i'm just going to say james and it's going to be a login role and i'm going to give him a password like let's say maybe i'll just keep it james for now okay i'm just going to create this role so if i execute this you can see that the role is now created okay so what this means is instead of sharing my database credentials with my vendor i'll just share the credentials of this role that is james so the role name is james the password is james the databases demo so the vendor or any client that i have can access our database using this role okay so let me try to do that so i'm just going to open a new session here and i'm going to connect to this new user so i'm just going to say this is demo database the user is james and i'll click ok and now if i try to execute okay so what i'm going to do is i'm just going to execute this view that is select start from order summary right so if i execute this view here the user is james he's connecting to the database demo but as the james user now if i execute this you can see that i'm getting an error saying that permission denied for this wave so this is what i mean what we have done is we have given access to our database demo to this vendor or to our external client but we have created a separate role for him or a user for him okay and what we will do is for this particular role will only give him access to this particular view so i'm just going to run a command like grant select on this view name so that is order summary to james okay that is my user or my role so i'll just execute that now you can see the grant is successful and now when james tries to log in and tries to execute this query now he's able to see the data in this wave okay so what we have basically done is i have created a separate role that is a separate user and that user has only select access on this wave so he will only be able to weave the data from this weave the client will not be able to see the sql query that was used to create this wave okay so this is how we achieve security using views okay so instead of sharing the entire sql query we just shared the view we created a new role for that external user and then we gave him only read access to that view so he will not be able to see what was the sql query used to create this view but he'll only be able to see the data in that wave okay so this is the one of the main advantages of using a view but of course there are other advantages as well and one of the other useful advantage of weave is that we can use weave to simplify complex sql queries okay now in this case this query is not that complex but let's say you had a query which was let's say 100 or 200 lines long and it was very complex there are a lot of logics that were written multiple different tables used etc and let's say you have some business user who is part of your own organization and part of your own project and he's a business user so he's not technically that sound and let's say he has a requirement where he wants to execute that query to view the report data okay now if i try to give him this complex sql query it might actually scare him because looking at this complex sql query he might not be very confident to execute it but rather if i just create a weave over that 200 lines of complex sql query and just give him that weave where he just has to execute like select star from that weave name it will be much easier for him to use it right and not just this but let's say i have some complex sql query which i keep on executing multiple times uh let's say i'm doing some data analysis or some testing or some development where i have a piece of sql query which i need to execute multiple different times i need to rewrite it several different times on maybe in today and maybe after few days etc right what i can just do is instead of rewriting the entire complex query multiple different times i can just create a weave over that complex sql query and then use this wave multiple times whenever i want so it's kind of saving that sql query as a wave so that i can use it multiple times instead of rewriting that query so this is the second main advantage we can simplify a complex sql query by using a wave i hope now you understand how to create a view which was very simple but more importantly what is the main purpose of using a view okay now let's talk about how to create viv in few different ways and how to modify a wave okay now to explain that what i'm going to do is i'll just remove all of this we do not need this but we will still need this query that i have created okay so i have created this wave here by using the create view command but let us say if i wanted to re-execute this wave and if i execute this you can see that i am getting an error saying that relation order summary already exists but i have another command here called as create or replace okay now create or replace is also basically creating a view but there is a slight difference so let's look at that but before that let me just execute this now you can see that i am not getting any error and i can execute this as many times as i want and it will work absolutely fine so what exactly is happening is when i do a create or replace sql will check if the weave is not present if the view is not present then it's going to create this view but let's say if the weave is already present then it's just going to replace that with with whatever you have provided newly okay but there are certain rules the rules are that whenever you're using a create or replace basically you cannot change the order of the column you cannot change the column data type you cannot change the column names okay now let's try to understand this with some examples okay so let's say i have this date column here right and i want to change this column name from date to let's say maybe order date okay and if i run this query this will basically work fine and it's showing me this new column name that is order date but let's say if i run this create or replace vf now it's going to throw me an error saying that cannot change name of view column date to order date so this is the first thing that you need to remember whenever you're using a create or a replace you cannot change the column name of the existing wave okay so if a view is already created then whatever column name that you have used you should still use the same column name now there is a different way to do that i'm going to talk about that in a short while but for now remember that when you're using create or replace you should still follow the same column name that was used when creating the original view okay now secondly so let me remove this column name so this is the first thing okay the second thing is i cannot change the data type so let's say i have this order id column and i know that this is an integer so if i execute this query you can see that it is okay this is a big end okay now let's say i want to change this data type to something else maybe i will make it like work here so if i just execute this query it works absolutely fine but let's say if i execute this weave now it's going to throw me an error saying that cannot change data type of the wave order id from begin to character varying okay so we cannot change the data type that's the second thing that you need to remember the third thing is as i told you we cannot change the order of the column so let's say i want to add a column here that is c dot cust id so i'm adding a column in between because i have totally five columns one two three four and this was the fifth column that is cost now in the fifth column's place i have put cust id okay now if i try to run this wave it's going to throw me an error saying that cannot change the name of weave column cos to cast id so we've already knows that the fifth column name was cost but now what i have done is the fifth column name i have made it like trust id and that is why it's showing an error okay so i cannot add a new column in between any of the existing columns but i can add this new column to the end of the column list so if i add it to the end and now if i try to run this now it will work absolutely fine okay so i cannot change the order of the column so whatever the columns were already defined during the creation of the weave that has to exist in the same order but at the end i can add new columns so let's say if i just execute this statement here so i'm just going to say select star from this and if i run this now you can see that the new column cast id has come up here okay so i can add new columns at the end but i just cannot add new columns in between okay so these are the certain things that you need to remember and why this happens is because when you create a view i told you that viv does not store data but we will store the structure of your query so when i say structure of the query it basically associates with what you have mentioned in your select clause so the list of columns the the data type of each of these columns and the column names okay so this structure is stored by the wave and that is what we will be associating it to okay so every time you do a create or replace it's going to match if the existing structure and the structure that you're trying to give newly using to create or replace is it in sync if it is not in sync then that is when it's going to throw an error you can add new columns at the end but other than that you cannot basically change anything else but you can of course change anything in the join condition so i can add new columns i can add order by i can remove a join uh table etc all that is fine just that the structure should not change the structure has to be the same when you are using create or replace okay okay so i hope this is clear how to use create or replace but let's say you still wanted to change the structure okay so for example i wanted to change this column from date to order date just to make it more meaningful now i can still change the structure of a view just that i cannot do that using create or replace okay in order to change the structure of the view i just need to use the command like alter weave okay so similar to a table i can just say alter wave and the view of name that is order summary and then i can say alter weave view name rename column and the column name which is date 2 i can just say the new column name which is order date okay now if i execute this the alter wave is successful and now if i execute this beef you can see that now this viv is getting the new column name that is order data but one thing that you need to remember is this altered we've only altered the column name of the weave but not of the underlying table okay so let's say if i look at the table itself so this order was coming from the order table if i look at this table this column will still be called as date so this is one thing that you need to remember so similarly i can also rename the view so let's say if i wanted to rename the wave i can again use the same alter view command so i can say alter wave and that is my view of name okay not this but order summary i can say rename to some let's say order summary underscore 2 and if i execute this you can see that the rename is successful and now if i execute select star from order summary it does not exist because i renamed this view to order summary underscore 2 and now if i execute this it's it's present here so i can rename the view i can rename the column of the view by using the alter commands i cannot just change the structure of the view by using create or replace okay i hope all of this is clear now similarly as you might have already guessed if i wanted to remove this wave or drop this view completely from the database then i can use the command create sorry drop wave and the view of name okay there is order summary if i do this okay order summary i already renamed it to underscore two so i'm just going to drop weave order summary underscore two and now you can see the drop is successful okay so this is basically how you can modify a weave we have also seen how to create a weave and how to create a web using create or replace and how to alter a weave etc okay and finally also how to drop a beef i hope all of this was clear now there is one more thing that i want to talk about here is that view does not automatically capture the change in a table structure okay so for example let's say you wanted to create a weave where you want to only capture the most expensive products okay so let's say i'm just going to do select star from tb underscore products uh or product underscore info right so what is the table in him here product info okay and here i'm just going to say where price is greater than one thousand so i'm just going to capture okay one thousand i'm just going to capture those products who are expensive and i'm i'm terming expensive uh when the price is greater than one thousand okay and i'm going to create a view for this i'm just going to say create weave okay and i'm just going to name it like let's say expensive products okay something like this okay and i'll use the as clause now if i run this the weave is created and now if i just select from this waves i'm just going to select start from this wave it's going to only show me those two products who are expensive that is whose price is greater than one percent and one thing that you need to remember here is that i created this view by using the star command in my select clause so meaning that all the columns from this table would be selected okay now what i'm going to do is let me change the structure of this underlying table so that is let me add a new column okay so i'm just going to say alter table and this table that is db product info and i'm going to say add column column name so let me add maybe prod configuration okay i'll just say prod config and this will be where care and i'm going to say maybe 100 okay and if i execute this the table is now altered okay so let's say if i just query from this table that is tb underscore product info okay so when i do a select start from eb product info you can see that i have a new column added here okay and now when i execute this weave okay that is the expensive product this was a view that i just created here right and i told you that whenever we execute a view internally it will just execute the query now when i execute this wave it should basically internally execute the query that is this one that is select star from uh tv product info where price greater than one thousand okay but the thing is i told you that a v when it is created it will store the structure of that weave right it does not store the data but it stores the structure so when the view is initially created it had stored the structure and that structure only had these first four columns this new column was not present in the initial structure so when i do a select star from this view that is expensive products you can see that it's not showcasing the new column that was added in the db product info table okay so this is an important thing that you need to remember and this can actually be a tricky question that will be asked in interviews okay i was asked about it and that is why i'm sharing this so the reason why this is happening is the structure of the view was already stored so the list of columns the column name the data type everything is already stored now if i wanted to change the structure of that weave or basically update the structure of that wave then i will basically need to recreate that view or basically refresh that wave in order to refresh that wave i can just say create or replace that weave and now if i try to run this wave now the view is recreated and now if i do a select star from that weave name now this new column will show up okay so this is one thing that you need to remember but this is only when you add a new column or basically change the structure of the table right but let's say if you add a new record so for example let's say if i do insert into this table that is tb product info i'm going to say my new column would be values here my product would be let's say p10 okay and maybe a new product maybe i'll just tell it like test okay and maybe it's a brand also i will name it like test and let's say the price is maybe 200 okay or maybe not 200 i'll say 1200 okay and my last column that is plot configure will be null okay if i do an insert here now the data is inserted so that in my table that is tb product info you can see i have this record p 10 that is newly added the test test product okay and if i go to my beef you will still see the data in this weave okay so the the thing that you need to remember is we will always showcase you the latest data okay because every time you execute a view it will execute the query whatever data is changed or updated or newly added or deleted or whatever the latest data will show from the view but when it comes to the table structure it may not show the latest table structure because you will need to refresh the v for it to show the latest table structure okay so this is a couple of things that you need to remember okay so i hope this was clear now the last part that i want to cover in this video is about update table views okay so how to create a view that can be updated and what are the different rules that you need to follow if you want to make a view to be updatable now to be frank right this is not recommended like if you want to update a table you should not be doing it through a viv but there can be some scenarios where you may want to update a table through a view maybe not you as a developer but maybe from some external system or some application that you have written there can be such cases and that is why such feature is still enabled in almost all of the databases okay now let's see what are the views that can be updatable now there can be many rules but i'm mainly going to be talking about the five most important rules that you need to follow if you want to make a view to be updatable okay so the first thing is that you can only update those views which are created using one table okay so the weave should be created using one table or weave only okay so if you have multiple table joints in your query and you created a view of using that then you will not be able to update that brief okay so for example let's use the expensive product view that we just created above okay so i have this beef and you can see that okay so i'm just going to copy this whole thing here and you can see that i created this weave that is expensive product which is basically created from just one table it has somewhere condition etc right if i do a select star from that view you can see i'm getting the data okay now let's say if i wanted to update this view so i can just say update and i'll just copy this view name that is this one and let's say i'm just going to change its product name okay so i'm just going to say prod name equal to let me give some a meaningful name so i'm just going to say airports pro okay and i also change the brand names i'm just going to say brand equal to maybe apple okay and then i'll say this is where my prod id equal to p10 right so what i'm trying to do is i want to just update this record p10 okay i want to just give some meaningful name here okay and if i just execute this you can see that the update is successful if i do a select start from that view that is expensive products you can now see that the name is updated to airpods pro and the brand is apple okay and now similarly if i just look at the underlying table okay the underlying table this view was created using this uh tb products info right so if i just do a select star from tb products info you can now see that i have this p10 product and its product name and brand have been updated so i did not update the table directly i just updated the weave but this viv was created from this table okay and the table internally got updated so if i had created a view just by using one table like a simple sql query that is like this then such views are updatable okay but for example let's say i have this another view that i created at the top right that is this one the order summary which i created this view using a query which was having three different tables right now let me try to update this query and let's see what happens so if i run similar update statement here so i'm just going to say update order summary i'm going to say okay what does this order summary have so i'll just do a select star from order summary and okay it has this many columns so let's try to update some columns so let's say set maybe cost cost equal to let's say 10 okay where i am just going to say ord id equal to 1 okay if i do an update here now it's going to throw me an error and error says cannot update weave order summary these that do not select from a single table or wave are not automatically updated okay so this is what i meant if you have created a weave with more than one table or more than one view then such views cannot be updated okay here one thing you need to remember is that we can create a weave over a weave as well okay but if you want to make it updateable then that query that you have used to create the view it should only have either one table or one wave okay if it has more than one table or more than one wave then such table such views cannot be updated okay so this is the rule number one that you need to remember in order to make your wife if you want to make your weave updateable now the second thing that you need to remember is that the query that you have used to create your weave cannot have distinct clause okay so cannot have distinct clause okay so let's try to see this also with an example so let's say i have this particular weave right again i'm just going to copy that here i'm just going to paste it here okay so maybe i'll just move this next what i'm trying to do is i'm again going to be using the same view but here instead of just select star i'm just going to use distinct okay and now i'll create this web the web is created successfully and you can see i have the data here now let me try to do a same update here okay similar update so i'm just going to say this one and maybe i'll update this pro to maybe pro 2 and everything else can stay as it is now if i try to do an update here now you can see i'm getting an error saying that views containing these things are not automatically updatable okay now there is a different way we can update it using instead of but i'm not going to be covering that in this video okay but yes this is what i meant if your view is using a distinct clause then such views cannot be updated okay so this is the second rule now similarly the third rule maybe some of you guys might have already guessed is that group by group by also does not work okay so basically ah if query contains group by then cannot update such waves okay so for example let's say i'm going to create a new view for this okay so let's say i wanted to write a query where it will tell me how many orders have been sold per day okay so something like that so i'm just going to say my order table that is tb underscore order details right and here i'm just going to say my order id and i'm just going to say count of one to count how many orders have been okay not order id may be the date right how many orders have been sold per day i'm going to name this like let's say number of orders and since i'm using account i'll use a group by here so i'm just going to say group by date okay so if i run this query okay you can see that for each day how many orders have been sold i can see it here now i'm going to create a view for this i'm just going to say create weave and i'll say maybe order count okay as and that should be fine so i'll create a view the weave is created now if i just execute this wave select start from this you can see that i'm getting the data in this view but let's say if i want to update this viv okay so i'm just going to say update order count set may be number of order equal to let's say zero okay where i'll say date equal to maybe i'll just copy this date that is 2020 0 6 okay 0 6 and 0 1 okay now if i try to run this you can see that i'm again getting an error saying that views containing group by are not automatically updated okay so this were the third rule so you cannot use distinct you cannot use group by and you cannot have more than one table so if you have any of those then such views cannot be updateable and i'm going to also mention you the fourth and the fifth rule so if query contains with clause then i cannot update such views okay this is the fourth rule and the fifth one i would say that if a query contains window functions then cannot update such views okay so i'm not going to be giving you example for width class and window functions because i think you get the idea right if your query is using any of this so whether it's having more than one table or whether it's having a distinct class whether it's having a group by clause or whether it's using a with clause or whether it's using a window function you cannot update such waves so you can still create views with all of this but just that the view cannot be updated i hope this is clear now the last thing that i want to cover in this video is that is also part of this update table view is the clause called as with check okay with check option okay now let's talk about with check option it can be pretty useful in some specific scenarios now let's say you have a supplier who is basically providing you all the products okay and let's say there is a supplier who provides you the apple products okay and he wants to see all the list of apple products that you have and he would like to make some updates if required okay so let's say how do you fetch all the apple products from your products table i can just say select star from db product info where i can just say brand right brand equal to apple now if i just run this you can see that i'm only going to get those products who belongs to the brand apple okay and there are three products as of now now i'm going to create a view for this i'm just going to say create weave and i'm going to name it like let's say apple uh products okay so this is my weave name and i'm going to create it and i'm going to give this view to my supplier who is dealing with apple products okay and let's say he wants to do some update so let's say he wanted to create a new product or basically he's going to supply you a new product and he wants to add this new product into this weave okay so let's say he will do a new insert so i'm just going to say insert into this weave and here is going to add let's say value and he's going to add all the values now remember that this is a simple sql query so it's just having one table there's no distinct there's no group by this no window function there's no with clause so this view can be updated right so that is why he's going to do an insert here so let's say what are the different columns available here so we have the product id so let's say he's going to name it like maybe p20 okay and then he's going to have a product name so let's say the product name would be by mistake instead of adding a apple product let's say he added some other product okay maybe belonging to some other brand okay so let's say he's adding note 20 okay some phone which is belonging to samsung brand so he's going to add the brand as samsung and let's say the price would be maybe uh 2500 okay just example and then let's say the product category he'll keep it like null okay and he'll do an insert now okay i'm getting some error what is that okay it should be values okay so let me run this now now you can see that the insert has worked fine and let me do a query from this weave okay so let's do a select start from this apple products weave and also select star from the products table itself so first let me show you the data in the products table now in the products table you can see that this new record p20 has been added that is note 20 samsung but the thing is this p20 record was added by that apple uh seller that we had okay or the reseller that we had so if i look at this view that is the apple products this does not have that record so basically what happened was you created a view that is apple products with an intention that this will only be used by the apple seller the supplier who is basically giving you the apple products and he can have access to all this data that belongs to the apple brand right but what he did was by mistake or intentionally or whatever he did some or basically he added a data that did not belong to his product that did not belong to him okay because he added a product belonging to samsung into this table okay which is actually technically it worked fine but it's actually wrong we should never have given him access to add any data other than apple right because he was an apple uh supplier so he should only have access to modify or update or add data or remote data which belong to the apple brand but since we did not have any checks in our weave he was able to basically add data that did not belong to apple as well now whenever you want to avoid this kind of issues we have a check called as or a special clause called as with check option so when i'm creating a weave i can give this option that is with check option at the end of my weave and then i can create my wave so what i'm going to do is i'm going to say create or replace weave and i'm going to recreate this view of now okay so everything is still the same only thing that i changed was i added this class here with check option now what this will do is once you created a view with this option whenever someone is trying to do an insert into this wave okay it will check if whatever data is getting inserted it is satisfying this where condition in this case the where condition states that the brand equal to apple so only insert statements where the brand will be apple only those insert statements will be successful if you try to insert any record where it belongs to any other brand then such insert statements will throw an error okay so let's see now we'll try to use the same insert statement here instead of p20 let's say i'm going to create p22 okay and let's say it's not 22 only and the same thing samsung or maybe let's say i'll make it note 22 and everything is the same now when i try to do an insert now you can see that sql is not allowing that to happen because it's showing an error saying that new row violates check option okay so this was a check option that worked in this particular case so this will kind of do a check such that wrong data does not get inserted into our brief okay and that is why we use check option but let's say instead of this node 22 i will insert something like maybe let's say macbook air and this belongs to the apple brand okay and now if i try to insert into this table now it works fine okay so the thing was with this check option it's like an additional verification or a check that we add to our web whenever someone tries to insert only those records will get inserted which will satisfy the wear condition okay so this is what a width check option does okay so hopefully this was clear and i think with this we have basically covered everything that you needed to know about reviews the only thing that i missed was the materialized view which i will be covering in a separate video okay now if you like this video and if you found some value from this video please make sure to give me a thumbs up and also subscribe to the channel and if you have any feedback definitely leave them in the comments below because i definitely like reading your comments and thank you so much for all the support and see you soon in the next one bye

Channel: techTFQ

Views: 167,196

Keywords: sql views, views in sql, sql views tutorial, sql, views, view, sql view, view in sql, sql view tutorial, sql tutorial, learn sql, create view, create or replace view, modify view, update view, insert into view, with check option, with check option in view, techtfq, techtfq sql, sql techtfq

Id: cLSxasHg9WY

Length: 43min 19sec (2599 seconds)

Published: Tue Feb 15 2022