但行好事
莫论前程❤

Oracle数据库sql语句练习(2)

本文使用的实例表结构与表的数据如下:

scott.emp员工表结构如下:

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                地点

scott.emp表的现有数据如下:

SQL> SELECT * FROM SCOTT.EMP;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
SMITH      CLERK      7902 1980-12-17     800.00               20
ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
JONES      MANAGER    7839 1981-4-2      2975.00               20
MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
BLAKE      MANAGER    7839 1981-5-1      2850.00               30
CLARK      MANAGER    7839 1981-6-9      2450.00               10
WANGJING   Developer  1455 2011-5-26 1   5500.00     14.00     10
rows selected

Scott.dept表的现有数据如下:

SQL> SELECT * FROM SCOTT.DEPT;

DEPTNO DNAME          LOC
------ -------------- -------------
信息科         海口
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
Developer      HaiKou
rows selected

用SQL完成以下问题列表:

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. 判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否抱错,为什么?

各试题解答如下:

  • 找出EMP表中的姓名(ENAME)第三个字母是A 的员工姓名。
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';

ENAME
----------
ADAMS
BLAKE
CLARK
  • 找出EMP表员工名字中含有A 和N的员工姓名。
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%N%';

ENAME
----------
ALLEN
MARTIN
WANGJING
--------或--------
SQL> SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%N%';

ENAME
----------
ALLEN
MARTIN
WANGJING
  • 找出所有有佣金的员工,列出姓名、工资、佣金,显示结果按工资从小到大, 佣金从大到小。
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
WANGJING         5514     14.00
huyong           5514     14.00
SMITH                 
JONES                 
JAMES                        
SCOTT                               

17 rows selected
  • 列出部门编号为20的所有职位。
SQL> SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 20;

JOB
---------
ANALYST
CLERK
MANAGER
  • 列出不属于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';
  • 显示工资不在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
  • 显示职位为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
  • 说明以下两条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

10 rows selected
---------------------------------------------------------------
SQL> SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;

EMPNO      COMM
----- ---------

--说明:IS NULL是判断某个字段是否为空,为空并不等价于为空字符串或为数字0;
--而 =NULL 是判断某个值是否等于 NULL,NULL = NULL和NULL <> NULL都为 FALSE。
  • 让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;
--......等等,在此不列出。
  • 语句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
EricHu       5500.00
huyong       5500.00
WANGJING     5500.00

10 rows selected

SQL> SELECT ENAME,SAL FROM EMP WHERE SAL > 1500;

ENAME            SAL
---------- ---------
FORD         3000.00
EricHu       5500.00
huyong       5500.00
WANGJING     5500.00

10 rows selected

–说明不会抱错,这儿存在隐式数据类型的。

赞(0) 打赏
未经允许不得转载:刘鹏博客 » Oracle数据库sql语句练习(2)
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏