风云学习oracle习题1、要求显示每一个雇员的职位基本年薪

SELECTename,job, eFROM SELECTename,job, eFROM SELECT'雇员编号是：'||empno||'的雇 是：'|| ||'，基本工资是：'||sal||'，职位是：'||job||'!'雇员信息FROMSELECT*FROMempWHEREsal>=1500ANDSELECT*FROMempWHEREjob='CLERK'ORSELECT*FROMempWHERE(job='CLERK'ORjob='SALESMAN')AND为SELECT*FROMempWHERESELECT*FROMempWHEREjob!='CLERK';SELECT*FROMempWHERENOTjob='CLERK';SELECT*FROMempWHEREhiredateBETWEEN'01-1月-1981'AND'31-12月-SELECT*FROMempWHEREcommISNOTSELECT*FROMempWHEREcommISSELECT*FROMempWHEREempno=7369ORempno=7566ORSELECT*FROMempWHEREempnoIN SELECT*FROMempWHEREenameLIKE 中第二个字母是A的全部雇员信SELECT*FROMempWHEREenameLIKE 中带有字母A的雇SELECT*FROMempWHEREenameLIKESELECT*FROMempORDERBYsalDESC,hiredate SELECTLOWER(ename)FROM SELECT CE(ename,'A','_')FROM FROMemployees; （MANAGER（CLERK ')FROMSELECT*FROMempWHERELAST_DAY(hiredate)-SELECTFROMempe,deptWHEREe.deptno=d.deptno(+)GROUPBYd.deptno,d.dname SELECTFROMempe,deptd,empmWHEREe.sal>ALL(FROMWHEREenameANDe.deptno=d.deptno SELECT FROMempe,empmORDER e SELECTFROMempe,empm,deptFROMempGROUPBYdeptno)ANDe.deptno=d.deptnoANDe.deptno=temp.dno;SELECTFROMempe,deptWHEREe.deptno(+)=d.deptno SELECTFROMempe,deptFROMGROUPBYdeptno)temp,salgradesAND ANDe.salBETWEENs.losalANDSELECTFROMdeptFROMempeGROUPBYempFROMGROUPBYdeptno)ANDe.deptno=res.dno; SELECTFROMempe,deptdANDd.dname='SALES'; SELECTFROMempe,deptd,empm,salgradeWHERESELECTAVG(sal)FROMANDANDANDe.salBETWEENs.losalANDSELECTFROMempe,salgradeWHEREe.salBETWEENs.losalANDGROUPBY SELECTFROMempeSELECTsalFROMempWHEREdeptno=30)ANDe.deptno=d.deptno;FROMempe,deptWHEREGROUPBYFROMempSELECTjob,MIN(sal)FROMempGROUPBYjob)tempWHEREe.job=temp.jobANDUPDATEemp sal=(1+WHEREOR 1、建表，并在相应字段上增加约束(直接使用students.sql语句运动员 2、运运动员 项目项目积分3、 C、找出参加 D、经 因为使用 E、 1、增加运动员数据：INSERTINTOsporter(sporterid,name, ,department)VALUES(1001,' INSERTINTOsporter(sporterid,name, ,department)VALUES(1002,' INSERTINTOsporter(sporterid,name, ,department)VALUES(1003,' INSERTINTOsporter(sporterid,name, ,department)VALUES(1004,' INSERTINTOsporter(sporterid,name,,department)VALUES(1005,'','女','心理系');INSERTINTOsporter(sporterid,name,,department)VALUES(1006,'','女','数学系');2、增加项目数据INSERTINTOitem(itemid,itemname,location)VALUES('x001','男子五千米','一操场');INSERTINTOitem(itemid,itemname,location)VALUES('x002','男子标枪','一操场');INSERTINTOitem(itemid,itemname,location)VALUES('x003','男子跳远','二操场');INSERTINTOitem(itemid,itemname,location)VALUES('x004','女子跳高','二操场');INSERTINTOitem(itemid,itemname,location)VALUES('x005','女子三千米','三操场');3、增加运动员的项目成绩INSERTINTOgrade(sporterid,itemid,mark)VALUESINSERTINTOgrade(sporterid,itemid,mark)VALUES(1002,'x001',4);INSERTINTOgrade(sporterid,itemid,mark)VALUES(1003,'x001',2);INSERTINTOgrade(sporterid,itemid,mark)VALUES(1004,'x001',0);INSERTINTOgrade(sporterid,itemid,mark)VALUES(1001,'x003',4);INSERTINTOgrade(sporterid,itemid,mark)VALUES(1002,'x003',6);INSERTINTOgrade(sporterid,itemid,mark)VALUES(1004,'x003',2);INSERTINTOgrade(sporterid,itemid,mark)VALUES(1005,'x004',6);INSERTINTOgrade(sporterid,itemid,mark)VALUES(1006,'x004',4);INSERTINTOgrade(sporterid,itemid,mark)VALUES(1003,'x002',6);INSERTINTOgrade(sporterid,itemid,mark)VALUES(1005,'x002',4);INSERTINTOgrade(sporterid,itemid,mark)VALUES(1006,'x002',2);INSERTINTOgrade(sporterid,itemid,mark)VALUES12FROMsporters,gradegWHEREFROMsporters,gradegWHEREGROUPBYSELECT*FROMSELECTs.department,SUM(g.mark)FROMsporters,gradeWHEREGROUPBYORDERBYsumDESC)WHEREROWNUM=1;SELECTs.department,SUM(g.mark)sumFROMsporters,gradegGROUPBYs.departmentHAVINGSELECTMAX(SUM(g.mark))sumFROMsporters,gradegGROUPBYs.department);2、找出在一操场进行比赛的各项目名称及其冠军 项目和成绩SELECTitemidFROMitemWHERElocation=SELECTi.itemid,MAX(g.mark)FROMitemi,gradeWHEREi.location=ANDi.itemid=g.itemidGROUPBYi.itemid;FROMsporters,gradeg,(SELECTi.itemidiid,MAX(g.mark)maxFROMitemi,gradegWHEREi.location=ANDGROUPBYi.itemid)WHEREs.sporterid=g.sporteridANDtemp.iid=g.itemidANDSELECTFROMsporters,gradeSELECTi.itemidiid,MAX(g.mark)FROMitemi,gradeWHEREi.location=ANDGROUPBYi.itemid)temp,itemWHEREs.sporterid=g.sporteridANDtemp.iid=g.itemidANDg.mark=temp.maxANDg.itemid=i.itemidANDtemp.iid=i.itemid;3、找出参加 ；第一步：确 SELECTs.sporteridFROMsportersWHERE SELECTg.itemidFROMgradegWHEREg.sporterid=(SELECTs.sporteridFROMsportersWHERE SELECTg.sporteridFROMgradegWHEREg.itemidIN(SELECTFROMgradeWHEREg.sporterid=(SELECTs.sporteridFROMsportersWHERE SELECTnameFROM