二、使用system/manager 用户/密码登陆,并完成以下操作
1.创建一个用户teacher ,密码是math ,默认表空间users ,临时表空间temp 无配额,帐户不锁定
CREAT USER teacher IDENTIFIED BY math
DEFAULT TABLESPACE user
TEMPORARY TABLESPACE temp
ACCOUNT UNLOCK
2.创建一个口令有效期为7天,锁定前允许的最大失败登陆次数为3 的概要文件MYPROFILE ,并分配给用户teacher CREAT PROFILE MYPROFILE LIMIT
PASSWORD-LIFE-TIME 7
FAILED-LOGIN-ATTEMPTS 3;
ALTER USER teacher PROFILE lock-account;
3.创建一个角色,具有连接到数据库以及创建表的权限,并把这个角色赋给用户teacher
CREAT ROLE X
GRANT CONNECT,CREAT TABLE TO X;
GRANT X to teacher;
4.以用户teacher 登陆,并建立下面Courses 和Students 两个表,并插入相应的数据
Courses 表,要求其中CourseCode 为主键,CourseName 非空
Students 表,要求StudentCode 为主键,CourseCode 为外键,score 在0-100之间
CREAT TABLE Courses
(CourserCode number(4) primary key
courseName varchar2(20) not null
Description varchar2(20));
Insert into Courses(coursecode,coursename,descpiption)values(x001,’数据库原理’,’计算机必修’);
Insert into Courses(coursecode,coursename,descpiption)values(x002,’计算机网络原理’,’计算机选修’);
CREAT TABLE Sutudents
(studentcode number(4)primary key,
Name varchar2(20),
Birthdate ,
Coursecode number(4) references courses(coursecode0,
Score number(2) check(score between 0 and 100));
Insert into students(studentcode,name,birthdate,coursecode,score)values(t08001,’赵磊’,1989-5-30,x001,89);
Insert into students(studentcode,name,birthdate,coursecode,score)values(t08002,’吴吉’,1987-12-3,x002,95);
(本题28分,每空2分)
1.创建一个以部门号为参数,返回该部门最高工资的函数。 CREATE OR REPLACE FUNCTION return_maxsal(p_deptno emp.deptno%TYPE) RETURN____emp.deptno%type __________
AS
v_maxsal emp.sal%TYPE;
BEGIN