数据库设计与管理实验告.doc

精品文档就在这里
-------------各类专业好文档，值得你下载，教育，管理，论文，制度，方案手册，应有尽有--------------
--------------------------------------------------------------------------------------------------------------------------------------------

数据库设计与管理实验告

学号：110506224

姓名：张留春

专业：信息管理与信息系统

指导老师：刘俊

实验1：Oracle的安装及其管理工具 的使用

实验2：创建数据库和表

create table Employees
( EmployeeID char(6) not null ,
Name varchar2(20) not null,
Birthday date not null,
Sex number(1) not null,
Address varchar2(30) null,
Zip char(6) null,
PhoneNumber char(12) null
EmailAddress varchar2(30) null,
DepartmentID char(3) not null,
Constraint Employees_EmployeeID_fk PRIMARY KEY(EmployeeID)
);

create table Departments
(     DepartmentID char(3) not null,
DepartmentName varchar2(20) not null,
Note varchar2(20) null,
Constraint Departments_DepartmentID_pk PRIMARY KEY(DepartmentID)
);

create table Salary
( EmployeeID char(6) not null,
InCome Number(8,2) not null,
OutCome Number(8,2) not null,
Constraint Salary_EmployeeID _pk PRIMARY KEY(EmployeeID)
);

Alter table Employees
Add(constraint Employees_DepartmentID_fk FOREIGN KEY(DepartmentID) references Departments(DepartmentID));

实验3

（1）Employees表

（2）Department表

（3）Salary表

(4)删除表Employees的第2、8行和Salary的第2、11行。注意进行删除操作时，作为两表主键的EmployeeID的值，以保持数据完整性。

(5) 删除表Departments的第2行，同时也要删除表Employees的第2行。

(6)将表Employees中编号为020018的记录的部门号改为4。

实验4：SQL编程
1. select *
from employees;

2.select address,phonenumber
from employees；

3.select address ,phonenumber
from employees
where employeeid='000001';

4.select address as 地址,phonenumber as 电话
from employees
where sex=0;

5.select employeeid,(income-outcome) as 实际收入
from salary;

6.select departmentid
from employees
where name like '王%';

7.select employeeid
from salary
where income between 2000 and 3000;

8.select *
from employees
where departmentid=(select departmentid
from departments
where departmentname='财务处');

9.select name
from employees
where departmentid in (select departmentid
from departments
where departmentname='财务处')
and birthday >all(select birthday
from employees
where departmentid in (select departmentid
from departments
where departmentname='研发部'));

10.select name
from employees
where employeeid in (select employeeid
from salary
where income>all(select income
from salary
where employeeid in(select employeeid
from departments
where departmentname='财务处')));

11.select employees.*,salary.*
from employees,salary
where employees.employeeid=salary.employeeid;

12.select name,income,outcome
from employees ,salary,departments
where employees.employeeid=salary.employeeid
and employees.departmentid=departments.departmentid
and departmentname='财务处' and income>2000;

13.select avg(income) as 财务处平均工资
from salary
where employeeid in (select employeeid
from employees
where departmentid=(select departmentid
from departments
where departmentname='财务处'));

14.select avg(income-outcome) as 求财务部雇员的平均实际收入
from salary
where employeeid in (select employeeid
from employees
where departmentid=(select departmentid
from departments
where departmentname='财务处'));

15.select count(employeeid)
from employees
where departmentid=(select departmentid
from departments
where departmentname='财务处');

16.select count(employeeid)
from employees
group by departmentid;

17.select employees.*,salary.*
from employees,salary
where employees.employeeid=salary.employeeid
order by income;

实验5：视图、存储过程
（1）根据表employees建Vemployees视图
create or replace view Vemployees
AS
select *
from Employees;
(2) )根据表departments建Vdepartments视图
create or replace view Vdepartments
AS
select *
from Departments;
(3)根据表salary建Vsalary视图
create or replace view Vsalary
AS
select *
from Salary;

（4）添加职员记录的存储过程
CREATE OR REPLACE PROCEDURE EmployeeAdd
( employeeid in char(6),name in varchar2（20），birthday in date
sex in number,address in varchar2(30),zip in char(6),phonenumber in char(12),
emailaddress in varchar2（30），departmentID in char(3))
AS
BEGIN
INSERT INTO Employees
VALUES(employeeid,name,birthday,sex,address,
zip,phonenumber,emailaddress,departmentID)
END EmployeeAdd;
(5) 修改职员记录的存储过程
CREATE OR REPLACE PROCEDURE EmployeeUpdate
( empid in char(6),employeeid in char(6),name in varchar2（20），birthday in date,
Sex in number,address in varchar2(30),zip in char(6),phonenumber in char(12),
Emailaddress in varchar2(30),departmentID in char(3))
AS
BEGIN
UPDATE Employees
SET Emploeeid=employeeid,
Name=name,
Birthday=birthday,
Sex=sex,
Address=address,
Zip=zip,
Phonenumber=phonenumber,
Emailaddress=emailaddress,
DepartmentID=departmentID
Where Employeeid=empid
END EmployeeUpdate;
（6）删除职员记录的存储过程
CREATE PROCEDURE EmpolyeeDelete
( employeeid in char（6）)
AS
BEGIN
DELETE from Employees
Where Employeeid=employeeid
END EmployeeDelete;

---------------------------------------------------------精品 文档---------------------------------------------------------------------