Test Bank for The Psychiatric Interview 4th Edition Daniel J. Carlat – Database Processing Fundamentals Instructor’s Manual – SQL Exercises & Solutions

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.