练习1sql语句答案.docx

1、练习1sql语句答案习题1请根据给出的数据库表结构来回答相应问题：DEPT (DEPTNO INT, DNAME VARCHAR(14),LOC VARCHAR(13);EMP (EMPNO INT,ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT, HIREDATE DATE, SAL FLOAT, COMM FLOAT, DEPTNO INT);BONUS (ENAME VARCHAR(10), JOB VARCHAR(9), SAL INT, COMM INT);SALGRADE ( GRADE INT, LOSAL INT, HISAL INT);其

2、中表中包含如下数据：DEPT表：EMP表：SALGRADE表：BONUS表： 无数据根据上面描述完成下面问题：（注意：注意保存脚本，尤其是DDL和DML，以便进行数据还原）DDL1. 写出上述表的建表语句。此外，在DEPT上创建名为”PK_DEPT”的主键约束，在EMP表上创建名为” PK_EMP”的主键约束以及指向表DEPT的外键约束” FK_DEPTNO”。命令：create table dept (DEPTNO INT CONSTRAINT PK_DEPT primary key, DNAME varchar(14), LOC varchar(13) )create table EMP(

3、 EMPNO INT CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT, HIREDATE DATE, SAL FLOAT, COMM FLOAT, DEPTNO INT CONSTRAINT FK_DEPTNO FOREIGN KEY REFERENCES dbo.dept(DEPTNO) )create table BONUS( ENAME VARCHAR(10), JOB VARCHAR(9), SAL INT, COMM INT)create table SALGRADE( GRADE I

4、NT,LOSAL INT,HISAL INT)DML2. 给出相应的INSERT语句来完成题中给出数据的插入。命令：insert into SALGRADE (GRADE,LOSAL,HISAL)VALUES(1,1,1)3. 将所有员工的工资上浮10%.然后查询员工姓名、薪水、补助。（emp.sal为工资，m为补助）命令：update emp set sal=sal*1.1Select ename,sal,comm from emp结果：单表查询4. 查看EMP表中部门号为10的员工的姓名，职位，参加工作时间，工资。命令：select ename,job,hiredate,sal from

5、emp where deptno=10结果：5. 查所有已有的职位，要求去除重复项。命令： SELECT DISTINCT JOB FROM EMP结果：6. 计算每个员工的年薪，并取列名为Salary of Year（emp.sal为员工的月薪），要求输出员工姓名，年薪。命令：select ename,sal*12 as salary of year from emp结果：7. 查询每个员工每个月拿到的总金额（emp.sal为工资，m为补助）。（提示：isnull（ex1,ex2）表示如果ex1为空则返回ex2）命令：select ename,isnull(sal+comm,sal) as

6、total from emp结果：8. 显示职位是主管（manager）的员工的姓名，工资。命令：select ename,sal from emp where job=manager结果：9. 显示第3个字符为大写O的所有员工的姓名及工资。命令：select ename,sal from emp where ename like _O%结果：10. 显示职位为销售员（SALESMAN）或主管（MANAGER）的员工的姓名，工资，职位。命令：select ename,sal,job from emp where job=salesman or job=manager结果：11. 显示所有没有补

7、助的员工的姓名。命令：select ename from emp where comm is null结果：12. 显示有补助的员工的姓名，工资，补助。命令：select ename from emp where comm is not null结果：13. 排序显示所有员工的姓名，工资（按工资降序方式）。命令：select ename,sal from emp order by sal desc结果：14. 显示员工的最高工资和最低工资。命令:select MAX(sal) as 最高工资,MIN(sal) as 最低工资 from emp结果：15. 显示所有员工的平均工资和总计工资。命令

8、：select AVG(sal) as 平均工资, SUM(sal) as 总计工资 from emp结果：16. 显示补助在员工中的发放比例、即有多少比例的员工有补助。（此题需注意两个问题：1.select语句中进行除法如何保留小数点后数据。2.count函数如何处理null型数据。）命令：select cast(count(comm)*1.0/count(*) as decimal(18,2) from emp结果：聚合查询17. 显示每种职业的平均工资。命令：select job,AVG(sal) averager from emp group by job结果：18. 显示每个部门每种

9、岗位的平均工资和最高工资。命令：select deptno,job,AVG(sal) average,MAX(sal) max from emp group by job,deptno结果：19. 显示平均工资低于2500的部门号，平均工资及最高工资。命令：select deptno,AVG(sal) as average,MAX(sal) max from emp group by deptno having avg(sal)2500结果：20. 上一条语句以平均工资升序排序。命令：select deptno,AVG(sal) as average,MAX(sal) max from emp

10、 group by deptno having avg(sal)2500 or job=manager结果： 22. 排序显示所有员工的姓名，部门号，工资（以部门号升序，工资降序，雇用日期升序显示）。命令：select ename,deptno,sal from emp order by deptno,sal desc,hiredate结果：23. 采用自然连接原理显示部门名以及相应的员工姓名。（Sql server不支持NATURAL JOIN语法。）命令：select dname,ename from emp inner join dept on emp.deptno=dept.deptn

11、o结果：24. 查询SCOTT的上级领导的姓名。命令：select emp2.ename from emp emp1 inner join emp emp2 on emp1.mgr=emp2.empno where emp1.ename=scott结果：25. 显示部门的部门名称，员工名即使部门没有员工也显示部门名称。命令：select dname,ename from emp full join dept on emp.deptno=dept.deptno结果：子查询26. 显示所有员工的名称、工资以及工资级别。命令：（连接方法）select ename,sal,grade from emp

12、 left join salgrade on emp.sal=salgrade.losal and emp.sal(select max(SAL) from EMP where DEPTNO=30)结果：集合查询30. 显示工资高于2500或职位为MANAGER的员工的姓名，工资和职位（采用UNION语法实现）。命令：select ename,SAL,job from EMP where SAL2500 union select ename,SAL,job from EMP where JOB=manager结果：31. 显示工资高于2500且职位为MANAGER的员工的姓名，工资和职位（采用INTERSECT语法实现）。命令：select ename,SAL,job from EMP where SAL2500 intersect select ename,SAL,job from EMP where JOB=manager结果：32. 显示工资高于2500但职位不是MANAGER的员工的姓名，工资和职位（采用EXCEPT语法实现）。命令：select ename,SAL,job from EMP where SAL2500 except select ename,SAL,job from EMP where JOB=manager结果：