编辑代码

CREATE DATABASE test;
use test;
CREATE TABLE CLASS (
    CNO CHAR(10) PRIMARY KEY, 
    DEPARTMENT CHAR(50), 
    SPECIALITY CHAR(50), 
    MONITOR CHAR(10)
);

CREATE TABLE STUDENT (
    SNO CHAR(10) PRIMARY KEY, 
    SNAME CHAR(50), 
    GENDER CHAR(1), 
    CNO CHAR(10), 
    FOREIGN KEY (CNO) REFERENCES CLASS(CNO) 
);


CREATE TABLE LESSON (
    LNO CHAR(10) PRIMARY KEY,
    LNAME CHAR(50),
    BOOK CHAR(50), 
    CREDIT INT 
);

CREATE TABLE TEACHER (
    TID CHAR(10) PRIMARY KEY, 
    TNAME CHAR(50), 
    DEPARTMENT CHAR(50) 
);

CREATE TABLE ELECTION (
    CNO CHAR(10), 
    LNO CHAR(10), 
    TID CHAR(10), 
    SYEAR INT,
    SEMESTER INT, 
    PRIMARY KEY (CNO, LNO, TID, SYEAR, SEMESTER), 
    FOREIGN KEY (CNO) REFERENCES CLASS(CNO), 
    FOREIGN KEY (LNO) REFERENCES LESSON(LNO),
    FOREIGN KEY (TID) REFERENCES TEACHER(TID) 
);

CREATE TABLE GRADE (
    SNO CHAR(10), 
    LNO CHAR(10), 
    SCORE INT, 
    PRIMARY KEY (SNO, LNO), 
    FOREIGN KEY (SNO) REFERENCES STUDENT(SNO), 
    FOREIGN KEY (LNO) REFERENCES LESSON(LNO) 
);

INSERT INTO CLASS (CNO, DEPARTMENT, SPECIALITY, MONITOR) VALUES
('C001', '计算机学院', '软件工程', 'S001'),  
('C002', '计算机学院', '数据库', 'S003'),
('C003', '电子工程学院', '通信工程', 'S004');

INSERT INTO STUDENT (SNO, SNAME, GENDER, CNO) VALUES
('S001', '张三', 'M', 'C001'),
('S002', '李四', 'F', 'C001'),
('S003', '王五', 'M', 'C002'),
('S004', '赵六', 'F', 'C003');

-- 插入课程数据（LESSON）
INSERT INTO LESSON (LNO, LNAME, BOOK, CREDIT) VALUES
('L001', '数据库', '数据库系统概念', 3),
('L002', '软件工程', '软件工程原理', 4),
('L003', '操作系统', '现代操作系统', 3);

-- 插入教师数据（TEACHER）
INSERT INTO TEACHER (TID, TNAME, DEPARTMENT) VALUES
('T001', '王老师', '计算机学院'),
('T002', '李老师', '计算机学院'),
('T003', '张老师', '电子工程学院');

INSERT INTO ELECTION (CNO, LNO, TID, SYEAR, SEMESTER) VALUES
('C001', 'L001', 'T001', 2024, 1),
('C001', 'L002', 'T002', 2024, 1),
('C002', 'L001', 'T001', 2024, 1),
('C003', 'L003', 'T003', 2024, 1);

INSERT INTO ELECTION (CNO, LNO, TID, SYEAR, SEMESTER) VALUES
('C001', 'L001', 'T001', 2018, 1),
('C001', 'L002', 'T001', 2018, 2),
('C002', 'L002', 'T002', 2018, 1),
('C003', 'L003', 'T003', 2018, 1);

INSERT INTO GRADE (SNO, LNO, SCORE) VALUES
('S001', 'L001', 90),
('S001', 'L002', 85),
('S002', 'L001', 88),
('S003', 'L001', 92),
('S004', 'L003', 80);

SELECT MONITOR,SNAME,CLASS.CNO,SPECIALITY FROM CLASS,STUDENT WHERE CLASS.MONITOR = STUDENT.SNO;

SELECT TID,LNO FROM ELECTION WHERE SYEAR = 2018 AND TID IN(
                                                            SELECT TID
                                                            FROM ELECTION
                                                            WHERE SYEAR = 2018
                                                            GROUP BY TID HAVING COUNT(*) >= 2);

SELECT  T.TID ,T.TNAME,COUNT(E.LNO),SUM(L.CREDIT) 
FROM TEACHER T
JOIN 
    ELECTION E ON T.TID = E.TID
JOIN 
    LESSON L ON E.LNO = L.LNO
WHERE  T.DEPARTMENT = '计算机' AND E.SYEAR = 2018
GROUP BY T.TID, T.TNAME
ORDER BY '总学分数' ASC;

SELECT CLASS.CNO,SPECIALITY,COUNT(SNO) 
FROM CLASS,STUDENT WHERE CLASS.CNO = STUDENT.CNO 
                    AND CLASS.CNO  IN (SELECT ELECTION.CNO 
                                FROM ELECTION,LESSON 
                                WHERE LNAME = '数据库' AND ELECTION.LNO AND LESSON.LNO) 
                    AND CLASS.CNO NOT IN (SELECT ELECTION.CNO 
                                FROM ELECTION,LESSON 
                                WHERE LNAME = '软件工程' AND ELECTION.LNO AND LESSON.LNO)
GROUP BY CLASS.CNO,SPECIALITY;

CREATE VIEW V1 AS
SELECT STUDENT.SNO,SNAME,GENDER,STUDENT.CNO,SCORE
 FROM STUDENT
JOIN CLASS  ON STUDENT.CNO = CLASS .CNO
JOIN GRADE  ON STUDENT.SNO = GRADE.SNO
JOIN LESSON  ON GRADE.LNO = LESSON.LNO
WHERE CLASS.SPECIALITY = '计算机学院' AND LESSON.LNAME = '数据库';

SELECT * FROM V1;

SELECT SNO, SNAME, SCORE 
FROM V1 
ORDER BY SCORE DESC 
LIMIT 1;