练习1sql语句答案.docx

练习1sql语句答案.docx

《练习1sql语句答案.docx》由会员分享，可在线阅读，更多相关《练习1sql语句答案.docx（13页珍藏版）》请在冰豆网上搜索。

练习1sql语句答案

习题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，以便进行数据还原）

DDL

1.写出上述表的建表语句。

此外，在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）

DML

2.给出相应的INSERT语句来完成题中给出数据的插入。

命令：

insertintoSALGRADE（GRADE,LOSAL,HISAL）VALUES（1,1,1）

3.将所有员工的工资上浮10%.然后查询员工姓名、薪水、补助。

（emp.sal为工资，m为补助）

命令：

updateempsetsal=sal*1.1

Selectename,sal,commfromemp

结果：

单表查询

4.查看EMP表中部门号为10的员工的姓名，职位，参加工作时间，工资。

命令：

selectename,job,hiredate,salfromempwheredeptno=10

结果：

5.查所有已有的职位，要求去除重复项。

命令：

SELECTDISTINCTJOBFROMEMP

结果：

6.计算每个员工的年薪，并取列名为SalaryofYear（emp.sal为员工的月薪），要求输出员工姓名，年薪。

命令：

selectename,sal*12as'salaryofyear'fromemp

结果：

7.查询每个员工每个月拿到的总金额（emp.sal为工资，m为补助）。

（提示：

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,deptnofromempwheresal>2500orjob='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,grade

fromempleftjoinsalgrade

onemp.sal>=salgrade.losalandemp.sal<=salgrade.hisal

orderbygrade

（子查询方法）

结果：

27.显示ACCOUNTING部门所有员工的名称，工资。

命令：

（连接方法）

selectename,salfromempinnerjoindeptonemp.deptno=dept.deptnowheredname='accounting'

（子查询方法）

selectename,salfromemp

wheredeptnoin

（

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,jobfromEMPwhereSAL>2500unionselectename,SAL,jobfromEMPwhereJOB='manager'

结果：

31.显示工资高于2500且职位为MANAGER的员工的姓名，工资和职位（采用INTERSECT语法实现）。

命令：

selectename,SAL,jobfromEMPwhereSAL>2500intersectselectename,SAL,jobfromEMPwhereJOB='manager'

结果：

32.显示工资高于2500但职位不是MANAGER的员工的姓名，工资和职位（采用EXCEPT语法实现）。

命令：

selectename,SAL,jobfromEMPwhereSAL>2500exceptselectename,SAL,jobfromEMPwhereJOB='manager'

结果：