views in sql
I definitely discuss about the only one database object is the table right so we are going to see another database object as a view okay see future underscore objects see what type of objects we have till now like if you go with all underscore objects okay see user underscore objects will contain fewer database objects okay see if you see the object type here see these are all objects we have plenum okay see we haven't the object large object so if you are creating any data type in the table for the lob data types object data types so it creates some object for that okay so and you created the table as well okay we haven't created a procedure and function okay see these functions this procedure and function uh I created uh for my reference so till now we created this table and the lab object so you people already know if you are creating any table with lob data type it create that okay coming to index see we haven't created any index but it creating default uh if you are doing some tar it creates some index for that I will let you know okay see while working with constraints of primary key or in EQ key see whatever the column is having uh whatever the column you are applying for the primary key or any q key it create index for that particular column okay so we have the index concept we will see in that particular thing okay see now we are going to see with the new object new object as a view see for now we haven't created a new view okay now we are going to create the view so we'll see okay see view is a virtual table does not physically exist see Mirage and Bell if you see like a mirage will uh look like I said well for you but if you uh reached the The Mirage place that won't be present okay see or you can say like uh the view is a virtual table see jamota and squeaky does not have the restaurants but they are supplying the food for us meaning so they are virtually taking the food from some place and delivering to us okay see if you are asking the food to the zomato the jamota people will uh taking the like you know we are discussing about what is view adward in which area we are going to use this view okay see if we have like a um paint speaking okay see we have three restaurants and swiggy is the delivery partner for this all three restaurants okay see you may order the food from this restaurant directly you may uh you may order the food with using this swiggy tomato our food fund this kind of foods right see meaning see what other restaurants is having like as a table the restaurants you can treat like as a table tables and this we can treat like I say view okay see the tables can store the information and you can able to use that information as well see the restaurants can able to make the foot and deliver them to the customers okay see meaning they are these are the physically exist and providing the food to us but view is a virtual table so you are ordering the food from this swiggy meaning so they have some restaurants there are some like a cooking area see the cooking area is depending on the restaurants he does not have the OWN cooking area phone cooking area see the meaning the meaning see the meaning if you are creating The View see if we have some cooking area meaning he may he may have some place or he may occupy some space in liver [Music] like making the food so for this he need to make some space to organize this all the things right see the swiggy does not have any cooking area right see meaning if you create the table the table can be stored in one place because it occupies some space so let's start from EMP okay you created the table and inserted the data see if we create the empty table also if you create like a x y z let's see that's the number of column okay we created this table okay see now if you create this table this does not have any data if you are starting some restaurant and the starting of the day that is empty but you may you may utilize that restaurant to keep some uh things okay that may be a anything okay Chase or uh uh like computer uh cooking uh cooking uh things everything right see now the meaning you occupied some space for the restaurant for your cooking area okay see the space is occupied or not you can check in user underscore segments see if you are anything like occupying in the space in the database that all the components it gets stored in in this place user underscore segments segment if you are creating the table it's the table name whatever the table name you have it creates some space name also with the same uh name the table name and the segment name will be same okay now c x y z so it occupies some space okay so but we don't have uh like data in that still it is taking space meaning you like you've started some new restaurant in some place meaning you occupied some space okay now I'm creating a view create view see the syntax create table table name column name data type right say you are creating this swiggy restaurant I mean a swiggy a food delivery app so he need to take the he need to link with the restaurants so meaning meaning see whatever the content is required for you create view view name example underscore view as select start from EMP see whatever the data we have in a EMP I'm taking this and I'm like a linking with this swiggy kind okay see whatever the data we are getting in this that I am linking with this particular View test I created The View see now the view as created and check in check in user underscore objects now the new object type is added earlier we have four types now we have five types okay and you can check in user underscore segments with XYZ XYZ you are able to see because it is a table object and take the sample underscore view object name and check whether this object is taking any space in the database or not see we are not seeing any entry for this meaning it does not occupying any space here see this occupying the space the same sample underscore view is taking the space in this table in this table see the swiggy restaurant one see if you are calling the food or making the order from the swiggy like from the restaurant one see the restaurant one is having the space and you are pulling the data or pulling the orders from this Restaurant to this place Restaurant to this place okay see you can able to you are ordering the food to the restaurant you are getting the food from the start say meaning you can able to insert the data into the table over the view or you can read the data whatever the data we have in the table that get read through the view okay that get read through the view okay see now we created The View and I said you can able to perform any operation see Select Staff from EMP now we have the 14 records and same thing I am going to select with the view as well if routine records is there any difference if you order the food through the jamota if you ordered the food directly there is no like food difference okay so there may be some cost different but there is no difference of taste and quantity so you can verify there is no difference of the data difference if you pull with view if you pull with directly of the table there is no difference of that okay see the meaning whatever the base tables you have while creating of the view what are the tables you are using in the view statement or select statement that tables are having the space the storage device is having for those tables in Azure underscore segments so they are pulling like if you are ordering the food meaning so he is coming like a one person to the this particular restaurant and getting the food and going back them meaning while retrieving the data from The View so it always face the data from the base tables of Select statement or whatever the statement you mentioned here now if you mention like two tables it faced the data from the two tables and deliver that to a data back to on screen okay so the definition is that view is a virtual table now see you are you are ordering the food and you are getting the food with them meaning view is also view is also like a table but you are not able to store any information on The View okay if you are trying to store the information it is going to the that particular restaurant not with the zomato or swiggy okay say view is a virtual table does not physically exist meaning you are not occupying any space okay view is not view does not store data in the data dictionary if any object is taking the space here you are storing any information in the database objects that all objects will be stored in data dictionaries see user underscore views user underscore tables okay user underscore constraints user underscore constraints if you create a constraint the consents will get stored in user underscore constraints this is like a meta objects are meta objects so our data dictionary tables or we can say data dictionary text say data is stored in into the base tables okay see it can be executed when called so view is created by a query joining one or more tables see if now we created with only one table of view okay now this view we can call it as a simple view simple view so if you are creating any View you are creating any View with that we can say it's a simple View let's say simple Cube now see now I am trying to recreate with the same uh like view name it is giving the error so maybe I I was trying to make some change here like EMP number instead of taking all the columns I'm making with them only few columns okay still it is giving the error okay in such place you need to drop this View and recreate this view right from this View and recreate this View okay now the same way the table also will do the same the XYZ table now if you are trying to do with another column also like C1 is the number so you cannot able to do because the name is already fixed for one object in this case you need to drop this table and ready create it right so instead of dropping and recreate that component if you if you want to made any changes in this select statement okay so those you know for that purpose use like a r replace keyword R replace keyword so the view is already created but I am trying to do some changes okay now it is it is not giving any error it is not giving any error or replaces the keyword which is used to drop and recreate kind but it won't drop the object so it like it work as a drop and recreate kind okay see if you drop the object okay say if you remove the restaurant from this figure okay or anything okay see whatever the agreement we have from this uh user to this user so that get to cancel so now you handle this restaurant for the new person meaning you need to give the privilege or access with these two this two part counterparties need to be make some agreement meaning like you need to provide that whatever the access we have in the earlier that access you need to give back them like it may be a canceling the permission or giving the permission also you need to give So to avoid that things so if you drop and recreate the whole uh privileges are the access or anything right so that get the canceled so instead of that if we use like R replace keyword so what are the existing privileges we have that keep same okay that keep same that persist is same we can say okay see for that reason we'll use or replace keyword instead of drop and recreate okay now now you can see like if you are creating view with one table that we can call it as a simple view okay in simple view you can able to I said like you can able to insert the data into the view okay insert into EMP and I'm trying to insert the data with only sorry in the base table so you need to verify see if restaurant people has updated their items that need to be reflected in your swiggy and jamota as well okay so meaning now I'm like inserting one data with one two three we have earlier we have only 14. now I am in setting the 50th record 15th record now see now we inserted the data and try to pull the data with table and as well as the view so earlier we are saying like 14 now we are seeing like 15 records in the base table and try to pull the data from the view as well okay now we are seeing like 15 records in this view as well because if you are updating something from restaurant that automatically get reflected anywhere uh food delivery partner okay see now meaning you are able to insert the data into the base tables of the base table date whatever the uh change data we have in the base table that automatically get affected in views okay so try to insert the data through the Tomato also you can able to suggest some things with the restaurant so the suggestions you can send to the River delivery app now try to do the one to four one to four width ABC see now you inserted the data in this and verify in your view you are seeing like 16 records now you are seeing like a 16 Recons and check in your original table launch select start from EMP this is also having so you can able to perform the DML operation with the best through the base table and you can able to perform the dmls on viewers view also now I said like view we are able to perform the DML operations right now I am trying to create with another view with two tables with two tables so mentioning like a complex View complex so view instead of going with the EMP BBT where so EMP athy number equals to D beta DPT number so I'm just taking like a d name RC I'm taking one column name from the beginning um in Department date just I am selecting the data what are the data I required for me I'm like selecting this okay so these three columns I am making with the as if you know okay now I created the view now I am trying to insert the data into the EMP table okay into the EMP table and as well as the view okay now see now I am trying to insert with one two five into five actual I inserted here 12 I inserted here Sun and check in EMP table is having a okay okay see I roll back these changes till now we haven't committed I use the rollback the whole data is back no we have only the 14 nikons we have only the 14 Recon see you like perform the DML operation in session one and you are trying to access that in session two meaning if you are doing like a uh some deployed or withdrawal in some uh your home Branch or some X branch and you are trying to check your balance okay or you are trying to check your balance or what are the recent transactions you perform okay see the open transaction is not committed okay so the deployed slip you provided and cash also provided say he entered he is trying to enter your transaction details meaning the account can be locked for a while a while meaning until like you commit the transaction or deploy it uh the the transaction need to be complete then only this transaction will be reflected in your mini transaction or the detailed transaction right see for the same way for the same way now you did the transaction here and you haven't committed a roll back see if you commit this the transaction get reflected in all systems else it won't be reflected in All Seasons it but it will be reflected in your current Branch current Branch meaning it's a current session okay see now now we did like a insertion in your base table and try to access this we are seeing like a 15 records now and access like a complex View not the simple view it's a complex view we are of something okay now try to insect and pull the data also if you have any changes in the if you have any changes in the now I'm using the rollback and I'm trying to insert in this okay EMP see now I'm not seeing any okay okay so I did the one mistake here see if I use this join you will get only the 14 records because because whatever the inside that record we have like 12 that does not have the department number value if you inside the department number then only this value will be present in your view or this query because you are using like a inner join so in this case in this case insert like a one two three and the department name also department number also department number I say then or 40. and now pull this data now you are able to see with the 15 records okay now we are able to see with 15 records now I'm creating The View okay now I'm creating a view and pulling the data from both places EMP is having 16 records the EMP table is having 16 returns so one of the record does not have the department number that is not getting pulled in View okay so now see we are seeing like a 15 records 16 records okay now you can pull the data with complex View so you are saying only 15.
now try to insert the data into the number so instead of one two so go with them 40. go with the quantity see it is giving the error our human mentioned like Department name not the teeny okay so I'm inserting like see it is giving error for you inert modified a column which maps to your non-key preserved table cannot modify which maps to non-key preserved table see these changes you are applying on the one view but that view is having like a thus view is having the two tables so if you are trying to do the changes on this view the changes need to be reflected in these two tables not with only one table so key preserved tables meaning so what are the changes you are doing this is for only this particular table so you need to mention like if it is a simple view meaning simple view meaning you are creating with view only one table of uh view okay so that we can say simple view so what are the changes treat like I say one parent is having only one child see whatever the parents earning money that can be preserved for the that particular child only if you have the two Childs the richest possibility so 50 50 maybe uh say he those we will get or he may get like 100 for the first sale or not with anything for the second child so the reverse can also can happen right so meaning if you have only one key preserved table now keep Reserve table meaning if you have only one table so that changes will reflect for that into the base tables you can able to perform into the anything like parent can able to do sales also can able to do okay cleaning so if you have like only one restaurant so you can able to do anything if you have a multiple restaurant the rules can be passed for the all the restaurants okay so for that reason you cannot able to make directly these changes these are all the people need to access the rules can be passed to this all the people and need to access so to pass these rules and regulations you may concern with some lawyer and make the agreements so this is what I'm going to change this is earlier chain I'm going to make these changes so you can intimate this all the restaurants okay the meaning the meaning you need to keep some uh like if you want to do this DML operation on this complex type of views you need to put some trigger on top of the view see we have some concept like trigger that will catch the see if you are performing like a a like EMP number and e-name column so that can be related to the employee table so that you need to mention this rule one is for the restaurant one rule 2 is for restaurant three rule three is for the restaurant two so you need to mention the detail um detail uh like a thing for them to understand clearly okay so if they are accepting that they are allowing to like make the uh like a orders from this sweetheart we need so if they are like accepting like the insertion adaptation so if you are doing this insertion this data EMP number and e-name data need to be inserted into EMP table this is what you need to mention in the trigger body okay see this can you can understand while discussing about the trigger Concept in the pl SQL not in this case but so the the compute like uh the company people will ask like is there any way to perform DML on complex views as well yes we have the way and view we can able to create some instead of trigger instead of trigger that can be help us instead of instead of trigger okay so this can help us to perform the DML operation on the complex view if you are able to perform uh like dmls and the complex view what are the base tables we have that all the things can be affected meaning the changes will come to this base table transfer okay now we learn like a simple View and a complex view see treat like I say so what is the advantage of this view so where we have like a complexity of queries we need to write on the daily basis see now we have only one line of query you are creating this see where we need to create like uh where we need to write the query with hundreds of line code and repeatedly on the daily basis in such place instead of writing this query manually and I can create the view see if you create the view the views are storing in user underscore views so how we have like user underscore tables the same way we have user underscore views is the place to get store all the views see if it is a single view how we created apply number employee PMP if it's a complex view we created with a TMP number we need D name see see this same statement as stored in this place a same select statement is stored in this place text field this is the text field is storing this so the advantage of this view you can able to store the complexity of queries permanently in the database permanently in the database and another Advantage another Advantage see now we have like a EMP table so now I need to access I need to access the 203 employee information with my manager or see we have like manager ID 101101101 see these three people is working under manager one zero one okay see the manager is asking asking the uh HR team or admin team so kindly provide who are working under me so provide all the employee information for these people see in such place you need to provide the employee table access to that particular manager user okay see you cannot able to provide with part of the table data so you cannot able to provide the grant or revoke on the data level you can able to provide the grant or privilege or select permission or update permission you are able to provide okay we have like a Grand select on EMP to Etc see this is the privilege you are going to provide on EMP table to HR user see you are providing the access you cannot able to provide the access on the data see like I need to provide only the like 10th Department information EMP where I need to provide like a DPT number equals to 10. see I need this information need to be accessed that partial Department see I cannot able to provide access this particular information only I need to provide access the whole table itself not with data level not with the data level okay see the advantage here you can able to like hide The Unwanted data so you can able to hide The Unwanted data so unnecessarily unnecessary data you no need to share with any other things see meaning so whatever the data you need to share with your manager or with that particular person create The View with that particular data and provide access and provide access on top of that particular data now see now I'm mentioning like a view one as now created The View as a V1 instead of providing EMP table access I can provide now see if you go to this HR user select start from nope 14 dot EMP so he can able to access all data instead of this providing the original database objects I can provide the V1 we need to access only the 10th Department information not with the any other information right so this is the advantage of this another advantage okay so views are storing the complexity of queries data in in the database permanently and Views are helping to provide the security to the data it is providing security somewhat security to the data as well okay so now we covered like a few things here like and we have some other things we'll discuss by tomorrow see we're left with only it may take like another 10 minutes if you go through this I will let you know okay so we are performing the dmls on the simple View right and complex view also we can able to perform okay in simple view also we can restrict the DMS so now we are performing the dmls on a simple View right now you are able to insert the data into the simple view right instead of this now I am creating with the read-only views so I'm changing just I'm using some keyword with read only in the end of statement the same statement I made it as a with read only see how many data we have how many regards we have 17 now I am trying to insert into the simple View data see it giving the error cannot perform DML operation on read-only views read only views okay and I will let you know some other things by tomorrow kindly uh practice with this and get back to me if you have any
2023-01-19 05:22
