 ඄ జ੍ররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররররর నిన్ం్చత్లూచిందిందా కాందాత్టందాత్నైండిరిఆట్చకాదర్దానుందికాసిదారార్దుత్ంత్నులిమీటార్యసుకిసినుసికేసింది. आदष मोडन्योच मोझ़ लम्वाप्दो materials. पोच् Honey आग परायमस्नक्वने। आम की प्आच ल grounded तराइ� vampire अग्ेट मेशे उंचो siendo अपको papel तराइच लाऊर a-sul Kamakon మార్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల్ల ueprint ధిస్నాత్లిదిందునికి కైచేనిమిని పిరిస్నెస్నికాకాస్దీకుకికైపిస్సనెదిఔందికాందింది పీరిసినుమాతికాయాయకి. లామారరంని. హా� నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిని నిన I can run a query on a view. Select a name from H8 students where S room is greater than 300 and SCPI is greater than 9. So I want to get all the top performing students on the third floor of my hostel. Now when I run this query, I will get only the names of people staying in my hostel. I will not get all students from all I had. Meaningful for some secretary who is handling accommodation or whatever. You see the value now. I have defined the base tables. I can define a view. Notice that, let's go back to the previous slide. Notice that the view is defined by a select query. It therefore implies that the select query can be as complex as you want involving multiple tables. Whatever gets selected as a result is the view. So I can have very complex views which are created as entities as single table. You will get a single table at the end with the appropriate schema which will now be available for being treated as a table, additional table. It's a dummy table however. Is this clear? This is the notion of the view. Here is the problem. Then I say select S them from HH students where S room is greater than 300 and S CPI is greater than 9. Suppose I created that view. Let's go back to the previous slide. I created that view yesterday. Then one month later, I run this query. Within that one month, some students have gone from second floor to third floor. Some students would have got 9.3 CPI instead of 8.9 earlier. Some students have got 6.4 instead of 9.1. Anyway, it's possible. What will happen then to this query? After all you want this query to return results which are true at the time of running this query. Not true at the time of when you form the view. That is the expectation. So SQL guarantees that by saying that when I create view, it only creates the schema of the view. It's an empty table. It's an empty table. What it means is every time I run a query, this or any other query on that view, at that point in time the actual data in that view is created by running that view definition query. Which means SQL guarantees that any time you run a query on a view, the information that you will get will be absolutely current contemporary information. Because the actual query in the view, let's go back to this thing. This is the query. By this query, only a coca of a table will be created with its schema. No data will be inserted. When I run a query on the view like this, first it will run the original query which created the view that is called materializing the table. Effectively you have an empty table and you are filling it up with appropriate values. That filling up is called materialization. Materialized view is a very strong research topic. Because when do you materialize? If every time you have to materialize, then how much work that fellow has to do? Suppose you have data which does not change normally. Is it worthwhile to keep materializing? So there are cases where you want to maintain a materialized view. Particularly when you look at statistics calculation etc., where you know that a few changes here and there will not change the overall statistics. You might want to keep a view materialized and update that view only once in a month, once in a 15 days etc. That's an important consideration in constructing information system. What we are discussing here is the implication of a view. So create view creates a schema with a table. It's an empty table. It's an empty logical table, no permanent table. Anytime I run a query on that table, first the view is materialized and then the query is run. Is that okay? Is that clear? So this is a sample query on a view. And the create view statement only creates the schema of the view. It is materialized only when a query is executed. Temporary views are created as a result of a query. What is the purpose of this? Look at this. Select SH, CPI average from select SH hostel AVG SCPI from student grouped by SH hostel as result SH, CPI AVG. This query is actually creating a view with schema SH, CPI AVG. That means it has one hostel number, average CPI, one hostel number, average CPI. If I want to treat this result as a table in an immediate query which follows, then this would be a temporary view. We will not go into details of this. There are so many details again. You can read more about views and find out things there. We will now come back to the actual data manipulation. We said DDL, data definition language, DML, data manipulation language and DCL, data control language. In DML we have studied select statement which is actually not a manipulative thing. It is only query. It does not change value. There are three statements which change values, insert, update and delete. We will look at each statement one by one. They are very simple statements. If you want to insert record of a new student joining the institute, insert into student values this, comma this, comma this, comma this, comma comma. Can you figure out what this would mean? This is whole number. This is name. This is hostel. This is room number. And what is this? CPR, but no value is given. This means null is inserted. Very good. Provided that column is nullable. If that is not nullable, this insert statement will result in an error. If that is nullable, this will insert this row. If I want to insert all new students who come into IIT, then actually I will have to execute one insert statement like this for every student. It is a painful thing. It is a painful thing. For every student I have to type insert into student values this, this, this, this. What would you prefer as information system people? But those who know some programming and those who know how data is organized. For example, if 600 students join the institute, it is quite likely that you will have this data in some text form in a file. You can always create that, you know, roll number, name, hostel number, room number. This data you can create. Now you got a file. Now if you really want a square statement to be typed for each student, that's a painful task. In short, in such situations what you are looking for is the facility to bulk up it. That means you want to tell a scale engine that I want to insert in student. But read from this file, take each row as an equivalent of insert statement. Create your own internal insert statement that you want, but insert all of these. That would make sense, right? Such a facility exists in most of the database products, not in most, all database products. Unfortunately, that syntax is not a standard syntax in SQL. It actually came in SQL 99, but that is not often followed. But those utilities exist. Here is another example of insert. Insert into student s-roll, s-hostel. Assume that I have only roll number and hostel number available. I have nothing else available. Assume of course that all other fields are nullable. Then if I prescribe a subset of a schema in the insert statement, I am required to give only two values or only as many values as there are attributes maintained here. Ordinarily this will not be done. Again, ordinarily as I said for bulk insert you will have some utility or some format of SQL. But if let's say one or student joins late after five days, then you don't want to insert his data into a file and then run the bulk utility. You want directly things to be inserted and the insert statement can be written. Here is another insertion example. Insertion into range select s-roll, cs-771 from range where c-code equal to cs-634 and s-roll in select s-roll from student where scpi is greater than 9.5. Without explaining anything can you figure out what this thing is doing. It is insulting into range. That means it is creating new registration records. But without any data values being given. So can you figure out the meaning of what this is trying to do. To understand this let us go backward. It is obvious that an inner query will be executed sometime or the other. What will this inner query do? Select s-roll from student where scpi is greater than 9.5. That means I am getting all roll numbers. Who have got scpi greater than 9.5. And all of those who are registered for cs-634. Let us say head of the cs department decides that people who are very great performers in academics and have registered for cs-634 will automatically get registration for a phd level 7 level course. Now instead of telling all of you saying those who have 9.5 please end the roll I can run an sql query saying anybody who got scpi greater than 9 anybody who is registered for cs-634 take that fellow's roll number and insert that roll number and this specific code as a duet because in range I need to insert minimally these two as the primary key. So you can see automatic registration. So insertion is not necessarily by giving explicit values as we saw in this last slide where you give explicit value. Insertion could also be based on some business logic which has been defined. Clearly such decisions will be taken by the CIDET at an appropriate point in time. These decisions will be only valid for that semester. Next semester some other decision may come. Consequently an information system for which you have defined schema written all sql queries defined screens etc. You may still have to write some small code to reflect that business. That is where sql comes in handy as opposed to conventional programming. No wonder these queries cannot be written by end user or operator but programmers can do that very quickly without having to spend time. This one you will like. You want to modify values. So how do you modify values? Modification is done by update state. So you are asking how will I change? You will say update range. So you are defining the table that is to be updated. Table updated means some rows have to be updated. Some of all rows. This statement here says set grade equal to AA where C code equal to CS634. What it means? Change the grades of all CS634 students to A. This will do it automatically. Now you will know why such update powers cannot be given to all students. Okay. Would make sense. So this will also tell you about the need for security. Very tight security. But this here demonstrates the power of sql. You can update a large number of rows by automatically based on some business criteria which you have taken a decision. This I like. Increase faculty salaries by 50%. Update faculty. Set salary equal to salary star 1.5. Please note that this statement is almost like an assignment statement in a programming language. It is not an equation or a condition. So it will first take the current value of salary multiplied by 1.5 and replace the old value by the new value. Set statement does that. Easy you can understand. Okay. I can of course say set salary equal to 14500 if there is an explicit salary which is at the time of appointment being given. That is possible of course. Delete. Delete registration records of CS634 for students with CPI less than 6.5. This is not actually a hypothetical case. This is a post graduate elective. And as you know in IIT post graduate electives can be taken by undergraduate students whose CPI is 6.5 or more. That's a rule. Now suppose some student registers for it because he has talked to me and he has asked me is that CPI required? I said no, this is a fundamental course in information systems. You can always take it. So he wants to register for it. But the system must not permit because there is a synod rule. So if some registration has gone like that this delete statement which says delete from range where s roll in select s roll from student natural join range where CPI is less than 6.5 and c code is equal to CS634. Remember we are not deleting the student we are deleting that row of the registration record which matches this. Of course this does not distinguish between undergraduate and post graduate students. So our meaningful interpretation of this query will be that for some reason Phatak wants only 6.5 or more CPI fellows to be in the class. That is the interpretation. If you want that undergraduate student condition then you must either have another flag and attribute in the student table which will identify that student table as UG, PG, whatever and then you will have to say select this where CPI is less than 6.5 and c code is this and the flag is equal to UG or whatever. Right? Is this clear? So delete is a very powerful statement again you can delete not just one row but multiple rows. If you say delete from range where s roll equal to some roll number then all registration records of that student will be deleted. What will happen if you say delete from range without any wear condition? Yes. So here is an example remove all students of hostel 8 delete from student where s was still equal to 8 fire all faculty delete from faculty this is a very powerful statement you will agree that therefore whatever access rights may be given to general users delete access rights must never be given because you could cause a chaos. That also stresses both the power of SQL for data manipulation and the need for the highest selective security you will see how that highest selective security is provided but are you clear on this? Basically insert, update and delete are the three statements which can cause change in the data in the database and no other statement exists. So in fact I remember Divakar Nigam who ran a company called Softek and wrote some of the finest compilers in Kobal and Fortran and so on when he was trying to construct a database he was trying to tell me look I have written compilers for languages which have so many commands SQL statement has worked select insert delete update then I told him that no there is query optimization there is integration he figured out when he tried to construct that he said okay let Oracle and others write databases now he develops document management system but please understand the power of the language is not defined by the number of different commands but how expressive those commands are what action those can cause in terms of information system you want to change the data insert delete update or you want to extract the data select nothing else is required enough complexity can be built by using the inner queries and nested queries and so on and now we turn our attention to rights security authorization you must not be able to trample with the security of the system in terms of data so the powerful SQL features can be misused to corrupt the contents of our database that is why there is one Brahmarshi defined for database he is called database administrator he is the king emperor of database nothing happens inside that without the permission of the individual called database administrator he might be a experienced programmer but even the director of the institute will have to get authorization from that dba to do anything with the database you will agree that such a centralized security for the major things have to be created and database permits you to do that the other users other than database administrator the database administrator has full rights of access consequently he has the total responsibility if the database something happens is the dba who is hand first so dba has to be a very trusted individual apart from being an experienced individual okay the other users are given selective permissions using grant statement grant leave grant this privilege grant that that is the word which SQL uses this is not part of dml okay grant is part of which part dcl the data control language these permissions can be revoked later so i may give you permission but suppose from a UG student you become a PG student or from a research scholar you become an employee on a project i may have to revoke some permissions i might have to give you some new permission all these tasks are done by the dba we talked about views insertion of values in a view actually means modification of values in the base tables and modification is permitted only in simple view please remember what i said view is a khoka a table band table so when you say update a view ordinarily it will not mean anything because view is not a permanent thing at all so if at all you say update this view means you don't say view or table you just say update some name if that is a view SQL interprets it to being base tables now in a complex view which is been created by multiple joins it is not very clear that update or changes insert delete could make sense consequently SQL says modifications are permitted only in simple views which are actually subsets of signal tables but otherwise if you want to insert delete update you better do that with the base tables this is no permission given granted required if it is permitted to modify values which we shall say how that is to be done then this is the only thing that you should be careful about schema modification let's say you have designed the schema student table course table, race table maybe some other tables attributes etc now what will happen over a period of time the database and operationalize the information system people registering people like us will be giving grades computing cpis all this will continue all of this will cause changes into the data in these tables but will you agree that the schema of these tables is unlikely to change every day every hour every minute in fact it should not change at all for a good design the design should stay for years of course there is always a requirement that some new functionality of my academic business might demand some additional field in which case I will have to add columns to a table or I will have to add new tables or I will have to delete some existing table this activity is called schema modification and what is being asserted here is while data changes will occur regularly schema changes are very infrequent not only that they must be handled with great care for example I change the schema of a table where scpi which was defined I don't even add any new column delete any column but I just change the characteristic of a column so from numeric 4,2 okay I change it to numeric 4,3 something must happen inside where old values are mapped to this new characteristic 4,2 to 4,3 means what 3 digits are decimal point only one integer digit so people who have 9.9 cpi will get 9.9 people who have 10 cpi will get 0 because only one integer can be kept there so it has to be done with great care alter table command is the command which permits schema modification when the table structure is changed what happens to values of existing and new columns is to be understood suppose I add a new column then when I alter a table a new table is created where existing values wherever pertinent will be migrated to the new table if there are new columns it will not create data from the sky it will expect data entity to be done through insert statement later but appropriately then this column should be nullable because only null value can be given at that time when I migrate the data remember alter table will change the structure of an existing table means essentially it will create a new table and delete the old table that is what alter table means thank you so much