编辑代码

-- 创建学生表
CREATE TABLE Student (
    Sno VARCHAR(10) PRIMARY KEY,
    Sname VARCHAR(20),
    Ssex CHAR(2),
    Sage DATETIME
);
-- 插入学生数据
INSERT INTO Student (Sno, Sname, Ssex, Sage) VALUES
('S001', '张三', '男', '1999-01-01 00:00:00'),
('S002', '李四', '女', '1999-02-02 00:00:00'),
('S003', '王五', '男', '1999-03-03 00:00:00'),
('S004', '赵六', '女', '1999-04-04 00:00:00');

-- 创建课程表
CREATE TABLE Course (
    Cno VARCHAR(10) PRIMARY KEY,
    Cname VARCHAR(20),
    Tno VARCHAR(10)
);
-- 插入课程数据
INSERT INTO Course (Cno, Cname, Tno) VALUES
('C001', '数学', 'T001'),
('C002', '英语', 'T002'),
('C003', '物理', 'T003'),
('C004', '化学', 'T004');

-- 创建教师表
CREATE TABLE Teacher (
    Tno VARCHAR(10) PRIMARY KEY,
    Tname VARCHAR(20)
);
-- 插入教师数据
INSERT INTO Teacher (Tno, Tname) VALUES
('T001', '张老师'),
('T002', '李老师'),
('T003', '叶平'),
('T004', '王老师');

-- 创建选课表
CREATE TABLE SC (
    Sno VARCHAR(10),
    Cno VARCHAR(10),
    Grade DECIMAL(5, 2),
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
);
-- 插入选课数据
INSERT INTO SC (Sno, Cno, Grade) VALUES
('S001', 'C001', 85.00),
('S001', 'C002', 78.00),
('S002', 'C001', 90.00),
('S002', 'C003', 55.00),
('S003', 'C002', 66.00),
('S003', 'C003', 70.00),
('S004', 'C001', 88.00),
('S004', 'C004', 50.00);