CREATE TABLE scores id INT PRIMARY KEY AUTO_INCREMENT name VARCHAR20 subject VARCHAR20 score INT ; INSERT INTO scores VALUESnull张三数学90null张三语文50null张三地理40null李四语文55null李四政治45null王五政治30null李四数学80n

1. 查询两门及两门以上不及格的学生姓名:

SELECT name FROM scores GROUP BY name HAVING COUNT(*) >= 2 AND SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) >= 2;

2. 查询学生平均分:

SELECT name, AVG(score) AS average_score FROM scores GROUP BY name;

3. 查询姓名是张三的学生成绩和:

SELECT subject, score FROM scores WHERE name = '张三';

4. 将学生信息按照分数倒序:

SELECT * FROM scores ORDER BY score DESC;

5. 获取学生信息中分数最低的学生姓名和分数最高的学生姓名:

SELECT name, score FROM scores WHERE score = (SELECT MIN(score) FROM scores) OR score = (SELECT MAX(score) FROM scores);

6. 查询两门及两门以上不及格同学的平均分:

SELECT name, AVG(score) AS average_score FROM scores WHERE name IN ( SELECT name FROM scores GROUP BY name HAVING COUNT(*) >= 2 AND SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) >= 2 ) GROUP BY name