数据查询作业

1、班级：计算机1603姓名：林亚超学号：163406010404部分信息太长所以只截取了部分截图连接到案例数据库结果如下：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.根据人力资源管理系统数据库中数据信息，完成下列操作。(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出七区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与内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)查询入职日期早于其直接上级领导的所有员工信息。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)查询各个部门号、部门名称、部门所在地及部门领导的姓名。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)查询各个部门中的人数及平均工资。00Qgee00_9271564611(21)统计各个职位的员工人数与平均工资。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网SI-CLEBK2M27B51UEF165。