oracle sql reference - cheat sheets

1 C. Michael Eliasz, Sr. oracle data ArchitectReserved WordsBuilt-in FunctionsCommon Clauses/Misc CmdsALTER / DROP COMMANDSCREATE CommandsACCESSABS allocate_extent_clauseALTER CLUSTERCREATE CLUSTERADDACOS constraintsALTER DATABASECREATE CONTEXTALLADD_MONTHS deallocate_unused_clauseALTER DIMENSIONCREATE CONTROLFILEALTERASCII file_specificationALTER FUNCTIONCREATE DATABASEANDASCIISTR logging_clauseALTER INDEXCREATE DATABASE LINKANYASIN parallel_clauseALTER INDEXTYPECREATE DIMENSIONASATAN physical_attributes_clauseALTER JAVACREATE DIRECTORYASCATAN2storage_clauseALTER MATERIALIZED VIEWCREATE FUNCTIONAUDITAVGALTER MATERIALIZED VIEW LOGCREATE INDEXBETWEENBFILENAMEALTER OPERATORCREATE INDEXTYPEBYBIN_TO_NUMALTER OUTLINECREATE JAVACHARBITANDMISCELLANEOUS COMMANDSALTER PACKAGECREATE LIBRARYCHECKCASTANALYZEALTER PROCEDURECREATE MATERIALIZED VIEWCLUSTERCEILASSOCIATE STATISTICSALTER PROFILECREATE MATERIALIZED VIEW LOGCOLUMNCHARTOROWIDAUDITALTER RESOURCE COSTCREATE

2 OPERATORCOMMENTCHRCALLALTER ROLECREATE OUTLINECOMPRESSCOALESCECOMMENTALTER ROLLBACK SEGMENTCREATE PACKAGECONNECTCOMPOSECOMMITALTER SEQUENCECREATE PACKAGE BODYCREATECONCATDELETEALTER SESSIONCREATE PFILECURRENTCONVERTDISASSOCIATE STATISTICSALTER SYSTEMCREATE PROCEDUREDATECORREXPLAIN PLANALTER TABLECREATE PROFILEDECIMALCOSGRANTALTER TABLESPACECREATE ROLEDEFAULTCOSHINSERTALTER TRIGGERCREATE ROLLBACK SEGMENTDELETECOUNTLOCK TABLEALTER TYPECREATE SCHEMADESCCOVAR_POPMERGEALTER USERCREATE SEQUENCEDISTINCTCOVAR_SAMPNOAUDITALTER VIEWCREATE SPFILEDROPCUME_DISTRENAMECREATE SYNONYMELSECURRENT_DATEREVOKECREATE TABLEEXCLUSIVECURRENT_TIMESTAMPROLLBACKD ROP COMMANDSCREATE TABLESPACEEXISTSDBTIMEZONESAVEPOINTDROP CLUSTERCREATE TEMPORARY TABLESPACEFILEDECODESELECTDROP CONTEXTCREATE TRIGGERFLOATDECOMPOSESET CONSTRAINT[S]DROP DATABASE LINKCREATE TYPEFORDENSE_RANKSET ROLEDROP DIMENSIONCREATE TYPE BODYFROMDEPTHSET TRANSACTIONDROP DIRECTORYCREATE USER oracle SQL reference WordsBuilt-in FunctionsCommon Clauses/Misc CmdsALTER / DROP COMMANDSCREATE CommandsGRANTDEREFTRUNCATEDROP FUNCTIONCREATE VIEWGROUPDUMPUPDATEDROP INDEXHAVINGEMPTY_BLOB, _CLOBDROP INDEXTYPEIDENTIFIEDEXISTSNODEDROP JAVAIMMEDIATEEXPDML COMMANDSDROP LIBRARYPSUEDO COLUMNSINEXTRACT (datetime)COMMITDROP MATERIALIZED VIEWCURRVAL and NEXTVALINCREMENTEXTRACT (XML)DELETEDROP MATERIALIZED VIEW LOGLEVELINDEXEXTRACTVALUEINSERTDROP OPERATORROWIDINITIALFIRSTMERGEDROP OUTLINEROWNUMINSERTFIRST_VALUEROLLBACKDR OP PACKAGEXMLDATAINTEGERFLOORSAVEPOINTDROP PROCEDUREINTERSECTFROM_TZSELECTDROP PROFILEINTOGREATESTTRUNCATEDROP ROLEDB OBJECTS (SCHEMA)

3 ISGROUP_IDUPDATEDROP ROLLBACK SEGMENTC lustersLEVELGROUPINGDROP SEQUENCEC onstraintsLIKEGROUPING_IDDROP SYNONYMD atabase linksLOCKHEXTORAWDCL COMMANDSDROP TABLED atabase triggersLONGINITCAPAUDITDROP TABLESPACED imensionsMAXEXTENTSINSTRGRANTDROP TRIGGERE xternal procedure librariesMINUSLAGNOAUDITDROP TYPEI ndex-organized tablesMLSLABELLASTREVOKEDROP TYPE BODYI ndexesMODELAST_DAYDROP USERI ndextypesMODIFYLAST_VALUEDROP VIEWJava classes, resources, source codeNOAUDITLEADDDL COMMANDSM aterialized viewsNOCOMPRESSLEASTsee ALTER commandsMaterialized view logsNOTLENGTHANALYZEDB OBJECTS (NON SCHEMA)Object tablesNOWAITLNASSOCIATE STATISTICSC ontextsObject typesNULLLOCALTIMESTAMPCOMMENTD irectoriesObject viewsNUMBERLOGsee CREATE commandsPFILES & SPFILESO peratorsOFLOWERDISASSOCIATE STATISTICSP rofilesPackagesOFFLINELPADsee DROP commandsRolesSequencesONLTRIMEXPLAIN PLANR ollback segmentsStored functions, stored WordsBuilt-in FunctionsCommon Clauses/Misc CmdsALTER / DROP COMMANDSCREATE CommandsPCTFREEMONTHS_BETWEENPRIORNCHRPR IVILEGESNEW_TIMESQL STANDARDSPUBLICNEXT_DAYANSI StandardsRAWNLS_CHARSET_DECL_LENSET OPERATORSISO StandardsRENAMENLS_CHARSET_IDUNIONFIPS ComplianceRESOURCENLS_CHARSET_NAMEUNION ALLUNICODEREVOKENLS_INITCAPINTERSECTO racle Standards ComplianceROWNLS_LOWERMINUSROWIDNLSSORTA merican National Standards InstituteROWNUMNLS_UPPER11 West 42nd StreetROWSNTILENew York, NY 10036 USASELECTNULLIFJOIN OPERATORST elephone: (212) 642-4900 SESSIONNUMTODSINTERVAL(+) - OUTER JOIN Fax.

4 (212) 398-0023 SETNUMTOYMINTERVAL equijoins (=) - inner join SIZENVL2 RIGHT - right outer Organization for StandardizationSMALLINTPATHLEFT - left outer Rue de Varemb STARTPERCENT_RANKFULL - full or two-sided outer joinCase postale 56 SUCCESSFULPERCENTILE_CONTOUTER - join keyword following RIGHT, LEFT, or FULLCH-1211, Geneva 20, SwitzerlandSYNONYMPERCENTILE_DISC star joinsTelephone: +41 (22) 749-0111 SYSDATEPOWER Fax: +41 (22) 733-3430 TABLERANK / Std 127-2: (Linear Regression) FunctionsUPDATEREPLACEUSERROUND (number)UNICODEVALIDATEROUND (date) complies fully with Unicode WordsBuilt-in FunctionsCommon Clauses/Misc CmdsALTER / DROP COMMANDSCREATE CommandsVIEWRPADWHENEVERRTRIMWHERESESSIO NTIMEZONEWITHSIGNSINSINHSOUNDEXSQRTSTDDE VSTDDEV_POPSTDDEV_SAMPSUBSTRSUMSYS_CONNE CT_BY_PATHSYS_CONTEXTSYS_DBURIGENSYS_EXT RACT_UTCSYS_GUIDSYS_TYPEIDSYS_XMLAGGSYS_ XMLGENSYSDATESYSTIMESTAMPTANTANHTO_CHAR (character)TO_CHAR (datetime)TO_CHAR (number)TO_CLOBTO_DATETO_DSINTERVALTO_LO BTO_MULTI_BYTETO_NCHAR (character)TO_NCHAR (datetime)TO_NCHAR (number) WordsBuilt-in FunctionsCommon Clauses/Misc CmdsALTER / DROP COMMANDSCREATE CommandsTRANSLATETRANSLATE.

5 USINGTREATTRIMTRUNC (number)TRUNC (date)TZ_OFFSETUIDUNISTRUPDATEXMLUPPERUS ERUSERENVVALUEVAR_POPVAR_SAMPVARIANCEVSI ZEWIDTH_BUCKETXMLAGGXMLCOLATTVALXMLCONCA TXMLELEMENTXMLFORESTXMLSEQUENCEXMLTRANSF ORMROUND/TRUNC Date FunctionsUser-Defined