以下查询操作使用SQL语句完成:查询共有多少学生 查询每个系有多少学生 查询选课门数在两门以上的同学的选课门数及其平均成绩 统计已有学生选课的课程的课程号,选课人数,最高成绩,最低成绩
时间: 2024-10-15 19:13:05 浏览: 39
50条SQL查询技巧、查询语句示例
针对这些SQL查询需求,我们需要假设相关的数据库表结构,例如:
1. 学生表(Students)包含字段:StudentID, StudentName
2. 系别表(Departments)包含字段:DepartmentID, DepartmentName
3. 课程表(Courses)包含字段:CourseID, CourseName
4. 选课记录表(Enrollments)包含字段:StudentID, CourseID, Grade
下面是对应的SQL语句示例:
1. 查询总学生数:
```sql
SELECT COUNT(*)
FROM Students;
```
2. 查询每个系的学生数:
```sql
SELECT Departments.DepartmentName, COUNT(Students.StudentID) AS TotalStudents
FROM Departments
JOIN Students ON Departments.DepartmentID = Students.DepartmentID
GROUP BY Departments.DepartmentName;
```
3. 查询选课门数在两门以上同学的选课门数及其平均成绩:
```sql
WITH HighGraders AS (
SELECT StudentID, COUNT(CourseID) as CourseCount, AVG(Grade) as AverageGrade
FROM Enrollments
GROUP BY StudentID
HAVING COUNT(CourseID) > 2
)
SELECT CourseCount, AverageGrade
FROM HighGraders;
```
4. 统计已有学生选课的课程信息:
```sql
SELECT Courses.CourseID, COUNT(DISTINCT Enrollments.StudentID) as NumberOfStudents,
MAX(Enrollments.Grade) as HighestScore, MIN(Enrollments.Grade) as LowestScore
FROM Courses
LEFT JOIN Enrollments ON Courses.CourseID = Enrollments.CourseID
GROUP BY Courses.CourseID;
```
阅读全文