 So, the first part of chapter 5 advance SQL is on not on SQL itself, but on how to access SQL from programming language. And we are going to focus on Java, although it is almost entirely equivalent way of doing it from most other languages such as C, C sharp and others. There are number of different ways of doing it, but all of them have basically the same underlying principles. So, let us cover that quickly. So, what are, what is JDBC? And it is, there is also ODBC which is used for other languages. JDBC is ODBC for Java. It is basically an API for a program to interact with the database server. So, the API allows the following functionalities. It allows you to first connect to a database server, then it allows you to send SQL commands to the server. And if the command is a update command, it will tell you what happened, did it succeed or not. If the command is a regular SQL query which could return many tuples, it gives you a way to step over the tuples, iterate over the tuples which are returned by the SQL query. As I said, ODBC works with other languages. In fact, there are variants of ODBC for pretty much every language that is out there today. But we are just going to stick to JDBC. Now, there are also some other APIs, ADO.NET and so on. Microsoft has a whole bunch of this. They are all similar. There are minor differences syntactically, but at the heart all of them let you do exactly the same thing which is connect to a database, send queries, get results back and step over the results. So, in JDBC, in fact, like ODBC, JDBC also offers many more things than just the minimum three things they showed you before. Not only does it allow things for data, getting data, it also supports metadata retrieval. For example, to find what relations are present in the database, what are the attributes of a relation, what are the types of the attributes, what are the foreign key and other integrity constraints, primary key and so forth. All of this is part of the JDBC API. So, it is actually pretty convenient to communicate with the database. The second thing about JDBC is the standard. ODBC is also standard. So, the API functionality will work the same way whether you are connecting to SQL server, Oracle, PostgreSQL, whatever. The same API functions work regardless. So, it is database independent. It turns out that metadata features are provided by all databases, but the exact syntax is totally database dependent. No two databases do it the same way. So, now coming back to JDBC, here are the details. First you open a connection, then you have to create a statement object and then you use the statement object to execute queries and to fetch results. And then you have to close these statements and connections when you are done. And JDBC also has an exception mechanism to handle errors. All of this is defined by the API. So, let us look at small JDBC program. This program is actually split over two slides and this is the kind of the enclosing program. So, here all that this slide shows is setting up the connection and creating a statement, the actual work is not shown in this slide that is shown in the next slide because everything would not fit in one slide and then it shows the closing and so forth. So, what are the things that go on here? This is just any program and what this program does, this is function does is several steps. The first step says class dot for name Oracle JDBC driver, Oracle for Postgresql there is a variant. If you see the assignment, there is a sample program with the corresponding things for Postgresql. So, what is happening here? As I said from the JDBC API, you can connect to many different databases. The way JDBC even ODBC for that matter does this is by having a separate library for each database. So, Oracle provides its library which implements the JDBC API for Postgresql, there is a library which implements the JDBC API and so forth. Now, you can actually load multiple of these at the same time. So, normally if you load one version of library, you cannot load a conflicting version of the same library that creates problems. But in the context of JDBC, you sometimes want to connect to more than one database at the same time. Maybe you will read data from one database and then copy that data into another database using your program. So, I need to concurrently have connections open to Oracle and to Postgres, let us say. So, I want to be able to load the implementations provided by Oracle and by Postgresql. And this class dot for name essentially does that. It loads this code in this case the Oracle JDBC driver. You could also load the Postgresql one at the same just after this. The next step is connection equal to driver manager dot get connection. The driver manager is a common function regardless of the database. But in the first argument to the driver manager, you are giving a protocol name. Just like in your web URLs, you say HTTP colon or HTTP colon. Here, you are saying JDBC colon Oracle colon thin. This identifies the protocol you are using. And more relevantly, it associates with this connection. It associates a particular driver. When I say JDBC Oracle thin, the driver manager knows that this particular library which I loaded supports this protocol. So, this connection will use this library which I loaded. If I load the Postgresql driver also and then I create another connection. Instead of Oracle, I use the corresponding string for Postgresql, then it will use that library for that connection. So, for each connection, it can use a different set of functions. But the API is the same. The other things here are this one db.dl.ed, you use the sample thing which gives the host name. 2000 is the port number. Univ db is which database to connect to. Some of these are optional. It will take the default if you do not specify it. For example, for Postgresql, the default port number is 5432. So, if you do not specify it, it will use that. But if you set up Postgresql to run on a different port, you must specify the port number here. Again, the database, there is a default database. I think it is Postgresql. If you want a different database, you can specify it here. And lastly, use an ID and password to authenticate yourself to the database. So, at this point, if everything succeeds, you have a connection object which has actually made a connection to the database. Now, if something fails, there is an exception. So, this part says try catch. So, the try catch is Java syntax for saying try this. If there is an exception raised in this, jump here and then take the following action. The following action is simply printing out the exception and then exiting from this function. So, now, once I have created this statement, I do the actual work which we will see in the next slide. But after the work is done, it is important to note that we should close the statement and the connection. This turns out is an important thing which many people who are familiar with Java do not take care about. So, if you program in C, you know that when you allocate memory, you must free it up. If you do not free it up, garbage accumulates and eventually, if you run the program for a long time, you may run out of memory. People who program in Java do not bother about it because Java does garbage collection automatically. This is with respect to memory. When you do new, in C, you must do free. In Java, you do not. But it turns out that for connections to a database, it is not possible for Java to garbage collect connections. And the thing is that most databases will support only a limited number of connections. The default may be a few hundred. You can push it to a few tens of thousands, but that is usually a limit. Beyond that, the database will not accept any more connections. So, what can go wrong? When you have a single program like this, you do not close the connection, no problem. But now, supposing another person executes a program, again the connection is not closed, no problem. All of this is fine if you have standalone program. But when you execute these from an application server, this happened to us in IIT Bombay. When we first used JDBC to connect to our databases for student registration, the programmers would write the program, test it out, works beautifully. Come registration day, hundreds of students start registering very soon after each other. And after some time, the connections are not closed. And we run out of connections to a database and the whole system grinds to halt. Students can no longer register. And it was very unclear in the beginning what went wrong. Why is this happening? The CPU is idle, the database server is idle, but the application is not able to make progress. It turned out that people had forgotten to close connections. So, when you write code in general, make sure you close connection. Now, let us come to the meat of what happens inside the code. So, the same scaffolding going back to the previous slide, this is the same scaffolding. The actual work which is done here could vary depending on what we want. So, the first example shows an update. The update is also in a try catch. And what does it do? It says statement.execute update, insert into instructor values, blah, blah, blah. So, it is executing an SQL insert query. And again, if there is an exception, it prints the exception. If there is no exception, the insert would have succeeded. So, that is an easy case. Now, here is a slightly more complex one, where we want to execute a query and fetch the results. So, what is happening here? Statement.execute query. And here is a query, which is select department name, average salary from instructor group by department name. This is a query we are familiar with. How many results does this have? It has one row per department, which has an instructor. So, the result of this query is stored in a result set object. So, that variable R set contains the result of this query. In fact, most implementations will evaluate the query, get the whole result and send it back. And it is now available inside this result set object. There are implementations, which if the result is too big, will send a few hundred tuples first, then more and more as you need them. But the default is everything is sent over and stored here. Now, you can step through all the tuples in this result set using a while loop like this. While R set dot next, what does R set dot next do? It moves to the next tuple and if there is a tuple, it returns true. If you have just fallen off the end of the result set, it returns false. Initially, you are before the first tuple. So, the very first R set dot next moves to the first tuple and returns true, assuming there is one tuple. If there is no tuple, it will return false right away. So, assuming it returns true, there is a tuple. What is happening inside here? System.out.print line is just printing R set dot get string department name. What is this particular thing doing? It is retrieving the department name and the get string says that, please return this value as type string. The second one is similarly R set dot get float. It is saying return the result attribute as a floating point type. Now, note here that for the first one, I have used department name as the attribute name and that the attribute name in the SQL query. What about for the second one? What is the name of this attribute? The name is not average open parenthesis as we close parenthesis. The name depends on the SQL implementation. If I had set as some name here, the second one could have been R set dot get float and then use that name, but I did not do that, but JDBC gives me an alternative which is positional notation. So, this two here means get the second column of the result. In this case, average. But for which row? So, this is a loop. The very first time it runs, it is going to retrieve these attribute values for the first row. The next time R set dot next moves the pointer from the first to the second row and then the same line here will get the department name and the average salary for the second row and so forth until we reach the end and there are no more rules. So, that is a quick overview of the basic things in JDBC. Now, a few more details. Note that it is equivalent if you say get string department name and get string one because department name was the first position. So, I could have used either one. There is a slight complication with null values. Supposing I want to get an integer value. So, I can say int a equal to r s dot get int and the attribute a. So, this returns a value, but what if the value in the database was null? What value is returned? Some value has to be returned because an integer, but what is that value? We do not know it. Some default in the system, but we want to know if it was null. So, immediately after a get int or get float or whatever, immediately after that if I execute r s dot was null, it will tell me if the last value fetch was null. So, in this case immediately after this, I call r s dot was null. If this integer was null, I now know that it was null and I can take suitable actions. In this case, I am just printing got null value. It is just a toy. It should be system not system. It is just a toy example, but in general, if I need to deal with null values, I can explicitly deal. I know it is null and I deal with it here. I actually have more details on JDBC coming up, but before that just a small quiz question. I have actually not told you the answer to this question, but guess what will happen. So, read the question. I do not expect you to actually get a right answer because I have not yet told you. So, let me explain what happens here. Option a is a run time error occurs since the type of salve is numeric not string. Well, actually this would not happen because the JDBC API can do type conversion. Salve is a numeric, but when I say get string, it can convert a numeric to a string. What about the other way? If I take a name and I say get int, what will happen? It will fetch the name and then it will try to cast it to an integer and if it finds it is not actually an integer value, it is going to give a run time exception. But in this case since I am saying get string, a run time error will not occur. A compile time error will not occur because there is no, the Java compiler actually has no idea what is the type of salary. C is the correct option. It automatically converts numeric value to a string. D is not a right option. Moving on, the next statement I want to cover is the prepared statement and this is actually very, very important. Turns out the previous statement execute query which we saw directly doing execute query on a statement is fine in many contexts, but it is a huge security risk in certain contexts and as far as possible you should avoid it and I will explain why. So, first what is the prepared statement? So, here is an example. Prepared statement is connection dot prepare statement. Earlier what did we see? Let us to contrast it here. We said statement s t m t is connection dot create statement. Instead of connection dot create statement what are we doing? We are saying connection dot prepare statement instead of create statement and this prepare statement in this case it is an insert, but it could equally well be a select or whatever. It does not matter, but look at something interesting here. It says insert into instructor values, question mark, question mark, question mark, question mark. What are these question marks? They are actually place holders. They are place holders for values which we are going to provide subsequently and this is actually the most important motivation for prepared statement that there are values which we are going to provide subsequently. What do you mean by subsequently? This part insert into instructor values is a static string. I already know what query it is. The values here in this case are all provided in the program, but the use case typically is when the values input from the user in some way. So, but here just to illustrate the feature I am using constant. So, what I am doing is a piece statement dot set string 1 to triple A double 7. What is that first field of instructor? It is I D. So, I D is being set to this value. Set string 2 to parry. What is the second field of instructor? It is name. So, the name is set to parry. Set string 3 to finance. What is the third field? It is a department name. Department name is set to finance. Such int 4 to 125 k. What is the fourth field? It is a numeric and I can set it to set int will can convert int to numeric. That is not a problem. So, the value which is set is 125 k. Now, I have set all the fields and I say piece statement dot execute update. In this case, why execute update? Because this is an update query. If this were a select query, I would have said execute query. So, now what does this do? It executes it. It may throw an exception. Assuming it did not, the next step says set string 1 to triple 8, 7, 8. It did not go ahead and set these values. But what happens is the last value which was set continues to hold. So, what this does? It ends up creating another instructor with a new I d. Instead of this one, this is the I d. But the other fields, peri, finance and salary remain the same. This is actually quite idiotic, but it is just to illustrate the feature that you can actually just change some field which you want and execute update. It is useful sometimes when one field keeps changing, but some other field takes a value initially but does not change. So, we can reuse it. So, as I said piece statement dot execute query can be used instead of execute update to get a result set back. Now, here is the warning. The warning is use prepaid statements when you take an input from the user and add it to a query. Never create a query by concatenating strings which you get as inputs. So, to illustrate what is this issue? The same thing, supposing I got these 4 values from the user as input and they are in variables. The variables are I d, name, depth underscore, name underscore is missing and balance. So, there are 4 Java variables which contain these values. What I am doing here is insert into instructor and I am creating a string. Note this carefully the quotes which happen here. This double quote is a Java double quote. Insert into instructor. All of this is an SQL query. Then note the single quote which starts a string and then the double quote which starts an SQL string, sorry. The double quote ends the Java string plus is a Java string concat. I d concatenates a value of the I d attribute and then plus double quote begins Java string. Single quote in this case is the ending quote for I d here. So, starting quote the I d value ending quote comma then here is another starting single quote in SQL. This is an ending double quote for Java and so forth. All of this actually works if you try it out with ordinary name. But the first time we realize there is a problem with it is when we try to load some data and there was a name like this Disuza. What happens with Disuza? There is a opening quote and then the name is concatenated quote Disuza quote. But what has happened here? The first quote started an SQL string. D is part of the string. The apostrophe in Disuza is a quote. It is the same character and that actually ended the string in SQL and after that Disuza is part of the SQL statement but not inside a string. This is actually gibberish as well as SQL is concerned. So, what happened when we first encountered this long ago when we first started playing around with JDBC was that certain input data would cause an exception and make the program crash and this was any data with a quote. So, that is when we realize that concatenating strings like this is a bad idea and then we switch to prepared statement. So, at that point we only thought it is a nuisance and the correct way is to use a prepared statement. But this is as long as nobody has names with quotes in it. By the way, this bug was introduced by programmers in other applications. In IIT Bombay's student database, the program which took in student data was would take a college name and they forgot this thing. So, people from Father Agnell's College, people from St. Francis College and various other Catholic colleges with a quote in the name. If you go back and see which college they are from, there is no information because that query actually crashed. For people from other colleges without a quote it worked and nobody realized this during testing. This was found out only when we were looking at the data later on and found many students with no college information. This is for where they did their undergrad degree for our first graduate students. So, you should not be writing it like this. But things actually became worse when these programs started getting used in web applications. And a new hack called SQL Injection emerged which is actually a very, very serious security problem. Extremely serious. This is no joke. And what is the issue? Let us take a simpler query from the earlier one. Select star from instructor where name equal to and then there is a single quote. The double quote ends the Java string plus name, double quote, single quote to close the SQL string, double quote. It may be a little confusing, but with a normal name this will create a perfectly fine SQL query. Now, with this was our father Agnes, this will die, but now here is something worse. Dying is fine, but a hacker can exploit this to do something very dangerous. Now, what is this query supposed to do? It is only supposed to retrieve instructors with this name. But a hacker could type X quote or capital Y quote equal to quote Y and then nothing more. What will this become? The resultant statement after processing all this is this. This is the SQL statement which would get executed. Select star from instructor where name equal to quote X quote or Y equal to Y. The hacker did not type a close quote the original quote close quote actually came in here. Now, what is the point of this statement? Name equal to X or Y equal to Y. Y equal to Y is certainly true. So, this query which was supposed to return only instructors with a given name now returns all instructors. Now, is this a big problem? Maybe this user was only supposed to see instructors with some name, but then they see all names say big deal. But it could be worse. The hacker could have done this. It has said X quote semicolon update instructor said salary equal to salary plus 10000 semicolon dash dash. What is this last dash dash doing? It is starting a SQL comment. So, this extra quote which would have been added here will go inside the comment and get signaled. So, what it is doing is the first part of the query ends up saying select star from instructor where name equal to X and that semicolon terminates that query. That is fine. The second thing does an update query. Now, you might say shouldn't the database system object to this? You are supposed to just run one query, but now there are two queries. But the fact is this is in use in many applications where you send multiple queries as part of one statement. And what happens is the system will go ahead and execute both the queries and update the instructor's salary. Now, you might say hey what is a big deal? We are increasing instructor's salaries. We will be happy. Next month's paycheck will be higher. But you know this is just an example. The hacker could have done anything. The hacker could have you know dropped an instructor. The hacker could have dropped the table, dropped table instructor. The hacker could have dropped all the tables in the database. The hacker can cause enormous havoc to your application. It can result in serious data loss. And all kinds of other things can happen. The hacker could get access to your bank information and change the balance and generate money and withdraw the money later on. And this actually happened. Hackers have used SQL injection to hack into bank databases and get money out of the bank database to transfer money to other account. All kinds of havoc has been caused. And the amounts involved are huge. You know, you would think are banks such idiots to write bad code with SQL injection? The fact is that initially nobody realized this. So, somebody figured this out. But many people continued with business as usual writing more such programs. Well, after some time people realized a mistake and started writing better programs. But not all. New programmers again make these mistakes. But what about all the old programs which are there? Many of them have this bug. And this is essentially what happened. People did not realize that one of the old programs had this bug and they used it to hack into the system. What about other domains? Banks were silly. What about IIT Bombay? I can assure you that IIT Bombay has a few old programs which have this bug. And we have still not fixed it because there is always a constant demand of new features. So, how is it okay to leave a security hole? And the answer is partly that the security hole is only available to people who are already logged into our system. So, if somebody does something, we know who they are. And the second thing is by uphaskation. Meaning that the holes are usually there in system which are used only by a few people. And those few people have no clue about SQL injection and are unlikely to hack our system. But if we put something out on the web, publicly accessible web with the SQL injection hole, you can be assured that some hacker will try it out. People have tools out there to try this out. And they will try it and will break into your system. It will happen someday. So, you have to be very careful with prepared statements. Never ever take user input and concatenate string. As you can see, it is also very clumsy. Concatenate string with single quotes is really clumsy. It is actually a lot simpler to use prepared statements. So, please use those. So, here is time for a quiz break. And I will follow up this quiz break with time for questions. So, read the question. So, let us go over this thing. We have used prepared statements, which is what I told you to use. But just merely using prepared statements is not going to solve the world's problem. It is not going to solve SQL injection problem also if you do string concatenation. What happened here? The programmer still used string concatenation instead of using prepared statements properly. What is the right way? Instead of name here, they should have had a question mark. And then they should have done a set string one to name. That would have been the right way of writing it. But what they did is they did string concatenation. And once you have done it, prepared statement can do anything about it. It takes this as a string and processes it. And if user tries to do SQL injection, sure, they will succeed. So, the above code is not secure. Since we are doing string concatenation, SQL injection can still occur. Now, time for a few questions. Kongu Engineering College, Tamil Nadu. If a row is duplicated, how to delete the duplicated row? Can you get me, sir? That is a good question. Supposing in a relation, you have a row which is duplicated. I want to remove one copy. So, the whole row is duplicated, not just some fields. The entire row is duplicated. And I want to remove the copy of just that one row. Supposing I want to remove all duplicates in the relation, I can execute a query like update. Well, first I can write a query which is select distinct from that relation into another relation and then remove all the rows from this relation and then assign that other one back. So, if I am removing all duplicates, a two-step process which is a select distinct followed by delete from the table and then insert into back from the temporary table will work. But your question is a little harder. I have a specific row which has a duplicate and I want to remove one copy of that. So, it is actually something like this cannot be done in a purely declarative way if there is no way to identify the row. So, the delete statement in SQL is declarative. It gives the condition on which to delete a row. Now, if the only fields accessible are the normal attributes which are all duplicates, there is no way to differentiate between the two rows. So, most databases though provide another extra attribute with a row which is like a row number or row ID or some such thing and that is not duplicated ever. So, they provide a way to access this field. So, depending on the database you have, you should figure out what that field is called and how to access it and then you can use that in your delete condition to delete just the one copy. Otherwise, the problem is if you add a construct to SQL, it says delete one row. You could do that. I mean you meaning SQL standards committee could have done that, but it is non-deterministic which row if in case the row number is there. So, as far as I know SQL does not have a purely declarative way without going around and finding out row number or ID. I have another question. What is the difference between snapshot and view? Snapshots are a different concept which I will come to later on when we cover concurrency control. So, I do not want to answer that question at this point, but conceptually it is very simple. It is like taking a snapshot, a photo of the database at a particular point in time and then running a query on that. The database may change later on, but the snapshot does not and you run a query on that, but I will cover it in more detail later on. I think we will stop there and go back to these slides. So, here is the metadata features in JDBC. Integrity constraints being one part of the metadata feature, but the simpler parts of it are what are the relations, what are the column names, what are the types and so forth. So, JDBC provides a way to get metadata in two different things, one is if I have executed a query, I can get metadata about the query result, what are the columns in the query result, what are their names, what are their types. So, once I have got a result set which we saw how to get earlier, on a result set I can execute the function r s dot get metadata and this function returns an attribute of type result set metadata. So, that is this variable r s m d is of that type. So, now this has a number of functions which I can call to get metadata about individual columns of the result. So, one of the method is r s m d dot get column count tells me how many columns there are in the query result and I have a loop here for i equal to 1 to that number i plus plus. So, I am looping over all the columns. Inside it I am saying system dot out dot print line I am just printing, what am I printing r s m d dot get column name of i. So, this is going to retrieve the ith column the first time around the first column then the second third and so forth. And the next one says get column type name that is the type of the column as a string and so that also can be printed out. So, it is going to print the names of the columns and their types. How is this useful? There are many uses if you use p g admin you saw that a p g admin can show you the what all relations are there what are the types, but in this particular context result set it can tell you the which attributes are present in the result set for an arbitrary query. You just give it a string to execute it figures out how many columns there are in the result it knows the types formats it nicely and shows it. How does it do all that using metadata feature? Now, p g admin is written using c or c plus plus and that would use odbc which is equivalent to jddc, but that also has metadata features. So, this was for a result set similarly there is a database metadata here you are getting metadata about the whole database and that you get by the following function connection once you open a connection dot get metadata it returns an object of type database metadata. Now, on this you can execute a number of functions I am not showing all of them you can read those up later I am just showing one simple function which is called get columns and get column basically gets the following thing. It gets you schema which looks like this column name it retrieves column name column name column type relation name then the database name and there are several other things here, but the get columns command works as follows it takes several arguments the first argument is a catalog this is again feature which we do not which is part of the SQL standard but most database is ignored. So, we will just set it to null the second one is the schema and this is a pattern in the sense that I can match multiple schema in this case I want to look at just the university database. So, I am saying university dv is not a pattern but in general it could be a pattern using percentage like very good the third one is a table pattern I could match multiple table, but here I chose to only find out about the department table. So, the table pattern here is just this thing the last one is a column pattern which can match all columns with a certain thing here I have used a pattern percent. So, in other words retrieved all columns of this table name. So, there are multiple columns each column turns into a row in this thing. So, the result of get columns is a result set. So, now I can iterate over the result set and there is one row per column here and for that column I can get string column name get string type name and so forth. So, I can extract the information about the columns and I can print it out here or I can store it or do something else. Where is this useful? Well again using PG admin you could browse the schema to see what all relations there are in the database and for each relation I could find what all column names and types. So, you can if you set the relation name here to percent I will find out what all relations there are in this database. If I set this to percent I will additionally find what all databases in the schemas are there and in each schema what are the tables and for each table what are the columns. So, that is one thing I can do there are other functions to get constraints primary key, foreign key and so on and so forth. So, I have not shown all those here, but using all of them the tools like PG admin can show you everything about the database including keys and so forth. I think this is the last topic on JDBC, on JDBC this is the last topic which is transaction control in JDBC. I already mentioned that SQL has a notion of transaction and you can put multiple statements into a single transaction. So, here what do we have? We have a connection by default any query which you execute on that connection is treated as a separate transaction and immediately committed. But, if you want multiple things to go as a single transaction multiple updates for example, I can do the following. I will say connection dot set auto commit false what this does is turn off auto commit by default it is on which means each SQL statement that I send is a transaction by itself and commits immediately when I said auto commit false subsequent SQL statements they send are not immediately committed they are all treated as part of one transaction. And at some point after sending a few of these I complete my transaction at which point I must say connection dot commit or connection dot roll back. And then SQL will make sure that all those intervening statements are either executed completely as one unit or if I roll back they are all undone and the final database state will be consistent with all of these updates. The database cannot reach an intermediate state where the first update has happened but not the second update. And to wrap it up connection dot set auto commit true turns on auto commit again if you want to go back to the default. So, that is a quick overview of JDBC and that is what I wanted to cover. What I hope to cover time permitting was a few more advance topics but we are actually at the lunch time now. So, I cannot really cover this in detail. So, what I am going to do is just flash these slides at you and if you have time break later in the course I may come back to some of these but in case I do not I want to urge you to go read up these topics in your own. So, this is a very short course you know 30. So, 30 hours of lectures which cover many topics and included in that is a lot of discussion time. So, I had to cut out some topics and these were some topics that I was supposed to cut out for lack of time. The first is procedural extension and stored procedure. So, SQL language as I have already told you has many constructs for loops, while loops all kinds of stuff if then else it is a procedural language on its own. And you can create procedure using these and store it in the database. So, that procedure is actually in the database but stored procedures are actually more general. You can define procedures in Java or other languages and store it in the database. It is now part of the database itself and you can invoke the procedure from SQL. So, these stored procedures are useful for many reasons. SQL stored procedures are useful when very often you want to do many statements together but you if you send them one at a time from the application program it is slow. If you put it all into one procedure at the database it executes in the database without continuous back and forth from application program to database it is more efficient. It is also cleaner in the sense we want to make a change to the stored procedure you can update the database you do not have to change the application code at all. So, that can be useful sometimes. So, we use this for example in our fee calculation. Every year our fees structure changes new fees are added amounts change. If we calculated the fees in the application code we would have to recompile our code every year or every semester. What we did instead is we created a stored procedure which we modify every year by just doing a database update or application code does not get changed. That is for stored procedure. I am going to skip these functions. This procedure is in SQL. This function procedural constructs I told you about all those. The next topic I want to briefly touch upon is triggers. Now, many of you have triggers as part of the core syllabus. So, it is a core thing for you, but unfortunately I could not get into it in detail. But, maybe later on I will take questions on triggers for those of you who not now because it is lunch time. But, later on we can have some amount of time on trigger. For those of you who do not know what is a trigger it is basically a statement that is executed automatically as a side effect of some modification to the database. Again I do not have time to get into details. I will just give a small example. This is create trigger with some name after insert on section. So, whenever I insert something on section this code is executed here and then there is some syntax here which I would not get into the details referencing your row for each row when something then do something. I would not get into the details of this, but essentially what it is doing is when I insert something into section it checks if the time slot ID is not in the time slot table. If it is not in there it rolls back the insert. So, this is enforcing a integrity constraint a foreign key constraint which unfortunately could not be imposed in SQL because time slot in the time slot sorry time slot ID in the time slot table is not a primary or a unique key. So, we could not make it a foreign key, but instead we could create a trigger to enforce the constraint. Unfortunately the trigger syntax is highly non-standard every database uses its own syntax for trigger. So, even if I had time whatever syntax I give here I can assure you it would not work on your database. The syntax in PostgreSQL is different from what I have here although this is part of the SQL standard no database is implemented exactly as shown. So, triggers can be useful, but they can also get abused if there is a risk with triggers especially if you look at the bottom of unintended execution of triggers. For example, when you load data from a backup you replicate updates at a remote site and so forth and that can cause damage to the database. So, you have to be careful. The next topic which I had hoped to cover is recursive queries again I do not have time. So, if I have time later on I will talk about recursive queries recursive views and the last topic which I wanted to cover time permitting was ranking. We have been discussing how to give ranks to students we talked of complex and expensive queries to do. So, but SQL now has syntax for it I would not get into the details, but look at this as an example say select ID rank over order by GPA descending as a rank from student grade. So, student grades has may be ID and some grade point average and I want to give a rank to student based on the grade point average. This is similar to the marks relation we used in our example query. So, this syntax basically says order these tuples by this field GPA in descending order and then this construct in the select clause returns the rank 1 2 3 and so on and we have given it a name S rank. Now, if I so the rank here is based on the order by GPA, but what SQL does not guarantee is that this overall result is sorted on rank it might give you rank 10 first then rank 7 and then rank 1 and then rank 9 and so forth. So, I can additionally add order by S rank. So, this overall query gives me all the students starting from first rank second rank and so forth and rank gives gaps. So, if 2 students have the same top GPA both will have rank 1 the next will have rank 3. Instead of rank if I say dense rank it may have 2 ones and then the next one would be 2 not 3 and there are variants which this one is ranking using aggregates which we have already seen earlier today. The last ranking feature which I think is worth mentioning is you can also rank over partition by forget the detail syntax, but do not look at the query syntax. What I want to mention here is when we are in school we have multiple sections and each section gives the rank. The students take the same exam they get marks, but they get a rank within the section and this construct is basically lets you partition the data in this case by section and get the rank within each partition. So, there is a lot of powerful things you can do with the ranking construct. Then there is something called windowing construct which we would not have time for it is part of the SQL standard now many databases support it go read it up your interest rate and the last topic which I am not going to cover in this course for sure is online analytical processing. Again there is a whole bunch of stuff here data cubes cross tabs and so forth OLAP. So, I just want to mention these buzzwords here and urge you to go back and read all this material is there in the book and on the slides online of chapter 5. So, go read it if you are interested. I will stop the session here and maybe take a few questions. Meanwhile there is one question on chat which is is there any tool for OLAP. So, there are lot of commercial tools for online analytical processing, but there is one open source tool from a company called Pentaho here Pentaho is a company and it has a tool called Mondrian which is an OLAP tool. So, if you want to set up an OLAP tool and go over it you know try it out you can download it Mondrian OLAP tool from Pentaho site it is written in Java. So, you can pretty much run it on any platform against any database. So, go ahead and try it out Gajanan Maharaj college Shegong you have a question please go ahead. Sir what is the difference between materialized views and tables? He is getting difference. The question is materialized view versus tables. The materialized view is defined by a query and the contents of the query result are computed and stored as a table, but the view definition is still there which means that if the underlying table is changed the materialized view can be updated using the view definition the query. Whereas, a table is just a table there is no notion of what it can be if you want to change it it is your responsibility to change it, but for a materialized view the database can recompute the view if the underlying tables change. And as I mentioned it can happen either you know periodically at night it can happen when you ask for it it can happen immediately as soon as the underlying table is changed meaning if the materialized view is a query queue. So, view is defined as select something from r comma s where something. The moment there is an update to r or to s insert delete update whatever the result of this query changes. And when the result of this query changes the view has to be updated whereas, for a table the database does not know it is your job to keep it updated. Does that answer your question? Thank you sir. We are with Techno India Salt Lake. Kulkath are any questions there? Actually we just want to know that would you please explain that how the image data can be stored into postgres SQL by writing SQL query and what are the data types that are specified in this regard? That is a good question and I am going to actually add some more to that question. The question is how do you store image data in a database like postgres SQL? What is the data type? So, the SQL standard has a data type called BLOB or binary large object BLOB. And that is the data type you would use to store images. The next question is how do you get it into the database in the first place? Normally you write an SQL query to insert data, but how do you how on earth do you put an image into an SQL query? And the answer is you can do it through JDBC. So, with JDBC you can get what is called an object locator. It is like you know you can have an insert query with the BLOB initially empty. And then you can essentially think of the BLOB as a file in the database and you have an interface which looks kind of like a file system interface to open and then write to the file and then close the file. So, that is typically how you add data to a file. Open a file, write to it, close it or read to it, seek into the file. So, there is a corresponding concept in JDBC called the object locator. So, you can essentially open your BLOB, add data to the BLOB and then close it through the JDBC API and similarly ODBC and other such things. So, that is the only meaningful way of storing an image in a database. You cannot do it through plain SQL. Does that answer your question? NIT Trichy, anybody is there? Sir, can we create a view without having any tuples in your table? So, there is absolutely no problem. Your view definition does not really care what data is in the table. When you use the view is when the tables contents are looked at. Even then it is okay. The view, it is an empty table. The view itself may be empty. That is fine. That is not a problem. IER, CEM, Institute. I have a one question that is can a view contains primary and foreign key relationship? And the answer is no. There are some interesting applications where you actually would like to have integrity constraints on a view. These are constraints which are hard to define on the base table, but you might be able to define it on a view. For example, in our section which had a time slot ID. Supposing I could create a view called time slot master which is select time slot, select distinct time slot ID from time slot. There there are no duplicates. It might be nice to say that time slot ID must be a primary key for that table and then allow a foreign key to reference a view. It would be nice, but I do not think any database supports it. So, conceptually it is a good idea. In fact, I think people have proposed it, but the best of my knowledge nobody implements it. So, may be interesting research project would be how to implement this. Maybe you could even take PostgreSQL and try to implement such things on PostgreSQL. I vaguely remember some project which tried to do this on PostgreSQL, but it would be interesting to try implementing this. The only catch is that PostgreSQL does not support view maintenance. So, what you need for this is immediate view maintenance. The problem is if you do not check it immediately, it is too late. Then what you do? So, you actually have to go implement materialized views, view maintenance and then integrity constraints on the materialized view all of these. So, SQL server already supports immediate view maintenance. So, theoretically there is no reason why it cannot additionally support integrity constraints such as primary and foreign key, but I do not think it has. To my knowledge it does not support it, but it is a very good question. Thank you for asking. Can a store procedure call itself? That means I mean to say recursive store procedure is possible and how many levels of store procedure nesting is possible? Can you have a recursive store procedure as a question? This is implementation dependent. I do not think SQL standard defines it. I have not tried it on any database. So, I think the answer would have to be as far as I know no, but this is something which might be worth trying out. The problem with recursion is it is easy to get into errors and it is more work to implement recursive function. So, to my knowledge most implementations do not support it, but I may be wrong. Maybe somebody does. So, it is maybe you should follow up on this and let us know over p ads are tomorrow if somebody actually does support it. So, recursive views are important and they are supported. Recursive store procedures I do not know. I have another question that is if relation has a composite primary key, then can the subset of the primary key be a foreign key of another relation? The question is if you have a primary key, first of all can that primary key itself be a foreign key referencing another relation? Yes, absolutely. The second way to interpret that question is can a subset of the primary key be a foreign key referencing another relation? Absolutely, no problem. So, in fact in our example we have that. We have course idea as a primary key. It is also as part of a primary key and it is a foreign key referencing another relation. The one part which is not allowed is that actually if you want to have a foreign key referencing a sub part of a primary key, no I do not think you can do that. So, incoming foreign keys which are to a subset of a primary key to my knowledge is not allowed. But again if you have declared it as unique maybe it will be supported you can try it out. So, thank you.