数据库设计与管理实验告.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) nullEmailAddress varchar2(30) null,DepartmentID char(3) not null,Constraint Employees_EmployeeID_fk PRIMARY KEY(EmployeeID);createtableDepartments(DepartmentIDchar(3)notnull, DepartmentNamevarchar2(20)notnull, Notevarchar2(20) null, ConstraintDepartments_DepartmentID_pkPRIMARYKEY(DepartmentID);create table Salary( EmployeeID char(6) not null,InCome Number(8,2) not null,OutCome Number(8,2) not null,ConstraintSalary_EmployeeID _pkPRIMARYKEY(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 employeeswhere 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 namefrom employeeswhere departmentid in (select departmentid from departments where departmentname=财务处) and birthday all(select birthdayfrom employees where departmentid in (select departmentidfrom departments where departmentname=研发部);10.select namefrom employeeswhere employeeid in (select employeeid from salarywhere incomeall(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.employeeidand employees.departmentid=departments.departmentid and departmentname=财务处 and income2000;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 VemployeesASselect *from Employees;(2) )根据表departments建Vdepartments视图create or replace view VdepartmentsASselect *from Departments;(3)根据表salary建Vsalary视图create or replace view VsalaryASselect *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)ASBEGININSERT INTO EmployeesVALUES(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)ASBEGINUPDATE Employees SET Emploeeid=employeeid, Name=name, Birthday=birthday, Sex=sex,Address=address,Zip=zip,Phonenumber=phonenumber,Emailaddress=emailaddress,DepartmentID=departmentIDWhere Employeeid=empidEND EmployeeUpdate; （6）删除职员记录的存储过程CREATE PROCEDURE EmpolyeeDelete( employeeid in char（6）)ASBEGINDELETE from Employees Where Employeeid=employeeidEND EmployeeDelete;-精品 文档-