生成sqlserver学生信息管理系统，给出字段注释，需要5个表每个表加5条数据，给出完整的步骤，并且给出20个sql问题和答案

1. 创建数据库

CREATE DATABASE StudentInfo;

2. 创建表格

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Gender CHAR(1) NOT NULL,
    DateOfBirth DATE NOT NULL,
    Email VARCHAR(50) NOT NULL,
    PhoneNumber VARCHAR(20) NOT NULL,
    Address VARCHAR(100) NOT NULL
);

CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50) NOT NULL,
    Department VARCHAR(50) NOT NULL,
    Credits INT NOT NULL
);

CREATE TABLE Enrollment (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT NOT NULL,
    CourseID INT NOT NULL,
    Grade DECIMAL(3,2),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

CREATE TABLE Instructor (
    InstructorID INT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(50) NOT NULL,
    PhoneNumber VARCHAR(20) NOT NULL,
    Department VARCHAR(50) NOT NULL
);

CREATE TABLE Department (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50) NOT NULL,
    Budget DECIMAL(10,2) NOT NULL,
    StartDate DATE NOT NULL,
    InstructorID INT NOT NULL,
    FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);

3. 插入数据

INSERT INTO Student (StudentID, FirstName, LastName, Gender, DateOfBirth, Email, PhoneNumber, Address) VALUES
(1, 'John', 'Doe', 'M', '1999-01-01', 'john.doe@email.com', '1234567890', '123 Main St'),
(2, 'Jane', 'Doe', 'F', '1999-01-02', 'jane.doe@email.com', '2345678901', '456 Center St'),
(3, 'Bob', 'Smith', 'M', '1999-01-03', 'bob.smith@email.com', '3456789012', '789 Pine St'),
(4, 'Sue', 'Johnson', 'F', '1999-01-04', 'sue.johnson@email.com', '4567890123', '321 Oak St'),
(5, 'Tom', 'Brown', 'M', '1999-01-05', 'tom.brown@email.com', '5678901234', '654 Birch St');

INSERT INTO Course (CourseID, CourseName, Department, Credits) VALUES
(1, 'Introduction to Computer Science', 'Computer Science', 4),
(2, 'Calculus I', 'Mathematics', 4),
(3, 'Introduction to Psychology', 'Psychology', 3),
(4, 'Introduction to Sociology', 'Sociology', 3),
(5, 'World History', 'History', 3);

INSERT INTO Enrollment (EnrollmentID, StudentID, CourseID, Grade) VALUES
(1, 1, 1, 3.7),
(2, 2, 1, 3.9),
(3, 3, 2, 3.5),
(4, 4, 3, 4.0),
(5, 5, 4, 3.8);

INSERT INTO Instructor (InstructorID, FirstName, LastName, Email, PhoneNumber, Department) VALUES
(1, 'David', 'Johnson', 'david.johnson@email.com', '1234567890', 'Computer Science'),
(2, 'Mary', 'Smith', 'mary.smith@email.com', '2345678901', 'Mathematics'),
(3, 'Susan', 'Jones', 'susan.jones@email.com', '3456789012', 'Psychology'),
(4, 'John', 'Brown', 'john.brown@email.com', '4567890123', 'Sociology'),
(5, 'Robert', 'Lee', 'robert.lee@email.com', '5678901234', 'History');

INSERT INTO Department (DepartmentID, DepartmentName, Budget, StartDate, InstructorID) VALUES
(1, 'Computer Science', 100000, '2000-01-01', 1),
(2, 'Mathematics', 80000, '2000-01-01', 2),
(3, 'Psychology', 60000, '2000-01-01', 3),
(4, 'Sociology', 50000, '2000-01-01', 4),
(5, 'History', 40000, '2000-01-01', 5);

4. SQL问题及其答案

1. 查询所有学生信息

SELECT * FROM Student;

2. 查询所有课程信息

SELECT * FROM Course;

3. 查询所有学生选修的课程和成绩

SELECT s.FirstName, s.LastName, c.CourseName, e.Grade
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID
JOIN Course c ON e.CourseID = c.CourseID;

4. 查询某个学生选修的所有课程和成绩

SELECT s.FirstName, s.LastName, c.CourseName, e.Grade
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID
JOIN Course c ON e.CourseID = c.CourseID
WHERE s.FirstName = 'John' AND s.LastName = 'Doe';

5. 查询某个课程的所有学生和成绩

SELECT s.FirstName, s.LastName, c.CourseName, e.Grade
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID
JOIN Course c ON e.CourseID = c.CourseID
WHERE c.CourseName = 'Introduction to Computer Science';

6. 查询成绩大于等于3.5的学生和课程信息

SELECT s.FirstName, s.LastName, c.CourseName, e.Grade
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID
JOIN Course c ON e.CourseID = c.CourseID
WHERE e.Grade >= 3.5;

7. 查询每个系的预算总额

SELECT d.DepartmentName, SUM(d.Budget) AS TotalBudget
FROM Department d
GROUP BY d.DepartmentName;

8. 查询选修课程最多的学生

SELECT s.FirstName, s.LastName, COUNT(e.CourseID) AS NumCourses
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID
GROUP BY s.FirstName, s.LastName
ORDER BY NumCourses DESC
LIMIT 1;

9. 查询任课教师是David Johnson的所有课程

SELECT c.CourseName, c.Department
FROM Course c
JOIN Instructor i ON c.Department = i.Department
WHERE i.FirstName = 'David' AND i.LastName = 'Johnson';

10. 查询选修了Introduction to Computer Science课程的所有学生

SELECT s.FirstName, s.LastName
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID
JOIN Course c ON e.CourseID = c.CourseID
WHERE c.CourseName = 'Introduction to Computer Science';

11. 查询选修了Calculus I课程的学生数量

SELECT COUNT(DISTINCT e.StudentID) AS NumStudents
FROM Enrollment e
JOIN Course c ON e.CourseID = c.CourseID
WHERE c.CourseName = 'Calculus I';

12. 查询选修课程数量大于等于3门的学生

SELECT s.FirstName, s.LastName, COUNT(e.CourseID) AS NumCourses
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID
GROUP BY s.FirstName, s.LastName
HAVING NumCourses >= 3;

13. 查询每个学生的平均成绩

SELECT s.FirstName, s.LastName, AVG(e.Grade) AS AvgGrade
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID
GROUP BY s.FirstName, s.LastName;

14. 查询每个系的平均预算

SELECT d.DepartmentName, AVG(d.Budget) AS AvgBudget
FROM Department d
GROUP BY d.DepartmentName;

15. 查询选修课程数量最多的学生的选修课程信息

SELECT s.FirstName, s.LastName, c.CourseName, e.Grade
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID
JOIN Course c ON e.CourseID = c.CourseID
WHERE s.StudentID = (
    SELECT e2.StudentID
    FROM Enrollment e2
    GROUP BY e2.StudentID
    ORDER BY COUNT(DISTINCT e2.CourseID) DESC
    LIMIT 1
);

16. 查询选修了所有课程的学生

SELECT s.FirstName, s.LastName
FROM Student s
WHERE NOT EXISTS (
    SELECT c.CourseID
    FROM Course c
    WHERE NOT EXISTS (
        SELECT e.StudentID
        FROM Enrollment e
        WHERE e.StudentID = s.StudentID AND e.CourseID = c.CourseID
    )
);

17. 查询选修了至少一门课程的学生数量

SELECT COUNT(DISTINCT e.StudentID) AS NumStudents
FROM Enrollment e;

18. 查询选修了所有课程的学生数量

SELECT COUNT(DISTINCT e.StudentID) AS NumStudents
FROM Enrollment e
WHERE NOT EXISTS (
    SELECT c.CourseID
    FROM Course c
    WHERE NOT EXISTS (
        SELECT e2.StudentID
        FROM Enrollment e2
        WHERE e2.StudentID = e.StudentID AND e2.CourseID = c.CourseID
    )
);

19. 查询选修课程最多的系

SELECT d.DepartmentName, COUNT(DISTINCT e.StudentID) AS NumStudents
FROM Department d
JOIN Instructor i ON d.InstructorID = i.InstructorID
JOIN Course c ON d.DepartmentName = c.Department
JOIN Enrollment e ON c.CourseID = e.CourseID
GROUP BY d.DepartmentName
ORDER BY NumStudents DESC
LIMIT 1;

20. 查询每个学生的选修课程数量

SELECT s.FirstName, s.LastName, COUNT(e.CourseID) AS NumCourses
FROM Student s
JOIN Enrollment e ON s.StudentID = e.StudentID
GROUP BY s.FirstName, s.LastName;