 Assalamu alaikum dear students today we are in the lecture number 29 of the database management system course the course code is CS403. At the end of previous lecture we were discussing the where clause of this select statement. As I have told you before that select statement is one of the most used statement in the SQL because the objective of the select statement is to access data from the database. On the other hand select is used to perform different types of operations that we studied in the relational algebra for example join and also Cartesian product and some other forms of joins as well. So, that is why select is a very lengthy and very useful statement and to have a better understanding of the statement we are studying it in different parts and right now we are going to discuss the where clause of the select statement. As I told in the previous lecture the objective of the where clause is to select certain rows because we have discussed it that in the relational algebra we have got two operations one is the projection and other is selection. Both of these operations are performed by the select statement in the SQL. So, when you want to select certain columns then you use the select and at the same time to select certain rows again we use the select statement. The difference is when we need to select when we need to project certain columns we use the columns names in the select statement but when we want to select certain rows using the select statement we use the where clause of the select statement. So far we have used the select statement only for the projection purpose that is to select certain columns. Now we are going to study how do we select different rows from the database by using the select statement. Dear students, I have already told you that the purpose of the where clause is to select certain rows and its objective is to provide a condition that is applied on every row of the table and the rows that fulfill that satisfy the condition they are selected and they are displayed as a result of the select statement. So, let us start today's lecture by studying the format or the general structure of the where clause and by knowing the format of the where clause you will know different variants the way it can be used. This is the select statement that we have studied so far and now where and search condition. See again the where itself it has been given in the square brackets that means this is an optional clause but since the where has been written in capital it means if you are using it you have to give it as such and the search condition this thing is written in lower case. It means that you have to produce it and secondly you have to see that it is written in the angle brackets. It means that its explanation and its further details that follows. You see that what does this search condition mean that we have to give it after the where clause. You can see that there are many different possibilities that we can give in the search condition. This is not very difficult if you just concentrate on that and you concentrate on them one by one you will find that they are not difficult to understand one thing and secondly if you practice them it becomes even more easier. So, let us start with the explanation of this search condition. First thing is that you see the curly braces it means k you have to pick one of the options given in this curly braces and then next is not. What is in the square bracket it means this is optional that is predicate and predicate is given in the angle bracket it means k it needs further explanation. So one option is you give not and predicate then the second option is you again give another search condition this is the second option and then you see then again there is an optional thing and or and again you can combine not predicate and the same thing. It means that k you give not predicate then and or which is optional and or then you can give the same thing in not predicate search condition. You see in the beginning it is not predicate or search condition and after that it is an optional part that you can give it and or and then it is not predicate or search condition. Now in this you see that after this after the curly braces which you see in the square brackets comma dot dot n you can repeat this thing as many times as is required it means that your one search condition is after that you give and or and or and or search condition and then after that you give and or and or search condition and like that this way your whole zone will become a search condition so whenever you place this form of search condition after the wear clause so all that search condition will be evaluated as per the rule that we are going to discuss right now and you will get a single value and that will be true or false so if the condition is true that particular row will be selected and if it is false the row will not be selected. So see how predicate is explained predicate is first thing is expression and expression say it could be a single attribute name like we have so far we have studied different tables like for example student the student may say agar we simply give one attribute like s t id or s t name or s t address it can be considered as an expression. Yes, it's a lot of work that you have to do that you have to give an attribute name or you apply some operator on that you know let's say you can say c g p a star five c g p a multiplied by point zero five so this means that what is written here is expression expression could be simply the name of an attribute or it can be an expression expression means that combination of attribute any operation and again another attribute or some value. This example you will see in the future and you will understand it but the expression is something that you read in other languages for example when you read c in c you will see the expression. This is something that I think you are familiar with already expression about different operators comparison operators are equal to or not equal to or not equal to less than greater than these are different comparison operators. So you give one expression then a comparison operator and then the second expression example you can say where age is less than 20 now age is the name of the attribute less than is the operator and 20 is the value or what we do value that is also an example of the expression. Dear students like operator is used basically to compare those strings when you are not sure about the exact values. So if you want the exact comparison exact similarity exact same values exactly the same values in that case you will use the equal operator but if you are not sure what exactly I am looking for I am searching for a different thing that you want that all these things for example you say that Ahmad in the names of the students are Ahmad whether he is Bilal Ahmad or he is Sagheer Ahmad all these names I need their data or I need to find them. So the students may be living in different cities from all over the Pakistan or even from the other parts of the world as well. So you want to search them as only you want to perform certain operation on the people on the students living in Lahore and the thing is if you say that address is equal to Lahore and the thing is that your address is not Lahore so if you apply the equal to operator then you will not get the desired value because Lahore is not the same. Lahore is written in the address for example X, Y, Z and Dimal Lahore. Now in some addresses Lahore can be in the first position or somewhere in the middle or somewhere in the end. So in such situations like operator is used that you said that this is a string and this is somewhere in the beginning, in the middle, in the end. So for this you use like operator and remember about not that not is basically it is a unary operator. This means that after this you have to give an argument or a parameter. So whatever is the value of the expression after the not, not will simply flip it. This is written as not and an expression. Now if expression evaluates to true then not will do false. So overall the result will be false and likewise if you say not an expression or not an expression evaluates to false then not will simply do true. So not operator basically means that the expression after that if you want to upload it. Now once again what I have said many times I will tell you that same thing can be performed by multiple ways. And everyone will give you the right result. Now it is up to you. This means that what things you find easy or what things are good or if you know about its execution cost then you will also see that which things are more efficiently executed or the result will be given. So on this basis you will select that the different options that we have then how to perform any special activity. Once again the user will never tell you that you have to use this operator, where cross has to be used, select has to be used. This is only and only your job as a designer, as a developer. You said that this user as an example as you are making a database of your educational institute. So it tells you that your library says that okay I need author-wise all the books. That one author's name is all the books, then the other author's name is all the books like this. It also says that I need subject-wise books. That one subject, let's say we call it a database, all the books in the library have their list. The other one is data structures. So all the data structures have their list. This is how it came from, let's say C. So the user told you in plain language or explained to you. Now this is your job that you will define what particular form of statement of obviously select or SQL statement will be used here. So you need to practice a lot of this so that you can transform the requirement of the user into your required query. In this course, you will get assignments and exercises. You will have a lot of practice queries. And apart from this, you will get different practice queries from different practice queries. You practice as much as you can and you will understand this better. Let's move on. After that, we have one more option where we can give another search condition is that expression again not is optional between expression and expression. When your between job operator is used in that case when you have got a range of values and you want that if there is a value, then it will be selected. For example, you say that cgpa between 2.5 and 3. You say age between 20 and 25. So all the rows in which age attribute value is between 20 and 25 will be fulfilling that condition. Similarly, we have expression. Don't get confused with expression. For simplicity, you can understand that expression means attribute name. When you practice, you will understand that expression has different forms. For now, you can understand it as simple attribute name. So expression is not null. When you want to check null, then you won't say that select star from student where address is equal to operator. When we have started, we don't have to use equal to operator. Instead, we say null is used. Let's say address is null. We will practice further. Similarly, you have another expression not in sub-curie. Now, the expression. Between is used when you have got a certain range. From here to here, all the values. In that case, you do not have got the range. You have got the list of values. For example, you say where age in 5, 10, 13. See, between and 13. It means from 5 to 13, all the values will be considered. If you say in 5, 10, 13, it means exactly 5, 10, 13. Similarly, we have an expression. After that, all the comparison operators and sub-curies. Sub-curies is an important feature that we will read in the next section. Similarly, exist sub-curies. Again, we will read it. This was a general discussion. There was a general format of where clause. What are the forms of the search condition after where? After this, we will practice different forms. You will see how it is used. For example, here it says display all courses of the MCS program. See, this is our requirement. This is the simple requirement given by an end user. He said that I need all the courses of MCS. Now, you see, there is no select mentioned here. That you said to the user that select it every day. No name of the table mentioned. And it is not mentioned that you have to use the where clause here. Nothing is there. But as a developer, as a designer, now this is your job. How to transform this requirement into an SQL statement? The first thing is that you need to know about courses. You know that we have stored the data about courses in the course table. You know that. After that, you see that if the user had simply said that I need all the courses of the data. It means that you had to realize that there is no condition here. In fact, you have to show all the courses. So, in that case, you would have given the select statement from the course table without any particular condition. But at this time, if you see the requirement of the user, it is that we need the MCS courses. Now, here, the thing to think about is how will we identify the courses of the MCS program from the course table? You know that we have stored the data about the courses. Along with that, we have stored this thing about every course that what is the program name of this course? This means that the course that is related to the MCS program has the program name attribute in the course table and it will be written as MCS. And in the same way, if there is a course called BSS, then it will be written as BSS. If there is another course called BIT, then it will be written as BIT. So, you know that, in fact, you knew that we will know about the program. What is the program related to this course? We will know this from our program name attribute. Now, how will we put the condition on a course? This means that the condition will be on the program name attribute of which course table? The second thing is that what will be the condition? What will be the condition? Because the program name stores the name of the program. So, you have to check that where you have the value of the program name attribute, MCS. So, this means that the condition will be on the program name attribute. The value check will be MCS. Now, what will be the operator? What will be the operator between them? Which will be checked and then will be compared. Obviously, because we have to find MCS. We need the value of MCS. So, obviously, what your operator will use here will be equal to. This means that your search condition will be that the expression that we were reading normally comes on the left side. The expression will come in the name of the program name attribute. The operator will come in equal to. And the expression on the right side which is basically a value will be your MCS. What does this mean? This means that now this condition you have selected. By the way, you gave the list of attributes you gave the name of the table from. Where? Condition. You have put where. After that, you said that the program name is equal to let's say MCS. Now, what will happen? Now, every row, your course table is equal to 50 rows in the first row. Every row will be accessible. And from that row, the value of your program name attribute will be accessible. And that value will be placed in this condition. Now, when you wrote your SQL query in the program name, the value will be placed in that place. For example, in the first row, it was called BCS. Now, what will happen? You will have BCS on the left. Equal MCS. Now, it is obvious that these two strings are not equal to each other. So, when this condition is it will be false. When this condition is false, it will happen that this row which has been created against this condition this row will not be selected as a result of this select statement. It is not being qualified. Now, let's say that this particular course will not have this display. Now, this sequence will be applied in the second row. From the second row, the value of your program name attribute will be picked. Let's say that there is MCS. Now, your condition will be MCS equal MCS. Now, both the strings are equal to each other. Both the values are equal to each other. So, your equal operation the result of this will be true. Now, when true comes then this particular row it will qualify as an output of this select statement. Let's say that this particular row in which you have the search conditions are true it will be selected. And this process carries on. Till the end of the table. It will be applied on all rows. So, every row will be individually checked and on every row this condition will be true that row will be selected. This means that at the end of this statement or the execution of this statement as a result that row will be displayed and selected that this condition was true. Let's see how this condition is written. It is the same that select course code, course name and program name from course. This is what we have already studied. And what is happening in this or in this situation or in this situation there is no condition at all. It means that this particular form of the select statement it is displaying all the rows of this table. But, if we have said where and look where I have written where I have written where I have written where I have written where the case does not matter then we have said where program name equals MCS. Now this is your condition. Then I have said that as this is the condition that will be applied on all the rows and the rows that are fulfilling this condition will be selected or displayed. The example data we have according to this this is the output of this statement. You see that in this because you have given the name of three attributes in your select statement then in the output you are saying just three attributes course code, course name and program name. And then in this as many courses are coming from course code and course name the program name will be written in all of them MCS. Now as an output that does not qualify the given search condition all of them they satisfy they qualify the given search condition so this is the role of where clause through which you select some specific rows and display them or use them in some other operation. We will see more examples of this. Now I am giving you as a practice that you practice like this. Now I have shown you all the courses of our MCS of the output. But now you do that you display it like this that the output is written like this that the first row is written that analysis of algorithm whose code is CS504 is offered in MCS. In this way in a descriptive style all of you the first course which we have shown you try to transform that course in this kind of output. After a little effort it will work. As I explained to you before that not simply reverses the output of an expression you give an expression like you have given a program name is equal to MCS. Now as I have explained to you that on every row this condition will be applied and against every row its value will be true or false. Not simply reverses it. For which row for this particular record the value of this expression will be true and in which it will be false Now what is its utility what can be used for example if you look at this last query what was in it we said give us the courses of the MCS program if we have this requirement that we give other than MCS courses of all programs then what we will do is program name is equal to MCS and before this condition you will simply apply not. Now what will happen that the rows for which the search condition evaluates to false Now you have given the condition that program name is equal to MCS course of BCS so your condition is false if not true then the overall result of the search condition is true when it is true then this record will be displayed Similarly there is another record there was a course of it your condition will be BIT is equal to MCS obviously it is not equal if it is false then what will you do then this course will be displayed Similarly you see we have a course of MCS Now the condition becomes MCS equals MCS True but what will you do then it will be false then this row will not be displayed So overall we got the impact that all the courses which were other than MCS were selected or displayed so you could do this in any other way but this is also one approach of doing this let's see inverses of predicates value for example this is list of course name offered to programs other than MCS and this is the same query and here it is where not MCS and this is the same and if you look at the output in our example data that we have got in our example database you can see that 6 courses are displayed and your program name there is no record in which the program name attribute is written as MCS so that is the utility of the not operator and there are many ways to use it which we will see in the future and the same thing as we just did we said not and gave an expression we can do the same thing as we can say where the condition of your program name is not equal to we specifically used not equal to operator where operator was equal and that condition was given as an example and here it is that straight away you are using the not equal to operator that means here we have a course of let's say BCS so what will be the place of program name and the place of it will be BCS so what will be the condition BCS not equal to MCS obviously this is true because BCS is not equal to MCS when it is true then this will be displayed but when arrow comes with then it will come MCS not equal to MCS obviously MCS is not equal to MCS so this expression it will evaluate to false when it will evaluate false then this display will not be there so I hope you have understood that as we used not but using the equal operator how will this work and here we are using the straight away not equal to operator so you will get the same result which you can use the between operator the objective of this is when you have got a certain range of values you don't have to do precise values as we were saying here that it is MCS so if it is not normally we use this in the numeric or date for its numeric values and you say that if it falls in this range then it will be selected and if we give individual values we can use the between operator check the values in the range for example list the IDs of the students with course scores having marks between 70 and 80 i.e. students ID and course score display whose marks are between 70 and 80 how will we write this query if you see what we want what are the things we want we have course score and student ID you have seen this straight away now you will see and apart from this the condition when we said that the marks are between 70 and 80 so you will see what condition this is on your total marks which is on the national and final marks so first you will think what is this information that we have all this data so till now in the queries our examples are like this all the data is in one table but this is not necessary this is not required all the data is in one table we are going towards the flex so now we have examples in which all the data is in one table later we will do such queries where the data can come from 2 to 3 tables now you have seen we want course score student ID and total marks so you will immediately think what is this data in enrol this is the enrol table where you have the performance of each course so you will get all these things so what you have student ID and course score you have to display so it will come in your select list you will say select course score and student ID till now you have to display the student which marks are between 70 and 80 and your condition that is on your total marks okay now if your total marks are 70 71, 72, 80 then any of the students will have the same value but any of the students who have the same value you want to select so that means you will select between 70 and 80 so the procedure which is working exactly the same that is you have set the condition on your total marks so in every row your total marks will pick the value and it will be checked the end 80 if yes condition evaluates to true and that row is selected next row next row and like that so all the rows in which the total marks value is between 70 and 80 will be selected and in this case display let us see how we have written this query simple is total marks see that the total marks are not required the query is simply to give us course code but we add something so that we can ensure the query that we have executed it is working properly it is giving the correct result so from and roll and here total marks are between 70 and 80 that is the total marks will check the value in every row and in this row the value is between 70 and 80 whether it is 70 1, 2, 3, 4, 71, 72 or 79, 80 all the rows will be selected and it will be displayed if we look at the output this is the output of this statement we have three rows because we have three of the students whose marks are between 70 and 80 first student IDS1015 and course code CS616 total marks are 80 similarly the second is 75 and the third is 76 all the records we have all the values are between 70 and 80 how the between operator works the in operator now see what was between you have got a range continuously your values are running like 70, 71, 72, 73 and up to 80 but if you do not have a continuous list but rather you have got the certain values in which this is this is so you will say I have to select those rows so for that you use the in operator check in a list of values now give the course name of MCS and BCS programs now see that you have told certain values now the same thing that you want course names in the course table and you want MCS and BCS so in every course table the name of the program which has that particular course that means we will display the course name from the course table and we will apply the condition on the program name so how we will apply the condition again I had told you that the user will not tell you you have to define this in the course table so you have to select course name program name to make it sure that we are receiving the correct result if you omit it then it will be correct from course where program name in MCS, BCS now see one thing you should note that you have written the in operator in your parenthesis you have written MCS and BCS in the course why? because this is a string type or text type value so you know that if such a value is there then that we give in the course if this is the number or date we will give the special number without the course date treatment it varies now see that now we have in it we have got the 8 rows and if you see in the program name attribute in the program name attribute you can see either BCS or MCS apart from this you cannot see any other value or you can say that we have accessed all the courses of BCS and MCS and then the same query you can write this query select course name, program name, from course where program name equals MCS or program name is equal to BCS now in this you have washed the condition see one thing you should note that after where your condition will come that condition will finally be evaluated in true or false in the end you have to give one value now in it as you can see here you have search conditions program name is equal to MCS program name is equal to BCS now if you see both of these conditions then each one of them will have an answer obviously program name is equal to MCS it will be true or false if you see program name is equal to BCS it will be true or false and two true or false we cannot have from search condition ultimately we need to have just one true or false in that what we have more operator here you must have used it in your programming courses that is the role you are playing that you gave one condition program name is equal to MCS and now both of them are getting results your first condition is true or false you have one value for or operator second program name is equal to BCS true or false is evaluated for or now as far as or is concerned now it will be on one side it will be true or false now you know the behavior of or if one of them is true then it will be true now if you see it with the reference of this statement program name is MCS the first part will be true and the second part will be false if true or false is true then it will be true then MCS will be selected similarly the day when your program name is BCS then for every row your program name is equal to MCS will be false but program name is equal to BCS so if it is false or true then it will be true similarly your condition will give you every day in which program name is either MCS or BCS and first if we have the course of MBA then program name is equal to MCS false program name is equal to BCS false then it will not be true through this condition you can structure all the courses in which your program name is MCS or BCS like operator till now you have used equal to operator when you said program is equal to MCS or program is equal to BCS here you have used the equal to operator the equal to operator which is your exact match that the value here is equal to here exactly match like MCS is equal to MCS if MCS is on one side MCS is on the other side then you know that it will not be equal to each other because it is equal to the exact match now the situations where you want those rows which are approximately or they match with each other then you use the like operator for example the people of Lahore now the people of Lahore will be written in the address in the beginning, in the middle, in the end then it will be there in the name of those Ahmad is coming so in this Ahmad will be Hussain Ahmad will be Hassan Ahmad will be Mujtaba Ahmad will be like Ahmad Ahmad will be somewhere then you use the like operator in such situations that when you have got a general condition that such a thing is in a string then give me that daily with like another concept is the wild cards you must be familiar with wild cards you keep using them so from wild cards there is a character who generalizes it here you will have two wild cards one is underscore and one is percent symbol two underscore and percent symbol underscore you use it that as much as you gave underscore exactly the same characters if you say one underscore underscore is Ahmad that means in the second position in the first position but in the second position Ahmad is this is the purpose of underscore and as you say percent but the objective is as much as you want as much as you want if you say percent Ahmad that means whether Ahmad is in the beginning whether it is after one character after 2, after 3, after 4, after 10 after 20 wherever you want then you say percent Ahmad but you say underscore you say underscore, underscore, underscore and then Ahmad that means three characters and then Ahmad that means in the fourth position you say that percent Lahore that means in the place where anything is Lahore in the end and mind it if Lahore is in between you say percent Lahore that means in the beginning but in the end Lahore percent Lahore percent that means Lahore is anywhere in the beginning in between like example for example display the names and credit of the CS programs now in this it is simple to know if we already know that there are two programs of CS BCS and MCS then we can do this as we said before if program name in program name is equal to MCS or program name is equal to BCS but the thing is if we are not sure how many programs can be so for that we can condition that set course name course credits program name from course where program name like percent CS that means that we have percent CS before no matter what one, two, three, four, five at any position if program name is CS then that will be selected and see what is the difference between percent CS and underscore CS that is the difference if we say underscore CS that means if CS starts exactly from the second position like in MCS now M is the first character and CS is from the second position first if we say underscore CS and in any program name we have MACS now the thing is in MACS that starts from the fourth position so underscore the single underscore will not cover that but the percent will cover that that means when you want to be precise exact about the location of your given string then you use underscore and if you want to have the flexibility in that case you use percent and see how like will be placed attribute name like percent CS that means whether it is MCS, BCS, MACS CSA, CSB, CS3 any such program name in which there is CS in the end mind it if we have MCSA now MCSA MB is CS but since CS is not in the end then it will not cover that so see from this where you know tentatively the string is given then you use like and the role of percent and underscore you must have understood see the scope which we have already seen that all the programs that you have and why are you selected here because CS is there and the final part of the program name that is why all these days you have selected and you have also seen that the query that we need all the programs of CS you have read it in three ways through these and by combining and the third condition you have got this like whatever suits you whatever fits you in this given situation order by clause is used to sort the output in a certain order or on the value on the basis of the value of an attribute rather on the basis of values of one or more attributes whatever you want you especially need to use order by clause and it will be that your DBMS will put your output on the value of that attribute then your data table will have no difference this is only for the display for the output let us see sort the rows in a particular order fine this is the syntax rather format select you have given from table name where search condition order by rational but if you give order space by you will give them as such order expression and now I am telling you for the sake of simplicity you should understand from that expression that it is your attribute name after that there are two options ascending or descending you will give one of them and after that you are seeing if you want to repeat this and all this thing is optional Dear students for example of order by we will discuss that in the next lecture the objective of order by you understood that you have an output but that output is for ease of user for understanding you want to see it in ascending or descending order and one more thing is that it will be in your mind that as many times as you want to consider order by the data in your table there will be no difference it will remain the same which will be in your output the examples of order by and the rest of the SQL which we will discuss in the next lecture I would like to say goodbye Thank you