oracle学生表与spj供应表

drop table student;
drop table course;
drop table sc;

create  table  student (
	sno  char(12)  not null ,
	sname  varchar2(20) ,
	ssex  char(3) ,
	sage  number(3）,
	sdept  varchar2(20) ,
	primary  key(sno)  
);


insert into student(sno, sname, ssex, sage, sdept)
values('200215121', '李勇', '男',20,'CS');

insert into student(sno, sname, ssex, sage, sdept)
values('200215122', '刘晨', '女',19,'CS');

insert into student(sno, sname, ssex, sage, sdept)
values('200215123', '王敏', '女',18,'MA');

insert into student(sno, sname, ssex, sage, sdept)
values('200215125', '张立', '男',19,'IS');

create  table Course (
	cno varchar2(6) NOT NULL ,
	cname varchar2(20) NOT NULL ,
	Cpno varchar2(6) NULL ,
	Ccredit number(2）NULL ,
	primary  key (cno) 
    );

INSERT INTO Course(cno, cname, Cpno ,Ccredit)
VALUES('1', '数据库',5,4);

INSERT INTO Course(cno, cname, Cpno ,Ccredit)
VALUES('2', '数学',NULL,2);

INSERT INTO Course(cno, cname, Cpno ,Ccredit)
VALUES('3', '信息系统',1,4);

INSERT INTO Course(cno, cname, Cpno ,Ccredit)
VALUES('4', '操作系统',6,3);

INSERT INTO Course(cno, cname, Cpno ,Ccredit)
VALUES('5', '数据结构',7,4);

INSERT INTO Course(cno, cname, Cpno ,Ccredit)
VALUES('6', '数据处理',NULL,2);

INSERT INTO Course(cno, cname, Cpno ,Ccredit)
VALUES('7', 'PASCAL语言',6,4);

CREATE TABLE sc (
	sno char(12)  NOT NULL ,
	cno varchar2(6) NOT NULL ,
	grade number(3） NULL ,
	PRIMARY KEY (sno,cno)
) ;

INSERT INTO sc(sno, cno, grade)
VALUES('200215121','1',92);

INSERT INTO sc(sno, cno, grade)
VALUES('200215121','2',85);

INSERT INTO sc(sno, cno, grade)
VALUES('200215121','3',88);

INSERT INTO sc(sno, cno, grade)
VALUES('200215122','2',90);

INSERT INTO sc(sno, cno, grade)
VALUES('200215122','3',80);

DROP TABLE SPJ; 

DROP TABLE S; 
DROP TABLE P; 
DROP TABLE J; 



CREATE TABLE s (
	sno varchar2(10) NOT NULL ,
	sname varchar2(50) NULL ,
	status NUMBER(4) NULL ,
	city varchar2(20)  NULL ,
	PRIMARY KEY (sno) 
) ;

CREATE TABLE p (
	pno varchar2(10)  NOT NULL ,
	pname varchar2(50)  NULL ,
	color varchar2(10)  NULL ,
	weight  NUMBER(4) NULL ,
	PRIMARY KEY (pno)
) ;

CREATE TABLE j (
	jno varchar2(10)  NOT NULL ,
	jname varchar2(50)  NULL ,
	city varchar2(20)  NULL ,
	PRIMARY KEY(jno)
) ;
CREATE TABLE spj (
	sno varchar2 (10)  NOT NULL ,
	pno varchar2 (10)  NOT NULL ,
	jno varchar2 (10)  NOT NULL ,
	qty  NUMBER(8) NULL ,
	PRIMARY KEY (sno,pno,jno),
        foreign key (sno) references s(sno),
        foreign key (pno) references p(pno),
        foreign key (jno) references j(jno)
);


INSERT INTO s(sno, sname, status, city)
VALUES('s1','精益',20,'天津');


INSERT INTO s(sno, sname, status, city)
VALUES('s2','盛锡',10,'北京');

INSERT INTO s(sno, sname, status, city)
VALUES('s3','东方红',30,'北京');

INSERT INTO s(sno, sname, status, city)
VALUES('s4','丰泰盛',20,'天津');

INSERT INTO s(sno, sname, status, city)
VALUES('s5','为民',30,'上海');

INSERT INTO p(pno, pname, color, weight)
VALUES('p1','螺母','红',12);

INSERT INTO p(pno, pname, color, weight)
VALUES('p2','螺栓','绿',17);

INSERT INTO p(pno, pname, color, weight)
VALUES('p3','螺丝刀','蓝',14);

INSERT INTO p(pno, pname, color, weight)
VALUES('p4','螺丝刀','红',14);

INSERT INTO p(pno, pname, color, weight)
VALUES('p5','凸轮','蓝',40);

INSERT INTO p(pno, pname, color, weight)
VALUES('p6','齿轮','红',30);

INSERT INTO j(jno, jname, city)
VALUES('j1','三建','北京');

INSERT INTO j(jno, jname, city)
VALUES('j2','一气','长春');

INSERT INTO j(jno, jname, city)
VALUES('j3','弹簧厂','天津');

INSERT INTO j(jno, jname, city)
VALUES('j4','造船厂','天津');

INSERT INTO j(jno, jname, city)
VALUES('j5','机床厂','唐山');

INSERT INTO j(jno, jname, city)
VALUES('j6','无线电厂','常州');

INSERT INTO j(jno, jname, city)
VALUES('j7','半导体厂','南京');

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s1','p1','j1',200);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s1','p1','j3',100);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s1','p1','j4',700);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s1','p2','j2',100);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s2','p3','j1',400);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s2','p3','j2',200);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s2','p3','j4',500);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s2','p3','j5',400);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s2','p5','j1',400);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s2','p5','j2',100);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s3','p1','j1',200);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s3','p3','j1',200);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s4','p5','j1',100);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s4','p6','j3',300);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s4','p6','j4',200);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s5','p2','j4',100);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s5','p3','j1',200);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s5','p6','j2',200);

INSERT INTO spj(sno, pno, jno, qty)
VALUES('s5','p6','j4',500);