Oracle 经典语法(一)

作者:%e6%9e%97+%e7%92%90 和oracle相关  

员工表 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                  部门编号

提示:工资 = 薪金 + 佣金


部门表 dept
Name   Type         Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)                         部门编号       
DNAME  VARCHAR2(14) Y                    部门名称    
LOC    VARCHAR2(13) Y                    地点 

 

select * from dept;
select * from emp;

1.列出至少有一个员工的所有部门。

 select dname from dept where deptno in
 (select deptno from emp)
 
 
 
2.列出薪金比“SMITH”多的所有员工。

 

 select * from emp where sal>(select sal from emp where ename='SMITH')
 
 
 
3.列出所有员工的姓名及其直接上级的姓名。

select a.ename ,(select ename from emp b where b.empno= a.mgr )as boss
from emp a

 

4.列出受雇日期早于其直接上级的所有员工。

select ename from emp a  where a.hiredate<
(select hiredate from emp b
where b.empno=a.mgr )

 

5.列出部门名称和这些部门的员工信息,
同时列出那些没有员工的部门


select a.dname,b.empno,b.ename,b.job,
b.mgr,b.hiredate,b.sal,b.deptno
from dept a left join emp b on a.deptno=b.deptno

 

6.列出所有“CLERK”(办事员)的姓名及其部门名称。


select a.ename ,b.dname from emp a
join dept b on a.deptno=b.deptno
where job='CLERK'

 

7.列出最低薪金大于1500的各种工作

select distinct job from emp
 group by job having min(sal)>1500

 

8.列出在部门“SALES”(销售部)工作的员工的姓名,
假定不知道销售部的部门编号。

 

select ename from emp where deptno =
(select deptno from dept
where dname='SALES ')

9.列出薪金高于公司平均薪金的所有员工。
 

select * from emp where sal >(select avg(sal) from  emp)

 

10.列出与“SCOTT”从事相同工作的所有员工。


select * from emp where job=
(select job from emp where ename='SCOTT')

 

11.列出薪金等于部门30中员工的薪金的所有员工的姓名
和薪金。

 

select ename,sal from emp where sal in
( select sal from emp where deptno=30)
and deptno not in 30

 

12.列出薪金高于在部门30工作的所有员工的薪金的
员工姓名
和薪金。
select ename,sal from emp where sal >
( select max(sal) from emp where deptno=30)
and deptno not in 30

 


select ename,sal from emp where sal>
(select max(sal) from emp where deptno=30);


13.列出在每个部门工作的员工数量、平均工资
select deptno , count(*) ,avg(sal)
from emp group by deptno


select (select b.dname from dept b where b.deptno=a.deptno) as dname ,
count(deptno) as deptcount,avg(sal)  from emp a group by deptno


14.列出所有员工的姓名、部门名称和工资。

 

select ename,dname,sal from emp a
 left join dept b on a.deptno=b.deptno


15.列出所有部门的详细信息和部门人数。

select a.deptno,a.dname,a.loc,
(select count(deptno) from emp b
where a.deptno=b.deptno group by deptno )
as deptcount from dept a

 

16.列出各种工作的最低工资。


select job,min(sal) from emp group by job

 

17.列出各个部门的MANAGER(经理)的最低薪金。

 

select deptno,min(sal) from emp where
job='MANAGER' group by deptno

 


18.列出所有员工的年工资,按年薪从低到高排序。


select ename,(sal+nvl(comm,0))*12 as salpersal
from emp order by salpersal asc

 

  -- nul 函数    格式为:NVL( string1, replace_with) 功能:如果string1为NULL,则NVL函数返回replace_with的值,否则返回string1的值。

 
 
  查询第二讲
 
 
 

1. 找出EMP表中的姓名(ENAME)第三个字母是A 的员工姓名。

 

SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '__A%';
sub

 

2. 找出EMP表员工名字中含有A 和N的员工姓名。

 

SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%N%';
SELECT ENAME FROM SCOTT.EMP WHERE ENAME LIKE '%A%N%';

 

3. 找出所有有佣金的员工,列出姓名、工资、佣金,
显示结果按工资从小到大,
佣金从大到小。
select * from emp where comm is null

 

SELECT ENAME,SAL + COMM AS WAGE,COMM  FROM SCOTT.EMP
 ORDER BY WAGE,COMM DESC;


4. 列出部门编号为20的所有职位。

 

 SELECT DISTINCT JOB FROM EMP WHERE DEPTNO = 20;
 
 

5. 列出不属于SALES 的部门。

 

SELECT DISTINCT * FROM DEPT WHERE DNAME <> 'SALES';
SELECT DISTINCT * FROM DEPT WHERE DNAME not in
( 'SALES');

 


6. 显示工资不在1000 到1500 之间的员工信息
:名字、工资,按工资从大到小排序。

 

SELECT ENAME,(SAL + COMM) AS WAGE FROM SCOTT.EMP 
WHERE SAL + COMM NOT BETWEEN 1000 AND 1500
 ORDER BY WAGE DESC;


SELECT ENAME,SAL + COMM AS WAGE FROM SCOTT.EMP
 WHERE WAGE < 1000 OR WAGE > 1500
  ORDER BY WAGE DESC;


7. 显示职位为MANAGER 和SALESMAN,
年薪在15000 和20000 之间的员工的信息:
名字、职位、年薪。


SELECT ENAME 姓名,JOB 职位,(SAL + COMM) * 12 AS 年薪
 FROM EMP 
 WHERE (SAL + COMM) * 12
 BETWEEN 15000 AND 20000 
 AND JOB IN('MANAGER','SALESMAN');
 
 
8. 说明以下两条SQL语句的输出结果:


SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;

 

--说明:IS NULL是判断某个字段是否为空,为空并不等价于为空字符串或为数字0;
--而 =NULL 是判断某个值是否等于 NULL,NULL = NULL和NULL <> NULL都为 FALSE。

 

9. 让SELECT 语句的输出结果为
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
……
列出当前用户有多少张数据表,结果集中存在多少条记录。


 SELECT 'SELECT * FROM '||TABLE_NAME||';'
  FROM USER_TABLES;
 
 

10. 判断SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'
是否抱错,为什么?

 

SELECT ENAME,SAL FROM EMP WHERE SAL > '1500';
SELECT ENAME,SAL FROM EMP WHERE SAL > 1500;
--不会抱错,这儿存在隐式数据类型的。

查询第三讲

1. 让SELECT LTRIM(TO_CHAR(sal,'L99,999.99'))
 FROM emp WHERE  ROWNUM < 5
输出结果的货币单位是¥和$。
LTRIM和RTRIM
--LTRIM  删除左边出现的字符串
---RTRIM  删除右边出现的字符串


SELECT TO_CHAR(sal,'L99,999.99') FROM emp WHERE  ROWNUM < 5;
SELECT TO_CHAR(sal,'$99,999.99') FROM emp WHERE  ROWNUM < 5;

/*--说明:对于'$99,999.99'格式符:
L:表示强制显示当地货币符号
$: 表示显示美元符号
9: 表示一个数字
0: 表示强制0显示
.: 表示一个小数点
,: 表示一个千位分隔符
--------------*/

2. 列出前五位每个员工的名字,工资、涨薪后的的工资
(涨幅为8%),
以“元”为单位进行四舍五入。

SELECT ename,sal,ROUND(sal * 1.08,2) FROM emp
 WHERE ROWNUM <=5;


3. 找出谁是最高领导,将名字按大写形式显示。


SELECT UPPER(ename) AS NAME   FROM  emp 
WHERE  mgr is null;


4. 找出SMITH 的直接领导名字。


SELECT  ename AS NAME  FROM emp where empno in
(select mgr from emp where ename ='SMITH')


6. 哪些员工的工资高于他直接上司的工资,
列出员工的名字和工资,上司的名字和工资。
select a.ename,a.sal, b.ename,b.sal
 from emp a,emp b where a.mgr=b.empno
and a.sal>b.sal

--SELECT E.ENAME,E.SAL,M.ENAME,M.SAL  FROM EMP E,EMP M
--WHERE  E.EMPNO = M.EMPNO AND E.SAL > M.SAL;


--SELECT E.ENAME,E.SAL FROM EMP E WHERE E.SAL >
--(SELECT M.SAL FROM EMP M   WHERE E.EMPNO = M.EMPNO);


7. 哪些员工和SMITH同部门。

SELECT ENAME  FROM EMP WHERE DEPTNO IN
(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH')

8. 哪些员工跟SMITH做一样职位。

SELECT ENAME  FROM EMP WHERE JOB=
(SELECT JOB FROM EMP WHERE ENAME='SMITH')

9. 哪些员工跟SMITH不在同一个部门。

SELECT ENAME  FROM EMP WHERE DEPTNO NOT IN
(SELECT DEPTNO FROM EMP WHERE ENAME='SMITH')


10. 哪些员工跟SMITH做不一样的职位。

SELECT ENAME  FROM EMP WHERE JOB !=
(SELECT JOB FROM EMP WHERE ENAME='SMITH')

11. 显示有提成的员工的信息:
名字、提成、所在部门名称、所在地区的名称。


SELECT E.ENAME, E.COMM,D.DNAME,D.LOC FROM EMP E,DEPT D 
WHERE E.DEPTNO = D.DEPTNO AND
(E.COMM IS NOT NULL AND E.COMM >0) ;

12. 显示 RESEARCH部门有哪些职位。


SELECT DISTINCT E.JOB FROM EMP E,DEPT D  WHERE
D.DEPTNO = E.DEPTNO AND D.DNAME = ' RESEARCH';

13. 整个公司中,最高工资和最低工资相差多少。

 

 SELECT MAX(SAL) - MIN(SAL) FROM EMP

14. 提成大于0 的人数。

 SELECT COUNT(*) AS 提成大于0的人数
  FROM emp WHERE comm > 0;


15. 显示整个公司的最高工资、
最低工资、工资总和、
平均工资保留到整数位。

 

SELECT MAX(sal) AS 最高工资, MIN(sal) AS 最低工资,
SUM(sal) AS 工资总和,
ROUND(AVG(sal)) AS 平均工资 FROM emp


16. 整个公司有多少个领导。

SELECT COUNT(DISTINCT(mgr)) 
FROM emp WHERE mgr IS NOT NULL


17. 列出在同一部门入职日期晚但工资高于其他同事的员工:
名字、工资、入职日期。

SELECT DISTINCT E1.ename AS 姓名, E1.sal AS 工资,
E1.hiredate AS 入职日期
 FROM emp E1,emp E2  WHERE
  e1.empno = E2.empno AND E1.hiredate > E2.hiredate
 AND E1.sal > E2.sal ORDER BY 工资 DESC;
 

查询 第四讲

1. 各个部门平均、最大、最小工资、人数,
按照部门号升序排列。


SELECT deptno AS 部门号,AVG(sal) AS 平均工资 ,
MAX(sal) AS 最高工资,MIN(sal)
 AS 最低工资 ,COUNT(*) AS 人数
 FROM emp GROUP BY deptno  
 ORDER BY deptno ASC;

2. 各个部门中工资大于5000的员工人数。

SELECT deptno,COUNT(*) FROM emp WHERE
sal > 5000  GROUP BY deptno;

3. 各个部门平均工资和人数,按照部门名字升序排列。

SELECT DNAME,AVG(SAL),COUNT(*) FROM
(SELECT
(SELECT DEPT.DNAME FROM DEPT WHERE
DEPT.DEPTNO = EMP.DEPTNO)
 DNAME,EMP.SAL FROM  EMP)
 GROUP BY DNAME  ORDER BY DNAME;
 
 select dname,sala,num from
 (select deptno ,avg(sal) as sala,count(*) as num from emp
 group by deptno
 order by deptno
 )a
 join  dept on dept.deptno= a.deptno
 order by dname
 
 
4. 列出每个部门中有同样工资的员工的统计信息,
列出他们的部门号,工资,人数。

 SELECT EMP1.DEPTNO,EMP1.SAL,COUNT(*)
   FROM   EMP EMP1,EMP EMP2
   WHERE  EMP1.DEPTNO = EMP2.DEPTNO
   AND  EMP1.SAL = EMP2.SAL
   AND EMP1.EMPNO <> EMP2.EMPNO
   GROUP BY EMP1.DEPTNO,EMP1.SAL;

5. 列出同部门中工资高于1000 的员工数量超过2 人的部门,
显示部门名字、地区名称。
SELECT
D.DNAME,D.LOC,COUNT(*)
     FROM EMP E,DEPT D
     WHERE E.DEPTNO = D.DEPTNO AND
            E.SAL > 1000
     GROUP BY D.DNAME,D.LOC
     HAVING COUNT(*) > 2;

6. 哪些员工的工资,高于整个公司的平均工资,
列出员工的名字和工资(降序)。
SELECT ENAME,SAL
     FROM EMP
     WHERE SAL> (
           SELECT AVG(SAL)
           FROM EMP
           )
     ORDER BY SAL DESC;

7. 哪些员工的工资,介于10号 和30号部门平均工资之间。
SELECT ENAME,SAL
     FROM EMP
     WHERE SAL
     BETWEEN
         (SELECT AVG(SAL) FROM EMP
         WHERE DEPTNO = 10)
     AND (SELECT AVG(SAL) FROM EMP
         WHERE DEPTNO = 80);

8. 所在部门平均工资高于5000 的员工名字。
 SELECT  ENAME,SAL
     FROM EMP
     WHERE DEPTNO IN
            (SELECT DEPTNO FROM EMP
             GROUP BY DEPTNO
             HAVING AVG(SAL) > 5000);

9. 列出各个部门中工资最高的员工的信息:
名字、部门号、工资。
 SELECT ENAME
             ,SAL ,DEPTNO
      FROM EMP
      WHERE (DEPTNO,SAL ) IN
            (SELECT DEPTNO,MAX(SAL)
             FROM EMP
             GROUP BY DEPTNO);


10. 最高的部门平均工资是多少。
SELECT  MAX(AVGSALARY)
     FROM(SELECT DEPTNO,AVG(SAL) AVGSALARY
       FROM EMP
        GROUP BY DEPTNO);
       

查询 第五讲

1. 哪些部门的人数比20 号部门的人数多。

 

SELECT DEPTNO,COUNT(*) FROM EMP
    GROUP BY DEPTNO
      HAVING COUNT(*) >
            (SELECT COUNT(*) FROM EMP
             WHERE DEPTNO = 20
            );
           
2. SMITH的领导是谁(非关联子查询)。

SELECT ENAME
      FROM EMP
     WHERE EMPNO in
            (SELECT MGR FROM EMP
            WHERE ENAME='SMITH'
           );

3.FORD 领导谁(非关联子查询)。
SELECT  ENAME
     FROM EMP
      WHERE MGR IN
           (SELECT EMPNO FROM EMP
             WHERE ENAME='FORD'  
                 );                
      5. FORD 领导谁(关联子查询)。
SELECT ENAME
      FROM EMP  E1
     WHERE EXISTS (
           SELECT 1 FROM EMP E2
           WHERE ENAME='FORD'
            AND E2.EMPNO = E1.MGR);           

4. SMITH 的领导是谁(关联子查询)。
SELECT ENAME
      FROM EMP E1
      WHERE EXISTS (
            SELECT 1 FROM EMP   E2
            WHERE ENAME='SMITH'
            AND E2.MGR = E1.EMPNO);

5. FORD 领导谁(关联子查询)。
SELECT ENAME
      FROM EMP  E1
     WHERE EXISTS (
           SELECT 1 FROM EMP E2
           WHERE ENAME='FORD'
            AND E2.EMPNO = E1.MGR);


6. 列出在同一部门共事,入职日期晚但工资高于其他同事的员工:
名字、工资、入职日期
(关联子查询)。
 SELECT ename 姓名,
             SAL  AS 工资,HIREDATE AS 入职日期
     FROM EMP E1
     WHERE EXISTS (
            SELECT 1 FROM EMP E2
           WHERE E2.DEPTNO = E1.DEPTNO
           AND   E1.HIREDATE > E2.HIREDATE
            AND   E1.SAL    > E2.SAL
           );


7. 哪些员工跟SMITH不在同一个部门(非关联子查询)。
SELECT ENAME
     FROM EMP a
           WHERE
          not EXISTS
           (SELECT 1 FROM EMP b
            WHERE b.ENAME='SMITH'
            and a.deptno=b.deptno
           );
          
           SELECT ENAME
      FROM EMP  E1
     WHERE EXISTS (
           SELECT 1 FROM EMP E2
           WHERE ENAME='FORD'
            AND E2.EMPNO = E1.MGR);


8. 哪些员工跟SMITH不在同一个部门(关联子查询)。
SELECT ENAME
      FROM EMP  E1
      WHERE NOT EXISTS (
            SELECT 1 FROM EMP  E2
           WHERE E1.DEPTNO = E2.DEPTNO
            AND E2.ENAME='SMITH'
           );

9.  RESEARCH部门有哪些职位(非关联子查询)。
 SELECT DISTINCT JOB  FROM EMP
      WHERE DEPTNO = (
           SELECT DEPTNO FROM DEPT
            WHERE DNAME = ' RESEARCH');  

10.  RESEARCH部门有哪些职位(关联子查询)。
SELECT DISTINCT JOB  FROM EMP
     WHERE EXISTS(
           SELECT 1 FROM DEPT
            WHERE EMP.DEPTNO = DEPT.DEPTNO
           AND DEPT.DNAME = ' RESEARCH');
    

 


 

相关资料:

Oracle 经典语法(一)来源网络,如有侵权请告知,即处理!

编程Tags: