Database Processing: Fundamentals, Design, and Implementation (15th Edition) – Complete Solutions Manual

INSTRUCTOR’S MANUAL k



TO ACCOMPANY k




David M. Kroenke | David J. Auer | Scott L. Vandenberg | Robert C. Yoder
k k k k k k k k k k k k k k




40th Anniversary Edition
k k




DATABASE PROCESSING k




Fundamentals, Design, and Implementation
k k k



15th Edition k




Chapter 2 k




Introduction to Structured Query Language
k k k k




PreparedkBy
Scott L. Vandenberg Sien
k k k



a College
k

,Instructor'skManualktokaccompany:

Database Processing: Fundamental, Design, and Implementation (15th Edition)
k k k k k k k




DavidkM.kKroenkek|kDavidkJ.kAuerk|kScottkL.kVandenbergk|kRobertkC.kYoder



Copyrightk©k2019kPearsonkEducation,kInc.

Allkrightskreserved.kNokpartkofkthiskpublicationkmaykbekreproduced,kstoredkinkakretrievalksystem,korktransmitted,
kinkanykformkorkbykanykmeans,kelectronic,kmechanical,kphotocopying,krecording,korkotherwise,kwithoutkthekprio

rkwrittenkpermissionkofkthekpublisher.kPrintedkinkthekUnitedkStateskofkAmerica.

, ChapterkTwok–kIntroductionktokStructuredkQuerykLanguage




 CHAPTER OBJECTIVES
k




 Tokunderstandkthekusekofkextractedkdataksetskinkbusinesskintelligencek(BI)ksystems
 Tokunderstandkthekusekofkad-hockquerieskinkbusinesskintelligencek(BI)ksystems
 TokunderstandkthekhistorykandksignificancekofkStructuredkQuerykLanguagek(SQL)
 TokunderstandkthekSQLkSELECT/FROM/WHEREkframeworkkaskthekbasiskfork
databasekqueries
 TokcreatekSQLkqueriesktokretrievekdatakfromkaksinglektable
 TokcreatekSQLkquerieskthatkusekthekSQLkSELECT,kFROM,kWHERE,kORDERkBY,k
GROUPkBY,kandkHAVINGkclauses
 TokcreatekSQLkquerieskthatkusekthekSQLkDISTINCT,kTOP,kandkTOPkPERCENTkk
eywords
 TokcreatekSQLkquerieskthatkusekthekSQLkcomparisonkoperators,kincludingk
BETWEEN,kLIKE,kIN,kandkISkNULL
 TokcreatekSQLkquerieskthatkusekthekSQLklogicalkoperators,kincludingkAND,kOR,kandk
NOT
 TokcreatekSQLkquerieskthatkusekthekSQLkbuilt-
inkaggregatekfunctionskofkSUM,kCOUNT,kMIN,kMAX,kandkAVGkwithkandkwithou
tkthekSQLkGROUPkBYkclause
 TokcreatekSQLkquerieskthatkretrievekdatakfromkaksinglektablekwhilekrestrictingkthekdatakb
asedkuponkdatakinkanotherktablek(subquery)
 TokcreatekSQLkquerieskthatkretrievekdatakfromkmultiplektableskusingkthekSQLkjoinkandkJ
OINkONkoperations
 TokcreatekSQLkquerieskonkrecursivekrelationships
 TokcreatekSQLkquerieskthatkretrievekdatakfromkmultiplektableskusingkthekSQLkOUTERkJ
OINkoperation
 TokcreatekSQLkquerieskthatkretrievekdatakfromkmultiplektableskusingkSQLksetko
peratorskUNION,kINTERSECT,kandkEXCEPT


 IMPORTANT TEACHING NOTES – READ THIS FIRST!
k k k k k k




Chapterk2k–
kIntroductionktokStructuredkQuerykLanguagekiskintendedktokbektaughtkinkconjunctionkwit

hkthekversionkofkonlinekChapterk10#kavailablekatkhttp://www.pearsonhighered.com/kroenk
e/kthatkcorrespondsktokthekDBMSkthatkyoukarekusingkinkyourkclass.
1. IfkyoukarekusingkMicrosoftkSQLkServerk2017kaskyourkDBMS,kyoukshouldkusekOnli
nekChapterk10Ak–
kManagingkDatabaseskwithkMicrosoftkSQLkServerk2017,kandkcoverkpagesk10A-

1kthroughk10A-58ktokhelpkyourkstudentskgetksetkupkforkthekSQLkworkkinkChapterk2.



Pagek2-3
Copyrightk©k2019kPearsonkEducation,kInc.

, ChapterkTwok–kIntroductionktokStructuredkQuerykLanguage


2. IfkyoukarekusingkOraclekDatabasek12ckReleasek2korkOraclekDatabasekXEkaskyo
urkDBMS,kyoukshouldkusekOnlinekChapterk10Bk–
kManagingkDatabaseskwithkOraclekDatabase,kandkcoverkpagesk10B-

1kthroughk10B-
55ktokhelpkyourkstudentskgetksetkupkforkthekSQLkworkkinkChapterk2.
3. IfkyoukarekusingkMySQLk5.7kaskyourkDBMS,kyoukshouldkusekOnlinekChapterk10Ck–
kManagingkDatabaseskwithkMySQLk5.7,kandkcoverkpagesk10C-1kthrough

10C-35ktokhelpkyourkstudentskgetksetkupkforkthekSQLkworkkinkChapterk2.
4. Thesekpageskcoverkhowktokbuildkakdatabasekfromkexistingk*.sqlkscripts,kandkthe
*.sqlkscriptskforkthekCapekCoddkdatabasekusedkinkChapterk2karekincludedkinkthe
studentkdatakfileskavailablekatkhttp://www.pearsonhighered.com/kroenke/.


 ERRATA
Therekareknokknownkerrorskatkthisktime.kAnykerrorskthatkarekdiscoveredkinkthekfuturekwillkbek
reportedkandkcorrectedkinkthekOnlinekDBPke15kErratakdocument,kwhichkwillkbekavailablekat
khttp://www.pearsonhighered.com/kroenke.




 TEACHING SUGGESTIONS
k




 Databasekfilesktokillustratekthekexampleskinkthekchapterkandksolutionkdatabasekfil
eskforkyourkusekarekavailablekinkthekInstructor’skResourcekCenterkonkthektext’skW
ebksitek(www.pearsonhighered.com/kroenke).
 ThekbestkwaykforkstudentsktokunderstandkSQLkiskbykusingkit.k Havekyourkstudentskw
orkkthroughkthekReviewkQuestions,kExercises,kthekMarcia’skDrykCleaningkCasekQ
uestions,kandkthekQueenkAnnekCuriositykShopkorkMorgankImportingkProjectkQuest
ionskinkankactualkdatabase.k StudentskcankcreatekdatabaseskinkMicrosoftkAccesskw
ithkbasicktables,krelationships,kandkdatakfromkthekmaterialkinkthekbook.kSQLkscriptsk
forkMicrosoftkSQLkServer,kOraclekDatabase,kandkMySQLkversionskofkCapekCodd,k
MDC,kQACS,kandkMIkarekavailablekinkthekInstructor’skResourcekCenterkonkthektext’
skWebksitek(www.pearsonhighered.com/kroenke).k AnkAccesskversionkofkWPCkisk
alsokavailablekthere.
 MicrosoftkAccesskdatabasekfileskforkCapekCodd,ktogetherkwithkSQLkscriptskforkMi
crosoftkSQLkServer,kOraclekDatabase,kandkMySQLkversionskofkCapekCodd,kMD
C,kQACS,kandkMIkarekavailablekforkstudentkusekinkthekStudentkResourceskonkthekt
ext’skWebksitek(www.pearsonhighered.com/kroenke).
 ThekSQLkprocessorskinkthekvariouskDBMSskarekverykfussykaboutkcharacterksetskus
edkforkSQLkstatements.k TheykwantktokseekplainkASCIIktext,knotkfancykfonts.kThiskisk
particularlyktruekofktheksinglekquotationk(k'k)kusedktokdesignatekcharacterkstrings,kbut
kwe’vekalsokhadkproblemskwithkthekminusksign.k Ifkyourkstudentskarekhavingkproblem

skgettingkak“properlykstructuredkSQLkstatement”ktokrun,klookkcloselykforkthisktypekofkp
roblem.k Itkoccurskmostkfrequentlykwhenkcopying/pastingkakquerykfromkakwordkproce
ssorkintokakquerykwindow.
 TherekiskakusefulkteachingktechniquekwhichkwillkallowkyouktokdemonstratekthekSQLkq
uerieskinkthektextkusingkMicrosoftkSQLkServerkifkyoukhavekitkavailable.



Pagek2-4
Copyrightk©k2019kPearsonkEducation,kInc.