SQL操作范例

/*3.将所有员工的工资上浮10%.然后查询员工姓名、薪水、补助。（emp.sal为工资，emp.comm为补助）*/update EMP setSAL=SAL*1.10select ename,sal,commfrom EMP/*4.查看EMP表中部门号为10的员工的姓名，职位，参加工作时间，工资。*/select * from EMPselect ename,job,HIREDATE,sal from EMPwhere DEPTNO=10/*5.查所有已有的职位，要求去除重复项。*/select job from EMP group by job/*6.计算每个员工的年薪，并取列名为Salary of Year（emp.sal为员工的月薪），要求输出员工姓名，年薪。*/select ename,sal*12 Salaryofyear from EMP/*7.查询每个员工每个月拿到的总金额（emp.sal为工资，emp.comm为补助）。（提示：isnull（ex1,ex2）表示如果ex1为空则返回ex2）*/select ename,isNULL(sal,0)+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.显示职位为销售员（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) 最高工资,min(SAL) 最低工资 from EMP /*15.显示所有员工的平均工资和总计工资。*/select AVG(sal) 平均工资,SUM(sal) 总计工资 from EMP/*16.显示补助在员工中的发放比例、即有多少比例的员工有补助。（此题需注意两个问题：1.select语句中进行除法如何保留小数点后数据。2.count函数如何处理null型数据。）*/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),max(sal) from EMP group by DEPTNO,job/*显示平均工资低于2500的部门号，平均）工资及最高工资*/select deptno,avg(sal),max(sal) from EMPgroup by DEPTNO having AVG(sal)<2500/*以平均工资升序排序*/select deptno,avg(sal),max(sal) from EMPgroup 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.21.显示工资高于2500或岗位为MANAGER的所有员工的姓名，工资，职位，和部门号。*/select ename,sal,job,DEPTNO from EMP order by deptno asc,sal desc,hiredate asc/*23*/select dname,ename from emp,dept where dept.DEPTNO=EMP.DEPTNO/*24*/select a.enamefrom EMP a join EMP b on b.DEPTNO=a.DEPTNO  where a.JOB='manager' and b.ename='scott'   /*25*/ select dname,ename from emp right join DEPT on emp.DEPTNO=dept.DEPTNO /*26*/ select ename,sal,grade from emp join salgrade on sal between losal and hisal Order by grade select * from SALGRADE/*28*/select ename ,sal from emp join dept on emp.DEPTNO=DEPT.DEPTNO where DEPT.dname='accounting'/*29*/select ename,sal,deptno from emp where sal>(select max(SAL) from EMP where DEPTNO=30)/*30*/select ename,sal,deptno from emp where sal>30 union select ename,sal,deptno from emp where job='manager' /*31*/select ename,sal,deptno from emp where sal>2500 intersect select ename,sal,deptno from emp where job='manager' /**32*/select ename,sal,deptno from emp where sal>2500 except select ename,sal,deptno from emp where job='manager'