Oracle-OCP-SQL-3-Single-Row-Functions

Copyright©OracleCorporation,2001.Allrightsreserved.Single-RowFunctions3-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•DescribevarioustypesoffunctionsavailableinSQL•Usecharacter,number,anddatefunctionsinSELECTstatements•Describetheuseofconversionfunctions3-3Copyright©OracleCorporation,2001.Allrightsreserved.SQLFunctionsFunctionInputarg1arg2argnFunctionperformsactionOutputResultvalue3-4Copyright©OracleCorporation,2001.Allrightsreserved.TwoTypesofSQLFunctionsFunctionsSingle-rowfunctionsMultiple-rowfunctions3-5Copyright©OracleCorporation,2001.Allrightsreserved.Single-RowFunctionsSinglerowfunctions:•Manipulatedataitems•Acceptargumentsandreturnonevalue•Actoneachrowreturned•Returnoneresultperrow•Maymodifythedatatype•Canbenested•Acceptargumentswhichcanbeacolumnoranexpressionfunction_name[(arg1,arg2,...)]3-6Copyright©OracleCorporation,2001.Allrightsreserved.Single-RowFunctionsConversionCharacterNumberDateGeneralSingle-rowfunctions3-7Copyright©OracleCorporation,2001.Allrightsreserved.CharacterFunctionsCharacterfunctionsLOWERUPPERINITCAPCONCATSUBSTRLENGTHINSTRLPAD|RPADTRIMREPLACECase-manipulationfunctionsCharacter-manipulationfunctions3-9Copyright©OracleCorporation,2001.Allrightsreserved.FunctionResultCaseManipulationFunctionsThesefunctionsconvertcaseforcharacterstrings.LOWER('SQLCourse')UPPER('SQLCourse')INITCAP('SQLCourse')sqlcourseSQLCOURSESqlCourse3-10Copyright©OracleCorporation,2001.Allrightsreserved.UsingCaseManipulationFunctionsDisplaytheemployeenumber,name,anddepartmentnumberforemployeeHiggins:SELECTemployee_id,last_name,department_idFROMemployeesWHERElast_name='higgins';norowsselectedSELECTemployee_id,last_name,department_idFROMemployeesWHERELOWER(last_name)='higgins';3-11Copyright©OracleCorporation,2001.Allrightsreserved.CONCAT('Hello','World')SUBSTR('HelloWorld',1,5)LENGTH('HelloWorld')INSTR('HelloWorld','W')LPAD(salary,10,'*')RPAD(salary,10,'*')TRIM('H'FROM'HelloWorld')HelloWorldHello106*****2400024000*****elloWorldFunctionResultCharacter-ManipulationFunctionsThesefunctionsmanipulatecharacterstrings:3-12Copyright©OracleCorporation,2001.Allrightsreserved.SELECTemployee_id,CONCAT(first_name,last_name)NAME,job_id,LENGTH(last_name),INSTR(last_name,'a')Contains'a'?FROMemployeesWHERESUBSTR(job_id,4)='REP';UsingtheCharacter-ManipulationFunctions1231233-13Copyright©OracleCorporation,2001.Allrightsreserved.NumberFunctions•ROUND:RoundsvaluetospecifieddecimalROUND(45.926,2)45.93•TRUNC:TruncatesvaluetospecifieddecimalTRUNC(45.926,2)45.92•MOD:ReturnsremainderofdivisionMOD(1600,300)1003-14Copyright©OracleCorporation,2001.Allrightsreserved.SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMDUAL;UsingtheROUNDFunctionDUALisadummytableyoucanusetoviewresultsfromfunctionsandcalculations.1233123-15Copyright©OracleCorporation,2001.Allrightsreserved.SELECTTRUNC(45.923,2),TRUNC(45.923),TRUNC(45.923,-2)FROMDUAL;UsingtheTRUNCFunction3121233-16Copyright©OracleCorporation,2001.Allrightsreserved.SELECTlast_name,salary,MOD(salary,5000)FROMemployeesWHEREjob_id='SA_REP';UsingtheMODFunctionCalculatetheremainderofasalaryafteritisdividedby5000forallemployeeswhosejobtitleissalesrepresentative.3-17Copyright©OracleCorporation,2001.Allrightsreserved.WorkingwithDates•Oracledatabasestoresdatesinaninternalnumericformat:century,year,month,day,hours,minutes,seconds.•ThedefaultdatedisplayformatisDD-MON-RR.–Allowsyoutostore21stcenturydatesinthe20thcenturybyspecifyingonlythelasttwodigitsoftheyear.–Allowsyoutostore20thcenturydatesinthe21stcenturyinthesameway.SELECTlast_name,hire_dateFROMemployeesWHERElast_namelike'G%';3-18Copyright©OracleCorporation,2001.Allrightsreserved.WorkingwithDatesSYSDATEisafunctionthatreturns:•Date•Time3-19Copyright©OracleCorporation,2001.Allrightsreserved.ArithmeticwithDates•Addorsubtractanumbertoorfromadateforaresultantdatevalue.•Subtracttwodatestofindthenumberofdaysbetweenthosedates.•Addhourstoadatebydividingthenumberofhoursby24.3-20Copyright©OracleCorporation,2001.Allrightsreserved.UsingArithmeticOperatorswithDatesSELECTlast_name,(SYSDATE-hire_date)/7ASWEEKSFROMemployeesWHEREdepartment_id=90;3-21Copyright©OracleCorporation,2001.Allrightsreserved.DateFunctionsNumberofmonthsbetweentwodatesMONTHS_BETWEENADD_MONTHSNEXT_DAYLAST_DAYROUNDTRUNCAddcalendarmonthstodateNextdayofthedatespecifiedLastdayofthemonthRounddateTruncatedateFunctionDescription3-22Copyright©OracleCorporation,2001.Allrightsreserved.•MONTHS_BETWEEN('01-SEP-95','11-JAN-94')UsingDateFunctions•ADD_MONTHS('11-JAN-94',6)•NEXT_DAY('01-SEP-95','FRIDAY')•LAST_DAY('01-FEB-95')19.6774194'11-JUL-94''08-SEP-95''28-FEB-95'3-23Copyright©OracleCorporation,2001.Allrightsreserved.•ROUND(SYSDATE,'MONTH')01-AUG-95•ROUND(SYSDATE,'YEAR')01-JAN-96•TRUNC(SYSDATE,'MONTH')01-JUL-95•TRUNC(SYSDATE,'YEAR')01-JAN-95UsingDateFunctionsAssumeSYSDATE='25-JUL-95':3-24Copyright©OracleCorporation,2001.Allrightsreserved.Practice3,PartOne:OverviewThispracticecoversthefollowingtopics:•Writingaquerythatdisplaysthecurrentdat