Oracle-OCP-SQL-9-Creating-and-Managing-Tables

Copyright©OracleCorporation,2001.Allrightsreserved.CreatingandManagingTables9-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Describethemaindatabaseobjects•Createtables•Describethedatatypesthatcanbeusedwhenspecifyingcolumndefinition•Altertabledefinitions•Drop,rename,andtruncatetables9-3Copyright©OracleCorporation,2001.Allrightsreserved.DatabaseObjectsObjectDescriptionTableBasicunitofstorage;composedofrowsandcolumnsViewLogicallyrepresentssubsetsofdatafromoneormoretablesSequenceNumericvaluegeneratorIndexImprovestheperformanceofsomequeriesSynonymGivesalternativenamestoobjects9-4Copyright©OracleCorporation,2001.Allrightsreserved.NamingRulesTablenamesandcolumnnames:•Mustbeginwithaletter•Mustbe1–30characterslong•MustcontainonlyA–Z,a–z,0–9,_,$,and#•Mustnotduplicatethenameofanotherobjectownedbythesameuser•MustnotbeanOracleserverreservedword9-5Copyright©OracleCorporation,2001.Allrightsreserved.TheCREATETABLEStatement•Youmusthave:–CREATETABLEprivilege–Astoragearea•Youspecify:–Tablename–Columnname,columndatatype,andcolumnsizeCREATETABLE[schema.]table(columndatatype[DEFAULTexpr][,...]);9-6Copyright©OracleCorporation,2001.Allrightsreserved.ReferencingAnotherUser’sTables•Tablesbelongingtootherusersarenotintheuser’sschema.•Youshouldusetheowner’snameasaprefixtothosetables.9-7Copyright©OracleCorporation,2001.Allrightsreserved.TheDEFAULTOption•Specifyadefaultvalueforacolumnduringaninsert.•Literalvalues,expressions,orSQLfunctionsarelegalvalues.•Anothercolumn’snameorapseudocolumnareillegalvalues.•Thedefaultdatatypemustmatchthecolumndatatype....hire_dateDATEDEFAULTSYSDATE,...9-8Copyright©OracleCorporation,2001.Allrightsreserved.•Createthetable.•Confirmtablecreation.CreatingTablesCREATETABLEdept(deptnoNUMBER(2),dnameVARCHAR2(14),locVARCHAR2(13));Tablecreated.DESCRIBEdept9-9Copyright©OracleCorporation,2001.Allrightsreserved.TablesintheOracleDatabase•UserTables:–Areacollectionoftablescreatedandmaintainedbytheuser–Containuserinformation•DataDictionary:–IsacollectionoftablescreatedandmaintainedbytheOracleServer–Containdatabaseinformation9-10Copyright©OracleCorporation,2001.Allrightsreserved.SELECTtable_nameFROMuser_tables;SELECT*FROMuser_catalog;QueryingtheDataDictionary•Viewdistinctobjecttypesownedbytheuser.•Viewtables,views,synonyms,andsequencesownedbytheuser.SELECTDISTINCTobject_typeFROMuser_objects;•Seethenamesoftablesownedbytheuser.9-11Copyright©OracleCorporation,2001.Allrightsreserved.DataTypesDataTypeDescriptionVARCHAR2(size)Variable-lengthcharacterdataCHAR(size)Fixed-lengthcharacterdataNUMBER(p,s)Variable-lengthnumericdataDATEDateandtimevaluesLONGVariable-lengthcharacterdataupto2gigabytesCLOBCharacterdataupto4gigabytesRAWandLONGRAWRawbinarydataBLOBBinarydataupto4gigabytesBFILEBinarydatastoredinanexternalfile;upto4gigabytesROWIDA64basenumbersystemrepresentingtheuniqueaddressofarowinitstable.9-13Copyright©OracleCorporation,2001.Allrightsreserved.DateTimeDataTypesDataTypeDescriptionTIMESTAMPDatewithfractionalsecondsINTERVALYEARTOMONTHStoredasanintervalofyearsandmonthsINTERVALDAYTOSECONDStoredasanintervalofdaystohoursminutesandsecondsDatetimeenhancementswithOracle9i:•NewDatetimedatatypeshavebeenintroduced.•Newdatatypestorageisavailable.•Enhancementshavebeenmadetotimezonesandlocaltimezone.9-14Copyright©OracleCorporation,2001.Allrightsreserved.DateTimeDataTypes•TheTIMESTAMPdatatypeisanextensionoftheDATEdatatype.•Itstorestheyear,month,anddayoftheDATEdatatype,plushour,minute,andsecondvaluesaswellasthefractionalsecondvalue.•TheTIMESTAMPdatatypeisspecifiedasfollows:TIMESTAMP[(fractional_seconds_precision)]9-15Copyright©OracleCorporation,2001.Allrightsreserved.TIMESTAMPWITHTIMEZONEDataType•TIMESTAMPWITHTIMEZONEisavariantofTIMESTAMPthatincludesatimezonedisplacementinitsvalue.•Thetimezonedisplacementisthedifference,inhoursandminutes,betweenlocaltimeandUTC.TIMESTAMP[(fractional_seconds_precision)]WITHTIMEZONE9-16Copyright©OracleCorporation,2001.Allrightsreserved.TIMESTAMPWITHLOCALTIMEDataType•TIMESTAMPWITHLOCALTIMEZONEisanothervariantofTIMESTAMPthatincludesatimezonedisplacementinitsvalue.•Datastoredinthedatabaseisnormalizedtothedatabasetimezone.•Thetimezonedisplacementisnotstoredaspartofthecolumndata;Oraclereturnsthedataintheusers'localsessiontimezone.•TIMESTAMPWITHLOCALTIMEZONEdatatypeisspecifiedasfollows:TIMESTAMP[(fractional_seconds_precision)]WITHLOCALTIMEZONE9-17Copyright©OracleCorporation,2001.Allrightsreserved.INTERVALYEARTOMONTHDataType•INTERVALYEARTOMONTHstoresaperiodoftimeusingtheYEARandMONTHdatetimefields.INTERVALYEAR[(year_precision)]TOMONTHINTERVAL'123-2'YEAR(3)TOMONTHIndicatesanintervalof123years,2months.INTERVAL'123'YEAR(3)Indicatesanintervalof123years0months.INTERVAL'300'MONTH(3)Indicatesanintervalof300months.INTERVAL'123'YEARReturnsanerror,becausethedefaultprecisionis2,and'123'has3digits.9-18Copyright©OracleCorporation,2001.Allrightsreserved.INTERVALDAYTOSECONDDataType•INTERVALDAYTOSECONDstoresaperiodoftimeintermsofdays,hours,minutes,andseconds.INTE