sql 查询选修并且成绩及格的课程总数大于3门（包括3门）的学生学号和姓名

SELECT 学生.学号, 学生.姓名 FROM 学生, 成绩 WHERE 学生.学号 = 成绩.学号 AND 成绩.成绩 >= 60 AND 成绩.课程编号 IN (SELECT 课程编号 FROM 课程 WHERE 课程.必修 = '否' GROUP BY 课程编号 HAVING COUNT(*) >= 3);

很好，您的问题是关于 SQL 语句的。以下是您提出的问题的 SQL 查询语句： (1)查询选修课程号为C01，并且成绩及格的学生学号、姓名和系别名称。 ``` SELECT StudentID, StudentName, DepartmentName FROM Student, Department, SC WHERE Student.StudentID = SC.StudentID AND SC.CourseID = 'C01' AND SC.Score >= 60 AND Student.DepartmentID = Department.DepartmentID; ``` (2)查询计算机学院选修C03课程的学生姓名、课程名称以及这门课程的成绩。 ``` SELECT StudentName, CourseName, Score FROM Student, Course, SC, Department WHERE Student.StudentID = SC.StudentID AND Course.CourseID = SC.CourseID AND Department.DepartmentID = Student.DepartmentID AND Department.DepartmentName = '计算机学院' AND Course.CourseID = 'C03'; ``` (3)在Reports表中查询至少选修课了程号为C01和C02的学生学号和姓名。 ``` SELECT StudentID, StudentName FROM Reports WHERE CourseID IN ('C01','C02') GROUP BY StudentID, StudentName HAVING COUNT(DISTINCT CourseID) >= 2; ``` (4)查询既没有选修课程号C01，也没有选修课程号C02的学生的学号和姓名。 ``` SELECT StudentID, StudentName FROM Student WHERE StudentID NOT IN ( SELECT StudentID FROM SC WHERE CourseID IN ('C01','C02') ); ``` (5)查询每个同学基本信息及其选修课程的情况。 ``` SELECT Student.StudentID, Student.StudentName, Student.Gender, Student.Birthday, Student.Address, Department.DepartmentName, SC.CourseID, Course.CourseName, SC.Score FROM Student LEFT JOIN SC ON Student.StudentID = SC.StudentID LEFT JOIN Course ON SC.CourseID = Course.CourseID LEFT JOIN Department ON Student.DepartmentID = Department.DepartmentID ORDER BY Student.StudentID, SC.CourseID; ``` (6)查询每个学生及其选修课程的情况(若某一个学生没有选课，只输出学生的基本情况信息，其选课信息为空值)。 ``` SELECT Student.StudentID, Student.StudentName, Student.Gender, Student.Birthday, Student.Address, Department.DepartmentName, SC.CourseID, Course.CourseName, SC.Score FROM Student LEFT JOIN SC ON Student.StudentID = SC.StudentID LEFT JOIN Course ON SC.CourseID = Course.CourseID LEFT JOIN Department ON Student.DepartmentID = Department.DepartmentID ORDER BY Student.StudentID, SC.CourseID; ``` (7)查询选修了课程名为“GIS开发”的学生学号和姓名。 ``` SELECT Student.StudentID, Student.StudentName FROM Student, SC, Course WHERE Student.StudentID = SC.StudentID AND SC.CourseID = Course.CourseID AND Course.CourseName = 'GIS开发'; ``` (8)查询没有选修C02课程的学生学号和姓名。 ``` SELECT Student.StudentID, Student.StudentName FROM Student WHERE Student.StudentID NOT IN ( SELECT StudentID FROM SC WHERE CourseID = 'C02' ); ``` (9)查询所有课程成绩均大于90分的学生学号和姓名。 ``` SELECT StudentID, StudentName FROM SC, Student WHERE SC.StudentID = Student.StudentID AND Score >= 90 GROUP BY StudentID, StudentName HAVING COUNT(DISTINCT CourseID) = ( SELECT COUNT(DISTINCT CourseID) FROM Course ); ``` (10)查询选修了4门及以上课程的学生的学号、姓名和选修课程数。 ``` SELECT StudentID, StudentName, COUNT(*) AS CourseCount FROM SC, Student WHERE SC.StudentID = Student.StudentID GROUP BY StudentID, StudentName HAVING COUNT(*) >= 4; ``` (11)查询选修全部课程的学生姓名，相当于查询这样的学生，没有一门课程是他不选的。 ``` SELECT Student.StudentName FROM Student WHERE NOT EXISTS ( SELECT CourseID FROM Course WHERE NOT EXISTS ( SELECT SC.StudentID FROM SC WHERE SC.StudentID = Student.StudentID AND SC.CourseID = Course.CourseID ) ); ``` (12)查询平均成绩大于等于黄河同学的平均成绩的学生的学号、姓名和平均成绩。 ``` SELECT AVG(SC.Score) AS AvgScore, Student.StudentID, Student.StudentName FROM SC, Student WHERE SC.StudentID = Student.StudentID GROUP BY Student.StudentID, Student.StudentName HAVING AVG(SC.Score) >= ( SELECT AVG(SC.Score) FROM SC, Student WHERE SC.StudentID = Student.StudentID AND Student.StudentName = '黄河' ); ``` (13)查询每门课程获得最高分的同学的学号、姓名、课程名称和成绩，并按照最高成绩从高到低进行排序。 ``` SELECT SC.StudentID, Student.StudentName, Course.CourseName, MAX(SC.Score) AS MaxScore FROM SC, Student, Course WHERE SC.StudentID = Student.StudentID AND SC.CourseID = Course.CourseID GROUP BY SC.CourseID ORDER BY MaxScore DESC; ```