--------或--------
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%N%';
ENAME
---------- ALLEN MARTIN WANGJING
/*--------3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，
佣金从大到小。----------*/
SQL> SELECT ENAME,SAL + COMM AS WAGE,COMM 2 FROM SCOTT.EMP
3 ORDER BY WAGE,COMM DESC;
ENAME WAGE COMM
---------- ---------- --------- TURNER 1500 0.00 WARD 1750 500.00 ALLEN 1900 300.00 MARTIN 2650 1400.00 EricHu 5514 14.00 WANGJING 5514 14.00 huyong 5514 14.00 SMITH JONES JAMES MILLER FORD ADAMS BLAKE CLARK SCOTT KING
17 rows selected
-------4. 列出部门编号为20的所有职位。---------- SQL> SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 20; JOB
--------- ANALYST
CLERK MANAGER
-------5. 列出不属于SALES 的部门。----------
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME <> 'SALES';
DEPTNO DNAME LOC
------ -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 40 OPERATIONS BOSTON 50 50abc 50def
60 Developer HaiKou 110 信息科 海口
6 rows selected
--或者：
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME != 'SALES';
SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME NOT IN('SALES'); SQL> SELECT DISTINCT * FROM SCOTT.DEPT WHERE DNAME NOT LIKE 'SALES';
---6. 显示工资不在1000 到1500 之间的员工信息：名字、工资，按工资从大到小排序。---------
SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP 2 WHERE SAL + COMM NOT BETWEEN 1000 AND 1500 3 ORDER BY WAGE DESC;
ENAME WAGE
---------- ---------- EricHu 5514 huyong 5514 WANGJING 5514 MARTIN 2650 ALLEN 1900 WARD 1750
6 rows selected --或者
SQL> SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP 2 WHERE SAL + COMM < 1000 OR SAL + COMM > 1500 3 ORDER BY WAGE DESC;
ENAME WAGE
---------- ---------- EricHu 5514 huyong 5514 WANGJING 5514 MARTIN 2650 ALLEN 1900 WARD 1750
6 rows selected
/*----- 7. 显示职位为MANAGER 和SALESMAN，年薪在15000 和20000 之间的员工的信息：名字、职位、年薪。----------*/
SQL> SELECT ENAME 姓名,JOB 职位,(SAL + COMM) * 12 AS 年薪 2 FROM SCOTT.EMP
3 WHERE (SAL + COMM) * 12 BETWEEN 15000 AND 20000 4 AND JOB IN('MANAGER','SALESMAN');
姓名 职位 年薪
---------- --------- ---------- TURNER SALESMAN 18000
/*----- 8. 说明以下两条SQL语句的输出结果： SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL; SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL; ----------*/
SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
EMPNO COMM
----- --------- 7369 7566 7698 7782 7788 7839 7876 7900 7902 7934
10 rows selected
--------------------------------------------------------------- SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
EMPNO COMM
----- ---------
--说明：IS NULL是判断某个字段是否为空，为空并不等价于为空字符串或为数字0；
--而 =NULL 是判断某个值是否等于 NULL，NULL = NULL和NULL <> NULL都为 FALSE。
/*-----9. 让SELECT 语句的输出结果为 SELECT * FROM SALGRADE; SELECT * FROM BONUS; SELECT * FROM EMP; SELECT * FROM DEPT; ??
列出当前用户有多少张数据表，结果集中存在多少条记录。 ----------*/
SQL> SELECT 'SELECT * FROM '||TABLE_NAME||';' FROM USER_TABLES;
'SELECT*FROM'||TABLE_NAME||';'
--------------------------------------------- SELECT * FROM BONUS; SELECT * FROM EMP; SELECT * FROM DEPT;
--......等等，在此不列出。
---10. 语句SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错？---------
SQL> SELECT ENAME,SAL FROM EMP WHERE SAL > '1500';
ENAME SAL
---------- --------- ALLEN 1600.00 JONES 2975.00 BLAKE 2850.00 CLARK 2450.00 SCOTT 4000.00 KING 5000.00 FORD 3000.00 EricHu 5500.00 huyong 5500.00 WANGJING 5500.00