1、习题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);其中表中包含如下数据：D
2、EPT表：EMP表：SALGRADE表：BONUS表： 无数据根据上面描述完成下面问题：（ 注意 ：注意保存脚本，尤其是DDL和DML，以便进行数据还原）DDL1. 写出上述表的建表语句。此外，在DEPT上创建名为”PK_DEPT”的主键约束，在EMP表上创建名为” PK_EMP”的主键约束以及指向表DEPT的外键约束” FK_DEPTNO”。命令：Create table DEPT(DEPTNO INT NOT NULL, DNAME VARCHAR(14),LOC VARCHAR(13);Alter table DEPT add constraint PK_DEPT PRIMARY KEY
3、 (DEPTNO);create table EMP (EMPNO INT NOT NULL,ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT, HIREDATE DATE,SAL FLOAT, COMM FLOAT, DEPTNO INT);alter table EMPadd constraint PK_EMP PRIMARY KEY (EMPNO);alter table EMPadd constraint FK_DEPTNO foreign key(DEPTNO) references DEPT(DEPTNO);DML2. 给出相应的INSERT语句
4、来完成题中给出数据的插入。命令：insert into DEPT values(10,ACCOUNTING,NEW YORK);insert into DEPT values(20,PESEARCH,DALLAS);insert into DEPT values(30,SALES,CHICAGO);insert into DEPT values(40,OPERATIONS,BOSTON);insert into EMP values(7369,SMITH,CLERK,7566,1980-12-17,800,NULL,20);insert into EMP values(7499,ALLEN,S
5、ALESMAN,7698,1981-02-20,1600,300,30);insert into EMP values(7521,WARD,SALESMAN,7698,1981-02-21,1250,500,30);insert into EMP values(7566,JONES,MANAGER,7839,1981-04-02,2975,NULL,20);insert into EMP values(7654,MARTIN,SALESMAN,7698,1981-09-28,1250,1400,30);insert into EMP values(7698,BLAKE,MANAGER,7839
6、,1981-05-01,2850,NULL,30);insert into EMP values(7782,CLARK,MANAGER,7839,1981-06-09,2450,NULL,10);insert into EMP values(7788,SCOTT,ANALYST,7566,1987-06-13,3000,NULL,20);insert into EMP values(7839,KING,PRESIDENT,NULL,1981-11-17,5000,NULL,10);insert into EMP values(7844,TURN,SALESMAN,7698,1981-09-08
7、,1500,0,30);insert into EMP values(7876,ADMAS,CLERK,7788,1987-06-13,1100,NULL,20);insert into EMP values(7900,JAMES,CLERK,7698,1981-12-03,950,NULL,30);insert into EMP values(7934,MILLER,CLERK,7782,1982-01-23,1300,NULL,10);3. 将所有员工的工资上浮10%.然后查询员工姓名、薪水、补助。（emp.sal为工资，m为补助）命令：update EMP set sal=sal+0.1
8、*sal;select ename,sal,comm from EMP;结果：单表查询4. 查看EMP表中部门号为10的员工的姓名，职位，参加工作时间，工资。命令：select ename,job,hiredate,sal from 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
9、 EMP;结果：7. 查询每个员工每个月拿到的总金额（emp.sal为工资，m为补助）。（提示：isnull（ex1,ex2）表示如果ex1为空则返回ex2）命令：select ename,sal+isnull(COMM,0) 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.显示职位为销售员
10、（SALESMAN）或主管（MANAGER）的员工的姓名，工资，职位。命令：select ename,SAL,job from EMP where JOB=SALESMAN or JOB=MANAGER;结果：11.显示所有没有补助的员工的姓名。命令：select ename from EMP where COMM is null;结果：12.显示有补助的员工的姓名，工资，补助。命令：select ename,SAL,COMM from EMP where COMM is not null;结果：13.排序显示所有员工的姓名，工资（按工资降序方式）。命令：select ENAME,SAL fr
11、om EMP order by SAL desc;结果：14.显示员工的最高工资和最低工资。命令：select MAX(sal) as 最高工资 ,MIN(sal) as 最低工资 from EMP;结果：15.显示所有员工的平均工资和总计工资。命令：select avg(sal) as 平均工资 ,SUM(sal) as 总计工资from EMP ;结果：16.显示补助在员工中的发放比例、即有多少比例的员工有补助。（此题需注意两个问题：1.select语句中进行除法如何保留小数点后数据。2.count函数如何处理null型数据。）命令：select cast(CAST(COUNT(COMM)
12、 as float)/cast(COUNT(*) as float) as numeric(13,12)from EMP;结果：聚合查询17.显示每种职业的平均工资。命令：select job,avg(SAL) as average from EMP group by JOB;结果：18.显示每个部门每种岗位的平均工资和最高工资。命令：select deptno,job,AVG(sal) as average,MAX(sal) as max from EMP group by DEPTNO,JOB;结果：19.显示平均工资低于2500的部门号，平均工资及最高工资。命令：select deptn
13、o,AVG(sal) as average,MAX(sal) as max from EMP group by deptno having AVG(SAL)2500 or JOB=manager;结果： 22.排序显示所有员工的姓名，部门号，工资（以部门号升序，工资降序，雇用日期升序显示）。命令：select ename,deptno,SAL from EMP order by DEPTNO asc,SAL desc,HIREDATE asc;结果：23.采用自然连接原理显示部门名以及相应的员工姓名。（Sql server不支持NATURAL JOIN语法。）命令：select dname,e
14、name from EMP,DEPT where EMP.DEPTNO=DEPT.DEPTNO;或select dname,ename from EMP join DEPT on EMP.DEPTNO=DEPT.DEPTNO;结果：24.查询SCOTT的上级领导的姓名。命令：select b.ename from EMP a join EMP b on a.DEPTNO=b.DEPTNO where a.ENAME=SCOTT and b.JOB=manager;结果：25.显示部门的部门名称，员工名即使部门没有员工也显示部门名称。命令：select dname,ename from EMP
15、right join DEPT on EMP.DEPTNO=DEPT.DEPTNO;结果：子查询26.显示所有员工的名称、工资以及工资级别。命令：select ename,SAL,grade from EMP join SALGRADE on EMP.SAL between SALGRADE.LOSAL and SALGRADE.HISAL;结果：27.显示ACCOUNTING部门所有员工的名称，工资。命令：select ename,SAL from EMP where EMP.DEPTNO=(select deptno from DEPT where DNAME=ACCOUNTING);结果
16、：28.显示职位属于10号部门所提供职位范围的员工的姓名，职位，工资，部门号。命令：select ename,job,SAL,DEPTNO from EMP where JOB in (select JOB from EMP where DEPTNO=10)结果：29.显示在所有员工中高于30号部门中任一个员工工资的员工的姓名，工资和部门号。命令：select ename,SAL,deptno from EMP where SAL(select MAX(SAL) from EMP where DEPTNO=30);结果：集合查询30.显示工资高于2500或职位为MANAGER的员工的姓名，工资
17、和职位（采用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;结果：
