练习1-sql语句答案

习题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);其中表中包含如下数据：DEPT表：EMP表：SALGRADE表：BONUS表：无数据根据上面描述完成下面问题：（注意：注意保存脚本，尤其是DDL和DML，以便进行数据还原）DDL1.写出上述表的建表语句。此外，在DEPT上创建名为”PK_DEPT”的主键约束，在EMP表上创建名为”PK_EMP”的主键约束以及指向表DEPT的外键约束”FK_DEPTNO”。命令：createtabledept(DEPTNOINTCONSTRAINTPK_DEPTprimarykey,DNAMEvarchar(14),LOCvarchar(13))createtableEMP(EMPNOINTCONSTRAINTPK_EMPPRIMARYKEY,ENAMEVARCHAR(10),JOBVARCHAR(9),MGRINT,HIREDATEDATE,SALFLOAT,COMMFLOAT,DEPTNOINTCONSTRAINTFK_DEPTNOFOREIGNKEYREFERENCESdbo.dept(DEPTNO))createtableBONUS(ENAMEVARCHAR(10),JOBVARCHAR(9),SALINT,COMMINT)createtableSALGRADE(GRADEINT,LOSALINT,HISALINT)DML2.给出相应的INSERT语句来完成题中给出数据的插入。命令：insertintoSALGRADE(GRADE,LOSAL,HISAL)VALUES(1,1,1)3.将所有员工的工资上浮10%.然后查询员工姓名、薪水、补助。（emp.sal为工资，emp.comm为补助）命令：updateempsetsal=sal*1.1Selectename,sal,commfromemp结果：单表查询4.查看EMP表中部门号为10的员工的姓名，职位，参加工作时间，工资。命令：selectename,job,hiredate,salfromempwheredeptno=10结果：5.查所有已有的职位，要求去除重复项。命令：SELECTDISTINCTJOBFROMEMP结果：6.计算每个员工的年薪，并取列名为SalaryofYear（emp.sal为员工的月薪），要求输出员工姓名，年薪。命令：selectename,sal*12as'salaryofyear'fromemp结果：7.查询每个员工每个月拿到的总金额（emp.sal为工资，emp.comm为补助）。（提示：isnull（ex1,ex2）表示如果ex1为空则返回ex2）命令：selectename,isnull(sal+comm,sal)astotalfromemp结果：8.显示职位是主管（manager）的员工的姓名，工资。命令：selectename,salfromempwherejob='manager'结果：9.显示第3个字符为大写O的所有员工的姓名及工资。命令：selectename,salfromempwhereenamelike'__O%'结果：10.显示职位为销售员（SALESMAN）或主管（MANAGER）的员工的姓名，工资，职位。命令：selectename,sal,jobfromempwherejob='salesman'orjob='manager'结果：11.显示所有没有补助的员工的姓名。命令：selectenamefromempwherecommisnull结果：12.显示有补助的员工的姓名，工资，补助。命令：selectenamefromempwherecommisnotnull结果：13.排序显示所有员工的姓名，工资（按工资降序方式）。命令：selectename,salfromemporderbysaldesc结果：14.显示员工的最高工资和最低工资。命令:selectMAX(sal)as'最高工资',MIN(sal)as'最低工资'fromemp结果：15.显示所有员工的平均工资和总计工资。命令：selectAVG(sal)as'平均工资',SUM(sal)as'总计工资'fromemp结果：16.显示补助在员工中的发放比例、即有多少比例的员工有补助。（此题需注意两个问题：1.select语句中进行除法如何保留小数点后数据。2.count函数如何处理null型数据。）命令：selectcast(count(comm)*1.0/count(*)asdecimal(18,2))fromemp结果：聚合查询17.显示每种职业的平均工资。命令：selectjob,AVG(sal)averagerfromempgroupbyjob结果：18.显示每个部门每种岗位的平均工资和最高工资。命令：selectdeptno,job,AVG(sal)average,MAX(sal)maxfromempgroupbyjob,deptno结果：19.显示平均工资低于2500的部门号，平均工资及最高工资。命令：selectdeptno,AVG(sal)as'average',MAX(sal)maxfromempgroupbydeptnohavingavg(sal)2500结果：20.上一条语句以平均工资升序排序。命令：selectdeptno,AVG(sal)as'average',MAX(sal)maxfromempgroupbydeptnohavingavg(sal)2500orderbyaverage结果：多表查询21.显示工资高于2500或岗位为MANAGER的所有员工的姓名，工资，职位，和部门号。命令selectename,sal,job,deptnofromempwheresal2500orjob='manager'结果：22.排序显示所有员工的姓名，部门号，工资（以部门号升序，工资降序，雇用日期升序显示）。命令：selectename,deptno,salfromemporderbydeptno,saldesc,hiredate结果：23.采用自然连接原理显示部门名以及相应的员工姓名。（Sqlserver不支持NATURALJOIN语法。）命令：selectdname,enamefromempinnerjoindeptonemp.deptno=dept.deptno结果：24.查询SCOTT的上级领导的姓名。命令：selectemp2.enamefromempemp1innerjoinempemp2onemp1.mgr=emp2.empnowhereemp1.ename='scott'结果：25.显示部门的部门名称，员工名即使部门没有员工也显示部门名称。命令：selectdname,enamefromempfulljoindeptonemp.deptno=dept.deptno结果：子查询26.显示所有员工的名称、工资以及工资级别。命令：（连接方法）selectename,sal,gradefromempleftjoinsalgradeonemp.sal=salgrade.losalandemp.sal=salgrade.hisalorderbygrade（子查询方法）结果：27.显示ACCOUNTING部门所有员工的名称，工资。命令：（连接方法）selectename,salfromempinnerjoindeptonemp.deptno=dept.deptnowheredname='accounting'（子查询方法）selectename,salfromempwheredeptnoin(selectdeptnofromdeptwheredname='accounting')结果：28.显示职位属于10号部门所提供职位范围的员工的姓名，职位，工资，部门号。命令：selectename,job,sal,deptnofromempwherejobin(selectjobfromempwheredeptno=10)结果：29.显示在所有员工用中高于30号部门中任一个员工工资的员工的姓名，工资和部门号。命令：selectename,SAL,deptnofromEMPwhereSAL(selectmax(SAL)fromEMPwhereDEPTNO=30)结果：集合查询30.显示工资高于2500或职位为MANAGER的员工的姓名，工资和职位（采用UNION语法实现）。命令：selectename,SAL,jobfromEMPwhereSAL2500unionselectename,SAL,jobfromEMPwhereJOB='manager'结果：31.显示工资高于2500且职位为MANAGER的员工的姓名，工资和职位（采用INTERSECT语法实现）。命令：selectename,SAL,jobfromEMPwhereSAL2500intersectselectename,SAL,jobfromEMPwhereJOB='manager'结果：32.显示工资高于2500但职位不是MANAGER的员工的姓名，工资和职位（采用EXCEPT语法实现）。命令：selectename,SAL,jobfromEMPwhereSAL2500exceptselectename,SAL,jobfromEMPwhereJOB='manager'结果：