# SQL Functions Explain | SQL DEVLOPER | SQL DBA| ETL DEVELOPER | SOFTWARE TESTING COURSE | JOYATRES|

you know okay so aggregate from sums we can do so what is function how many categories of function many things are there we'll discard details whenever session will be there but this is aggregate function so in this segregate function some functions are there we'll discuss on that okay so function is nothing but the sub kind of sub program only so if we are writing some functions then function name is required function name what is that then function name always be written within parenthesis and in this parenthesis first argument is input value that it must require minimum one argument is must required and after that it can be depend on the categories of that function so some are required one parameter some are required two parameters some are required five parameters some are required six parameter like that and out of all these fews are mandatory and future optional okay so here parameter list okay so any number of parameters so this will be mandatory input value that is mandatory and after that some parameter lists are there those are optional maybe for few it is optional perfume it is mandatory also okay so in this aggregate functions which are there aggregate function is also called as group function group console so grouping wise the result will be given so if you have a group suppose that cricket team is one group or chess players one group or one branch is there in a college it Branch mechanical Branch computer science branch those are one one group so as a group result it will give what is the highest lowest and all those things it will give as a group result so for that sum group or aggregate functions are there so what are those Max mean some down average so these are the five functions since this is function so we have to write within parenthesis everywhere parenthesis only need to keep so max function will give the highest value highest value of one column suppose student table is there so student marks will be there in one column so what is the highest Mark of that student what is the lowest Mark of that student using minimum we can get the lowest using sum we can get total after doing the addition what is that value total merge suppose count how many subjects are there how many populations are there how many countries are there how many states are there so those are the count total how many are there of total count we can mention similarly average so average formula is sum divided by a count so what is the total sum total value divided by total how many are there then we can get the average so these are very common things in arithmetic mathematics it is present in standard three four five seven eight classes also so everybody known about that now we can see this in SQL in SQL also it is available and in many topics very frequently it is using okay so we can write one query how to write select we can give that is a clause and in that select clutch we can mention some column names with the aggregate functions so I am writing Max column we can take so we have one employee table given in this Oracle database so from HR dot employees table we can give so make salary similarly mean salary we can give with comma then sum salary some salary then count of salary then average of selling so you type 9 it is okay uh average salary from HR that employees so we have one table employees table it will see select star from HR dot employees see the data salary is one column so this is a table consists of rows and columns rows and column data is present means that is called a structure data for structured data the language used is SQL structure query language so salary is one column in that column what is the highest value what is the lowest value what is the average what is the count those things it will display so here 107 employees are there if you will do right click count rows 107 rows so out of these 107 rows which have 107 employees this is employee stable so employees in permissions are there what is the highest all are getting salary so what is highest that it will display what is lowest that it will display but is the average what is sum of all these values how many are getting salary all those things it will display so we can see this so out of all 107 employees one person is getting 24 000 salary which is highest maximum one is getting 2100 that is minimum one is getting six flux 91 000 for 16. this is some count of salary so how many are getting an average so this is very straightforward so in this Max mean and sum purely straightforward nothing is there just pick up the highest value lowest value sum of that value the problem is with count and in count only many people are doing Mystic here only tricks are there so we'll specially discuss about this town now how it is counting so some rules are there if using this aggregate functions not only for count any aggregate functions we can use it will exclude null always so what is null then null means unidentified so null is one kind of thing in database it is a missing kind of value suppose one person is getting bonus 5000 in one year another person is not getting bonus so that is not zero not getting anything means that is null so those kind of thing is null so it is a missing kind of value we can tell it is undefined it cannot be defined it cannot be comparable on compare it is not exist on exist 10 and 20 we can compare which is greater which is lower but null and null we cannot compare or null and some other thing also we cannot compare those kind of value is null so it is not present also it is missing always okay we cannot do any calculation with null also it is not calculated those kind of values now so if you will see here then we can execute the select star from HR dot employee select we have to mention the column name star for all the columns all all the things very detailed level will discuss demo is not sufficient for half an hour just picking some topic from we can supply the data here we have supplied the data with table name and schema name so after executing you are getting one commission column is there see here so Queen commission PCT means commission percentage so many employees are having non non null in this column means they are not getting any permission again some employees are getting commission 0.4 0.3 and all those things again some are not getting permission again some are getting permission so who are not getting permission for them it mention no so don't think that knowledge zero it is not zero also some are thinking knowledge is space no knowledge not a space Also so this is on different kind of thing it cannot be compared with any value it cannot compare with that same null also null null book are not same if you are telling these null equal to null also that is false that is false null equal to null we cannot compare if we are comparing then we can tell that both are same for two if 10 equal to 10 means we are able to compare 10 10 both are same we can tell 10 equal to 10 is true but knowledge on exist kind of thing that's why we cannot tell that both these two are same also we cannot tell these two are not same also we cannot tell no just asking one question related to null cast null is not equal to null so here we have given not equal to symbol in operator we will discuss which symbol is for what this is not equal to in arithmetic already it is there everybody know about this so can you tell null is not equal to null is it true or false it's a false false so we cannot compare we cannot tell that it is greater than also we cannot tell that that is less than also we cannot tell that both are equal we cannot tell that both are not equal also now another thing is writing non is and all can you tell now this is true or false and another is non is not known we see through which is false or well or false thank you knowledge category knowledge not a value don't think acknowledge a value like 10 or 5 or 0 or ABCD or XYZ so null is null is it true or is it false the common sense not related to SQL [Music] so one simple example giving tiger is there so tiger is a category of animal animal category it is coming so many categories of animals are there so tiger also one category in the tiger category baby tiger is there old Tiger is there then man tiger woman tiger Middle East tiger red tiger yellow tiger white tiger many tigers are there but if we are telling tiger each tiger is it true yes or no yes yes it is a category so null is known will be true or false now this will be true [Music] is not tiger that is false okay so this is very important and non-related many people are doing Mystic if you know this concept where it is true and where it is first it will help a lot not in many places because all related many tricky things are there not only in one Topic in many places and tricky things are they are related to Norm and today also we are going to do that one okay now always exclude knob so whenever we are doing any aggregate function it will always exclude null suppose here 1000 is there 5000 is there one null is present another null is present eight thousand is there so how many values are there in this column suppose this column name is called how many values are there so here how it will count one two three null will be not count always exclude null not only for count function if maximum if you are taking also what is high as 5000 is highest no 8 000 is highest so it will not consider null it will exclude faster any knowledge they are in that column first it will ignore then it will take highest or lowest or sum or average or count not only for one function all these five functions null will be not considered anywhere how many arguments are using in these functions aggregate functions only one argument that is the input value and what is that that input value is a column name and what column we are using here salary column we are using here in this table now we can go for this count okay so Max mean and some purely straightforward no problem at all in count only many people are doing Mystic now we can do something if you have some little bit of knowledge in SQL now can you tell this answer select count of one from some table name we are giving so you don't know or who know can you just try and tell but it will display or what it display it will display the first column counter First Column okay no it will display entire table okay enter table what count how many tables are there count of one means it is counter Star right [Music] so first we can take column number that is the problem count of star many people are telling First Column that is the program that's why I only discussing this okay wait five ten minutes we will able to learn those select count of salary from HR dot employee but it is displaying executive count of I have given a column name so can you tell what it is displaying so the values which are present in the particular column name will be displayed I mean the number of or not seven it will display putting nulls okay so 1.7 so in this employees table one not seven rows are there so it is displaying one not seven now I am changing another column uh here only we can give count of another column so before sometime you already have seen that permission PCT column contains so many nulls now what you are thinking same one zero seven it will display no null will be excluded right no information so so many nodes are there in the spelling ISS summation okay so so many nulls are there those are excluded and after that it is calculating one two three four five and all those so 35 values are they are excluding now in salary column no Norms are there that's why one zero seven so what you understand here count of column name means count of non null values in the column now another thing we can do count of null so what is null already told so many things if we'll mention count of null using any of the table then what it will do count of suppose null null is not exist null is uncomparable not is not visible knowledge on exist knowledge uncalculated kind of thing it is not zero also it is not a space so what it should display then count of null 0 so it is uncountable so if we are counting then the result it will display at zero okay so count of null is always zero now another one count of one count of two from a chapter templates so can you tell what it will display maybe few already you know somebody told uh total row count in the column one is of account one it will display First Column details and the counter it will be the second column okay any other same account one will give the total record okay okay so somebody posted in online account 12 you give the total values in column one count two total values how many are there in column two so many people are telling that answer that is purely wrong some are poster that doesn't mean that in online whatever it is there it is correct maybe some are posted wrong maybe some are posted correct so if we are giving here then 107 is displaying so in column number one 107 value is there in column number to 107 values there if that is the case suppose we can give count five thousand n so in our table 5000 column is not there so what will happen it will throw error in that case then we don't have 5000 columns but it is also giving one zero seven so what you understand now here then any numeric value if you are giving positive numeric value means it will give the row count not only for First Column count not only for second column count somebody posted that wrongly and ninety percent candidates are learning that and telling in many places that is purely wrong not for First Column second column if that is the case then 5000 it will give the error but it is not giving the error so count of some positive numeric field we have given which is giving the result as row count now we can give some negative value also and decimal value also so what you are thinking it will work or not work minus 1 suppose I have given and here to point i47 and here minus 50 7.479 purpose for it so what you are thinking it will throw errors no okay so what it will display then that think and tell it is ultimately a numeric value as you said positive numeric only but here positive I mentioned but here we have we have given negative and decimal also given floating points also maybe it might be difficult to error which one I think it might not so what you got but in this broke out so it can be positive value it can be negative value it can be positive decimal value it can be negative decimal ways even if 0 if you will give also it will give the row count of zero can I ask one question okay this is also one zero seven now okay just count we can complete then you can ask okay okay so in Middle means maybe concentration okay so now some other kind of value I am giving count of some character suppose I am giving a so anywhere character is using not only for today session any any any Topic in SQL if any character is using date is using means compulsory we have to write within single put if numeric value and column name we are using means don't give any single quote so I have given one single quote otherwise it will throw error so one alphabet we have given and that is lowercase alphabet another one I am giving T capital letter uppercase giving now next one symbol also I am giving at the Red symbol count of some date I am giving today's date is 19 man 2023 date value also we have to write within single board symbol we have to write within single port alphabet value we have to write it in single code column name don't write within single portal if writing single quote it will consider as character numeric value don't write within single port now accept numeric value we have given some other value so can you think and tell what we display everything errors no error then what it display the same as above okay one zero seven so what is the conclusion now any character you can give or any value you can give from the keyboard it will throw or it will give the result as row count only if you need count of star you can give also it will be row count so just close your eye and touch one he or one character in the keyboard which is available it will give the row count only no need of anything no need to ask anybody so forget about one two five and all those things it can be any positive numeric value it can be any negative numeric value it can be positive decimal point it can be negative decimal point it can be Alpha it will be with uppercase it can be alphabet value with lower case it can be any symbol it can be star it can be anything whatever it is available in our keyboard anything blindly you can take simply it will keep the lookout so we can mention as a summary any value giving the result Edge row count okay so these are the three different meanings of count if you are knowing this knowledge of this count with these three then it is very easy anywhere count related things are there and due to this lack of knowledge only many people are doing mistake in the exam as well as in the real time and everywhere so just you can go through for half minute about this three point try to remember now only related to that few questions we lost you can tell any cost you can remember now only count of column name if we are giving means in that column how many non-non values are there that it will display it will count only for non-non values count of null if you are doing means always it will be display count of count of not always it will be zero knowledge uncountable count of any value don't give column name don't give null any value you are giving any kind of value positive negative numeric decimal floating uppercase lowercase symbol date star anything that is giving the row counter okay so everybody remember this related to this some questions okay now asking one question select count of first time taking average so average of commission PCD then form of permission PCT divided by count of permission PCD after that so you write in the commission is in different formats permission okay underscore underscore package okay another is giving permission underscore PCT with star these are the three things I have given from HR Dot employee stable and these employees table contains 107 rules everybody know that again mentioned there so now the question is all these three are related to average you see direct function name given average this is some divided back out for getting average this is also some divided by count for getting average so now the question is all these three will give the same result or different result uh first two columns will be the same results and the last one will give different results as count of stores average okay so what about others okay all will give same results yes yes execute okay so concentrate Whatever It Is Telling and again told that those three points you can try to remember now only now if you'll see here no sir no sir yes yes count of star it will give whole row count one zero seven so if you are doing some suppose that sum is five thousand so if you are dividing 5000 with 35 and you are dividing five thousand is one zero seven is it same no no no second will give this second will give will not give the same now which will be same then one and three one and three however Mission PCT is 35 excluding null average also aggregate function it will exclude not most are all will give the different results so which will be the same then what is the formula of average by totally divided by total count yes again in that it is coming two functions sum and count yes in that count again null will be excluded so this is coming in sums this is coming so at the end these two will give same or different how it will be different yeah yeah some divided account is the formula of average indirectly you can mention with the formula anything is fine so if you are expanding with the formula then this is the formula super Mission PCT column is coming commission PCT column is coming both the place has 35 uh sum is 35 yes will be same this will not same because your total row count we are giving so this will be different in many of the real scenarios it will come some example count related lot of things are there people are doing mistake suppose some profit loss need to find out and give a report so in that cases suppose CR trading is there or interest related anything any scenario is there so in that case as if they have given that calculate for or consider for all the customers and find out the report how much percentage of loss or profit happen so all the customers means what to do some customers enroll in that bank for sale trading or open the account recently just five days before verification is not happened real scenario only verification is not happened and due to that in that customer ID column their ID number is not there empty and maybe account number is not generated due to some problem they have not submitted some documentations or maybe verification is not clear or something negative result came out or something happened due to some reason it is in hold and for them account number is not mentioned it is not but in their bank they are telling that consider all the customers and give me the report of the percentage of profit so who are not also it will be considered all customers who are not having customer ID also they will they already enrolled with they have given some amount to us so maybe it will take five days 10 days or 20 days they will be considered so in that cases average if you are taking any average profitably related calculation star you have to use this formula again the same kind of thing only scenario it changed they are telling that no no customers who are not uh means having the customer ID or bank account number maybe they are not a pay lead customer they have some uh means blacklisted or maybe something happened so they are not getting all those we can reject them we cannot accept them in our bank we are very reputed Bank don't consider that so in those cases who are having normal don't consider so average what you will do using this you will do either of these are these understand this scenario blindly don't use average many people are blindly doing average otherwise blindly doing this problem start developer having no knowledge of this test or having no knowledge of this testing testers are not able to find out this developers are not able to fix this small is only it is going 8 Days 10 days and lot of problems are happening at the end client is capturing and in those cases it is good in the very rare zone so very small things only this non-related concept but due to this 10 15 years experience guys also doing Mystic okay so now we can execute these two will display same this will display different now some other question asking before that telling one concept one keyword is they are distinct select Department ID from HR dot so aggregate functions completed with all these things now another topic telling then again we'll ask you questions select Department ID from HR dot employees but it will display it will display the list of department ID values these are the list so so many values are repeated also and it is not in sync ascending or descending order so if we want to see that in ascending or descending order then we can use order by order by what is the column name Department ID these are the list so in sorting order it is this thing ascending order wise looking good now in this department if we see scroll down down down down down down last is null so if ascending order wise we are sorting null will display last always this is one of the interview question similarly if we are sorting that column in descending order then null will come first nothing no it will come first then some highest value to lowest value it will certain descending order now we don't want to see the repeated value all the value we want to see but only one time don't miss any single value but display only one time so here it is displaying so many times 10 20 20 30 30 30 30 30 40 50 50 50 50 50 so many so all values we will display but don't display any repeater value that is the requirement so what to do here need to give distinct these are the list of values or list of departments we can tell in ascending order it is there 10 20 30 40 50 60 70 80 90 100 110 and not okay so now also if more than one time it is present finally it will display one time only every value it will display but single time only it will destroy that is the use of distinct distinct is one keyword which will suppress the duplicator values and it will display one time only with all the values including null for everything one time it will display from that column that display but distinct is always not appropriate very easy one everybody know that but but two zombies cleoperator firstly that doesn't mean that more price no performance issue so distinct if we are using means if we have a 20 lines of query after executing everything finally it will apply the distance problem start our scenario answer will be changed question is different answer will come different and again after complete all the execution of function some other function then some other Clauses and everything this will be applied that is one problem and another problem is each row one by one one by one one by one it will do another problem so if Rose close billions of records are there then unnecessary it will take 30 minutes 40 minutes one hour and who will wait for one hour for one small task that is not the good approach so other alternatives are there for this distinct also in those sessions you can understand in small time you cannot understand all those startup alternative approach are there now this is fine distinct we did now one small question asking select and another one telling select dual is one table in Oracle database star from 12. so column name is Domi value is capital x so this table is provided in Oracle as well as in many databases they have given some dummy table just for normal calculation 10 plus 5 what is that 20 multiplied with some value what is that just as a calculator purpose they have given we can use some other table also but the problem is in those tables so many rows are there the same result it will display so many times now we can give select 10 multiply with 20 10 divided by 4 10 plus 5 10 minus 6 again we can give 10 with some operation null also so if we are doing some operation with null then the return result will be null only from to n so dual is a table they have given for doing the normal calculation just like a calculator purpose so it is giving this result so is it possible by using some other table or not yes that is also possible now we can use this HR dot employees table we can do from select this this from Etc e m p l y e s okay this is also displaying that same result but why repeatedly it is displayed because employee state will contain 107 rows one not seven rows so the same result it will repeat in all 107 rows so how we can make it unique and distinct now execute now same so we can get by using some other table also this kind of calculator value but why we are going to use this dual table then yes that is correct but distinct is a partially operator already told firstly keyword it will degrade the performance so if prose approach are there and we are doing all these kind of things it will display so many times unnecessary which is useless and again on top of that after displaying it will make the unique each and every row each and every way to check whether that is same or not same or not same or not for all those columns all those rows it will drink so again it will take time one hour 30 minutes like that million billion subtractors are there so why we use this distinct then better to go for dual table here one row is there result will come with one row which is a common calculation now you can tell select distinct tiger is giving tiger lion means people can remember if 1020 means nobody can remember select distinct of tiger from dwell can you tell what it will display tiger how many times it will display sorry one time only one time one time only one time only tiger similarly any things you can give this thing top 10 10 it will display District or Romeo Romeo it will display distance of India India it will display distinctive uh USA USA to display distinct from laptop laptop whatever we can give distinct of anything we are giving that only it will display so just remember tiger I have given now some questions are they are very good questions just five minutes you can tell that then we can wind up today okay see here select star from test underscore demo so test underscore demo is one table we have and these are the data let's see here one one one non non none and another one so we can take this also one screenshot okay so here how many one is there three ones are there you can summarize also total rows how many are there seven see here one two three four five six seven rows are there how many nulls are there how many ones are there these are the summary now some questions are there everybody you can try and tell first question select count of one from test underscore demo particularly four four seven okay Monique so any other Monique anush a okay those three points already told again you forgot no count will be Q the seven only so these are the three points any cost you can remember already told that's why today we are doing again you can see count of column name means it will exclude now count of any value means it will not exclude not at the total row count how many rows are there count of knowledge always zero only three things seven seven yes okay so what it will display seven seven now next one don't do mistake correctly tell amount of star what it will display seven seven any value seven seven count of null what it will display zero zero zero again these three are very good and very danger also lot of things are happening in real time project okay so count of null what it will display then zero zero [Music] distinct of call one from test demo tell now what it will decide one sorry two two distinct of one is everything it will display but one time one null [Music] well I agree with this one and none yes before sometime only told whatever already explained that only asking not extra one and none so null how many times it is present one time it will displace one also any number of times it is present one time it will display one and all now select start from testem order by call one descending so none will come fast or non will display last null will come first one will pass okay one will first no one will last we will use first nor last now select count of call one from test underscore demo now tell these are straight forward now little bit [Music] count of call one if column name is a column name four four now this one it will give call one of all problems seven seven seven Hungarian seven or four Sorry Seven okay so count of anything you see anything will display a seven right you said yeah not called it is a single coat right so it should be the display the characteristics so if it uh single code for what we can write for the text for text you can tell for number column name we don't write single code so single code if writing then it will not consider this is a column even if that is a column that is the C that's why if somebody is asking they can give a column name if some other thing they are giving tiger and anything anybody can tell that if giving a column name you know but you will do the mistake that is the thing so call one is a column name but writing within single code means it will not strike your mind within a single code if writing means that will consider as character it can be anything it can be single quote is writing means that is a character like tiger telling is you can tell answer x y a t you can tell answer but column name with single quote count of any value is row count seven now next one select distinct count of call one inner will be first next that will apply in the outer count of call one already did distinct also already did and some example also told for distinct distinct of tiger will be tiger to remember that example given may be distinct of 10 is 10 you can forget so tiger example given now tell this answers so 4 okay only one two are telling everybody tell anush sundariya Munich for it it will divide right column one it will divide one and null distinct this is again you are doing mystery count of column means what we have done already here immediately you forget and distance of India is India okay so this is now next just I changed distinct taken inside and count taken outside today topic you are able to tell me this will be perfect in SQL this is the entrance test for you you can do excellent you will do very good anywhere you can go high package only you will get you get seven it's not seven um seven sir distinct of call one already did that where is that distinct of call one and all in a column it is displaying one and all then in that column below count of to that column name will apply so count of column name means what again two so how that is displaying one Norm so this is one column suppose again on top of that count you are using count of column name means what one [Music] only one one one count s one or two all now Instinct of count one from test demo seven seven count of one one means already we did any value that is seven distance select distinct of count of null so count up you know zero zero zero to each zero zero next is so okay so these are some tricks or very good questions related to these distinct as well as counter so count is perfect means many places will not press any so due to this count only lot of people are facing problem okay so already 8 10 we can close for today

*2023-05-28 04:08*