oracle实验内容2共40题文档格式.docx
- 文档编号:15384056
- 上传时间:2022-10-29
- 格式:DOCX
- 页数:19
- 大小:376.82KB
oracle实验内容2共40题文档格式.docx
《oracle实验内容2共40题文档格式.docx》由会员分享,可在线阅读,更多相关《oracle实验内容2共40题文档格式.docx(19页珍藏版)》请在冰豆网上搜索。
thenewsalary.LabelthecolumnIncrease.Savethecontentsofthefileaslab_03_04.sql.Runtherevisedquery.
selectemployee_id,last_name,salary,salary*1.155new_salary,salary*0.155increase
……
5.Writeaquerythatdisplaysthelastname(withthefirstletteruppercaseandallotherletterslowercase)andthelengthofthelastnameforallemployeeswhosenamestartswiththelettersJ,A,orM.Giveeachcolumnanappropriatelabel.Sorttheresultsbytheemployees’lastnames.
selectlast_name,length(last_name)length
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"
orderbyhire_datedesc
7.Createareportthatproducesthefollowingforeachemployee:
<
employeelastname>
earns<
salary>
monthlybutwants<
3timessalary>
.LabelthecolumnDreamSalaries.
selectlast_name||'
earns'
||to_char(salary,'
$999,999.99'
)||'
monthlybutwants'
||to_char(3*salary,'
)
8.Createaquerytodisplaythelastnameandsalaryforallemployees.Formatthesalarytobe15characterslong,left-paddedwiththe$symbol.LabelthecolumnSALARY.
selectlast_name,lpad(salary,15,'
$'
)"
SALARY"
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"
10.Displaythelastname,hiredate,anddayoftheweekonwhichtheemployeestarted.LabelthecolumnDAY.Ordertheresultsbythedayoftheweek,startingwithMonday.
selectlast_name,hire_date,to_char(hire_date,'
day'
DAY"
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,'
*'
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'
then'
when'
then'
else'
0'
end"
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