Oracle语句及查询结果

 

 

SQL> conn scott/scott
已连接。
SQL> --1.查询员工的所用数据
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7369 SMITH      CLERK           7902 17-12月-80            800            
        20                                                                      
                                                                                
      7499 ALLEN      SALESMAN        7698 20-2月 -81           1600        300 
        30                                                                      
                                                                                
      7521 WARD       SALESMAN        7698 22-2月 -81           1250        500 
        30                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7566 JONES      MANAGER         7839 02-4月 -81           2975            
        20                                                                      
                                                                                
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400 
        30                                                                      
                                                                                
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850            
        30                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7782 CLARK      MANAGER         7839 09-6月 -81           2450            
        10                                                                      
                                                                                
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000            
        20                                                                      
                                                                                
      7839 KING       PRESIDENT            17-11月-81           5000            
        10                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0 
        30                                                                      
                                                                                
      7876 ADAMS      CLERK           7788 23-5月 -87           1100            
        20                                                                      
                                                                                
      7900 JAMES      CLERK           7698 03-12月-81            950            
        30                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7902 FORD       ANALYST         7566 03-12月-81           3000            
        20                                                                      
                                                                                
      7934 MILLER     CLERK           7782 23-1月 -82           1300            
        10                                                                      
                                                                                

已选择14行。

SQL> --2.查询职位（JOB）为“PRESIDENT”的员工的工资
SQL> select sal from emp where job ='PRESIDENT';

       SAL                                                                     
----------                                                                      
      5000                                                                      

SQL> --3.查询佣金（COMM）为0或为NULL的员工信息
SQL> select * from emp where comm=0 or comm is null;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7369 SMITH      CLERK           7902 17-12月-80            800            
        20                                                                      
                                                                                
      7566 JONES      MANAGER         7839 02-4月 -81           2975            
        20                                                                      
                                                                                
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850            
        30                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7782 CLARK      MANAGER         7839 09-6月 -81           2450            
        10                                                                      
                                                                                
      7788 SCOTT      ANALYST         7566 19-4月 -87           3000            
        20                                                                      
                                                                                
      7839 KING       PRESIDENT            17-11月-81           5000            
        10                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0 
        30                                                                      
                                                                                
      7876 ADAMS      CLERK           7788 23-5月 -87           1100            
        20                                                                      
                                                                                
      7900 JAMES      CLERK           7698 03-12月-81            950            
        30                                                                      
                                                                                

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7902 FORD       ANALYST         7566 03-12月-81           3000            
        20                                                                      
                                                                                
      7934 MILLER     CLERK           7782 23-1月 -82           1300            
        10                                                                      
                                                                                

已选择11行。

SQL> --4.查询入职时间在1981-5-1到1981-5-31之间的所有员工信息
SQL> select * from emp where hiredate between '01-5月-81'and'31-5月-81';

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7698 BLAKE      MANAGER         7839 01-5月 -81           2850            
        30                                                                      
                                                                                

SQL> --5.查询所有名字长度为4的员工的员工编号
SQL> select deptno , ename from emp where ename like '____';

    DEPTNO ENAME                                                               
---------- ----------                                                           
        30 WARD                                                                 
        10 KING                                                                 
        20 FORD                                                                 

SQL> --6.显示10号部门的所有经理（“MANAGER”）和20号部门的所有职员（“CLERK”）详细
SQL> select * from emp where deptno=10 and job='MANAGER' or deptno=20 and job='CLERK';

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7369 SMITH      CLERK           7902 17-12月-80            800            
        20                                                                      
                                                                                
      7782 CLARK      MANAGER         7839 09-6月 -81           2450            
        10                                                                      
                                                                                
      7876 ADAMS      CLERK           7788 23-5月 -87           1100            
        20                                                                      
                                                                                

SQL> --7.显示姓名中没有L字的员工的详细信息或含有SM子的员工信息
SQL> select * from emp where ename like 'is not L' or ename like '%SM%';

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7369 SMITH      CLERK           7902 17-12月-80            800            
        20                                                                      
                                                                                

SQL> --8.显示各部门经理（“MANAGER”）的工资
SQL> select sal from emp where job ='MANAGER';

       SAL                                                                     
----------                                                                      
      2975                                                                      
      2850                                                                      
      2450                                                                      

SQL> --9.显示佣金（‘COMM’）收入比工资（‘SAL’）高的员工的详细信息
SQL> select * from emp where comm>sal;

     EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
---------- ---------- --------- ---------- -------------- ---------- ---------- 
    DEPTNO                                                                      
----------                                                                      
      7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400 
        30                                                                      
                                                                                

SQL> exit