201258014009杨玉玲实验2_第1页
201258014009杨玉玲实验2_第2页
201258014009杨玉玲实验2_第3页
免费预览已结束,剩余1页可下载查看

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

1、实验序号2实验名称SQL语言基础实验过程及步骤SQL>desceznplayees;NullableDefsultCcmcrientsNa.msTypeEMPQYEZIDNUMBER(fc)1Prirria.rykeyoFIRST_NAME'20YFirstmamsofLASCNAMEVARCHAfl2(25Lastnarne口!EEWATLVx?_RCHAR2'2E)Ena.ilidn兰t.PHONENUMBERVARCHAR2(20)YDhciriEnLuiLDerHIRE_DATEDATEDatewhent;?ieJOBTDVARCHAR211C)Cu.rrentj

2、otoj口bstaibLe.區Eiotnullcolumn.SAIlAF.YICTMBER(Sr2YMciTuthlysalar十AnnrabfiamFekkOsKX"ctf-hniTi1-cmtv口n*13kF?TW114T!1connjConn&ciedlocracKJigEnt&rpElKEdilQn11.202OConnectedaahrorcl1. 登录Oracle,进入HR方案,使用DESC和SELECT命令查看employees,departments表的结果和所有数据;connhr/hrorcl;descemployees;descdepartment

3、s;select*fromdepartments;select*fromemployees;SQL>descdepartinents;NameTypeNullableDefaultcommeEPA=ITMENT_TDKDMEER)PrimaDEPARIMENT_N71MEVARCHAS2(30)AnotMarketingrPuichasirjgTHunisiii且esources>sii-pping,IRelationstFinance,nnTAuc:ountingMANAjER_IDNUMBERYMdGig匸OCATIONIDNUMBERJYLscaSSQL>select*

4、fromemployees;EMPLOYEE_LDFIRSWAMELAST_NAME100StevenRing101NeenaKochJir102LexDeHaar103AlexanderHunald104BriiceErnet105DavidAustin106ValliParaballa107DianaLorentz10SNancyGreenberg109DanielFaviet110JohnChenSQL>select*f工亡mdepar'meritstDEPR7ME54T_IDDEPARTMENT_NAMEMANAGER10AjiminigtratiOfl:20Macket

5、ing':3 0Purchasin石4 0HmnanResonrcesj5 3sMppirq60IT70PublicRe1a.-iona«6 D341口吕&0Exacutive1QUFinancG2. 创建一个名为manager的表,它由编号EMPLOYEE,名FIRST_NAME姓LAST_NAM,邮箱EMAIL,电话号码PHONE_NUMBE部门编号JOB_ID,薪资SALARY部门经理编号MANAGER八个属性组成;createtablemanagerasselectLAREMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_

6、NUMBER,JOB_ID,Y,MANAGER_IDfromemployeeswhere1>2;W叶trtibkimpifEH阳口阳时阳忙禹田艮卫乂SLt险血圧川trm闲沪初肚幣;SLdecnntfET;IikTntJiill疝EliefMltCwffltjEOK】61?亚二nimu(3J刑鲨m咼血DEL頊20?mitjo礎血wm辭tWiJEF.IBOf'16?3. 向manager表中增加"性别”列:altertablemanageraddsexvarchar2(2);SQLaltertabLenariageradtlsesVETchaTSZj;Tablealtere

7、dQLydtscidnageTN-mcTypeffullablnDtfaul1C&j,uen15EIFLDYiE.IDYFIRST恥METLASTHANIVAECSA?2(25)EIAILVaEC3AS2(2PHOH2SUJIETRYJOBJDVARCUGSdO)SALARYraBHRCe,2)yIMaNaCEJLIDNUHESR(fi)Y旺KT4. 查看jobs表中每个工种最高工资和最低工资之间的差距,把单位换算为万元;selectJOB_TITLEas工种,(MAX_SALARY-MIN_SALARY)/10000as工资差fromJOBS;sclectRBTniTuHfr*Om_

8、SALAJlY-IIiN_SALAFlV)/10000as工挠差IrcaJOBS;工并工資签t2ArtniniztrstioiriVicePresidenil.bAiilnistratLO'iitseLstaat0i3FLutLn.csV.ujasier0.T8Ai-AuunlaRt0.换ArciMiffitinE:0.7BFutillcfircountant0.+SSalesftaiiagej:'1SalesF.epxes:entilivE0.6Pizr亡ha曹Irtg.Haniger0.7Furcbo'ir.2ClerkQL3StcLItinaFcr畀3Stcfcr

9、iertX3ShippingClerk0.3PrograniirTr0LEHarJceilngManager010MketicibReprjtailvs0.5EIceourcesRepresentstiveCHSPublicRrJ即Repine刍上nt冷卜疔/v*白5. 编写一个查询,查询所有第二个字母为“r”的国家的名称;selectCOUNTRY_NAMEfromCOUNTRIESwheresubstr(COUNTRY_NAME,2,1)='r:SQL>5rlrctOOtHTTRYjAYEfron(TiUlTTEIESrhrresulistr24.1>=;COJNTRV

10、_t1AHnEraxlLTranca6. 查询出所有属于IT部门(DEPARTMENT_ID=60,从事过程序员(job_id='IT_PROG')工作的雇员号;selectEMPLOYEE_IDfromemployeeswhereJOB_ID='IT_PROG:SQL>selecTEUFLOTEE.IDIroi.eir.plcys&svliere;EIPLOYEE.ID103101105106107使用GROUPBYF句对薪金记录进行分组,使用SQL函数计算每个JOB_ID的平均薪金(AVG)、所有薪金的总和(SUM),以及最高薪金(MAX)和各组的行数

11、;selectJOB_ID,avg(SALARY),sum(SALARY),max(SALARY),count(JOB_ID)fromemployeesgroupbyJOB_ID;卜xele匚tLj鼻审呂-EJLLAILY)j药lIb?blazALAfc1?)?cowittl)frwEB.pl<oreEsgroupbylb,JVC(SILAEV;!sin(sujnT)uxfsujiL巧门河盯口期jp;itrU'KBTGOWOO5MOOD1200013000I1CAD3OITiTssoo8300ESOiiJIET1X1ir2tfUgbcuoK2UW5FirTAR11DOOimoa1

12、1nonI丄Disr4440440044001JlD悴lDOO颁IJO1T0U02EKCLERE321564300<2011闕Fl_4jccoirrr7K&3W0CiHOO5FIKrl1200012I.HJ0120001fl!CLER£2780moo31005站1“12300iLlOODBOQD5IKI1K130001MOO1EOOOtPIUP1UWJUlwiuoluouvIADPRESH4UQQ24呦24000IEJLJLEF8Z5025050CI11500SOIK吐时IsWJOboon1盯LfHK27SB5旷肿3600的HEREPESOUB5Q0b5Q017. 使用

13、子查询查看所有部门在某一地区(1700)的雇员信息:select*fromemployeeswhereDEPARTMENT_ID(selectDEPARTMENT,fromdepartmentswhereLOCATIOND=1700);select*frfli<mplDfiPTKJTT.IDLaI_NjJLIelectABP必丁世MT_】Dfradepi.rtimesvhereLOCuHCi-lElJlELt眩_血仕LVOKini.EBJJICUS.123.A5fr?iw-fr-1?Ll梅調H押SIS.131.IBM1Mfr9-21L»2LesDe.LtEtaAl515.123

14、.45d谕U3LIBItrierCrpTfibtriORntW1他卜IT“9DuilelPiMletLiPAriETS1Q.124-13G9i=ei-fr-i6110JdmChziiJSiEB啲杵卜刘U1Jinifllirr-a1SCWRJH轨皿1WT-Sr-3QLiz_|g亡flaruElUman.JIU5£1A1UB-盅>L曲195E-3-711SLu3>刊旧LTOIT51S.124.<5-7lMfrl2-T114DenliFAPHFM.1刚-nLIBbleiafiderKPw-bKHX曲乩U1L4D42Eli血515.1即-*网1W-12-2-11?Slgnl

15、STcrlAS919.12T.49641旳T巧烈nsCLftiG-HTilTRiZ313.12T.4B6DIWS-ll-J!Eaxezi-llLflniTEELiX_lEJi*515.127.45663toJWWJ倔JIHfclFSns.133.tm1阀T仔ITShelleyHL£gliisUIil.1251E.122.90&:ilWi-fr-7niiiuGitIs利im519.taxaiaiI'rtfi-H】忌esiesielectted9.查询将EMPLOYEE表和DEPARTMEN表相连接,生成一个笛卡儿积:selecte.*,d.*fromemployeese

16、,departmentsd;SQL>selecte.+,d.+fronemployeese,departnentsd;|EIPLOYEB_IDFIRST_NAltELAST_NAMEEJIAI100StevenKingSKIN101NecnaKxhhmrNKOC102LexDeHaanLDEH103AlexanderTiuiiolaAUUN104BruceErnstBERN105DavidAusrinDAUS106ValliPataballa¥PAT107DianaLorentiDLOR108NancyGreenbergKGRE查询EMPLOYEE表,将薪金大于本职位平均薪金的

17、雇员信息显示出来:select*fromemployeeseleftjoin(selectJOB_ID,avg(SALARY)asavg_salaryfromemployeesgroupbyJOB_ID)bone.JOB_ID=b.JOB_IDwhereSALARY>avg_salary;SQL>vvlvci*eielcywBtleftjln(1罄1世弋fmnpl枷和trg&JOBJDAbmiJ06_II>dJC8JD.mImh:*Ironel#ftjoinava(salary)asava_salarjtfiEKtlOY££_二口Uk£T

18、_J4AKl103A-ex-an-zer日utwIdAHUSOIiD1C4Biru-o=Efzat.EkERSIS?109Dam«lFaTifiT.DFAVIETno*ghnChc-nJCHEK115Ala-K-sri-lsr柿gAKEDO1.(Shell丄珀丄da£BAl.Di111SialTabiAESTMIA812UMaTth*wH«i4aMWET531.-3E17pnn1CDTDOUST.HMILIMlilLfHL4B_|TUHmHRlLUhllJtHJDiimIKIBS血iW聞创TJiD_FflIff10:NwmaE-diiar5te.latF-51点i?吃】AD7FlOtLeftvIninLtU-IMRHS阳純-叩j03:AL«xandHrBlxk;-rl&HnKLXMIX42.45£?lWu-1-SIT_FBiK-04IfiUAIrrwiEEESSTW.tiX切杆I匸PROSID:-DavidJmcSiiDADHTCKESCLdZLl怦ISW-C-Z'5IT_FEIK-10*UlllFfl*:«&

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论