查询所有选修课程成绩均大于55分的学生s_number。

这个问题需要至少两个表：一个是学生表，包含学生的学号和姓名等基本信息；另一个是成绩表，包含学生的学号、课程号和成绩等信息。

假设学生表名为students，成绩表名为scores，那么可以使用以下SQL语句查询所有选修课程成绩均大于55分的学生学号：

SELECT s_number
FROM students
WHERE s_number NOT IN (
    SELECT s_number
    FROM scores
    WHERE score <= 55
);

这个查询语句的原理是，首先从学生表中选择所有学生的学号。然后，从成绩表中选择所有成绩小于等于55分的学生学号。最后，在学生表中排除这些学生，并返回剩余的学生学号，这些学生的选修课程成绩均大于55分。

相关问题

1、建立数据库，以自己的名字+最后两位学号命名。库中有如下关系，关系模式如下： 教师（教师号，教师名，部门,奖金） 学生（学号，姓名，性别，年龄，系,奖学金） 课程（课程号,课程名,学分，教师号） 选课(学号,课程号,成绩) 创建上述表结构，并插入元组 2、请在“课程”表中插入('c8', 'java', 3 ,'0005') 请在“选课”中中插入('95003', 'c3') 3、查询刘老师所授课程的课程号、课程名及学生选课的名单（包括学号、姓名） 4、查询各学生的选课门数，要求查询学号、姓名和选课门数。 5、查询每个学生所修课程的总学分（成绩大于等于60才有学分） 6、查询选修了课程名为“数据库原理”的学生的学号，姓名和成绩 7、按性别统计男女生人数，显示性别和人数。查询获得奖学金的学生的学号和姓名，系。 8、查询平均成绩在85以上的学生的学号和姓名。

抱歉，我作为一个AI语言模型，并没有数据库查询的功能。不过，我可以告诉你一个基本的SQL查询语句，你可以在相关的数据库管理系统中使用它来查询：

1. 建立数据库并创建表：

CREATE DATABASE your_name+last_two_digits_of_student_number;

CREATE TABLE teacher (
  teacher_id INT PRIMARY KEY,
  teacher_name VARCHAR(50),
  department VARCHAR(50),
  bonus INT
);

CREATE TABLE student (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(50),
  gender VARCHAR(10),
  age INT,
  department VARCHAR(50),
  scholarship INT
);

CREATE TABLE course (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(50),
  credit INT,
  teacher_id INT,
  FOREIGN KEY (teacher_id) REFERENCES teacher(teacher_id)
);

CREATE TABLE choose_course (
  student_id INT,
  course_id INT,
  score INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES student(student_id),
  FOREIGN KEY (course_id) REFERENCES course(course_id)
);

2. 插入元组：

INSERT INTO course (course_id, course_name, credit, teacher_id)
VALUES ('c8', 'java', 3 ,'0005');

INSERT INTO choose_course (student_id, course_id)
VALUES ('95003', 'c3');

3. 查询刘老师所授课程的课程号、课程名及学生选课的名单（包括学号、姓名）：

SELECT course.course_id, course.course_name, student.student_id, student.student_name
FROM course
INNER JOIN teacher ON course.teacher_id = teacher.teacher_id
INNER JOIN choose_course ON course.course_id = choose_course.course_id
INNER JOIN student ON choose_course.student_id = student.student_id
WHERE teacher.teacher_name = '刘老师';

4. 查询各学生的选课门数，要求查询学号、姓名和选课门数：

SELECT student.student_id, student.student_name, COUNT(choose_course.course_id) AS courses_num
FROM student
LEFT JOIN choose_course ON student.student_id = choose_course.student_id
GROUP BY student.student_id;

5. 查询每个学生所修课程的总学分（成绩大于等于60才有学分）：

SELECT student.student_id, student.student_name, SUM(course.credit) AS total_credits
FROM student
INNER JOIN choose_course ON student.student_id = choose_course.student_id
INNER JOIN course ON choose_course.course_id = course.course_id
WHERE choose_course.score >= 60
GROUP BY student.student_id;

6. 查询选修了课程名为“数据库原理”的学生的学号，姓名和成绩：

SELECT student.student_id, student.student_name, choose_course.score
FROM student
INNER JOIN choose_course ON student.student_id = choose_course.student_id
INNER JOIN course ON choose_course.course_id = course.course_id
WHERE course.course_name = '数据库原理';

7. 按性别统计男女生人数，显示性别和人数。查询获得奖学金的学生的学号和姓名，系：

-- 统计男女生人数
SELECT gender, COUNT(*) AS num
FROM student
GROUP BY gender;

-- 查询获得奖学金的学生的学号和姓名，系
SELECT student_id, student_name, department
FROM student
WHERE scholarship > 0;

8. 查询平均成绩在85以上的学生的学号和姓名：

SELECT student.student_id, student.student_name
FROM student
INNER JOIN choose_course ON student.student_id = choose_course.student_id
GROUP BY student.student_id
HAVING AVG(choose_course.score) >= 85;

生成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;