let us see the another kind of comments like sub language that is dql dql means database query language which is meant for the purpose of to retrieve the data to retrieve the data right so in order to return the data from the tables we are going to use a command called select come right so select is the command which is meant for the purpose of retrieving data so if you look into the syntax of Select command select list of attributes what you want to get at the output side and those list of attributes should be from which tables so here tables can be one table or it can be from multiple tables and if you want to filter this data you can able to use a condition called where plus where followed by a condition right this is a simple syntax which we can able to use in order to work with the select command see for example uh I want to print like for example if you take any student table in a student table assume that the student table having Sid s name and their ages there is I want to produce all the students all the students who are from the student table then what you can write the query select what the output you need to get you need to get all the tuples all the tuples means what all the tuples combination contains Sid s name essay so I will write the list of attributes what you are trying to produce out output Sid comma s name comma sh from which table you are need to get from student so you are going to get all the data as output I'll return right this is the meaning right sometimes it is not only meant for getting the data from one table you can able to get it from one R more tables also that will see in further Classics okay similarly here I have written only one table but here tables can be again one not more tables can you able to write like 20 column names it is difficult we can write but the thing is we can have an alternative procedure that is star I can write in place of all the columns as star which means all column names select star from student meaning that I am going to get all the columns from the respective tables today so these two are equivalent right so have you heard the terminology of Select anywhere right so if You observe the relational algebra select Command right which is like Sigma right this one is same as this select command do you think that these both are same right so these both are not same both are not safe right see try to observe so as part of this particular example these two are same right see I am going to write select is one I am going to produce all the tuples of this one relation that's it as I am not going to mention any kind of condition this is okay fine but if I am going to mention certain kind of condition at the time also this is equivalent to select command only but the situation is for example I don't want to get all the tuples all the columns of this particular relation for example let me try to write select Sid from student table now is it equal into select student command no here in this particular situation it is going to produce all the student details along with all columns whereas coming to here it is going to produce only respective column that's the reason select command in SQL is not equal into select command in relation algebra but can I say that is it equivalent to production yes it is equal into production select command in SQL is equal into production because if you would like to produce all the columns what you can write select Sid s name sh is equal to this one if you want to get this query we can simply write select essay so I can say that these two are equal okay fine see guys here so now you can observe where class where class right so this where class is meant for to filter the data if you would like to filter out the data in the form of horizontal rows I can go for where plus something like I would like to write select star from student where scig is greater than 15. what are the meaning of this one I am going to so from here to here I am going to get all the data from the data I am going to filter out once again host scig greater than 50. so this is what I can call as horizontal filtration I am going to get 10 rows out of the 10 rows again I am going to filter out based on certain kind of condition we can call this hardened filters or not production is vertical filtration this is what we have seen release algebra also okay so if you are able to observe here the conditions this conditions can be a combination of one or more again one or more and this can be used with help of and or not conditions logical and logical R and logical R right I hope you know the meaning right when both conditions are true then only the result is true far and when both conditions are false then only the result is false for R when one is true the result is false for not I hope I understood right yes fine so based upon the way that you are going to write the queries with help of where condition they classified these SQL queries as different types of so SQL queries based on SQL queries based on relational operators based on relational orbit relation operators what are there for us less than greater than greater than or equal to less than or equal to equal to operator equal to operator not equal to is there right but in SQL not equal to will repent like this this is different from all the other things but in some textbooks they will write it as this not equal to also but basically execution point of view you have to use left angular bracket along with the rectangular bracket to reprint not equal to these are the various kind of relational operators okay so meaning that for example I am trying to write select star from student where yes cig is greater than 15 and CIS is less than 45 this kind of query can call it as queries based on relation operators or not because it contains relational orbitals along with the large collaboratory right meaning that it is going to produce all the rows which are going to satisfy the conditions whose sh is greater than 15 and less than 45 less than 45 days right so before going further let me try to execute all the things what we discussed see I am going to have a table star from student table I have Mitten certain data right so what are the columns that I am going to have all those columns along with the enter data I am going to produce right now you can observe here third row third row contains data is empty and a throw data is null rotational right if you want to select display only particular row particular column then what you can do select Sid from student you can able to get only particular column this is equivalent to production for example I want to filter out with respect to certain kind of condition select for example SCH from student guys what the output are you going to expect you can have how many rows are you going to expect eight rows should visitors if you're able to observe here there is 125 is there another 25 is it eight rows or seven rows now you did mistake right so it will be eight race only okay so the reason is the reason is observe you got duplicates also right you got duplicates right the reason is when you are going to study the concepts of relational algebra relation algebra concepts are mathematical operations that are purely based on sets right when you are going to work with sets definitely it is going to remove duplicates so you are going to get unique data you are going to get indicator but whereas coming to SQL it is not actually meant for the sets but internal light is using the concepts of relational algebra right so that's the reason it is going to produce duplicate data also duplicate it also right if you don't want to get the duplicate data we are going to use a one more keyword called distinctly distinct keyword which is meant for to remove the duplicates try to observe now see you got eight rows right mean that I can say that this is not eliminating duplicate data if you want to eliminate the duplicate data you can simply use distinct cable now you got seven rows because 25 is applied for two times okay fine so if you want to filter horizontally then I can write select star from student where scig is greater than 50. and Sh is less than 45 so this kind of core I can call it as war is based on relational operators so you are going to filter out all the things which are greater than 15 and less than 45 okay is there anything which is less than 15 yes Bala is there right so which is not in the resultant relation observed so the same thing what you did now convert it with respect to with respect to another way see how can we read this one can I read as I want to find out all the student deals whose is between 15 and 45. can I call yes so I can say that there are other type of queries are there which are based on range operators which are based on Range operators range operators in SQL are between and not benefit right so I don't want to write here directly I will write here only so what I will do select star from student where essays between Essence between 15 and 45. and you have to make sure that this between operator is enclosure operator meaning that it is going to answer both 15 as well as 45. but that is the difference you have to observe there but if you want to make these two uh queries are equal what you have to modify the above operation you have to compensate greater than or equal to less than or equal because inclusive operator is between right so I can make that you are going to get the same data six rows six rows right if you don't want to get the between I can write as where sh is not between 15 and 45. I will get observed now this is what we can call the square is based upon range operators right so these operators are completely enclosed completely inclusive this is one difference and is there another difference is there the thing is can we able to apply text for relational operators no but whereas coming to between operators we can able to apply text also meaning that where s name between a and j I can write if you are going to say select star from this is something like s name between uh what we can do can I say that between J and K as J and K how it is going to perform it is going to Works based on dictionary order dictionary so it is going to start with j and it's going to end with k so but here K is there right Krishna and Krishna Krishna and God is there okay fine let me try to take one more thing J and B right you are getting like J and V is not getting B is not meaning that at the end uh it is not inclusive V is not including r is it exclusive or into the operator right it is enclosure operator only when it is includes operator I am trying to say that between J and V what are the meaning it is going to start with J followed with j a j a a j a y a j Krishna whatever it is going to make all the possible combinations and so on it is going to do and it is going to end with what we V vishu is available at which location after we you have to go with VA vaa then you have to go with all and then you are going to get VI so it is going to show up at V so that is the reason you are not getting the vishu as output see you have to know the common Matrix probably have idea right right see if you are going to observe the dictionary order basically it's going to start with the a followed with all possibilities of a right followed with B all boundary meaning that string length of one you see if I try to take J j string length one J next string length to how many probabilities are there 26 parameters are there one is from J2 JJ this is string length of two whatever single length of three j a a j a b j a c and so on j a j right so if you are going to do like this K is going to get which location K starting letter with swing length of one and then it is going to start with k a next KB and so on Krishna is getting which location and so on right so but we are expecting here what value between J and K J and K so between J and K it is going to start with string length of 1 and is going to include all these possibilities and it is going to end at this particular thing and it's battling Krishna is already at some other location so that's the reason Krishna is not including the horizon right that is the reason you are getting output like this so for Strings also for since also between operate is going to work similarly which is going to work for date range so it is going to work for numbers text as well as date range also right so another kind of operators are there those are call it as in operators in operators I can call it as set operators right for example I want to find out I want to find out all the students foreign how can I write the query select star from student where sa is equal to 25 are 26 can I write directly or we have to write again column name s is equal to 26. we are going to get all the screenings whose sag is either 25 or 26 the same thing you can able to get with the another way that is through in operator so instead of writing like this what you can do now where essays in essays in 25 comma 26 . are you going to get the meaning is enough rate is equal into R operator meaning that essay is in one of the among the set of operations either 25 or 26 similarly if you are able to write like not available which is not inside that is what I can call Tesla in operators right so you can able to Define either in or not in another kind of classification they classified as patterns meaning that I would like to find out all the student names who start with the J start with J how can you do this basically it couldn't possible right so we're coming to SQL we have a keyword called like keyword is there yes name like s name like you have to write the uh pattern pattern is what J followed with the permission symbol defines 0 are more so I think you have gone through pattern matching in Java language again regular pattern regular Matrix right if star means zero or more plus means one or more I think that you observe flat also in flat also it is there right so in JavaScript also it is there right okay fine so here the percentage symbol is equal into zero or more right for example I want to find out all the students all the students whose name starts with j starts with j and ends with a and ends with a then what you can do in between any number of characters are there so I can make it as J percentage right similarly I want to find out all the students whose name starts with a and ends with a but in between there should be exactly two characteristics then I can use underscore Apprentice underscore apprentation right means underscore means what it will allow only one character underscore only one character so you can have to observe only two kind of uh symbols specifically percentage symbol and underscore symbol for example let me try to write select star from student where s name like which is going to produce 1 Jr 25 right if I am going to say something like K percentage it is going to produce only starts with k now here I want to find out all the students whose length of the string is 4 and that you have to start with okay what I can do mark three times I can view again there are three underscore some actually okay so you are going to get 40 right if you would like to find out all the students who whose name start with k start with K and it can end with again a I can get Krishna so like this you can able to write the queries right similarly for this what is the converse where s name not like K percentage meaning that which is going to get complete over to that condition about that condition okay similarly there are another kind of queries are possible those I can call it as ease operators quarries based on is operator right so ease operators are meant for specifically for the purpose of checking null values null values actually if I am going to have a data if I am going to have a data select star from student if you would like to find out all the students whose s name is not given how can we do that can you write like this select star from student where s name equal to empty I can able to find out M Tech and I will find out but can we able to find null values where s name equal to null what is happening here meaning that null is not compared with nulls in SQL so that's the reason they come across one more operator call is null operator so what you can do now in place of this one where s name is which is going to produce all the rows which are having the null all those means what the column having the null similarly if you'd like to find out all the students whose s name is not null then I can able to Simply use is not null keywords right so these are the quarries that you are able to observe okay fine see whatever the course that we have seen so far see if you take any kind of chorus all those queries will be concerned these patterns only okay and these queries are going to produce certain output you want to produce certain output this output can be either in some order or it will be in the zigzaga okay so let me continue with directly in the command prompt only see observed now I am trying to do select star from student that's it I am going to get my output my output right so this output uh is in some order in some other can I call it as jigsaw gutter but actually this is the order what you have inserted the other in which we have inserted is the data record but if you think that this is a kind of I need it in another way I need to based on sh sorting of sh then I can able to use one more kind of a class called order by class so is meant for to sort the resulted data order by SH now observe what you got they ordered in sh essay that is also in ascending order if you would like to have it in descending order you can go for DSC DSC okay so if it is something like ascending I can go for ascending on right by default the Sorting will be done on ascending that is yes if you would like to go with descending you can go with Des C then it is going to short the data based upon descending order that is what order by class right see guys for example I am going to filter something filter horizontally where essays is greater than 50. this is given what are the meaning of fun I am trying to filter all the rows which are greater than 15 among the data what you are getting I am going to arrange based on descending order listening okay fine guys can you write the query like this can write order by first then where class right that is not correct so you have to make sure that order by class should be always at the last line of the select Commission order by query should be last line of the selector okay fine I don't want to show that execution if you want you can you can just check right otherwise I will try to do that where SA is greater than 50. right so meaning that order by class should have to follow the rule it has to be the last line it has to be lastly okay fine now we got the data if you are able to observe here 25 and 25 as they are having equal I got in the short order the order in which I have inserted now I need to sort the data based on sh then if sci's data is equal I need to sort them based upon their Sid this upon the race ID that also I can do by separating with the comma operator order by SAS comma SS descending order comma based on Sid that should be descending order observe now you got 62 26 again 25 25 in these 25 25 you got based on descending of are they meaning that order by class can able to combine more than one column also foreign this is the meaning of order by class right this should be compulsory should be last line okay fine now let us move with the further continuation try to observe uh I am trying to write select star from star from I am going to extend it to multiple tables where one table is student another table is let me call if it is University we are going to have Department what is the output you are going to get right which is equivalent to which is equal into cross joining cross joint or Cartesian product right meaning that I am trying to combine student table with Department table that's it this is equal into cross joint project I mean for example if you are going to have I am trying to create one table create table Department table whatever conditions you have to write Department number department number which is integer Department name which is very care of 10 so I am trying to create a dynamic memory allocation right I have created the table in this I am trying to insert into Department values of I am going to insert department number one hostname is CSC I am trying to insert multiple values at the same time 2 comma EC is two rows inserted select star from department so you got two rows now I am going to make select star from student comma Department what did you are going to get you are going to get how many rows 8 into 2 that is total 16 rows you are going to get observed each and every row is going to cross part into the other row so we are going to do a total 16. which is equal to normal so is there any meaning for this one there won't be any meaning so that's the reason for this you can able to Simply add one kind of wear condition wear condition so how we are going to add by simply doing uh in for assume that in student table if you are going to have Department number you can able to do that right Department number where department number equal to D number where Department numerical D number right now it is going to have some meaning I am trying to get all the students whose department number is working along with their Department names now how many rows you are going to get eight rows only eight rules only because it is going to emit all the invalid columns right so guys can we do uh alter the student table what is index for alternate student table alter table name is student at what I am trying to add column what is the column name B number column name is D number which is of type integer can I can I enter is it correct what happened here it is query is working meaning that if you are going to say describe student column is column is added okay fine now what I will do now I will try to make some kind of update query update table student update table student set uh set department number equal to 5 where sh is greater than 15 what is the meaning of this one I am trying to set develop number equal to 5 for all the students whose sh is greater than 15. see I am trying to make something like 25 so that it will be good in a mistake another mistake you have to tell directly right you have to use update student so select star from now I am trying to update uh as something like 6 which are less than or equal to 25. okay again mistake again I am trying to do search circumstable okay now observe in the department number what are the dates what are the rows are there one and tuna okay I will make them update again I'll make them updated right so I will try to use update update Department set D number equal to 1 where D num equal to where denom equal to one can we do this one is it correct self self kind of condition yes it is correct denum equal to one okay I am trying to do for D num equal to 5. right is it one row affected only one row right okay good so now I'm trying to do department number as six and D number equal to 2. see actually what I'm trying to do now I'm trying to match with this example right so finally if I'm going to make select star from student I will get data like this if I am going to make select star from Department you'll get data like this right now so if I am going to perform uh like select star from Department comma student what it ever going to get you are going to get all the data also which contains invalid combination I don't want like that I want to have the valid combination where D number equal to D number right what is going to happen it is it is going to get it is going to get error right why it is showing error because both tables contains what name same name when same name is there how it can know that's the reason we are moving for the another kind of concept called Alias names Alias names right so instead of this Alias names also we can go with another process so what is that means uh I will go with only execution directly better so this Dynamics belongs to which one I am assuming it as Department table department DOT D number equal to student dot department now it got idea this number is belongs to student and this D number is belongs to Department table so if I enter are we going to get only eight rows yes exactly is going to happen right so for example I want to print I want to print B numbers of the so what you can do if I want to print the numbers of the both tables can I write like denom comma DM you can write if I write the again couple will get ambiguity which denomin it is so I can write it as student dot Dynam and department so there is no clear meaning here but for the sake of for understandable I am trying to say but the thing here is see here I am trying to write fully qualified name here also I am trying to write fully qualified so wherever I am trying to use like for example if I am going to have n Columns of the student table for all those n columns I had to write student dot Department ID student or Department name student dot Department blockage like that I had to write right that's the problem so instead of writing fully qualified name we can give Alias names to that so whenever you are going to write you are going to write the table name table name you can simply use as keyword as D what are the meaning of this one wherever you are going to Simply use Department I can simply use D similarly student as soon as Now is it going to work again you are going to get error why because once we renamed the permanent table name as some other name you should have to use only temporary names you should have to use only temporary name so here I can remove the student as yes Department as d here the student has yes this way I can able to use Alias names concept okay okay guys so what are the correct that you have seen so far can I call this as a kind of equation means what I am going to apply certain kind of condition that condition contains equal to operator can I call this as uh inner join yes only because we are going to loss all the data which is not uh match that is what I can call this energy okay so the same query I can never write in another way okay so the same coin I can write in another way try to observe uh instead of writing see I will try to write from scratch select select star from star from Department actually comma student they department DOT D number equal to s dot sorry student dot debunked right but the thing is the same core I can write the form of inner joins also because the meaning is same Department I can write the keywords inner join okay what's inner join student meaning that same thing Department become a student I am going to write as Department inner joint student for these two I am going to give someone else name Department as d Department as D student s yes friend as s where D dot b number equal to s dot d number are you going to get the same result or not right of the yes I am going to write the same code again in the form of basic thing select star from Department comma student there okay I will write the fully qualified name only instead of that department DOT D number equal to student dot d number now is these two are the same red object here also got eight rows inside here also got eight rows inside everything is completely same meaning that both are inner joints only but another answer the reason why I have taken this query is when you are going to go for this kind of sentence this kind of sentence if you don't want to last some kind of valid data which is not matched the concept is outer joins which you have seen relational algebra outer Giants right so at that situation this kind of quarries not going to provide solution for you right if you don't want to last the invalid data also then this kind of core is one provide solution other situation you need to write the syntax as see here I am going to have two tables select star from student and select star from Department okay fine if you want to have inner join simply you can make uh use of the syntax what you know right if you want to have outer joints in outer joints we are going to have left outer join right outer join as well as full outer joint so if you want to have the like left outer giant you are going to Simply make sure that selects are from student as yes so it does yes and then you have to make sure that left Joy left to join and you have to write the other side table Department on on so if you want you can write daliasms also as d r s dot department number equal to D dot Department number right you are going to perform it as left Joy left join right similarly if you like to perform the right join you can simply use the same syntax in place of left you can place as right so in red joint what is going to happen wherever it is not match it is going to place its values as usual of the right side table and left side table is going to place null values observe right side it is going to place as this and left side is going to place null values similarly if you would like to have to Define full outer join you can't use full outer join I am just checking with here you can just get error as MySQL doesn't support okay fine so we look into the full outer join the later part now we will continue our session so here the next point is we can able to perform various kind of aggregate operations also right so when you are trying to perform a great operations in relational algebra what are the various kind of aggregate operations are there some some you are going to average mean Max and count and you have to make sure that each and every function is going to take one column as input that it should be clear if you are going to give more than one column as input there is no specific meaning for it if I am trying to perform sum of sh finger it is going to perform the addition of all these of the respective data similarly average means what performing the average operation of the respective data we have seen it in relation algebra similarly Min operation Min of sh which is going to perform the menu value from the respective data maximum means maximum value from the respect data count means what it is going to count the total number of rows of the respective condition right so I will try to do that with the example okay see try to observe I am going to have data select star from student now I would like to find out the minimum age of all those from the all the students then what I can do select mean sh from which table student table I got no message right similarly if I want to have maximum sh ack I can go for maximizes right so here you are getting the output as Max of sh followed by 62 right you can able to rename it also how can you rename it as Max underscore again I will rename it as this one right see if I write like this Max of yes name what the output is it going to work first it will work because it will take ASCII values also it will be converted into ASCII value order for all those alphabetical orders vishu is the largest one similarly you can able to go with the minimum minimum right similarly if I try to perform count of star count of star means all from from student how many rows are you going to get eight rows because eight rows are existed similarly if you are going to give some particular column particular column like sh what the output you are going to get you are going to get again eight again a right so because the sci's count rows count is 8. for example I am going to pass here as yes name what is now count seven because yes account aggregate operation is going to is not going to work on null values it's not going to work on null value that's the reason you are getting here as cell okay fine now let's see ah see for this you can able to combine also count of s name where SAS is greater than 25. you are going to get three so whatever the result that you are going to get that result contains number of rows are three that's the meaning of this one right similarly you can able to find out some operation sum of yes yes one I am trying to find out the sum of the values of the edges whose age is greater than 25. 25 right yes yes similarly can you able to find out the average 41 right so for example I am writing like this sum of h by count of essence is it correct is it correct yes it is correct right so this is one of the gate question of actually one of the following is invalid this is one of the categories so this like this also we can able to do so what I can say now here for min max count you can able to give numerical values or you can able to give text as values coming to summoned average you have to give only numerical values that is what you have to understand right that will see in the next place okay okay so this is for today's class in the next class we'll continue further