oracle创建学生、课程、成绩表

​
---创建Student学生表
-- 删除表
DROP TABLE Student;
-- 创建表
CREATE TABLE Student(
Sno NUMBER(20) PRIMARY KEY,-- 学号 主键
Sname VARCHAR2(20) NOT NULL,-- 姓名 非空
Sage INT NOT NULL,-- 年龄
Ssex VARCHAR2(5) NOT NULL,-- 性别
Sdept VARCHAR2(10) NOT NULL -- 所在系
);
COMMIT;

SELECT * FROM Student;

--创建Course课程表
-- 删除表
DROP TABLE Course;
-- 创建表
CREATE TABLE Course(
Cno NUMBER(20) PRIMARY KEY,-- 课程号 主键
Cname VARCHAR2(50) NOT NULL ,-- 课程名 非空
Cpno VARCHAR2(30)  NOT NULL,-- 先行课
Ccredit number(5,2) NOT NULL-- 学分
);
commit;

select * from Course;

--创建SC成绩表
-- 删除表
DROP TABLE SC;
-- 创建表
CREATE TABLE SC(
Sno NUMBER(20),-- 学号 主键
Cno NUMBER(20),-- 课程号 主键s
Grade INT NOT NULL, -- 成绩
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)
);
commit;

select * from sc;

--插入数据;
INSERT INTO Student
(Sno,Sname,Sage,Ssex,Sdept) VALUES ('20211127','诸葛亮',22,'男','数学系');
commit;
INSERT INTO Student
(Sno,Sname,Sage,Ssex,Sdept) VALUES ('20211128','周瑜',20,'男','农业系');
commit;
INSERT INTO Student
(Sno,Sname,Sage,Ssex,Sdept) VALUES ('20211129','小乔',18,'女','外语系');
commit;
INSERT INTO Student
(Sno,Sname,Sage,Ssex,Sdept) VALUES ('20211130','赵云',23,'男','体育系');
COMMIT;


INSERT INTO Course
(Cno,Cname,Cpno,Ccredit) VALUES ('1001','数据库原理及应用','数据结构',2);
COMMIT;
INSERT INTO Course
(Cno,Cname,Cpno,Ccredit) VALUES ('2002','LINUX操作系统与程序设计','操作系统',4);
COMMIT;
INSERT INTO Course
(Cno,Cname,Cpno,Ccredit) VALUES ('3003','离散数学','高等数学',2);
COMMIT;
INSERT INTO Course
(Cno,Cname,Cpno,Ccredit) VALUES ('4004','Java程序设计语言','c语言和c++语言',2);
COMMIT;
INSERT INTO Course
(Cno,Cname,Cpno,Ccredit) VALUES ('5005','网络安全','计算机网络',4);
COMMIT;



INSERT INTO SC (Sno,cno,Grade) VALUES('20211127','1001',98);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211127','2002',96);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211127','3003',95);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211127','4004',90);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211127','5005',93);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211128','1001',98);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211128','2002',96);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211128','3003',95);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211128','4004',90);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211128','5005',93);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211129','1001',98);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211129','2002',96);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211129','3003',95);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211129','4004',90);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211129','5005',93);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211130','1001',98);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211130','2002',96);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211130','3003',95);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211130','4004',90);
COMMIT;
INSERT INTO SC (Sno,cno,Grade) VALUES('20211130','5005',93);
COMMIT;

​