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