数据库sql查询语句上机练习1习题结果单世民.docx
《数据库sql查询语句上机练习1习题结果单世民.docx》由会员分享，可在线阅读，更多相关《数据库sql查询语句上机练习1习题结果单世民.docx（14页珍藏版）》请在冰豆网上搜索。
数据库sql查询语句上机练习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）;
其中表中包含如下数据：
DEPT表：
EMP表：
SALGRADE表：
BONUS表：
无数据
根据上面描述完成下面问题：
（注意：
注意保存脚本，尤其是DDL和DML，以便进行数据还原）
DDL
1.写出上述表的建表语句。
此外，在DEPT上创建名为”PK_DEPT”的主键约束，在EMP表上创建名为”PK_EMP”的主键约束以及指向表DEPT的外键约束”FK_DEPTNO”。
命令：
CreatetableDEPT
（DEPTNOINTNOTNULL,DNAMEVARCHAR（14）,LOCVARCHAR（13））;
AltertableDEPT
addconstraintPK_DEPTPRIMARYKEY（DEPTNO）;
createtableEMP（EMPNOINTNOTNULL,ENAMEVARCHAR（10）,JOBVARCHAR（9）,MGRINT,HIREDATEDATE,
SALFLOAT,COMMFLOAT,DEPTNOINT）;
altertableEMP
addconstraintPK_EMPPRIMARYKEY（EMPNO）;
altertableEMP
addconstraintFK_DEPTNOforeignkey（DEPTNO）referencesDEPT（DEPTNO）;
DML
2.给出相应的INSERT语句来完成题中给出数据的插入。
命令：
insertintoDEPTvalues（10,'ACCOUNTING','NEWYORK'）;
insertintoDEPTvalues（20,'PESEARCH','DALLAS'）;
insertintoDEPTvalues（30,'SALES','CHICAGO'）;
insertintoDEPTvalues（40,'OPERATIONS','BOSTON'）;
insertintoEMPvalues（7369,'SMITH','CLERK',7566,'1980-12-17',800,NULL,20）;
insertintoEMPvalues（7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30）;
insertintoEMPvalues（7521,'WARD','SALESMAN',7698,'1981-02-21',1250,500,30）;
insertintoEMPvalues（7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20）;
insertintoEMPvalues（7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30）;
insertintoEMPvalues（7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30）;
insertintoEMPvalues（7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10）;
insertintoEMPvalues（7788,'SCOTT','ANALYST',7566,'1987-06-13',3000,NULL,20）;
insertintoEMPvalues（7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10）;
insertintoEMPvalues（7844,'TURN','SALESMAN',7698,'1981-09-08',1500,0,30）;
insertintoEMPvalues（7876,'ADMAS','CLERK',7788,'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为工资，m为补助）
命令：
updateEMPsetsal=sal+0.1*sal;
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,sal+isnull（COMM,0）totalfromEMP
结果：
8.显示职位是主管（manager）的员工的姓名，工资。
命令：
selectename,salfromEMPwhereJOB='manager';
结果：
9.显示第3个字符为大写O的所有员工的姓名及工资。
命令：
selectename,salfromEMPwhereenamelike'__O%';
结果：
10.显示职位为销售员（SALESMAN）或主管（MANAGER）的员工的姓名，工资，职位。
命令：
selectename,SAL,jobfromEMPwhereJOB='SALESMAN'orJOB='MANAGER';
结果：
11.显示所有没有补助的员工的姓名。
命令：
selectenamefromEMPwhereCOMMisnull;
结果：
12.显示有补助的员工的姓名，工资，补助。
命令：
selectename,SAL,COMMfromEMPwhereCOMMisnotnull;
结果：
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（CAST（COUNT（COMM）asfloat）/cast（COUNT（*）asfloat）asnumeric（13,12））fromEMP;
结果：
聚合查询
17.显示每种职业的平均工资。
命令：
selectjob,avg（SAL）as'average'fromEMPgroupbyJOB;
结果：
18.显示每个部门每种岗位的平均工资和最高工资。
命令：
selectdeptno,job,AVG（sal）as'average',MAX（sal）as'max'fromEMPgroupbyDEPTNO,JOB;
结果：
19.显示平均工资低于2500的部门号，平均工资及最高工资。
命令：
selectdeptno,AVG（sal）as'average',MAX（sal）as'max'fromEMPgroupbydeptnohavingAVG（SAL）<2500;
结果：
20.上一条语句以平均工资升序排序。
命令：
selectdeptno,AVG（sal）as'average',MAX（sal）as'max'fromEMPgroupbydeptnohavingAVG（SAL）<2500orderbyAVG（SAL）asc;
结果：
多表查询
21.显示工资高于2500或岗位为MANAGER的所有员工的姓名，工资，职位，和部门号。
命令：
selectename,SAL,job,DEPTNOfromEMPwhereSAL>2500orJOB='manager';
结果：
22.排序显示所有员工的姓名，部门号，工资（以部门号升序，工资降序，雇用日期升序显示）。
命令：
selectename,deptno,SALfromEMPorderbyDEPTNOasc,SALdesc,HIREDATEasc;
结果：
23.采用自然连接原理显示部门名以及相应的员工姓名。
（Sqlserver不支持NATURALJOIN语法。
）
命令：
selectdname,enamefromEMP,DEPTwhereEMP.DEPTNO=DEPT.DEPTNO;
或
selectdname,enamefromEMPjoinDEPTonEMP.DEPTNO=DEPT.DEPTNO;
结果：
24.查询SCOTT的上级领导的姓名。
命令：
selectb.enamefromEMPajoinEMPbona.DEPTNO=b.DEPTNOwherea.ENAME='SCOTT'andb.JOB='manager';
结果：
25.显示部门的部门名称，员工名即使部门没有员工也显示部门名称。
命令：
selectdname,enamefromEMPrightjoinDEPTonEMP.DEPTNO=DEPT.DEPTNO;
结果：
子查询
26.显示所有员工的名称、工资以及工资级别。
命令：
selectename,SAL,gradefromEMPjoinSALGRADEonEMP.SALbetweenSALGRADE.LOSALandSALGRADE.HISAL;
结果：
27.显示ACCOUNTING部门所有员工的名称，工资。
命令：
selectename,SALfromEMPwhereEMP.DEPTNO=（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';
结果：
升级会员 