数据库设计与管理实验告.docx
《数据库设计与管理实验告.docx》由会员分享,可在线阅读,更多相关《数据库设计与管理实验告.docx(17页珍藏版)》请在冰点文库上搜索。
数据库设计与管理实验告
数据库设计与管理实验告
学号:
110506224
姓名:
张留春
专业:
信息管理与信息系统
指导老师:
刘俊
实验1:
Oracle的安装及其管理工具的使用
实验2:
创建数据库和表
createtableEmployees
(EmployeeIDchar(6)notnull,
Namevarchar2(20)notnull,
Birthdaydatenotnull,
Sexnumber
(1)notnull,
Addressvarchar2(30)null,
Zipchar(6)null,
PhoneNumberchar(12)null
EmailAddressvarchar2(30)null,
DepartmentIDchar(3)notnull,
ConstraintEmployees_EmployeeID_fkPRIMARYKEY(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)
);
createtableSalary
(EmployeeIDchar(6)notnull,
InComeNumber(8,2)notnull,
OutComeNumber(8,2)notnull,
Constraint Salary_EmployeeID_pk PRIMARY KEY(EmployeeID)
);
AltertableEmployees
Add(constraintEmployees_DepartmentID_fkFOREIGNKEY(DepartmentID)referencesDepartments(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*
fromemployees;
2.selectaddress,phonenumber
fromemployees;
3.selectaddress,phonenumber
fromemployees
whereemployeeid='000001';
4.selectaddressas地址,phonenumberas电话
fromemployees
wheresex=0;
5.selectemployeeid,(income-outcome)as实际收入
fromsalary;
6.selectdepartmentid
fromemployees
wherenamelike'王%';
7.selectemployeeid
fromsalary
whereincomebetween2000and3000;
8.select*
fromemployees
wheredepartmentid=(selectdepartmentid
fromdepartments
wheredepartmentname='财务处');
9.selectname
fromemployees
wheredepartmentidin(selectdepartmentid
fromdepartments
wheredepartmentname='财务处')
andbirthday>all(selectbirthday
fromemployees
wheredepartmentidin(selectdepartmentid
fromdepartments
wheredepartmentname='研发部'));
10.selectname
fromemployees
whereemployeeidin(selectemployeeid
fromsalary
whereincome>all(selectincome
fromsalary
whereemployeeidin(selectemployeeid
fromdepartments
wheredepartmentname='财务处')));
11.selectemployees.*,salary.*
fromemployees,salary
whereemployees.employeeid=salary.employeeid;
12.selectname,income,outcome
fromemployees,salary,departments
whereemployees.employeeid=salary.employeeid
andemployees.departmentid=departments.departmentid
anddepartmentname='财务处'andincome>2000;
13.selectavg(income)as财务处平均工资
fromsalary
whereemployeeidin(selectemployeeid
fromemployees
wheredepartmentid=(selectdepartmentid
fromdepartments
wheredepartmentname='财务处'));
14.selectavg(income-outcome)as求财务部雇员的平均实际收入
fromsalary
whereemployeeidin(selectemployeeid
fromemployees
wheredepartmentid=(selectdepartmentid
fromdepartments
wheredepartmentname='财务处'));
15.selectcount(employeeid)
fromemployees
wheredepartmentid=(selectdepartmentid
fromdepartments
wheredepartmentname='财务处');
16.selectcount(employeeid)
fromemployees
groupbydepartmentid;
17.selectemployees.*,salary.*
fromemployees,salary
whereemployees.employeeid=salary.employeeid
orderbyincome;
实验5:
视图、存储过程
(1)根据表employees建Vemployees视图
createorreplaceviewVemployees
AS
select*
fromEmployees;
(2))根据表departments建Vdepartments视图
createorreplaceviewVdepartments
AS
select*
fromDepartments;
(3)根据表salary建Vsalary视图
createorreplaceviewVsalary
AS
select*
fromSalary;
(4)添加职员记录的存储过程
CREATEORREPLACEPROCEDUREEmployeeAdd
(employeeidinchar(6),nameinvarchar2(20),birthdayindate
sexinnumber,addressinvarchar2(30),zipinchar(6),phonenumberinchar(12),
emailaddressinvarchar2(30),departmentIDinchar(3))
AS
BEGIN
INSERTINTOEmployees
VALUES(employeeid,name,birthday,sex,address,
zip,phonenumber,emailaddress,departmentID)
ENDEmployeeAdd;
(5)修改职员记录的存储过程
CREATEORREPLACEPROCEDUREEmployeeUpdate
(empidinchar(6),employeeidinchar(6),nameinvarchar2(20),birthdayindate,
Sexinnumber,addressinvarchar2(30),zipinchar(6),phonenumberinchar(12),
Emailaddressinvarchar2(30),departmentIDinchar(3))
AS
BEGIN
UPDATEEmployees
SETEmploeeid=employeeid,
Name=name,
Birthday=birthday,
Sex=sex,
Address=address,
Zip=zip,
Phonenumber=phonenumber,
Emailaddress=emailaddress,
DepartmentID=departmentID
WhereEmployeeid=empid
ENDEmployeeUpdate;
(6)删除职员记录的存储过程
CREATEPROCEDUREEmpolyeeDelete
(employeeidinchar(6))
AS
BEGIN
DELETEfromEmployees
WhereEmployeeid=employeeid
ENDEmployeeDelete;