数据库sql查询语句上机练习1习题结果单世民

1、习题1请根据给出的数据库表结构来回答相应问题：DEPT(DEPTNOINT,DNAMEVARCHAR(14),LOCVARCHAR(13);EMP(EMPNOINT,ENAMEVARCHAR(10),JOBVARCHAR(9),MGRINT,HIREDATEDATE,SALFLOAT,COMMFLOAT,DEPTNOINT);BONUS(ENAMEVARCHAR(10),JOBVARCHAR(9),SALINT,COMMINT);SALGRADE(GRADEINT,LOSALINT,HISALINT);其中表中包含如下数据：DEPTg:DEPTNODNAMELOC10ACCOUNTINGNEW

2、YORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONEMPt:EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO-；7369SMITHCLERK756E1980-12-1700:00:0a000SOONULL202_P7499ALLENSALESMAN76981961-02-2000:00:00.00016C030030_3_(7521WARDSALESMAN7E981981-02-2200:00:00.0001350500304J75G6JONESMANAGER7B3919B1-04-0200:00:00.00029

3、75NULL20_5_7654MARTINSALESMAN7E931981-09-2800:00:00.0001250140030_6_769BBLAKEMANAGER7B3919B1-05-0100:00:00.0002350NULL30_7_77B2CLARKMANAGER7E39iaei06090000:00.0002450NULL10_8_77E8SCOTTANALYST75£61987-06-1300:00:00.0003000NULL20_9_7839KINGPRESIDENTNULLiaei-ii-i7oo：oo：00.0005000NULL10107844TURNSA

4、LESMAN76981981-090800:00:0a0001500030117876ADAMSCLERK77881967-06-1300:00:00.0001100NULL20127900JAMESCLERK769319&1-12-0300:00:00,000950NULL301317934MILLERCLERK77821962-01-2300:00:000001300NULL10SALGRADE:GRADELOSALHISAL17001200212011400314012000420013000530019999BONUS：无数据根据上面描述完成下面问题:（注意：注意保存脚本，尤其

5、是DD即DML以便进行数据还原）DDL1 .写出上述表的建表语句。止匕外，在DEPt创建名为"PK_DEPT的主键约束，在EM表上创建名为"PK_EMP的主键约束以及指向表DEPT的外键约束"FK_DEPTNO。命令：CreatetableDEPT(DEPTNOINTAltertableaddconstraintNOTNULL,DNAMEVARCHAR(14),LOCVARCHAR(13);DEPTPK_DEPTPRIMARYKEY(DEPTNO);createtableEMP(EMPNOINTNOTNULL,ENAMEVARCHAR(10),JOBVARCHAR

6、(9),MGRINT,HIREDATEDATE,SALFLOAT,COMMFLOAT,DEPTNOINT);alteraddalteraddDMLtableconstrainttableconstraintEMPPK_EMPPRIMARYKEY(EMPNO);EMPFK_DEPTNOforeignkey(DEPTNO)referencesDEPT(DEPTNO);2 .给出相应的INSER格句来完成题中给出数据的插入。命令:insertintoDEPTvalues(10,'ACCOUNTING','NEWYORK'insertintoDEPTvalues(20,

7、'PESEARCH','DALLAS');insertintoDEPTvalues(30,'SALES','CHICAGO');insertintoDEPTvalues(40,'OPERATIONS','BOSTON')insertintoEMPvaluesinsert(7369into'SMITH'EMP,'CLERK',7566,'1980-12-17',800,NULL,20);valuesinsert(7499into'ALLEN

8、9;EMP,'SALESMAN',7698,'1981-02-20',1600,300,30);valuesinsert(7521into,'WARD',EMP'SALESMAN',7698,'1981-02-21',1250,500,30);valuesinsert(7566into,'JONES'EMP,'MANAGER',7839,'1981-04-02',2975,NULL,20);valuesinsert(7654into,'MARTIN'E

9、MP,'SALESMAN',7698,'1981-09-28',1250,1400,30);valuesinsert(7698into,'BLAKE'EMP,'MANAGER',7839,'1981-05-01',2850,NULL,30);valuesinsert(7782into,'CLARK'EMP,'MANAGER',7839,'1981-06-09',2450,NULL,10);valuesinsert(7788into,'SCOTT'EMP

10、,'ANALYST',7566,'1987-06-13',3000,NULL,20);valuesinsert(7839into,'KING',EMP'PRESIDENT',NULL,'1981-11-17',5000,NULL,10);valuesinsert(7844into'TURN',EMP'SALESMAN',7698,'1981-09-08',1500,0,30);values(7876,'ADMAS','CLERK',77

11、88,'1987-06-13',1100,NULL,20);insertintoEMPvalues(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);insertintoEMPvalues(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);3 .将所有员工的工资上浮10%.然后查询员工姓名、薪水、补助（emp.sal为工资，讷补助）命令：updateEMPsetsal=sa

12、l+0.1*sal;selectename,sal,commfromEMP;结果:ename：salcomfn1SMITHE350NULL2ALLEN17603003WARD13755004JONES32725NULL5MARTIN137514006ESKE3135NULL7CL4RK2695NULL8SCOTT3300NULL9KING5500NULL10TURNER1650011ADAMS1210NULL12JAMES1045NULL13MILLER1430NULL单表查询4 .查看EM医中部门号为10的员工的姓名，职位，参加工作时间，工资命令：selectename,job,hireda

13、te,salfromEMPwhereDEPTNO='10'结果：enamejobhiredate1CLARk!MANAGER1981-06-0900:00:00.00024502"kingPRESIDENT1981-11-170Q00:00.00050003MILLERCLERK133201230Q00:00,00013005 .查所有已有的职位，要求去除重复项,命令：selectdistinctjobfromEMP结果：iob1 jANALYST,hnai>>!rami2 CLERK3 MANAGERAPRESIDENT5 SALESMAN6 .计算每个

14、员工的年薪，并取列名为SalaryofYear（emp.sal为员工的月薪），要求输出员工姓名，年薪。fromEMP;命令：selectename,sal*12as'SalaryofYear'结果：enameSalaryOfYear1jSMITHj96002ALLEN192003WARD150004JONES357005MARTIN150006BSKE342001CLARK234008SCOTT3E0009KING6000010TURNER1800011ADAMS1320012JAMES1140013MILLER156007 .查询每个员工每个月拿到的总金额（emp.sal为工

15、资，empcomiife补助）。（提示：isnull（ex1,ex2）表示如果ex1为空则返回ex2）命令：selectename,sal+isnull(COMM0)totalfromEMP结果：enametotal1SMITH8002ALLEN19003WARD17504JONES29755MARTIN26506BLAKE20507CLARK2450eSCOTT30009KING500010TURNER150011ADS110012JAMES95013MILLER13008 .显示职位是主管（manager）的员工的姓名，工资。命令：selectename,salfromEMPwhereJO

16、B='manager'结果：J_&narnesalJQNEE29752BLAKE23503CLARK21509 .显示第3个字符为大写O勺所有员工的姓名及工资命令：selectename,salfromEMPwhereenamelike'_O%结果：enamesdjll-4111HTUHTSUI+叫1:SCOTT;和皿Mil(命令：selectJOB='MANAGER'10 .显示职位为销售员（SALESMANK主管（MANAGER勺员工的姓名,工资，职位。ename,SAL,jobfromEMPwhereJOB='SALESMAN

17、9;or结果:enamejob1ALLEN1500SALESMAN2WARD1250SALESMAN3JONES2975MANAGER4MARTIN1250SALESMAN5EL4KE2850MANAGER6CL>RK2450MANAGER7TURNER1500SALESMAN11 .显示所有没有补助的员工的姓名命令：select结果：ename1 ；SMITH：2 JONES3 BLKE4 CLARK5 SCOTTEKING7 ADAMS8 JAMES9 MILLERenamefromEMPwhereCOMMisnull;12 .显示有补助的员工的姓名，工资，补助EMPwhereCOM

18、Misnotnull;命令：selectename,SAL,COMMfrom结果：enarnnecomm1jALLEN1S0O3002WARD12505003MARTIN125014004TURNER1500013 .排序显示所有员工的姓名，工资（按工资降序方式）。命令：selectENAME,SALfromEMPorderbySALdesc结果：enamesal1|kingj50002SCOTT3口叩3JONES29754SLAKE28505CLARK24506ALLEN16007TURNER15008MILLER13009WARD125010MARTIN125011ADAMS110012J

19、AMES95013SMITHSOO14.显示员工的最高工资和最低工资。命令：selectMAX(sal)as'最高工资',MIN(sal)as'最低工资'fromEMP;结果：最高工资曷低工资1I5000jBOO15.显示所有员工的平均工资和总计工资。命令：selectavg(sal)as'平均工资',SUM(sal)as'总计工资"fromEMP;结果：平均工资总讦通1r2Q0192307692382印2516.显示补助在员工中的发放比例、即有多少比例的员工有补助。(此题需注意两个问题：1.select语句中进行除法如何保留小

20、数点后数据。2.count函数如何处理null型数据。)as命令：selectcast(CAST(COUNT(COMMasfloat)/cast(COUNT(*)asfloat)numeric(13,12)fromEMP;结果：比列名)i记而由石百函31聚合查询17.显示每种职业的平均工资。命令：selectjob,avg(SAL)as'average结果：1iobaveragejANALYST30002CLERK1037.53MANAGER27501333333333334PRESIDENT50005SALESMAN1400fromEMPgroupbyJOB;18 .显示每个部门每种

21、岗位的平均工资和最高工资。命令：selectdeptno,job,AVG(sal)as'average',MAX(sal)as'max,groupbyDEPTNO,JOB;fromEMP结果:deptnoiobaveragemaw1"20JuiBiaaaanalyst30003000210CLERLERK9501100430CLERK950950510MANAGER34502450620MANAGER29752泗730MANAGER2S502350810PRESIDENT50005000930SALESMAN14001600命令：se

22、lectdeptno,AVG(sal)as'averagebydeptnohavingAVG(SAL)<2500;结果：deptnoaveragemax1120T156B66G666E6B672S5C20.上一条语句以平均工资升序排序。命令：selectdeptno,AVG(sal)as'averagebydeptnohavingAVG(SAL)<2500order结果：,MAX(sal)as'max'from,MAX(sal)as'max'frombyAVG(SAL)asc;EMPgroupEMPgroup

23、19 .显示平均工资低于2500的部门号，平均工资及最高工资deptnoaveragemax11565.6666S66666728502._2。19弭753000多表查询21 .显示工资高于2500或岗位为MANAGERS有员工的姓名，工资，职位,和部门号。命令：selectename,SAL,job,DEPTNOfromEMPwhereSAL>2500orJOB="manager'结果：enamesaliobdeptnoHJONES；2975MANAGER202BWE2850MANAGER303CLARK2450MANAGER104SCOTT3000ANALYST20

24、5KING5000PRESIDENT1022 .排序显示所有员工的姓名，部门号，工资（以部门号升序，工资降序，雇用日期升序显示）。命令：selectename,deptno,SALfromEMPorderbyDEPTNOasc,SALdesc,HIREDATEasc;结果:enamedeptno1KINGj1050002CLARK1024503MILLER1013004SCOTT2030005JONES2029758ADAMS2011007SMITH20800BBLAKE3023509ALLEN30160010TURNER30150011WARD30125012MARTIN30125013JA

25、MES3095023 .采用自然连接原理显示部门名以及相应的员工姓名。（Sqlserver不支持NATURALJOIN法。）命令：selectdname,enamefromEMP,DEPTwhereEMP.DEPTNO=DEPT.DEPTNO;或selectdname,enamefromEMPjoinDEPTonEMP.DEPTNO=DEPT.DEPTNO;结果：dn5oieename1jRESEARCHSMITH2SALESALLEN3SALESWARD4RESEARCHJONES5SALESMARTIN6SALES0JXKE7ACCOUNTINGCLARKeRESEARCHSCOTT9AC

26、COUNTINGKING10SALESTURNER11RESEARCHADAMS12SALESJAMES13ACCOUNTINGMILLER24 .查询SCOT的上级领导的姓名。命令：selectb.enamefromEMPajoinEMPbona.DEPTNO=b.DEPTNOwherea.ENAME='SCOTT'andb.JOB='manager'结果：enameKJ1JONES25 .显示部门的部门名称，员工名即使部门没有员工也显示部门名称。命令：selectdname,enamefromEMPrightjoinDEPTonEMP.DEPTNO=DEPT

27、.DEPTNO;结果：dnameename1ACCOUNTINGCLARK2ACCOUNTINGKING3ACCOUNTINGMILLER4RESEARCHSMITH5RESEARCHJONES6RESEARCHSCOTT7RESEARCHADAMSSSALESALLEN9SALESWARD10SALESMARTIN11SALESELAKE12SALESTURMER13SALESJqME514OPERATIONSNULL子查询26 .显示所有员工的名称、工资以及工资级别命令：selectename,SAL,gradefromEMPjoinSALGRADEonSALGRADE.LOSALandS

28、ALGRADE.HISAL;结果：enamegrade1ISMITH”口口12ADAMS110013JAMES95014WARD125025MARTIN12502EMILLER130027ALLEN16003eTURNER15003gJONES2975410BLME26504iiCLBK2450412SCOTT3000413KING5000527 .显示ACCOUNTING1所有员工的名称，工资。命令：selectename,SALfromEMPwhereEMP.DEPTNO=(selectwhereDNAME='ACCOUNTING');EMP.SALbetweendeptnofromDEPT结果：enamesal1CLARk24502KING50003MILLER130028 .显示职位属于10号部门所提供职位范围的员工的姓名，职位，工资,部门号。命令：selectename,job,SAL,DEPTNOfromEMPwhereJOBin(selectJOBfromEMPwhereDEPTNO='10')结果：enamepbdeptno1SMITHCLERKeoo202JONESMANAGER2975203ELAKE