So, let us discuss each of these queries and solutions. If you have proposed alternative solutions, let us know. There are more than one solution for most of these. So, take the first one. Select, this is the solution. Where is the, no that is not what I intended. Yeah, the solutions are available. Are they made visible? Just now they have been made visible. So, you can see it on your laptop also. But, I suggest you focus on the current thing we are seeing over here, because there will be interesting discussions. So, the first one is very easy, trivial. I suppose everyone would have got that. Select name from instructor. So, let us skip that. Now, let us look at the second one. Find the course ID and titles of all courses taught by an instructor named Srinivasan. So, how do you write a query in general?  If you are, use the schema a lot sure you know everything. But, since these schemas are new to you, it is worth keeping the schema diagram in front of you. If you want to write now also you may find it useful to keep the schema diagram, which is in chapter 2 of the slides. If you have written it down, that is fine. But, keep it with you to understand what is going on. If people are ready, this one says course ID and titles of all courses taught by an instructor named Srinivasan. So, what are the relevant tables here? Obviously, course ID and title come from the course table. The other part, who has or rather which courses has Srinivasan taught? How do you get that? The name Srinivasan would appear in which table, that is one of them. We are collecting what all tables we need to answer this query. The name is only going to be in the instructor table. The course ID is in the course table. Now, how do you know what course is Srinivasan taught? We have the teachers table. So, we have to join these tables. Do you need any other tables to complete it? No, because teachers has the instructor ID of the instructor who taught it and it also has the course ID of the corresponding course. So, we could join course instructor and teachers. So, we do natural join. So, we have course natural join teachers natural join instructor. If you run this query, it will more or less work. You can write this in SQL. How do you write it in SQL? Select course ID, title from course natural join teachers natural join instructor. That is one small glitch though. You have to watch out for whenever you use natural joins. Natural joins are going to equate any columns with the same name and you can get into trouble if you use natural join with unintended department name and credits. If you see instructor, there is ID name, department name and salve. If you do this natural join, what is going to happen? The department name should be equated. So, what would the query actually give? It will give you course ID and title of all courses taught by Srinivasan which are in the same department as Srinivasan's department. But there is nothing in our schema which says Srinivasan from computer science cannot go and teach a course in mechanical engineering. But you will lose all those courses if you just read it wrong. Yes, all of us have made this. I made the same mistake the first time I wrote many of these. Let us hope that the TAs who did this have done it right. And in fact, they have by the simple experience of not using natural joins. It is safer not to use natural joins. There is an inner join using class, join using which is safe where you specify exactly which columns you want equating. That is safe. So, this query here is joining the usual teachers instructor course. But the join attributes are casually listed. Instructor.id is teachers.id. Course.courseid is teachers.courseid and instructor name is Srinivasan. Now, let us look at this query. Let us understand what it means first of all. The id and names of instructors who have taught a course in the computer science department even if they are themselves not from the computer science department. So, what we need is actually courses in the computer science department and instructor id and name. And how do you link these up? Again teachers. It is in fact basically the same query except the selection condition changes and the output changes. So, this is identical except. So, now you will see that teachers instructor course. This part is identical and course.department name is from that is all we care about. We do not care which department instructor was from and select instructor id and name. I think even instructor dot is redundant here id only. The next query. Find id is of instructors who have never taught a course. So, how do you do this? How do you find someone who has not ever taught a course? So, you can use not in or not exist or except. So, there are several different ways of writing it. So, if you use the except way, what you will do is find instructors who have taught a course and find all instructors. So, all instructors except instructors who have taught a course that is one way of writing.  We will see this. I think the solution here is this one. So, this one is the not in version. You can also write it with not exist. So, this one is probably the easiest version. Select id from. Instead of where id not in. Yes. This is on set and that is under set. Select id. This minus that is a set minus set. Yeah or if you use not exist you can say where not exist select id sorry select star from teachers where teachers dot id equal to instructor dot id. That is yet another solution. I hope people have given these different solutions. We can put them together as the. No, it cannot be teachers or takes. I am sorry. This should be a section should be a section. This is a mistake. I think teachers. Teachers which courses which are offered. We are not saying if anyone needs to teach it or not. It is possible for a course to be offered as a section without an associated teacher although that is probably a bad idea. The schema allows it and since the English query said courses which are offered we should be using a section relation not the teachers relations. In fact this query solution use the takes relation which is even worse because it only includes courses which are actually taken by some students. We do have courses which are offered and nobody takes it. It does happen occasionally not often but it can happen. Yeah we can probably again if you use intersect there is no need even for this thing because intersect removes duplicates anyway. So, intersect is probably the cleanest way of writing this. What happened? Now the next one was find the id and title of all courses which do not require any prerequisites. So, how do we do this? We have to make sure that this course does not exist in the prerequisite table as the first one the one which requires a prerequisite. It can be a prerequisite for some course we do not care but it should not have a prerequisite. So, again a not in or not exist query is the simplest way but you could also do it using except essentially identical to this query to the previous query rather. So, select course id title course where course id not in select course id from prereq. Pre-req id is the id of the course which is a prerequisite. We are of course assuming that we do not have entries like course id null in prereq that should not be there. In fact, it cannot be there because we have declared course id comma pre-req id as the primary key for pre-req. So, there will be no null values there anyway. The last one is students will not taken any biology department courses. Again we can use the same kind of structure as the previous queries. So, we can make sure that id is not in takes where takes join with course because we need department name biology. So, solution is not giving that. What? That is that is. The solution is totally wrong. Totally wrong. Apologies for not looking at the solution. Yes, I have given select distinct name. So, one minute what is the query what did it want names of students. There is no need for distinct even. So, just select name from student where id we could do it either as id not in or let us do the other one where not exist. So, we have to join takes and course because takes has the course id, but it does not have the department id where takes dot course id equals course dot course id and course dot department id. Name equals and now this is the part we are not yet put any constraint on who is the student here in the sub query. So, we wanted for the outer student. So, takes dot id equals student and that ends the sub query in the rest course. So, again as discussed we can do it using an except the same way. So, we have all students except students who have taken a biology that will simply be a join again or we can use not in all the forms are equivalent. So, we have done I think we have done two of those forms. We have the exist and we have the not exist and we have the not in except is the one form we have not illustrated. So, there is a railway DDL. So, here we have the following queries find the names of all trains which have a stop at Thane. So, what is the schema? So, back to the schema we have train hoards station train and track. So, we want to the query was find things which names of trains which have a stop at Thane. So, that we can use from the train hoards and the station code, but the name is Thane. So, we have to use the station relation and equate the name to Thane and then join this with train hoards on station code and make sure it actually hoards at Thane does not just go through it and what is the condition? It hoards if the time in is not equal to if it is this is just another way of writing that query. We could have wanted the name of the train I guess. So, we have to join with train also the train hoards only gives us the ID of the train. So, if you want the name we have to join with train and train ID equal to train hoards at ID train hoards dot station code. Instead of joining this particular version has used a sub query, but that is not really required. We could have equally well just done a join that is probably simpler. In general it is better to use a join than to use a sub query. Why? Why is it better to use a join than a sub query? If it if the sub query helps to understand what is going on more clearly then that is fine, but if it could have been easily change into a join the issue is that many database systems like in particular PostgreSQL or MySQL for that matter are not very good at optimizing sub queries. So, if you run queries with a sub query on a very large relation they may actually choose a bad plan. Whereas, if you use a join they are going to get a much better plan. Now, it is possible for a good optimizer to say that this query is really equivalent to the join query and it can convert it and good optimizers actually do that, but the optimizers in PostgreSQL and MySQL they are they do a good job of join optimization, but with sub queries they do not do such a great job. So, do not use it unnecessarily for no good reason. Whereas, we are not in then there is a slightly better reason again except could have would probably guarantee a better solution, but sometimes not in is easier to read. So, if you are a one to one option is first write the query with not in or and check out the performance. If the performance turns out to be bad then you can look at how to rewrite the query using except for example. Let us do that before that time underscore in time in. Now, coming back names of all stations which are 20 kilometers from Mumbai.  What do we have here? We have station we have track. So, this distance is from one station to the next which are our assumption is that this track shows station which are connected directly by a track they are adjacent to each other. There is nothing in between that was the assumption in this schema we stated that also we discussed it. It turns out that this query I how did this query get in. In fact, if you think about it this is not an easy query. Why is it not an easy query? You can find out stations which are adjacent to assuming this track only has adjacent immediately next station not two stations away and so on. It is easy to find stations which are next to Mumbai which are within a distance of 20 kilometers. But what if this is actually the situation in Mumbai I mean there is no station called Mumbai, but let us say that is Mumbai central. Now, within 20 kilometers of Mumbai central there are a lot of stations. There is a station every 3 or 4 kilometers from Mumbai central. So, there are 4 or 5 stations in there. Now, how do I find out which all stations are within 20 kilometers? How do I find the distance of an arbitrary station? Let us say I do not know Burrivili. How do I know how far from Mumbai central Burrivili is? Does this give me information? How will I find it? From track. How do I find it from track? If track only has adjacent edges you know this is Mumbai central let us call this S 1, S 2 and then there is Burrivili. So, maybe this is 5, this is 3, this is the distance and that is 9 may be. Now, Burrivili is within 20, but the single track does not have 20. Now, let us how do we know the actual distance of Burrivili to the we have to add up these things? Some aggregate. Unfortunately, this is not so easy. This how far do you how many links do you go? So, if you write a query like this, select you know what is track station code 2, S T code 2 from track where S T code 1 well we can say track comma what is track station. Since, we are using the name station code sorry S T code this they are anyway separate 1 and S T code. So, we do not need to qualify them S T code 1 equals S T code and name equals Mumbai central if you wish. So, this will tell us all stations which are directly linked to Mumbai. Now, how do I find a station which is linked by 2 links. So, this one is 2 steps. So, this will give me S 1 and I can get the distance also S T code 2 comma distance. So, I get the distance. Now, how can I get a station which is 2 hops away? We can find things which are 2 hops away by doing another joint. So, what we will do is we will join track with itself. So, let us call this track 1 sorry call it track 1 we have to rename it. So, station comma track T 1 comma track T 2 and let us say this is T 1 dot S T code 1 equal to. So, there are 2 copies of track. So, what this will give me is if I select T 1 dot S T code 2 and T 1 dot distance that will give me things which are immediately adjacent, but there is a track T 2 also which I can use. So, what I am going to do is T 2 dot S T code 1 equals T 1 dot S T code 2 that will what I have done is T 1 dot S T code 1 is you know the station code for Mumbai and the other end of the track is the first end of T 2 dot track and the other end of T 2 dot is what I am going to use here. T 2 dot S T code 2 is this I will get S 2 and any other thing which is within 2 what about the distance? The distance will have to be I have to add up the distances T 1 dot distance plus T 2 dot distance. So, to get stations 2 away I had to take 2 copies of track and join them. So, I want to get 3 away I will get 3 copies and continue this I can repeat this structure however many times I need to get stations which are that much distance away not distance that many hops away. If I want to get things which are an arbitrary number of hops you know what do I do given any single SQL query like this depending on how many copies of track I have there I am limited in how many hops I can go. So, it turns out the query as stated here is actually very hard to answer because it requires what is called the transitive closure operation if because the query does not specify the number of hops. So, there is a all station which are in a range of 20 kilometers you know for all I know there may be a station every 1 meter of course not realistic, but if I do not know anything about the railway system I there may be 1 every meter and then I have 20,000 such things joints which I have to do in Mumbai they are not quite 1 meter, but within 2 kilometers is not at all uncommon 2, 3 kilometers. So, we may have even 7 things in between. So, we need a 7 way join here which is of course very boring to write such long queries. So, there is a solution SQL actually supports recursive queries. So, you have 2 options one is instead of writing a query like this you can write a loop in java or pls2k. So, what we can do though is instead of asking you to solve that we can always update the assignment. So, maybe what was intended here was I think the problem is the who are the TA who inserted this question assume that the track relation included all station pairs not just immediately adjacent ones which is why the query was written that way, but we have made it clear elsewhere that the track is only adjacent one. So, we will modify the query which says. So, now if you assume this is the question let us see if the solution provided with TA matches this. Now, we will change the assignment the database itself is that is not an unreasonable one the queries have to be changed. So, I have already changed this query the solution to it was the first one I gave without doing the extra join. So, we will put the solution in I will just delete this I have no clue what it is doing. So, this is looking for station not equal to Mumbai and track distance. So, this solution intended to be things which are adjacent away on that from the track. Now, what is the other thing doing I am not very sure I think this we made an assumption that if you have a track from A to B there will also be a track from B to A there will be two rows in the database. This query this does not make that assumption it is assuming that you may have this or you may have that and it whichever one is there it will work that is the intention of the query. But obviously, if you do not have both pairs that is if you have two pairs of stations we have stated already in the schema that we will have both pairs. If there is a track from A to B we will have A B distance 10 we will also have B A distance 10 and the goal of that was to simplify query. So, that we do not have to go through all this pain. Let me just delete that whole thing for the moment and we will add the correct one later. I suspect the remaining queries are also very complicated for exactly the same reason that assumption of symmetry was not made in the queries. So, the TS had a hard time writing the query I think the query is correct. If you assume symmetry is not there, but it is unnecessary and we did not introduce to write such complex queries. Therefore, I think we can safely well let us see what is the query I will delete it in just a moment. Find ID of trains which cover Mumbai, Thane and Umbarnath. So, what does it mean to cover this go through these things whether they stop or not is another question. So, maybe it is better to interpret this as cover meaning that they should stop. Otherwise, it is not very useful if it just goes through the station. So, let us interpret this as stop which go through all the stations and stop it. So, this is a not such a hard query, but it is a little lengthy. Now, let us see if the how would you do this. So, you need a train we have the train hoist relation. So, we can check with the train hoist relation if it hoist at a particular station easily with a given name. So, what we will need is let us see if this query is badly formatted. Let us go back to display and see what that solution is giving. So, this could be a good example of a complex little more complex query. We have seen the width clause. So, this is a query where the width clause may be used. So, what we will do for the December workshop is give hints on how to write this query instead of just giving a query like this and say solve it. We will add hints to help people structure their answer and then they would have learnt how to write a more complex query. So, T is just please note this if we will modify this query. So, what we want is a train which hoist at those three stations which requires us to join the train hoist train and station code. Now, we have to do the same thing three types. One is for Mumbai, one for Rambarnath, one for Thani. Now, if instead of writing the join so many times it is a lot easier if you do the following train hoist comma station where train hoist dot s t code equals station dot s t code. So, this is a hoist for that station and we are going to get the name, but we still have to make sure it hoist and time in not equal to time ok. So, this is going to give me all pairs of train ID and station name where the train actually stops at that station. Is this clear? And we have done the join with station here. So, that is the with clause. Now, what do we want select? So, from we will come to what is selected. We are going to have train hoist three times train hoist th 1 train hoist train hoist 2 and train hoist th 3 where now we want all of them to be in the correspond to the same train. This is just one train which has to go through all. So, th 1 dot id equals th 2 dot id and can you read the thing at the bottom. And th 2 dot id equals th 3 dot id. So, we have forced all of these to be from the same train. What we wanted is one at Mumbai, one at Amarnath, one at Thani or whatever that was. So, and th 1 dot station name equals Mumbai. And you can obviously fill in the rest, the other ones are for the other two. And th 2 dot station name equals Thani and similarly, blah blah equals whatever that last one was Amarnath. Is this clear? Are there other ways of writing this query? Anyone wrote this query in any other way? You could of course, have not used this and done all you know there are there is a pair of joints. So, you could have had six things here similarly, each of these would have been replaced by the joint. And then this condition and this condition all through. So, you get a very big query as a result. Whereas, this is much easier to understand. Actually the first this query is basically without the with clause it is doing something very similar. Instead of a joint it is doing something in something else. But this is the first copy as a temp. This is the second copy. So, one was for Mumbai, one was for Thani and the third one was for Amarnath. And then it makes sure that the IDs of the trains are the same. So, this query is correct. It is just that if you use the with clause to write it, it is a lot easier to understand what is going on than that one. Find the names of trains which are covering Thani before the sixth stop. So, there how do you know which stop it is? The sequence number does the sequence number give the stop? It does not. It just tells you what is going through. So, now finding the sixth act stop over there is actually not going to be easy. I do not think this query was also it does not actually do that. Now, it is just assuming sequence number less than 6. So, the English formulation was again messed up. So, if you actually want to find out if it stops, the sequence number is meaningless. The first stop may be sequence number 25 for all we know. So, for a Rajasthani express that is probably true. It is the first thought will be way above high. So, the solution, one solution to this problem is to change it. But I would not work out the detail. What if you did want the sixth stop? If you wanted to solve the problem as stated, how would you do it? I can get two copies of train hoist. Join them for the same train where the sequence number of the first one is less than the sequence number of the second one. So, these are all pairs of train hoist for the same train where the first one is before the second and the first one is actually a hoist. Now, if I group by train ID sequence number for the second one and count how many there are, I can find out how many hoist there are before this sequence number. So, do not understand what I am saying. Pardon me. Maybe I will come back to this later. I do not want to get stuck on this because of one more set of again let us not say covering. Do you want pass through or stop at? Stop at Thane that a reasonable phrasing is that clear which pass sorry which pass through or stop at Thane before the sixth station in the route of the train. If you go back here. So, this query matches the reformulated one. What is it doing? Select distinct name. Now distinct is important because well let us see if it matters. So, what we want is train hoist, train train hoist where train ID equal to train hoist. So, this is simply to get the name of the train and the train hoist sequence number is less than 6 and the code is basically Thane's code. So, this is the station is Thane. It is on the route of this train and its sequence is less than 6. So, this query answers the previous one correct the not the previous one. The new phrasing of the question matches the solution. So, that tends the first assignment. Let us get to that. The third one is find the ID. So, let us get to the solution here. Yes. So, what will happen here is without the distinct. So, let us modify that. So, point is if that person taught many courses in CS that ID and the name will occur that many times. So, if you want to avoid that we could. Yeah, that is a general tip. If the query is becoming very long. So, you can always give short names T I C and then use the alias here. Any other comments on the solutions? Now, let us move on to the second assignment. What I thought was the easier lab. So, unintentionally to have been may be the harder lab unless this one is even harder. Find the maximum number of teachers for any course section. How do you do that? First of all how do you know how many teachers are there for a course section? There is a teacher's relation which has the section and the ID of the teacher. So, how do I find how many teachers are there for a particular section? Group by the section's primary key count of ID. So, that will give me how many teachers are there for each section. What is this query? I want to find the maximum number across all things. Now, how do I do that? So, a simple query will give me the simple group by query gives me a number of teachers for each section. So, one way is to say where this is greater equal to all again of the same thing that is one way or equivalently we could use. We can just take the max of that. Since we just want the number, max is good. In fact, teachers already has course ID. So, there is no reason to join with course. If you see here, it is not doing anything useful. Teachers has ID, teachers has course ID. So, why join with course? So, we will remove that. Usually, it is done to reflect the fact that foreign key constraint. Making sure that course exists. So, that is what some of the database literature says. This particular case, the teachers, because it has a foreign key to course, the course will exist. It will not make any difference at all here. Luckily, it will not make any difference. It does not modify the count. In general, if you do a join, it could mess up the count also. But, you do not want to unnecessarily join with no particular goal here. So, you just want select count ID. So, we do not even need to know which course that was. All we need is the number of teachers for each course section. Again, this is a course or section. It is a section. So, group is also wrong. So, not for a course. If it was for a course, this is okay. Course section's primary key is what? It is course ID, section ID, semester. So, that will give me the number of teachers for that section. But, I think, Alice, we have to write as L. When we run it, it was not working. Which one? When we run it, it was not working. At last, we have to add some as something. Alice. Yeah, as L. Over here. As L. Give it a name. As L. As L. T. No, no, no. T2. Anything. As anything. Good. Good. Yeah. So, I think, there is some requirement in PostgreSQL that, if you just have a Alice we need. Subquery like this, it should be given in alias. Otherwise, it does not have any name. It could have used an internal name. I do not think the SQL standard requires this. But, PostgreSQL has this limitation. I mean, it does not cost anything to add as T2. So, this subquery, from that we are just selecting max T there. So, this is clean. Let us go to the second query. Find all departments that have the minimum number of instructors using a subquery. Order the result by department name in descending order. So, how do you know which have the minimum number? This time, we do not want just the number. We want to find the departments. So, I have created a view first and then. Yeah. So, there are several ways of doing it. You could create, use a with class. We have a similar query in the book slides also. Yes. Use a with class to structure this or you can just repeat the whole thing. It is more clumsy. Now, what is the solution given here? That is not working. What? It is not working. No, no, no. This is completely wrong. Wrong. That is not working. It is the answer is Wrong. It is completely wrong. What is this doing at all? This is finding the, what is it doing? Something. Finding the number of instructors in each department. Yes, yes. It is wrong. It is wrong. But, after that it just. Order. Simply descending. Order. Not working. Whereas the query clearly said. Yes. Only find those which are the minimum number of instructors. Not even doing minimum. What is going on? I think I need to fire all my TAs. Get a new set of TAs or roast them or do something. There are too many mistakes here. Okay. And I should fire myself also for not cross checking all the answers before coming here. Anyway, so. You are with view in subject. So, how would you do it? You would use a, with class would help a lot. So, first of all we, for each department we need to know the number of instructors. So, we can say with dip count or something like that. Department name. Number also. Num instructors. Number. Asselect. Asselect. Department name. Department name. Count star. From instructor. From. Instructor. Instructor. Group by department name. Group by. Well close. That's all. And as they say close but no cigar. This is missing out one special case. What if a department doesn't have any instructor? What is going to happen? We have, whenever you write a query we have to watch out for this one special case. You know. If we have a instructor the department will not appear here at all. So, then if we find the minimum on this the department will not appear. So, if a department is 0 that is the minimum. 0 should be there. But it won't help. It won't help. So, what we can do here? How do we ensure that we get a count of 0? There are several possible solutions. The simplest solution to make sure every department appears is to use department we can say natural left outer joint. Space here. Space. Group by department name doesn't change. Now the one other thing is if I do count star. If a department doesn't have an instructor, count star would be 1. It would not be 0. Because count star will always, there is one tuple. But what distinguishes this from the other things is that the instructor ID would be none. So, if I want to know how many instructors there are in the department, instead of count star I can do count. ID. ID. ID. Yeah. There is only, you don't have to make it unambiguous. It is unambiguous. Count ID. And will the instructor get duplicated? No. Instructors are only in one department. And it will just match here. Exactly. This is the number of instructors for a department. Except for if a department has no instructor, instructor ID would be null. Count of any attribute like this eliminates null before giving the count. Therefore, it would be 0. As an alternative, we could write a sub query which finds the count. That's the other. Both are there in the book. They are described. You can go look it up later. So, there is an alternative. But this one gives us the correct count. So, now with this the rest of your query would be the same. Please. So, what is the rest of the query? Select department name. Yeah. Select department name. Select. Department name and. Department name. And what is that? Number or what? Num instructor. Yeah. Num instructor. From department count, DEPT count. We need to find the min. Yeah.  Yeah. From the same department count. Yeah. From. DEPT count. Order by. Yeah. Order by this. Yeah. That one is over. Order by. Order by department. Name. Name. Descent. Is this query clear? Please note this query so we can enter it later. Okay. So, we are still on query two out of how many more queries? We have almost eight more queries and the descriptions are becoming longer and longer. Okay. I think this assignment was probably a five hour assignment at minimum. So, again we will cut down the questions. I think we told you this was optional. Okay. We said it is part of the disumber lab but I think we will remove this. So, you will not have more than six questions for the disumber lab. Okay. So, now what is the next one? For each student compute the total credits they have successfully computed. This query is actually directly there in the book. Yeah. If you want to look it up. It is easy. And it says do not use the total credits attributes of students. That is a materialized view of some kind which can be computed from the underlying relations but it does not actually match in the book schema. I mean book example schema data rather the number does not match. So, we could run this query. How would you do this? How do you compute the total credits? We have to see which courses they have taken and completed successfully. Meaning the grade is not null and it is not F. So, the takes relation will tell us which students have taken which course and the grade. We can eliminate from their grade rows where the grade is null or F. So, that gives us students who have passed a certain course but we do not have the credits there whereas we need the total credits. So, where do we get the credits from? Course. Course. So, we have to join takes with the course relation. The course relation has the credits. So, we have to join it and then for each student we need the total credits. So, what do we do? Group by. Sum. Student ID and sum of credits. So, that will be the query. Let us hope this one is correct here. Yeah, that is working. That is fine. That is working. Now, it should have been. But not. ID, not name. Null also. Yeah, the null part. Not equal to null that also. Did the thing. Yeah, for each student. So, we can interpret student as show the ID rather than the name. So, let us correct that. Takes joins student.course, where grade not equal to f. And not equal to null. And of course takes.course, id is course.course, id is student.id. Group by id. We actually do not need to join with student here unless we want the name. So, if you want the name we join with students. Otherwise. Otherwise it is not required. Takes itself. Takes is enough. It has the id. So, since the query did not. Well, we can interpret the query as we want the name also. That is better. In which case, but then we have to do one more thing. If we output name here, the query is not quite right yet. We need one more change. What is it? Group by id comma name. Any attribute which appears here also has to appear in the group by list. Unless it is aggregated. Name, id comma name. Some SQL implementations will actually let you get away with this. Because, one course from Srinivasan. How do you do this? We have to figure out who took a course taught by Srinivasan. How do you do that? Find out all courses taught by Srinivasan. Yeah. So, how do you do that? The Srinivasan is in the instructor name. Yeah. So, you have to join that with teachers on instructor id. Select different course id. That is all. Now, you go for students and if it is taken courses equal to sum. Equal to any of this set. So, you could do that. So, you can find students whose id from takes. Actually, we need takes also. Takes and takes. The section has to match the teachers section and then from that we get the student id. So, we can actually just do a join of instructor, teachers, takes and from that we can get the student id. Then, we need to know the number of students. So, on top of that we can do a distinct id. Distinct and constant. So, yeah. Yes. Select count distinct takes dot id from. So, the join is instructor takes teachers. Takes course id equal actually this part is wrong. It is not just course takes course id because Srinivasan may have taught the course once and somebody else Ram may have taught the course another time. So, if you look at only the course id you think that this student has been taught by Srinivasan. But Srinivasan taught it in 2009. This student took it in 2010 when Ram taught it. So, the join of teachers and takes should cannot be just on course id. Did you get the point? What does the join have to be on? It is on the section. It should be the same section. The student took a section taught by Srinivasan. So, what is the primary key of section? How do you know that section from teachers is the same as the section from takes? We need more things here. We want and takes dot sec id equals and takes dot semester equals teachers dot id equal instructor id instructor name is Srinivasan. So, that completes that query. So, again for December I think we will give some simpler queries also. All these queries are hard. They are hard as evidence by the fact that several of our students have goofed up on the answer. So, we better make it a bit simpler than this. We will give some simple queries to start with and a few of these will be retained as harder queries. But with some hints so that it will help the people to solve it. So, we will make all these queries and the solutions available to you before the workshop. So, you can go over it, give your comments. If you feel something is too difficult or it is not clear what is going on, please give us your feedback. So, before the workshop we have something which everybody here is comfortable with. That should be our goal. So, before the workshop you should know the solutions for all of these including alternative solutions. You should be comfortable with it because you will be acting as TAs not TA. You will be the teachers for the lab sessions in that part. We won't even know what is going on. We are not going to be involved. You are the boss for these sessions. That is one possible solution. Let us wrap up in a few minutes. We are behind time. Names of instructors who get the highest salary in their department. How do you answer this? I think this should be hopefully simple enough. Sixth is tough I think. So, this again you can do it different ways. So, this sub query is finding for each department name. This is the maximum salary in that department name among all instructors in that department. So, we do not need the department relation actually. We just need the instructor relation. Yes. Group by department name and find the maximum salary. And also we need the department name because the outer query is going to take instructors from that department whose salary equal to max salary. I have used width function. You used width and made it. Yes, you can do it. This has a small, again there is a small error here which we need to fix. This assumes that max salary is called max which is not quite right. Where did that go? Either we should give this a name outside or give it a name here. So, let us call it max cell. If you just did i2 dot max, what is that? There is no column called i2 dot max. Unless postgresql happened to call that column max. There is no guarantee what any database will call it. If you just say max of salary, it can give it any name. Some internal name it gives. Last one for the day. We are not going to cover these today. Sixth is tough. Now, this one is find students who have taken all courses taken by… Relational algebra is easy, but here… Relational algebra is easy, but here… You can just put in the division operation and you are done. But here I tried and just… So, again if you have access to the book, there is an example. Even the slides it is there which shows how to express the division operator, the for all operator using SQL query. So, it is there in the slides. So, you can take that and map it to this slightly different thing. So, in general how do you do this? This is like a for all query. For all courses taught by… In this case it should… Should we call it courses or… We have to first interpret this correctly. Do we want them to have been taught by Srinivasan? Or if Srinivasan has taught a course, these people should have taken that course. It does not matter who taught the course. So, there is some English ambiguity here. So, we have to first rewrite this to make it unambiguous. What do you think this means? Does this mean they have… It could mean that Srinivasan taught 101. This student should have taken 101. That is probably what… If you will literally read it. If you would say course, then you could say 101. If you say course section, it has to be actually that taught by Srinivasan. So, let us match the query to the answer. What does this answer do?  So, this one is ignoring the section aspect. So, that is easier also. Ignore the section. So, we will update this to make it unambiguous. So, they should have just taken the course if at all it was taught by Srinivasan. So, how do we do that? It is always an except. So, first find the courses which were taught by Srinivasan. Take the courses taken by this student and do an except. So, if there is a course taught by Srinivasan which this student did not take, then this will be non-empty. The difference will be non-empty. And that will be the condition here. So, we… Let us look at this here. So, the outer one is takes with… Well, the join with student over here is simply because we want… If you want the name, otherwise that is not required. But look at this not exist part. It is a not exist. Let us take the first part. So, this one is finding all courses taught by Srinivasan. Again, I think there is a redundant join here. We can simplify it. Let us go down. So, teachers and instructor is required. The join with course is redundant. So, now a little simpler. So, can you read this? There is no course dot course ID. That would also be course ID from teacher's instructor where instructor ID equal to teacher's ID. Instructor name is Srinivasan. So, this is giving us all courses that Srinivasan has taught. Now, we are going to remove from this all courses taken by this student, this guy. So, how do we find out what all courses the student has taken? Again, this course is redundant. We are going to remove that. So, what is this query doing? Select course ID from takes where takes T where S dot ID. S is this outer one. This takes. Actually, we do not even need S dot ID. We can do student dot ID where student dot ID equal to T dot ID. So, for this particular student, it is getting us all the courses which that student has taken. So, now if we remove from all the courses that Srinivasan has taught, we remove all the courses the student has taken. If the student has actually taken every one of those courses, the result will be empty. If the student missed even one course, it is going to be the endless. So, the condition here is not exist. Not exist means it is empty. If it is empty, the student has taken every course. Therefore, they should be in the output. So, that is what we do here. And that query is now correct. Any questions about this query? The outer one is joining takes and student to get the ID and name. And the inner one is the sub query is the standard form for the division operator or the for all operation. To take the full set, remove another set and check that the result is not empty. And note also that the second set here would normally have a condition from the outer one. This condition would come from the outer correlation. So, the inner one is being done only for specifically the one outer tuple we are considering at any point of time. So, we will do this for each outer tuple one at a time.


So, we're here today with Dr. Nate Holmes, colorectal surgeon at Hackensack Meridian Bayshore Medical Center and Olbridge Medical Center. I know what you're thinking, there are no more buts when it comes to your butt. But, things like, but I don't have time for a colonoscopy, but I'm healthy, but I don't have symptoms can be really bad for your butt, right, Dr? Yeah, that is so true. I love that. You know, just need to get their colonoscopy. It really is a fairly straightforward and easy test. The excuses that I hear all the time, you know, people just don't want it done. They say that they feel fine. They have their bowel movements every day like clockwork. They have no problems with their GI tract.   The earlier we find the tumor, the better, which is the purpose of, you know, screening colonoscopy. Now, what do you say to the people who think colonoscopies are painful? Oh, it's so wrong. It's such the opposite. It's such an easy procedure. So, you know, they come in in the morning. The worst part of the morning is they put an IV in and that generally is not that painful.  And that's today. The hard part of the procedure is, you know, doing the prep the night before.  But it's so important. What are the early warning signs of colon cancer? Well, there are a number of things you can have. You can have a little bit of bleeding. You can have a little bit of abdominal pain. By and large, the real problem is most often, there are no symptoms.  Doctor, the bottom line on this whole thing, I think what we've learned today is no more butts when it comes to your butt, right? Absolutely. No ifs and or buts to tease. And yeah, gotta get it done. Doctor Holmes, thanks so much for the info. To learn more about cancer screenings, visit hackensackbrideanhealth.com slash cancer and select colorectal cancer.


Hi, welcome to first post. We are here to discuss Rally for Rivers with Satguru Jaggi Vasudev followed by millions of people across the world On this on this what I call ambitious which you will hear Jaggi Vasudev Ji saying why it is not ambitious in this in this beautiful fabulous lush gardens of Raj Bravan and Overlooking the sea we are going to be discussing. I think as a perfect setting for discussing environment and how to repair what we have done to this The environment in the last 25 years as we keep saying Sir, welcome to first post It's about 40% of your rally underway now. It's over. So what's it been like? So till now we've covered seven stage including Maharashtra The seven states are ruled by six different political parties But in one voice they're supporting this this is incredible and People are telling me the media is telling me In the history of independent India, this has never happened that all these parties are agreeing to one thing and Not only agreeing supporting and making a big noise about it in their states This is wonderful. I Know this is a very difficult thing and that there'll be so much criticism about it but I want to acknowledge the political class for Standing up for what truly matters to the nation Small things they may be fighting on a daily basis when real big things come They stand up and in they speak in one voice. I think it's a fantastic thing at the same time the Puducherry chief minister was sharing when the public he said Satguru 24 years ago when I was in Rajiv Gandhi's cabinet The experts at that time prepared a policy which is very similar to what you're saying But we just could not get the consensus from the states. We tried very hard, but we could not get the consensus But now all the sixteen states I'm driving through and many other states which I'm not driving through They're all calling and saying we are with you. Please give us the policy. We will implement before the center path So consensus, which was the biggest hurdle effortlessly we've crossed Thanks to all the political leaders who made this happen Now the next step is a policy like this a comprehensive policy which has many layers of complexities in implementation It is not simple There are many many challenges legislative challenges legal challenges and administrative challenges and social challenges Apart from that, there is a large financial outlay and there is a gestation period which is not considered reasonable by most people If you aggressively implement this this will take ten to fifteen years of implementation And it'll take another five to ten years for actually reverse to rise It will rise only fifteen percent or twenty percent, but visually you can see rivers have come up. I Don't go by how many thousands or millions of q-sacks of water. That's not the point visually For me in my experience from the age of twelve to seventeen. I used to swim in Cauvery almost every day Now if I go to the same places I can just walk across In twenty-five years time, I want to see that once again people will be swimming in the same place Why I'm saying this is this is a real-time real things Somewhere figures wig figures scientific figures That's not what we want to see Real-time if you want to see rivers actually came up. It'll take twenty to twenty-five years This means four to five Governments would have come and gone by the simple democratic process So I'm saying such a long gestation period many complexities in implementation and it's not an election winning policy So who will do it Unless people of this nation give a clear call that we want this that we have matured We are not a freebie generation We have matured as a citizenry if you do something that is for the long-term well-being of this nation and the future Generations we are with you. This is what I'm asking them to do with this phone call I am saying a minimum of thirty crore calls must come Why thirty crore is this is forty percent of the electorate. This is the election winning number So if if forty percent of the electorate clearly in one voice say We want you to take long-term steps for the reverse not ad hoc steps and saying I'll give you this I'll give you that if we say this Government cannot ignore this Because people tell me oh, why can't you just talk to the prime minister? Why all this? They don't understand the complexity of the policy It is not a simplistic policy when I talk to the media. I'm talking one kilometer. That's not how it is That is a minimum we are speaking Depending upon the topographical features and various other soil and other conditions There are many things to be done, which is very complex There is much science involved in this Implementation is not going to be okay.  So how have half a baby, you know Solomon's wisdom So it's not going to work like that So for this to happen to push the government to take that full step Which is not necessarily a populistic step to take that people must Say in one voice instead of I'm asking for minimum 30 crore if you give me 60 crore calls Tell me which government will say no How can they say no? Yes That's what a democracy is So that's what I'm trying to get on the way of course there is many million I mean million varieties of situations. We are such a diverse population above all We have too many armchair environmentalists We've never done a thing on the ground and we have Scientists who are knowledgeable, but they're sitting up there When I the comments that are coming to me Generally overwhelming support everywhere, but the small voice which we want to hear because they've done work They've done genuine work. There's no question about it We want to hear their voice because we want to get every input we can get we have been consultation with some of the top Scientists, but some of the scientists are sitting up there on the tawa They're saying unless you pull down the dams Unless you pull out all the cities which are on the riverbank. There is no solution. So the guru is getting corrupt He's trying to please the political class. Tell me pulling down the dam and pulling down cities it ever going to happen You think so That means you don't want to do anything you only want to earn a PhD I Want something to happen I want to marry science and politics So that both of them make compromises and find a solution. This is what the policy is about it must be economically Sustainable otherwise people will make sure it never happens. Okay We have many idealistic policies like this which never come on to the ground Because we don't take a practical step so We're trying to make this as balanced and as practical as possible at the same time that it will have significant ecological impact So in this effort We're driving through as you said 40% I didn't think of it that what percentage I was only thinking the most difficult part of the journey is over because Six different parties we've covered all of them full-on the next states. We are going are all one party. I think it's a easy run So there's a lot to discuss especially on the marriage of science and politics you said I think it's a great phase phrase from what you have just said Sounds to me like The the politician is not the unwilling bride. Is it the scientists then what the kind of See they're like two different religions. It's like getting a Hindu and a Muslim married So you're sure you're you're travel some distance in answering the scientists objections to this like See Those scientists who are very academic in nature They may know they do know many things which is very important But at the same time you can't turn the country into rainforest once again How it was thousand years ago, you can't go back. There's no question about it How to do what we can do for the future without disturbing Human settlements without disturbing the economic process anything against the economic process you talk. It's a sure failure. Okay So we are seeing how to in many ways reinforce the economic Process, this is essentially an economic plan with a significant ecological impact And that's the only way it'll work All the five stakeholders are addressed here first stakeholder is a river The second store a stakeholder is the life that the river sustains When I say life India as a subcontinent has the highest number of species living off the rivers I don't know why it is so the more I look at it the more amazing it is this small piece of land Probably four five percent of the country's worlds landmass in this there are more species of Plants and animals then you will find anywhere There is no Geographical explanation to this because there are many countries of the same latitude It's it's amazing. It's amazing means It's mind-boggling. Why is it? Why I've been thinking why is it? I you know right from ancient times our sages and our scriptures and many others always claiming That this is the greatest land. This is the most whatever sacred land I Thought everybody loves their country. Everybody loves their language. Everybody loves their culture. That's okay I'm not somebody who gets emotionally moved by these things But now looking at the biodiversity of this country. I'm wondering probably they knew that's why they were talking like this Because it's amazing I cannot put numbers on it. I'm not some kind of a scientist or an environmentalist But when I look at it from what simple understanding of outside, you know from like an outsider looking at it It's almost like if you take entire North American continent, which is probably many times over India Probably we have ten twenty times more biodiversity Why I'm not able to explain why I've been asking this question to whoever seems to be knowledgeable about these things Nobody seems to know why it's just there. That's all we know What is it that made this land like this that it's so rich So the owners and responsibilities on us that we keep it that way and in future Having this many species in our land may become the greatest asset Yes so Are you saying that? the Opposition or the criticism that is happening about this is this is No, criticism is just very minuscule. I am not bothered about that. It's there because we are not doing this unilaterally We are consulting the best scientists in the country and they're I You know that I don't want to name people right now We will acknowledge them later because they don't want to be into the you know I'm I'm somebody who sticks my neck out all the time for anything and everything They want to go in a certain way. They don't want their names out later on will acknowledge those people The top water experts in the country. I sat with them and said see I Said see I don't know any of your science nor am I an environmentalist But I'm somebody who's engaged with life Right from my very early childhood. I don't go anywhere without being involved with what's around me So I pay attention in my attention and my simple sense This is how I see it and I explain to them in about two hours time what this policy means Then I asked is the science with me They said in one voice that guru science is hundred percent with you. Can you get the policy politics? So that's when I said I'll get them See I've been Engaged with mountains forests and rivers right from my childhood When I say engaged and involved not as seeing it as a resource or a natural beauty not in that sense I live there and I've always experienced these things These situations of being in jungles and you know floating down the rivers this always made me More about how insignificant a life. I am That tomorrow if I if I drown in this river Everything goes on fine People like you and may come and go in millions, but the rivers and forests have lived for millions of years and lived on But that's come to a threat now It may not flow many of them are not flowing So last 25 years I've been watching this depleting So about 20 22 years ago. We started this project green hands with that we planted over 32 million trees now It has increased the green cover in Tamil Nadu Well, we've changed the culture in many ways people have become much more conscious It spawned hundreds of other organizations which are doing similar work, which is wonderful But in the last seven eight years, I'm seeing that drop in the river waters is so steep Alarmingly steep in the last seven years. I don't have a hundred percent explanation. Why it's suddenly going like this I think in my mind out of simple common sense. I'm saying I Think it's because the bore wells are sinking over fifteen hundred feet. I think directly these aquifers are just sucking out the river water and going into the bore wells and Unfortunately many NGOs and others have dug up rivers They've dug up rivers like canals I heard you say that this is the murder of the river, you know This is to please the local farmer, but after some time near the father farmer will benefit nor the river will benefit nothing will benefit So maybe it is because of that or maybe there are other factors Maybe there's not enough vegetation. So the water is not getting in everything is just going as a flood Whatever the factor last seven eight years the steep is very alarming. I have been watching with with some You know consternation But I Try to encourage other people to do this because I didn't want to get into this my fundamental work is spiritual these are things when When they irritate me beyond a point I get into action So I try to encourage other people who are involved in this type of work, but then I saw Nobody seems to have a breath of understanding And if they have understanding, they're not practical people Being practical is very important because otherwise we will talk great things and do nothing So I jumped into it. I got everybody into it the entire foundation Three months all Isha Foundation's activity stands still Yes, millions of people are on the job They're all standing out on the street from morning to evening doing rally for rivers It's been an incredible run till now and I'm sure things are only going to build up But just building up this momentum is not the goal. The goal is the government should feel confident That they can take such a long-term step and still have people with them Otherwise if you do not make an elected government comfortable, it's not going to happen. I'm very sure about that So you mentioned somewhere that there is a problem for every solution in India I didn't say there's a problem. There are people who have become experts in finding a problem in every solution Okay So in that sense, there is this directly It must have irritated you enough, but I'll ask you the question nonetheless That the vehicle you're driving is producing so much Poisonous gases. Anyway, they got the basic arithmetic wrong. Yes, I don't hear that So This is about people who have a squint I want them also to make the missed call but Let me tell you The vehicle I'm driving Is it legal? It is Mercedes openly advertises the g-wagon that I'm driving Mercedes openly advertises it It's a legal vehicle cleared by the government of India. It is a euro five emission standards It's the highest emission standard on the planet on the planet. Yes Only thing is some people think Their opinion is more important than the law of the land. This is what I want to change This is what I want to change even with the environment Your opinion is never about the law of the land If you think the law is not meeting all the aspects you must try to change the law Which is what I'm trying to do right now I feel the laws are inadequate to protect our rivers. So I'm trying to change the law But the moment you think your opinion is bigger than the law.  That's what you're trying So you want a lynch mob of a nation? No Five want a law abiding nation So if I have broken the law, tell me ban the vehicle. I'll drop it here and take something else and drive But any of them we thought it's 7,000 kilometers already It's 4700 only 40% probably will cross 9,000 kilometers for all you know 9,000 kilometers in 30 days Every day I'm doing a minimum of 10 to 12 interviews and meetings and on the road Stopping wherever people want me to stop this ask any one of them to do it People half my age. Let them do it. Let me see. So I'm saying you are trying to find a problem in the solution. All right Now the problem you you have is that you don't understand how much Ecological damage you're doing. Anyway, they are shooting all this stuff in a studio. Let me tell you the BBC has released this numbers a television still studio per hour generates 820 kilograms of carbon Okay Please for all the bug was that's happening With all due respect to news channels and others who are doing useful work I'm saying so much of it happening the entertainment staff reality shows everything All that you need but if I drive you want me to drive an auto rickshaw 9,000 kilometers Tell me at least tell me which brand I should drive. Please sir You give me a suggestion which brand should I drive you drive through the many of these Stretchers we are doing in the night Throughout the day. We have something going entire day and then driving in the night Look at me. I'm a old man At least some comfort and safety. Have you ever driven long distances on Indian roads? How dangerous it is to drive in the night? Every moment is a possible that you could get into a mess. Okay So if I drive a car, which is fit for that kind of journey, which is safe Which is Reasonably comfortable. It's not the most comfortable vehicle. Believe me. It is reasonably comfortable And above all it's legal So if you want to form your own law and Pursuque me for that.  Please stop that Let's make this into a law-abiding nation. Okay.  It doesn't matter. You don't like me It doesn't matter because you don't have to marry me nor do you have to take me as a guru All right, you don't have to do any of those things. Please give a missed call Because this is for the future of this nation and for our children. And I'm sure I'm sure Somewhere deep within your heart even for you. It matters Please do that. Say some more nasty things about me, but make sure the nation gives missed calls So in a sense are you saying sir that the 28,000 kilo tons of gases or whatever that this journey is supposed to end Are the best bad gases that will be produced because the Purpose is something much much. No, no, I wouldn't say that anyway. They got the arithmetic all wrong They said some eight lakh trees if you have to plant no, it's actually some 2,500 trees. Anyway, I already told you we planted 32 million trees now I have signed the MOUs the 50 crow trees here and 25 crores in this thing and all the government lands in Tamil Nadu openly the chief minister declared Satguru will give it to you you plant how many ever you want So compensatory action will be done. But anyway If I am that is a test vehicle if I am not drying somebody's testing out Anyway, it's going to be out there and the Maindra vehicles are also used vehicles My vehicle has already done 30 30,000 kilometers Maindra vehicles also Used vehicles all given to us for 30 days of usage We did not buy any of them all of them given to us this thing It is wonderful. The industry is looking at it like this Though they know when we pass ecological loss, it may be it may hit them in spite of that They are sponsoring usually they will never sponsor anything ecologically related stuff because it always goes against them Now they're sponsoring it. Should you not appreciate that instead of? At least learn your arithmetic properly You you mentioned stakeholders in this I find for this to actually Succeed finally if it is up if it is made into a policy and all it is the farmer So what is the outreach to the farmer? Have you already made some outreach or? See Going and preaching and teaching the farmer is no use So we have made small-scale demonstrations How a farmer who shifts from crop-based agriculture to tree-based agriculture His income can multiply three to eight times in a matter of six to seven stated in India. Yes. Yes in Tamil Nadu Some things have been done in Maharashtra also by other people Now what is needed is a large-scale demonstration in every state We want to create a large-scale demonstration which all the states have gone through I've suggested They've all said hundred percent they will item they said you choose the river where you want to do it because I want to choose a small stream Where the difference can be seen? If you go for a large river, you may not see it If you go for a small stream in four to five years, you can show a difference that is for ecological purposes But in three years time or five years time, you can clearly demonstrate Economically, this is much better than what you were doing Some Vietnamese experts were with us for a few days as a part of this rally In their country the farmers who shifted from paddy cultivation to fruit cultivation Their incomes went up by 20 times 2,000 percent We were amazed. We said how did you guys do this? They're laughing at us They said Twenty-twenty-two years ago, we all came to Indian universities Agriculture universities we studied there. We came here and implemented what we learned you guys have all earned PhDs They this is exactly their words. I'm telling you. Yes So all the knowledge is sitting there, but in this country everything is sitting in silos Now we need an agency or an authority Which will take this knowledge and technologies which are there to the ground our farmers are still irrigating the lands as they did a thousand years ago and They think it's the right in Telangana and other places. They were saying Because nine hours in the night is when they're getting the power when the other load is less They're getting in the night. All of them have automatic turn-on things So everybody's farm. It's automatic on nine hours. Simply water runs. Okay, because they'll sleep. Yes So things like this are happening eighty four percent of water usage in the country's agriculture In many other countries for the similar crops that we are growing their yields are much higher But the water that they're using is ten to twenty percent of the water we are using Very easily with with small interventions not with major intervention with small interventions Not some super technology some simple micro irrigation technologies if you bring in Very easily we can bring down the present consumption of water In the country to 30% for agriculture 30% We are telling our children when you drink water every drop you must drink as consciousness. It's good But it doesn't solve any problem Eighty-four percent of the water is consumed by agriculture Very easily you can change that so I am suggesting this not to be compelled on a farmer Where they are willing we are thinking of forming large FPOs, which we have already done around Coimbatore Our FPO which is only three and a half years old is being today rated as the best FPO in the country But let me admit you on camera admit to you on camera. We've done nothing Okay, we've done no work on the ground all we have done is three of our volunteers are using their cell phone renegotiating prizes and Directing them which market they should go what they should do. This is all we've done nothing on the ground With this farmer's income has doubled This is all three cell phones. We're using probably four or five hours a day They are on phones talking to everybody here there. This is about the job. We have done On the ground if we work which we want to do now We can further multiply their income if you do the right things on the ground No work on the ground. We've doubled the income of the farm. So we want to form large-scale FPOs Let's say Thousand farmers come together and they own ten thousand acres. I'm I'm just shooting the numbers, okay? Ten thousand acres are here a river is flowing here Now government should give access to the river water Now each farmer puts one one borewell in ten acres. He puts four five or wells I met a farmer very close to our place three-and-a-half acres. He has a 27-year-old farmer. I Am asking what would you do for your water? It's a rich place You know, it's a fertile land He's put nine borewells in three-and-a-half acres of land Nine borewells will cost one crore rupees When is he going to earn this money? Either he has to leave town Sell the land or hang himself This all the up. He's driving himself into it Because people are advising borewell companies are saying one more borewell. You will get water one more You will get water. It's like gambling. They're just hitting it Borewells and irrigation systems are the most expensive part of farming Yes Once they get into this kind of loan, he's not going to be out in his life this one crore rupees I Don't know if it's from the banks of the private loan agencies So somebody will come and take his land or somebody will harass him to death one of these things will happen So what I'm saying is ten thousand acres We want this to be outsourced Irrigation to be outsourced because there are companies which are already doing and ready to do but there is so much emotion about it Oh, you let somebody on your land. This will happen. That'll happen So just outsourcing clear-cut loss that they'll never have rights over the land But the pharma has to Keep his commitment that if his contract is signed for ten years ten years there for sure He can't break that either we we are looking at legal experts to form law, you know framework like this Legal framework like this that outsourced the water irrigation Then just to give an example See in Karnataka, I have been doing farming in the past. I was getting around 210 nuts per coconut tree. I Know farmers who are getting over 250 nuts per tree In Tamil Nadu, the average is 110 nuts In Kerala, it's 80 nuts Okay Because in Tamil Nadu and Kerala, they're largely flood irrigate Coconut trees don't like it They just need 80 to 90 liters per day If his rainy season and it's reasonably soil is wet 25 30 liters per day I'm saying if a company is pumping and it costs money for them to pump They will just do the right thing When the yield comes you have to share in percentage or you must give a fixed fee Per acre so much, whatever we arrive at now the company irrigates for you Farmer's life becomes easy Whatever you're growing you have to go to a farm only 60 days to 65 days in a year Rest of the time you can do some allied industry Value addition some of the business you can do something else farmers income will multiply in that also not just in the farm Very easily the farm yields will multiply because fertilization also will happen through this irrigation system And it will happen efficiently They will fire they will hire qualified people to do this job and water You will bring it down to 25 to 30 percent of what is being used right now And this is the solution for the future, but there is so much emotion about it Oh my land if somebody comes and it is also privatization Many people who will say privatization. That's the best way to that is the whole problem They don't understand We have taken a step away from communism and socialism We have decided we will go the commercial way Every day if you want to take a backward step, there are a whole lot of people who are stuck in socialism It cannot work like that. All of us have to decide. This is the policy chosen by the government and As a nation we have chosen this once we choose something going on debating whether it's good It's like you got married 25 years later. You're still debating.  It'll something will happen. That's how life happens So once we have chosen a system you've grown go on debating which is the best system No system is good if you ask me, okay But you can make every system work There will always be a flip side to everything But you can minimize the flip side and do the best that you can do Every day you take a forward step. Somebody wants to take a backward step This should go and we must understand this is not a joke because 300 million people are still not even eating You go into a village and see today. I'm talking about the better off-stage.  You're talking about demographic dividend. Where is the dividend? Only if you produce quality human beings full body full brain and some skill then it's a dividend Otherwise, where is the dividend? So considering that Politics especially politicians especially are petrified of pushback from farmers and that Limits them from doing various things that then I want to do. So how do you think this is this entire grand plan? How will you align the farmers see 25% of the land is in the government Self is four to five years of work. Oh full-scale if you go so start there We will start only in the Just now somebody was asking me that guru there is this thing Hill Can you plant this hill for us? Two hills are there in our village, but it's not catchment area. There's no river I said Every square inch of land is catchment area If you plant enough you will see streams will start flowing from a hill which was dry We've seen this happening. Okay in many places Not only done by us many other people have done this Unite in United States the New York state did something very revolutionary In 1984 or 5 they wanted to set up a filtration unit for the New York City The budget given was $9.2 billion filtration for New York City Instead of that the administration at that time took this stand Instead of investing 9.2 billion and putting up filtration. They instead invested 1.5 billion dollars and Planted the entire region six rivers are feeding New York City all the six rivers. They did extensive plantation New York City is one of the few cities in the world which drinks unfiltered water because of the plantation At 1.5 billion they did that Any number of examples in the world So now Telangana and Andhra Pradesh are going full-on full-on Andhra Pradesh is aiming to make 50% of the agriculture land into horticulture He openly his mission is declared and Telangana is doing a fabulous job of reviving Tanks and ponds of the old system Kakathia kings had done so they're calling it mission Kakathia They are doing a wonderful job other states just have to emulate I'm saying Policy will come so that deviance can be handled, but why can't we do it voluntarily? What is right? So about the farmer we will never pressure the farmer. We will set up demos Large-scale they can see the yes See this demo will not succeed unless the industry comes in so I am I'm already been Talking to the we spoke to the CI council and many other industries. We brought them together on one platform First the industry must invest Then the farmer invest right now farmer invest if the produce is good then the industry comes if it comes Otherwise, they don't come for whatever reasons first industry must invest After that farmer will invest a guaranteed by bar back cold storage you will be amazed See these are things that must be told unfortunately our media doesn't focus on positive things in a big way You know daily politics they handle. I was driving from Hydra Vijay Vada to Hyderabad. This is the only place in the country I have seen as I drove. I saw 12 cold storages run by private agencies Along the highway. I have not seen this anywhere in this country if you go in United States and California You will see cold storages everywhere You don't see this in this country 60% of the fruit grown in this country goes rotten At 40 degrees 45 degrees. He's selling the fruit on the street. What is the life of that fruit? By the time you buy it half of it is gone 50 60% of the nutrients are gone.  So Infrastructure other things have to be privatized. The government cannot build these things. This is the only Road on the end in the entire country. I have Extensively I've driven in this country. This is the only place where I saw 12 cold storages Private agencies running it. This is what the nation needs So one other question before we wrap up You mentioned about swimming in river bathing in the river In my childhood, I also Did the same So for you and me to understand what reverse meant to us To everyday life even in the city. I was I was in a town not even in a village There was there was this empathy. There was this relationship with the river Most of our children have grown up not even seeing a river yesterday in Navi, Mumbai They welcome me and they took me into that thing. It is an unexpected thing I didn't know they were the welcome, but it became so big and the administration there Whatever you call that southern which is there.  Oh my god. She went on and on Full blast. I'm telling you very few politicians can speak like that She is covering such wide variety of subjects And she tells me this sadguru is telling me he floated down the river 165 hundred and 163 kilometers 13 days. I don't believe all this. I have to check because I have never seen a river which This is I know that girls wisdom This is intelligence that you spoke of intelligence questioning as opposed to descent. Yes Somewhere I heard about She's saying I have no I've not seen a river that I can float down on how can I believe you I see only sand How do I believe you that all the way there was water? So this is my commitment 50 years ago how I saw the rivers in the next 25 years we have to get it there Please everybody get together whoever you are. So you're hopeful that you will be able to sell this Not sell this was a sentence message approach make that this generation also aware of what they are facing. Yes, there's a phenomenal response across the country And Irrespective of who they are from school children to celebrities from simple farmers to tall leaders just about everybody Is supporting it in a huge way But I want to make sure a large percentage at least 60% are voting when it comes to the actual voting But I want more than that because you don't have to go to your booth It's just just your phone and after all you love your phone more than anybody on the planet So please use the phone and make sure you express your responsibility for this country and the future generations This much everybody must do. Thank you. Thank you so much