sql题练习

sql题练习

 

1.  选择部门30中的所有员工.

select * from emp where deptno=30

2.  列出所有办事员(clerk)的姓名，编号和部门编号.

select empno,ename,deptno from emp wherejob='clerk'

3.  找出佣金高于薪金的员工. 

select * from emp where comm>sal

4.  找出佣金高于薪金的60%的员工.   

select * from emp where comm>sal*0.6

5.  找出部门10中所有经理(manager)和部门20中所有办事员(clerk)的详细资料.

select * from emp where (deptno=10 andjob='manager') or (deptno=20 and job='clerk')

6.  找出部门10中所有经理(manager),部门20中所有办事员(clerk),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料.  

select * from emp where (deptno=10 and job='manager') or(deptno=20 and job='clerk')
or (sal>2000 and deptno!=10 and deptno!=20)