oracle实验内容2共40题.docx
- 文档编号:2467131
- 上传时间:2022-10-29
- 格式:DOCX
- 页数:19
- 大小:376.82KB
oracle实验内容2共40题.docx
《oracle实验内容2共40题.docx》由会员分享,可在线阅读,更多相关《oracle实验内容2共40题.docx(19页珍藏版)》请在冰豆网上搜索。
oracle实验内容2共40题
Part1
1.Writeaquerytodisplaythecurrentdate.LabelthecolumnDate.
selectto_char(sysdate,'year-mon-day')“Date”
fromdual
2.TheHRdepartmentneedsareporttodisplaytheemployeenumber,lastname,salary,andsalaryincreasedby15.5%(expressedasawholenumber)foreachemployee.LabelthecolumnNewSalary.PlaceyourSQLstatementinatextfilenamedlab_03_02.sql.
selectemployee_id,last_name,salary,salary*1.155new_salary
fromemployees
3.Runyourqueryinthefilelab_03_02.sql.
4.Modifyyourquerylab_03_02.sqltoaddacolumnthatsubtractstheoldsalaryfrom
thenewsalary.LabelthecolumnIncrease.Savethecontentsofthefileaslab_03_04.sql.Runtherevisedquery.
selectemployee_id,last_name,salary,salary*1.155new_salary,salary*0.155increase
fromemployees
……
5.Writeaquerythatdisplaysthelastname(withthefirstletteruppercaseandallotherletterslowercase)andthelengthofthelastnameforallemployeeswhosenamestartswiththelettersJ,A,orM.Giveeachcolumnanappropriatelabel.Sorttheresultsbytheemployees’lastnames.
selectlast_name,length(last_name)length
fromemployees
wherelast_namelike'A%'orlast_namelike'J%'orlast_namelike'M%'
orderbylast_name
Rewritethequerysothattheuserispromptedtoenteraletterthatstartsthelastname.Forexample,iftheuserentersHwhenpromptedforaletter,thentheoutputshouldshowallemployeeswhoselastnamestartswiththeletterH.
6.TheHRdepartmentwantstofindthelengthofemploymentforeachemployee.Foreachemployee,displaythelastnameandcalculatethenumberofmonthsbetweentodayandthedateonwhichtheemployeewashired.LabelthecolumnMONTHS_WORKED.Orderyourresultsbythenumberofmonthsemployed.Roundthenumberofmonthsuptotheclosestwholenumber.
Note:
Yourresultswilldiffer.
selectlast_name,round(months_between(sysdate,hire_date))"MONTHS_WORKED"
fromemployees
orderbyhire_datedesc
7.Createareportthatproducesthefollowingforeachemployee:
selectlast_name||'earns'||to_char(salary,'$999,999.99')||'monthlybutwants'||to_char(3*salary,'$999,999.99')
fromemployees
8.Createaquerytodisplaythelastnameandsalaryforallemployees.Formatthesalarytobe15characterslong,left-paddedwiththe$symbol.LabelthecolumnSALARY.
selectlast_name,lpad(salary,15,'$')"SALARY"
fromemployees
9.Displayeachemployee’slastname,hiredate,andsalaryreviewdate,whichisthefirstMondayaftersixmonthsofservice.LabelthecolumnREVIEW.Formatthedatestoappearintheformatsimilarto“Monday,theThirty-FirstofJuly,2000.”
Selectlast_name,to_char(hire_date,'dd-mm-yy')"hire_date",to_char(next_day(add_months(hire_date,’星期一'),'day,"the"ddspth"of"month,yyyy')"REVIEW"
fromemployees
10.Displaythelastname,hiredate,anddayoftheweekonwhichtheemployeestarted.LabelthecolumnDAY.Ordertheresultsbythedayoftheweek,startingwithMonday.
selectlast_name,hire_date,to_char(hire_date,'day')"DAY"
fromemployees
orderbyto_char(hire_date-1,'d')
11.Createaquerythatdisplaystheemployees’lastnamesandcommissionamounts.Ifanemployeedoesnotearncommission,show“NoCommission.”LabelthecolumnCOMM.
Selectlast_name,nvl(to_char(commission_pct),'NoCommission')"COMM"
Fromemployees
12.Createaquerythatdisplaysthefirsteightcharactersoftheemployees’lastnamesandindicatestheamountsoftheirsalarieswithasterisks.Eachasterisksignifiesathousanddollars.Sortthedataindescendingorderofsalary.LabelthecolumnEMPLOYEES_AND_THEIR_SALARIES.
Selectrpad(substr(last_name,1,8),8)||lpad('',salary/1000,'*')
fromemployees
orderbysalarydesc
13.UsingtheDECODEfunction,writeaquerythatdisplaysthegradeofallemployeesbasedonthevalueofthecolumnJOB_ID,usingthefollowingdata:
JobGrade
AD_PRESA
ST_MANB
IT_PROGC
SA_REPD
ST_CLERKE
Noneoftheabove0
selectjob_id,decode(job_id,'AD_PRES','A',
'ST_MAN','B',
'IT_PROG','C',
'SA_REP','D',
'ST_CLERK','E',0)"GRA"
fromemployees
14.RewritethestatementintheprecedingexerciseusingtheCASEsyntax.
selectjob_id,casejob_idwhen'AD_PRES'then'A'
when'ST_MAN'then'B'
when'IT_PROG'then'C'
when'SA_REP'then'D'
when'ST_CLERK'then'E'
else'0'
end"GRA"
fromemployees
15.Groupfunctionsworkacrossmanyrowstoproduceoneresultpergroup.
True/FalseT
16.Groupfunctionsincludeenullsincalculations.
True/FalseF
17.TheWHEREclauserestrictsrowsbeforeinclusioninagroupcalculation.
True/FalseT
TheHRdepartmentneedsthefollowingreports:
18.Findthehighest,lowest,sum,andaveragesalaryofallemployees.Labelthecolu
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 实验 内容 40
![提示](https://static.bdocx.com/images/bang_tan.gif)