经典查询练手第二篇

--经典查询练手第二篇

SQL> DESC SCOTT.EMP;
Name Type Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO NUMBER(4) 员工编号
ENAME VARCHAR2(10) Y 员工姓名
JOB VARCHAR2(9) Y 职位
MGR NUMBER(4) Y 上级编号
HIREDATE DATE Y 雇佣日期
SAL NUMBER(7,2) Y 薪金
COMM NUMBER(7,2) Y 佣金
DEPTNO NUMBER(2) Y 所在部门编号
--提示：工资 ＝ 薪金 ＋ 佣金
scott.dept部门表
SQL> DESC SCOTT.DEPT;
Name Type Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(3) 部门编号
DNAME VARCHAR2(14) Y 部门名称
LOC VARCHAR2(13) Y 地点

1.找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
2.找出EMP表员工名字中含有A 和N的员工姓名。
3.找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
4.列出部门编号为20的所有职位。
5.列出不属于SALES 的部门。
6.显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。
7.显示职位为MANAGER 和SALESMAN，年薪在15000 和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 * FROM DEPT;
„„
10.列出当前用户有多少张数据表，结果集中存在多少条记录。
11.判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错，为什么？

1.找出EMP表中的姓名（ENAME）第三个字母是A 的员工姓名。
SCOTT@PROD2> select ename from emp where substr(ename,3,1)='A';
--SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';  两种方式都可。

ENAME
----------
BLAKE
CLARK
ADAMS

2.找出EMP表员工名字中含有A 和N的员工姓名。
SCOTT@PROD2> select ename from emp where instr(ename,'A')<>0 and instr(ename,'N')<>0;
--SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%N%';  两种方式都可。

ENAME
----------
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
KING
TURNER
ADAMS
JAMES

10 rows selected.

3.找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
SCOTT@PROD2> select ename,sal,comm from emp where comm <> 0 order by sal,comm desc;

ENAME		  SAL	    COMM
---------- ---------- ----------
MARTIN		 1250	    1400
WARD		 1250	     500
ALLEN		 1600	     300

4.列出部门编号为20的所有职位。
SCOTT@PROD2> select distinct(job) from emp where deptno=20;

JOB
---------
CLERK
MANAGER
ANALYST

5.列出不属于SALES 的部门。
SCOTT@PROD2> select distinct(job) 
from emp,dept d 
where emp.deptno=d.deptno and d.dname <> 'SALES';

JOB
---------
CLERK
PRESIDENT
MANAGER
ANALYST

6.显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。
SCOTT@PROD2> select ename,sal+nvl(comm,0) as mon 
from emp 
where sal+nvl(comm,0)<1000 or sal+nvl(comm,0)>1500 
order by  mon desc;

ENAME		  MON
---------- ----------
KING		 5000
SCOTT		 3000
FORD		 3000
JONES		 2975
BLAKE		 2850
MARTIN		 2650
CLARK		 2450
ALLEN		 1900
WARD		 1750
JAMES		  950
SMITH		  800

11 rows selected.

7.显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。
SCOTT@PROD2> select ename,job,(sal+nvl(comm,0))*12 t_sal 
from emp 
where (sal+nvl(comm,0))*12 between 15000 and 20000 and (job in ('MANAGER','SALESMAN'));

ENAME	   JOB		  T_SAL
---------- --------- ----------
TURNER	   SALESMAN	  18000

8.说明以下两条SQL语句的输出结果：
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
--第一条输出奖金为空的员工和奖金。
--第二天输出空。

SCOTT@PROD2> SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;

no rows selected

SCOTT@PROD2> SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;

     EMPNO	 COMM
---------- ----------
      7369
      7566
      7698
      7782
      7788
      7839
      7876
      7900
      7902
      7934

10 rows selected.


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

SCOTT@PROD2> select 'SELECT * FROM '||TNAME||';' FROM TAB;

'SELECT*FROM'||TNAME||';'
---------------------------------------------
SELECT * FROM BONUS;
SELECT * FROM DEPT;
SELECT * FROM EMP;
SELECT * FROM SALGRADE;
SELECT * FROM T;


10.列出当前用户有多少张数据表，结果集中存在多少条记录。

11.判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错，为什么？
不报错在比较过程中发生了隐式转换，'1500'字符被转化成了数字1500。