搜索
【boat-sailor】1.找出预订了103船的水手的名字SELECTdistinctsnameFROMsaliorsWHEREsidIN(selectsidfromreserveswherebid='103')DustinLubberHoratio2.找出预订了红色船的所有水手的名字SELECTdistinctsnameFROMsaliorsWHEREsidIN(selectsidfromreserveswherebidin(selectbidfromboatswherecolor='red'))DustinLubberHoratio3.找出Lubber预定的船的所有颜色selectdistinctcolorfromboatswherebidin(selectbidfromreserveswheresidin(selectsidfromsaliorswheresname='Lubber'))RedGreenRed4.找出至少预订了一艘船的水手的名字selectdistinctsnamefromsaliorswhereexists(selectsidfromreserves)AndyArtBobBrutusDustinHoratioLubberRustyZorba5.找出预订了一艘红色船或者绿色船的水手名selectdistinctsnamefromsaliorswheresidin(selectsidfromreserveswherebidin(selectbidfromboatswherecolor='red'orcolor='green'))DustinHoratioLubber6.找出同时预订了红色船和绿色船的水手名selectdistinctsnamefromsaliorswheresidin(selectsidfromreserveswherebidin(selectbidfromboatswherecolor='red'))intersectselectdistinctsnamefromsaliorswheresidin(selectsidfromreserveswherebidin(selectbidfromboatswherecolor='green'))DustinHoratioLubber7.找出预定了所有船的水手们的名字selectdistinctsnamefromsaliorswherenotexists(select*fromboatswherenotexists(select*fromreserveswheresid=saliors.sidandbid=boats.bid))Dustin8.找出预定了所有名为Interlake船的水手的名字selectdistinctsnamefromsaliorswherenotexists(select*fromboatswherebname='Interlake'andnotexists(select*fromreserveswheresid=saliors.sidandbid=boats.bid))DustinHoratio9.找出最老的水手的名字和年龄selectdistinctsname,agefromsaliorswhereage=ALL(selectagefromsaliors)Bob63.510.找出比等级为10的最老的水手的年龄还要大的水手的名字selectdistinctsnamefromsaliorswhereageALL(selectagefromsaliorswhererating='10')BobDustinLubber11.对于每个等级级别找出最年轻的水手的年龄selectmin(age)fromsaliorsgroupbyrating33.025.535.025.535.016.012.对于至少有两个水手的等级级别，求出水手们的平均年龄selectavg(age)fromsaliorsgroupbyratinghavingcount(*)=225.540.040.544.513.对于至少有两个水手的等级，找出到了投票年龄（18岁）的所有水手的平均年龄selectavg(s.age)fromsaliorsswheres.age18groupbys.ratinghaving1(selectcount(*)fromsaliorss2wheres.rating=s2.rating)44.54040.53514.找出在所有的等级中水手的平均年龄是最低的那些等级selects.ratingfromsaliorssgroupbys.ratinghavingavg(s.age)=all(selectavg(s2.age)fromsaliorss2groupbys2.rating)10【work】1.列出公司全部职工的信息select*fromemp7369SMITH7902CLERK1980-11-1700:00:00.000NULL800207499ALLEN7698SALESMAN1981-02-2000:00:00.0005001600307521WORD7698SALESMAN1981-02-2200:00:00.0005001250307566JONES7839MANAGER1981-04-0200:00:00.000NULL2975207654MARTIN7698SALESMAN1981-09-2800:00:00.00014001250307698BLACK7839MANAGER1981-05-0100:00:00.000NULL1850307782CLARK7839MANAGER1981-06-0900:00:00.000NULL2450107788SCOTT7566ANALYST1981-12-0900:00:00.000NULL3000207839KINGNULLPRESIDENT1981-12-1700:00:00.000NULL5000107844TURNER7698SALESMAN1981-09-0800:00:00.00001500307876ADAMS7788CLERK1981-09-2300:00:00.000NULL1100207900JAMES7698CLERK1981-11-0300:00:00.000NULL950307902FORD7566ANALYST1981-11-0300:00:00.000NULL3000207934MILLER7788CLERK1982-01-2300:00:00.000NULL1300102.列出公司总全部职工的姓名selectenamefromempSMITHALLENWORDJONESMARTINBLACKCLARKSCOTTKINGTURNERADAMSJAMESFORDMILLER3.列出10号部门中全体职工的姓名及职工号selectename,empnofromempwheredeptno=10CLARK7782KING7839MILLER79344.列出10号部门中全体职工的姓名及其工作情况selectename,jobfromempwheredeptno=10CLARKMANAGERKINGPRESIDENTMILLERCLERK5.求工资额在2000，3000之间的职员情况（姓名，工种，工资额）selectename,job,salfromempwheresal3000andsal2000JONESMANAGER2975CLARKMANAGER24506.列出公司中所有名字以LI开头的全部职工及其所在的部门代号selectename,deptnofromempwhereenamelike'LI%'(结果为空)7.查询20号部门的不是服务员的雇员姓名和工种情况selectename,jobfromempwherejob!='clerk'anddeptno=20JONESMANAGERSCOTTANALYSTFORDANALYST8.查询20号部门或者不是服务员的雇员的情况select*fromempwherejob!='server'ordeptno=207369SMITH7902CLERK1980-11-1700:00:00.000NULL800207499ALLEN7698SALESMAN1981-02-2000:00:00.0005001600307521WORD7698SALESMAN1981-02-2200:00:00.0005001250307566JONES7839MANAGER1981-04-0200:00:00.000NULL2975207654MARTIN7698SALESMAN1981-09-2800:00:00.00014001250307698BLACK7839MANAGER1981-05-0100:00:00.000NULL1850307782CLARK7839MANAGER1981-06-0900:00:00.000NULL2450107788SCOTT7566ANALYST1981-12-0900:00:00.000NULL3000207839KINGNULLPRESIDENT1981-12-1700:00:00.000NULL5000107844TURNER7698SALESMAN1981-09-0800:00:00.00001500307876ADAMS7788CLERK1981-09-2300:00:00.000NULL1100207900JAMES7698CLERK1981-11-0300:00:00.000NULL950307902FORD7566ANALYST1981-11-0300:00:00.000NULL3000207934MILLER7788CLERK1982-01-2300:00:00.000NULL1300109.查询获得的佣金超过他本人基础工资的5%的职工，并按百分比的高低排序显示selectename,comm,sal,comm*1.00/salfromempwherecommsal*0.05orderbycomm*1.00/saldescMARTIN140012501.1200000000000WORD50012500.4000000000000ALLEN50016000.312500000000010.列出公司中不能给奖金的雇员姓名及其工种情况selectename,jobfromempwherecommisnullorcomm=0SMITHCLERKJONESMANAGERBLACKMANAGERCLARKMANAGERSCOTTANALYSTKINGPRESIDENTTURNERSALESMANADAMSCLERKJAMESCLERKFORDANALYSTMILLERCLERK11.要安排Wilson做销售员，同时工资改为2000，佣金增加50%updateempsetsal=2000,comm=comm*1.5,job='SALESMAN'whereename='Wilson'12.给20号部门中所有分析员和经理提高工资15%updateempsetsal=sal*1.15wheredeptno='20'andjobin(selectjobfromempwherejob='manager'orjob='analyst')13.给在NEWYORK工作的雇员提高工资５％updateempsetsal=sal*1.05wheredeptnoin(selectdeptnofromdeptwherecity='NEWYORK')14.为公司中工龄超过10年的职工提升一级工资（10元）updateempsetsal=sal+10wheredatediff(year,hiredate,getdate())1015.找出所有在1956年1月4日到4月15日之间雇佣的雇员