编辑代码

CREATE DATABASE test;
use test;


CREATE TABLE Students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);


CREATE TABLE Courses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);


CREATE TABLE Credits (
    StudentID INT,
    CourseID INT,
    Credit DECIMAL(5, 2)
);


INSERT INTO Students (name) VALUES
('Alice'), ('Bob'), ('Charlie'), ('David'), ('Eve'), ('Frank'),('Geng');


INSERT INTO Courses (name) VALUES
('Math'), ('Science'), ('History'), ('English');


INSERT INTO Credits (StudentID, CourseID, Credit) VALUES
(1, 1, 3.0), (1, 2, 4.0), (1, 3, 3.5),
(2, 1, 4.0), (2, 2, 3.0),
(3, 1, 3.0), (3, 2, 4.0), (3, 3, 2.0),
(4, 3, 3.5), (4, 4, 3.0),
(5, 2, 4.0), (5, 1, 4.0), (5, 3, 2.0),
(6, 1, 3.0), (6, 3, 3.5), (6, 4, 3.0), (6, 2, 4.0),
(1, 4, 3.0), (2, 3, 3.5), (4, 1, 2.0);


select s.id,s.name,count(CourseID) from Students s
join Credits c on s.id=c.StudentID
group by StudentID
having count(CourseID)>=4