INSTRUCTOR’S MANUAL a
TO ACCOMPANYa
David M. Kroenke | David J. Auer | Scott L. Vandenberg | Robert C. Yoder
a a a a a a a a a a a a a a
40th Anniversary Edition
a a
DATABASE PROCESSING a
Fundamentals, Design, and Implementation
a a a
15th Edition a
Chapter 2 a
Introduction to Structured Query Language
a a a a
PreparedaBy
Scott L. Vandenberg Sien
a a a
a College
a
,Instructor'saManualatoaaccompany:
Database Processing: Fundamental, Design, and Implementation (15th Edition)
a a a a a a a
DavidaM.aKroenkea|aDavidaJ.aAuera|aScottaL.aVandenberga|aRobertaC.aYoder
Copyrighta©a2019aPearsonaEducation,aInc.
Allarightsareserved.aNoapartaofathisapublicationamayabeareproduced,astoredainaaaretrievalasystem,aoratransmitted
,ainaanyaformaorabyaanyameans,aelectronic,amechanical,aphotocopying,arecording,aoraotherwise,awithoutatheapri
orawrittenapermissionaofatheapublisher.aPrintedainatheaUnitedaStatesaofaAmerica.
, ChapteraTwoa–aIntroductionatoaStructuredaQueryaLanguage
CHAPTER OBJECTIVES
a
Toaunderstandatheauseaofaextractedadataasetsainabusinessaintelligencea(BI)asystems
Toaunderstandatheauseaofaad-hocaqueriesainabusinessaintelligencea(BI)asystems
ToaunderstandatheahistoryaandasignificanceaofaStructuredaQueryaLanguagea(SQL)
ToaunderstandatheaSQLaSELECT/FROM/WHEREaframeworkaasatheabasisafora
databaseaqueries
ToacreateaSQLaqueriesatoaretrieveadataafromaaasingleatable
ToacreateaSQLaqueriesathatauseatheaSQLaSELECT,aFROM,aWHERE,aORDERaBY,a
GROUPaBY,aandaHAVINGaclauses
ToacreateaSQLaqueriesathatauseatheaSQLaDISTINCT,aTOP,aandaTOPaPERCENTa
keywords
ToacreateaSQLaqueriesathatauseatheaSQLacomparisonaoperators,aincludinga
BETWEEN,aLIKE,aIN,aandaISaNULL
ToacreateaSQLaqueriesathatauseatheaSQLalogicalaoperators,aincludingaAND,aOR,aanda
NOT
ToacreateaSQLaqueriesathatauseatheaSQLabuilt-
inaaggregateafunctionsaofaSUM,aCOUNT,aMIN,aMAX,aandaAVGawithaandawitho
utatheaSQLaGROUPaBYaclause
ToacreateaSQLaqueriesathataretrieveadataafromaaasingleatableawhilearestrictingatheadataa
basedauponadataainaanotheratablea(subquery)
ToacreateaSQLaqueriesathataretrieveadataafromamultipleatablesausingatheaSQLajoinaanda
JOINaONaoperations
ToacreateaSQLaqueriesaonarecursivearelationships
ToacreateaSQLaqueriesathataretrieveadataafromamultipleatablesausingatheaSQLaOUTERa
JOINaoperation
ToacreateaSQLaqueriesathataretrieveadataafromamultipleatablesausingaSQLaseta
operatorsaUNION,aINTERSECT,aandaEXCEPT
IMPORTANT TEACHING NOTES – READ THIS FIRST!
a a a a a a
Chaptera2a–
aIntroductionatoaStructuredaQueryaLanguageaisaintendedatoabeataughtainaconjunctionawi
thatheaversionaofaonlineaChaptera10#aavailableaatahttp://www.pearsonhighered.com/kroen
ke/athatacorrespondsatoatheaDBMSathatayouaareausingainayouraclass.
1. IfayouaareausingaMicrosoftaSQLaServera2017aasayouraDBMS,ayouashouldauseaOnli
neaChaptera10Aa–
aManagingaDatabasesawithaMicrosoftaSQLaServera2017,aandacoverapagesa10A-
1athrougha10A-58atoahelpayourastudentsagetasetaupaforatheaSQLaworkainaChaptera2.
Pagea2-3
Copyrighta©a2019aPearsonaEducation,aInc.
, ChapteraTwoa–aIntroductionatoaStructuredaQueryaLanguage
2. IfayouaareausingaOracleaDatabasea12caReleasea2aoraOracleaDatabaseaXEaasayo
uraDBMS,ayouashouldauseaOnlineaChaptera10Ba–
aManagingaDatabasesawithaOracleaDatabase,aandacoverapagesa10B-
1athrougha10B-
55atoahelpayourastudentsagetasetaupaforatheaSQLaworkainaChaptera2.
3. IfayouaareausingaMySQLa5.7aasayouraDBMS,ayouashouldauseaOnlineaChaptera10Ca
–aManagingaDatabasesawithaMySQLa5.7,aandacoverapagesa10C-1athrough
10C-35atoahelpayourastudentsagetasetaupaforatheaSQLaworkainaChaptera2.
4. Theseapagesacoverahowatoabuildaaadatabaseafromaexistinga*.sqlascripts,aandathe
*.sqlascriptsaforatheaCapeaCoddadatabaseausedainaChaptera2aareaincludedainathe
studentadataafilesaavailableaatahttp://www.pearsonhighered.com/kroenke/.
ERRATA
Thereaareanoaknownaerrorsaatathisatime.aAnyaerrorsathataareadiscoveredainatheafutureawillab
eareportedaandacorrectedainatheaOnlineaDBPae15aErrataadocument,awhichawillabeaavailabl
eaatahttp://www.pearsonhighered.com/kroenke.
TEACHING SUGGESTIONS
a
Databaseafilesatoaillustrateatheaexamplesainatheachapteraandasolutionadatabaseafi
lesaforayourauseaareaavailableainatheaInstructor’saResourceaCenteraonatheatext’sa
Webasitea(www.pearsonhighered.com/kroenke).
TheabestawayaforastudentsatoaunderstandaSQLaisabyausingait.a Haveayourastudentsa
workathroughatheaReviewaQuestions,aExercises,atheaMarcia’saDryaCleaningaCasea
Questions,aandatheaQueenaAnneaCuriosityaShopaoraMorganaImportingaProjectaQu
estionsainaanaactualadatabase.a StudentsacanacreateadatabasesainaMicrosoftaAcce
ssawithabasicatables,arelationships,aandadataafromatheamaterialainatheabook.aSQLas
criptsaforaMicrosoftaSQLaServer,aOracleaDatabase,aandaMySQLaversionsaofaCapea
Codd,aMDC,aQACS,aandaMIaareaavailableainatheaInstructor’saResourceaCenteraona
theatext’saWebasitea(www.pearsonhighered.com/kroenke).a AnaAccessaversionaofa
WPCaisaalsoaavailableathere.
MicrosoftaAccessadatabaseafilesaforaCapeaCodd,atogetherawithaSQLascriptsaforaMi
crosoftaSQLaServer,aOracleaDatabase,aandaMySQLaversionsaofaCapeaCodd,aMD
C,aQACS,aandaMIaareaavailableaforastudentauseainatheaStudentaResourcesaonathea
text’saWebasitea(www.pearsonhighered.com/kroenke).
TheaSQLaprocessorsainatheavariousaDBMSsaareaveryafussyaaboutacharacterasetsaus
edaforaSQLastatements.a TheyawantatoaseeaplainaASCIIatext,anotafancyafonts.aThisais
aparticularlyatrueaof atheasingleaquotationa(a'a
)ausedatoadesignateacharacterastrings,ab
utawe’veaalsoahadaproblemsawithatheaminusasign.a Ifayourastudentsaareahavingaprobl
emsagettingaaa“properlyastructuredaSQLastatement”atoarun,alookacloselyaforathisatype
aof aproblem.a Itaoccursamostafrequentlyawhenacopying/pastingaaaqueryafromaaaworda
processoraintoaaaqueryawindow.
ThereaisaaausefulateachingatechniqueawhichawillaallowayouatoademonstrateatheaSQLa
queriesainatheatextausingaMicrosoftaSQLaServeraifayouahaveaitaavailable.
Pagea2-4
Copyrighta©a2019aPearsonaEducation,aInc.
TO ACCOMPANYa
David M. Kroenke | David J. Auer | Scott L. Vandenberg | Robert C. Yoder
a a a a a a a a a a a a a a
40th Anniversary Edition
a a
DATABASE PROCESSING a
Fundamentals, Design, and Implementation
a a a
15th Edition a
Chapter 2 a
Introduction to Structured Query Language
a a a a
PreparedaBy
Scott L. Vandenberg Sien
a a a
a College
a
,Instructor'saManualatoaaccompany:
Database Processing: Fundamental, Design, and Implementation (15th Edition)
a a a a a a a
DavidaM.aKroenkea|aDavidaJ.aAuera|aScottaL.aVandenberga|aRobertaC.aYoder
Copyrighta©a2019aPearsonaEducation,aInc.
Allarightsareserved.aNoapartaofathisapublicationamayabeareproduced,astoredainaaaretrievalasystem,aoratransmitted
,ainaanyaformaorabyaanyameans,aelectronic,amechanical,aphotocopying,arecording,aoraotherwise,awithoutatheapri
orawrittenapermissionaofatheapublisher.aPrintedainatheaUnitedaStatesaofaAmerica.
, ChapteraTwoa–aIntroductionatoaStructuredaQueryaLanguage
CHAPTER OBJECTIVES
a
Toaunderstandatheauseaofaextractedadataasetsainabusinessaintelligencea(BI)asystems
Toaunderstandatheauseaofaad-hocaqueriesainabusinessaintelligencea(BI)asystems
ToaunderstandatheahistoryaandasignificanceaofaStructuredaQueryaLanguagea(SQL)
ToaunderstandatheaSQLaSELECT/FROM/WHEREaframeworkaasatheabasisafora
databaseaqueries
ToacreateaSQLaqueriesatoaretrieveadataafromaaasingleatable
ToacreateaSQLaqueriesathatauseatheaSQLaSELECT,aFROM,aWHERE,aORDERaBY,a
GROUPaBY,aandaHAVINGaclauses
ToacreateaSQLaqueriesathatauseatheaSQLaDISTINCT,aTOP,aandaTOPaPERCENTa
keywords
ToacreateaSQLaqueriesathatauseatheaSQLacomparisonaoperators,aincludinga
BETWEEN,aLIKE,aIN,aandaISaNULL
ToacreateaSQLaqueriesathatauseatheaSQLalogicalaoperators,aincludingaAND,aOR,aanda
NOT
ToacreateaSQLaqueriesathatauseatheaSQLabuilt-
inaaggregateafunctionsaofaSUM,aCOUNT,aMIN,aMAX,aandaAVGawithaandawitho
utatheaSQLaGROUPaBYaclause
ToacreateaSQLaqueriesathataretrieveadataafromaaasingleatableawhilearestrictingatheadataa
basedauponadataainaanotheratablea(subquery)
ToacreateaSQLaqueriesathataretrieveadataafromamultipleatablesausingatheaSQLajoinaanda
JOINaONaoperations
ToacreateaSQLaqueriesaonarecursivearelationships
ToacreateaSQLaqueriesathataretrieveadataafromamultipleatablesausingatheaSQLaOUTERa
JOINaoperation
ToacreateaSQLaqueriesathataretrieveadataafromamultipleatablesausingaSQLaseta
operatorsaUNION,aINTERSECT,aandaEXCEPT
IMPORTANT TEACHING NOTES – READ THIS FIRST!
a a a a a a
Chaptera2a–
aIntroductionatoaStructuredaQueryaLanguageaisaintendedatoabeataughtainaconjunctionawi
thatheaversionaofaonlineaChaptera10#aavailableaatahttp://www.pearsonhighered.com/kroen
ke/athatacorrespondsatoatheaDBMSathatayouaareausingainayouraclass.
1. IfayouaareausingaMicrosoftaSQLaServera2017aasayouraDBMS,ayouashouldauseaOnli
neaChaptera10Aa–
aManagingaDatabasesawithaMicrosoftaSQLaServera2017,aandacoverapagesa10A-
1athrougha10A-58atoahelpayourastudentsagetasetaupaforatheaSQLaworkainaChaptera2.
Pagea2-3
Copyrighta©a2019aPearsonaEducation,aInc.
, ChapteraTwoa–aIntroductionatoaStructuredaQueryaLanguage
2. IfayouaareausingaOracleaDatabasea12caReleasea2aoraOracleaDatabaseaXEaasayo
uraDBMS,ayouashouldauseaOnlineaChaptera10Ba–
aManagingaDatabasesawithaOracleaDatabase,aandacoverapagesa10B-
1athrougha10B-
55atoahelpayourastudentsagetasetaupaforatheaSQLaworkainaChaptera2.
3. IfayouaareausingaMySQLa5.7aasayouraDBMS,ayouashouldauseaOnlineaChaptera10Ca
–aManagingaDatabasesawithaMySQLa5.7,aandacoverapagesa10C-1athrough
10C-35atoahelpayourastudentsagetasetaupaforatheaSQLaworkainaChaptera2.
4. Theseapagesacoverahowatoabuildaaadatabaseafromaexistinga*.sqlascripts,aandathe
*.sqlascriptsaforatheaCapeaCoddadatabaseausedainaChaptera2aareaincludedainathe
studentadataafilesaavailableaatahttp://www.pearsonhighered.com/kroenke/.
ERRATA
Thereaareanoaknownaerrorsaatathisatime.aAnyaerrorsathataareadiscoveredainatheafutureawillab
eareportedaandacorrectedainatheaOnlineaDBPae15aErrataadocument,awhichawillabeaavailabl
eaatahttp://www.pearsonhighered.com/kroenke.
TEACHING SUGGESTIONS
a
Databaseafilesatoaillustrateatheaexamplesainatheachapteraandasolutionadatabaseafi
lesaforayourauseaareaavailableainatheaInstructor’saResourceaCenteraonatheatext’sa
Webasitea(www.pearsonhighered.com/kroenke).
TheabestawayaforastudentsatoaunderstandaSQLaisabyausingait.a Haveayourastudentsa
workathroughatheaReviewaQuestions,aExercises,atheaMarcia’saDryaCleaningaCasea
Questions,aandatheaQueenaAnneaCuriosityaShopaoraMorganaImportingaProjectaQu
estionsainaanaactualadatabase.a StudentsacanacreateadatabasesainaMicrosoftaAcce
ssawithabasicatables,arelationships,aandadataafromatheamaterialainatheabook.aSQLas
criptsaforaMicrosoftaSQLaServer,aOracleaDatabase,aandaMySQLaversionsaofaCapea
Codd,aMDC,aQACS,aandaMIaareaavailableainatheaInstructor’saResourceaCenteraona
theatext’saWebasitea(www.pearsonhighered.com/kroenke).a AnaAccessaversionaofa
WPCaisaalsoaavailableathere.
MicrosoftaAccessadatabaseafilesaforaCapeaCodd,atogetherawithaSQLascriptsaforaMi
crosoftaSQLaServer,aOracleaDatabase,aandaMySQLaversionsaofaCapeaCodd,aMD
C,aQACS,aandaMIaareaavailableaforastudentauseainatheaStudentaResourcesaonathea
text’saWebasitea(www.pearsonhighered.com/kroenke).
TheaSQLaprocessorsainatheavariousaDBMSsaareaveryafussyaaboutacharacterasetsaus
edaforaSQLastatements.a TheyawantatoaseeaplainaASCIIatext,anotafancyafonts.aThisais
aparticularlyatrueaof atheasingleaquotationa(a'a
)ausedatoadesignateacharacterastrings,ab
utawe’veaalsoahadaproblemsawithatheaminusasign.a Ifayourastudentsaareahavingaprobl
emsagettingaaa“properlyastructuredaSQLastatement”atoarun,alookacloselyaforathisatype
aof aproblem.a Itaoccursamostafrequentlyawhenacopying/pastingaaaqueryafromaaaworda
processoraintoaaaqueryawindow.
ThereaisaaausefulateachingatechniqueawhichawillaallowayouatoademonstrateatheaSQLa
queriesainatheatextausingaMicrosoftaSQLaServeraifayouahaveaitaavailable.
Pagea2-4
Copyrighta©a2019aPearsonaEducation,aInc.
