oracle练习(三)

预置的表及数据

--------------------------------------------------------------
---------------------员工部门表练习----------------------
--------------------------------------------------------------

--emp员工表
--(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

--dept部门表
--(deptno部门编号/dname部门名称/loc地点)
--工资 = 薪金 + 佣金

/*
--创建部门表
create table DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC    VARCHAR2(13)
)
tablespace SJZ_FORMAL_TABLESPACE;
--给部门表增加主键
alter table DEPT add constraint PK_DEPT primary key (DEPTNO);
insert into DEPT (DEPTNO, DNAME, LOC) values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC) values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC) values (40, 'OPERATIONS', 'BOSTON');
commit;
--创建员工信息表
create table EMP
(
EMPNO    NUMBER(4) not null,
ENAME    VARCHAR2(10),
JOB      VARCHAR2(9),
MGR      NUMBER(4),
HIREDATE DATE,
SAL      NUMBER(7,2),
COMM     NUMBER(7,2),
DEPTNO   NUMBER(2)
)
tablespace SJZ_FORMAL_TABLESPACE;
alter table EMP add constraint PK_EMP primary key (EMPNO);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300, null, 10);
commit; 
--工资的级别
CREATE TABLE SALGRADE(GRADE NUMBER,LOSAL NUMBER,HISAL NUMBER)tablespace SJZ_FORMAL_TABLESPACE;
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;
*/

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

me:select distinct (t.dname), count(e.ename)
  from emp e, dept t
 where e.deptno(+) = t.deptno
 group by t.dname having count(e.ename) > 0;
answer:select deptno,count(*) from emp e group by e.deptno having count(*) > 0;
--解析:该语句主要学习分组函数group by,以及对分组后过滤的条件函数having。
--需要注意的是使用了分组函数语句的select字段中不能包含group by后没有的字段,
--原因是如果显示非分组的字段就可能显示多条记录,就达不到按某字段分组的目的。

--Having只能用在分组函数group by后,相当于对分组后的记录做where条件过滤。

--2.列出薪金比“SMITH”多的所有员工。
me:select * from emp e where e.sal > (select e1.sal from emp e1 where e1.ename = 'SMITH');
--解析:该语句主要学习子查询,且子查询在where条件的后面。注意和3的区别。

--3.列出所有员工的姓名及其直接上级的姓名。
answer:select a.ename, (select ename from emp where empno = a.mgr) as "上级" from emp a;
--解析:该语句主要学习子查询,且子查询在select 和 from的中间,
--同时内部语句使用了外部语句的字段。注意和2的区别。

--4.列出受雇日期早于其直接上级的所有员工。
me:select e.ename, m.ename, e.hiredate, m.hiredate
  from emp e, emp m
 where e.mgr = m.empno(+)
   and e.hiredate < m.hiredate;
answer:select ename from emp a where hiredate>(select hiredate from emp where empno=a.mgr);
--解析:该语句主要学习子查询,且子查询在where条件的后面。同时内部语句使用了外部语句的字段。
--注意和2的区别。注意和2的区别。

--5.列出最低薪金大于1500的各种工作。
select distinct(e.job), min(e.sal) from emp e group by e.job having min(e.sal) > 1500;
--解析:该语句主要学习分组函数,功能同1。

--6.列出在每个部门工作的员工数量、平均工资和平均服务期限。
select d.deptno,d.dname,
       count(e.empno) as "员工数量",
       trunc(avg(e.sal + nvl(e.comm,0))) as "平均工资",
       trunc(avg(sysdate - e.hiredate)/365)||'年' as "受雇期限/年"
  from emp e, dept d
 where e.deptno = d.deptno
 group by d.deptno,d.dname;

select * from emp;
select deptno,
       count(*),
       trunc(avg(sal + nvl(comm, 0))) avgsal,
       trunc(avg(sysdate - hiredate)) avgday
  from emp
 group by deptno;
--解析:该语句主要学习分组函数,同时学习其相关的统计函数count, avg等。

--7.能领取奖金的员工(有佣金的)
select * from emp e where nvl(e.comm, 0) > 0;

--8.能领取奖金并且工资大于1500的员工
select * from emp e where nvl(e.comm, 0) > 0 and e.sal > 1500;

--9.能领取奖金或者工资大于1500的员工
select * from emp e where nvl(e.comm, 0) >0 or e.sal > 1500;

--10.1981年1月1日到1981年12月31日的员工
select * from emp e where e.hiredate between '01-1月 -81' and '31-12月 -81';
select initcap('HELLO WORLD') from dual;

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值