pta mysql查询题目

1. 查询所有学生的姓名和年龄。

SELECT name, age FROM students;

2. 查询学号为1001的学生的姓名和性别。

SELECT name, gender FROM students WHERE id = 1001;

3. 查询所有女生的姓名、年龄和班级。

SELECT name, age, class FROM students WHERE gender = '女';

4. 查询年龄大于等于18岁的学生的姓名和年龄。

SELECT name, age FROM students WHERE age >= 18;

5. 查询班级为1班或2班的学生的姓名和班级。

SELECT name, class FROM students WHERE class IN ('1班', '2班');

6. 查询所有未修满学分的学生的姓名和学分。

SELECT name, credit FROM students WHERE credit < 100;

7. 查询所有男生的平均年龄。

SELECT AVG(age) FROM students WHERE gender = '男';

8. 查询所有班级的学生人数。

SELECT class, COUNT(*) FROM students GROUP BY class;

9. 查询所有学生的姓名和选课的课程名称。

SELECT students.name, courses.name FROM students JOIN course_selection ON students.id = course_selection.student_id JOIN courses ON course_selection.course_id = courses.id;

10. 查询选修了所有课程的学生的姓名。

SELECT name FROM students WHERE id IN (SELECT student_id FROM course_selection GROUP BY student_id HAVING COUNT(DISTINCT course_id) = (SELECT COUNT(*) FROM courses));