數(shù)據(jù)查詢作業(yè)

1、班級：計算機1603姓名：林亞超學(xué)號：163406010404部分信息太長所以只截取了部分截圖連接到案例數(shù)據(jù)庫結(jié)果如下：IlinUESiEfr-OraceVMVirtualBoxSBC:Wirdows5jfstem32cmd.exE-sqlpliJ5/sssysdba：|二連接到：Dpac1eSafabase11grEhtepppixeEditinn11.2.H.1,fl-fi4hitPi*ndiiiztianKiththePartitcmingr-OLAP.DataMiningetneiRealAnpHeationlestinsrnptions：SY?LlnyachaaCDnnhrhr；己齦

2、HIR&1iinyacliauiselcct*shnwhsrpUSER頭TF1HR?LinliaoCwfranenuloveestajieredeijftrCnenL.ld=1031.根據(jù)人力資源管理系統(tǒng)數(shù)據(jù)庫中數(shù)據(jù)信息，完成下列操作。(1)查詢100號部門的所有員工信息。Select*fromemployeeswheredepartment_id=100;L(DFIRSTNflHELASTNRHEEMAIL用8MncyGpeenberuHGHEEhtiE1.09DaniieclFavletDFftUIET1LHJohnChenJGHkN1111nae1GefIBdABRA112Ju毋七tld

3、LmuLelUrindUkJMURHAN113LuisPDLFOPPEMPLOYEEHR(?llnachaosetpagesizeZ0S；-LnjAC:han3ert*tpomEmpliei/rcs(jifvedRpAi*tmfnt_id=1flfl；已選擇。幾(2)查詢所有職位編號為“SA_MAN”的員工信息。Select*fromemployeewherejob_id=SA_MAN；R(?Linvacbanselect*franenuloveeswherejob1A=1SANflNJ；iHPL.OEfc_ll)FlUSUAMELflSI_NAHEhMAlL145JolinRussellJH

4、US豈EL14GKarenPartnersKP由RIMER147AlhePtoErrazuria科EHR曲皿R14RCdLfihirAUItCCftHBRAIIl-!9EleniEZL01KEVIRP1inyachdubENQ1LPMONF_NUMBFR1HIHF_DftTF,TOB_TIJHUESELWll,4l.l314,4ZZbBWL-1E月SflJIANMPflRTHFRUH1.44.144.4672即西-1月-KSA_MfiNAERRAZUR011Tl.1344.429278103月05SA_MAMCCqnUKAU團1.4%1344.61926g15-1W-W/SfiJIflNFZF

5、.OTKFV(itti.44.1344.4?9B1ft29-1向-mSA_HfiN(3)查詢每個員工的員工號、工資、獎金以及工資與獎金的和。selectemployee_id,salary,commission_pct,salary*(1+nvlI(commission_pct,0)Fromemployees;11HHiinIectenpLoyccid.aalary.camision_ptti-salarj/*n2frun虺-iipluy匕出蘭；inriQVELIDMM附OOMniCSIOHrciSAU1W*=2000;己法理iiIt-HWlinaclwnM：lcctatIrortcmplDy

6、ccswherecnplapcc3Tjob-id-job3-joti-id2aodJOH.TLtLENWUHStock頻1也墾日人具公岫匕畫e1)320皂0md.電。-對Iplus./a(6)查詢有獎金的員工的不同職位編號和名稱。HRlinyciciaoselectdistinctjob_id,job_titlefronjobswherejoh_idin2(selectJob_idlFrowemplospecswherecominissicn_pct0IJ0mi11arcoiwlhinnpct1兩;EMPLWfEE_inFIBT_Nftl1ESgTNPIEEMAILPHONEJhlUIIBER

7、IHrfSteyenMiriyKKlHG515iJ23.4SG7IMNmnnAKnnhhArNKOCIIHIAH182LaxDoIHaan&PEHA曲N515Jm56?103育LexandeHunoId育HUHOLD5S6.423.456714EeeEnlistBERKSt59B.423.46SIM,DelcidAtuilfiMiiyiiN59B.tt3.4S691期Un111RatnhftL)nVVinRAb197DimaLointzBbQREMTZ590.423.567IBSHdnc*GaenVjvr-i4NGREENBE515.124.45；691BF9Danie1FavietDFAUI

8、ElSIS.124.416911HJohnChenJCHKMmz4.4g111lanolciarraIBClflRHfl中mny112JguHixnua1Uvmn州URNANEL5.124.44E9113L皿PoppLPOPP515.124,4567114DeniRaplmeLjf鼬府yHE由L5t5,i2?.4S61115fllexflndet*Mh(MAKHODj&U,居E2116Shall!Balrfa515.127.4563117SlalTobi也仁STOBIAS515.127.4S64HsGiijlHinurviGH1NIIR0515.1T7.45GE11/MilEDCoIrwm依l

9、ekKCOl.MEHfl515.127.4S6612：BHa.-tthcuWeisaIWE1SE&SB.123.123121AdaniFrippDFR1PF&se.123.2234122PdljMlliXduflinyPMAUFLIHfiES.123.3234VJ1IhMltnIIt11VknliitUOLLNAN&Sa.lS3.4234寸;MKfivmHaifrypfl；KHOllRfiOSIII180WinstonWIAULOR64帕7.靖找i191.FlediUi*JFLEAUR650472877182MarthaBulllv4nMBULL1VA6.507+SS78183GiratdGG

10、&QMI6M5仍能79184HanditaSELrcli-nndNEARCHAH6S6.599.1876IBSAlexishillADiLL65乩59九翻76IB*lllingKFJPtLLIWtGftbrioAGnilFtTO1H81dlyChrnnyKCHUMGGG.MIkJH7G1H9Jc-nni.fi=r-DillybSH.hdS.2H9A19M1imotKvGa七es:IGGIESH4M上削.3。用1VR4nddllFei*klihcHFJEHK1出七區(qū)Lb，b.油用inSetrdhBfrllSBELL8.01.1876193BritneyEwviwtcBEUERETT6.501,2

11、8768日mqIMeCainNCCniN6E9.501.3S76195UancoJonBEyjONES6&e.591.4876196AlanaWalsltflURLSH6Se.S97.9811KeMing曲壯也能，HFEENEYf八卯部19*DLonold(0nnnn11DQGpwn.皿UEHT八MOJFQqDoing1flnGrnntDGRAMTW雨7311+f,2WMJ-E-nnifer-LJhnXeiiJMHaU洲S15-123-44442H1Mit：iaE=lHavts/te-inflHAHISTE51A555芝H2PatITAVM3_193_AA46203SuvdllMrtVFlvS

12、MRUR1S515.123.7777BrtvrHBER515.123.sees26SShelleyHI卬h士n號HIGGN515.123+BI3O206MillianGiAtzWGIEIZ515.123.BIBIb3t哂.聞R1aliinynflwm?(8)查詢員工(巾rst_name)中不包括字母S的員工。Selectfirst_namefromemployeeswherefirst_namenotlike%S%;JELLJushuuRandallHazelLuisTi*ennaDen1Hixliai勺工JohnHcindlcaiIsnae1JohnLindseyUilllARPut&kJo

13、nahlionU111辟LullPeterOliverJoseManuelPeterClataAIcLTkDLMatt-lfeCVFJenniferEleni已選擇94彳丁.XR0linrachao(9)查詢員工的姓名和入職日期，并按入職日期從先到后進行排序。IRGlinachAOselectFii*st_nanie,last_nanehire-datcFran2enplopeesor*det*Jbyhiie-dlate?IRST_HfiHELASTNftMEHrRE_DftTEfllllainleTinann:5與內(nèi)nCelleyniellanc5j2n*ayaniilexandei*Ite

14、uenlensheJenniferrennalanditaFanett:?；arhlichael(attlcklienDeMaAnGletzBaeiMvrisH199insFauietGreenbergR建phdQlyKauflinsrKhoaKingfLadwigWhalenRajsSajrchandMinorBellHaptsteinSullvAbelMallin0月月3022222200-RMAw-00-月月月月月2r一66681_777677000JI0HanceJones17-3E-07DanlieGveene19-S-07Elisabetli24T后-07IJOlon10-4.E|

15、-B7BruceErnst21-5戶-07KinhereIpGp-ant24T耳-07Donald1121-G-07lavtliaGulliuan07Karen10-S|-07GeraldCaribrault15-10,-07KeuinHogg。lG-11-07OliuerTuuauIt23-11J-07Luis07-12-07KiGee12-12月T7Ndrlda11Pei*ins19-12-07CliarlesJdlinson國4L月-08Douglas1A1后-081atteaMxirvins24T巨-08EleniZlot3selectairstjmc,b.fii*st_nari&Fr

16、onemployeesa2Joinemployeesb3onb.emplo；ee_ld=.nan-acr_id；FIRSTJIfttlEFrBST_NfiHEUillLamGeraldLi?aGeraldSunditaGeraldTayleiGerAldHa.F-i-L&anGeraldElizabethGeraldALexClaraAlbertoHatteaAlbertoDavidRlbertoDanidleAlhertofleitAlbertoSundapAlbertoMsindLtAAdamTJ丁RdamTdhrzLsitoplicrJolineJohn卜IdlTQ*上春JoImBaud

17、LJofiknSteplieiiBlicuitrkJolinSlhQnifc口TntitigkiRlirint苴GruclGliant；aJtfnilkR林M刁nf7dUnrwDSticinaiSHantriSan*aiStianCeiUzLriiB七0nMa七it彰口3ljJuiliciHe佇itliuvj1neEa七七Ai日s*trUUUl1MClLtrllCVFJAF1OOriiOLittllOWGli*at*dM.anzlieuJeanMa.七七】1匕VFranmniEl*niJuuKEl6ii1ChaileElen工AljFSisaEleniICWEJbokQ工9Elen士T1ILn

18、FlF?nF已選擇2E行.HUI/J:in(11)查詢?nèi)肼毴掌谠缬谄渲苯由霞夘I(lǐng)導(dǎo)的所有員工信息。IIRPlinsFachaoelect*fronemployeesawherehii*e_datblopee5buhere3h_efnpLosFee_d=a.nana.ger_idJ；EMFLOEE_IDFIHST_MAI1ELAST_NAMEEMAIL102LexDeHaanLDEHAAN105DauidAastinDAUSTIN108NancyGi-eenbevgNGREENBE109DanielFavietDFHUIET114DenRa.pliaelyDRAPHEAL122PayanKaufl

19、ingPKAUFLIN131JanesMarlouJ口HRLOW137RensheLiduigRUDWIG141TvennaHajsTRAJS142CuftisDaviesCDAUIES143PandalLMa.tosRMATOG144PetEi?UargasPUARGHS156JanetteKingJKING157PatrickSullyFSULLV158AllnticEvenAMCEUEN168LisaOzerLOZER1G9HapvisanBloomHBLOOfl170Tayler1FoxTFOX171UllliamSnithUSHITH172ElizabethBat不EBATES174

20、EllenAbelEABEL175AlissaHatton妹HUTTON17ftJonathonTaylopJTfiYLORrat144PeterUargasFWRWSGSfl.121.2MH15fiJanetteHingJKINCUi.44il345.42Ht157SullyPSULLY011.44.134S.f292615RAIIaaHi：FnAMC1-WFHR1L44J3咨M”26LisfiOarrrL困RAll.44.13.9292it9HarrisonDloOilNBLOt1mi.44.iW.8292GladlerFnxIPOK011.134.722171WllliMiinithus

21、miHRll.44i1343.62936172Rlgh4fBatnoEBftTlS011,4401343.52926174EllenAlbtlEABELeil.44.1644.42926175Hutton曲HUTTONeii.44ai&44a429l2617f.JnnatlianIAj/lnrJTflYLOJI011.44.164.429261尸，JackLiuiingsLonJLrULNiGStill_44.i!b-M.42V2t178HlnbereljiCibantMRRN1HH狼煙171?InnslnltiniJtonGilOHNSOIMR11.4中11蝙44彳科92八1S1Nancli

22、taSarchAndlMSARCHnM65年.5抑,18曾185AlexisBullABULLG50.509.2876122BsllSHELL6S0.5ei.l87193BehiiHyEuepeltBEUEHTTt50.5tt.lSW19GA14114UalihAWALSH650.?；H7.9811197K*rvinFHrniryKFfEHFV65R?J?H22IflHDonnIdOGnnnel1MGdNHFL65.7.33JcnniFeirWh.iIcnJUHALBIN515,123,1414293SusanHavrisSFinUBIS515.123.77772B4Hlei-iiiannBa

23、oi*HBIIER515.123.B8Sg2asEbwll0里HigylrxuHICGENt615.123.M0(12)查詢各個部門號、部門名稱、部門所在地及部門領(lǐng)導(dǎo)的姓名。HUeiinyachaoselectd,department_idddep4rtneitt_naRe,d.locatior_idefirst_nane2 frHFAHrHtHI_NAnfcLOGATJON.IDFlflSl_NArtK70PublicRelation零2700HeniMim50Shipping1500Adam1UUFinance1,演HNdney20MarLctInsc1800HIcJlOlC1118Acc

24、ounting1700Slieliev6HITAlexanderUBlLxecutiue17UMKteuen40HunanRcsouimccsSusetn30Parchasinc(1700Den80Sales2500John1Adfioistriat：ivnJennifeT已詵擇11行。(13)查詢所有部門及其員工信息，包括那些沒有員工的部門。lieALuisJoseMnue1JohnDanie1Isnae1NancyWillianShelleyHRPlinyachaoselectdepaptnenit_naine,first_nanefromdepartmentsd2leftjoinempl

25、oyeese3ond.departnent_id=e.department.id；DEPARTMENT_NftrtEFIRST.NftMEAdninistrationJenniferMapketingPatMarketincjMichaelPupchasingSigalPurchasingKapenPurchasingShelliPunchasingDenPiihcIi#欠ingfitiRxandRi*PurchasingGuyHunaniResourcesSusanShippingKevinShippingJeanShippincfAdamShippingTimothyShippingKiS

26、hippingGivandShippingDouglasShippingUanceShippingPayanShippingRenskeShippingBr*itneyFinanceFinanceFinanceFinanceFinanceFinancetccountinsAccountinsTreasuryCorporateTaxControlAndCreditShareholderSeruicesBenefitsManufacturingConstructionContractingOperationsITSupportHOCITHelpdeskGovernnentSalesRetailSa

27、lesRecruitingPayroll已選擇122行。IRPlinyachaoMSalesSalesSalesSalesSalesSalesSalesSalesSalesSalesExecutiveExecutiveExeGutiveFinanceFinanceFinanceFinanceFinanceFinanceAccountingAccountin3(14)查詢所有部門及其員工信息，包括那些沒有還不屬于任何部門的員工。HFQlinywdanlecteFipstjia.ne,ddepaitnert_nan*eFromemployeese2 leftJoindeprtnentsdon3 e

28、.dejpartnent_idtl.depai*tnent_id；FIBST_NfimDEPflRTMENTJIAnEJenniferAdninistrationFatriaiketinqMicJi-ae1Natketing-KarenPiipchasinsrGuyPu.*cliasingSigalPureliasinffSlielliPllpchasingnilexnderPiip-chawingBenPulicinsrSu5anHumainResdniecesDj”日lasShjLppi_nDon*IdShippings(15)查詢所有員工的員工號、員工名、部門名稱、職位名稱、工資和獎金。

29、HRPliryachanleere.enplnyeeidFe.firstnams,ri.2e.salarye.salary*e.connission_pctdepartnent_name,j-joh_titl&,JOB_TITLE3fromdepartnentadjoin4ond_departnent_ide.Sjoinjobsjanj_job_idEMPLOEE_IDFIRST_NflMEemployeesedepartnent_ide-job_id;DEPARTMENT由ME2&QJennifet*AdninlstpationAdministration202PatMarketinfMar

30、ketingRep,201nichaelMarketinrMarhetinMana114DenPurchasingPurchasingNan119KarenPurcliasinyPurchasingCie118GuvPuvchasinqPurchasingCidi84SalefiS：缶1總wRejF&nitatiue163DanIelieSalesWalesRejirtfLfeiitdCIve179CharlesSalesS&1C5Rewestntatioe161GcirAthSalcaSalesAcpreaentatIt/c1bHLouisealesIdle*Heppesent&tive1E

31、9LWnde”ale&Sale?RepresentatIve158AllanSdleu&dlesRepreeiitdtlvte157PatrickSalesSalesTtepresentatlk*e15GJDinettealc3Sale3Rcpresentativclb5Olitei*Sale號寫Keppesnt&tIve149EleniSale&SaleManager145JahnSalesSaleshanageF14fcKarenSale5SftlesManager147AlbcrLUSalcSalesHLn.iiiacjEr114MGeraldSale等!alesHanaggr101Ho

32、onaJEXGCUtLVCDdniinilotrationUicbr102LexExecutlueAidmlftitrationViceFIftflSteueriExecutLuePresident109HanuyfliiancePIriaiiiL；eMianuyez*112JoserlaviLie1financeAccDiintanitHiIBRAO1financeflh心cown七日口七113Lalsfinancefliccdulfitaut1H9DanielFinanceftccDiintanit119Julkflfinduce：Aiccuuiitaint205Shelienficcou

33、ntingAccDiintinigManagfer2&i,UillianCGOuntingfPub1ilc力ccouintant已選熱甑行.WMlinuachaiDi(16)查詢至少有一個員工的部門信息。1RPlinvchaoselect埼frondepartmentsd2seLeetdepart:ment_idft*omenploees3department_id.havingcau.n；DEPARTMENT_IDEPftRTMENT_NflMEvjhered.depArtmcnt_idingr-otipbyMANAGEB_ILOCATIOND50Sliippinselect*femploy

34、eesulieiesalavy2 CselLectsalaryfi*omemployees3 ijhei*eemployee_i.d=108；未選定行HRP11riuarhrifiInnl;salrirufynniRmnln(18)查詢工資高于公司平均工資的所有員工信息。IIJtlin5/fi*omgrip工oyee=s：3FMFIX)YEEDFIRSI_MfinELftST_NAME100StevenKincj161NeenaKoclihai12LexDeHaan103Alexander-Hunold168NancyGreenberg169DanielFaulet110JolinChen11

35、1tsmaelSciaiia112JoseHanue1Ui-nan113Lui7Popp1HDenRapliely120HattlievjUeIssJ芝士/dwmFr*zLpp(19)查詢各個部門中不同職位的最高工資。MR(?lny&chaoleetjabdmaxCaalAJfi*om2emplov&es卬AOmpbyjolb_idl;JOBDHH睽SMRRYIT_PROG?000AG_T1GR1Z98SAC_ACCOUMT8300ST_T1AM828011900flD_fiST4400ftD_UP17300SH-CLERK4200PI_ftCCOUMT9000PI_MCR120OdPU.CL

36、ERK3100SftJlfiN14900HKJIftN13000PRmEPisaeaftI_PRES249B0SftJIEP11500MK_REP6000ST-CLERK36鮑HR_KEP&SO0已選利9行.HR(?LinyachaoHE?1in4/acliciD5seled：deparitnentidj-couri*、ravg(sa1-ar)2fron3groupbydepArtncnt_id;已選擇12行.EPRBTMENT_1I)COUNT*?AUG(20)查詢各個部門中的人數(shù)及平均工資。00Qgee00_9271564611(21)統(tǒng)計各個職位的員工人數(shù)與平均工資。68601.3333

37、3641501319333.333325601100602101G4453475.55556348955.882351$055%0144碗inj/acliao君lectjob_id,conntenployee,avsf2froR3groupL*job_id；JOB.IDCOUNT(EMPLOEE_IDRUGIT_PKOG55760rtC_MGB112908ftC_ACOOUNT18309SInfiN57280FUJIAN111000ftJ)_flSST14400HDJUF217B09SH_CLEBK2回3215PUCC0UN757920FI_MCR112908PU.CLEPK52789S由JAN5122H0MK-MflH113000PJIJEP110000ftJJBES124000Srt_BEP30835。1KJEP16網(wǎng)SI-CLEBK2M27B51UEF165。