數(shù)據(jù)庫(kù)英語(yǔ)綜合測(cè)試題

1、Part3:QuestionsAnswers1.Considerthefollowingrelationalschema:student(studentno_rstnamelastname)book(isbntitleauthspublisheryear)loan(studentnoisbncheckoutdateduration)UseSQLtowritethefollowingqueries:A.Createthetablefthe

2、booktableappropriatedomainrequiredcontraints.B.Changethedatatypeofthecheckoutdateattributeoftheloantabletodate.C.Addaconstraintintotheloantabletomakesuretheloandurationisnomethan180days.D.GrantArvilAmyupdateauthrizationo

3、nthebooktable.2.Whatare6basicoperatsofrelationalalgebra3.Explainhownaturaljoinoperationcanbeaccomplishedbybasicrelationalalgebraoperations4.Explainhowthedivisionoperationcanbeaccomplishedbybasicrelationalalgebraoperation

4、s5.Thedatabaseofaresearchcentercontainsthefollowingthreetablesaboutemployeesprojectsthetimespentbytheemployeesontheprojects.Employee(ssn:intname:stringjobTitle:string)Project(pid:intname:stringspons:stringstartYear:inten

5、dYear:int)WkedOn(ssn:intpid:intyear:intmonth:intnoHours:int).ThetableEmployeelistsalltheemployeesofthecentre.ThetableProjectlistsalltheprojectsofthecentrewiththeirsponsthestartendyearoftheproject.ThetableWkedOnrecdshowma

6、nyhourstheemployeeshavespentonwhichprojectinwhichmonth.Feachtabletheattributesthatmakeuptheprimarykeyareunderlined.Expresseachofthefollowingqueriesinrelationalalgebra.A.Returnthenamesoftheprojectsthatwereactivein2008.B.R

7、eturnthenamesofthoseprogrammerswhoinsomemonthspentmethan60hoursonaprojectsponsedbytheEU.C.ReturnthenamesofthoseprogrammerswhoneverwkedonaprojectsponsedbytheEU.6.ConsidertherelationalschemaofQuestion5.WriteSQLqueriesovert

8、hisschemathatanswerthefollowingquestions.A.Howmanyprojectsthatwereactivein2008weresponsedbytheEUB.Feachprojectyearmonthhowmanyhoursofwkhavebeenspent(Returnonlydatafaprojectyearmonthifsometimehasbeenspent.)C.Howmanyprogra

9、mmersaretherewhohaveexperienceinwkingonaprojectsponsedbytheEU(Notethataprogrammerwhowkedontwomeprojectsshouldbecountedonlyonce.)D.ReturnthenamesoftheprogrammerswhowkedonnomethantwoprojectssponsedbytheEU.(Notethatthisincl

10、udestheprogrammerswhoneverwkedonanyprojectsponsedbytheEU.)E.Whichprogrammer(s)spentthemaximaltotalnumberofhoursonEUprojects10.Considerthefollowingemployeedatabasewheretheprimarykeysareunderlined.Employee(ename:stringstre

11、et:stringcity:string)Wks(employee:stringcompany:stringsalary:real)Company(cname:stringcity:string)Manages(employee:stringmanagername:string)GiveasingleSQLstatementfeachofthefollowingqueries:A.Findthenamesstreetaddressesc

12、itiesofresidenceofallemployeeswhowkf“FirstBankCpation“earnmethan$40000.B.Findthenamesofallemployeesinthedatabasewholiveinthesamecitiesasthecompaniesfwhichtheywk.C.GiveallmanagersofFirstBankCpation“a10percentsalaryraise.D

13、.Findthenamesofallemployeesinthedatabasewhoearnmethananyemployeeof“SmallBankCpation“.E.Assumethatthecompaniesmaybelocatedinseveralcities.FindthenamesofallcompanieslocatedineverycityinwhichSmallBankCpation“islocated.F.Fin

14、dthenameofthecompanythathasthemostemployees.G.Findthosecompanieswhoseemployeesearnahighersalaryonaveragethantheaveragesalaryat“FirstBankCpation“displaythosecompaniesnamesinendingder.11.Considerthefollowing(simplified)rel

15、ationalschemafuniversitystudy:Student(id:integerfamily:stringgiven:stringdegree:stringenrolled:date)Course(id:integercode:stringsession:stringtitle:stringsyllabus:string)Enrolment(student:integercourse:stringmark:realgra