新建 WinRAR ZIP archiv...

CREATE TABLE Student (
Sno CHAR(8) PRIMARY KEY,
Sname VARCHAR(20) NOT NULL,
Ssex CHAR(2) CHECK (Ssex IN ('男', '女')),
Sbirthdate DATE,
Smajor VARCHAR(50)
);
CREATE TABLE Course (
Cno CHAR(5) PRIMARY KEY,
Cname VARCHAR(50) NOT NULL,
Credit INT CHECK (Credit > 0),
Cpno CHAR(5),
FOREIGN KEY (Cpno) REFERENCES Course(Cno) ON UPDATE NO ACTION ON DELETE NO ACTION
);
CREATE TABLE SC (
Sno CHAR(8),
Cno CHAR(5),
Grade INT CHECK (Grade >= 0 AND Grade <= 100),
Semester CHAR(6),
Teachingclass VARCHAR(20),
PRIMARY KEY (Sno, Cno),
FOREIGN KEY (Sno) REFERENCES Student(Sno) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (Cno) REFERENCES Course(Cno) ON UPDATE CASCADE ON DELETE CASCADE
);
INSERT INTO Student (Sno, Sname, Ssex, Sbirthdate, Smajor)
VALUES
('20180001', '李勇', '男', '2000-03-08', '信息安全'),
('20180002', '刘晨', '女', '1999-09-01', '计算机科学与技术'),
('20180003', '王敏', '女', '2001-08-01', '计算机科学与技术'),
('20180004', '张立', '男', '2000-01-08', '计算机科学与技术'),
('20180005', '陈新奇', '男', '2001-11-01', '信息管理与信息系统'),
('20180006', '赵明', '男', '2000-06-12', '数据科学与大数据技术'),
('20180007', '王佳佳', '女', '2001-12-07', '数据科学与大数据技术');
INSERT INTO Course (Cno, Cname, Credit, Cpno)
VALUES
('81001', '程序设计基础与C语言', 4, NULL),
('81002', '数据结构', 4, '81001'),
('81003', '数据库系统概论', 4, '81002'),
('81004', '信息系统概论', 4, '81003'),
('81005', '操作系统', 4, '81001'),
('81006', 'Python语言', 3, '81002'),
('81007', '离散数学', 4, NULL),
('81008', '大数据技术概论', 4, '81003');
INSERT INTO SC (Sno, Cno, Grade, Semester, Teachingclass)
VALUES
('20180001', '81001', 85, '20192', '81001-01'),
('20180001', '81002', 96, '20201', '81002-01'),
('20180001', '81003', 87, '20202', '81003-01'),
('20180002', '81001', 80, '20192', '81001-02'),
('20180002', '81002', 98, '20201', '81002-01'),
('20180003', '81001', 71, '20202', '81001-02'),
('20180003', '81003', 82, '20192', '81003-01'),
('20180003', '81002', 76, '20201', '81002-02'),
('20180004', '81001', 56, '20192', '81001-02'),
('20180004', '81002', 97, '20201', '81002-02'),
('20180005', '81003', 68, '20202', '81003-01');

CREATE TABLE S (
SNO CHAR(2) PRIMARY KEY,
SNAME VARCHAR(20) NOT NULL,
STATUS INT,
CITY VARCHAR(20)
);
GO

CREATE TABLE P (
PNO CHAR(2) PRIMARY KEY,
PNAME VARCHAR(20) NOT NULL,
COLOR VARCHAR(10),
WEIGHT INT
);
GO

CREATE TABLE J (
JNO CHAR(2) PRIMARY KEY,
JNAME VARCHAR(20) NOT NULL,
CITY VARCHAR(20)
);
GO

CREATE TABLE SPJ (
SNO CHAR(2),
PNO CHAR(2),
JNO CHAR(2),
QTY INT,
PRIMARY KEY (SNO, PNO, JNO),
FOREIGN KEY (SNO) REFERENCES S(SNO),
FOREIGN KEY (PNO) REFERENCES P(PNO),
FOREIGN KEY (JNO) REFERENCES J(JNO)
);
GO

INSERT INTO S (SNO, SNAME, STATUS, CITY)
VALUES
('S1', '精益', 20, '天津'),
('S2', '盛锡', 10, '北京'),
('S3', '东方红', 30, '北京'),
('S4', '丰泰盛', 20, '天津'),
('S5', '为民', 30, '上海');
GO

INSERT INTO P (PNO, PNAME, COLOR, WEIGHT)
VALUES
('P1', '螺母', '红', 12),
('P2', '螺栓', '绿', 17),
('P3', '螺丝刀', '蓝', 14),
('P4', '螺丝刀', '红', 14),
('P5', '凸轮', '蓝', 40),
('P6', '齿轮', '红', 30);
GO

INSERT INTO J (JNO, JNAME, CITY)
VALUES
('J1', '三建', '北京'),
('J2', '一汽', '长春'),
('J3', '弹簧厂', '天津'),
('J4', '造船厂', '天津'),
('J5', '机车厂', '唐山'),
('J6', '无线电厂', '常州'),
('J7', '半导体厂', '南京');
GO

INSERT INTO SPJ (SNO, PNO, JNO, QTY)
VALUES
('S1', 'P1', 'J1', 200),
('S1', 'P1', 'J3', 100),
('S1', 'P1', 'J4', 700),
('S1', 'P2', 'J2', 100),
('S2', 'P3', 'J1', 400),
('S2', 'P3', 'J2', 200),
('S2', 'P3', 'J4', 500),
('S2', 'P3', 'J5', 400),
('S2', 'P5', 'J1', 400),
('S2', 'P5', 'J2', 100),
('S3', 'P1', 'J1', 200),
('S3', 'P3', 'J1', 200),
('S4', 'P5', 'J1', 100),
('S4', 'P6', 'J3', 300),
('S4', 'P6', 'J4', 200),
('S5', 'P2', 'J4', 100),
('S5', 'P3', 'J1', 200),
('S5', 'P6', 'J2', 200),
('S5', 'P6', 'J4', 500);
GO