Oracle数据库习题Practice311Word下载.docx

Oracle数据库习题Practice311Word下载.docx

《Oracle数据库习题Practice311Word下载.docx》由会员分享，可在线阅读，更多相关《Oracle数据库习题Practice311Word下载.docx（21页珍藏版）》请在冰点文库上搜索。

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.

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.

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