课程实验详细答案.doc

第一章 Oracle 9i 系统入门实验实例代码1创建用户 (1)以Oracle 系统用户SYSTEM登录(2)创建用户student SQLcreate user studentidentified by student123default tablespace userstemporary tablespace temp;(3)给用户student授权SQLgrant connect,resource to student;2. 更改用户密码(1)以Oracle 系统用户SYSTEM登录(2)更改用户student 密码为stu123456SQLalter user student identified by stu123456;授权：给用户student授予SCOTT用户emp表的查看更新权限。回收权限：回收用户student对SCOTT用户emp表的查看更新权限。删除用户student (1)以Oracle 系统用户SYSTEM登录(2)删除用户studentSQLdrop user student cascade;第二章 SQL基础实验实例代码实验1 创建表1. 使用SQL语句分别创建表Employees2 create table Employees2(EmployeeID char(6) constraint Em_pk primary key,Name char(10) NOT NULL,BirthDate Date NOT NULL,Sex number(1) NOT NULL,Address char(20) NOT NULL,Zip char(6) NULL,PhoneNumberchar(12) NULL,EmailAddress char(20) NULL, DepartmentID char(3) NOT NULL);2. 使用SQL语句分别创建表Departmentscreate table Departments(DepartmentID char(3) constraint D_pk primary key,DepartmentName char(20) NOT NULL,Note char(16) NULL );3.使用SQL语句分别创建表Salarycreate table Salary(EmployeeID char(6) constraint S_pk primary key,InCome number(8,2) NOT NULL, OutCome number(8,2) NOT NULL );实验2 向表中进行数据插入、修改和删除操作1、向创建的三个表，即Employees表、Departments表和Salary表中插入数据。 (1)向Employees2表中插入三十条记录。启动SQL * Plus界面，输入以下SQL语句，按回车键执行命令：INSERT INTO Employees2 VALUES(001101,王林,TO_DATE(1988-06-18,YYYY-MM-DD),1,珠海,660104NULL,005); ( 依次输入三十条记录。)(2)向Departments表中插入十条记录。INSERT INTO Departments VALUES(001,销售部, NULL);(依次输入十条记录)(3)向Salary表中插入三十条记录。INSERT INTO Salary VALUES(001101,3500.00, 809.00); ( 依次输入三十条记录。)2、使用SQL命令修改表Salary中的数据。启动SQL * Plus界面，输入以下SQL语句，按回车键执行命令： UPDATE Salary SET InCome=3800 WHERE EmployeeID=001101;上述命令执行后，编号为001101的职工的收将改为3800。打开表，观察数据的变化。然后仿照上例，做二至三个练习。给所有收入在1000元以下的员工的工资增加15%。 UPDATE Salary SET InCome=InCome+InCome*0.15 WHERE InComeselect * from departments; 2)将该SQL命令存入DEPARTMENTS.SQL文件;SQL SAVE departments.sql 3)清空缓冲区;SQLDEL 4)调入DEPARTMENTS.SQL文件到内存;SQLGET departments.sql 5)再次执行同样的SQL命令。SQLdepartments.sql 或 SQLSTART departments.sql 实例练习2：分别在SQL*Plus和SQL*Plus Worksheet环境下执行下列操作，观察和分析结果。 创建一个替换变量Cont_name，在查询中用&符号说明。SELECT Name,Address, PhoneNumber FROM EmployeesWHERE name=&Cont_name; 接受用户的日期的SQL语句。SELECT EmployeeID,Name,sex,phonenumber ,Birthday FROM EmployeesWHERE Birthday =&birth; 替换变量用于列名。SELECT &Col_Name FROM departments; 替换变量用于表达式。SELECT Name FROM Employees WHERE &var; 显示“广州”的雇员的姓名、性别、电话号码以及珠海籍员工的工资。DEFINE City=广州;SELECT Name,sex,phonenumber FROM Employees WHERE Address=&City;SELECT Name, EmployeeID,Income FROM Employees e,Salary sWHERE e.EmployeeID =s.EmployeeID AND e. Address =&City;DEFINE City;DEFINE;UNDEFINE City; 定义数值变量，在SQL语句里使用变量。使用完后将变量删除。DEFINE Salary=1800;SELECT EmployeeID,IncomeFROM salary WHERE Income &Salary;UNDEFINE Salary; 分析以下代码。SELECT EmployeeID,Income, &ColumnFROM salary ORDER BY &Column DESC;实验2 格式化查询结果1使用格式化命令 为列departmentID设置标题为部门代码。 COLUMN departmentID HEADING 部门代码 为Income列定制格式。要求在每个值前加￥符号作为前缀，并保留一个小数位。 COLUMN Income JUSTIFY RIGHT FORMAT $99,999.0 用- - 替换所有空值。 COLUMN departmentID FORMAT 999 NULL - - - 显示departmentID的当前设置。 COLUMN departmentID 清除departmentID列的设置。 COLUMN departmentID CLEAR 清除重复值。SELECT DepartmentID,EmployeeID,Name,phonenumber FROM Employees; 可能有许多雇员属于同一个部门，那么在DepartmentID列将有重复值出现。BREAK ON DepartmentID;SELECT DepartmentID,EmployeeID,Name,phonenumberFROM Employees ORDER BY DepartmentID; CLEAR BREAK实验3 假脱机输出将销售表(XSB)和学生成绩表(SCJ)的查询结果输出到文件d:spool_score.prn中。 Spool d:spool_ score.prn SELECT CPBH,CPMC,SCS,JG FROM XSB; SELECT XH,XM,XB,YW,SX,YY,YW+SX+YY AS 总分 FROM SCJ; Spool off第四章 Oracle数据库体系结构实验实例代码实验1 创建控制文件副本1)首先启动SQL*Plus，以SYSDBA身份连接到数据库SQL conn sys/oracle as sysdba利用数据字典v$controlfile查询实例当前使用的控制文件。SQL Select name from v$controlfile;2)修改SPFILE,使用alter system set control_files命令来改变控制文件的位置。alter system set control_files = C:ORACLEORADATAORA9CONTROL01.CTL, C:ORACLEORADATAORA9CONTROL02.CTL, C:ORACLEORADATAORA9CONTROL03.CTL, e:ora_bakora9CONTROL01.CTL scope=spfile;3)使用SHUTDOWN命令关闭数据库。SQL SHUTDOWN IMMEDIATE 4)使用操作系统命令将现有控制文件复制到指定位置将C:ORACLEORADATAORA9CONTROL03.CTL文件复制到e:ora_bakora9CONTROL01.CTL5)重新启动Oracle数据库。如：startup6)使用Select name from v$controlfile语句查询实例当前使用的控制文件。SQL Select name from v$controlfile;实验2 创建联机重做日志组和联机重做日志文件1. 创建一个新的联机重做日志组，此组包含一个大小为1024KB 的重做日志文件C:ORACLEORADATAEXAMDBRED07_01.LOG。SQL ALTER DATABASE ADD LOGFILE GROUP 4 (C:ORACLEORADATAORA9RED07_01.LOG) SIZE 1024K;SQL SELECT MEMBER FROM V$LOGFILE;SQLALTER DATABASE ADD LOGFILE GROUP 5 (C:ORACLEORADATAORA9RED07_02.LOG.) SIZE 1024K;实验3 管理表空间1. 创建本地管理表空间APP,增加一个20M的数据文件APP1SQLcreate tablespace app datafile c:oracleoradataapp.dbf size 50M extent management local autoallocate;SQLalter TABLESPACE app add DATAFILE c:oracleoradataapp1.dbf SIZE 20M;2. 更改表空间状态。1)使表空间脱机SQLalter tablespace app offline;2)使表空间联机SQLalter tablespace app online;3)更改表空间为只读SQLalter tablespace app read only;更改表空间为可读写SQLalter tablespace app read write;更改数据文件状态1)使数据文件脱机SQLALTER DATABASE DATAFILE C:ORACLEORADATAAPP1.DBF OFFLINE;2)使数据文件联机SQLALTER DATABASE DATAFILE C:ORACLEORADATAAPP1.DBF ONLINE;3. 更改数据文件存储参数，数据文件APP1的大小为40MB，并更改自动增量为1024K，最大大小为100MB.SQLALTER DATABASE DATAFILE C:ORACLEORADATA APP1.DBF RESIZE 100M;SQLALTER DATABASE DATAFILE C:ORACLEORADATA APP1.DBF AUTOEXTEND ON NEXT 1024K MAXSIZE 100M;4. 查看表空间APP的信息SQLselect tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files where tablespace_name=APP;实验4 查看视图查看USER视图SQLselect * from USER_TABLES;查看ALL视图SQLselect * from ALL_TABLES;查看DBA视图SQLselect * from DBA_TABLES;第五章 数据库对象实验实例代码实验1 创建表和维护表1、创建招生院校信息表“college”SQLCREATE TABLE COLLEGE(YXBH number(4) PRIMARY KEY,YXMC char(20) NOT NULL,LQFSX number(3) CHECK(LQFSX BETWEEN 300 AND 700),ZSRS number(3) CHECK(ZSRS=20),LQRS number(3) DEFAULT 0);2、创建考生志愿信息表“student1”CREATE TABLE STUDENT1(XSBH number(5) PRIMARY KEY,XSXM char(8) NOT NULL,XB Char(1) CHECK(XB IN(1,2),ZF number(3) CHECK(ZFdeclarebegindelete from dept where deptno=&deptno;dbms_output.put_line(该部门被删除);exception when no_data_found then dbms_output.put_line(该部门不存在); when others then dbms_output.put_line(该部门有员工不能删除); end;修改为以下代码：SQL declare no_dept exception; begin delete from dept where deptno=&deptno;if sql%notfound=true then raise no_dept;else dbms_output.put_line(该部门被删除);end if; exception when no_dept then dbms_output.put_line(该部门不存在); when others then dbms_output.put_line(该部门有员工不能删除或输入部门号错误); end;实验2 条件分支控制结构1使用条件分支语句，为工资小于2000元的员工增加工资200元。SQL Declare v_sal number(6,2); v_empno number; Begin v_empno:= &empno; Select sal into v_sal from emp where empno= v_empno; If v_sal 2000 then Update emp set sal=v_sal+200 where empno= v_empno; End if; End;2使用CASE 语句，更新相应部门的员工补贴。部门10补贴100，部门20补贴50，部门30补贴80，40补贴60。(使用SCOTT用户的emp表)begin case &depno when 10 then update emp set sal=sal+100 where deptno=10; when 20 then update emp set sal=sal+50 where deptno=20; when 30 then update emp set sal=sal+80 where deptno=30; when 40 then update emp set sal=sal+60 where deptno=40;end case;end;实验3 循环控制结构分别使用三种循环方式，计算10的阶乘。Loop方法:declare total number:=1; num number:=10;begin loop if num0 loop total:=total*num; num:=num-1; END loop; dbms_output.put_line(total);end;/For方法:declare total number:=1;begin for num in 1.10 loop total:=total*num; END loop; dbms_output.put_line(total);end;/使用FOR 循环语句，输出一个实心三角形。begin for i in 1.6 loop for j in 1.i loop dbms_output.put(*); end loop; dbms_output.put_line(); end loop;end;实验4 游标1. 利用游标变量和记录的方法，把雇员表EMP的记录提取出来，并逐行显示。(使用SCOTT用户的EMP表和DEPT表)begin dbms_output.put_line(雇员表EMP的记录);dbms_output.put_line(-); for emp in (select * from emp) loop dbms_output.put_line(EMPNO:|emp.empno); dbms_output.put_line(ENAME: | emp.ename); dbms_output.put_line(JOB: | emp.job); dbms_output.put_line(MGR: | emp.mgr); dbms_output.put_line(HIREDATE: | emp.hiredate); dbms_output.put_line(SAL: | emp.sal); dbms_output.put_line(COMM: | m); dbms_output.put_line(DEPTNO: | emp.deptno); dbms_output.put_line(-); end loop;end;2. 使用游标，从Employees表中选择姓、名、薪水和雇佣日期。从游标中检索每一行，如果雇员的薪水大于30000美元，则显示雇员信息。(使用hr用户的Employees表)begin dbms_output.put_line(雇员信息);dbms_output.put_line(-); for employees in (select * from hr.employees where salary30000) loop dbms_output.put_line(姓:| employees.first_name); dbms_output.put_line(名: | employees.last_name); dbms_output.put_line(薪水: | employees.salary); dbms_output.put_line(雇佣日期: | employees.hire_date); dbms_output.put_line(-); end loop;end;改为：使用游标，从Employees表中选