use exercise;找出这个数据库中所有的表select name FROM sysobjects WHERE type='U'1. 创建表Create table DEPT(DEPTNO INT NOT NULL, DNAME VARCHAR(14),LOC VARCHAR(13));Alter table DEPT add constraint PK_DEPT PRIMARY KEY (DEPTNO);create table EMP (EMPNO INT NOT NULL,ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT, HIREDATE DATETIME,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);create table salgrade(grade int,losal int,hisal int);select * from dept;select * from emp;select * from salgrade;2. 插入数据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','SALESMAN',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,'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',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);insert into salgrade(grade,losal,hisal)select 1,700,1200 union allselect 2,1201,1400 union allselect 3,1401,2000 union allselect 4,2001,3000 union allselect 5,3001,9999 ;3.将所有员工的工资上浮10%.然后查询员工姓名、薪水、补助。（emp.sal为工资，emp.comm为补助）select sal from emp;update emp set sal = sal+0.1*sal;select ename,sal,comm from emp;update emp set sal = sal/1.1;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 emp;7.查询每个员工每个月拿到的总金额（emp.sal为工资，emp.comm为补助）。（提示：isnull（ex1,ex2）表示如果ex1为空则返回ex2）select ename,sal+isnull(comm,0) as 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.显示所有没有补助的员工的姓名。select ename from emp where comm is null;12.显示有补助的员工的姓名，工资，补助。select ename,sal,comm 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.显示所有员工的平均工资和总计工资。select avg(sal) as '平均工资',sum(sal) as '总计工资' from emp;16.显示补助在员工中的发放比例、即有多少比例的员工有补助。（此题需注意问题：1.select语句中进行除法如何保留小数点后数据。2.count函数如何处理null型数据。3.cast()语法）select cast(cast(count(comm) 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 '平均工资',max(sal) as '最高工资' from emp group by deptno,job order by deptno;19.显示平均工资低于2500的部门号，平均工资及最高工资。select deptno,avg(sal)as 'average',max(sal)as 'max' from emp group by deptno having avg(sal) < 2500;20.上一条语句以平均工资升序排序。select deptno,avg(sal)as 'average',max(sal)as 'max' from emp group by deptno having avg(sal) < 2500 order by avg(sal) asc;多表查询21.显示工资高于2500或岗位为MANAGER的所有员工的姓名，工资，职位，和部门号。select ename,sal,job,deptno from emp where 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,ename from emp,dept where emp.deptno=dept.deptno order by dname;select dname,ename from emp join dept on emp.deptno=dept.deptno;24.查询SCOTT的上级领导的姓名。select ename from emp where empno=(select mgr from emp where ename='scott');select a.ename,b.job from emp a join emp b on a.empno=b.mgr and b.ename = 'scott';select * 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 a right join dept b on a.deptno=b.deptno;26.显示所有员工的名称、工资以及工资级别。select ename,sal,grade from emp join salgrade on sal between salgrade.losal and salgrade.hisal;27.显示ACCOUNTING部门所有员工的名称，工资。select ename,sal from emp join dept on emp.deptno=dept.deptno and dept.dname='accounting';select ename,sal from emp where deptno=(select deptno from dept where dname='accounting');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的员工的姓名，工资和职位（采用UNION语法实现）。select ename,sal,job from emp where sal>2500 union select ename,sal,job from emp where job='manager' order by ename;select ename,sal,job from emp where sal>2500 or job='manager'order by ename;31.显示工资高于2500且职位为MANAGER的员工的姓名，工资和职位（采用INTERSECT语法实现）。select ename,sal,job from emp where sal>2500 and job='manager';select ename,sal,job from emp where sal>2500 intersect select ename,sal,job from emp where job='manager';32.显示工资高于2500但职位不是MANAGER的员工的姓名，工资和职位（采用EXCEPT语法实现）。select ename,sal,job from emp where sal>2500 except select ename,sal,job from emp where job='manager';select * from dept; select * from emp; select * from salgrade;
