Oracle数据库习题Practice311.docx
- 文档编号:23077048
- 上传时间:2023-04-30
- 格式:DOCX
- 页数:21
- 大小:386.43KB
Oracle数据库习题Practice311.docx
《Oracle数据库习题Practice311.docx》由会员分享,可在线阅读,更多相关《Oracle数据库习题Practice311.docx(21页珍藏版)》请在冰豆网上搜索。
Oracle数据库习题Practice311
Practice3
Part1
1.Writeaquerytodisplaythecurrentdate.LabelthecolumnDate.
2.TheHRdepartmentneedsareporttodisplaytheemployeenumber,lastname,salary,andsalaryincreasedby15.5%(expressedasawholenumber)foreachemployee.LabelthecolumnNewSalary.PlaceyourSQLstatementinatextfilenamedlab_03_02.sql.
3.Runyourqueryinthefilelab_03_02.sql
4.Modifyyourquerylab_03_02.sqltoaddacolumnthatsubtractstheoldsalaryfrom
thenewsalary.LabelthecolumnIncrease.Savethecontentsofthefileaslab_03_04.sql.Runtherevisedquery.
5.Writeaquerythatdisplaysthelastname(withthefirstletteruppercaseandallotherletterslowercase)andthelengthofthelastnameforallemployeeswhosenamestartswiththelettersJ,A,orM.Giveeachcolumnanappropriatelabel.Sorttheresultsbytheemployees’lastnames.
Rewritethequerysothattheuserispromptedtoenteraletterthatstartsthelastname.Forexample,iftheuserentersHwhenpromptedforaletter,thentheoutputshouldshowallemployeeswhoselastnamestartswiththeletterH.
Practice4
Determinethevalidityofthefollowingthreestatements.CircleeitherTrueorFalse.
1.Groupfunctionsworkacrossmanyrowstoproduceoneresultpergroup.
True/False
2.Groupfunctionsincludenullsincalculations.
True/False
3.TheWHEREclauserestrictsrowsbeforeinclusioninagroupcalculation.
True/False
TheHRdepartmentneedsthefollowingreports:
4.Findthehighest,lowest,sum,andaveragesalaryofallemployees.Labelthecolumns
Maximum,Minimum,Sum,andAverage,respectively.Roundyourresultstothenearestwholenumber.PlaceyourSQLstatementinatextfilenamedlab_04_04.sql.
5.Modifythequeryinlab_04_04.sqltodisplaytheminimum,maximum,sum,andaveragesalaryforeachjobtype.Resavelab_04_04.sqlaslab_04_05.sql.Runthestatementinlab_04_05.sql.
6、Writeaquerytodisplaythenumberofpeoplewiththesamejob.
Savethescripttoafilenamedlab_04_06.sql.
7.Determinethenumberofmanagerswithoutlistingthem.LabelthecolumnNumberofManagers.Hint:
UsetheMANAGER_IDcolumntodeterminethenumberofmanagers.
8.Findthedifferencebetweenthehighestandlowestsalaries.LabelthecolumnDIFFERENCE.
Ifyouhavetime,completethefollowingexercises:
9.Createareporttodisplaythemanagernumberandthesalaryofthelowest-paidemployeeforthatmanager.Excludeanyonewhosemanagerisnotknown.Excludeanygroupswheretheminimumsalaryis$6,000orless.Sorttheoutputindescendingorderofsalary.
Ifyouwantanextrachallenge,completethefollowingexercises:
10.Createaquerytodisplaythetotalnumberofemployeesand,ofthattotal,thenumberofemployeeshiredin1995,1996,1997,and1998.Createappropriatecolumnheadings.
11.Createamatrixquerytodisplaythejob,thesalaryforthatjobbasedondepartmentnumber,andthetotalsalaryforthatjob,fordepartments20,50,80,and90,givingeachcolumnanappropriateheading.
Practice5
1、WriteaqueryfortheHRdepartmenttoproducetheaddressesofallthedepartments.UsetheLOCATIONSandCOUNTRIEStables.ShowthelocationID,streetaddress,city,stateorprovince,andcountryintheoutput.UseaNATURALJOINtoproducetheresults.
2、TheHRdepartmentneedsareportofallemployees.Writeaquerytodisplaythelastname,departmentnumber,anddepartmentnameforallemployees.
3、TheHRdepartmentneedsareportofemployeesinToronto.Displaythelastname,job,departmentnumber,anddepartmentnameforallemployeeswhoworkinToronto.
4、Createareporttodisplayemployees’lastnameandemployeenumberalongwiththeirmanager’slastnameandmanagernumber.LabelthecolumnsEmployee,Emp#,Manager,andMgr#,respectively.PlaceyourSQLstatementinatextfilenamedlab_05_04.sql.
5、Modifylab_05_04.sqltodisplayallemployeesincludingKing,whohasnomanager.Ordertheresultsbytheemployeenumber.PlaceyourSQLstatementinatextfilenamedlab_05_05.sql.Runthequeryinlab_05_05.sql.
6、numbers,andalltheemployeeswhoworkinthesamedepartmentasagivenemployee.Giveeachcolumnanappropriatelabel.Savethescripttoafilenamedlab_05_06.sql.
7、TheHRdepartmentneedsareportonjobgradesandsalaries.TofamiliarizeyourselfwiththeJOB_GRADEStable,firstshowthestructureoftheJOB_GRADEStable.Thencreateaquerythatdisplaysthename,job,departmentname,salary,andgradeforallemployees.
Ifyouwantanextrachallenge,completethefollowingexercises:
8.TheHRdepartmentwantstodeterminethenamesofallemployeeswhowerehiredafterDavies.CreateaquerytodisplaythenameandhiredateofanyemployeehiredafteremployeeDavies.
9、TheHRdepartmentneedstofindthenamesandhiredatesforallemployeeswhowerehiredbeforetheirmanagers,alongwiththeirmanagers’namesandhiredates.Savethescripttoafilenamedlab5_09.sql.
Practice6
1.TheHRdepartmentneedsaquerythatpromptstheuserforanemployeelastname.Thequerythendisplaysthelastnameandhiredateofanyemployeeinthesamedepartmentastheemployeewhosenametheysupply(excludingthatemployee).Forexample,iftheuserentersZlotkey,findallemployeeswhoworkwithZlotkey(excludingZlotkey).
2.Createareportthatdisplaystheemployeenumber,lastname,andsalaryofallemployeeswhoearnmorethantheaveragesalary.Sorttheresultsinorderofascendingsalary.
3.Writeaquerythatdisplaystheemployeenumberandlastnameofallemployeeswhoworkinadepartmentwithanyemployeewhoselastnamecontainsau.PlaceyourSQLstatementinatextfilenamedlab_06_03.sql.Runyourquery.
4.TheHRdepartmentneedsareportthatdisplaysthelastname,departmentnumber,andjobIDofallemployeeswhosedepartmentlocationIDis1700.
ModifythequerysothattheuserispromptedforalocationID.Savethistoafilenamedlab_06_04.sql.
5.CreateareportforHRthatdisplaysthelastnameandsalaryofeveryemployeewhoreportstoKing.
6、CreateareportforHRthatdisplaysthedepartmentnumber,lastname,andjobIDforeveryemployeeintheExecutivedepartment.
Ifyouhavetime,completethefollowingexercise:
7.Modifythequeryinlab_06_03.sqltodisplaytheemployeenumber,lastname,andsalaryofallemployeeswhoearnmorethantheaveragesalaryandwhoworkinadepartmentwithanyemployeewhoselastnamecontainsau.Resavelab_06_03.sqlaslab_06_07.sql.Runthestatementinlab_06_07.sql.
Practice7
1.TheHRdepartmentneedsalistofdepartmentIDsfordepartmentsthatdonotcontainthejobIDST_CLERK.Usesetoperatorstocreatethisreport.
2.TheHRdepartmentneedsalistofcountriesthathavenodepartmentslocatedinthem.DisplaythecountryIDandthenameofthecountries.Usesetoperatorstocreatethisreport.
3.Producealistofjobsfordepartments10,50,and20,inthatorder.DisplayjobIDanddepartmentIDusingsetoperators.
4.CreateareportthatliststheemployeeIDsandjobIDsofthoseemployeeswhocurrentlyhaveajobtitlethatisthesameastheirjobtitlewhentheywereinitiallyhiredbythecompany(thatis,theychangedjobsbuthavenowgonebacktodoingtheiroriginaljob).
5.TheHRdepartmentneedsareportwiththefollowingspecifications:
-LastnameanddepartmentIDofalltheemployeesfromtheEMPLOYEEStable,regardlessofwhetherornottheybelongtoadepartment
-DepartmentIDanddepartmentnameofallthedepartmentsfromtheDEPARTMENTStable,regardlessofwhetherornottheyhaveemployeesworkinginthem
Writeacompoundquerytoaccomplishthis.
Practice8
TheHRdepartmentwantsyoutocreateSQLstatementstoinsert,update,anddeleteemployeedata.Asaprototype,youusetheMY_EMPLOYEEtable,beforegivingthestatementstotheHRdepartment.
InsertdataintotheMY_EMPLOYEEtable.
1.Runthestatementinthelab_08_01.sqlscripttobuildtheMY_EMPLOYEEtabletobeusedforthelab.
2.DescribethestructureoftheMY_EMPLOYEEtabletoidentifythecolumnnames.
2.CreateanINSERTstatementtoaddthefirstrowofdatatotheMY_EMPLOYEEtablefromthefollowingsampledata.DonotlistthecolumnsintheINSERTclause.Donotenterallrowsyet.
4.PopulatetheMY_EMPLOYEEtablewiththesecondrowofsampledatafromtheprecedinglist.Thistime,listthecolumnsexplicitlyintheINSERTclause.
5.Confirmyouradditiontothetable.
6.Writeaninsertstatementinadynamicreusablescriptfilenamedloademp.sqltoloadrowsintotheMY_EMPLOYEEtable.ConcatenatethefirstletterofthefirstnameandthefirstsevencharactersofthelastnametoproducetheuserID.Savethisscripttoafilenamedlab_08_06.sql.
7.Populatethetablewiththenexttworowsofsampledatalistedinstep3byrunningtheinsertstatementinthescriptthatyoucreated.
8.Confirmyouradditionstothetable.
9.Makethedataadditionspermanent.
UpdateanddeletedataintheMY_EMPLOYEEtable.
10.Changethelastnameofemployee3toDrexler.
11.Changethesalaryto$1,000forallemployeeswhohaveasalarylessthan$900.
12.Verifyyourchangestothetable.
13.DeleteBettyDancsfromtheMY_EMPLOYEEtable.
14.Confirmyourchangestothetable.
Practice9
1.CreatetheDEPTtablebasedonthefollowingtableinstancechart.Placethe
syntaxinascriptcalledlab_09_01.sql,andthenexecutethestatementinthescripttocreatethetable.Confirmthatthetableiscreated.
2.PopulatetheDEPTtablewithdatafromtheDEPARTMENTStable.Includeonlycolumnsthatyouneed.
3.CreatetheEMPtable
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 数据库 习题 Practice311
![提示](https://static.bdocx.com/images/bang_tan.gif)