sql.sql

来自「近几年来」· SQL 代码 · 共 75 行

drop table lxf_poll_line;
drop table lxf_poll;
drop table lxf_person;
drop table lxf_course;
drop table lxf_classes;

create table lxf_person(
	id number(4),
	name varchar2(20) unique,
	password varchar2(20),
	flag varchar2(1),
	primary key(id)
);

create table lxf_course(
	id number(2),
	name varchar2(20) unique,
	description varchar2(300),
	primary key(id)
);

create table lxf_classes(
	id number(3),
	name varchar2(20) unique,
	types varchar2(40),
	primary key(id)
);

create table lxf_poll(
id number(10),
teacher_id number(4) references lxf_person(id),
course_id number(2) references lxf_course(id),
classes_id number(3) references lxf_classes(id),
checked_date Date,
checked_person_id number(4) references lxf_person(id),
created_date Date,
amount number(2),
available_num number(2),
totalScore number(5),
avg_score number(3,2),
status varchar2(1) default 'n',
primary key(id)
);

create table lxf_poll_line(
	id number(10),
	poll_id number(10),
	c1 number(1),
	c2 number(1),
	c3 number(1),
	c4 number(1),
	c5 number(1),
	c6 number(1),
	c7 number(1),
	c8 number(1),
	c9 number(1),
	c10 number(1),
	c11 number(1),
	c12 number(1),
	c13 number(1),
	c14 number(1),
	q1 varchar2(4000),													
	q2 varchar2(4000),
	avgScore number(3,2),
	isValid varchar2(1),		
	primary key(id),
	foreign key(poll_id) references lxf_poll(id)
);

 insert into lxf_person values(1001,'liuxf','liuxf','a');
 insert into lxf_person values(1002,'liuxf1','liuxf1','m');
 insert into lxf_person values(1003,'liuxf2','liuxf2','t');
 commit;