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

SELECTename,job, eFROMSELECTename,job, eFROMSELECT'雇员编号是：'||empno||'的雇员是：'|| ||'，基本工资是：'||sal||'，职位是：'||job||'!'雇员信息FROMSELECT*FROMempWHEREsal>=1500ANDSELECT*FROMempWHEREjob='CLERK'ORSELECT*FROMempWHERE(job='CLERK'ORjob='SALESMAN')ANDSELECT*FROMempWHEREjob<>'CLERK';SELECT*FROMempWHEREjob!='CLERK';SELECT*FROMempWHERENOTjob='CLERK';SELECT*FROMempWHEREhiredateBETWEEN01-1月-1981'AND'31-12月-SELECT*FROMempWHEREcommISNOTSELECT*FROMempWHEREcommISSELECT*FROMempWHEREempno=7369ORempno=7566ORSELECT*FROMempWHEREempnoINSELECT*FROMempWHEREenameLIKESELECT*FROMempWHEREenameLIKESELECT*FROMempWHEREenameLIKESELECT*FROMempORDERBYsalDESC,hiredateSELECTLOWER(ename)FROMSELECTINITCAP(ename)FROMSELECTename,LENGTH(ename)FROMempWHERESELECTRECE(ename,'A','_')FROMSELECTename,SUBSTR(ename,LENGTH(ename)-2)FROMemp;SELECTename,SUBSTR(ename,-3)FROMemp;SELECTename,hiredate,TRUNC(MONTHS_BETWEEN(SYSDATE,hiredate))FROMemp;SELECTtrunc（months_between(sysdate,hire_date),0）FROM （MANAGER（CLERKSELECT*FROMempWHERELAST_DAY(hiredate)-FROMempe,deptdWHEREe.deptno=d.deptno(+)GROUPBYd.deptno,d.dnameSELECTe.empno,e.ename,d.dname,m.enameFROMempe,deptd,empmWHERESELECTsalFROMWHEREenameANDe.deptno=d.deptnoANDe.mgr=m.empno(+);SELECTe.empno,e.ename,m.empno,m.ename,(m.sal+NVL( FROMempe,empmWHEREe.mgr=m.empno(+)ORDERBY eDESC;SELECTe.empno,e.ename,d.dname,d.loc,temp.countFROMempe,empm,deptd,(SELECTdeptnodno,COUNT(empno)countFROMempGROUPBYdeptno)WHEREe.mgr=m.empno(+)ANDe.hiredate<m.hiredateANDe.deptno=d.deptnoANDe.deptno=temp.dno;FROMempe,deptdWHEREe.deptno(+)=d.deptnoGROUPBYd.deptno,d.dname,d.loc;SELECTe.ename,d.dname,temp.count,s.gradeFROMempe,deptd,(SELECTdeptnodno,COUNT(empno)countFROMempGROUPBYdeptno)temp,salgradesWHEREe.job='CLERK'ANDe.deptno=d.deptnoAND ANDe.salBETWEENs.losalANDSELECTtemp.job,temp.count,d.dname,e.ename,res.avgFROMdeptd,(SELECTe.jobjob,COUNT(e.empno)countFROMempeGROUPBYempFROMGROUPBYdeptno)WHEREe.deptno=d.deptnoANDe.job=temp.jobANDe.deptno=res.dno;SELECTe.ename,e.sal,e.hiredate,d.dnameFROMempe,deptdWHEREe.deptno=d.deptnoANDd.dname='SALES';SELECTe.empno,e.ename,e.job,e.sal,d.dname,d.loc,m.ename,s.gradeFROMempe,deptd,empm,salgradesWHEREANDe.deptno=d.deptnoANDe.mgr=m.empno(+)ANDe.salBETWEENs.losalANDFROMempe,salgradesWHEREe.salBETWEENs.losalANDs.hisalGROUPBYs.grade,s.losal,s.hisal;SELECTe.empno,e.enameFROMempeWHERESELECTsalFROMempWHEREdeptno=30)ANDe.deptno=d.deptno;FROMempe,deptdWHEREe.deptno(+)=d.deptnoGROUPBYd.deptno,d.dname,d.loc;SELECTe.ename,e.job,e.salFROMempe,(SELECTjob,MIN(sal)FROMempGROUPBYjob)tempWHEREe.job=temp.jobAND sal=(1+WHEREOR SQL语句完成如下功能：1、建表，并在相应字段上增加约束(students.sql语句2、运动员运动员 3、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)VALUES(1001,'x001',6);INSERTINTOgrade(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)VALUESINSERTINTOgrade(sporterid,itemid,mark)VALUES1、第一步：sportergradeSELECTs.department,g.markFROMsporters,gradegWHERESELECTs.department,SUM(g.mark)FROMsporters,gradegWHEREs.sporterid=g.sporteridGROUPBYs.department;SELECT*FROMSELECTs.department,SUM(g.mark)sumFROMsporters,gradegWHEREs.sporterid=g.sporteridGROUPBYs.departmentORDERBYsumDESC)WHEREROWNUM=1;SELECTs.department,SUM(g.mark)sumFROMsporters,gradegWHEREs.sporterid=g.sporteridGROUPBYs.departmentHAVINGSELECTMAX(SUM(g.mark))sumFROMsporters,gradegWHEREs.sporterid=g.sporteridGROUPBYs.department);2、找出在一操场进行比赛的各项目名称及其冠军的第一步：IDSELECTitemidFROMitemWHERElocation='SELECTi.itemid,MAX(g.mark)maxFROMitemi,gradegWHEREi.location=ANDi.itemid=g.itemidGROUPBYi.itemid;SELECT,g.itemid,temp.maxFROMsporters,gradeg,(SELECTi.itemidiid,MAX(g.mark)maxFROMitemi,gradegWHEREi.location='ANDi.itemid=g.itemidGROUPBYi.itemid)tempWHEREs.sporterid=g.sporteridANDtemp.iid=g.itemidANDSELECT,g.itemid,temp.max,i.itemnameFROMsporters,gradeg,(SELECTi.itemidiid,MAX(g.mark)maxFROMitemi,gradegWHEREi.location=ANDi.itemid=g.itemidGROUPBYi.itemid)temp,itemiWHEREs.sporterid=g.sporteridANDtemp.iid=g.itemidANDg.mark=temp.maxANDg.itemid=i.itemidANDtemp.iid=i.itemid;3、