SQL 经典练习.docx

-- Create table 学生信息create table HAND_STUDENT( STUDENT_NO VARCHAR2(10) not null, STUDENT_NAME VARCHAR2(20), STUDENT_AGE NUMBER(2), STUDENT_GENDER VARCHAR2(5));-- Add comments to the table comment on table HAND_STUDENT is 学生信息表;-- Add comments to the columns comment on column HAND_STUDENT.STUDENT_NO is 学号;comment on column HAND_STUDENT.STUDENT_NAME is 姓名;comment on column HAND_STUDENT.STUDENT_AGE is 年龄;comment on column HAND_STUDENT.STUDENT_GENDER is 性别;-- Create/Recreate primary, unique and foreign key constraints alter table HAND_STUDENT add primary key (STUDENT_NO);-- Create table 教师信息表create table HAND_TEACHER( TEACHER_NO VARCHAR2(10) not null, TEACHER_NAME VARCHAR2(20), MANAGER_NO VARCHAR2(10));-- Add comments to the table comment on table HAND_TEACHER is 教师信息表;-- Add comments to the columns comment on column HAND_TEACHER.TEACHER_NO is 教师编号;comment on column HAND_TEACHER.TEACHER_NAME is 教师名称;comment on column HAND_TEACHER.MANAGER_NO is 上级编号;-- Create/Recreate primary, unique and foreign key constraints alter table HAND_TEACHER add primary key (TEACHER_NO);-- Create table 课程信息表create table HAND_COURSE( COURSE_NO VARCHAR2(10) not null, COURSE_NAME VARCHAR2(20), TEACHER_NO VARCHAR2(20) not null);-- Add comments to the table comment on table HAND_COURSE is 课程信息表;-- Add comments to the columns comment on column HAND_COURSE.COURSE_NO is 课程号;comment on column HAND_COURSE.COURSE_NAME is 课程名称;comment on column HAND_COURSE.TEACHER_NO is 教师编号;-- Create/Recreate primary, unique and foreign key constraints alter table HAND_COURSE add constraint PK_COURSE primary key (COURSE_NO, TEACHER_NO);-- Create table 成绩信息表create table HAND_STUDENT_CORE( STUDENT_NO VARCHAR2(10) not null, COURSE_NO VARCHAR2(10) not null, CORE NUMBER(4,2));-- Add comments to the table comment on table HAND_STUDENT_CORE is 学生成绩表;-- Add comments to the columns comment on column HAND_STUDENT_CORE.STUDENT_NO is 学号;comment on column HAND_STUDENT_CORE.COURSE_NO is 课程号;comment on column HAN