Oracle习题带答案

题目：已知公司的员工表EMP(EID, ENAME, BDATE, SEX, CITY)， 部门表DEPT（DID, DNAME, DCITY）， 工作表WORK（EID,DID,STARTDAT

题目：已知公司的员工表EMP(EID, ENAME, BDATE, SEX, CITY)，

部门表DEPT（DID, DNAME, DCITY），，

工作表WORK（EID,DID,STARTDATE,SALARY）。各个字段说明如下：

EID——员工编号，最多6个字符。例如A00001（主键）

ENAME——员工姓名，最多10个字符。例如SMITH

BDATE——出生日期，日期型

SEX——员工性别，单个字符。F或者M

CITY——员工居住的城市，最多20个字符。例如：上海

DID——部门编号，最多3个字符。例如 A01 （主键）

DNAME——部门名称，最多20个字符。例如：研发部门

DCITY——部门所在的城市，最多20个字符。例如：上海

STARTDATE——员工到部门上班的日期，日期型

SALARY——员工的工资。整型。

请使用Oracle的sql*plus 完成下列的操作

1、 创建表EMP，DEPT，WORK，并定义表的主键和外键。

2、 向每个表中插入适当的数据。例如：插入三条部门的数据，分别为每个部门插入两条员工数据

3、 查询“研发”部门的所有员工的基本信息

4、 查询拥有最多的员工的部门的基本信息（要求只取出一个部门的信息），如果有多个部门人数一样，那么取出部门编号最小的那个部门的基本信息。

5、 显示部门人数大于5的每个部门的编号，名称，人数

6、 显示部门人数大于5的每个部门的最高工资，最低工资

7、 列出员工编号以字母P至S开头的所有员工的基本信息

8、 删除年龄超过60岁的员工

9、 为工龄超过10年的职工增加10%的工资

答案如下：

create table emp1(
eid number(6),
ename varchar2(20),
bdate date,
sex char(2),
city varchar2(20)
);

create table dept1(
did number(6),
dname varchar2(20),
dcity varchar2(20)
);

create table work1(
eid number(6),
did number(6),
startdate date,
salary integer
);

1.
alter table emp1
add constraint emp1_eid_pk primary key(eid);

alter table dept1
add constraint dept1_did_pk primary key (did);

alter table work1
add constraint work1_eid_fk foreign key(eid) references emp1(eid);

alter table work1
add constraint work1_did_fk foreign key (did) references dept1(did);

2.
往emp1 表添加数据
insert into emp1 (eid,ename, bdate)
select empno, ename, hiredate
from emp;

update emp1
set (sex,city) =('男'，'北京'); ( 这种写法是错误的，只有利用子查询更新表，可以这样）

update emp1
set sex='男' , city='北京';

往dept1表添加数据

insert into dept1(did,dname)
select deptno,dname
from dept;

update dept1
set dcity='北京';

/把其中的一个部门改成开发，为了后面练习用
update dept1
set dname='developer'
where did=10;

往work1表添加数据

insert into work1 (eid,did,startdate,salary)
values(7369,10, '', 2500);

insert into work1 (eid,did,startdate,salary)
values(7499,10, '', 2501);

insert into work1 (eid,did,startdate,salary)
values(7521,10, '', 2500);

3.
select emp1.eid, emp1.ename,dept1.dname,work1.salary
from emp1,dept1,work1
where emp1.eid=work1.eid
and dept1.did=work1.did;

4.

select max(e.renshu), d.did
from (select did,count(eid) renshu
from work1
group by did)e,dept1 d
where d.did=e.did
group by d.did;

5.

select e.*, d.did, d.dname, d.dcity
from (select did,count(eid)
from work1
group by did
having count(eid)>5)e,dept1 d
where d.did=e.did;

6.

select max(salary), min(salary)
from work1
having count(eid)>2
group by did;

7.

select ename

from emp1

where ename like 'S%'

or ename like 'P%' ;

8.

delete from emp1

where months_between( sysdate, bdate)/12>60;

9.

update work1

set salary= salary*1.1

where months_between( sysdate ,startdate)/12>10;