/*3.将所有员工的工资上浮10%.然后查询员工姓名、薪水、补助。(emp.sal为工资,emp.comm为补助)*/
update EMP
set
SAL=SAL*1.10
select ename,sal,comm
from EMP
/*4.查看EMP表中部门号为10的员工的姓名,职位,参加工作时间,工资。
*/
select * from EMP
select ename,job,HIREDATE,sal from EMP
where 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 EMP
group by DEPTNO having AVG(sal)<2500
/*以平均工资升序排序*/
select deptno,avg(sal),max(sal) 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.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.ename
from 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'
SQL操作范例
最新推荐文章于 2024-07-02 20:06:37 发布