数据库设计与管理实验告

精品文档就在这里-------------各类专业好文档，值得你下载，教育，管理，论文，制度，方案手册，应有尽有-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------精品文档---------------------------------------------------------------------数据库设计与管理实验告学号：110506224姓名：张留春专业：信息管理与信息系统指导老师：刘俊实验1：Oracle的安装及其管理工具的使用实验2：创建数据库和表createtableEmployees(EmployeeIDchar(6)notnull,Namevarchar2(20)notnull,Birthdaydatenotnull,Sexnumber(1)notnull,Addressvarchar2(30)null,Zipchar(6)null,PhoneNumberchar(12)nullEmailAddressvarchar2(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));AltertableEmployeesAdd(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编程select*fromemployees;2.selectaddress,phonenumberfromemployees；3.selectaddress,phonenumberfromemployeeswhereemployeeid='000001';4.selectaddressas地址,phonenumberas电话fromemployeeswheresex=0;5.selectemployeeid,(income-outcome)as实际收入fromsalary;6.selectdepartmentidfromemployeeswherenamelike'王%';7.selectemployeeidfromsalarywhereincomebetween2000and3000;8.select*fromemployeeswheredepartmentid=(selectdepartmentidfromdepartmentswheredepartmentname='财务处');9.selectnamefromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='财务处')andbirthday>all(selectbirthdayfromemployeeswheredepartmentidin(selectdepartmentidfromdepartmentswheredepartmentname='研发部'));10.selectnamefromemployeeswhereemployeeidin(selectemployeeidfromsalarywhereincome>all(selectincomefromsalarywhereemployeeidin(selectemployeeidfromdepartmentswheredepartmentname='财务处')));11.selectemployees.*,salary.*fromemployees,salarywhereemployees.employeeid=salary.employeeid;12.selectname,income,outcomefromemployees,salary,departmentswhereemployees.employeeid=salary.employeeidandemployees.departmentid=departments.departmentidanddepartmentname='财务处'andincome>2000;13.selectavg(income)as财务处平均工资fromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentid=(selectdepartmentidfromdepartmentswheredepartmentname='财务处'));14.selectavg(income-outcome)as求财务部雇员的平均实际收入fromsalarywhereemployeeidin(selectemployeeidfromemployeeswheredepartmentid=(selectdepartmentidfromdepartmentswheredepartmentname='财务处'));15.selectcount(employeeid)fromemployeeswheredepartmentid=(selectdepartmentidfromdepartmentswheredepartmentname='财务处');16.selectcount(employeeid)fromemployeesgroupbydepartmentid;17.selectemployees.*,salary.*fromemployees,salarywhereemployees.employeeid=salary.employeeidorderbyincome;实验5：视图、存储过程（1）根据表employees建Vemployees视图createorreplaceviewVemployeesASselect*fromEmployees;(2))根据表departments建Vdepartments视图createorreplaceviewVdepartmentsASselect*fromDepartments;(3)根据表salary建Vsalary视图createorreplaceviewVsalaryASselect*fromSalary;（4）添加职员记录的存储过程CREATEORREPLACEPROCEDUREEmployeeAdd(employeeidinchar(6),nameinvarchar2（20），birthdayindatesexinnumber,addressinvarchar2(30),zipinchar(6),phonenumberinchar(12),emailaddressinvarchar2（30），departmentIDinchar(3))ASBEGININSERTINTOEmployeesVALUES(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))ASBEGINUPDATEEmployeesSETEmploeeid=employeeid,Name=name,Birthday=birthday,Sex=sex,Address=address,Zip=zip,Phonenumber=phonenumber,Email