[Music] welcome to week 3 module 11 of database management systems course in iit address online bsc program in the last week over all the five modules we have done a detailed introduction to the fundamentals of relational database models attributes types the basic mathematical structure we will have to do deeper later on then talking about schema instance different types of keys and the basic operations like select project join this kind of basic operations of relational algebra and introduced the basic structure of queries both in terms of the data definition language as well as for the data manipulation language and using several features of the sql language select from where and then different extensional features on that we have shown how to form different types of queries relatively simple ones i would say and we have also shown the use of different set operations use of null values aggregation functions and so on so it has been a quite an active week last week talking about relational database model and sql so we start this week today by doing a quick recap of what we have done this recap is not in terms of restating what we have learnt last week but strikes trying to solve an example each for most of the major features that we have discussed because it is very important for you now to actually start practice practicing coding in sql because it is as you have seen it is a kind of a programming language but it is a different style of programming language very different from all that you have done so far which has significantly been procedural in nature but this is the first declarative language you are doing and based on a very different kind of model so unless you practice a lot you will not be able to internalize the mechanisms to extract queries making changes to the database structure or to the manipulation of tuples and so on and going forward it will become very difficult so we would be doing a quick round up of the of some key examples here we will continue to use the same database schema of the university database that we had used last week so you could anytime refer to that so the first is the basic select that is you are given a relation and you have to find out information from that so from the classroom relation we want to find the names of buildings in which individual classroom has to be taken out which has a capacity less than 100 so because it is classroom relation you immediately know the from part of the query so for from clause will be from classroom that says that we are talking about will be dealing with the classroom relation then we are saying that individual classroom capacity must be less than hundred so in the relation there are three attributes room number capacity and building obviously it goes without saying though we have not looked at the schema we can very easily understand by common sense that room number is the key here because certainly there will be multiple classrooms in a building and two or more classrooms can always have the same capacity so it is a capacity which is bounded by hundred so in the very close condition which is the predicate we set capacity is less than hundred so that tells us that we will get classrooms only where the capacity is less than hundred and then finally we put the select laws which says that what do you want you want names of the buildings right so we want the name of the building so it says select building and as you have learnt that select from where this by default gives you a multiset so duplicates are not removed so here we specifically want remove duplicates so we are you specifying the distinct qualifier so select distinct building i will remove these markings now so select distinct building from classroom where capacity is less than 100 so it goes to the table looks at capacity less than 100 this will not qualify this will qualify this will qualify this will qualify this will qualify right so this condition is satisfied so in terms of ah distinct bit in terms of buildings this is what we will get painter taylor watson watson naturally we want to remove duplicate you are using distinct so one of the watsons have to go and this is what is your output so output is a table having only one column that is corresponding to building and it has three rows right so this is this is the commonest simplest kind of select from where that you can have and this by itself covers a huge part of the types of queries that you write naturally if we look at if we want that we would not remove duplicate this query is same as before except that i am sorry except that it is saying without removing duplicate so when you do not want to remove duplicates naturally you do not say distinct you can say all so which will mean that again these four are selected by the where clause this is my relation taking a projection on building because i want select building and the watts and repeated will not be removed in this case so you have two instances of the watson now it may be noted that again i am saying it repeatedly so that it gets into your mind that select from where gives you a multi set so even if you do not say even if you do not say all by default it will give you this result so giving all is just kind of explicitly saying ok give me everything but it is optional if you do not give anything then it will give you the multiset it will give you the repetitions it will not remove the repetition for repetitions to be removed you need to put the distinct right so this was the simplest of queries that you could have and you can keep tracking on the left as to where we are progressing next what we should say is a cartesian product cartesian product is a so select from here actually covers both your projection as well as selection these two ah operators of the relational algebra now cartesian product is where you have two or more relations so let us look at we are having two relations student and department and we want to take a cartesian product that is make all combination of tuples between student and department so if we just if we did not write this where part if we just did select name budget from student department it will give you all combinations of students and department but we have qualified that by saying that the student dot department is equal to department dot department now the student or department name is equal to department or department name so what it will retain only those tuples only those rows where the department name under the from the student relation and the department name from the department relation is same designating that this student actually studies in this department right so that is that so it is a cartesian product with a qualifier which will soon see is what will turn out to be i mean we can do this very directly by using something called join which we will introduce in the later module and further we are saying that we have say that list of students of all departments so this is what comes here and we want a budget which is less than 0.1 million which is hundred thousand dollar so the budget is lesser so it will have to match and the budget will have to be less so if you check back on the two relations ah that we have already given you in the example then you will see that these are the tuples which will survive through it where we finally show the name of the student we are not showing by projection we are not showing the name of the department we are showing the name of the student as required and the budget as required where the budget has to be more than ah your ten thousand or whatever less than less than ten thousand dollars in every case you can check that is the case right so this is ah this is the little bit extended query where instead of using one relation now we are using two relations now let us go further the another way to write this relation is using the renaming feature that is the as we can any ah attribute or any relation i can rename with it to a different name so first if you look at the relations the student i have renamed as s department as i have renamed as d and writing this as is optional also you can just write student s comma department d it will mean the same thing but writing as i i prefer to be not smart but explicit that way chances of error gets less so now i write s dot depth name equal to d dot dependent we do not need to write the student or department that makes it a little bit less of typing and also on the select clause we are saying that s dot name which is actually student dot name because of this alias is given a new this attribute is given a new name student name budget is given a new name depth budget you can see that i could have written this as d dot budget also right because d is a department and department has the budget but i am not writing that that is i mean i can write there is nothing wrong but i do not need to write it because the budget attribute occurs only in the department it does not occur in the student so i do not need to distinguish whereas the department name occurs in both so i need to distinguish which department name it is the department name of the student or is it the department name of the department table that i am talking of right so these are these are the variabilities so instead of budget here i could have written d dot budget as well okay so this is just another way of writing that query when it gets more and more complex you will probably or when you do want to do a self cross product cross product between a relation and itself you will certainly need to use rename as you have seen already in the earlier discussion ah certainly in the where clause you have a predicate so the predicate can use any operator of the boolean algebra and or not so we are trying to see that we want to find names of all instructors so department is finance or whose department is in watson or taylor right so name we want names of all instructors so instructor table will have to be there instructor table will be required we want that the name or the department's building should be either watson or taylor and that will satisfy so the department table is also required so this is how you decide that in the from on the from clause you have two relations instructor and department now what is the condition that you need you need to know the department of a faculty right department of an instructor because whose department whose department so you need to make sure that the department of the faculty of the instructor and the department name in the department has to be equal exactly the similar thing we are doing for students there so first part of the wire clause has d dot dev name is equal to i dot dab num after renaming then we say that what are the condition on which we choose this is this is the base condition this will make sure that the instructor and department are properly correlated that is in other words the join is happening and then what you have you say that either the department of the instructor is finance so i dot def name is equal to finance or or building should be watson or taylor so we are using the in clause here by doing in watson or taylor so it means that if the value of building is watson or the value of building is taylor this will be true this predicate will be true if it is something other than that this predicate would be false mine dude this using this in ah we are making it simpler because otherwise i would have to again do or building equal to watson or building equal to taylor lot more of you know verbosity as well as chances of error so what if you if you just carefully look at how it is composed you can see that this is this join condition has to happen irrespective of anything else that is the validity of the query and rest of it is the total condition so you have a and and look at this parenthesis which make sure that this joining condition will have to be true as well as this condition has to be true for the table to be selected right so that is how you have and here but within the within the selection criteria of the table you have either the instructor is from finance or his or her building is what's on a tailor so you have a or in between them right if you do not put this parenthesis it will be it will be wrong because this will become and or and something you will have to then figure out which order they should be evaluated and so on so when you have multiple such connectives and or or qualifiers as not you should be it is better to put the correct set of parentheses according to the semantics so that is how this can be done ah other we showed is the use of string which can be used to match a variety of types of values here we have the table of courses and what we are trying to show is how to pick up the find the titles of all courses whose course id has three alphabets indicating the departments you can see some have three some have two right and then there is a hyphen and then the number so how do i say that there is to be three alphabet so i want three characters to come before the hyphen so i put three dashes under scores and then the hyphen and then anything else is allowed so i put a while string percentage which will match everything so this will get matched this will get match this will get matched but this will not get much because cs has two characters followed by a hyphen right so in this way if you check these all these a whole of c s e e m u this will get excluded and whatever remains then i am doing a projection on title so i will get the titles of those courses right so this is how and variety of ways you can use strings and it is very powerful in this way ah i think this was easy that how do you order your output all that i am trying to show here order by basically will say whatever attribute you have given the result tuples will be ordered by that by default it is ascending but if you want you can put asc it is optional but if you want descending you will have to put assignment what i am showing here is ordering by two tuples so what did i do is department name asc that is it is to be within alphabetical order of departments and within each department decreasing order of total credits so then total credit decreasing so if you see in computer science first of all you have biology computer science this is the lexicographical order and in computer science you have a decreasing order of total gradients to be compiled here together right so it is within computer science this is if i if i change this order if i put this first and this next then it will first get sorted by this total credit and within equal value of total credit it will get sorted by the name of the department so this the the in order by it is very important to know to decide which is the ordering that you actually want of the attributes in is we have already discussed so the purpose of in is i think clear so i will skip this you just ah check it out at home in set we have three operations union intersection and difference which is accept here so we say for the ah what we are trying to find out is uh we want to find the solution using union so we say this is the courses that are offered in 2018. so semester is fall this is courses offered in spring and then you take them together then you get the total set of courses and please recollect that union is a pure set operation is not a multiset operation unlike select so ah it will remove all duplicate if you do not if you want the duplicates to be retained that is some courses which are offered in both should come twice then you will have to write union all right similarly you have intersect here where you say the names of all instructors who taught either in computer science department or in finance department okay and whose salary is this so you have first this either or you put it in terms of in so that any of the values will be fine get the instructors get all instructors whose salary is less than 80 000 so it's kind of an condition that is happening here so you subtract you uh do the do the interest not subtract you do the intersection that is those who are common that is who are in computer science or finance and has a salary less than 80 000 will come in the intersection and that is the result that you will see if you check with the table i mean ah naturally urge you to check with all the results that we have uh given here ah similarly you are saying find names of all instructors who taught either in computer science or in finance this part remains same and your salary is either less than 90 000 greater than 90 000 or less than 70 000 right so what we are doing here is we are showing this by accept so we have accept so we get all the faculty who qualify for the list and then all the faculty who satisfy the salary criteria we just take them out right you can you could have obviously done it using a more complex where clause also but possibly it is easier to understand it through the sec theoretic operations if there is such a clear notion of two sets or more sets getting formed ah finally aggregation is is ah is easy so all that you have to remember that for aggregation we will need to group by on on the entity on whose property we will aggregate here that entity is building from the classroom find the names and average capacity of each building average capacity of each building this is what i want so classrooms comes clearly you want to find the name and average so this comes clearly since i want it for each building you will group by building so together take on the building and take the capacity and we are again done some qualifier is just not grouping but we will consider only those capacity only those whose average capacity is greater than 25 right so you take the buildings compute the average if that average is greater than 25 then only this becomes true and then it comes in the output otherwise it does not come in the output right so this is how in in this case as it as it turns out you have you will not have painter in the output right clearly because painted is just one instance so its average is that instance itself which is 10 which is not greater than 25 so painted is not there in the output similarly you can do minimum which is very very similar doing the minimum finding the minimum salary you can do maximum finding the maximum credit just work this out with the given table instance and convince yourself that they work properly and finally you have count which counts the number of instances number of rows basically so what you are saying that from the section relation so this is the section relation given in the figure find the number of courses run in each building right it is run in each building from the section relation so from section run in each building each building means i have to group by the building and then what do i want to find out i want to find out how many courses are run right so i want to count i'll count by course id naturally you know the building is grouped so it is grouped according to building like say say taylor here taylor here so these are grouped together and then i will count the number of courses that will be there so there are as you can see there are five such courses will come so the account id is five okay so i am counting based on and then you can see that there are duplications of course ids and they will be counted as distinct right because naturally i am just counting the available number of rows in this sum is what you use to find out the total so for example here we are finding out the total credit offered by each state each department so it is from the course cable so from course offered by each department each department so group by is department and what we are finding the sum of is total credit so we sum on the credit to get that so if we have say if i consider say music i'll get three only one entry if i consider biology there are three entries four plus four plus three so this will be eleven biology biology is eleven right now this are occurring in an ah half hazard order if i want i can again put an order by on this on the department name and get it in the order of biology computer science like that those are all different choices okay so this was just a quick round up of the most common and most widely used this is kind of the foundational part of the sql language and its use and i would again again reiterate and argue that you practice a lot of examples there will be tutorials on this there will be practice problems also given to you but get confident on this and as we move forward through this week you will get to see lot more of sql features which are more advanced and by which you can do more interesting things so thank you very much for your attention and let us meet and discuss more of sql in the next module