SQL练习(二)

--找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
select ename from emp where ename like '__A%';
--找出EMP表员工名字中含有A 和N的员工姓名。
select ename from emp where ename like ('%A%N%');
select ename from emp where instr(ename,'A',1)>0 and instr(ename,'N',1)>0;
select ename from emp where instr(ename,'A')<>0 and instr(ename,'N')<>0;
--找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
select ename,sal,comm from emp where nvl(comm,0)<>0 order by sal asc,comm desc; 
--列出部门编号为20的所有职位。
select job from emp where deptno = 20;
--列出不属于SALES 的部门。
select * from emp where deptno <> (select deptno from dept where dname=upper('sales'));
select * from dept where dname <> 'SALES';
--显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。
select * from emp where sal not between 1000 and 1500 order by sal asc;
select * from emp where sal between 0 and 1000 or sal >1500  order by sal asc; 
select * from emp where sal < 1000 and sal >0 or sal >1500  order by sal asc; 
--显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。
select ename,job,(select (nvl(sal,0)+nvl(comm,0))*12 "年薪" from emp e2 where e1.empno=e2.empno) as "year" from emp e1 where job =upper('manager') or job='SALESMAN';
SELECT ENAME 姓名,JOB 职位,(SAL + COMM) * 12 AS 年薪 FROM EMP WHERE (SAL + COMM) * 12 BETWEEN 15000 AND 20000 AND JOB IN('MANAGER','SALESMAN');  


SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL; 
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;


SELECT 'SELECT * FROM '||TABLE_NAME||';' FROM USER_TABLES;