关闭

oracle 练习

215人阅读 评论(0) 收藏 举报
create table emp(
empno number(4) not null,
ename varchar2(10) ,
job varchar2(9),
mgr number(4),
hiredate date default sysdate,
sal number(7,2),
comm number(7,2),
deptno number(2));

create sequence sq_emp;


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


 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,DEPTNO)values(7369,'SMITH','CLERK',7902 ,800.00,2);
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,COMM,DEPTNO)values(7499 ,'ALLEN',     'SALESMAN',   7698 , 1600.00  ,  300.00   ,  3  );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,COMM,DEPTNO)values(7521 ,'WARD',       'SALESMAN',   7698 ,  1250.00  ,  500.00  ,   3  );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,DEPTNO)values(7566 ,'JONES',      'MANAGER',    7839 ,   2975.00  ,       2  );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,COMM,DEPTNO)values(7654 ,'MARTIN',     'SALESMAN',   7698,   1250.00 ,  1400.00  ,   3 );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,DEPTNO)values(7698, 'BLAKE',      'MANAGER',    7839 ,    2850.00 ,              3 );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,DEPTNO)values(7782 ,'CLARK',      'MANAGER',    7839 , 2450.00  ,         1 );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,DEPTNO)values(7788 ,'SCOTT',      'ANALYST',    7566,    4000.00   ,           2  );
 insert into emp(EMPNO,ENAME,JOB,SAL,DEPTNO)values(7839, 'KING',       'PRESIDENT',    5000.00   ,            1  );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,COMM,DEPTNO)values(7844 ,'TURNER',     'SALESMAN',   7698 ,  1500.00 ,     0.00 ,    3  );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,DEPTNO)values(7876 ,'ADAMS',      'CLERK',      7788 , 1100.00   ,            2 );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,DEPTNO)values(7900, 'JAMES',      'CLERK',      7698 ,  950.00    ,           3 );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,DEPTNO)values(7902, 'FORD',       'ANALYST',    7566 ,   3000.00  ,             2 );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL, DEPTNO)values(7934, 'MILLER',     'CLERK',      7782 ,   1300.00   ,             1  );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,COMM,DEPTNO)values(102, 'EricHu',     'Developer',  1455 ,  5500.00   ,  14.00   ,  1  );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,COMM,DEPTNO)values(104 ,'huyong',     'PM',         1455 ,  5500.00  ,   14.00 ,    1  );
 insert into emp(EMPNO,ENAME,JOB,MGR,SAL,COMM,DEPTNO)values(105 ,'WANGJING',   'Developer',  1455 ,  5500.00   ,  14.00   ,  1  );

-----------------------------------------------------
create table dept(
deptno number(2),
dname varchar2(14),
loc varchar2(13)
);

DEPTNO NUMBER(2)                         部门编号          
DNAME  VARCHAR2(14) Y                    部门名称       
LOC    VARCHAR2(13) Y                    地点

create sequence sq_dept;


insert into dept(deptno,dname,loc)values(sq_dept.nextval,'ACCOUNTING','NEW YORK');
insert into dept(deptno,dname,loc)values(sq_dept.nextval,'RESEARCH','DALLAS');
insert into dept(deptno,dname,loc)values(sq_dept.nextval,'SALES','CHICAGO');
insert into dept(deptno,dname,loc)values(sq_dept.nextval,'OPERATIONS','BOSTON');
insert into dept(deptno,dname,loc)values(sq_dept.nextval,'50abc','50def');
insert into dept(deptno,dname,loc)values(sq_dept.nextval,'Developer','HaiKou');
insert into dept(deptno,dname,loc)values(sq_dept.nextval,'信息科技','海口');

1 ACCOUNTING     NEW YORK  
  2 RESEARCH       DALLAS  
    3 SALES          CHICAGO  
    4 OPERATIONS     BOSTON  
    5 50abc          50def  
    6 Developer      HaiKou  


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

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

 答案:.(1) select dname from dept where deptno in(select distinct deptno from emp) ;

               (2) select dname from dept where deptno in ( select deptno from emp group by deptno having count(deptno)>0);
  2、列出薪金比“SMITH”多的所有员工。
 答案: 2.(1) select ename from emp where sal>(select sal from emp where ename='SMITH');
  3、列出所有员工的姓名及其直接上级的姓名。
 答案:.3.(1) select a.ename,b.ename as boss_name from emp a left join emp b on a.mgr=b.empno;
  4、列出受雇日期早于其直接上级的所有员工。
 答案:.4.(1) select e.ename  from emp e where e.hiredate>(select hiredate from emp where empno=e.mgr);
  5、列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
 答案:.5.(1) select d.* ,e.* from dept d left join emp e on d.deptno=e.deptno;
  6、列出所有“CLERK”(办事员)的姓名及其部门名称。
 答案:.6.(1) select e.* ,d.dname from emp e ,dept d where e.deptno=d.deptno and e.job='CLERK';
  7、列出最低薪金大于1500的各种工作。
 答案:.7.(1) select job from emp group by job having min(sal)>1500;
  8、列出在部门“SALES”(销售部)工作的员工的姓名,假定不知道销售部的部门编号。

 答案:.8.(1) select e.ename from emp e where e.deptno=(select deptno from dept where dname='SALES');

                 (2) select emp.ename from emp inner join dept on emp.deptno=dept.deptno where dept.dname='SALES';

  9、列出薪金高于公司平均薪金的所有员工。
 答案:.9.(1) select emp.ename from emp where emp.sal>(select avg(sal) from emp);
  10、列出与“SCOTT”从事相同工作的所有员工。
 答案:.10.(1)select e.ename from emp e where e.job=(select job from emp where emp.ename='SCOTT');
  11、列出薪金等于部门3中员工的薪金的所有员工的姓名和薪金。
 答案:.11.(1)select a.* from emp a where a.sal in (select sal from emp where deptno=3);
  12、列出薪金高于在部门3工作的所有员工的薪金的员工姓名和薪金。
 答案:.12.(1)select a.* from emp a where a.sal > (select max(sal) from emp where deptno=3);
  13、列出在每个部门工作的员工数量、平均工资和平均服务期限。

 答案:.13.(1)select d.dname, count(e.ename)  员工数量,avg(e.sal+(NVL(e.comm,0))) 平均工资

                            from dept d left join emp e on d.deptno=e.deptno group by e.deptno,d.dname;

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

 答案:.14.(1)select e.ename as 姓名,(e.sal+(NVL(e.comm,0))) as 工资,d.dname as 部门 from emp e,dept d where e.deptno=d.deptno;

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

 答案:.15.(1)select d.* ,(select count(*) from emp where emp.deptno=d.deptno) 部门人数 from dept d;

                   (2)select d.deptno,d.dname,d.loc ,count(e.deptno) 部门人数 from dept d inner join emp e on d.deptno=e.deptno group by e.deptno, d.deptno,d.dname,d.loc;

  16、列出各种工作的最低工资。
 答案:.16.select min(sal+nvl(comm,0)) 最低工资,job 职位 from emp group by job;
  17、列出各个部门的MANAGER(经理)的最低薪金。
 答案:.(1)select min(sal+nvl(comm,0)) 最低工资,job 职位 from emp where job='MANAGER' group by job ;
              (2)select min(sal+nvl(comm,0)) 最低工资,deptno 职位 from emp where job='MANAGER' group by deptno;
  18、列出所有员工的年工资,按年薪从低到高排序。
答案:18.(1)select ename 员工名称, ((sal+nvl(comm,0))*12) 年薪 from emp order by 年薪 ;

用SQL完成以下问题列表:

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

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

  3、找出所有有佣金的员工,列出姓名、工资、佣金,显示结果按工资从小到大,佣金从大到小。

  4、列出部门编号为2的所有职位。

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

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

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

  8、说明以下两条SQL语句的输出结果:

答案:
1.(1)select * from emp where ename like '_A%';
2.(1)select * from emp where ename like '%A%N%';
3.(1)select * from emp where comm is not null order by sal,comm desc;
4.(1)select distinct job from emp where deptno=2;
5.(1)select * from emp where deptno!=(select deptno from dept where dname='SALES');
(2)SELECT DISTINCT * FROM  DEPT WHERE DNAME <> 'SALES';
6.(1)select * from emp where sal not between 1000 and 1500 order by sal desc;
7.select ename,job ,(sal+nvl(comm,0))*12 as 年薪 from emp where job in('SALESMAN','MANAGER') and (sal+nvl(comm,0))*12 between 15000 and 20000;

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


查看用户权限

select * from user_role_privs









0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:979759次
    • 积分:8513
    • 等级:
    • 排名:第2461名
    • 原创:125篇
    • 转载:448篇
    • 译文:3篇
    • 评论:37条
    最新评论