oracle学生表中创建住址,Oracle 创建表 练习题

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;