资源描述:
《数据库作业答疑》由会员上传分享,免费在线阅读,更多相关内容在行业资料-天天文库。
1、数据库作业答疑向小岩刘沾沾2006年5月第一次作业P181.3、1.5、1.7P362.4、2.5、2.61.3参见课本P191.5参见课本P91.7参见课本P202.4参见课本P292.5参见课本P27如果用户和用户的应用程序能相对于数据库物理结构的改变而保持不变,则实现了物理独立性;如果用户和用户的应用程序能相对于数据库逻辑结构的改变而保持不变,则实现了逻辑独立性。2.6参见课本P30“数据字典”相关内容第二次作业P523.2、3.33.3见P.54参考答案3.2TABLETABNAMECOLCOUNTROWCOUNTTABLE35COLUMN217S45P56SP312第二次作业
2、TABNAMECOLNAMETABLETABNAMETABLECOLCOUNTTABLEROWCOUNTCOLUMNTABNAMECOLUMNCOLNAMESS#SSNAMESSTATUSSCITYPP#PPNAMEPCOLORPWEIGHTPCITYSPS#SPP#SPQTYCOLUMN第三次作业讲义Chapter4SQL(1)第90面题目:Writethefollowingqueries,basedonthefollowingdatabaseexample:Movie(title,year,length,inColor,studioName,producerC#)StarsIn(m
3、ovieTitle,movieYear,strName)MovieStar(name,address,gender,birthdate)MovieExec(name,address,cert#,netWorth)Studio(name,address,presC#)Classes(class,type,country,numGuns,bore,displacement)Ships(name,class,launched)Battles(name,date)Outcomes(ship,battle,result)第三次作业InSQL.1.FindSandraBullock’sbirthd
4、ate2.Findallexecutivesworthatleast$10,000,0003.FindallthestarswhoeitheraremaleorliveinMalibu4.WhichstarsappearedinmoviesproducedbyMGMin1995?5.WhoisthepresidentofMGMstudio?6.Findthecountrieswhoseshipshadthelargestnumberofguns.7.Findthenamesoftheshipwith16-inchbore.8.Findtheaveragenumberofgunsofba
5、ttleshipclasses.第三次作业1.selectbirthdatefromMovieStarwherename=‘SandraBullock’;2.selectnamefromMovieExecwherenetWorth>=10000000;3.selectnamefromMovieStarwheregender=‘male’oraddress=‘Malibu’;4.selectdistinctStarsIn.starNamefromStarsIn,MoviewhereStarsIn.movieTitle=Movie.titleandMovie.year=1995andMov
6、ie.studioNmae=‘MGM’;第三次作业5.selectMovieExec.namefromMovieExec,StudiowhereMovieExec.cert#=Studio.presC#andStudio.name=‘MGM’;6.selectcountryfrom(selectcountry,sum(numGuns)sumGunsfromClassesgroupbycountry)wheresumGuns=max(sumGuns);另一种方式:selectcountryfromClasseswherenumGuns=max(numGuns);7.selectShips
7、.namefromShips,ClasseswhereShips.class=Classes.classandClasses.bore=16;第三次作业8.selectavg(numGuns)fromClasseswheretype=‘battleship’;第四次作业P674.5见教材P73补充题:讲义Chapter4SQL(2)functionofSQL(oracle)P34第3题Findalldepartments’namewhichha