CS669 Lab1Explanation PDF

Preview

Summary

Download CS669 Lab1Explanation PDF

Description

MET CS 669 Database Design and Implementation for Business Lab 1 Explanation: SQL From Scratch

 

DocumentOverview

 Thepurposeofthisdocumentistoexplain,step‐by‐step,howtocompleteLab1.Theexplanationscoverthe conceptsyouneedlearn,andprovideplentyofexamples.Youcanthinkofthisdocumentasanassistantthat isheretohelpyoucompletethelab.  Thisdocumentisorganizedbythelab’sstepnumbers.So,ifforexample,youareworkingonstep1,you wouldreadthroughthestep1sectioninthisdocumenttolearnhowtocompletestep1.Thesamegoesforall ofthesteps.Readeachstep’sexplanationherefirst,thencompletethestep.   

Page1of39 Copyright2012,2013,2015,2018‐2019BostonUniversity.AllRightsReserved.



Table of Contents SectionOne–AbsoluteFundamentals...................................................................................................................3 STEP1..................................................................................................................................................................3 STEP2..................................................................................................................................................................6 STEP3..................................................................................................................................................................8 STEP4..................................................................................................................................................................9 STEP5................................................................................................................................................................12 STEP6................................................................................................................................................................14 STEP7................................................................................................................................................................17 SectionTwo–MorePreciseDataHandling..........................................................................................................18 STEP8................................................................................................................................................................18 STEP9................................................................................................................................................................21 STEP10..............................................................................................................................................................27 STEP11..............................................................................................................................................................28 STEP12..............................................................................................................................................................32 STEP13..............................................................................................................................................................33 STEP14..............................................................................................................................................................35 STEP15..............................................................................................................................................................37 STEP16..............................................................................................................................................................38   



Page2of39

SectionOne–AbsoluteFundamentals 

STEP 1

CreatetheMovietable.Asareminder,makesuretofollowalongintheLab1 Explanationsdocumentasitshowsyouhowtocreatetablesandcompletetheother steps.

 Toshowyouhowtocreateatable,we’llshowyouanexampleofcreatingasimplePersontable,whichwill lookasfollows. 

  ThisPersontablehasthreecolumns‐‐person_id,first_name,andlast_name‐‐withdatatypesspecifiedinthe diagramabove.We’llusethepersontablethroughthefirstsectiontoillustratethevariouscommands.  First,let’slookatthecommandweusetocreatethePersontable.  CREATE TABLE Person( person_id DECIMAL(12), first_name VARCHAR(256), last_name VARCHAR(256) );   Thefirstthingtonoteisthatthespaces,parentheses,andnewlinesareallimportant.TheseassisttheSQL processorindifferentiatingonewordfromthenext.Removingthesecancauseerrors.  Next,letuslookatandunderstandvariousaspectsofthiscommand.ThecommandbeginswiththeSQL keywordsCREATETABLE.ASQLkeywordisawordthatthedesignersoftheSQLlanguagechosetocarry specialmeaningwithinthelanguage.ThetwokeywordsCREATETABLEtogetherindicatetotheSQLcompiler thatwearebeginningacommandtocreateatable.Thenextword,"Person",isthenameofthetableweare creating.Thisisnotakeyword,butsimplyanidentifierofourchoosing.Wecouldhavechosenanalternative identifier,limitedonlytowhatisrelevantandourimagination.SQLcompilersknowthatbydefinition,the identifierfollowingtheCREATETABLEkeywordsdefinesthenameofthetable.Onlycertaincharactersare legalinidentifiers,andthelegalcharactersdependupontheparticulardatabaseweareusing.Probablythe mostcommoncharactersusedareletters,numbers,andtheunderscore.  Nextletusexaminetheclausethatbeginsandendswithparentheses.Theleftparenthesisbeginsthe specificationofthecolumnsandconstraintsforthetable,whiletherightparenthesisclosesthesame specification.Wewilllearnaboutconstraintsnextweek.Fornow,wefocusonthecolumnspecifications.The firstthingyoumaynoticeisthateachcolumnspecificationisseparatedbyacomma,andthelastspecification Page3of39



inthelisthasnocomma.Wewouldputfewerspecificationsifwehadfewercolumns,andmorespecifications formorecolumns.  Thenextthingyoumaynoticeisthateachcolumnspecificationhastwowords.Althougheachcolumn specificationmayhavemorethantwowordsthatdefineadditionalaspectsofthecolumn,itmusthaveata minimumboththecolumn'snameandthecolumn'sdatatype.Justaswiththetablename,thecolumnname identiferisnotaSQLkeyword,andwecanchooseavarietyofnames.Agoodidentifierdescribeswellwhatit represents.Forourfirstcolumn,wechose“person_id”.ThesecondwordinacolumnspecificationisaSQL keywordindicatingthecolumn'sdatatype.Forourfirstcolumn,thedatatypeis“DECIMAL(12)”.  Letusbrieflydiscussthetwodatatypesillustratedinourexample.Adatatyperestrictsthesetoflegalvalues foracolumntoaparticulardomain.TheDECIMALdatatypeinourexampleindicatesthatwearerestricting thevaluesforperson_idtonumbers.The“(12)”aftertheDECIMALkeywordindicatesthatwearerestricting thenumbersfurthersothattherearenodecimalpoints,andamaximumof12digits.Ifwehadput“(12,2)”, forexample,wewouldallowtwodecimalpointsforamaximumof12digits.  TheVARCHARdatatypeforthefirst_namecolumnindicatesthatwearerestrictingthatcolumn’svaluesto charactersequences,forexample,“abcd”.The“(256)”meansthatthemaximumnumberofbytesis256. SomeRDBMSallowyoutochangethemeaningofthisnumberfrombytestocharacters,sothatthe256 wouldmean256characters,regardlessofthenumberofbytespercharacter.  InmostmodernRDBMS,bothspacesandnewlinesbetweenSQLkeywordsarecategorizedsimplyas "whitespace",andaretreatedidenticallybytheDBMS.Thisabstractionmeansthatonecouldusespacesin lieuofnewlines,therebycontainingtheSQLcommandtoasingleline.However,acommonconvention,as illustratedabove,usesnewlinestoincreasethereadabilityandclarityoftheSQLcommand. InmostmodernRDBMSincludingOracle,MicrosoftSQLServerandPostgreSQL,SQLkeywordsarenotcase‐ sensitive,meaningthatthechoicetouseanuppercaseorlowercasecharacterdoesnotmattertotheDBMS. Acommonconvention,asillustratedabove,istocapitalizeSQLkeywords,capitalizethefirstletterofeach wordforthetablename(knownascamelcase),anduselowercaseforotheritems.  Belowarescreenshotsofthiscommandbeingexecutedinthethreesupporteddatabases.  OracleSQLDeveloper MicrosoftSQLServer pgAdmin

 NoticethatthescreenshotscontaintheSQLcommandandtheresultoftheSQLcommand,andonly capturestherelevantportionofthescreen.Youshoulddothesame.  Page4of39

OnedatatypetheMovietableincludes,butthePersontabledoesnot,isDATE.TheDATEdatatypeletsus trackcalendardates,asitsnamesuggests.  

Page5of39

STEP 2

Insertthefirstrowwherethetitleis“Furious7”,thegenreis“ActionFilm”,therelease dateis4/1/2015,andthepriceis$9.94. 

 Addingdatatoatableismoretechnicallynamedan“insert”inSQL.FollowingwithourPersonexample,here isthecommandtoinsertarowwithvaluesperson_id=1,first_name=John,andlast_name=Smithby executingthefollowingcommand.  INSERT INTO Person (person_id, first_name, last_name) VALUES (1, 'John', 'Smith');   Thecommaseparatedlistonthefirstlineindicatesthenamesofthecolumns,andthecommaseparatedlist onthesecondlineindicatesthevaluestoinsertintothosecolumns,respectively.Thevalue1isinsertedinto totheperson_idcolumn,thevalue“John”isinsertedintothefirst_namecolumn,andthevalue“Smith”is insertedintothelast_namecolumn.Thoughitispossibletoomitthefirstcommaseparatedlistbyinserting thevaluesintheordertheyexistinthedatabase,itisnotrecommendedtodoso.ProductionstrengthSQL insertionsspecifythecolumnnamesasillustratedabove,tohelppreventseveralcaseswheredatais unknowinglyinsertedintothewrongcolumn.  Numericvaluescanbetypedwithoutapostrophes.Characterbasedvaluessuchasafirst_nameand last_namemustbequotedwithanapostrophe(').TheseapostrophestelltheRDMBSwherethecharacter sequencebeginsandends.Onereasonapostrophesarenecessaryisthatcharactersequencescancontain spaces,andtheRDBMSneedsawaytoknowwhenaspaceisaseparatorforatokenintheSQLlanguage,and whenaspaceispartofasinglesequenceofcharactersforavalue.Makesuretousetheregularapostrophe ('),andnotthetypographer'sapostrophe(`).  Belowisascreenshotofthecommandandtheresultofitsexecution.  OracleSQL Developer



Page6of39

MicrosoftSQL Server Management Studio  pgAdmin

  SincethepersontabledoesnotincludeaDATEdatatype,hereisthesyntaxofhowyoucanhardcodea particulardate:  CAST(’01‐MAR‐2019’ASDATE)  Thisexamplehardcodes3/1/2019asadate.Thedaynumbercomesfirst,followedbyamonthabbreviation, followedbytheyear.   

Page7of39

STEP 3

Selectallrowsinthetabletoviewtherowyouinserted. 

 It’snottoocomplextoretrieveallrowsfromatable.Thetechnicalwordforretrievingdatais“select”in SQL.Asanexample,wecanselectallrowsfromthePersontablebyexecutingthefollowingcommand.  SELECT * FROM Person;   ThefirstlineinstructstheRDBMStoretrieveallcolumns,becausethe"*"valueindicates"all".Thesecond lineinstructstheRDBMStoretrievefromthePersontable.Ascreenshotofthecommandandtheresultof itsexecutionisbelow.  OracleSQL Developer

MicrosoftSQL Server Management Studio

pgAdmin

  Noticethatweseetherowwejustinsertedintheresultset.  Page8of39

STEP 4

Updatethepriceoftherowinthetableto$10.15,thenselectallrowsinthetableto viewtherowyouupdated. 

 NowyouhaveachancetoupdateexistingdataintheMovietable.Hereisanexampleofhowweupdateall rowsinthePersontable.SincethereisonlyonerowithastheeffectofupdatingthelastnameofJohnSmith from“Smith”to“Glass”.    UPDATE Person  SET last_name = 'Glass';   Thefirstword“UPDATE”indicatestotheRDBMSthatweareupdatingrow(s)inatable.Thesecondword “Person”indicatesthatweareupdatingrow(s)inthePersontable.TheSETkeywordonthesecondlinebegins acomma‐separatedlistofcolumnnamesandtheirnewvalues.Byusingthephrase:  last_name = 'Glass'  weareinstructingtheRDBMStosetthevalueofthelast_namecolumntothevalue“Glass”.Ifwehadinstead usedthephrase:  first_name = 'Jane'  forexample,wewouldbeinstructingtheRDBMStosetthefirst_namevalueto“Jane”.  TheUPDATEcommandaboveupdatesallrowsinthePersontable.Ifthereweretobemorethanonerowin thePersontable,thelast_namevaluesforalloftherowswouldbeupdated.Wewilllearninthenextsection howtolimitupdatestoaspecificroworgroupofrows.  Hereisascreenshotofthecommandtheresultofitsexecution.  OracleSQLDeveloper

Page9of39

MicrosoftSQLServer ManagementStudio

pgAdmin

 Weviewtheresultsofourupdatebyselectingallrowsinthetable.   SELECT *  FROM Person;   Inthescreenshotsbelow,you’llnoticethelastnameisnow“Glass”.  OracleSQLDeveloper

MicrosoftSQLServerManagementStudio



Page10of39

pgAdmin

 



Page11of39

STEP 5

Removeallrowsfromthetable,thenselectallrowsinthetabletoverifythereare norows. 

 Toremoveallrowsfromatable,youusetheDELETEcommand.Hereisanexampleofdoingsowiththe Persontable.    DELETE FROM Person;   TheDELETEFROMkeywordsindicatetotheRDBMSthatwearedeletingoneormorerowsfromatable.The nextword,“Person”,indicatesthatthetableisthePersontable.  ExecutingthecommandabovewilldeleteallrowsinthePersontable.Inourexampleweonlyhaveonerow, buttablesinproductionenvironmentsusuallyhavemanyrows.Wewilllearninthenextsectionhowtolimita deletetoaspecificroworgroupofrows.  Hereisascreenshotofexecutingthiscommand.  OracleSQL Developer

MicrosoftSQL Server Management Studio

Page12of39

pgAdmin

 NoticethateachRDBMSindicatethenumberofrowsthatweredeleted.  Inordertoseethatthetablenowhasnorows,letusagainselectallrowsinthetable.  OracleSQL Developer

MicrosoftSQLServer ManagementStudio

pgAdmin

  Noticethatalthoughthecolumnsstillappearintheresultset,norowsofdataappear.Thisisbecauseallrows havebeenremovedfromthePersontable.  

Page13of39

STEP 6

DroptheMovietable,thenselectallrowsinthetabletoverifythetabledoesn’t exist. 

 ThecommandforgettingridofatableisDROP.TodropthePersontable,wewouldusethiscommand.    DROP TABLE Person;   ThefirsttwokeywordsDROPTABLEinstructtheRDBMStoremoveatable.Thethirdword,“Person”,isthe nameofthetabletobedropped,inthiscase,thePersontable.  AlthoughinthisexercisewearecasuallydroppingthePersontable,inproductionenvironmentscommand shouldbeusedwithextremecare.Allofthedatainthetablewillalsoberemoved,andthedatacannot alwaysberecoveredifyoulaterdecidethatyouwanttokeepthedata.  BelowisascreenshotofdroppingthePersontable.  OracleSQL Developer

MicrosoftSQL Server Management Studio

Page14of39

pgAdmin

 Forgoodmeasure,letusattempttoretrievedatafromthePersontablewejustdeleted.Ofcourse,nowthat thePersontablehasbeendropped,thecommandwillnotbeabletoretrievetheresults.Itishelpfulhowever tobecomeaccustomedtotheerrormessageindicatingthatthetabledoesnotexist.  OracleSQL Developer

 Microsoft SQLServer Management Studio

Page15of39

pgAdmin

  



Page16of39

STEP 7

Explainhowyouwouldusetheerrormessageresultingfromstep#6,inconjunction withtheSELECTcommand,todiagnosetheerror. 

 OracleExplanation TheOracleerrormessageclearlystatesthatthetableorviewbeingreferencedinthecommanddoesnot exist.Thereasonfortheadditionalclause“orview”intheerrormessageisthatwhereveratableisusedina command,aviewcanbeusedaswell.Wewillstudyviewsinfutureweeks.  Todiagnosetheissue,theerrormessageandSQLcommandneedtobeanalyzedtogether.Eitheralonedoes notprovidesufficientinformation.Forexample,inthiscase,theerrormessagedoesnotstatethatitwasthe referenceto“Person”thatwasinvalid.WemustdeducethatbyreviewingourSQLcommand.Theerror messagehasonemorepieceofinformationthatwillhelpusdoso,whichisalinenumberandacolumn number.Inthiscase,theerrormessageisstatingthatthesourceoftheerrorbeginsatline2,column5.Ifwe reviewourcommand,weseethattheword“Person”beginsatline2,column5,andsothatisthesourceof theerror.ThefactthatPersondoesnotexistisexpected,sincewepreviouslydroppedthattable.  MicrosoftSQLServerExplanation TheerrormessagegeneratedbySQLServermaybesomewhatdifficulttointerpretfromarelationalpointof view,becauseitdoesnotusethefamiliarterm“table”intheerrormessage.SQLServerusesthegenericterm “object”todenoteanydurableentitythatitsupports,includingtablesandviews.Thereforewecanintepret thephrase“Invalidobjectname‘Person’”tomeanthatweattemptedtousethename“Person”asa referencetoadurableentityintheSQLcommand,butnodurableentityexistsbythatname.  JustaswiththeOracleerrormessages,weneedtousetheSQLServererrormessage,incombinationwiththe SQLcommand,todiagnosetheissue.Theerrormessagedoesprovidealinenumberindicatingthestartofthe commandwiththeissue.Sinceinthiscaseweonlyhaveonecommand,itstatesthatitbeginsonline1.Ifwe weretobeexecutingmultiplecommands,thelinenumberwouldbehelpful.  Whenweseethiserrormessage,weneedtothink,“Somehwereinthecommandthatbeginsonline1isa referenceto‘Person’thatdoesnotexist.”Wecanthensearchthroughthecommandandfindtheinvalid reference.Inoursimplecase,weonlyhaveonereferenceto“Person”,andweattemptedtoreferenceitasa table,andsoweknowthatthePersontabledoesnotexist.Thisiswhatweexpected,sincewepreviously droppedthePersontable.  PostgreSQLExplanation UsingtheprovidederrormessagealongwiththeSQLcommandweareabletodiagnosetheissue.  TheerrormessagegeneratedinPostgreSQLspecifiesashortdescriptionoftheerrormessagealongwiththe linenumberinthescripttheerrorcorrespondstoandthemorecomplextheSQLcommandthemore beneficialthelinenumberbecomes.Inthisexampletheerrormessagestatesthatthe“person”doesnotexist andpointsustoLine2wherethe“person”objectisreferencedin...