Oracle sql语句练习

最新推荐文章于 2024-07-18 15:32:08 发布

_SnowMultiflora

最新推荐文章于 2024-07-18 15:32:08 发布

阅读量1.6k

点赞数

分类专栏：数据库文章标签： Oracle SQL

本文链接：https://blog.csdn.net/Xw_Vivian/article/details/98484049

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

完成如下的SQL语句练习：

每个员工的所有信息

SQL> select * from empscott;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

14 rows selected

每个人的部门编号，姓名，薪水

SQL> select deptno,ename,sal from empscott;

DEPTNO ENAME SAL

------ ---------- ---------

20 SMITH 800.00

30 ALLEN 1600.00

30 WARD 1250.00

20 JONES 2975.00

30 MARTIN 1250.00

30 BLAKE 2850.00

10 CLARK 2450.00

20 SCOTT 3000.00

10 KING 5000.00

30 TURNER 1500.00

20 ADAMS 1100.00

30 JAMES 950.00

20 FORD 3000.00

10 MILLER 1300.00

14 rows selected

每个人的年薪

SQL> select ename,sal*12 from empscott;

ENAME SAL*12

---------- ----------

SMITH 9600

ALLEN 19200

WARD 15000

JONES 35700

MARTIN 15000

BLAKE 34200

CLARK 29400

SCOTT 36000

KING 60000

TURNER 18000

ADAMS 13200

JAMES 11400

FORD 36000

MILLER 15600

14 rows selected

求每个人的年薪，列的别名：“年薪”

SQL> select ename,sal*12 as "年薪" from empscott;

ENAME 年薪

---------- ----------

SMITH 9600

ALLEN 19200

WARD 15000

JONES 35700

MARTIN 15000

BLAKE 34200

CLARK 29400

SCOTT 36000

KING 60000

TURNER 18000

ADAMS 13200

JAMES 11400

FORD 36000

MILLER 15600

14 rows selected

求 10 这个部门的所有员工

SQL> select * from empscott where deptno=10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7839 KING PRESIDENT 1981/11/17 5000.00 10

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

求名字是 KING 的这个人的信息

SQL> select * from empscott where ename='KING';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7839 KING PRESIDENT 1981/11/17 5000.00 10

求薪水大于 2000 的员工信息

SQL> select * from empscott where SAL>2000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

6 rows selected

求部门不是 10 的员工信息

SQL> select * from empscott where deptno<>10;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

11 rows selected

求薪水在 800 和 1500 之间的员工信息(包含 800 和 1500)

SQL> select * from empscott where SAL BETWEEN 800 AND 1500;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

7 rows selected

列出 deptno 为 10 或者 30，并且工资>2000 的所有人

SQL> select * from empscott where deptno in(10,30) and sal>2000;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7839 KING PRESIDENT 1981/11/17 5000.00 10

利用 in 操作符，列出部门 10 和 20 的人员

SQL> select * from empscott where deptno in(10,20);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7839 KING PRESIDENT 1981/11/17 5000.00 10

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

8 rows selected

利用 like 操作符，查处名字中含有"H"的人员

SQL> select * from empscott where ename like '%H%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980/12/17 800.00 20

分别利用 like 操作符和正则表达式，查处名字中含有"S"或者"M"的人员

SQL> select * from empscott where ename like '%S%' or ename like '%M%';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7369 SMITH CLERK 7902 1980/12/17 800.00 20

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7876 ADAMS CLERK 7788 1987/5/23 1100.00 20

7900 JAMES CLERK 7698 1981/12/3 950.00 30

7934 MILLER CLERK 7782 1982/1/23 1300.00 10

7 rows selected

计算 emp 表中的所有人员的平均薪水

SQL> select avg(sal) from empscott;

AVG(SAL)

----------

2073.21428

计算 emp 表中最高薪水

SQL> select max(sal) from empscott;

MAX(SAL)

----------

5000

计算 emp 表中最低薪水

SQL> select min(sal) from empscott;

MIN(SAL)

----------

800

计算 emp 表中薪水大于 1000 的人员的个数

SQL> select count(*) from empscott where sal>1000;

COUNT(*)

----------

计算 emp 表中薪水的总和

SQL> select sum(sal) from empscott;

SUM(SAL)

----------

29025

计算 emp 表中薪水和津贴的总和

SQL> select sum(sal)+sum(comm) from empscott;

SUM(SAL)+SUM(COMM)

------------------

31225

求各部门最高薪水

SQL> select deptno,max(sal) from empscott group by deptno;

DEPTNO MAX(SAL)

------ ----------

30 2850

20 3000

10 5000

按照部门和职位分组，分别求最高薪水，该组人员个数

SQL> select deptno,job,max(sal),count(*) from empscott group by deptno,job order by deptno;

DEPTNO JOB MAX(SAL) COUNT(*)

------ --------- ---------- ----------

10 CLERK 1300 1

10 MANAGER 2450 1

10 PRESIDENT 5000 1

20 ANALYST 3000 2

20 CLERK 1100 2

20 MANAGER 2975 1

30 CLERK 950 1

30 MANAGER 2850 1

30 SALESMAN 1600 4

9 rows selected

求薪水最高的员工姓名

SQL> select ename from empscott where sal=(select max(sal) from empscott);

ENAME

----------

KING

求平均薪水是 2000 以上的部门

SQL> select e.deptno,d.dname,avg(sal) from empscott e inner join deptscott d on d.deptno=e.deptno group by e.deptno,d.dname having avg(sal)>2000;

DEPTNO DNAME AVG(SAL)

------ -------------- ----------

10 ACCOUNTING 2916.66666

20 RESEARCH 2175

求每个部门的平均薪水，并按照薪水降序排列

SQL> select deptno,avg(sal) from empscott group by deptno order by avg(sal) desc;

DEPTNO AVG(SAL)

------ ----------

10 2916.66666

20 2175

30 1566.66666

求每个部门薪水在 1200 以上的雇员的平均薪水、最高薪水，并且分组结果

中只包含平均薪水大于 1500 的部门，排序按照部门平均薪水倒序排列

SQL> select deptno,avg(t.sal),max(t.sal) from (select deptno,ename,sal from empscott where sal>1200 group by deptno,ename,sal)t having avg(t.sal)>1500 group by deptno order by avg(t.sal) desc;

DEPTNO AVG(T.SAL) MAX(T.SAL)

------ ---------- ----------

20 2991.66666 3000

10 2916.66666 5000

30 1690 2850

把雇员按部门分组，求最高薪水，部门号，过滤掉名字中第二个字母是'A'的，要

求分组后的平均薪水>1500，按照部门编号倒序排列

SQL> select deptno,max(sal) from empscott where ename not like '_A%' group by deptno having avg(sal)>1500 order by deptno desc;

DEPTNO MAX(SAL)

------ ----------

30 2850

20 3000

10 5000

求平均薪水最高的部门的部门编号

SQL> select deptno from (select deptno, avg(sal) from empscott group by deptno order by avg(sal) desc) where rownum=1;

DEPTNO

------

求出 emp 表中哪些人是经理人，打印出名字和编号

SQL> select ename,empno from empscott where job='MANAGER';

ENAME EMPNO

---------- -----

JONES 7566

BLAKE 7698

CLARK 7782

求比普通员工的最高薪水还要高的经理人名称

SQL> select ename,empno from empscott where job='MANAGER' AND SAL>(SELECT MAX(SAL) FROM EMPSCOTT WHERE JOB<>'MANAGER');

ENAME EMPNO

---------- -----

每个部门平均薪水的等级(需要用到表的连接)

SQL> select e.deptno, s.grade from (select deptno, avg(sal) avg_sal from empSCOTT group by deptno) e join salgrade s on e.avg_sal between s.losal and s.hisal;

DEPTNO GRADE

------ ----------

30 3

20 4

10 4

求部门经理人中平均薪水最低的部门名称

SQL> select deptno, d.dname from (select deptno, avg(sal) from empSCOTT where job='MANAGER' group by deptno order by avg(sal)) natural join deptSCOTT d where rownum=1;

DEPTNO DNAME

------ --------------

10 ACCOUNTING

求薪水最高的前 5 名雇员

SQL> select * from (select * from empSCOTT order by sal desc) where rownum<=5;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

7839 KING PRESIDENT 1981/11/17 5000.00 10

7788 SCOTT ANALYST 7566 1987/4/19 3000.00 20

7902 FORD ANALYST 7566 1981/12/3 3000.00 20

7566 JONES MANAGER 7839 1981/4/2 2975.00 20

7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30

求薪水最高的第 6 到第 10 名雇员

SQL> select * from (select rownum rn, t.* from (select * from empSCOTT order by sal desc) t where rownum<=10) tt where tt.rn>5 and rownum<=10;

RN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ----- ---------- --------- ----- ----------- --------- --------- ------

6 7782 CLARK MANAGER 7839 1981/6/9 2450.00 10

7 7499 ALLEN SALESMAN 7698 1981/2/20 1600.00 300.00 30

8 7844 TURNER SALESMAN 7698 1981/9/8 1500.00 0.00 30

9 7934 MILLER CLERK 7782 1982/1/23 1300.00 10

10 7521 WARD SALESMAN 7698 1981/2/22 1250.00 500.00 30

_SnowMultiflora

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
Oracle sql语句练习

完成如下的SQL语句练习：每个员工的所有信息 SQL> select * from empscott; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ---------- --------- ----- ----------- --------- ---...
复制链接

扫一扫

专栏目录