整理sql课后题

1、1作业(1) 从表中查询出数据查询出scott.dept表中部门号(dept no)小于50的部门名称(dn ame)SQL select dname from dept where deptno desc dept ；TypeNameNull?DEPTNONOT NULLNUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(131此处有隐式转换，oracle把varchar2转换成了数字。(2)描述表scott.emp的结构SQL desc emp；NameNull?TypeEMPNONOT NULLNUMBERS)ENAMEVARCHAR2(ie)JOBVARCHAR

2、2(9)MGRNUMBER(4)HIREDATEDATESALNUMBER72)COMMNUMBER(7t2)DEPTNONUMBER!2(3)进行数学表达式的计算、使用列的别名 从scott.emp表中查出所有员工的工资和奖金之和。5QL select empnof enaine, sal+decode(coininf null,0,comm) sal*comm from emp；EMPNOENAMEsal+comm7369SMI TH3007499ALLEN19007521WARD175D7566JONES29757654MARTIN26507698BLAKE28507782CLARK24

3、507788&C0TT3QGQ7S3$KING50007344TURNER156(37876AOAMS1LO0EMPNOENAMEsal+comTH79G0JAMES9507902FOKD30607934MILLER130014 raws selected 正确统计出scott.emp表中部门号为10的，每个员工全年收入S0L select eiipn o 4 ena ne (s a I * de fodeJtzcMiinFriiLilLL.EhcDniiilJ.U total troni emp wfiere deptno-10 d rder b y total；EF*PNOENAMETOT

4、AL734FILLER1560677S2CLARK2M0O7839KINbbOaUCl运用SQL*Plus工具将缓冲区中的SQL语句保存到一个文件，并执行该文件中的SQL语句。SQL L1* select * from testSQL save 7u01/test02.sql上面(3)中的结果：5QL L1* select cimpno亡Ftiini吃,(0comm) =12 total f roni cup where y total5QL save */home/oraclc/emp L sqV *Created file /hore/oracle/emp.sql50Lhonip/orac

5、I/empEMPNOENAFIETOTAL7S34MILLER156007782CLARK294907839KING600002作业i。创建一个查询，来显示雇员的名字和工资。 其工资要在$2850以上。将该语句保存到一个文件中：p2q1.sql在sqlplus中执行该文件。5QL select * fronEMPNO EMAflEemo whe re sal-2650;SALCDHMDEPTNOJOBMGR HrREO/TE7566 JONESMEAGER7839 S2-AFH-B1207S2G7?sa E匚DTTftNALVST7566 L9*APR-S730 oe7039 KINGPRES

6、IDENTL7-M0V-B1586ie79G2 FORDANALY5I75bb 93-DEC-81205QL Idetect * fromenp where 5al205OSQL 53ve /hane/pracle/p2ql+5ql;Created file /h hono/a raclfl/p2q 16MPM0 ENAMEJOEMGR HIREDATESALCOMMDEPTtdO7566 JONESNANMFR7B39 B2-&PR R12752077B0 SCOTTANALYST7566 LO-APR BT3GQ92&783y KINGPHtSlUtNTL?*MUV-ML107902 FO

7、RDANALYST7566 Ui-Ott. KL抄2更改文件p2q1.sql中的命令，将查询条件改为：工资（sal）在$1500和$2850之间。重新执行该文件。5QLn selecL * fron enp lAihtre sal bsLween Z3Q3 and 2S50；FFlFNnENAMEJORMGRHTREDATEcmDEPTH。7499ALLEMSMESANI759S23-FEB-311600dQ7fiQRRL AKENAN&GFR7Riqai may-si?05O107782C LAR ItMANAGER733999-JUN-8110044TUFV1LRSAtLAN759Bea-

8、SEF-si15003胡L L1* select * f(qii etip 诫怡古dl twen 1:500 dud ZBtjQ5QLn Sdve /riome/ur di-le/p2L；l.sqf ；SP2- 9540 . File Hhone/oiactE/pJql. hqth dIr eddy.Ube -SAVE fllEnare.est REFLACE.SQL save Vhome/oracle/pSql.sqf replace；Wrote file /hcme/oratle/p2c|l：5寸ISQL /hjme/cracl_ep2qlENAMEJOBHGR HIREDATESALCO

9、MMDEPTNO7409ALLENSALESMAN7698 29-FEB-E11500390307现RL AKEMANGFR7S3 select Fripna enmE1 r J ab hiredatp Fran emp wlie re deptro in ( 116 F 3 j ordE r by hiredate de sc ;E忖 PIW IENAMEJOBHtFUTE汕4 M1LLLRCLfcRK21 JAM 829U0 JAMLSCLLWK03 DLL U17館9 KINGPRESIDENT17 MOV QI7654 HARTIlNSALESMAN2S-3EF-S17344 TURN

10、ERSALESMANeS-SEF-4177BZ CLARKMANAGERS9-JUH-B17698 Rl AKEMANAGER01-MAY-S17521 WARDSALESMAN22-FEB或隐身转换：SQLi select empna,einane H J obrhlrGOate f ronn mip wherG deptn3 ln(止呂,3日i order by hiredate de-sc；EMPKU EriAHEJUE3HIRLUATE?934 MILLEftCLERK23 JAN-Q2zyGQ JamesCLERK Sielect * from Aoip where- (decode

11、j ccmm,. null. S, ronnm) - tai )/salG . 1 ；EMPNIO ENAMEJO-BMGR HIREDATE5ALCOMMDEPTWO7654 HARTLMbALEbMAN769B 2B-SEP-811230149930注意如果sal有空的（注意分母不能为0,而且保持原来的数据最好用1）：SQL*lect rrofllnp 诃Herg (dectdconnn,nu uLrtl, cotnr)-&aL/de 亡l 左 aLL)；EUPNO EhAPIEJOEMGH HIREDATESALCOMMDEPTH076S4 MARTINSALESMAN7&rfl 28

12、SEP 811259HOD列这道题颠倒一下就很难了：SQL select * framemp where ( sal-decodeccimn, inutl10 . cainn) J/dlecade(sialOp 1pHUllasal I&.1EMP*JO EtJAPIEJDB炖 HI IRE DATESALCOrtfDEPTNO7 359 SIN I T H匚 LEHK7tJH2 1T-DE 匚-J3DEBB7499 ALLEN5ALE5HAU7&9S 20-FES-fil1.6 OD7521 VfARDSALESMAN7&98 22-FEB-fil125&560307556 JOMESMAN

13、AGED7B39 02-APil-El29?5賞7&9B ELWtEMAN.GEFt7B39 &1-MAY-612B5D3&?I2 CLARKMANAOER7839呂与UJ-El24591077& 5COTTAMALVST7S66 19-APft-07JOOD207039 KINGPSlDEHT17-HDV-fil5005107844 TURNERSALfcSMAUH3-SEP-E1L50We37076 ADA15CLERK77SB 23-MAY-e?L1&3/93B JAHE5匚 LERKBi-DE匚-El95330EMPNO EHA.*1EJ3B酯 R HI IRE DATESALCOTt

14、fDEPTNO7902 FORDAJALVST7see &3-DEc-ai2&甜勢MILLEDCLEKK7782 23-JAN-S2ISO&游加 EMLTblfc匚注FIK7&92 17 DEC-SO咖201 d rnw qp Ittarl3作业(department_id)的员工记录以下练习题使用 hr用户登录1查询所有员工及对应部门的记录，包括没有对应部门编号SQL select e.oupno .anni4he. sal rd .dcptnOj dl .dnsred. loc f rott cvp dpt d 总 &. doptno-d .dept no(+ i j17 raws sel

15、ecttd.SQL select e ,erpnoB d&pl: na=tl. d&ptno)；百alM心mptnciid dnmirQ.cllQ train mp e left outer join dept d on (g17 luwbled.EMPNO ENAMESALDEPT1N0 DNAflFLOC?3tj9 SMITH的e26 RtStARCH A.LILASZ499 ALLEN16CJ0dO SALESCHlLAbO7521 WARDizse30 SALESCHICAGO7564 JONES297526 RESEARCHDALLAS754 MARTIN1250iALESCHICA

16、GO769H OLAKE25G30 SALESCHICAGO770J CLARK343B1 ACCOUITTMCNEW YORK7798 SCOTT300029 RESEARCHDALLAS79 3Q KING5帕8M ACCOUNTINGNEW YORK7RJ4 TURNERISflO10 SAI fSCHICAGO7876 ADAMS110S7B RFFARCHDALLASEMPKO ENAHEsalDEPTNO DNANELOC79 BO J flJMES9503B SALESCHTCA&O79B2 FORDjose20 Rt5EARCHALLAS旳料MILLLRL3Q010 A(._U

17、UUrLNGNtW YORK的的SMim2B RESEARCHDALLASMOL LIU3ose目也虹ZHAN&17 i uurn 注l飙 tmd.a.XXX=b.XXX(+)等价于 a left outer join b on (a.XXX=b.XXX)左外连接。2查询所有员工及对应部门的记录，包括没有任何员工的部门记录。SQIL seiect e .erpno.e .enameesal .d,deptnd.d seLect e-enpne.e前孑帕色亠$31.口“ select e.VipnopbeiifMner&.?alrd.deptnQrd.dninejd.lot dcptno-d-

18、dcptnoj jfrani emp eLOCfunouter join dept d on (e.FM1PIW FN3NFSALDFPTN DINAMF7369 SMITH叭26 RESEARCH1ALIA$749 ALLEN16G -3S SALESCHICAGO?521 利ARDi2se30 SALESCHICAGO?5cb JOESs j2U RESEAHLHDALLAS7(154 MARTtNi2se3 SALESCHTCAGD76S BLAKE2fi536 SALESCHICAGO?/82 CLAKK亠牡：13NLh档瞅7783 SCOTT360626 RESEARCHDALLAS

19、7839 KIMG560IQ ACCOUMTINGNEM YORK?U44 TURNERiset3fl EALE&?fi AniM! !0!23 RESEARCH Ml屮何 ENAHESALDEPTNO DN4HELOC7900 JAJW1ES95 036 SALESCHICAGO79&2 FORD3驱2& RESEARCHDALLAS793+ MILLERnee13 iCCDJVriNCNEW YORK999 SMITH%ABA2e ESEACHDALLAS日阳1 LIUseeeM62 ZHANG30&e4fi OPERATIONSMST0N16 row,5QHL ?elect e . er

20、pno.e.enfiine,e.r sal.d .deptno.d.dnnied .locfron enp e,deptd wtiere e.deptnot+J=d,dEpt no +);e. iipno e . endfie . sd L)d dplnci pd. dflain, d, loc f r oim en p o deipl d wfner g； deptno(idadeptrio*ERROR 甘1 line 1:0RA-eil69； a preoicat rttrerence only one Qutr-joined table4.写一个查询来查询出雇员的名字，部门号，部门名称。

21、SQL select B emipnc|l.naimie Pe.deptnp ad .dnane from m)p , dept d whe redeptna=d + dGptno ；EHPNO EMAMFDEPTNO I1MANE739 5*1 JTHZO RESEARCH7499 ALLEN39 5ALES7521 WARD30 5ALE5756 J3MES20 RESEARCH765 MARTIN39 5ALES7698 BLAKE39 5ALES77H? n IRKIf) acrOUWTTNG778R SCDTT2fl FtFSFARCH7S39 KING10 ACCQUhTINQ7S

22、44 TURNER0 5ALES707G ADAH520 HE5URCHENPUO EN4IIEDEFIMO DHAE7Q0B ftMFS30 SAI ES79fl2 FORD20 RESEARCH7934 MILLERIQ ACCOUNTING9999 SMITHS20 RESEARCH15 rows selected.5输出30号部门的所有工作的列表，以及部门名称。SQL select dis tinczt e L job0dfrom emp e . dept d whe re e . deptno-d .cluptrici and e dept no-3 OsJOBUMAFlIbSLES

23、MfiNCLCIWSALESSALESSALES6.写一个查询，来查询出挣到佣金( comm)的雇员姓名，部门名称，和部门所处的位置。SQIL select ? . Bnpn , b . enaiie, e .匚drum. dl. rlmp tnciE . ciinRiriEri _ Lu匚 Frcin emp b k d&pit d where e. dep tro=d . dept no and e.conmi 丄雪 not null；EMPhC :NtMEcowlBEFHNONiklELOC7459 ALLLNJBSAL EhCH 丄 CACjD7521 WA4D5UUJOALESCHI

24、CAGO764 MAlRTTN14Q030SALESCHICA607844 TURNER030SALESCHICAGO7.写一个查询，查出在名字里面有一个A的所有雇员姓名和其所在的部门的名称。SQL select 巴.empnof e 便口刊11亡日d巳ptii口d dn select e. emp no e. enarae s d, dept no rtdLlot from emp e. dept d where e,deptno=d .(lEptnc andLnBtrte.eridrnie, A )SEWPNO ENWEtJEPTNO DMAJdELOC7499 ALlLEhJU SALf

25、cCHICAGO7521 U4R.D3G SALESCHICAGO7654 MAHTINSALLSUH 5朗弼 BLAKE36 SALESCHICAGO7702 CLAFW16 ACCOUNTINGNEW YORK78 76 ADWfe26 RkSfeARLHDALLAS700 14MES38 S4LE5CHICArO7 rows iclcctea.8.写出一个查询，查出工作在DALLAS的所有员工的姓名、工作、部门号、部门名称。SQIL select e - empna e . enaraej ob, d. dppt no r dl. rinarE d . Id匚 f rom emp e r

26、 dpt d u*he fp e _ rieptno=d . dept na 3nd l, LOC-DALLAS ;EP*PND EMAHEJDSDEPTHO DKAIELDC7369 SMITHCLERKJfl RESEARCHDALLAS7566 JONESMANAGER20 RESEARCHDALLAS?7sa sconANLTbl2U IRLSLARCHDALLAS7376 ADVI5CLERK23 RESEARCHULLAS7902 FORDANALYST20 RESEARCHDALLAS9999 5MITH*CLERKH ftESEMCHDALLAS9.查出每个雇员的编号、姓名、其

27、管理者的编号和姓名。各个列分别命名为：Emp#,EmpName,EmpM#,EmpName.注意看下面的语句：SQL select from emp；EMHJt)EHAMEJOEMG.F1HIEDATESALC3MMDEPTNO73695MITHICLERK790217-DEC-86SGB2G7499ALLENsalesman20 FEB SI1609300307521WARD5ALE三H肩N7693Z2-FEB-S1500307566J0NE5MAINAGER783902-APR-8129752 select a. enipno ra亡门己门亡孑定mpmoEngine tron emp a

28、t emp b where a.pnobrj：EMPNO ENAMEEMPNQ EriAME7902 FORD7369 SMITH7698 BLAKE749 ALLEN769B BLAKE7839 KING7 73CRk|7M6 JONeI769S BLAKE7844 TURNER7788 SCOTT78?6 A&AHS7693 BLAKE79ae JAMESMPNO ENAMGGHPMO EUAME7566 10NES793J FORD7782 CLARK7934 MILLER7902 FORD999 SMITH%7839 KING阳 01|7839 Klhtiaoaz zhangI16 r

29、ows selected.如果a.id=b上司号，那么这时，a的信息就是当领导的人的信息（不管管几个人） 也就是a表中的哥们是b表中哥们儿的的领导。这时a表中有king,而且显示出了该领导管理的人员名单。SQL selec t 3 empTio a , ename r b . empno 9 b B ename fro nr emp a f emp lb whe re a . mg rb * empno ；EMPNO ENAbtEMPMD ENAME73(i9 SMITH7Q92 FORD7499 ALLEN7&9R BLAKE7521 WARD7698 BLAKE7566 JONES7839

30、 KING7654 MARTIN7698 BLAKE7698 BLftKE7B39 KING7782 匚LARK7039 KING77S8 SCOTT7566 JONES7844 TURNER7698 BLAKE7076 ADAM577S8 SCOTT7900 JAMES7&98 BLAKEEMPNO EMMEEMP40 ENAME7902 FORD75&6 JONES7934 MULLER7782 CLARK妁W SMITHS7302 FORD36Q1 LIU7839 KINGaH02 Z卜ANh7859 K.ING1& rows selected.5QLi select empno, a

31、. ename亡口.enaine f ron eiap a hemp b where Kigr*b豆卩1。；EHPNOENAMEEMPMOCWAHE7 369SMITH7902FORD7499ALLE 制769&BLAKE7仞R1 Akf|7566JONES7039KING |blaKE?B3511 77S2CLARK7S3KING 177Bscon756&JONESTURNER7G9BBLAKE7376AE1AM577085COTT7960JAMfi7698肌*EMPNOENPfJOENAME79G2FORD756&JONESMILLER772CLARK豹胆SMITHS7962FORDi s

32、eoiLIU703?KING II 8M2ZHANG7839KING |16 rows如果a.上司号=b.id,那么这时，a的信息就是有上司的人的信息 （也就是有领导的人的信息） 也就是b表中的哥们儿是a表中哥们的领导。9题结果:SQL select a.empnio enp#.scenario etTipnameb.Einfin eiipN#, D.enarMQ emHnaiiD rrom pnp apenip b here mgr=b. euipno-；Ell 卩# J1PNAMLLHPP# LMI-HNiML北&9 bMHHFORD7499 ALLEN769 BLAKE7521 WARD

33、769S BLAKEJUlESr?839 KING7054 MARTIN7698 BLAKE7698 BLAKE7839 KING7702 CURK7139 KIM7788 SCOTT7566 JOWES7844 TURNER7G9 DUKE7S76 ADAMS7TA0 SCOTT7906 JAMES7698 BLAKEEHPF EHIP NAMEEPW-SF EFIPHN-ME加壮FOfiB7566 JONES7934 HILLER77U2 LLARKW99 SMITH%7992 FORDMAI LIU7S39 KINGM32 ZHANG?39 KING1& rws selected.10.

34、 修改第9题的查询，使得没有管理者的员工的信息也可以显示出来。SQL select a rempno emp.a芒仃日僅户 empnainePb.erpno emipfW (b.EriRne eripMuane from emp ?n.emp b where a. (iqr=b.eiiipr)D4+i ；EHPJt EMPNAHEEMPM EMPWAME73M SM11Hi9td2 FC KU7499 ALLEM7G9 BLAKE7521 WARD7698 BLAKE75&6 JUNES7S39 K1K7fi54 MARTTN7690 BLAKE76Rl AKE7839 KIMT7782 CL

35、ARK7839 KHMT7fia SCOTT7 5fi6 10NF57839 KIU(17844 TURNER7698 BLAKE7B76 ADANS7788 seemEHP# Er*PNAHEEMIPM EMfflN.AlE760 JAMESLAKE旳 82 fURLiS6b JCNE57934 MILLER77S2 CLARK尊ggg smiths79S2 FORDU90. UM?SJ9 KIMG002 ZllANG7839 KINC17 row? solccic.SOL select 曰.enpno erip-af. a .enane erapnane,b.empno enpHytjbr

36、ename enpHnane f ron emp a left outer jo in enp b on (a.mqr=h.區 up no；EUPi EHPWANEENP阳 EMPHhlAME7369 SHITHJ902 FORD7499 ALLEN769a BLAKE7521 TAKD768 BLAKE75G5 QHES7139 KING7651 NARTIN769S BLAKE托沁BLAKE用39 KIN7762 CLARK7339 KING778B O3TT7566 JONES7日站KINGTLJRNFR76Q8 BLAKE737& ADAMS77B8 SCOTTEMP# FHPNAME

37、ENP冊 EHPMNIflMF769S BLAKE79&2： FORD7565 JONES沖34 MILLtRJ 782 CLAftK的的SMITH%79&2 FORDLIU7S39 KINGdHANG?839 KING17 rowE selectea.4作业i查询出入职超过一年的员工信息st)l aiact empnu,S41, vruncfsysdata-hlraclAtP) dmyq 1 rwi mp vhereEMPNQ5ALDAYS7369 SMITH2G&118197499 ALLENwee117547521 WARD1?59r7566 JONES2975117137654 MAR

38、TINI125011534T6GJ9 BLJKE倔ullbS4T7E2 CLARK245911M5T7fi8 SCDTT3060ssas7839 KINGsees114&47044 TURNER150011554JH/fe ADA1S11GB9471EMPhlC EFiAhESALBAYS7900 JAMES9501146673G2 rORD30001146B7934 HILLER13GO11417qggg shtthnai9RWl LTU8002 ZHAr5rweeks from emp；2查询出每个员工从工龄有多少个周5QL select empnt)H enameal, trun匚f 5y5dNte-lil亡EMPNO ENAMESALWEEKS7369SMITH80016007499ALLEN160016797521WARDL25016787566JONES29751673754MARTIM125616477698BLAKE285016597782CLARK2450166