但行好事
莫论前程❤

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

建库脚本及测试数据

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

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部门表

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

提示:工资=薪金+佣金

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

SQL> select * from 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
WARD       SALESMAN   7698 1981-2-22     1250.00    500.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
SCOTT      ANALYST    7566 1987-4-19     4000.00               20
KING       PRESIDENT       1981-11-17    5000.00               10
TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
ADAMS      CLERK      7788 1987-5-23     1100.00               20
JAMES      CLERK      7698 1981-12-3      950.00               30
FORD       ANALYST    7566 1981-12-3     3000.00               20
MILLER     CLERK      7782 1982-1-23     1300.00               10
EricHu     Developer  1455 2011-5-26 1   5500.00     14.00     10
huyong     PM         1455 2011-5-26 1   5500.00     14.00     10
WANGJING   Developer  1455 2011-5-26 1   5500.00     14.00     10
rows selected

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

SQL> select * from dept;

DEPTNO DNAME          LOC
------ -------------- -------------
ACCOUNTING     NEW YORK
RESEARCH       DALLAS
SALES          CHICAGO
OPERATIONS     BOSTON
50abc          50def
Developer      HaiKou
rows selected

用SQL完成以下问题列表:

1.列出至少有一个员工的所有部门。
2.列出薪金比“SMITH”多的所有员工。
3.列出所有员工的姓名及其直接上级的姓名。
4.列出受雇日期早于其直接上级的所有员工。
5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
6.列出所有“CLERK”(办事员)的姓名及其部门名称。
7.列出最低薪金大于1500的各种工作。
8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。
9.列出薪金高于公司平均薪金的所有员工。
10.列出与“SCOTT”从事相同工作的所有员工。
11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
13.列出在每个部门工作的员工数量、平均工资和平均服务期限。
14.列出所有员工的姓名、部门名称和工资。
15.列出所有部门的详细信息和部门人数。
16.列出各种工作的最低工资。
17.列出各个部门的MANAGER(经理)的最低薪金。
18.列出所有员工的年工资,按年薪从低到高排序。
  • 1.列出至少有一个员工的所有部门。
SQL> select dname from dept where deptno in(select deptno from emp); 
DNAME
--------------
RESEARCH
SALES
ACCOUNTING
--------或--------
SQL> select dname from dept where deptno in(select deptno from emp group by deptno having count(deptno) >=1); 
DNAME
--------------
ACCOUNTING
RESEARCH
SALES
  • 2.列出薪金比“SMITH”多的所有员工。
SQL> select * from emp where sal > (select sal from emp where ename = 'SMITH');

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
JONES      MANAGER    7839 1981-4-2      2975.00               20
MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
KING       PRESIDENT       1981-11-17    5000.00               10
TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
JAMES      CLERK      7698 1981-12-3      950.00               30
huyong     PM         1455 2011-5-26 1   5500.00     14.00     10
WANGJING   Developer  1455 2011-5-26 1   5500.00     14.00     10
rows selected
  • 3.列出所有员工的姓名及其直接上级的姓名
SQL> select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a; 
ENAME      BOSS_NAME
---------- ----------
SMITH      FORD
ALLEN      BLAKE
SCOTT      JONES
KING       
TURNER     BLAKE
FORD       JONES
MILLER     CLARK
EricHu     
huyong     
WANGJING    
rows selected
  • 4.列出受雇日期早于其直接上级的所有员工。
SQL> select a.ename from emp a where a.hiredate<(select hiredate from emp b where b.empno=a.mgr); 
ENAME
----------
SMITH
ALLEN
WARD
CLARK 
rows selected
  • 5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
SQL> 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;

DNAME          EMPNO ENAME      JOB         MGR HIREDATE          SAL DEPTNO
-------------- ----- ---------- --------- ----- ----------- --------- ------
RESEARCH        7369 SMITH      CLERK      7902 1980-12-17     800.00     20
SALES           7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00     30
SALES           7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00     30
ACCOUNTING       105 WANGJING   Developer  1455 2011-5-26 1   5500.00     10
50abc                                                                 
OPERATIONS                                                            
Developer                                                          
rows selected
  • 6.列出所有“CLERK”(办事员)的姓名及其部门名称。
SQL> select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK'; 
ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ADAMS      RESEARCH
JAMES      SALES
MILLER     ACCOUNTING
  • 7.列出最低薪金大于1500的各种工作。
SQL> select distinct job as HighSalJob from emp group by job having min(sal)>1500; 
HIGHSALJOB
----------
ANALYST
Developer
MANAGER
PM
PRESIDENT
  • 8.列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号
SQL> select ename from emp where deptno=(select deptno from dept where dname='SALES'); 
ENAME
----------
ALLEN
WARD
MARTIN
BLAKE
rows selected
  • 9.列出薪金高于公司平均薪金的所有员工。
SQL> select ename from emp where sal>(select avg(sal) from emp); 
ENAME
----------
JONES
BLAKE
SCOTT
KING
huyong
WANGJING 
rows selected
  • 10.列出与“SCOTT”从事相同工作的所有员工。
SQL> select ename from emp where job=(select job from emp where ename='SCOTT');
 ENAME
----------
SCOTT
FORD
  • 11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
SQL> select a.ename,a.sal from emp a where a.sal in (select b.sal
 from emp b where b.deptno=30) and a.deptno<>30; 
ENAME            SAL
---------- ---------
  • 12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
SQL> select ename,sal from emp where sal>(select max(sal) from emp where deptno=30); 
ENAME            SAL
---------- ---------
JONES        2975.00
SCOTT        4000.00
KING         5000.00
FORD         3000.00
WANGJING     5500.00 
rows selected
  • 13.列出在每个部门工作的员工数量、平均工资和平均服务期限。
SQL> select (select b.dname from dept b where a.deptno=b.deptno) as deptname ,count(deptno) as deptcount,avg(sal) as deptavgsal
 from emp a group by deptno; 
DEPTNAME        DEPTCOUNT DEPTAVGSAL
-------------- ---------- ----------
ACCOUNTING              6 4208.33333
RESEARCH                5       2375
SALES                   6 1566.66666
  • 14.列出所有员工的姓名、部门名称和工资。
SQL> select a.ename,(select b.dname from dept b where b.deptno=a.deptno) as deptname,sal from emp a; 

ENAME      DEPTNAME             SAL
---------- -------------- ---------
SMITH      RESEARCH          800.00
ALLEN      SALES            1600.00
WARD       SALES            1250.00
JONES      RESEARCH         2975.00
MARTIN     SALES            1250.00
WANGJING   ACCOUNTING       5500.00
rows selected
  • 15.列出所有部门的详细信息和部门人数。
SQL> select a.deptno,a.dname,a.loc,(select count(deptno) from emp b where b.deptno=a.deptno group by b.deptno) as deptcount from dept a; 
DEPTNO DNAME          LOC            DEPTCOUNT
------ -------------- ------------- ----------
ACCOUNTING     NEW YORK               6
RESEARCH       DALLAS                 5
SALES          CHICAGO                6
OPERATIONS     BOSTON        
50abc          50def         
Developer      HaiKou     
rows selected
  • 16.列出各种工作的最低工资。
SQL> select job,avg(sal) from emp group by job;

JOB         AVG(SAL)
--------- ----------
ANALYST         3500
CLERK         1037.5
Developer       5500
MANAGER   2758.33333
PM              5500
SALESMAN        1400
rows selected
  • 17.列出各个部门的MANAGER(经理)的最低薪金。
SQL> select deptno,min(sal) from emp where job='MANAGER' group by deptno;

DEPTNO   MIN(SAL)
------ ----------
      2450
      2975
      2850
  • 18.列出所有员工的年工资,按年薪从低到高排序。
SQL> select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;

ENAME       SALPERSAL
---------- ----------
SMITH            9600
JAMES           11400
ADAMS           13200
MILLER          15600
TURNER          18000
rows selected
赞(0) 打赏
未经允许不得转载:刘鹏博客 » Oracle数据库sql语句练习(1)
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

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

支付宝扫一扫打赏

微信扫一扫打赏