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

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

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

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

数据如下:

用SQL完成以下问题列表：

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

解答如下:

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

 

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

 

 

--3.找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大 佣金从大到小
-- 既然是有佣金 我觉得应该判断 comm is not null 原作者没有判断
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;

 

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

 

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

 

--6.显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序
-- 工资=sal+comm 由于comm有null值 所以要给它一个默认值0 不然和sal相加的时候会没有结果 原答案没有加nvl
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