1.找出EMP表中的姓名(ENAME)第三个字母是A?的员工姓名。
select ename from emp where ename like ‘__A%’;
2.找出EMP表员工名字中含有A?和N的员工姓名。
select ename from emp where ename like ‘%A%’ and ename like ‘%N%’;
3.找出所有有佣金的员工,列出姓名、工资、佣金,显示结果按工资从小到大,佣金从大到小。
select ename,(sal+nvl(comm,0)) income,comm from emp where comm is not
null order by income,comm desc;
4.列出部门编号为20的所有职位。
select deptno,job from emp where deptno=20;
5.列出不属于SALES?的部门。
select distinct * from dept where dname!=’SALES’;
6.显示工资不在1000?到1500?之间的员工信息:名字、工资,按工资从大到小排序。
select ename,(sal+nvl(comm,0)) from emp where (sal+nvl(comm,0))<1000
or (sal+nvl(comm,0))>1500 order by (sal+nvl(comm,0)) desc;
7.显示职位为MANAGER?和SALESMAN,年薪在15000?和20000?之间的员工的信息:名字、职位、年薪。
select ename,job,(sal+nvl(comm,0))*12 income from emp where job
in(‘MANAGER’,’SALESMAN’) AND (sal+nvl(comm,0))*12 between 15000 and
20000;
8.说明以下两条SQL语句的输出结果:
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
9.让SELECT语句的输出结果为
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
。。。。
select ‘select * from’ ||table_name||’;’ from user_tables;
select * fromDEPT;
select * fromEMP;
select * fromBONUS;
select * fromSALGRADE;
10.判断SELECT?ENAME,SAL?FROM?EMP?WHERE?SAL?>?’1500’是否抱错,为什么?
不报错,发生了隐式转换,‘1500’字符被转换成了数字