a) 建立下列教学管理用的数据表。注意,表名和字段名都是英文。
学生表(student)
字段 | 名称 | 数据类型 | 约束 |
学号 | S_NO | CHAR(6) | 主键 |
姓名 | S_NAME | CHAR(10) | 非空 |
性别 | S_SEX | CHAR(2) | 只取男、女 |
出生日期 | S_BIRTHDAY | DATE |
|
入学成绩 | S_SCORE | NUMBER(5,1) |
|
附加分 | S_ADDF | NUMBER(5,1) |
|
班级编码 | CLASS_NO | CHAR(5) | 外键,关联班级表CLASS_NO |
班级表(class)
字段 | 名称 | 数据类型 | 约束 |
班级编码 | CLASS_NO | CHAR(5) | 主键 |
班级名 | CLASS_NAME | CHAR(10) | 非空 |
所属系部 | DEPT_NO | CHAR(2) | 外键,关联系部表DEPT_NO |
系部表(department)
字段 | 名称 | 数据类型 | 约束 |
系部编码 | DEPT_NO | CHAR(2) | 主键 |
系部名 | DEPT_NAME | CHAR(10) | 非空 |
课程表(course)
字段 | 名称 | 数据类型 | 约束 |
课程编码 | COURSE_NO | CHAR(5) | 主键 |
课程名 | COURSE_NAME | CHAR(20) | 非空 |
教师表(teacher)
字段 | 名称 | 数据类型 | 约束 |
教师编码 | T_NO | CHAR(6) | 主键 |
教师姓名 | T_NAME | CHAR(8) | 非空 |
性别 | T_SEX | CHAR(2) | 只取男、女 |
出生日期 | T_BIRTHDAY | DATE |
|
职称 | TECH_TITLE | CHAR(10) |
|
选修表(choice)
字段 | 名称 | 数据类型 | 约束 |
学生编码 | S_NO | CHAR(6) | 主键,与学生表中S_NO外键关联,级联删除 |
课程编码 | COURSE_NO | CHAR(5) | 主键,与课程表中COURSE_NO外键关联 |
成绩 | SCORE | NUMBER(5,1) |
|
授课表(teaching)
字段 | 名称 | 数据类型 | 约束 |
教师编码 | T_NO | CHAR(6) | 主键,与教师表中T_NO外键关联,级联删除 |
课程编码 | COURSE_NO | CHAR(5) | 主键,与课程表中COURSE_NO外键关联 |
b) 为每个表添加1至2条测试数据。
c) 使用字典表user_constraints和user_cons_columns 查询学生表上有哪些约束?
d) 通过复制学生表和班级表创建 copy_student 、copy_class,并为复制的表添加和学生表和班级表相同的约束。班级表DEPT_NO 的外键不需要添加。
e) 使用字典表user_constraints和user_cons_columns 查询确认约束添加成功。
f) 删除copy_student、copy_class上的约束,使用字典表user_constraints和 user_cons_columns查询确认约束删除成功。
g) 查询编号为c002班级中所有女同学;
h) 查询所有学生出生日期,要求输出格式为'yyyy-mm-dd';
i) 查询总分(入学成绩+附加分)在550和600之间的所有学生;
j) 查询名字中带“小”字的所有学生;
k) 查询所有学生,按总分(入学成绩+附加分)降序排列;
l) 查询入学成绩大于平均入学成绩的所有学生;
--删除表
DROP TABLE department CASCADE CONSTRAINT;
DROP TABLE course CASCADE CONSTRAINT;
DROP TABLE teacher CASCADE CONSTRAINT;
DROP TABLE class CASCADE CONSTRAINT;
DROP TABLE student CASCADE CONSTRAINT;
DROP TABLE choice CASCADE CONSTRAINT;
DROP TABLE teaching CASCADE CONSTRAINT;
--创建表
CREATE TABLE department(
dept_no char(2) CONSTRAINT pk_department_dept_no PRIMARY KEY,
dept_name char(10) CONSTRAINT nn_department_dept_name NOT NULL
);
CREATE TABLE course(
course_no char(5) CONSTRAINT pk_course_course_no PRIMARY KEY,
course_name char(20) CONSTRAINT nn_course_course_name NOT NULL
);
CREATE TABLE teacher(
t_no CHAR(6) CONSTRAINT pk_teacher_t_no PRIMARY KEY,
t_name CHAR(8) CONSTRAINT nn_teacher_t_name NOT NULL,
t_sex NCHAR(1) CONSTRAINT ck_teacher_t_sex CHECK(t_sex IN('男','女')),
t_birthday DATE,
tech_title CHAR(10)
);
CREATE TABLE class(
class_no CHAR(5) CONSTRAINT pk_class_class_no PRIMARY KEY,
class_name CHAR(10) CONSTRAINT nn_class_class_name NOT NULL,
dept_no CHAR(2)
);
CREATE TABLE student(
s_no CHAR(6) CONSTRAINT pk_student_s_no PRIMARY KEY,
s_name CHAR(10) CONSTRAINT nn_student_s_name NOT NULL,
s_sex NCHAR(2) CONSTRAINT ck_student_s_sex CHECK(s_sex IN('男','女')),
s_birthday DATE,
s_score NUMBER(5,1),
s_addf NUMBER(5,1),
class_no CHAR(5)
);
CREATE TABLE choice(
s_no CHAR(6),
course_no CHAR(5),
score NUMBER(5,1),
CONSTRAINT pk_choice_s_no_course_no PRIMARY KEY(s_no,course_no)
);
CREATE TABLE teaching(
t_no CHAR(6),
course_no CHAR(5),
CONSTRAINT pk_teaching_t_no_course_no PRIMARY KEY(t_no,course_no)
);
--向表中插入记录
INSERT INTO department VALUES('01','工商');
INSERT INTO department VALUES('02','旅游');
INSERT INTO department VALUES('03','信息');
INSERT INTO department VALUES('04','机电');
INSERT INTO department VALUES('05','汽车');
INSERT INTO course VALUES('01','C语言');
INSERT INTO course VALUES('02','营养与健康');
INSERT INTO course VALUES('03','大学英语');
INSERT INTO course VALUES('04','线性代数');
INSERT INTO course VALUES('05','计算机网络');
INSERT INTO teacher VALUES('0101','司马','男',TO_DATE('13-07-1987','dd-mm-yyyy'),'甲');
INSERT INTO teacher VALUES('0201','诸葛','男',TO_DATE('21-03-1976','dd-mm-yyyy'),'乙');
INSERT INTO teacher VALUES('0302','慕容','女',TO_DATE('7-05-1985','dd-mm-yyyy'),'丙');
INSERT INTO teacher VALUES('0403','夏侯','男',TO_DATE('18-08-1982','dd-mm-yyyy'),'甲');
INSERT INTO teacher VALUES('0502','欧阳','女',TO_DATE('25-02-1989','dd-mm-yyyy'),'丙');
INSERT INTO class VALUES('1101','工商','01');
INSERT INTO class VALUES('1201','旅游','02');
INSERT INTO class VALUES('1102','信息','03');
INSERT INTO class VALUES('1202','机电','04');
INSERT INTO class VALUES('1103','汽车','05');
INSERT INTO student VALUES('0001','小明','男',TO_DATE('13-07-1990','dd-mm-yyyy'),89,12,'1101');
INSERT INTO student VALUES('0002','小红','女',TO_DATE('13-07-1991','dd-mm-yyyy'),83,17,'1201');
INSERT INTO student VALUES('0003','小强','男',TO_DATE('13-07-1990','dd-mm-yyyy'),80,11,'1102');
INSERT INTO student VALUES('0004','小刚','男',TO_DATE('13-07-1991','dd-mm-yyyy'),75,19,'1202');
INSERT INTO student VALUES('0005','小花','女',TO_DATE('13-07-1991','dd-mm-yyyy'),90,15,'1103');
INSERT INTO choice VALUES('0001','01',98);
INSERT INTO choice VALUES('0002','02',87);
INSERT INTO choice VALUES('0003','03',91);
INSERT INTO choice VALUES('0004','04',94);
INSERT INTO choice VALUES('0005','05',83);
INSERT INTO teaching VALUES('0101','01');
INSERT INTO teaching VALUES('0201','02');
INSERT INTO teaching VALUES('0302','03');
INSERT INTO teaching VALUES('0403','04');
INSERT INTO teaching VALUES('0502','05');
--添加外键约束
ALTER TABLE student ADD CONSTRAINT fk_student_class_no FOREIGN KEY(class_no) REFERENCES class(class_no);
ALTER TABLE class ADD CONSTRAINT fk_class_dept_no FOREIGN KEY(dept_no) REFERENCES department(dept_no);
ALTER TABLE choice ADD CONSTRAINT fk_choice_s_no FOREIGN KEY(s_no) REFERENCES student(s_no) ON DELETE CASCADE;
ALTER TABLE choice ADD CONSTRAINT fk_choice_course_no FOREIGN KEY(course_no) REFERENCES course(course_no) ON DELETE CASCADE;
ALTER TABLE teaching ADD CONSTRAINT fk_t_no FOREIGN KEY(t_no) REFERENCES teacher(t_no) ON DELETE CASCADE;
ALTER TABLE teaching ADD CONSTRAINT fk_course_no FOREIGN KEY(course_no) REFERENCES course(course_no) ON DELETE CASCADE;
SELECT TABLE_NAME FROM USER_TABLES;
SELECT * FROM department;
SELECT * FROM course;
SELECT * FROM teacher;
SELECT * FROM class;
SELECT * FROM student;
SELECT * FROM choice;
SELECT * FROM teaching;