Oracle SQL：经典查询练手第二篇

承接上一篇,表结构如下,用户scott

 1 desc emp
 2 名称       空值       类型           
 3 -------- -------- ------------ 
 4 EMPNO NOT NULL NUMBER(4) 
 5 ENAME          VARCHAR2(10) 
 6 JOB           VARCHAR2(9) 
 7 MGR          NUMBER(4) 
 8 HIREDATE          DATE   
 9 SAL           NUMBER(7,2) 
10 COMM          NUMBER(7,2) 
11 DEPTNO          NUMBER(2)

1 desc dept
2 名称     空值       类型           
3 ------ -------- ------------ 
4 DEPTNO NOT NULL NUMBER(2) 
5 DNAME          VARCHAR2(14) 
6 LOC          VARCHAR2(13)

数据如下:

用SQL完成以下问题列表：

找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
找出EMP表员工名字中含有A 和N的员工姓名。
找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
列出部门编号为20的所有职位。
列出不属于SALES 的部门。
显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。
显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。
说明以下两条SQL语句的输出结果
让SELECT 语句的输出结果为
判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错，为什么？

解答如下:

1 --1.找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名
2 --使用下划线代替一个字符 如果是第三个字母 那么使用两个下划线
3 SELECT e.ENAME FROM EMP e WHERE e.ENAME like '__A%';
4 --使用截取 截取第三个字符且截取一个字符
5 SELECT e.ENAME FROM EMP e WHERE substr(e.ename,3,1)='A';

1 --2.找出EMP表员工名字中含有A 和N的员工姓名
2 -- 使用and 两个like
3 SELECT e.ENAME FROM EMP e WHERE e.ENAME like '%A%' and e.ENAME like '%N%';
4 -- 或者'%A%N%' 这种我没有想到
5 SELECT e.ENAME FROM EMP e WHERE e.ENAME like '%A%N%';

1 --3.找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大 佣金从大到小
2 -- 既然是有佣金 我觉得应该判断 comm is not null 原作者没有判断
3 SELECT e.ENAME,e.SAL+e.COMM,e.COMM FROM EMP e WHERE e.COMM is NOT NULL ORDER BY e.SAL+e.COMM asc,e.COMM desc;

1 --4.列出部门编号为20的所有职位
2 SELECT DISTINCT e.JOB FROM EMP e WHERE e.DEPTNO='20';

1 --5. 列出不属于SALES 的部门
2 -- 好几种方式
3 -- <>、!= 、not in 、not like
4 SELECT * FROM DEPT d WHERE d.DNAME <>'SALES';
5 SELECT * FROM DEPT d WHERE d.DNAME !='SALES';
6 SELECT * FROM DEPT d WHERE d.DNAME not in ('SALES');
7 SELECT * FROM DEPT d WHERE d.DNAME NOT like 'SALES';

1 --6.显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序
2 -- 工资=sal+comm 由于comm有null值 所以要给它一个默认值0 不然和sal相加的时候会没有结果 原答案没有加nvl
3 SELECT e.SAL+nvl(e.COMM,0) wage FROM EMP e WHERE e.SAL+nvl(e.COMM,0)>1500 OR e.SAL+nvl(e.COMM,0)<1000 ORDER BY wage desc;

--7. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪
-- 职位可以使用in('MANAGER','SALESMAN')
-- 年薪计算的时候也要注意null值的相加 使用nvl
SELECT e.ENAME,e.JOB,(e.SAL+nvl(e.COMM,0))*12 yearly_salary FROM EMP e WHERE e.JOB IN ('MANAGER','SALESMAN') and (e.SAL+nvl(e.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;

IS NULL判断某个字段是否为空
=NULL判断某个值是否等于NULL

--9.SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否会报错
-- 不会 存储隐式类型转换

-- 10. 让SELECT 语句的输出结果为
SELECT * FROM SALGRADE;  
SELECT * FROM BONUS;  
SELECT * FROM EMP;  
SELECT * FROM DEPT;

SELECT 'SELECT * FROM '||table_name||';' from user_tables;

--11.列出当前用户有多少张数据表，结果集中存在多少条记录
-- 4
select COUNT(1) from user_tables;
-- 23
SELECT sum(num_rows) from user_tables;

原文地址：http://database.51cto.com/art/201106/270285_2.htm