 Chapter 5 has quite a few slides and I know I will not have time to cover all of them. But I would like you to read up the remaining slides of Chapter 5 afterwards even though I would not have time to go over all the slides. Maybe we can have a short discussion one of the later days as time permits on the extra slides. So what I want to focus on today from Chapter 5 is the first part which is accessing SQL from a programming language. So there are many programming languages and each of them has its own API for accessing databases from the language. In fact some of the programming languages have many APIs for accessing databases. So there is no one single solution to this. But for consistency we are going to stick to Java and its JDBC API to access databases. A little bit of history Microsoft actually, well even before Microsoft there was an attempt to standardize this in SQL for database access and Microsoft did an implementation which more or less followed the standard but differed in some ways and they called it ODBC. So that was from C language initially but also basic and a few other languages and that became kind of a standard and JDBC was modeled after ODBC. So I am going to focus on this. The remaining parts of the chapter are more on more types, schemas, procedural constructs, triggers, advanced aggregation features including ranking features which we discussed earlier and online analytical processing. So let me focus on JDBC. ODBC has the same properties. It is an API for the program to interact with the database server. Now how many times have you or anybody you have known actually typed SQL queries to a database system outside of a database course probably hardly ever. The only people who write SQL queries are programmers and those queries are part of an application program and executed whenever the application program is executed. So it is obviously very important to define how the application program talks to the database. So the basic sequence of steps whichever API you use is to first connect to the database server through the network, send SQL commands to the database server and fetch results. If you have a single result you can fetch it immediately. If your query has many tuples in the result you have to fetch them one by one or block by block or whatever other way and then output them to the application user. So JDBC is the Java language API which is the standard. So the way you do stuff in JDBC is you open a connection to the database then you have to create what is called a statement object and then execute queries using the statement object and then at the end you have to close the statement object close the connection when you are done. There is also an exception mechanism if something goes wrong while processing queries Java has an exception mechanism and the JDBC API uses that to signal errors back to the application program. JDBC like ODBC not only lets you access data it also lets you access metadata which is like the schema information and you know what are the views in the database what are the primary keys what are the foreign keys what columns does this relation have. So all kinds of metadata can be accessed from JDBC also it turns out that the SQL standard also lets you access metadata through a set of relations called the information schema. Unfortunately nobody implements it more or less whereas the JDBC API to get at metadata is standard and everybody implements it. So this is what most people use to get at metadata. So here is a small snippet of JDBC code I broken it up into two parts in this slide I am just showing you how to establish a connection and the actual work is on the next slide. So the first step says class dot for name oracle dot JDBC dot driver dot oracle driver now this is a bit confusing to most people what is this there is actually there is two ways of doing this there is another thing using driver manager I think the assignment will tell you bit more about the alternatives. But what is this first step and the answer is the JDBC API like the ODBC API lets each database vendor create their own library implementing the JDBC calls. So what does that mean you may have a library for SQL server you may have a library for PostgreSQL you may have a library for Oracle. Now if an application needs to use this library it should link with that library supposing this link is done statically when you compile the application program that means that the application can either talk to Oracle or it can talk to SQL server or to PostgreSQL you have to decide that at compile time. But what if I want an application which can get data from two different databases combine them and output it to the user this would not be possible with static linking. So in fact what all these APIs do is they allow you to dynamically load multiple implementation you can load Oracle and PostgreSQL and SQL server libraries all at the same time and then depending on which connection you are using the appropriate library is used. Now how does it do it first of all this class dot for name tells it to load the Oracle driver then when you say driver manager dot get connection it uses this one which you have just loaded and opens a connection to the database. Now how do you open a connection to the database first of all you have to tell the API which machine it is on that is down here db dot el dot edu you have to tell it which port number the database is running on in this case I have said 2000 actually each database has a standard port PostgreSQL runs on port 5432 by default so that is what you will be using in over here other databases run on other ports by default. And you can change it okay so that is the port number then the next part here Unif db a particular Oracle installation can have many databases in this case it says connect to Unif db same with PostgreSQL. Now what is the prefix here JDBC colon Oracle colon thin is basically something which is associated with this fellow that is I can load multiple such drivers each driver will have something identifying what protocol it supports what specific variant of the implementation of the JDBC protocol it supports. Now this particular driver would have said it supports the protocol JDBC Oracle thin therefore when you say driver manager dot get connection JDBC Oracle thin the driver manager knows to use the implementation which is there in this particular thing Oracle driver what is that Oracle driver it actually has to be a file in the file system which contains the byte code for the Oracle specific implementation. So it is going to load that byte code dynamically and then this one says use that particular one because you may have loaded three different ones this tells which one to use so that is how you tell the connection manager to open a specific connection to a specific database. Once you open the connection the next step is to create a statement on that connection and you use that statement to do the actual work. You will not refer to the connection subsequently you will only refer to the statement and finally you can close the statement close the connection and that is done and at any point you can have an exception. So this catches SQL exception that is the type of the exception that JDBC throws. So this catches all SQL exceptions and then prints the error message prints a string followed by SQLE that object itself is simply going to convert the error message into text and output it so you know what went wrong. So the actual work in here you know maybe it could not open the connection that will be an exception maybe when it was doing actual querying there was an error that would also get caught in the display. So now let us see how to do actual work on the database so the first example is to update a database by inserting something into the database. So here we have a piece of code which again encloses the work in a try catch so that if the update fails you can immediately say sorry the update failed. So what is the update here? I am trying to insert a tuple for this particular guy 7 7 9 8 7 with the name Kim physics department some salary into instructor. So I am passing an entire SQL string here to statement dot execute update and it is going to execute that and it as long as everything works there is no exception it is fine if something fails why would it fail maybe it violated a foreign key constraint maybe 7 7 9 8 7 is already there so it will reject it maybe there is a constraint on salary which does not allow 98000 a salary perhaps it is rejected maybe the physics department does not exist it is a foreign key violation it is rejected. So many possible reasons are there and when I print this here the SQLE that will actually contain a specific message saying what went wrong so that is important. So that was an update now the queries are slightly different because they actually have to return multiple answers potentially. So the interface is slightly different here here I say statement dot execute query and I pass a query string to it. So what is this string it says select department name average salary from instructor group by department name so this is going to find for each department which has some instructor the average salary of instructors. Now obviously this query has one row potentially per department so the result of this query is not a single value but it is a set of tuples. So in JDBC the result is an object of type result set. So I am assigning the result to this object R set whose type is result set so this is a variable. So once this is executed I can fetch the results one by one using the R set object. So how do I fetch a result I have to do R set dot next to fetch the first tuple and I keep doing next next next it will get second third fourth and so on. What if a next failed because there is no more tuple then the R set dot next would return false. So we put this in a loop which says while R set dot next. So if the result is empty what happens R set dot next returns false immediately. So the loop never executes if the result had two tuples the first time it would succeed go process it the next loop would fetch the next tuple process it the third time around it will try to fetch it fails and exits the loop. So this loop is only executed when next succeeded and what is the content of the loop doing is doing system dot out dot print line R set dot get string department name plus R set dot get float to. So what is this stuff R set dot get string department name gets for the current row. So note that result set is a set of rows but when I am doing next next next at each point there is a current row I am at the first row second third fourth at wherever current row I am at R set dot get string department name will get the department name attribute of that row. Note that JDBC has somehow found out that this query result its first column is called department name therefore it can fetch that value. Now the second column what is the name of the second column. The second column is an aggregate its name is database dependent so I do not know what that name will be JDBC will know but I do not know as a programmer. So what I do instead is I say R set dot get float to which means the second attribute. So I do not care about its name get the value of the second attribute note I am doing get float because I know this attribute is a number its average of something what if I do get string on this well luckily JDBC actually converts a floating point number to a string. So I can do get string on it it will still work what if I do get float on department name it will be a runtime error it will say sorry department name is physics I cannot convert that to a floating point. So JDBC tries to convert types as required depending on whether you do get string get float and so on. If it can if the type matches no problem if the type does not match it will try to convert if it succeeds fine otherwise it raises an exception. So as we just saw I can get an attribute either by attribute name or by position. So since department name is the first attribute in the result RS dot get string department name and RS dot get string one are really the same thing so far so good. Now what if a particular value in the database happened to be null now if it was a string value and I do get string it can return null that is not a problem. But what if it is an integer or a float I said get float now null is not a valid value for floating point types in Java so it cannot give any meaningful result there. So what will do is if the value is null it will give you some result but in addition it is going to set a flag that that value was null whatever value you fetch last was null. So we want to see if it was null then moment right after you say get int or get float or whatever attribute value you say check if RS dot was null what does that mean the last value you fetch was null if it was null then you know that the value you got was null the actual assignment here a might get 0 it might get min int it may get something we do not know what it gets depends on the implementation. But when I do this check I know that a was null and I can deal with it explicitly in Java ok. So that was a quick overview of part of JDBC there is a question here but I think I will defer it just a moment and come back to it after finishing up with JDBC. So far we saw that we can execute a update or a query which is a string. Now most applications require input from the user which they need to stick into a query the correct way of doing it is as follows. If I am going to get an input from the user which has for example a student name student ID course ID whatever input I get from the user I can create a query template a query template which has question marks wherever I intend to put values. So this is the correct way of doing it. So what do you do the earlier insert now I am going to say insert into instructor values question mark question mark question mark question mark. So these are place holders I cannot actually execute this query right now instead what I do is I say connection dot prepare statement with this statement. So the statement is prepared but it cannot execute because we do not have values for the question mark. So after preparing I can do the following on the same prepared the result is a prepared statement object and on that prepared statement object I can set the values. So I have set the ID to the string 88877 I have set the second one which is the name to Perry I have set the third one which is the department to finance and the fourth one which is the salvi to 125000 the finance guys always get paid the most these days. And after setting all the values for these four question marks I can do p statement dot execute update with no further parameters. So it is going to fill in the values and execute it this is the correct way of passing parameters taken from the user user input may be keyboard or if it is a web application through the browser across the net to your application this is how you will put it in the database. Similarly if you are running a query which takes parameters. So I want to find all people in a specified department the user enters the department name I want to find all people in that department. So similarly I will have a query with a question mark for the department name and same prepares this part is exactly the same I can set the parameter value exactly the same. The only difference is here instead of execute query update I will do results at equal to execute query p statement dot execute query. So that returns the result set just like we saw before. Now here is a warning which you should take very very very seriously because this has enormous security implications whenever you take input from a user always use prepared statements to pass that value to the database whether it is a update or a query does not matter use prepared statements. A lot of people lot of programmers that I have seen whether it is students or even many program new professional. So not yet learnt what not to do do the following. So I have got let us say you know ID name department name and balance input from the user. So they will create an SQL query dynamically they will say insert into instructor values open parenthesis single quote and then the string closes plus ID which was entered by the user plus in the inside of double quote single quote comma quote. So what is this doing it is taking the user input and creating an SQL query putting a quote around each attribute over here. Except for the last attribute balance which is actually not balance I am sorry this should have been salary. Salary is an integer so that one is not quoted the others are all quoted. Now there is a major problem with this query supposing I try to insert a person called this Suza with a quote after the D what happens name comes here and I get a open quote which starts the name D quote which is the quote from this Suza and what is SQL thing it thinks that the name is ended quote D quote is a name and after that is Suza. Now this does not make sense to SQL and it gives an error and it does not store anything in the database it is an error. So we 10 years ago when we first started using JDBC we did not realize this and we had an application which would load a lot of names and it would fail on names with quotes that is when we realize what is going wrong. But sometimes people build applications do not even check for errors. For example in IATB's empty admission system we store the college name from where they graduated. Now this actually failed for a lot of people because in Bombay we have a lot of Christian college father Agnol's college Saint Xavier's college you know so many colleges with single quotes and guess what happened all of them had a syntax error and those updates never happened and worse still the programmer in the try catch they simply output a message to some log and nobody knew it had gone wrong it just went to a log and that was it nobody saw the logs. So eventually when we went round looking at colleges we found many many students did not have a college name associated with them and it will because of this error. So that is innocuous it was not it is bad but not horrible well here is the horrible part which is called SQL injection vulnerability. So let us take a simpler query which is select star from instructor where name equal to single quote close the string plus name plus in double quotes a single quote. So it is constructing an SQL query with a name input by the user. Entering the user types the following instead of entering a meaningful name the user says enters X quote this is just from web application there is a box to enter a name. So the user types X quote or quote Y quote equal to quote Y quote now what is this doing the name string is going to end with X where it says select star from instructor where name equal to X that is going to fail no instructor has name X probably but the constructed SQL now continues it does not end there it says or Y equal to Y which is obviously going to be true. So what is this query going to do now it is going to output all the instructor names which is not what the programmer intended well that does not sound so bad but the user could have done something much much more dangerous. The user could have typed X quote semicolon what does semicolon do it terminates that string at the query following that can be a new query that query could be update instructor set salary equal to salary plus 10000. So this very generous hacker has just given a 10000 rupee raise to all instructors hey all of us are instructors we may be very happy with this we may not mind but somebody minds and could get a lot worse the hacker could be a student who did not like instructors and says the salary to 0 or says the salary to something slightly lower which you would not even notice on your face slip all kinds of bad things can happen worse still the hacker could delete all the relations in the database nothing prevents it they can cause enormous damage all from just sitting at a web browser and typing some funny strings where it text was expected strings with single quotes and SQL constructs if they know what they are doing they can cause enormous havoc and in fact this problem was so under recognized that apparently many department stores in the US and even some of the credit card processing companies they had web applications which were vulnerable to this problem the web application may have done something very simple like allow users to check their balance nothing no updates but in there there was a SQL injection vulnerability these guys went in there and started messing around with the database and there is a very famous case which was published in New York Times month ago where these guys hacked into the credit card company databases and got a huge number of credit and debit card numbers the moment they got a debit card number or credit card with a pin they actually captured the pin number even which you enter at various places now they could go and manufacture cards of their own and go to an ATM and withdraw money they actually did this they made a lot of money using this in fact it was a more complex operation these guys extracted it and went and sold the information to somebody in Russia these guys were in the US they sold it to somebody in Russia so these guys stopped going to ATM machines themselves the guys in Russia use the ATM machines but they transferred money back to these guys so it to make the connection you actually had to go find who did it in Russia come back to US Polish in two countries involved is actually very difficult to catch them they did catch these guys but who knows how many more people got away with it so what is the moral of the story we as people writing queries in JDBC or equivalently ODBC or any other language it's the same the problem is the same if you construct strings like this from user inputs you are asking for disaster there are tools which you can run commercial tools which will try to probe your system to look for vulnerabilities like this so you should probably use those tools but in the first place you shouldn't make this error so people should realize they should never ever concatenate user input to form a query they should use prepared statements what's the difference if you use a prepared statement well the system knows about quotes it can deal with it you can put in escape characters and so on so that at the end what goes to the database is a properly formatted query so if somebody typed St. Xavier's College that would work if somebody tried to hack the system well that entire complex SQL query they constructed will simply go in as a field in the database and their attempt is defeated so that's the moral of this story it's really important I find many people making this mistake even after being told in my course this semester I found about 25% of students making this mistake and I should have probably failed them in the lab I didn't have the heart to do it but I scolded them severely okay so now here is a quiz question let me take the one more quiz question which I postponed but let me take it out of order let me do this question now because it's a continuation so all of you please press your ST buttons and be ready for this quiz question I'm giving you about 15 seconds to press your ST button okay the timer has started now please don't press the ST button anymore press your answer only at this point so oh wait I didn't explain the question I'm sorry let's can you cancel this okay I'm I'm going to ignore this because I forgot to tell you what is the question one minute is not enough to read this question so don't bother answering this quiz forget it I'm going to run this quiz again so let me explain the question first so the question is here is this piece of code which uses a prepared statement I just told you you should be using prepared statements so a few students went ahead and wrote code like this prepared statement P statement equal to connection dot prepared statement select star from instructor where name equal to single quote plus name plus single code and then dead results at RS is P statement dot execute query and then went ahead and fetched results from it the question is is the above code secure and the possible answers are a yes it is secure since we are using prepared statements I told you to use prepared statement so everything is fine B is no we are still concatenating strings so a square injection can still occur C is yes it is secure since we are using execute query and D is no it's not secure since we are using execute query so you have these four options I'm going to start the quiz again just hang on for a moment press your ST keys at this point to prepare your remotes I'm giving you 10 seconds quiz should be enable now check that the red lights are blinking or blinking right there should be blinking on your remote please press option 1 through 4 you should be able to see the timer counting down in the video feed okay time is up so let me explain the answer and then we will see what people have responded so the answer a is wrong prepared statements are not magic if you create a string by concatenating user input and then prepare that the database still thinks it's a query with multiple parts so if the string had quotes it's very much going to run into the same a square injection problems just because you replaced execute query by a prepared statement doesn't make any difference if you still concatenate strings I found a lot of students earlier on would get confused by this that's why I raised this question B is no we are since we are still concatenating strings using user input SQL injection can still occur and that's is the correct answer C and D are wrong it's certainly not secure and the D is execute query has nothing to do with being secure the point is you should not be concatenating strings on your own you should be using question mark placeholders for user input and then use prepared statement dot set string set in as depending on the type so the answer is B and let us see the responses okay this time more centers have worked only about four or five centers are left 177 people have responded out of 300 it's better and audience wins this time I'm happy that audience has won good so B was the right answer it's still you know the losers can form a coalition government and topple the right answer still because they have enough numbers but still the major the most frequent one was the correct answer so quite a few had the same error which I saw in many students they think that just because you stuck a prepared statement it solves the problems no not only should you use prepared statement you should not concatenate strings using user input that is the bottom line okay now quick overview of metadata features in JDBC JDBC lets you look at what are the relations in a database what are the columns in a query result so there are two interfaces the first one is called result set metadata so when you run a query you would like to know how many columns are there in the result what are the names of the columns what are the types and results at metadata is something which lets you see the metadata about a query result so after you have executed a query you've got a result set RS I can say RS dot get metadata that returns an object of type result set metadata on that object I can step through you know the how many ever it has a column count how many columns it has and then I can get the name of the ith column using get column name you can get the type of the ith column by getting get column type name and then there are a few more so all of this lets me see the names and types this is how an interface like PG admin 3 if you manage to get it working when you run a query it will show you the column names followed by the actual data so it figured out the column names using results at metadata actually PG admin 3 is not written in JDBC Java but if it were it would have used this it uses an equivalent feature with ODBC similarly you have database metadata which lets you find what are all the relations in the database what are their attributes what are the types of the attributes and I've not shown all of it here but you can even find what are the constraints primary key foreign key constraints so there is a whole bunch of features which let you view all details of the schema using different API calls so the simplest one is on the connection get metadata which let's returns a database metadata object from that object you can get columns which match a certain thing for example UnifDB department relation percent which means all columns regardless of the name I can put filters here and it gets those columns so from the result of that is column name column type and so on with one row per column so why is this useful well there are many reasons if I want a database browser I need to know what are the tables in the database I need to know what are the columns of that table I need to know what are the primary key constraints foreign key constraints on that table so again PG admin you would have used a database browser the left panel you see that there are tables and procedures and the variety of stuff how did PG admin 3 know all that through a metadata database metadata feature that's how it is used now earlier today I told you something about transactions in that you can turn auto commit on or off so in what does that do by default each SQL statement sent through JDBC in pretty much all databases is treated as a separate transaction which is committed immediately but if I want a transaction with just two updates and I want them to be atomic both occur or neither occur the way to do it is to treat all of these as one transaction from JDBC how do I do it the first step is to say connection dot set auto commit false so once I set auto commit false SQL statements are not committed automatically instead subsequent SQL statements after this are all treated as part of one single transaction so I can run two three how many ever SQL statements I want and then I have to say either connection dot commit or connection dot rollback and whatever is appropriate is done I should warn you though that even if you say connection dot commit the database may find some problem it may find that some other concurrent updates happen which conflicted with yours and it may say sorry I am rolling you back if you say rollback it will rollback now if you are done and you want to go back to auto commit you can say connection dot set auto commit true which will then commit each transaction immediately. So that was a quick overview of JDBC JDBC is widely used but equally widely used is the ODBC API which is the original one on which JDBC is based and the exact you know calls in the API depends on the language ODBC is actually made available in visual basic in C C sharp etc etc so each depending on the language the library calls a slightly different the functionality is the same. Edo.net is successor to ODBC which was introduced by Microsoft adding a whole bunch of other features to access non-relational data sources also there is a small sample of code which I am not going to cover for now if you are interested you can read it looks quite a bit like JDBC but it is slightly different and has some quite a few other features then there is embedded SQL which is which lets you embed SQL code in a programming language without explicitly using calls like we do with connection dot prepare statement and so forth. So there are several such things there are SQL standards for embedding SQL and cobalt C and so forth and usually there is a pre-processor which takes a program which has embedded SQL rewrites it for example it may take a Java program with embedded SQL and rewrite it into Java code calling JDBC. So it saves some amount of effort for the programmer I would not get into the details. I will also mention that the SQL language we have seen so far is pretty much just declarative there are select from where clauses and bunch of other related features but people soon realize that they would like to do more stuff with SQL. So SQL actually evolved into a full-fledged programming language whose goal was not you know doing graphics or something a programming language which could do complex logic while running in the database system. So there is something called stored procedures which lets you store a program with imperative constructs in the database and execute it on demand. Now this imperative language is basically SQL plus a bunch of procedural constructs loops if then else and so forth. So again I don't have time to cover all this in detail but stored procedures are fairly widely used. Unfortunately stored procedures there is a SQL standard for stored procedures syntax for procedural SQL syntax the only sad thing is nobody follows the standard every database does its own thing. Now what this means is if you use stored procedures in Oracle you are stuck with Oracle you cannot port your application to any other database because its language is very different from let's say the DB2 or the PostgreSQL each one has its own language. So that is a danger of lock-in certain people say don't use stored procedures because of this but there are a few places where it's good to have procedural code in the database to carry out certain tasks. For the most part you can do the same thing using a Java program but there are a few limited cases where you would like a single thing in the database. Then there are SQL functions and table valued functions and procedures I am going to skip all of those procedural constructs I have skipped all of those. Let me introduce you very briefly to a few more topics I don't have time to get into all the details. So I want you to read it up if you are not familiar with it. The first is the notion of a trigger. A trigger is basically a statement that is automatically executed as a side effect of some other update or operation done on a database. So for example here is a trigger create trigger time slot check 1 after insertion on section. So whenever there is an insert on the section relation this piece of code gets invoked. What does this code do? It says referencing new row as n row. So that row has been inserted I need a variable which contains the contents of that inserted row. So that is what this basically declared. It says n row is a variable containing the value of the new the inserted row. And then it says for each row if the transaction may have inserted a thousand rows what this says is for each row loop over each row. If the it says when n row dot time slot id is not in select time slot id from time slot. What is this check doing? It is running a query which is checking if the time slot id of the newly inserted row is present in the time slot table. If it is present no problem if it is not in there then what does it do? It does a rollback. So this is enforcing an integrity constraint using a trigger. So as you can imagine this particular trigger must run as part of the transaction which is doing the insert. Whereby the error is detected while the transaction is running and it rolls back. That is how triggers operate. They run as part of the transaction. Of course this check is only on insert to section. What if I insert successfully? Then I go to the time slot table and delete a particular time slot. Now you have a section which has a time slot which no longer exists. It was there when the section was inserted. It is gone now. What about updates on section? When it inserted the time slot was correct now it was updated to some arbitrary value. So you can actually modify this to say after insert or update. And similarly for delete or update of time slot I can check if that particular time slot no longer exists in that relation. This is the last tuple with that time slot and it is still referenced from somewhere else. Then I roll back. So I do not have time to discuss this but go back and read it later on. There are a bunch of other trigger related constructs. There is another trigger here which maintains the taught credits, the total credits value for each student. Every time a grade is allocated to that student. So when the grade is done by updating takes it checks if you know this student was newly allocated a grade and then updates total credits. Again I do not have time to discuss this now but I will urge you to read it later on. So triggers can be very useful. Triggers can also be a little dangerous. There are many applications where you should not be using triggers. Again I do not have time to discuss it but go read it up. These slides tell you what to do. So we are quite a bit beyond time. So let me just wrap up by saying that less of chapter 5 contains several topics. One is a bunch of advanced aggregation features. The first one is a ranking feature. This was a question raised earlier how to get the nth tuple or a more general question. If I have a bunch of students in the class how do I give them ranks? Who is first rank? Who is second rank? So it can be done using basic SQL constructs but it is inefficient. So some of these slides here show you new SQL constructs which were added to simplify the task of assigning ranks and which can be executed very efficiently by the database. Again I do not have time to discuss this. This slide points out that yes you can get ranks using basic SQL queries without these features but the query execution will be very inefficient. How does it do it? This was I posted this on the Moodle thing. If you read it you will see it there also or read this slide and figure out how it works. I do not have time to discuss it now. And there is a whole bunch of features for ranking. For example, you can get the rank for each section of a course separately or overall for a course and so forth. And the last topic in this chapter is OLAP for online analytical processing. We are well out of time. So I am not going to even try to cover it here but I urge you to read these slides at least. This is a very quick overview of OLAP. Go read it up to get an idea. If time permits on the last day when I do advanced topics I will try to cover a bit of OLAP over there. With that I will stop. We have time for a couple of questions. Two questions which we have received over chat. What is the exact date format used in the last quiz? Is it year month date or any other format? In the question it was 2011. Oh, that was an error. That should have been 2011-30. So maybe people recognize this error which I did not notice and I apologize for the typo. So the date format by default would be year month day. Why not day month year? Because US guys use month day year which causes confusion. So the default is year month day in reverse order. You can change it but that is the default. The next one is we have a text file which contains 100 student records. We need to insert this in a student table. How do we insert it? The answer is you can write a small Java program with JDBC to read that file and insert it. Another possibility is if the file has good formatting with separators such as comma or tab, there are tools which are built into most database systems including PostgreSQL which can load directly from a file into a relation. You can see the system manuals for details. In PostgreSQL I think it is called B copy or I forget. One of those is B copy. But most databases do provide bulk load facility where you can directly take a text file which has some separation between columns and load it directly. The next question is why cannot we write create or replace table as that of trigger or procedure. Create or replace table is oracle construct. I do not think it is yet part of standard SQL although it is obviously very useful construct. So, it lets you clobber an existing table if it exists because otherwise if you say create table the database will say sorry it already exists. But if you say delete table the database will say if it is not there it will say does not exist. So, the question is why cannot we do it with create table? I am not sure why not. Logically it is a nice feature to have and every database ought to support it. Then finally, can you give an example of implementation of dynamic SQL? JDBC is an example of dynamic SQL. So, we have just discussed this in great detail. So, the question is what is dynamic SQL? JDBC is an example where you are basically creating strings and passing it to the database. As opposed to the SQL query being part of the programming language and being compiled by the compiler. So, that is slightly different. So, what is used in practice today almost always is JDBC, ODBC which are dynamic SQL. I think I will stop here. Thank you and bye.