sql functions3.pdf about the function of sql

Very interesting and easy to learn

1. Informatics Practices
Class XII ( Asper
CBSEBoard)
Chapter 3
Database query using sql–
functions and data reporting
Visit : python.mykvs.in for regularupdates
New
syllabus
2023-24

2. SQL functions
Basically,itisasetofSQLstatementsthatacceptonlyinputparameters,perform
actionsandreturntheresult.Afunctioncanreturnanonlyasinglevalueoratable.
Functionsarenotalternatetosqlcommandsbutareusedasapartofsql
command(generallyselectcommand).
TypesofFunction(Systemdefined)
Ascalarfunctionisafunctionthatoperatesonscalarvalues--thatis,ittakesone(or
more)inputvaluesasargumentsdirectlyandreturnsavalue.Maths,text,date
functionsetc.Thesefunctionscanbeappliedovercolumn(s)ofatabletoperform
relevantoperationonvalueofeachrecord.
Fore.g.selectleft(name,4)fromstudent;
Willdisplay4leftsidelettersofeachrowofnamefieldfromstudenttable.
Anaggregatefunctionisafunctionthatoperatesonaggregatedata--thatis,ittakes
acompletesetofdataasinputandreturnsavaluethatiscomputedfromallthe
valuesintheset.E.g.max(),min(),count(),sum(),avg().Generallytheseareusedfor
reportpreparation&mostlyusedwithgroupbyandhavingclause.
Visit : python.mykvs.in for regular updates

3. SQL functions
Mathematicalfunctions–Performoperationovernumericvalue
POWER()–power()returnsthevalueofanumberraisedtothe
powerofanothernumber.Thesynonymofpower()ispow().
Syntax-pow(m,n)
mAnumberwhichisthebaseoftheexponentiation.
nAnumberwhichistheexponentoftheexponentiation.
E.g.
Mysql> select pow(2,3);
Mysql>8
Mysql> select pow(2.37,3.45);
Mysql>19.6282……
Visit : python.mykvs.in for regular updates

4. SQL functions
Mathematicalfunctions
ROUND()–theround()function
returnsanumberroundedtoa
certainnumberofdecimalplaces.
Syntax-ROUND(column_name,decimals)
column_name-Required.Thefield
toround.
decimals-Required,Specifies
thenumberofdecimalstobe
returned.
Decimalplacespositionvalueisrounded
tonextinteger,ifitsnextrightside
numberis>=5
Defaultdecimalplaceis0positionifwe
notspecify
Visit : python.mykvs.in for regular updates

5. SQL functions
Mathematicalfunctions
MOD()–TheMOD()functionreturnstheremainderofone
numberdividedbyanother.Thefollowingshowsthesyntaxof
theMOD()function:
Syntax-MOD(dividend,divisor)
Dividend-isaliteralnumberoranumericexpressiontodivide.
Divisor-isaliteralnumberoranumericexpressionbywhichto
dividethedividend.
E.g.
Mysql> SELECT MOD(11, 3);
Mysql>2
Mysql> SELECT MOD(10.5, 3);
Mysql>1.5
Visit : python.mykvs.in for regular updates

6. SQL functions
Textfunctions-Performoperationoverstringvalues.
UPPER()–UPPER(str)
Returnsthestringstrwithallcharacterschangedtouppercase.
mysql>SELECTUPPER(‘Tej');
->‘TEJ'
UCASE(str)-UCASE() is a synonym for UPPER().
LOWER(str)-Returns the string strwith all characters changed to
lowercase
mysql> SELECT LOWER('QUADRATICALLY');
-> 'quadratically’
LCASE(str)
LCASE() is a synonym for LOWER().
Visit : python.mykvs.in for regular updates

7. SQL functions
Textfunctions-Performoperationoverstringvalues.
SUBSTRING(str,pos)-SUBSTRING(strFROMpos),
SUBSTRING(str,pos,len)-SUBSTRING(strFROMposFORlen)
Theformswithoutalenargumentreturnasubstringfromstringstrstartingatpositionpos.Theformswitha
lenargumentreturnasubstringlencharacterslongfromstringstr,startingatpositionpos.Theformsthatuse
FROMarestandardSQLsyntax.Itisalsopossibletouseanegativevalueforpos.Inthiscase,thebeginningof
thesubstringisposcharactersfromtheendofthestring,ratherthanthebeginning.
mysql>SELECTSUBSTRING(‘practically',5);
->'tically'
mysql>SELECTSUBSTRING('foofarbar'FROM4);
->‘farbar'
mysql>SELECTSUBSTRING('Quadratically',5,6);
->'ratica'
mysql>SELECTSUBSTRING(‘Aakila',-3);
->'ila'
mysql>SELECTSUBSTRING(‘Aakila',-5,3);
->'aki'
mysql>SELECTSUBSTRING(‘Aakila'FROM-4FOR2);
->'ki'
MID(str,pos,len)
MID(str,pos,len) is a synonym for
SUBSTRING(str,pos,len),substr()
Visit : python.mykvs.in for regular updates

8. SQL functions
Textfunctions-Performoperationoverstringvalues.
LENGTH(str)-Returnsthelengthofthestringstr
mysql>SELECTLENGTH('text');
->4
LEFT(str,len)-Returnstheleftmostlencharactersfromthestringstr,or
NULLifanyargumentisNULL.
mysql>SELECTLEFT(‘Toolbar',4);
->‘Tool‘
RIGHT(str,len)-Returnstherightmostlencharactersfromthestringstr,or
NULLifanyargumentisNULL.
mysql>SELECTRIGHT(‘Toolbar',3);
->'bar'
Visit : python.mykvs.in for regular updates

9. SQL functions
Textfunctions-Performoperationoverstringvalues.
INSTR(str,substr)-Returnsthepositionofthefirstoccurrenceeofsubstring
substrinstringstr.
mysql>SELECTINSTR(‘Toobarbar','bar');
->4
mysql>SELECTINSTR('xbar',‘ybar');
->0
Visit : python.mykvs.in for regular updates

10. SQL functions
Textfunctions-Performoperationoverstringvalues.
LTRIM(str)-Returnsthestringstrwithleadingspacecharactersremoved.
mysql>SELECTLTRIM('Toolbar');
->‘Toolbar‘
RTRIM(str)-Returnsthestringstrwithtrailingspacecharactersremoved.
mysql>SELECTRTRIM(‘Toolbar');
->‘Toolbar‘
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)-Returns the string strwith all
remstrprefixes or suffixes removed. If none of the specifiersBOTH, LEADING, or TRAILING is
given , BOTH is assumed.
mysql> SELECT TRIM(' tool ');
->'bar'
mysql>SELECTTRIM(LEADING'x'FROM'xxxtoolxxx');
->‘toolxxx'
mysql>SELECTTRIM(BOTH'x'FROM'xxxtoolxxx');
->‘tool'
mysql>SELECTTRIM(TRAILING'xyz'FROM‘toolxxx');
->‘tool'
Visit : python.mykvs.in for regular updates

11. SQL functions
Datefunctions-Performoperationoverdatevalues.
NOW()-Returnsthecurrentdateandtimeasavaluein'YYYY-
MM-DDhh:mm:ss'orYYYYMMDDhhmmssformat,dependingon
whetherthefunctionisusedinstringornumericcontext.
mysql>SELECTNOW();
->'2020-04-0523:50:26'
mysql>SELECTNOW()+0;
->20200415235026.000000
Here+0means+0second
DATE(expr)-Extractsthedatepartofthedateordatetime
expressionexpr.
mysql>SELECTDATE('2003-12-3101:02:03');
->'2003-12-31'
Visit : python.mykvs.in for regular updates

12. SQL functions
Datefunctions-Performoperationoverdatevalues.
MONTH(date)-Returnsthemonthfordate,intherange1to12
forJanuarytoDecember,or0fordatessuchas'0000-00-00'or
'2008-00-00'thathaveazeromonthpart.
mysql>SELECTMONTH('2008-02-03');
->2
MONTHNAME(date)-Returnsthefullnameofthemonthfor
date.
mysql>SELECTMONTHNAME('2008-02-03');
->'February‘
Visit : python.mykvs.in for regular updates

13. SQL functions
Datefunctions-Performoperationoverdatevalues.
YEAR(date)-Returnstheyearfordate,intherange1000to9999,
or0forthe“zero”date.
mysql>SELECTYEAR('1987-01-01');
->1987
DAY(date)-Returnsthedayofthemonthfordate,intherange1
to31,or0fordatessuchas'0000-00-00'or'2008-00-00'that
haveazerodaypart.
mysql>SELECTDAYOFMONTH('2007-02-03');
->3
DAYNAME(date)-Returnsthenameoftheweekdayfordate.
mysql>SELECTDAYNAME('2007-02-03');
->'Saturday'
Visit : python.mykvs.in for regular updates

14. Visit : python.mykvs.in for regular updates
AggregateFunctions&NULL-Performoperationoversetof
valuesConsideratableEmphavingfollowingrecordsas-
Nullvaluesareexcludedwhile(avg)aggregatefunctionisused
SQLQueries
mysql>SelectSum(Sal)fromEMP;
mysql>SelectMin(Sal)fromEMP;
mysql>SelectMax(Sal)fromEMP;
mysql>SelectCount(Sal)fromEMP;
mysql>SelectAvg(Sal)fromEMP;
mysql>SelectCount(*)fromEMP;
Emp
Code Name Sal
E1 Mohak NULL
E2 Anuj 4500
E3 Vijay NULL
E4 Vishal 3500
E5 Anil 4000
Result of query
12000
3500
4500
3
4000
5
SQL functions