--------1. 找出EMP表中的姓名(ENAME)第三个字母是A 的员工姓名。---------SQL>SELECTENAMEFROMSCOTT.EMPWHEREENAMELIKE'__A%';
ENAME----------ADAMS
BLAKE
CLARK-------2. 找出EMP表员工名字中含有A 和N的员工姓名。----------SQL>SELECTENAMEFROMSCOTT.EMPWHEREENAMELIKE'%A%'ANDENAMELIKE'%N%';
ENAME----------ALLEN
MARTIN
WANGJING--------或--------SQL>SELECTENAMEFROMSCOTT.EMPWHEREENAMELIKE'%A%N%';
ENAME----------ALLEN
MARTIN
WANGJING/*--------3. 找出所有有佣金的员工,列出姓名、工资、佣金,显示结果按工资从小到大,
佣金从大到小。----------*/SQL>SELECTENAME,SAL+COMMASWAGE,COMM2FROMSCOTT.EMP3ORDERBYWAGE,COMMDESC;
ENAME WAGE COMM---------- ---------- ---------TURNER15000.00WARD1750500.00ALLEN1900300.00MARTIN26501400.00EricHu551414.00WANGJING551414.00huyong551414.00SMITH
JONES
JAMES
MILLER
FORD
ADAMS
BLAKE
CLARK
SCOTT
KING17rows selected-------4. 列出部门编号为20的所有职位。----------SQL>SELECTDISTINCTJOBFROMEMPWHEREDEPTNO=20;
JOB---------ANALYST
CLERK
MANAGER-------5. 列出不属于SALES 的部门。----------SQL>SELECTDISTINCT*FROMSCOTT.DEPTWHEREDNAME<>'SALES';
DEPTNO DNAME LOC------ -------------- -------------10ACCOUNTING NEW YORK20RESEARCH DALLAS40OPERATIONS BOSTON5050abc 50def60Developer HaiKou110信息科 海口6rows selected--或者:SQL>SELECTDISTINCT*FROMSCOTT.DEPTWHEREDNAME!='SALES';
SQL>SELECTDISTINCT*FROMSCOTT.DEPTWHEREDNAMENOTIN('SALES');
SQL>SELECTDISTINCT*FROMSCOTT.DEPTWHEREDNAMENOTLIKE'SALES';---6. 显示工资不在1000 到1500 之间的员工信息:名字、工资,按工资从大到小排序。---------SQL>SELECTENAME,SAL+COMMASWAGEFROMSCOTT.EMP2WHERESAL+COMMNOTBETWEEN1000AND15003ORDERBYWAGEDESC;
ENAME WAGE---------- ----------EricHu5514huyong5514WANGJING5514MARTIN2650ALLEN1900WARD17506rows selected--或者SQL>SELECTENAME,SAL+COMMASWAGEFROMSCOTT.EMP2WHERESAL+COMM<1000ORSAL+COMM>15003ORDERBYWAGEDESC;
ENAME WAGE---------- ----------EricHu5514huyong5514WANGJING5514MARTIN2650ALLEN1900WARD17506rows selected/*----- 7. 显示职位为MANAGER 和SALESMAN,年薪在15000 和20000 之间的员工的信息:名字、职位、年薪。----------*/SQL>SELECTENAME 姓名,JOB 职位,(SAL+COMM)*12AS年薪2FROMSCOTT.EMP3WHERE(SAL+COMM)*12BETWEEN15000AND200004ANDJOBIN('MANAGER','SALESMAN');
姓名 职位 年薪---------- --------- ----------TURNER SALESMAN18000/*----- 8. 说明以下两条SQL语句的输出结果:
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
----------*/SQL>SELECTEMPNO,COMMFROMEMPWHERECOMMISNULL;
EMPNO COMM----- ---------736975667698778277887839787679007902793410rows selected---------------------------------------------------------------SQL>SELECTEMPNO,COMMFROMEMPWHERECOMM=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||';'FROMUSER_TABLES;'SELECT*FROM'||TABLE_NAME||';'---------------------------------------------SELECT*FROMBONUS;SELECT*FROMEMP;SELECT*FROMDEPT;--......等等,在此不列出。---10. 语句SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错?---------SQL>SELECTENAME,SALFROMEMPWHERESAL>'1500';
ENAME SAL---------- ---------ALLEN1600.00JONES2975.00BLAKE2850.00CLARK2450.00SCOTT4000.00KING5000.00FORD3000.00EricHu5500.00huyong5500.00WANGJING5500.0010rows selected
SQL>SELECTENAME,SALFROMEMPWHERESAL>1500;
ENAME SAL---------- ---------ALLEN1600.00JONES2975.00BLAKE2850.00CLARK2450.00SCOTT4000.00KING5000.00FORD3000.00EricHu5500.00huyong5500.00WANGJING5500.0010rows selected--说明不会抱错,这儿存在隐式数据类型的。